3.2.2 Verifying a Backup

To verify the backup, restore the backup data on a different server and run the MySQL daemon (mysqld) on the new data directory. Then you can execute SHOW statements to verify the database and table structure, and execute queries to verify the number of rows, latest updates, and so on.

This is the same general technique to use when you intend to put the backup data to some other use. For example, you might set up a replication slave by making a backup of the master server, or turn a backup into a new MySQL instance for running report queries.

Note

Always do verification against restored data, rather than running mysqld with datadir pointing to the backup directory. The SQL statements you use to verify the data change the underlying logical sequence number, which would interfere with using the backup directory for subsequent incremental backups.

If you did the backup with the backup-and-apply-log option as in the previous example, the backup data is fully consistent and ready to verify. If you only ran the first stage by using the backup option, run mysqlbackup a second time with the apply-log option before doing this verification. (Typically, you run this second phase on the other server after transferring the backup data there, to minimize the load on the original database server.)

See Chapter 5, Recovering or Restoring a Database for the procedure to restore the database files on a different server.

Running the mysqld daemon on the restored data requires a valid configuration file, which you specify with the --defaults-file option of the mysqld command. You can reuse most of the settings from the original my.cnf file, combined with the backup-my.cnf file in the backup directory, which contains only the small subset of parameters required by mysqlbackup. Create a new configuration file by concatenating those two files into a new one, and use that configuration file on the server where you do the verification. Edit the resulting file to make sure the datadir parameter points to the right location on the verification server. directory. Edit the values for port, socket, and so on if you need to use different connection settings on the verification server.