Running a MySQL backup and ensuring it completed successfully and backup files exist is not enough. In my B&R Quiz from Checked your MySQL recovery process recently? one important step is “Do you review your backup logs EVERY SINGLE day or have tested backup monitoring in place?”
This is what I found when reviewing a backup log for a client today.
mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES
The backup script was completing, backup files were in place (and are listed in the log file) however these errors were occurring.
Further investigation was less then one minute of actual work. This server runs multiple MySQL instances and recently one instance was upgraded from MySQL 5.1 to MySQL 5.5 however the call to mysqldump was not. This error was the result of running 5.1 mysqldump against a 5.5 database. Change the hard coded path and the error message went away.
The greater problem is teaching people to understand the importance of backups.