0

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)

3 Answers 3

1

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.

2
  • 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. Jul 18, 2011 at 13:56
  • You can do this with MySQL replication logs... but it's not pretty.
    – womble
    Jul 18, 2011 at 14:18
1

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.

0

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/

You must log in to answer this question.

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