Zmanda Recovery Manager for MySQL
What is Zmanda Recovery Manager for MySQL
Zmanda Recovery Manager for MySQL (MySQL ZRM) is a flexible and robust backup and recovery solution for MySQL server. It also provides users the capability to schedule and get report on the backup of MySQL Databases.
What does MySQL ZRM run on?
I have questions/suggestions/bug fixes. How do I contact other users/developers?
What can MySQL ZRM do?
What will be implemented in future releases?
How do you install MySQL ZRM?
Before installation: Do's/Don'ts
- Check the version of your MySQL server. Verify that this is a version supported by Zmanda Recovery Manager for MySQL.
- MySQL ZRM requires MySQL client commands to be installed on the machine where MySQL ZRM is running. This is required even if MySQL ZRM is backing up remote MySQL servers. MySQL ZRM depends on the following commands:
- mysqladmin
- mysqlshow
- mysqlhotcopy
- mysqldump
- mysqlbinlog
- mysql
- The MySQL client commands installed on MySQL ZRM machine should be compatible with the MySQL server. It is preferable to use the same version of MySQL on MySQL servers and on the MySQL ZRM machine (if they are running on different machines.
- Zmanda Recovery Manager for MySQL installation and configuration requires Unix superuser access. All MySQL ZRM commands are run as the superuser.
- Check the version of Perl and compatibility with MySQL database. MySQL Perl interfaces (DBD, DBI packages) are required for some backup methods.
- DBD::mysql package must be installed on your system. Following command can install DBD::mysql package:
# perl -MCPAN -e 'install DBD::mysql'
RPM installation
- RPM installation must be done as superuser.
- RPMs are available from Zmanda downloads page.
# rpm -ivh MySQL-zrm-1.0.2-1.noarch.rpm Preparing... ########################################### [100%] 1:MySQL-zrm ########################################### [100%]
- Files are installed in following directories:
| Directory location | |
|---|---|
| Executables | /usr/bin |
| Man pages | /usr/share/man/man1 |
| Configuration files | /etc/mysql-zrm |
| Libraries | /usr/lib/mysql-zrm |
| Log files | /var/log/mysql-zrm |
| Documentation | /usr/share/doc/MySQL-zrm-1.0 |
Is MySQL ZRM really installed?
You can do one of the following to verify installation:
- Use rpm(8) command
# rpm -qa | grep MySQL-zrm MySQL-zrm-1.0.2-1
- Check mysql-zrm man page
# man mysql-zrm
Do I need to make changes to MySQL database configuration?
There are some changes required to MySQL database and server configuration for the following configurations:
- Different user roles
- Create a new MySQL backup user as well as restore user. This is required only if the user with the root privileges will not be doing backup and/or recovery of MySQL databases.
- Incremental backups
- Enable binary logs on the MySQL server if incremental backups of MySQL database have to be performed.
- Secure communication between MySQL server and MySQL ZRM
- Enable SSL on the MySQL server.
MySQL backup user
MySQL backup user should be created and sufficient privileges to do backups and recovery. Minimal set of MySQL privileges for
- backup user
- LOCK TABLES, SELECT, FILE, RELOAD
- restore user
- CREATE, DROP, INDEX, SHUTDOWN, INSERT, ALTER
If backups are being done from MySQL replication slave, backup user should have REPLICATION CLIENT, SUPER privileges in addition to the above mentioned ones.
MySQL command to grant privileges for a user:
mysql> GRANT <privileges> ON
{tbl_name | * | *.* | db_name.*} TO <user name> identified by PASSWORD <user password>; Example: Command that grants minimal user privileges for backup user dba-backup to backup database expenses remotely from domain company.com: mysql> GRANT LOCK TABLES, SELECT, FILE, RELOAD -> ON expenses.* -> TO 'dba-backup'@'company.com' -> IDENTIFIED BY 'obscure'; MySQL ZRM should be running on a machine with domain name company.com. It is recommended to create a backup user instead of using MySQL root user. If it is necessary to use different MySQL user for backup and restoration, backup user should be specified in mysql-zrm.conf for the backup set. The restore user can be specified in the mysql-zrm command line using --user and --password options.Binary logs
To do MySQL incremental backups, it is necessary to enable binary logging on the MySQL server. MySQL server process should be started with --log-bin option
mysqld --log-bin=<binlogfilename>
Enabling binary logs on a MySQL server causes about 1% reduction in performance.
It is good idea to store binary logs in a filesystem (storage) from the database data directory location.
For more information on MySQL binary logs, see MySQL reference manual
SSL support on MySQL server
Configuring SSL between MySQL server and ZRM : This configuration is necessary only for logical backups of remote MySQL server. MySQL server has to be configured for SSL. To check if SSL support in MySQL server, you can do the following:
# mysqld --ssl --help 060828 15:25:08 [ERROR] mysqld: unknown option '--ssl'
To check whether a running mysqld server supports SSL, examine the value of the have_openssl system variable:
mysql> SHOW VARIABLES LIKE 'have_openssl'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_openssl | YES | +---------------+-------+
For more information on configuring SSL on MySQL server, see MySQL reference manual
There are two options to configure SSL between MySQL server and ZRM. This configuration is relevant only for logical remote backups.
- Set up SSL parameters in my.cnf file on the machine running ZRM.
ssl-ca=<mysql_conf_dir>/openssl/cacert.pem ssl-cert=<mysql_conf_dir>/openssl/client-cert.pem ssl-key=<mysql_conf_dir>/openssl/client-key.pem
- You can configure ssl-ca, ssl-cert, ssl-key in mysql-zrm.conf for the backup set.
ssl-options="--ssl --ssl-ca=<mysql_conf_dir>/openssl/cacert.pem --ssl-cert=<mysql_conf_dir>/openssl/client-cert.pem --ssl-key=<mysql_conf_dir>/openssl/client-key.pem"
How do I configure MySQL ZRM?
MySQL ZRM configuration is based on "backup sets". A backup set is set of databases or tables in a database that have to be backed up in same manner - same backup method, same backup schedule. Each backup set is identified by a unique name (a string) for each MySQL ZRM instance.
Use for multiple backup sets
- It is advisable to create backup sets based on the applications using the MySQL database and the window available to do backups. All databases and/or tables in a database used by a single application should be part of one backup set.
- Multiple backup sets have to be used for managing backups multiple MySQL servers. Each backup set will have different MySQL server parameters and databases/tables to be backed up. All backup sets can share the same backup target directory.
Backup target directory
All MySQL database backups are stored under MySQL backup root directory. MySQL backup root directory is a filesystem local to the machine where MySQL ZRM is running.
Default directory used is /var/lib/mysql-zrm. It is possible to use to use different backup directory location for each backup set in MySQL ZRM (But this is not a recommended practice).
It is necessary to allocate sufficient disk space to meet the backup space needs for the MySQL databases. If there is not sufficient disk space to do backups, MySQL ZRM backup run will fail and there will be no backups.
You can use NFS or CIFS mounted storage for storing MySQL backup data. The MySQL backup data can be migrated to other storage devices using Network based backup and recovery tool such as Amanda
MySQL ZRM configuration file
MySQL ZRM configuration file is located under /etc/mysql-zrm directory. All backup parameters applicable for all backup sets are specified in a global configuration file - /etc/mysql-zrm/mysql-zrm.conf. Parameters that are specific to a backup set are specified in /etc/mysql-zrm/<backup set name>/mysql-zrm.conf. All backup set specific parameters will override the parameters specified in the global configuration file.
Since the MySQL user and password in plain-text has to be provided in the configuration file, sufficient care should be taken to protect the configuration file from unauthorized access.
All parameters in the MySQL ZRM configuration file are optional and MySQL ZRM will use the MySQL client file (my.cnf) parameters if needed.
Backup set parameters
These are the list of the backup set parameters that can be specified in the configuration file - global and backup set specific configuration file.
All lines beginning with # in the configuration file are comments and are ignored.
Backup parameters
- comment
- Note about the backup. This note can be used by the database administrator to store information about the backup set.
- backup-level
- Backup level to be used for the backup set. It can be full or incremental. The values can be 0 (full backups) or 1 (incremental backup). This parameter is optional and default is full backup.
- destination
- The directory location of backups for the backup set. All backups are stored under this directory. This parameter is optional and default value is /var/lib/mysql-zrm. This directory should have sufficient space to store MySQL database backups. If sufficient space is not available, MySQL backup runs will fail. The directory permissions should allow the mysql-zrm user to write to the directory and create sub-directories.
- retention-policy
- Backup images for the backup set will be retained for time specified as argument. The time argument can be specified in days (suffix: D), weeks (suffix: W), months (suffix M) or years (suffix Y). If no suffix is specified, unit of days is assumed. 30 days in a month and 365 days in a year are assumed. For example: retention-policy 10M would mean the backup images would be retained for 300 days since the backup date. This parameter is optional and default is backups are retained forever.
- backup-mode
- MySQL backups can be raw or logical backups. Raw backup images contain actual database whereas logical backups contains the list of SQL statements (CREATE TABLE, INSERT) to recreate the tables and the data in the database. Logical backup images can be easily restored to another machine with different system architecture or to different type of database (not MySQL). The values can be raw (Raw backups) and logical (logical backups). This field is optional and default mode is raw.
- lvm-snapshot
- MySQL ZRM can use LVM snapshots to create a consistent raw backup of the MySQL database. If this parameter is specified in the configuration file for the backup set, LVM snapshot will be used. To use LVM snapshot, all the MySQL databases in the backup set should be part of a logical volume. The value of this field should be size of snapshot volume. Sufficient disk space should be available for the snapshot. During the MySQL backup, LVM stores the snapshot blocks corresponding to the blocks that are modified in the original logical volume in the snapshot volume. If the database has lots of activity during the backup, lot of blocks will be modified during backup and snapshot volume will run out of space. Under these circumstances, the backup will not be consistent. The size of snapshot can have size suffix of k for kilobytes, m for megabytes, g for gigabytes or t for terabytes
- replication
- This parameter is set to 1 if the backup is being done from a MySQL replication slave. This would allow all replication related files to be backed up. One use of MySQL replication is for doing database backups without impacting the MySQL database server. MySQL ZRM does not set up MySQL replication slave for backups.
Databases/tables in the backup set
One of the all-databases or databases or tables/database parameters should be specified. If none of them is specified, all-databases is assumed. Default is backup set contain all databases in the MySQL server.
- all-databases
- This parameter should be set to 1 if all databases are part of the backup set.
- databases
- List of databases that are part of the backup set. The database names have to be separated by space character. If all databases are part of backup set, use all-databases parameter.
- tables
- List of tables that are part of the backup set. These tables should belong to the database specified in database backup parameter. The table names should be separated by space character. MySQL ZRM does not verify database referential integrity of the backups. database backup parameter must be specified if this parameter is specified.
- database
- The tables specified in tables backup parameter belong to the database specified in this field. There can be only one database name specified as value.
MySQL server parameters
- user
- MySQL database user that will be used by MySQL ZRM for connecting to the MySQL server to do backups and recovery. This user should have sufficient privileges to do backup and recovery. See MySQL backup user section for more information on granting privileges. If this field is not specified, information from MySQL client configuration file - my.cnf is used.
- password
- MySQL database password for the user specified in user backup parameter. This password has to be provided in plain text. If this field is not specified, information from MySQL client configuration file - my.cnf is used. If there is no password for the user (not recommended), do not specify this parameter in the configuration file.
- host
- Fully qualified host name of the MySQL server. If this field is not specified, information from MySQL client configuration file - my.cnf is used.
- port
- MySQL server port. This parameter is optional. Default value is 3306.
- ssl-options
- List of SSL options to connect to the MySQL server. These SSL options are required if the MySQL server has SSL enabled.
- Example: ssl-options="--ssl --ssl-ca=<mysql_conf_dir>/openssl/cacert.pem --ssl-cert=<mysql_conf_dir>/openssl/client-cert.pem --ssl-key=<mysql_conf_dir>/openssl/client-key.pem"
- For more information, see SSL support on MySQL server section of this page.
- mysql-binpath
- The directory when MySQL commands are located. This parameter is optional. It is required only if the MySQL commands are not installed in default location (/usr/bin). Example: /opt/lampp/bin. By default, MySQL commands are searched in following directories: /usr/bin:/usr/sbin:/sbin:/bin:/usr/ucb
ZRM parameters
- verbose
- This field controls verbosity of MySQL ZRM logging. Values can be 0 or 1. Default value is 0. Higher the value, more information will be logged. MySQL ZRM logs are available at /var/lib/mysql-zrm/mysql-zrm.log directory.
- mailto
- Mail notifications about the backup run is sent to this mail address. The backup report is sent to this address after every backup run. Usually the email address of the MySQL database administrator is specified in this field. This parameter is optional. If this parameter is not specified, email notifications are not sent.
Backup scheduling
MySQL ZRM backup runs can be scheduled using mysql-zrm-scheduler tool. Backup runs can be scheduled on a daily, weekly and monthly basis at a specific time. Each backup run is scheduled for a specific backup set. Multiple backup runs can be scheduled in a day for a backup set.
Scheduling Backups section provides a detailed description of mysql-zrm-scheduler tool.
Finally, Can I do MySQL backups?
Yes!!! All configuration is complete. You can do backups now or schedule backups for a later time.
Backup now
MySQL ZRM scheduler tool can be used to perform backups immediately. The following command does a full backup for backup set dailyrun:
# mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 0
Backup now option of mysql-zrm-scheduler should be used after MySQL ZRM configuration changes. The backup now option also checks the MySQL ZRM configuration before doing the backup.
If mailto parameter is configured for the backup set, following email will be sent to the administrator.
backup-set=dailyrun backup-date=20060828102505 mysql-version=4.1.12 backup-directory=/var/lib/mysql/dailyrun/20060828102505 backup-level=0 raw-databases=cdcol mysql phpmyadmin test backup-time=400 seconds. backup-size=0.29 MB last-backup=/var/lib/mysql/dailyrun/20060828101657 backup-status=Backup succeeded
Incremental backups
If backup level is set to 1 in mysql-zrm-scheduler command line or in mysql-zrm.conf configuration file, incremental backup is performed. Incremental backups are performed with respect to prior full backup or incremental backup for the backup set. Binary logging should be enabled in the MySQL server. Incremental backups uses binary logging and the binary log backups are not restricted to the list of databases or tables specified in the backup set. MySQL binary logs contain all database events executed by the MySQL server.
# mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 1
Scheduling backup runs
The mysql-zrm-scheduler tool can schedule daily, weekly, monthly backup runs for a backup set. You can also query the scheduled runs as well as delete a scheduled run. If start time is not specified, start time of 2am is assumed for daily runs, 3am for weekly runs and 12:00am is assumed for monthly runs. The start time is based on date and time on the machine where MySQL ZRM is running.
The mysql-zrm-scheduler tool also purges MySQL backups based on backup retention policy every day at 4am.
Examples on how to use mysql-zrm-scheduler:
- To schedule a weekly full backup run for backup set BackupSet1
# mysql-zrm-scheduler --add --interval weekly
- To schedule a daily incremental backup run at 1:35pm:
# mysql-zrm-scheduler --add --interval daily --start 13:35 --backup-level 1
- To display scheduled backup runs:
# mysql-zrm-scheduler --query
0 2 * * 0 /usr/bin/mysql-zrm --action backup --destination /var/lib/mysql-zrm --backup-set BackupSet1
35 13 * * * /usr/bin/mysql-zrm --action backup --destination /var/lib/mysql-zrm --backup-set BackupSet1 --backup-level 1
0 4 * * * /usr/bin/mysql-zrm --action purge --destination /var/lib/mysql-zrm # purging expired backup files at 4am daily
- To delete a weekly backup run from the schedule for the backup set BackupSet1:
# mysql-zrm-scheduler --delete --interval weekly
- To delete a weekly backup run which has specific start time from schedule:
# mysql-zrm-scheduler --delete --interval weekly --start 08:20
- To schedule a backup run for backup set dailyrun now:
# mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 0
<br\> To change backup run schedule:
- Run mysql-zrm-scheduler --query to list the current scheduled backup runs.
- Run mysql-zrm-scheduler --delete .. to remove the scheduled run.
Checking status of backup run
The mysql-zrm-reporter tool provides information on status on each backup run. The status can be
- Backup succeeded
- Backup done with errors
- Backup failed
MySQL ZRM logs can be used to figure out why the backup failed or why the errors occurred. The email notification also contains information on the status of the backup run.
Following example shows how to mysql-zrm-reporter to find status of backup runs for the backup set "backupSet3":
# mysql-zrm-reporter --fields backup-set,backup-date,backup-level,backup-status --where backup-set=backupSet3
***********************************************************************************************************
backup-set backup-date backup-level backup-status
***********************************************************************************************************
backupSet3 20060829140739 0 Backup done but with errors
backupSet3 20060829140836 0 Backup succeeded
backupSet3 20060829141029 0 Backup succeeded
Email notification
The mysql-zrm tool sends email to MySQL database administrator after every backup run. The email contains information about the status of backup run, backup statistics, location of backup and backup level.
mailto parameter in mysql-zrm.conf configuration file should be configured to get email notification.
The machine where MySQL ZRM is running should be configured for sending emails.
Example: Mail message sent after successful incremental backup run.
Subject: MySQL ZRM Backup Report backup-set=backup backup-date=20060829093000 mysql-version=4.1.21-standard-log backup-directory=/mysql-zrm/backup/20060829093000 backup-level=1 incremental=vmsql4-bin.[0-9]* backup-time=0 seconds. backup-size=3431 Bytes next-binlog=vmsql4-bin.000009 last-backup=/mysql-zrm/backup/20060829092833 backup-status=Backup succeeded
Verification of backup images
MySQL ZRM has the capability of verifying the backup images. It verifies the consistency of backup using backup checksums. It is a recommended practice to verify the newly created backup image after a backup run. Best practice is to verify the backup images for all backup sets on a regular basis.
The following command verifies the backup images of the last backup run for the backup set backup:
# mysql-zrm --action verify-backup --backup-set backup
The following command verifies the specific backup run of backup set backup:
# mysql-zrm --action verify-backup --backup-set backup \ --source-directory /var/lib/mysql-zrm/backup/20060829093000
mysql-zrm returns 0 if the verification is successful.
Backup failed. What do I do?
MySQL ZRM logs are available under /var/log/mysql-zrm directory on the machine where MySQL ZRM is running. All backup and recovery operations, status of these operations are logged. These logs can be used for debugging in case of failures as well as auditing backup/recovery database operations.
mysql-zrm-scheduler and mysql-zrm-reporter also have log files in the same directory.
Example: mysql-zrm log file entries from a restore of incremental backup:
Wed Aug 30 02:39:48 2006: INFO: mysql-zrm started
All the MySQL backup runs starts with "mysql-zrm started" message.
Wed Aug 30 02:39:48 2006: INFO: action being performed is restore Wed Aug 30 02:39:48 2006: INFO: backup set being used is backup
Above lines show the list of command line parameters to mysql-zrm
Wed Aug 30 02:39:48 2006: INFO: Reading options from file /etc/mysql-zrm/backup/mysql-zrm.conf
Wed Aug 30 02:39:48 2006: INFO: Mail address: root is ok
Wed Aug 30 02:39:48 2006: INFO: Input Parameters Used {
Wed Aug 30 02:39:48 2006: INFO: verbose=1
Wed Aug 30 02:39:48 2006: INFO: backup-level=1
Wed Aug 30 02:39:48 2006: INFO: mailto=root
Wed Aug 30 02:39:48 2006: INFO: destination=/mysql-zrm
Wed Aug 30 02:39:48 2006: INFO: databases=wikidb
Wed Aug 30 02:39:48 2006: INFO: source-directory=/mysql-zrm/backup/20060830020843
Wed Aug 30 02:39:48 2006: INFO: host=localhost
Wed Aug 30 02:39:48 2006: INFO: database=wikidb
Wed Aug 30 02:39:48 2006: INFO: backup-mode=raw
Wed Aug 30 02:39:48 2006: INFO: password=******
Wed Aug 30 02:39:48 2006: INFO: user=root
Wed Aug 30 02:39:48 2006: INFO: stop-position=5003
Wed Aug 30 02:39:48 2006: INFO: }
The above lines show the list of parameters used for restore operation
Wed Aug 30 02:39:48 2006: INFO: Getting the data directory Wed Aug 30 02:39:48 2006: INFO: mysqladmin --user=root --password=***** --host=localhost variables 2> /tmp/mysql-zrm-20060830023948.out Wed Aug 30 02:39:48 2006: INFO: datadir is /var/lib/mysql/ Wed Aug 30 02:39:48 2006: INFO: mysql_version is 4.1.21-standard-log Wed Aug 30 02:39:48 2006: INFO: Checking if this is a replication slave using command Wed Aug 30 02:39:48 2006: INFO: echo "show slave status"|mysql --user=root --password=***** --host=localhost Wed Aug 30 02:39:48 2006: INFO: This is not a replication slave or we do not have appropriate access rights. replication data if any has not been backed up. Wed Aug 30 02:39:48 2006: INFO: Ignoring the --replication option
The above lines show validation of backup parameters
Wed Aug 30 02:39:48 2006: INFO: Restoring incremental Wed Aug 30 02:39:48 2006: INFO: mysqlbinlog --user=root --password=***** --host=localhost --stop-position=5003 --database=wikidb "/mysql-zrm/backup/20060830020843"/vmsql4-bin.[0-9]* | mysql --user=root --password=***** --host=localhost Wed Aug 30 02:39:48 2006: INFO: Incremental restore done
Incremental restoration is complete
Wed Aug 30 02:39:48 2006: INFO: for database wikidb Wed Aug 30 02:39:48 2006: INFO: Wed Aug 30 02:39:48 2006: INFO: Shutting down MySQL Wed Aug 30 02:39:53 2006: INFO: Restore done in 5 seconds.
MySQL server is shutdown after database restoration.
What information can be obtained from a backup report?
MySQL ZRM stores policy, performance, database/tables information in a database. All the information is organized in terms of backup run. The mysql-zrm-reporter tool can be used to extract the information stored about the backup runs.
All backup parameter field names can be passed as parameters to fields argument to display select set of fields. If select set of field names are not provided as parameter, backup-set, backup-date, backup-directory, backup-level and backup-status are displayed. The reporter tool can also search for a specific field value.
The list of information about a backup run that are available:
Backup parameters
- backup-set
- Name of the Backup Set
- comment
- Database administrator comments about the backup set or the backup run
- backup-date
- Date and time stamp of when the backup was done.
- mysql-version
- Version of the MySQL server used to backup the backup set. When MySQL server is being upgraded to a newer version, this field can be used to check the MySQL version of the backup images.
- backup-directory
- The location of backup directory on the machine where MySQL ZRM is running.
- backup-level
- Backup level (full or incremental). Full backup is 0. Incremental backup is level 1.
- retention-policy
- Retention time for the backup image. The backup images will be removed after retention time since the backup-date value. The unit can be D (days), W (weeks), M (months), Y (years). 30 days in a month and 365 days in a year are assumed.
Tables/Databases that were backed up
- raw-databases
- List of databases that have raw backups - backups done using the mysqlhotcopy command. If the raw-tables field is present, all the tables listed in raw-tables belong to the database in this parameter.
- raw-tables
- List of tables backed up using the mysqlhotcopy command. All the tables listed in this parameter belong to the database in the raw-databases field.
- raw-databases-snapshot
- List of databases backed up using LVM snapshots.
- raw-tables-snapshot
- List of tables backed up using LVM snapshots. All the tables listed in this parameter belong to the database in the raw-tables-snapshot field.
- logical-databases
- List of databases backed up using mysqldump(1)
- logical-tables
- List of tables belonging to "logical-databases" backed up using mysqldump(1)
- replication
- Names of replication files that were backed up - namely master.info and relay-log.info
- slave-load-files
- Names of SQL_LOAD* files that were backed up
- incremental
- Names of Binary log files that are part of incremental backup.
Status and performance of backup run
- backup-time
- Time taken by the backup run. Format is HH:MM:SS
- backup-size
- Size of backup image in MB.
- read-locks-time
- During backups, the mysql-zrm tool holds the read lock on the database(s) or the table(s) that being backed up. The time for which the read locks were held is available.
- flush-logs-time
- The time taken to flush database pages from memory to disk. All modified database pages written from memory to the disk during backup for some backup methods. Format is HH:MM:SS.
- backup-status
- Status of the backup run. The values can be Backup Failed, Backup done with errors, and Backup succeeded.
- Backup Failed means there was a fatal error and backup was not completed.
- Backup succeeded means the backup was successful.
- Backup done with errors means there were errors during backup, not all tables/databases in the backup set were not backed up. To find out which databases or tables were backed up successfully, see mysql-zrm log file.
Predefined backup reports
Predefined backup reports can using --show option to mysql-zrm-reporter tool. The list of predefined backup reports available are shown in the table below:
| Backup report name | Description | Information available |
|---|---|---|
| backup-status-info | Status of backup runs | backup-set, backup-date, backup-level, backup-status, backup-comment |
| backup-method-info | Backup methods used | backup-set, raw-databases, raw-databases, logical-databases |
| backup-retention-info | How long are the backups retained? | backup-set, backup-date, backup-level, backup-size, retention-policy |
| backup-performance-info | Backup performance and impact on application | backup-set, backup-date, backup-level, backup-size, backup-time, read-locks-time, flush-logs-time |
| restore-full-info | Information for doing full/incremental restoration | backup-set, backup-date, backup-level,backup-directory |
| restore-incr-info | Information for selective restoration | backup-set, backup-date, incremental |
| replication-info | Replication files backed up | backup-set, backup-date, replication, slave-load-files |
Specific backup runs can be selected from the predefined backup reports using --where option.
Example: A backup status report
# mysql-zrm-reporter --where backup-set=backupSet1 --show backup-status-info
backup_set backup_date backup_level backup_status comment
-------------------------------------------------------------------------------------------
backupSet1 20060909100021 0 Backup Before application upgrade
succeeded
backupSet1 20060909100123 0 Backup After application upgrade
succeeded
backupSet1 20060909100300 0 Backup Nightly
succeeded
Formatting backup reports
mysql-zrm-reporter report format can be controlled by specifying format in /etc/mysql-zrm/mysql-zrm-reporter.conf.
The configuration file allows user to specify the format for each backup parameter. User can specify the size of each parameter value and alignment in the backup reports. The configuration file is used for predefined reports as well as custom reports generated by the user.
Each line in the file is for a specific backup run parameter. The syntax for each line is
fieldname=width,alignment
- width
- the number of characters for the value. If the value exceeds the number of characters specified, the value will wrap around into multiple lines
- alignment
- values can be left ("<") or (">") aligned
How do I recover data when there is a failure or data loss?
Backups can be recovered from full backups as well as incremental backups. mysql-zrm tool has options to restore full and incremental backups. It is possible to do selective restoration of a backup set from incremental backups.
MySQL ZRM does not support restoring to live databases. All applications using the database that is being used must be stopped.
Before restoring backups, it is necessary to find out the directory location of full/incremental backups for a backup set. The mysql-zrm-reporter tool can be used to find the location of backups and backup levels for a backup set.
Following example shows the backup directory, backup level, backup date stamp for a backup set backupSet1:
# mysql-zrm-reporter --fields backup-set,backup-date,backup-level,backup-directory \
--where backup-set=backupSet1
******************************************************************************************
backup-set backup-date backup-level backup-directory
*******************************************************************************************
backupSet1 20060829140710 0 /var/lib/mysql-zrm/backupSet1/20060829140710
backupSet1 20060829140803 0 /var/lib/mysql-zrm/backupSet1/20060829140803
backupSet1 20060829140933 0 /var/lib/mysql-zrm/backupSet1/20060829140933
After restoring the database or tables in the database, it is important to verify the restored database contents before restarting the MySQL server.
Complete restoration of full/incremental backups
The mysql-zrm can be used to restore full/incremental backups using restore action. Following example shows complete restoration of backup set backupSet1:
# mysql-zrm --action restore --backup-set backupSet1 \ --source-directory /var/lib/mysql-zrm/backupSet1/20060829140710 MySQL server has been shutdown. Please restart after verification.
Selective restoration
Selective restoration of a backup set is possible only from an incremental backup. The prior full backup or incremental backup should have been restored before attempting selective restoration.
It is important to determine the list of database events that should included or excluded from selective restoration. The next section talks about how to browse incremental backups (MySQL binary logs) to determine the database events in the binary logs.
The database events that have to be selectively restored can be specified in terms of database events or in terms of time when the events actually occurred.
Browsing MySQL binary logs
The mysql-zrm provides an option (--action parse-binlogs) to parse binary logs to determine the log positions and timestamp of database events. Information from mysql-zrm tool parse binary logs output can be used as input for mysql-zrm restore action. The binary logs output contains the binary log filename, position in the log, timestamp, type of event and actual database event. Binary logs only contain database events modify data or data attributes.
The backup directory location for the incremental backups can be found using mysql-zrm-reporter command.
The following mysql-zrm command displays binary logs from incremental backup directory /var/lib/mysql:
# mysql-zrm --action parse-binlogs --source-directory=/var/lib/mysql
Sample output from parse binary logs command:
----------------------------------------------------------------------------
Log filename | Log Position | Timestamp | Event Type | Event
----------------------------------------------------------------------------
/var/lib/mysql/my-bin.000015 | 9762 | 06-09-19 06:20:03 | Query | CREATE TABLE `table_InnoDB` ( `name` varchar(20) default NULL, `age` int(3) default NULL, `address` varchar(200) default NULL, `sex` char(1) default NULL, `DOB` date default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/var/lib/mysql/my-bin.000015 | 10058 | 06-09-19 06:20:03 | Query |
/var/lib/mysql/my-bin.000015 | 10178 | 06-09-19 06:20:03 | Query | INSERT INTO `table_InnoDB` VALUES ('1kkg',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg1',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg2',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg3',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg4',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg5',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg6',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg7',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg8',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg9',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg10',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg11',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09');
/var/lib/mysql/my-bin.000015 | 11013 | 06-09-19 06:20:03 | Xid = 4413 | COMMIT;
/var/lib/mysql/my-bin.000015 | 11040 | 06-09-19 06:20:03 | Query |
/var/lib/mysql/my-bin.000015 | 11159 | 06-09-19 06:20:03 | Query | DROP TABLE IF EXISTS `table_MyISAM`;
/var/lib/mysql/my-bin.000015 | 11263 | 06-09-19 06:20:03 | Query | CREATE TABLE `table_MyISAM` ( `name` varchar(20) default NULL, `age` int(3) default NULL, `address` varchar(200) default NULL, `sex` char(1) default NULL, `DOB` date default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/var/lib/mysql/my-bin.000015 | 11559 | 06-09-19 06:20:03 | Query |
/var/lib/mysql/my-bin.000015 | 11679 | 06-09-19 06:20:03 | Query | INSERT INTO `table_MyISAM` VALUES ('1kkg',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg1',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg2',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg3',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg4',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg5',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg6',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg7',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg8',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg9',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg10',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg11',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09');
/var/lib/mysql/my-bin.000015 | 12514 | 06-09-19 06:20:03 | Query |
/var/lib/mysql/my-bin.000015 | 12633 | 06-09-19 06:20:03 | Stop |
/var/lib/mysql/my-bin.000016 | 4 | 06-09-19 06:20:14 | Start: binlog v 4, server v 5.0.21-max-log created 060819 6:20:14 at startup | ROLLBACK;
/var/lib/mysql/my-bin.000016 | 98 | 06-09-19 06:25:10 | Stop |
/var/lib/mysql/my-bin.000017 | 4 | 06-09-19 19:15:13 | Start: binlog v 4, server v 5.0.21-max-log created 060819 19:15:13 at startup | ROLLBACK;
/var/lib/mysql/my-bin.000017 | 98 | 06-09-19 19:35:57 | Query | FLUSH TABLES /*!32323 `kkg123`.`table_ARCHIVE`, `kkg123`.`table_BerkeleyDB`, `kkg123`.`table_InnoDB`, `kkg123`.`table_MyISAM` */;
/var/lib/mysql/my-bin.000017 | 285 | 06-09-19 20:08:28 | Rotate to my-bin.000018 pos: 4 |
Restoration based on timestamp - Point in time recovery
MySQL ZRM can do point in time recovery. Selective recovery of an incremental backup can be done till a particular time or starting from a particular time. For example: A full backup of a backup set is done at 1am and followed by an incremental backup at 10am. It is possible to recover the full backup and recover the database to the state at 8am from the incremental backup.
The following example shows restoration of backup set backup to the state at 9pm on Aug 30, 2006.
# mysql-zrm --action restore --backup-set backup \ --source-directory /var/lib/mysql-zrm/backup/20060830020843 \ --stop-datetime "200608302100"
MySQL server has been shutdown. Please restart after verification.
Restoration based on log position
MySQL ZRM can do selective database recovery based on the positions in the binary log. This selective recovery method is useful for recovering from operator errors. For example: Suppose there was errant SQL statement executed to drop a database table between last full backup and last incremental backup. To recover from the error, the last full backup should be restored. The incremental backup is selectively restored starting from the beginning till the errant statement and followed by another restoration from the event after the errant statement.
The mysql-zrm tool option --stop-position can be used to recover the database till the particular log position. All events that have log positions less than the log position are recovered.
Option --start-position can be used to start recovery from a particular log position instead of beginning of the log file.
If there are multiple binary log files in an incremental backup, the --start-position refers to the log position in the first log file and --stop-position refers to the log position in the last log file. --bin-logs should be used to specify the binary log file names when there are more than one binary log file in the incremental backup.
The mysql-zrm also supports --offset parameter to specify an offset that can be skipped to skip N entries from the first log file.
Following example shows restoration from incremental backup stored in /var/lib/mysql-zrm/backup/2006830020843 directory starting from log position 4 to log position 22:
# mysql-zrm --action restore --backup-set backup \ --source-directory /var/lib/mysql-zrm/backup/20060830020843 \ --start-position 4 --stop-position 122 MySQL server has been shutdown. Please restart after verification.
If MySQL server is running, it is stopped by mysql-zrm tool during database restoration.
Following example will selectively restore from log position 100 from /var/lib/mysql-zrm/backupset1/200608 18121532/mysql-bin.00001 from multiple binary log files using single connection to the MySQL server:
# mysql-zrm --action restore --bin-logs \ "/var/lib/mysql-zrm/backupset1/20060818121532/mysql-bin.[0-9]* \ /var/lib/mysql-zrm/backupset1/20060819121532/mysql-bin.[0-9]*" --start-position=100
Verifying restoration
After restoration of the database, MySQL ZRM shuts down the MySQL server. It is important to check the database(s)/table(s) that were restored before restarting the MySQL server. SQL command CHECK TABLE can be used for consistency checking. Use of EXTENDED option is recommended. EXTENDED option does a full key lookup for all rows in the table and will take significant time for a large database.
mysql> CHECK TABLE <table1>, <table2> EXTENDED;
No other application must be using the database during table consistency check.
How did the MySQL ZRM do the job?
Did I use the correct method to backup?
The mysql-zrm-reporter tool provide backup statistics about the MySQL backup run. It is a good idea to review the backup-time, read-locks-time, flush-logs-time and backup-size to determine whether the method used by MySQL ZRM is appropriate for the backup set. The predefined backup-performance-info report is a useful tool to tune backup process or switch to a different backup method.
An example command displaying backup size and backup time for a backup set backupSet2:
# mysql-zrm-reporter --fields backup-date,backup-level,backup-size,backup-time \
--where backup-set=backupSet2
***************************************************************************
backup-set backup-date backup-level backup-size backup-time
****************************************************************************
backupSet2 20060829140723 0 58MB 15 seconds.
backupSet2 20060829140819 0 59MB 26 seconds.
backupSet2 20060829141001 0 78MB 40 seconds.
Using mysql-zrm-reporter tool, you can figure out what backup method was used to do the backup (backup-method-info report). You can override the backup method by setting backup parameters - replication, logical-backup, lvm-snapshot in MySQL ZRM configuration for the backup set.
Can I make backups more efficient?
The time taken to do a backup (backup window) and backup image size depends on various factors:
- Backup method used for each database - logical backups, raw backups, lvm snapshot, MySQL replication
- Backup level for the backup set
- Size of the database
- Database activity - read-only, read/write ratio
- Database transaction rates
- Recovery requirements - how often do you recover data? What is the reason for data recovery?
Use mysql-zrm-reporter tool and mysql-zrm logs to analyze the backups. The backup-performance-info report is a good starting point to analyze the bottlenecks in the backup strategy. It is possible to change all the MySQL backup parameters using the configuration file. Often, it takes multiple backup runs to arrive at a good parameters for a backup set.
Example: Backup performance report
# /usr/bin/mysql-zrm-reporter --where backup-set=backupSet1 --show backup-performance-info backup_set backup_date backup_level backup_size backup_time read_locks_time flush_logs_time ------------------------------------------------------------------------------------------------- backupSet1 20060909100021 0 4.26 MB 00:00:06 00:00:03 00:00:00 backupSet1 20060909100123 0 4.26 MB 00:00:03 00:00:02 00:00:00 backupSet1 20060909100300 0 4.26 MB 00:00:03 00:00:02 00:00:00