Thesql backup validation

MySQLmysql-5.5mysqldump

Need help regarding backup validations for MySQL, what is the best way / easiest way to validate Mysql backups.

we are using mysqldump backup for taking backup's and tar the backup file at the end, could not find any solution on my research.

Any help from this community, Thanks in advance.

Best Answer

The only way you can validate a backup is to restore that backup; plain and simple. Spin up a VM, install the same version of MySQL from which you took the backup, restore the backup and run some simple tests. If you make this new VM a slave of the original master, configure replication and use pt-table-checksum to validate all data is the same on the slave. At the very least you can use CHECKSUM TABLE <table>; on every table in every database to validate that MySQL is capable of reading every row.

Related Solutions

MySQL Backup – Quick Methods Using Mysqldump

mysqldump should be just fine. Since you have only 3GB of data, I expect the mysqldump to be decently fast (1-2 min as a single output). However, since your database is comprised of 3 MyISAM tables expect all three to be locked during the backup. Please keep mind that since tables are locked one at a time, you will not have point-in-time consistency across all tables in the database.

I have some scripts I wrote and still use to perform

parallel backups of separate databases
parallel backups of separate tables

If you are concerned with consistent data, you may need to implement something from Option 4 of the link I provided which executes "FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400);" in a separate session to lock all writes to all tables, perform the mysqldump(s), and then kill that session holding the tables locks.

If you are afraid to try executing this, you should look into using MySQL Replication. A slave server with the same data can be used to perform mysqldumps without imposing anything disk I/O or overall server load on the master database. You basically do these things on the DB Slave:

STOP SLAVE;
mysqldump
START SLAVE;

Give it a Try !!!

MySQL Backup – How to Exclude Specific Tables

You can backup databases and tables using the mysqldump command. There are parameters for excluding certain tables.

mysqldump -u username -ppasswword -h servername --ignore-table=dbname.tabletoskip dbname

If you need to get fast backups of MyISAM or ARCHIVE tables, you can also use mysqlhotcopy. That has more restrictions though.

Best Answer

Related Solutions

MySQL Backup – Quick Methods Using Mysqldump

MySQL Backup – How to Exclude Specific Tables

Related Question