Some Tips on Database Backup and Restore

  • Verifying the restore-able ability is very important.
  • Checksum the data integrity of master and its replica is also necessary.

For the mysql backup made by xtrabackup, the only way for testing  the  capability of restore is truly performing restore operation once:  

1. download this backup if you upload it somewhere; 
2. apply log; 
3. change master to the original master if you use a replica for backup.

Why testing ?

Xtrabackup cannot gain our fully trust for several reasons. 

It is very likely to get a invalid backup when there is ddl operation or some kind of huge transaction if you use an relative old version of xtrabackup, say 2.0.6.

We use xtrabackup stream feature therefore maybe the backup is already corrupted when uploading the backup server.  The backup may fail at prepare stage and shows log sequence number is incorrect.

If you enable parallel replication feature in mysql 5.6, you may encounter one xtrabackup bug for getting the incorrect coordinate position of master.  We can trigger this bug very easily. The root cause for this is when enabling the parallel replication, innobackupex cannot get the correct coordinate if it does not stop slave sql thread before flushing table.  The fix is simple: stop sql thread before flushing table and start it again after it.

For large scale mysql servers, you may want to learn more about  facebook team's work which optimizes the mysqldump up to 10x speed.