My organisation has been looking into how to spread our servers around geographically while keeping backups very up to date, and ideally spreading the load.
The initial thing I have in mind is Rails on MySQL. The write rate isn't too high (articles/comments being left at less than 1 per minute, though some have large media attachments).
So,
- does MySQL replication work well across wide area networks?
- Does the connection (or a slave server) going down mean that manual intervention is required (once the two servers can talk to each other again) or is recovery automatic?
- If the master disappears, what is required to turn a slave into a master? Are there standard scripts/tools to help manage that?
- Any other gotchas etc?
Best Answer
We use replication across datacenters in several European countries (so they aren't across the world from each other, but they are certainly not local) and it works without any problem.
Replication will automatically restart if possible. If there is a problem with a query (e.g. a database is present on the master and not the slave, and a query uses it), then it will require manual correction by default (but you can set it to ignore such errors). If the databases are exact mirrors, then you should never need to manually restart replication.
If you have two servers and the master disappears, then to turn the slave into the 'master', just stop replication and alter your code (to write to the new 'master'). If you have three or more servers and the master disappears, then stop replication on the slaves, change them to use the new master, and start again. If they aren't exactly in sync (depends how much data is being transferred, how busy the servers are, how good the network connection is, etc), then you might have to do more work than that. The replication section of the MySQL documentation covers this in more detail.
I would suggest that you ensure that you are replicating over SSL (i.e. set the replication user to require a SSL connection).