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.<\/p>\n\n\n\n
We should see suggestions for:<\/p>\n\n\n\n
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.<\/p>\n\n\n\n\n\n\n\n
Let’s consider one by one.<\/p>\n\n\n\n
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.<\/p> <\/p>\n\n\n\n
We already have a configuration option to help us out on backup failure scenario:<\/p>\n\n\n\n
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\n<\/pre>\n\n\n\nInstead of a command we can use a script in this command option.<\/p>\n\n\n\n
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.)<\/p>\n\n\n\n
Monitoring backups taken by MySQLdump<\/b><\/h2>\n\n\n\n
Let’s consider we’re using mysqldump to take logical backup. The backup script can have an additional check-point for backup confirmation.<\/p> <\/p>\n\n\n\n
Check out the snippet as follows:<\/p>\n\n\n\n
# ... mysqldump command to take mysql-backup ...#\n # Confirming last line of mysqldump as 'dump completed'\n IsOK=`zcat full_dump.sql.gz | tail -n1 | grep \"Dump completed\" | wc -l`\n if [ $IsOK -eq 1 ]; then\n subject=\"SUCCESS: $DBSERVER Daily backup completed.\";\n content=\"Success string 'Dump completed' found in the backup file full_dump.sql.gz. Backup is successful.\"\n email_list=$success_email;\n log \"Backup is successful.\"\n else\n subject=\"FAILURE: $DBSERVER Daily backup failed.\";\n content=\"Success string 'Dump completed' not found in the backup file full_dump.sql.gz. Backup appears to have been failed.\"\n email_list=$failure_email\n log \"[ERROR] Backup failed.\" # Write in log, we can scan log for backup verification as well.\n fi;\n sendEmail # Use the send email function to alert for failure\n<\/pre>\n\n\n\n(Functions in above code snippet are taken from previous post. You might want to refer useful-code-snippets-for-your-shell-scripts<\/a>.)\n<\/pre>\n\n\n\nMonitoring MySQL Backups by Xtrabackup <\/b>log<\/strong><\/h2> 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<\/a> for the backup user!<\/p>\n\n\n\n
You may check for:<\/p>\n\n\n\n
grep \"ERROR\\|WARNING\" xtrabackup.log # Should be 0\n grep \"completed OK!\" xtrabackup.log | wc -l # Should be >1\n<\/pre>\n\n\n\nMonitoring MySQL Backups using Nagios:<\/b><\/h2> 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. <\/p>\n\n\n\n
Define Nagios Service:
<\/p>\n\n\n\nvi localhost.cfg (usually under \/usr\/local\/nagios\/etc\/objects\/)\ndefine service{\n use active-service\n hostgroup_name mysql-servers\n service_description MySQL User Connections\n check_command check_file_age!86400!86400!\/backup\/file-name\n}\n<\/pre>\n\n\n\nDefine Nagios Command:
<\/p>\n\n\n\nvi commands.cfg (usually under \/usr\/local\/nagios\/etc\/objects\/)\ndefine command{\n command_name check_file_age\n command_line $USER1$\/check_file_age -H $HOSTADDRESS$ -w $ARG1$ -c $ARG2$\n}\n<\/pre>\n\n\n\nI 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.<\/p>\n","protected":false},"excerpt":{"rendered":"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…\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,870,377],"tags":[881,879,878,876,877,882,875,880],"class_list":["post-2255","post","type-post","status-publish","format-standard","category-mysql","category-mysql-tools","category-mysql-articles","tag-backup-monitor-nagios","tag-monitor-mysqldump","tag-monitor-xtrabackup","tag-monitoring-backups","tag-mysql-backups","tag-mysql-holland-backup","tag-mysql-monitor-backup","tag-mysql-monitoring-using-nagios"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t