MySQL Master/Slave. Actual use of Slave Machine

Question

We have a MySQL server running in a mobile production trailer. The trailer is out on the road for weeks at a time. We have cell internet and wifi access is nearly all of our locations. Our office staff (bookkeeper, HR, etc) are at a brick and mortar office.

There are many times when the office staff need to access information but if the trailer is traveling or after hours then clearly we aren't online.

I was thinking/hoping we could replicate from the Trailer (Master) to the Office (Slave). This way the office web servers (duplicated of the trailer web server) would be able to access nearly up to date information. It wouldn't be 100% live but it's better then waiting a week or two for the trailer to come home.

The office would need to be able to write to the database.. BUT these changes CAN be overwritten during replication. All of the work they do is simply reading data. The few writes they would perform are inconsequential and would not matter if they were overwritten.

Am I going about this the right way? Can I simply point my office web server to the Slave MySQL server? Will they be able to write to the slave?

Thanks!

Answer 1

Can I simply point my office web server to the Slave MySQL server? Will they be able to write to the slave?

Short answer: Yes, but they probably shouldn't.

Depending on how your application works, it is possible (even likely) that writing to the slave will break replication. Even if it doesn't, over time it's likely that your databases will develop split brain - for example, someone could change some data on your slave that remains undetected for a long period of time.

If you are able to maintain two MySQL servers in your office, I would suggest the following:

• Master server #1 on your trailer
• Replicant server #2 in your office, acting as slave to #1
• "Working" server #3 in your office

Server #3 would be the server that your staff connect to. Set up a job - maybe one that runs each night - which takes a backup of server #2 and then restores it over the top of server #3. This way staff can work with fairly recent data without the risk of affecting replication.

You could even make server #3 a slave to server #2 if it looked like that might work, though I suspect this might cause more problems than it solves.

Public cell and wifi networks are unlikely to be as secure as you like - I'm assuming that you will secure the connection to your office via VPN. If not, MySQL can secure communications via SSL and you may want to consider that.

Answer 2

In general, you can safely read from a slave but you cannot safely write to a slave. If you insert data into a table in the slave, the master data will not overwrite it, it will fail to insert due to a duplicate key.

However, with MySQL you can setup a master-master (a.k.a dual master) configuration that will allow both the Trailer and the Office read/write access to the local database. The big caveat there is that now you can have replication failures in either direction and replication failures are both surprisingly easy to generate yet hard to recover from. You will need a professional DBA or Systems Administrator to set it up and to maintain it when it inevitably breaks.

In practice what I've done in this kind of situation is setup up periodic database dumps from the master and use rsync to keep that dump in sync with dumps stored on any read/reporting databases and then periodically replace the reporting databases with the dump.