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!
Best Answer
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:
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.