I have a master database server that has one slave server replicating (on the other side of town). I have a remote office (different state) that only has T1 connectivity to the Master DB. So when we get lots going on they complain of slowness, and it is pretty bad. So we are thinking about putting another slave server on their local network, and have that serve the SELECTS and the modifying of data will still have to go to the Master.
Anyway, does anyone have any experience doing something like this? Or is there a better way to get data to the remote location for this purpose (cluster, or something)? How about if the data is changed on the master but has not propagated to the slave, will the user see old data?
Any information would be very helpful. I’ve read all the manuals on replication, but I was hoping for a real world point of view.
Using v5.0.42. We have 2 masters in a replication ring separated by 180 miles and connected by a VPN tunnel. Master-1 (M1) is a slave to Master-2 (M2) and M2 is a slave to M1. The lag is usually sub-second. M1 averages 350 qps and Master-2 averages 240 qps with each growing all the time. Clients attached to M2 use M2 exclusively. Clients on M1 are directed to M2 for queries that generate reports and other non-time-critical work but otherwise do everything else on M1. All MySQL statements flow through the replication ring providing us an extra layer of recovery in addition to our backups and allowing us to sleep, knowing that we have (almost) hot swapability for our clients.
This sytem has been running for a year now and we’ve never experienced a network failure (but that wouldn’t be catastrophic) and replication has broken several times because of application problems at either end.
Thanks for the info, and if you don’t mind I have another question along this same line. How do you handle INSERT, UPDATE, DELETE? Do they all have to do this at M1? What if someone gets an UPDATE of some kind to M2, does it get overwritten?
Ok, I read through those system variables, and that seems to makes sense. So essentially all the ID’s for updates that happen on M1 will be even numbers and M2 will be odd numbers. (Obviously depending on your increment values).
So one more question. If the link goes down for some time say half a day. Let say user1 makes an update on M1. Then user2 who uses M2 selects that range of data that user1 changed earlier. User2 is going to see “old” data? Then when the link comes back and M1/M2 sync the data would be “good”? Right?