How to monitor MySQL backups

It’s important to monitor the (database) backups after you set-it-up. In this post we will answer a few how to monitor MySQL Database backups solutions – monitoring methods.

We should see suggestions for:

• Monitoring Holland MySQL Backup
• Monitoring mysqldump backups
• Monitoring Xtrabackup using log
• Monitoring Backups using Nagios

Usually we can have our backup script capable of detecting the status of the backup and send an success / failure email accordingly. Alternatively the backup script can write the status to backup logs and the monitoring system should be capable of reading logs, determine the backup failure and alert.

Let’s consider one by one.

Monitoring MySQL Backup – Holland Framework

Recently we saw how to setup MySQL backup using holland and xtrabackup. So if you’re using Holland framework to configure and setup your backup, things get little easier for you! From version 1.0.7 we have the new variable, failed-backup-command, which comes handy to take an action upon backup failure detected by Holland framework itself.

We already have a configuration option to help us out on backup failure scenario:

    failed-backup-command = echo "The backup on `hostname` (`date '+%Y%m%d'`) has failed. Please review the backup logs." | mail -s "[ALERT] Backup has failed on `hostname`." email@domain.com

Instead of a command we can use a script in this command option.

We can ofcourse check the holland backup logs to view if the backup has failed. (If you’re using older Holland version and this feature sounds interesting, it’s time to upgrade.)

Monitoring backups taken by MySQLdump

Let’s consider we’re using mysqldump to take logical backup. The backup script can have an additional check-point for backup confirmation.

Check out the snippet as follows:

    # ... mysqldump command to take mysql-backup ...#
    # Confirming last line of mysqldump as 'dump completed'
    IsOK=`zcat full_dump.sql.gz | tail -n1 | grep "Dump completed" | wc -l`
    if [ $IsOK -eq 1 ]; then
        subject="SUCCESS: $DBSERVER Daily backup completed.";
        content="Success string 'Dump completed' found in the backup file full_dump.sql.gz. Backup is successful."
        email_list=$success_email;
        log "Backup is successful."
    else
        subject="FAILURE: $DBSERVER Daily backup failed.";
        content="Success string 'Dump completed' not found in the backup file full_dump.sql.gz. Backup appears to have been failed."
        email_list=$failure_email
        log "[ERROR] Backup failed." # Write in log, we can scan log for backup verification as well.
    fi;
    sendEmail # Use the send email function to alert for failure

(Functions in above code snippet are  taken from previous post. You might want to refer useful-code-snippets-for-your-shell-scripts.)

Monitoring MySQL Backups by Xtrabackup log

In case you’re using a script wrapper around xtrabackup, make sure you check the xtrabackup backup-log for possible errors to detect the failures. For a successful backup, xtrabackup writes “completed OK!” to the log, which should confirm the backup success. Though we should make sure to also verify the backup log to check for “ERROR” or “WARNING” getting logged in case of any failures. Xtrabackup may also fail due to a database directory / file permission issues for the backup user!

You may check for:

    grep "ERROR\|WARNING" xtrabackup.log # Should be 0
    grep "completed OK!" xtrabackup.log | wc -l # Should be >1

Monitoring MySQL Backups using Nagios:

If you’re using Nagios to monitor the MySQL database system you may choose to configure check_file_age plugin to look to check the backup file.

Define Nagios Service:

vi localhost.cfg (usually under /usr/local/nagios/etc/objects/)
define service{
    use                             active-service
    hostgroup_name                  mysql-servers
    service_description             MySQL User Connections
    check_command                   check_file_age!86400!86400!/backup/file-name
}

Define Nagios Command:

vi commands.cfg (usually under /usr/local/nagios/etc/objects/)
define command{
    command_name    check_file_age
    command_line    $USER1$/check_file_age -H $HOSTADDRESS$ -w $ARG1$ -c $ARG2$
}

I hope this answers few of the the backup monitoring needs.
Ofcourse, never to forget that even when you confirm that the backups are successful, restore tests doesn’t loose their importance.