I develop websites for several small clients and I would love to be able to keep my local databases up to date with each client's production servers. (I'm thinking nightly updates) Many of their databases are in the hundreds of megabytes, so I feel like creating and transferring complete dumps every night is excessive.

Here are the harebrained ideas I have come up with so far:

Create a dump on the server and rsync with the previous night's dump
on my local machine. That should only transfer the changed bits of
the file right?

Create a dump on the server, and locally diff it from last night's
dump. Transfer only that diff. Maybe it could also send to md5 of the
original dump so I could be sure I was applying the diff to the same
base file.

Getting ssh access to (most) of these servers is possible, but requires getting my clients to call their hosting providers with that rather technical request, which is something I would rather not have to ask them to do. Bonus points if you can suggest a solution that I could implement via ftp/php.

This question (in several forms) seems pretty common. Most of the need, and therefore most of the tools, seems to focus around keeping the schema up to date rather than the data. Also, most of the tools seem to be commercial and GUI.

So far the best looking option seems to be pt-table-sync from the Percona Toolkit, although it looks like it might be a pain to setup on OSX.

I am downloading Navicat to test right now. I runs on OSX but is a commercial GUI program.

1 Answer
1

I think the most efficient way to set this up is to use rsync to backup the files is by using rsync to run each night , doing a incremental backup by syncing yesterday's back to todays backup then running the rsync for that day. In terms of backing-up the different websites i would look at having the sites as slaves which back up to your server (master) using the replicator function this will allow you to keep all the databases upto date and backed up