How to write a shell script to check the mysql database backup status.

Question

I have mysql production databases running in main server(Linux) and from this server every night the backup is taken into local server. Now i want to find a shell script to check if all the databases backup is up to date or not(for an example: i have twenty database in the main server so i want to know all of them have been backed up completely or not) and compare the each database's current size with their previous size, in a sense i want to make sure with through this script that my backup is perfect and if not i will have a track of that occurrence.

Please help me to get a solution of this problem.

Answer 1

This would indeed be nice to be able to do it in an automatic way. Unfortunately, to be 100% sure your backups are fine, you need human intervention. After taking the backup, you must be able to check if you can import it safely. Of course, you can not always do it. You can check your backups for example every 2-4 days.

On the other hand, you can always check if the size of the new backup is significantly smaller than the previous one (considering that the database always increases in size). This could be an indication that your backup might not have been taken correctly or is corrupted.