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:

  1. Start MySQL;
  2. 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:

mysql> CHECK TABLE demo_table;
+-----------------+-------+----------+----------+
| Table           | Op    | Msg_type | Msg_text |
+-----------------+-------+----------+----------+
| demo.demo_table | check | status   | OK |
+-----------------+-------+----------+----------+
1 row in set (0.00 sec)

The output of a CHECK TABLE query produces the following columns:

  1. The Table column contains the name of the table;
  2. The value of the Op column is always check;
  3. The Msg_type column contains the type of the message which can be either status, error, info, note, or warning;
  4. The Msg_text column contains an informational message.

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.

Summary

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.

Contributor
Comments to: MySQL Backup Verification

Your email address will not be published. Required fields are marked *

Attach images - Only PNG, JPG, JPEG and GIF are supported.