2

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?

4 Answers 4

2

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.
1

I don't know if it's awesome, but we're quite happy with R1Soft's CDP for MySQL. You might want to take a look at it: http://wiki.r1soft.com/pages/viewpage.action?pageId=4460570

It will create backups to any local directory/mountpoint. So using Subcloud or s3fs you could directly backup to S3.

3
  • Hum, Thx for the link. I'd love to have more feedback. Using a solution for which I don't understand all the inside mechanism is something I'm not sure I want to do :( Mar 7, 2010 at 17:38
  • What CDP does is, it loads a Kernel Module which keeps track of all block level changes. However it has an understanding of Filesystems and using the MySQL-Addon even on MySQL MySQL and InnoDB Filecontent. So it is basically creating point in time snapshots, but with a deeper understanding of the content it is snapshotting. This allows you to do DB restores on the table level, which in our case has proven to be quite a valuable feature. I believe you can download a trial version. Since setup is relatively easy I would recommend trying that and see if it works for you. Mar 7, 2010 at 18:48
  • I ment "MySQL MyISAM and InnoDB" of course :-) Mar 7, 2010 at 19:27
0

What about MySQL replication? http://dev.mysql.com/doc/refman/5.0/en/replication.html

1
  • Have you read the question? We use replication... but this cannot obviously be used as a backup method! Mar 7, 2010 at 17:36
0

What storage engine your application use?

If it is InnoDB,you could use xtrabackup for full backup every day and incremental backup every few hours.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .