Checked that MySQL backup log lately?

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.

Tags: , ,

5 Responses to “Checked that MySQL backup log lately?”

  1. Amen to checking backups. One of my favorite bad experiences in this area was on an Oracle 8 system years ago. Our ops team used to store the archive logs on a remote system so they could do point-in-time recovery. It turns out that system ran out of disk space and it seems the backup script did not check the return code of the transfer. Result: no backups for months. The problem was only revealed when a customer messed up and needed data restored. :(

  2. Shlomi Noach says:

    Good advice. An easy shortcut is to check the backup result’s size. It’s not 100% safe to verify this way, but when something goes wrong, there is usually an significant impact to the file size.

  3. kOoLiNuS says:

    What if I have:

    # mysqldump –version
    mysqldump Ver 10.13 Distrib 5.5.16, for Linux (x86_64)

    and

    [root@cerbero cron.daily]# rpm -qi mysql-server
    Name : mysql-server Relocations: (not relocatable)
    Version : 5.5.16 Vendor: Remi Collet

    and STILL have the issue ???

  4. puneet says:

    So when you’re doing this remotely does the server call the remote server’s instance of mysqldump or the local one ?

  5. ronald says:

    If you have upgraded to 5.5 did you run mysql_upgrade?