How can I centralise MySQL data between 3 or more geographically separate servers?

Question

To explain the background to the question:

We have a home-grown PHP application (for running online language-learning courses) running on a Linux server and using MySQL on localhost for saving user data (e.g. results of tests taken, marks of submitted work, time spent on different pages in the courses, etc).

As we have students from different geographic locations we currently have 3 virtual servers hosted close to those locations (Spain, UK and Hong Kong) and users are added to the server closest to them (they access via different URLs, e.g. europe.domain.com, uk.domain.com and asia.domain.com). This works but is an administrative nightmare as we have to remember which server a particular user is on, and users can only connect to one server. We would like to somehow centralise the information so that all users are visible on any of the servers and users could connect to any of the 3 servers.

The question is, what method should we use to implement this. It must be an issue that that lots of people have encountered but I haven't found anything conclusive after a fair bit of Googling around. The closest I have seen to solutions are:

• something like master-master replication, but I have read so many posts suggesting that this is not a good idea as things like auto_increment fields can break.

• circular replication, this sounded perfect but to quote from O'Reilly's High Performance MySQL, "In general, rings are brittle and best avoided"

We're not against rewriting code in the application to make it work with whatever solution is required but I am not sure if replication is the correct thing to use.

Thanks,

Andy

P.S. I should add that we experimented with writes to a central database and then using reads from a local database but the response time between the different servers for writing was pretty bad and it's also important that written data is available immediately for reading so if replication is too slow this could cause out-of-date data to be returned.

Edit: I have been thinking about writing my own rudimentary replication script which would involve something like having each user given a server ID to say which is his "home server", e.g. users in asia would be marked as having the Hong Kong server as their own server. Then the replication scripts (which would be a PHP script set to run as a cron job reasonably frequently, e.g. every 15 minutes or so) would run independently on each of the servers in the system. They would go through the database and distribute any information about users with the "home server" set to the server that the script is running on to all of the other databases in the system. They would also need to suck new information which has been added to any of the other databases on the system where the "home server" flag is the server where the script is running. I would need to work out the details and build in the logic to deal with conflicts but I think it would be possible, however I wanted to make sure that there is not a correct solution for this already out there as it seems like it must be a problem that many people have already come across.

Answer 1

Circular replication, which is simply master/master/master in your case will work. Although some claim this can at times be slightly "brittle", so is any multi-server setup. A decent monitoring system, which should be part of your overall package anyway, will help to detect any problems early, so they can be resolved before becoming a serious problem.

The auto-increment "problem" is so easily circumvented that I can't believe people are still raising it as an issue. Simple start the counter on each server at a different number and use a step at least as large as the number of servers, leaving enough space for any possibly future additions.

Answer 2

For your application, it sounds like circular replication (of which multi-master is a special case) shouldn't be too big of an issue.

The auto_increment issue is easily addressed via auto_increment_increment and auto_increment_offset.

Monitor replication on all isntances with a relatively high frequency, and fix the sources of anything that causes replication to break or your data to drift. Maatkit's mk-table-checksum and mk-table-sync are good for identifying the drifting data. Gotta hit the binary logs and code to identify the sources... :)

Answer 3

We're not against rewriting code in the application to make it work with whatever solution is required but I am not sure if replication is the correct thing to use.

The issue you're describing really sounds like a case for on-demand user transfers.

It sounds as though it would be unacceptable to create a delay (unavoidable with geographically-disparate servers) for all users, so give your users an "export profile" or "transfer" option and write a function which pushes user data from one server to the next on demand.

Edit: ... and while you are at it, perhaps you could add a suffix to the usernames at each of your instances so users can indicate (by providing their usernames) which server their account is registered at when they request assistance.

Answer 4

I've also used the replication ring model, and had both successes and problems due to the brittle aspects. The biggest problem with a ring is that losing a single server will stop all data from flowing.

If you have real-time monitoring, you can keep the ring alive using CHANGE MASTER TO (you'll have to stop the slave first). That allows you to switch from master-master-master to master-master and back on the fly. This will only help in conjunction with an active failover mechanism that kicks in and directs users to an active site instead of the "local" (which is currently down).

I only had two sites, and had two servers at each site (instead of your single-server-per-site model). A potential solution in our case was to create a MySQL NDB cluster at each site that backed the per-site MySQL instances, and set up a replication ring among the MySQL instances. That would mean that losing a site (or communications between the sites) would not require any emergency changes, and everything would heal once the failed site was restored.

Answer 5

If you're willing to redesign your data access layer and data model, and you're willing to have the data stored somewhere besides your servers, you could try a distributed DB service such as http://aws.amazon.com/simpledb/

Answer 6

Lets think about a simple scenario where all the database servers (Server 1 , Server2 and server3 ) are geographically located at different sites linked via a VPN or some other network that may have link failures. We schedule that every 10 mins the master is changed.

For that to be possible, we must stop the slave and change master to that of a new server. Basically, it is a cyclic round robin rotation for masters' IP addresses on each database, but remember to stop the slave, change master, then start the slave.

The pseudo code script for each server is as follows:

Server 1

Script 1 executed for 10 mins, then chain to script 2:

• Stop slave
• Change master to that of server 2
• Start slave

Script 2 executed for 10 mins, then chain to script 1:

• Stop slave
• Change master to that of server 3
• Start slave

Server 2

Script 1 executed for 10 mins, then chain to script 2:

• Stop slave
• Change master to that of server 3
• Start slave

Script 2 executed for 10 mins, then chain to script 1:

• Stop slave
• Change master to that of server 1
• Start slave

Server 3

Script 1 executed for 10 mins, then chain to script 2:

• Stop slave
• Change master to that of server 1
• Start slave

Script 2 executed for 10 mins, then chain to script 1:

• Stop slave
• Change master to that of server 2
• Start slave

This is like a scheduled kind of synchronization between the masters.

Any comments are most welcomed.