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.