Verifying a backup is one of the key ways to ensure that a backup was successful – in general, a backup is verified to be successful if the backup data is restored on a different MySQL server and, after that, MySQL functions as expected. To verify a backup, restore it first. To do that using mysqldump, execute the following command:
$ mysqldump -u root -p database_name < backup.sql
Replace database_name with your database name.
To restore a backup using MySQL Enterprise Backup, execute the following command (MySQL Enterprise Backup is covered in-depth in this blog):
$ mysqlbackup --defaults-file=/etc/mysql/my.cnf --datadir=/var/lib/mysql --backup-image=/home/backups/backup.mbi --backup-dir=/home/backups-tmp copy-back-and-apply-log
After you have restored a backup, verify it’s corruption free. Perform the following steps:
- Start MySQL;
- Test that each table is usable by running the following query:
SELECT CONCAT('CHECK TABLE ',table_name,';') FROM (SELECT CONCAT(table_schema,'.',table_name) table_name FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')) A;
This query will create a
CHECK TABLE query for every table with the InnoDB, MyISAM, CSV, and
ARCHIVE storage engines – you can put those queries in a file and execute them through a command line.
CHECK TABLE checks tables for errors and a sample
CHECK TABLE output looks like so:
The output of a CHECK TABLE query produces the following columns:
ClusterControl – Verify Backup Feature
The best way to ensure your backup healthy is by running a backup verification in an automated way. For this, ClusterControl has the “Verify Backup” feature available for your scheduled backups.
In the scheduled backup configuration, in the ClusterControl UI, just enable the “Verify Backup” button to use this feature:
You will need to specify a dedicated host (or VM) that is not part of the current cluster, as ClusterControl will use this host as a new node, install the software, configure it, and restore the backup to confirm that everything is working as expected.
Verifying a backup should be a crucial part of any backup routine – MySQL backups can be verified by restoring them, then checking the integrity of the restored data. When a MySQL backup is verified, you can make sure that the backup produces a copy of data that can be restored and actually used by MySQL.