7

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.

4

The best way to verify a backup is to restore database from it.

Another reason to do so is reduced restore time.

4

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 !!!

  • The maximum length of the result of the GROUP_CONCAT() function is 1024 characters. If you have a lot of tables, it will not get all tables and most likely the last table name printed will be cut off. You want to SET SESSION group_concat_max_len = 10000; prior to the command. – jnovack Jun 11 '15 at 12:19
  • @jnovavk thank you for catching that. I learned about group_concat_max_len afterwards and put that in my later posts. See dba.stackexchange.com/…. I just changed 10000 to 1048576. Again, thank you for your eagle eye. – RolandoMySQLDBA Jun 11 '15 at 16:17
1

An approach I follow for normal mysqldump consistency

Write a shell script in below mentioned flow and save it as "mysqlbackup.sh"

  • On Master

!# /usr/bin/bash

  • Spool the database names in FOR loop and create .sqls of their own like db1.sql

  • mysqldump -uroot -pPASSWORD --single-transaction "db1" > /backup/mysql/TODAY_DATE/db1.sql

  • cd /backup/mysql/

  • tar -czvf TODAY_DATE.tar.gz TODAY_DATE # Tar Gun Zip directory

  • gzip -t /backup/mysql/TODAY_DATE.tar.gz # For gzip integrity check

  • scp /backup/mysql/TODAY_DATE.tar.gz file to another server # Inorder to cover server level failure as a minimal point of failure or archive in SAN or multiplex depending upon your levels of failure may be Data center level, Network Level, etc.,

  • Purge and Rotate old backups in a retention of 7 Days (Depending upon your need to restore from local server)

crontab -l

00 3 * * * /usr/scripts/mysqlbackup.sh 2> /usr/scripts/log_mysqlbackup.date +%Y_%m_%d_M.err

Monitor your nagios or netcool to point to the file /usr/scripts/log_mysqlbackup.date +%Y_%m_%d_M.err

err_file="/usr/scripts/log_mysqlbackup.`date +%Y_%m_%d_M`.err "

if [[ -s $err_file ]]
then
"Trigger alert -> Alerting team should call DB Team to fix it by rerunning backup"
fi
  • You may add other check criterias to trigger the alert like, the size of the backup file should be more than 3GB(depends upon standard backup size you get) and trigger alarm if it is lesser etc,. Parse these criterias in mysqlbackup.sh and incase of issues in sufficing your criteria then >> write the details in /usr/scripts/log_mysqlbackup.date +%Y_%m_%d_M.err. Since the monitoring tool triggers alert incase if the err file has size in it.

  • Since you have master-binlog-file and master-binlog-position recorded in sqldump, which will be helpful in case of PITR if you have later binary log files to apply.

Other level checks :

  • Once in a month you may try restore any random backup from all multiplexed devices (i.e., local server/ external server/ SAN/ tape).

This ensures your backup data consistency and can avoid situations invalid backup data at the time of urgent restore.

0

You should remember that a MySQL dump is basically a big SQL script textfile that creates the database(s) schema and inserts all data into it. So once mysqldump exits correctly, you can be pretty sure that everything went well (assuming of course that you're backing up the correct databases!)

I'm not aware of any way a mysqldump file could end up corrupted (unless there were filesystem issues, in which case any file could be mangled). MySQL is (rightfully) pretty squeamish about it; if there were some corrupted tables in the database, mysqld would crash and would not allow mysqldump to terminate, in order to protect database integrity.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.