MySQL Forums
Forum List  »  Backup

Re: How can we ensure if mysql backups are consistent
Posted by: Rick James
Date: September 08, 2012 12:10PM

* Stop mysql, copy files, start mysql -- slow, but complete and consistent
* LVM -- downtime is brief (stop mysql, take snapshot, start mysql, then copy snapshot)
* Xtrabackup -- little downtime for InnoDB, but must lock for MyISAM.
* mysqldump -- must lock for MyISAM
* slave -- zero downtime for master; use slave as backup / dual master / host to backup from.

Plan A: Switch to all InnoDB. Then use Xtrabackup.

Plan B: Take a one-time hit to build a slave (need consistent dump to get it started), then use slave for backups.

Plan C: Use LVM, but be sure to dump the snapshot to some other media/location.

It is best to have the slave in another geographical location (think floods, earthquakes, etc), but it could even be on the same machine (use diff port and diff my.ini/my.cnf). Once you have backed up the slave to an "offsite" location, you are disaster-protected.

Options: ReplyQuote

Written By
Re: How can we ensure if mysql backups are consistent
September 08, 2012 12:10PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.