Home / server / Questions / 81208
Accepted
ae.
Asked: 2009-11-04 15:54:49 +0800 CST

Mysql high availability

  • 772

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.

Any suggestions?

Has to run on linux, has to be open source.

mysql

5 Answers

  1. Best Answer

    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.

    • 4

  2. MySQL Cluster maybe?

    Anyway, yeah, forget SQLite. It's the wrong solution for your problem.

    • 1

  3. Mysql Cluster seems to be a good solution for You, however is dependent on database size, because for now all data must fit into memory when using Mysql Cluster

    • 0

  4. MySQL Cluster would seem the obvious choice as it can support 5 9s HA but it is not well suited to web applications.

    Checkout Finding your MySQL High-Availability solution – The questions:

    http://planet.mysql.com/entry/?id=21763

    Given your (write) requirements I think I would look into Heartbeat/SAN if I were you.

    Cheers

    • 0

  5. 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.

    • 0