I have a application that receives data from approximately 1000 different sources every couple of minutes. This data needs to be saved into a OSS database (most likely MySQL - but depending on answers I'd consider switching if appropriate).

The 1000 external servers that are sending the information will often request data every 2 minutes from the application.

The application is data critical in that it cannot afford to ever be down. I have the application part sorted for HA (High Availability), but I'm after suggestions for database HA. Oracle is not an option.

A friend suggested SQLlite rsynced between the application servers but that sounds dangerous to me. I looked into a MASTER-MASTER MySQL db setup, but it looks problematic and from users comments potentially unstable.

5 Answers
5

We've been using master<->master MySQL replication for 2+ years in production on critical databases without any issues. In our setup, databases are replicated over an unstable non-dedicated link. Configuration is easy and disaster recovery is without hassles. I recommend it.

Previously, we've been using a MySQL master->slave replication over a dedicated link with heartbeat for failing over, which is a viable option - but both machines have to be behind the same router.

Master<->master replication can be a bitch if there's any data discrepancy between the two instances. In our case, we've been using master-master for a number of web applications using silo'ed servers (two sets of servers, with MySQL, Apache, and Squid, each), and in our case we've got a sessions table which gets heavy write traffic which can result in write collisions (two sets of data being inserted into the same table with the same ID).

In cases like this, you need some heavy-duty logic in your application layer to make sure writes are properly divided so you never have a write collision. Not horrible, but a replication error can't be easily recovered from without a system administrator's intervention. In that case Master<->Master can actually result in lower availability.

Not to discourage you as it certainly works well once you get the kinks worked out, however, there are some annoying pitfalls.