For the past few months I have been working on a multisite IT project, part of it involves keeping various databases synchronized between different sites. A single master database needs to be pushed out to all the remote clients and a local database on each of the remote machines needs to be backed up to the master.
I hadn't attempted anything as complicated as this before so it was a daunting task.
The only type of database replication I was aware of was the standard replication feature built into MySQL, I had tried to use this previously but had a lot of trouble as I found the different machines easily came out of sync and wouldn't recover without manual intervention. Despite the previous problems I set up replication on the master machine and configured each of the remote locations to replicate a copy of the main database, this took a bit of work to setup but one it was working it seemed to operate reliably. When I had set it up I could randomly turn on and off machines and they would start replicating from where they left off, that was until I moved the master to a different location, then they decided to stop working.
Because I couldn't have each of the remote sites copying there local databases to the master through replication I decided to use mysql dump on a batch job, I was originally going to create a script to dump the database and FTP it to the master and then have another script on the master which would import the backups as they arrived. Thankfully I discovered a brilliant little feature of mysql dump which saved the day.
Mysql dump normally outputs the dump to a file but you can pipe it to a mysql instance connected to another machine, this meant I could copy the database from the remote site to the master in one line. Once I had set this up it worked brilliantly; very reliable and very simple to use.
mysqldump --opt --user USER --password=PASSWORD DB | mysql --host=IP --user USER --password=PASSWORD -C DB
After the continuing problems with the built in replication feature I decided to turn off replication and use mysql dump to replicate the databases out to the remote sites.
This obviously isn't an ideal solution because as as the databases grow the amount of data needing to be transfered will increase, I believe it also locks the database as it dumps it meaning people can't use the system as this is happening. Despite these problems it works brilliantly for my needs.
Incase your wondering the remote sites connect to the master site using VPN connections so I don't need to worry about sending data over an un-secure network.