Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
A few questions: Do you need to write to the database from all sites? Or is a read-only replica sufficient? Would a read-only replica be sufficient for 90% of cases? If you need to write to the databases, what sort of delay is acceptable? –  Stefan Lasiewski Sep 14 '10 at 21:58
    
Database would need to be written to from all sites, although majority of cases would be just from the one site. For example, if a student connects to the server in Asia then they should be able to read and write to the Asia database without any delay. However, they might travel from Asia to Europe and when abroad if they connect to the system they will be connecting to the European server, which again they will need to be able to read and write so without delay. However, a delay in replicating information between these servers would be acceptable. See the edit to my post for one idea I had. –  Andy Castles Sep 15 '10 at 6:00
    
I plan to reply to your question but want to give it the proper attention. I am unusually busy this week and have yet to find the opportunity. I should have something together by next week. –  Warner Sep 16 '10 at 6:05
    
Thanks, I look forward to your input when you get a chance. –  Andy Castles Sep 16 '10 at 15:18
add comment

6 Answers

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.

share|improve this answer
    
I make the claim that it's brittle and try to avoid it even though I use it sometimes. Once you have rebuilt enough large MySQL replication servers, you get really tired of it. Do you have better success personally? –  Warner Sep 15 '10 at 14:27
    
@Warner, I've never had to deal with what I would consider to be really big installations but have had excellent success with a 3 site master/master/master installation, with each site in a different state and connected by distinctly unreliable connections. The only time it broke I used the fastest option, which for me was to wipe the broken link and recreate it. That won't be viable for everyone of course. –  John Gardeniers Sep 15 '10 at 21:57
    
@Warner, I appreciate your input as you seem to have a lot of experience working with high availability systems. In your experience, what would you use as an alternative to master/master/master replication in this situation? Thanks. –  Andy Castles Sep 16 '10 at 2:57
    
thanks for letting me know that you've used master/master/master and that it can be made to work without too many problems. I'll investigate it further as it seems like it could be a possible solution. I hope that Warner can give me some insight into some alternative so that I can compare two different angles and work out which would be best. –  Andy Castles Sep 16 '10 at 3:00
add comment

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... :)

share|improve this answer
add comment

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.

share|improve this answer
    
Thanks for the suggestion. I had been thinking about something like this but an automatic system, i.e. a cron job that periodically pushes data between servers, and does it for all users. The reason I haven't done this yet is it sounds like I'm just re-inventing replication, and I'm sure that MySQL could do it much more efficiently than I could expect to do with a PHP scripted cron job. It seems like it must be a common problem I wanted to find out if there is a "correct" way to solve the problem before I invent my own solution. –  Andy Castles Sep 15 '10 at 5:31
add comment

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.

share|improve this answer
add comment

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/

share|improve this answer
    
Thanks for the suggestion. I don't have any experience with distributed DB services like this yet so I'm not sure how much work is involved in implementing it. However at the moment our application's data access layer isn't too well defined so I'm sure it would be a very large job adapting it to work with this, so even to try it out wouldn't be feasible at the moment. Thanks again for the suggestion though. –  Andy Castles Nov 2 '10 at 15:13
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.