Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

migrated from stackoverflow.com May 19 '13 at 9:07

This question came from our site for professional and enthusiast programmers.

    
Can you add any more detail about how large your database is in total, and how much it changes while you are on the road? It might help identify whether there are other solutions that will solve your underlying problem. –  Nathan Jolly May 19 '13 at 13:16
    
The database isn't all that large, a full backup is about 450meg and writes are small but fairly often when we are under full tilt. We are basically run an eCommerce site out of the trailer. –  Matt Winer May 22 '13 at 15:17

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
+1 very good point about "split brain" since MySQL replication will not detect this condition until one of the mismatched (or missing) (or extra) rows on the slave is referenced by, or causes a constraint violation in response to, an event replicated from the master. –  Michael - sqlbot May 19 '13 at 15:02
    
hmm good point about "split brain". The writes that the staff would have to perform in the office are simply flipping a job on and off to get the financial data. The issue I have, is our system only allows one job to be active at a time. So if the staff want to see details of a job they turn that job live (isActive = 1) basically. From there they aren't writing any other data. It's just reading invoices and financial data. Then as a backup, just in case. –  Matt Winer May 22 '13 at 15:24

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.

share|improve this answer
    
An NDB cluster is probably a terrible idea. –  Denis May 19 '13 at 9:50
    
@Denis, agreed, a cluster is a terrible idea. I meant master-master. –  Old Pro May 19 '13 at 10:04
    
Isn't master-master relying on NDB in MySQL? –  Denis May 19 '13 at 10:15
    
@Denis, no, master-master involves partitioning the keyspace so that writes to the slave do not conflict with writes to the master and then making each of two servers both master and replication slave to the other. It is asynchronous. One explanation of how to set it up can be found here –  Old Pro May 19 '13 at 10:27
    
The office staff wouldn't not be entering any data. Just changing a field in the DB that specifies which job number is Active. Beyond that they are only reading data. Eventually we'll update our system to allow for multiple jobs live. If I go Master-Master I cannot allow somebody in the office to change the live job and let that replicate to the trailer. If any customers are on the trailer when that replicates. I'm in trouble. –  Matt Winer May 22 '13 at 16:28

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.