Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

4 Answers

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.
share|improve this answer
add comment

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.

share|improve this answer
    
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 :( –  Julien Genestoux Mar 7 '10 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. –  Lukas Loesche Mar 7 '10 at 18:48
    
I ment "MySQL MyISAM and InnoDB" of course :-) –  Lukas Loesche Mar 7 '10 at 19:27
add comment

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

share|improve this answer
    
Have you read the question? We use replication... but this cannot obviously be used as a backup method! –  Julien Genestoux Mar 7 '10 at 17:36
add comment

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.

share|improve this answer
add comment

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.