MySQL backup scheduler
The Problem
The value of data stored in MySQL grows exponentially, loss of data can be catastrophic, resulting in lost revenues, lost customers and lost reputations. Setting up MySQL backup and restore processes typically takes up a lot of a DBA’s time and attention.
The Solution
Zmanda Recovery Manager (ZRM) for MySQL simplifies the life of a DBA who needs an easy-to-use yet flexible and robust backup and recovery solution for MySQL server. In just about 15 minutes, I installed and configured a fast and reliable MySQL backup solution, performed a backup, verified the backup and performed a restoration. I did it with freely downloadable open source software that can install from binaries. As a DBA, have a reliable and easy to implement backup solution to protect database using ZRM for MySQL.
Note: I personally tested with ZRM Community Edition 2.1.1 with ubuntu server 9.10
Install ZRM for MySQL.
1. Log in as root on your Linux Server. Download mysql-zrm_2.1.1_all.deb from http://www.zmanda.com/backup-mysql.html
2. Install the ZRM for MySQL community edition. Installation of the package should be done as root.
dpkg -i mysql-zrm_2.1.1_all.deb
Configure ZRM for MySQL to backup your database: For this example, I will refer to a database called LocalEntity with a size of 340mb.
1. Log in as root on your Linux Server.
2. Configure a daily full backup of the LocalEntity Database. For the purpose of this test, I will create a directory called “dailyrun”, and create mysql-zrm.conf configuration file into there. This allows more flexibility to create individual backup sets for individual databases.
cd /etc/mysql-zrm
mkdir dailyrun
cd dailyrun/
3. Edit /etc/mysql-zrm/dailyrun/mysql-zrm.conf to change the following parameters. Please be sure to protect this file with the proper permissions, as it stores the password for the MySQL ‘root’ in clear text. For the purpose of this test we are demonstrating a full backup:
# Backup level. It can be full or incremental
# Use 0 for full and 1 for incremental backups
# This parameter is optional and default value is full backup.
backup-level=0
# Backup method
# Values can be “raw” or “logical”. Logical backup are backups using
# mysqldump(1) tool
# This parameter is optional and default value is “raw”.
backup-mode=logical
# Specifies how long the backup should be retained. The value can be
# specified in days (suffix D), weeks (suffix: W), months (suffix: M) or
# years (suffix Y). 30 days in a month and 365 days in a year are assumed
# This parameter is optional and the default is the backups are retained
# forever.
retention-policy=10D
# This parameter should be set to 1 if backups should be compressed. If this
# parameter is set, gzip(1) command is used by default. If different
# compression algorithm should be used, it must be set in “compress-plugin”
# parameter. Default: There is no data compression.
compress=1
# List of databases that are part of this backup set. Multiple database
# names are separated by space character. This parameter is ignored if
# “all-databases” is set 1.
databases=LocalEntity
# MySQL database user used for backup and recovery of the backup set.
# This parameter is optional. If this parameter is not specified, values from
# my.cnf configuration file.
user=”root”
# MySQL database user password.
# This parameter is optional. If this parameter is not specified, values from
# my.cnf configuration file or no password is used.
password=”pass123″
# This parameter controls the verbosity of MySQL ZRM logging. The MySQL ZRM logs
# are available at /var/log/mysql-zrm/mysql-zrm.log. This parameter is optional
# default value is 0 (less verbose).
# The valid values are 0 and 1
verbose=1
# After a backup run the backup report is emailed to the mailto address
# This parameter is optional and default behavior is not to send mail notifications.
mailto=”krishna.manchikalapudi@gmail.com”
4. Save and close the /etc/mysql-zrm/dailyrun/mysql-zrm.conf file
Perform a Backup
1. Schedules a daily full at 1pm everyday.
mysql-zrm-scheduler –add –interval daily –start 13:00 –backup-level 0 –backup-set dailyrun
2. Verification that scheduler has been configured
mysql-zrm-scheduler –query
3. The first report shows us the status of backup(s) for backup-set ‘dailyrun’, including backup level (full [0] or incremental [1]) & backup status (success or failure).
mysql-zrm-reporter –where backup-set=dailyrun –show backup-performance-info
Perform a full restoration
1. First we’ll drop the LocalEntity database. In order to this, we will have to login MySQL with a user that has root privileges. In this example, we have renamed the ‘root’ user to ‘admin’ (for security reasons), within MySQL.
drop database LocalEntity;
2. Determine which backup to restore from.
mysql-zrm-reporter –show restore-info –where backup-set=dailyrun
3. Kick off restore job. We’ll restore from the last full backup, from 1pm on Jan 01, 2010.
mysql-zrm -restore –backup-set dailyrun –source-directory /home/krishna/backup/mysql/dailyrun/20100101130000/
4. After starting the MySQL service (as root type: /sbin/service mysqld start) Verify the database was restored.
show tables;
The Problem The value of data stored in MySQL grows exponentially, loss of data can be catastrophic, resulting in lost revenues, lost customers and lost reputations. Setting up MySQL backup and restore processes typically takes up a lot of a DBA’s time and attention. The Solution Zmanda Recovery Manager (ZRM) for MySQL simplifies the life…