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
- Database to configure backups to be taken and storing status of all ongoing backups
- Pre-Script to check which backups are active and make an entry in table to mark the corresponding backup as started
- 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.
- 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
- 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.