Mysql – Does MySQLDump Verify the backup as restorable

mysqldumppowershell

I have written a PowerShell script to use MySQL dump to perform full backups of a specific database at a specific time.
I am wondering if I need to confirm the backup is restorable via my script or will MySQLDump fail if there is corruption in the database?

The command I am currently using:

.\mysqldump.exe --user=old --password=mate --log-error=$errorLog  --result-file=$backupfile  --databases $database

Best Answer

No - there is no way to check if the text file that is produced by the mysqldump utility will in fact restore correctly. Of course, the backup process can be automated (e.g. as per here). I'm not much of a Windows man, but search engines appear to return a number of scripts which you could use/adapt.

What you CAN do however is to run the CHECK table command on your tables of interest - you mightn't be so interested in slow/never changing reference tables (i.e. names of US states for example). Depending on your scripting ability, if the CHECK process throws an error, you could a) stop the backup and b) page a dba or invoke some other process. An excellent answer on how to automate this workflow is given here.

If you wish to use a "belt and braces" approach, you could use the mysqlcheck utility which:

mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user.

Also, see here, but be warned,

If you use the --databases or --all-databases option to process all tables in one or more databases, an invocation of mysqlcheck might take a long time.

Basically, it can't be put better than as expressed here:

It is always a good practice to test the whole solution backup/restore. So nothing is better than testing a restore, actually it should be a periodic procedure.

Like most things in life, dba work is a series of compromises! :-) You have to consider your RPO (Recovery Point Objective) and your RTO (Recovery Time Objective) (see here) and balance those with the time and expense spent in verifying backups. An agreement with the major stakeholders (clients, managers, dbas) on what is an acceptable level of risk vs. expense is what is required.

Remember though, that every extra 9 in your reliability will add an extra 0 to your budget!

"Yae cannae beat the laws o' physics, Jim!" (*)

(*) with apologies to Gene Roddenberry

Related Question