Mysql – Full MySQL BAckup strategy

amazon s3backupMySQL

Here is our MySQL settings :

3 MySQL servers in a "replication-ring" : they're all Master and S1 is slave to S2, which is slave to S3 which is slave to S4.

Up until now we would do snapshots of the machines, but that's not something we can/want to do anymore. We're trying t get a full-proof backup of the databases to S3.

My idea was that we need to make mysqldump every few hours and then upload that to S3. The problem with that is that a dump is pretty expensive when you have a more than a few hundred MBs of data (we're at 8GB).

Saving the binary logs is an option, but then the restore may take days, as we need to apply all the binary logs :/

Any awesome solution for us?

Best Answer

You could actually use replication for this. Two options comes to mind.

  1. Use MySQL "style" Time delayed replication (i.e. insert a fourth machine and let it do a rolling recovery).
  2. You could also add a fourth machine as hot standby, take it out daily and then either back it up normally or snapshot it (fx. using ZSF on Solaris or LVM on Linux). Finally put it back in replication and let it catch up with the main servers.
Related Topic