Mysql – Verify thesqldump backup is corruption free

backupinnodbMySQLmysqldumprestore

We've got a cron task backing up our Database using mysqldump – my main concern is corruption, short of manually importing and checking the backups each time whats the best way to check / verify a backup is corruption free ?

Background: We are running mysql 5.5.* and InnoDB. We are running mysqldump straight from the server. We are not currently using master / slave or master / master but could change if it would help. The DB is small currently <1mb, it will be <50mb for the foreseeable future, so scale dosnt really need to be taken into account in this instance.

Best Answer

There are two things you want to consider

BACKUP PROCESS

There really is no substitute of doing a restore. This is just a replay of a mysqldump. If you need to do PITR, you may need to run mysqlbinlog against applicable binlogs and play the events up to the date and time needed.

You should do this periodically in case the disk where the mysqldump is located goes bad or if the mysqldump contains a sequence of characters in a BLOB making it impossible to restore.

BACKUP INTEGRITY

You should be able to start mysqld without a problem. Once you have mysqld running, you need to test each table's usability. You can do that by running this query:

SELECT CONCAT('CHECK TABLE ',dbtb,';') FROM
(SELECT CONCAT(table_schema,'.',table_name) dbtb FROM
information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql')) A;

This will create a CHECK TABLE command for every MyISAM, InnoDB, ARCHIVE and CSV table to check their integrity. You can output this to a script and execute the script. The CHECK TABLE command can also allow you to check all the tables as a single command. You can change the query to collect the table names as a comma-separated list and prepend:

SELECT CONCAT('CHECK TABLE ',dbtblist,';') FROM
(SELECT GROUP_CONCAT(table_schema,'.',table_name) dbtblist FROM
information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql')) A;

WARNING: The GROUP_CONCAT() function has a default limit of 1024 characters. If you have a modest number of tables, you will not list all the tables, and the last table listed will be cut off, causing an error. You can preface your command with SET SESSION group_concat_max_len = 10000; to increase the length of the return.

Here is a sample from MySQL 5.6.16 (Windows) on my laptop:

mysql> SELECT CONCAT('CHECK TABLE ',dbtblist,';') FROM
    -> (SELECT GROUP_CONCAT(table_schema,'.',table_name) dbtblist FROM
    -> information_schema.tables WHERE table_schema NOT IN
    -> ('information_schema','performance_schema','mysql')) A;
+----------------------------------------------------------------------------+
| CONCAT('CHECK TABLE ',dbtblist,';')                                        |
+----------------------------------------------------------------------------+
| CHECK TABLE ayman.articles,ayman.topics,test.nuoji,test.prod,test.prodcat; |
+----------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

Then, store that output to a variable and execute from the command line. Note that we have temporarily increased the group_concat_max_len before running the command.

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SET SESSION group_concat_max_len = 1048576;"
SQL="${SQL} SELECT CONCAT('CHECK TABLE ',dbtblist,';') FROM"
SQL="${SQL} (SELECT GROUP_CONCAT(table_schema,'.',table_name) dbtblist FROM"
SQL="${SQL} information_schema.tables WHERE table_schema NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql')) A"
mysql ${MYSQL_CONN} -ANe"${SQL}" | mysql ${MYSQL_CONN}

Give it a Try !!!

Related Question