4

I'd like to multi-home a proprietary web-based application for a relatively small user base with modest dataset size. Scalability is not as much of a concern as extremely high availability. The system has extremely basic architecture: PHP running on a single Linux VPS tapping into a standard MySQL server.

I'm thinking to use multiple VPS's in geographically distinct datacenters such that if a user has problems connecting to one server location, they can try another (I have separate ideas on how to semi-automate the failover, and potentially georoute, but that's another issue). PHP application code and other files would be rsynced; however, I haven't yet resolved how to sync the databases... is it reasonable to use MySQL Cluster for this?

If one node goes down, the others must continue working. When a node comes back up, it should automatically get back in sync. Given that there would be WAN-type latency between datacenters, write speed cannot be dependent on intra-cluster connectivity. Data consistency is desirable but not the overriding priority. System simplicity is important--I can't afford to spend the next month figuring out the nuances of the database.

If MySQL Cluster is not appropriate for this, then what are some other alternatives to consider?

dlo
  • 451
  • 1
  • 4
  • 14
  • I'd like to leave this open a little longer before accepting an answer, to see what other replies might come in. BTW- if you could mention any real-world experience you have with a suggested approach, that would be greatly appreciated! – dlo Jun 10 '11 at 14:48

2 Answers2

3

A simpler solution, in my opinion, is a MySQL master-master replication configuration where both sites push queries to the other and execute them. A to-the-point howto can be found here:

http://www.howtoforge.com/mysql_master_master_replication

If you have an existing dataset, you can use mysqldump to export the database prior to performing these steps, so that it can be inserted on the slave before the configuration is done. Just be sure to get the master_log_position as you do the export so you know where to catch up from. I highly recommend building a dev environment to work on so that you can learn how it all works.

Kyle Smith
  • 9,563
  • 1
  • 30
  • 32
  • And for more than two servers this can be expanded to master/master/.../master, etc., with the last one linking back to the first. – John Gardeniers Jun 08 '11 at 22:24
  • sorry for the delay in accepting. I only now had a chance to try it out, and so far so good! – dlo Aug 07 '11 at 19:30
0

MySQL Cluster generally is not suitable for web applications.

It's kind of an exotic solution but check out Galera

I remember seeing somewhere that they said WAN replication is possible.

There is also Tungsten Enterprise.

Galera is synchronous replication, whereas Tungsten is asynchronous.

Cheers

HTTP500
  • 4,827
  • 4
  • 22
  • 31