Monitoring Your MySQL Backup

In California we are always thinking about backups.   Living near an earthquake fault line makes this necessary.  For me, it is the Hayward Fault (it runs from goal post to goal post  in University of Californa Berkeley stadium).  We are strongly advised to have backup systems for water, food, and medical emergencies.  It’s necessary to monitor your food and water emergency supplies so if the worst happens, you don’t have spoiled food or water as your backup (who knew water expires?) .   Plan for the worst, hope for the best, but keep an eye on those supplies and replenish them when necessary!  And most of all, make sure your good intentions end up as actual physical supplies in the garage!

Backups are also an incredibly critical part of the enterprise  environment.  It’s all about being able to successfully restore your database when needed.  There are many ways to backup your database, but are you monitoring your MySQL backups to make sure they are going to be there when you need them?  In this post, we’ll cover the new features in MySQL Enterprise Monitor (MEM) 2.3.5  that help monitor backups.

In the last post  I covered some of the new features in MySQL Enterprise Backup (MEB) that allow you to write single file backups, stream these backups  to  remote servers or other devices, stream to media management software like Oracle Secure Backup, and take advantage of tape encryption capabilities (Steps 1-4 from the previous post ) . This is the final post  in this series which describes the new Backup Advisor in MEM and the underlying mysql tables (Step 5). 

MySQL Enterprise Monitor (MEM) is the monitoring software supplied with the Enterprise Edition subscription of MySQL  and if you are interested in trying it, you can download it  for a trial at https:edelivery.oracle.com.  I find MEM very helpful because it provides  proactive monitoring for your MySQL databases.  This allows you to increase your productivity because you automate monitoring and help speed up diagnosis of potential issues. 

MEB is a backup tool included with an enterprise subscription from Oracle/MySQL, also available for trial from http://edelivery.oracle.com.  MEB was previously known as InnoDB Hot Backup, and provides hot, non-blocking backups for InnoDB tables, and “warm” backups for MyISAM tables. 

Monitor Your Backup
MEM 2.3.5  (and above) has a new Backup Advisor that can be used to monitor backups (note, MEM 2.3.6 was released in early September).  The Backup Advisor alerts you to backup success or failure, excessive backup lock time, backups that are too old, and failure to use incremental backups.  Here are some screen shots.  The first is  from the Details and Advanced Tabs for the “Backup Succeeded” rule  in MEM:

This rule will let you know how long the backup took to complete and how long locks were held. 

Full backups that are older than a threshold number of days (default is 7) are reported since out of date backups will only cause delays or problems if you ever need to restore from them:

You will also see if any excessive lock time in your backup.  :


Backups are always a balancing act between performance, storage space, and restoral time.  Incremental backups only backup the data that has changed since the last backup, save on storage space, and are faster than a full backup.  I encourage the use of incremental backups in your backup strategy.  MEM will notify you if  incremental backups are not enabled:

 

You can customize any of the thresholds to suit your environment.

Tables Behind the Curtain

MEM uses the backup progress information written into the mysql.backup_progress table, and status information from the mysql.backup_history table.  You can query these tables to get backup status information if you are not using MEM (but you will not receive the alerts and notifications that MEM provides).

Here I’ve queried from the backup_history table, which keeps a history of the backups I’ve completed:

The backup_progress table shows the state of the backup as it progresses from start to finish:

MySQL Enterprise Backup and Enterprise Monitor new features bring us one step closer to a true enterprise backup environment. Streaming, integration with media management systems, and the ability to take advantage of tape encryption features coupled with the new Backup Advisor in MEM will help achieve that state we all need to plan for but hope to never see – the ability to quickly restore a database when needed.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s