1

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 
1
  • --single-transaction. Also look a binary logs for PITR. Practicing a restore is the true measure of reliability testing. Also ensuring that utf8mb4 table contents are stored correctly. for a while mysqldump defaulted to utf8{mb3} and corrupted backups.
    – danblack
    Apr 8, 2020 at 2:14

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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