You are viewing our old blog site. For latest posts, please visit us at the new space. Follow our publication there to stay updated with tech articles, tutorials, events & more.

Database Backups Using MySQL-ZRM

Monday, January 11, 2016 | By
0.00 avg. rating (0% score) - 0 votes

Data is one of the biggest assets for any organization, so maintaining multiple copies of updated data and to make it available in case of any hardware/software failure is a key thing to keep the business running. Thus, an efficient data backup solution is a must. Taking backups of huge data is always a challenge because having a hot backup of TBs of data is impossible and backing up the complete filesystem doesn’t guarantee data consistency. While exploring different open source backup solutions, we came across MySQL-ZRM (MySQL Zmanda Recovery Manager) which matched our requirements to an extent which could be further customized on need basis.

MySQL-ZRM

It’s an open source tool which can take backup using various methods like Raw Backup using Logical Volume Snapshot, Logical Backup using dumping the data with the help of SQL queries. There are multiple configurable options available like full/incremental Backups, Backup of remote database, compression, Mail notifications etc.

Customizations Made As Per Our Needs

To make this tool fit for taking backups of our database we made few customizations like

  1. Database to configure backups to be taken and storing status of all ongoing backups
  2. Pre-Script to check which backups are active and make an entry in table to mark the corresponding backup as started
  3. Post-Script to insert backup status in table that broadly includes source, destination servers, time taken to complete, compressed size, DB Names to take backups etc.
  4. Instead of running mysql-zrm-scheduler we created a customized script to schedule backups which takes input as Script.sh <backup-set name><backup level>                       In addition to backup scheduling, this script is also responsible for purging old             backups, running post script. Reason behind running post script via this script             instead of post-backup plugin option in configuration file is that this script reads index file created by MySQL-ZRM to capture status of backup taken by the scheduler which is created once the backup process is completed
  5. Modified snapshot plugin to throw an error in case of snapshot exhaustion instead of assuming that backup has completed.

Reports

To make this as a complete solution, we modified it’s reporting to cater our needs. Below is the report which we get via Mail

Backup Report for 2015-06-23

  Successful Backups
Backup-set    Comment Backup-date Backup-time    Backup-size  
MonitorDB Monitor Full Backup from 11.xx to 11.yy 2015-06-23 00:30:02 01:18:05 9.50 GB
Test1 Test1 Full DB Backup from 11.xx to 11.yy 2015-06-23 03:00:01 00:00:19 9.42 MB
mailer Mailer DB Full Backup from 11.xx to 11.yy 2015-06-23 01:30:02 02:38:28 17.45 GB
Naukri Naukri Full Backup from 11.xx to 11.yy 2015-06-23 04:00:02 02:03:15 19.29 GB

 

  Inactive Backups
Backup-set   Comment Backup-date Backup-time   Backup-size
TestDB TestDB Full Backup from 11.xx to 11.yy 2015-06-23 00:30:02 01:18:05 9.50 GB

 

  Failed Backups
Backup-set   Comment Backup-date Backup-time   Backup-size  
99acres 99acres Full Backup from 11.xx to 11.yy 2015-06-23 00:30:02 01:18:05 9.50 GB

 

  In Process Backups
Backup-set Comment Backup-date Backup-time       Backup-size     
99acres-Mailer 99acres-Mailer Full Backup from 11.xx to 11.yy 2015-06-23 00:30:02 01:18:05 9.50 GB

To create this consolidated report we copy all index files created by corresponding backups to one server at specific location, read data logged in DB by post script and Mysql-zrm-reporter to get details of ongoing backups.

Posted in Database, Software Development
Tags: , ,