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.

Sometimes if a database gets corrupted, a replicated database could also replicate that same corruption.

What is the best way to replicate/backup a database and prevent something like this from happening?

(specifically for MySQL)

share|improve this question
add comment

3 Answers

If the "corruption" is at a level where it will be replicated, then the only solution is frequent backups and a good means of detecting such corruption so you can quickly recover. You'd need to define how to detect the corruption based on your specific application, because from the database's point of view, everything is as it should be.

share|improve this answer
    
yes. i understand what you mean. so is there a way to recover something right before the corruption happened? like how you can do an SVN diff on the HEAD vs the previous version and know what the changes are. Also, if that is not possible, how can one do frequent backups of a database that is getting larger and larger? if you want 5 minute backups, sometimes it might take 1 hour to do a whole backup if you have a huge data set. so i it would make sense to backup the CHANGES (like version control) instead of taking a snapshot of the whole shebang. –  lamp_scaler Jul 18 '11 at 13:56
    
You can do this with MySQL replication logs... but it's not pretty. –  womble Jul 18 '11 at 14:18
add comment

This covers everything you want in depth: http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html

Basically, what you probably want is a slave that you can do daily and/or weekly full backups on, then do incremental backups by saving the binary logs since the full backup. You can re-play the binary logs to any command you want.

share|improve this answer
add comment

and to add more information about what opsguy said, there is a few scripts known as to replay binary logs and so, you can check maatkit documentation: http://www.maatkit.org/

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.