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.

The current release is 1.0.1

What does MySQL ZRM run on?

MySQL ZRM has been tested on Redhat and Suse linux distributions. Since, the ZRM tools are written in Perl, it will likely work in other Linux and Unix platforms.

MySQL ZRM RPMs and Source tar ball are available at Zmanda downloads page.

MySQL versions 4.1.x and 5.0 have been tested. Version 1.0 release of MySQL ZRM is likely to work on MySQL 5.1 beta release.

I have questions/suggestions/bug fixes. How do I contact other users/developers?

Forums

Community support for MySQL ZRM is available on MySQL ZRM forums. Any one interested in MySQL ZRM can register and participate.

Mailing lists

There are three mailing lists for the MySQL ZRM project.

• mysql-zrm-announce -Mailing [moderated] list used for announcing MySQL ZRM related information. This is a low traffic mailing list
• mysql-zrm-users - Mailing list to discuss MySQL ZRM deployments, questions and enhancement requests. All users and developers should subscribe to this list.
• mysql-zrm-bugs - Mailing list that is notificed of bugs regarding the MySQL ZRM

To subscribe to the mailing lists, please see http://www.zmanda.com/mailing-lists.php page.

Bug reporting

Please report bugs in ZRM for MySQL bugzilla. Please use Zmanda Recovery Manager for MySQL product and appropriate component. List of components can be

• Backup module: Backup module of MySQL ZRM
• Configuration: MySQL ZRM configuration issues
• Documentation: MySQL ZRM wiki documentation issues
• Installation: MySQL ZRM installation issues and other dependencies
• Recovery module: Issues related full and selective restoration of MySQL databases using the MySQL ZRM.
• Reporting module:Issues related to the reporting of MySQL ZRM.
• Scheduling module:MySQL ZRM scheduler issues

What can MySQL ZRM do?

Backup features

Backup of multiple databases

MySQL ZRM can backup multiple MySQL databases that are managed by the MySQL server. It can also backup tables in a single database. It can perform hot and cold backups of the databases.

Support for multiple backup methods

MySQL ZRM can use various MySQL backup methods depending on the storage engine used by MySQL tables. It uses mysqldump, mysqlhotcopy, lvm snapshots and MySQL replication as various backup methods. The tool will use the method that will create consistent backup of the database irrespective of the storage engines used by the databases tables. Backup method can be overridden by user input.

Backup of local MySQL server

In this mode, MySQL ZRM runs on the same machine as the MySQL server. It can use mysqldump, LVM snapshots, mysqlhotcopy to do local MySQL server backups.

Backup of remote MySQL server

In this mode, MySQL ZRM runs on a different machine from the MySQL server. It can use mysqldump or MySQL replication to do backups of remote MySQL servers. SSL authentication is supported between MySQL ZRM and MySQL server which allows for backups over internet or across firewalls.

Backup Levels

MySQL ZRM does full database backups and incremental database backups.

Disk based backups

All backups are stored on disk (under backup root directory - /var/lib/mysql-zrm)

Backup retention policy

Different retention policies can be specified for each backup set.

Recovery features

Backup index

All information about backup run is stored in a backup index. The backup index can be browsed using MySQL ZRM reporting tool.

Full and incremental database recovery

Database restoration can be done only when the MySQL server is inactive. MySQL server should be stopped.

Selective restoration

Incremental restores can be done based on binary log position. This would permit recovery from database operator errors

Point in time recovery

Database can be recovered to any point in time between two successful backups.

Reporting and Scheduling features

Backup sets

MySQL ZRM can be configured in terms of backup sets. Each backup set consists of the list of databases or tables within a database, backup schedules, backup method and configuration parameters. All tools use backup set as parameters

Scheduling

MySQL ZRM backup runs can be scheduled in daily/weekly/monthly intervals. Scheduler can also be used to do backup immediately.

Backup reporting

Status of backup run, backup statistics, backup contents, location of backups can be obtained using backup reporting tool. Backup performance measurements can be used to choose appropriate backup method for a database.

Email notication

MySQL ZRM can send email notification about the backup run status to the MySQL backup administrator.

Logging

All backup and Recovery information in logged in a log file that can be used for auditing as well as debugging.

What will be implemented in future releases?

Some of the features that are being considered for future releases:

• Web based console for Zmanda Recovery Manager for MySQL
• Integration with innobackup for InnoDB storage engine backups
• MySQL database binary log browser to make the specifying log positions much easier
• More formatting tools for MySQL backup reports

If you have feature requests, please make the request using MySQL ZRM bugzilla

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.

• 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.1-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
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.1-1

• Check mysql-zrm man page

# man mysql-zrm

Do I need to make changes to MySQL database configuration?

Yes. There are some changes required to MySQL database and server configuration depending on the backup method used.

MySQL backup user

MySQL backup user should be created and sufficient privileges to do backups. If logical backups are being created, the backup user should have privileges to do LOCK TABLES, SELECT, GRANT, RELOAD for the databases being backed up.

GRANT LOCK TABLES, SELECT, GRANT, RELOAD ON database.* TO 'backup-user'@'hostname_of_mysql_zrm_machine' IDENTIFIED BY '<password>';

It is recommended to create a backup user instead of using MySQL root server.

Binary logs

To do 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.

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.

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.

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 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 field is mandatory.

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 mandatory.

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 gigbytes or t for terabyte

destination

The directory location of backups for the backup set. All backups are stored under this directory. The default value is /var/lib/mysql-zrm.

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.

all-databases

This field 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.

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.

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.

password

MySQL database password for the user specified in user backup parameter. This password has to be provided in plain text.

host

Fully qualified host name of the MySQL server. This field is mandatory.

port

MySQL server port. Default value is 3306.

Other 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. The 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.

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.

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 can be used after configuration changes.

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
next-binlog=
last-backup=/var/lib/mysql/dailyrun/20060828101657
backup-status=Backup succeede

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. Incremental backups uses binary logging is not restricted to the list of databases or tables specified in the backup set. MySQL binary logs contains 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:

1. Run mysql-zrm-scheduler --query to list the current scheduled backup runs.
2. 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 occured. 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 --key 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

Damn!!! 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 of myzql-zrm log file entries:

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 in a backup report?

The mysql-zrm-reporter tool can provide various information about the backup run. All backup parameter names can be passed as parameters to fields argument to display select set of fields. The reporter tool can also search for a specific field value.

The list of information fields about a backup run that are available in a report are:

backup-set

Name of the Backup Set

backup-date

Date and time stamp of Backup

mysql-version

MySQL server version

backup-directory

The location of backup directory

backup-level

Backup level (full or incremental). Full backup is 0. Incremental backup is level 1.

raw-databases

List of databases that have raw backups - backups done using mysqlhotcopy

backup-time

Time taken by the backup run. Format is hh:mm:ss

backup-size

Size of backup image.

next-binlog

Next binary log file name

last-backup

Location of the last backup for this backup set

retention-policy

The backup policy will be purged after this time. Default unit is in days.

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.

backup-status

Status of the backup run. The values can be Backup Failed, Backup done with errors, Backup succeeded.

raw-tables(snapshot)

List of tables backed up using LVM snapshots

raw-tables

List of tables backed up using mysqlhotcopy

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

slave-load-files

Names of SQL_LOAD* files that were backed up

How do I recover data when there is a failure?

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.

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 --directory /var/lib/mysql-zrm \
  --fields backup-set,backup-date,backup-level,backup-directory --key 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

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.

If MySQL server is running, it is stopped by mysql-zrm tool during database restoration.

Selective 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 upto 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.

If MySQL server is running, it is stopped by mysql-zrm tool during database restoration.

Selective 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.

Browsing MySQL binary logs

To determine the log positions and corresponding events, it is necessary to browse the MySQL binary logs. Binary logs can be browsed using mysqlbinlog command. The binary logs can be found in the backup directory location of the incremental backup. The backup directory location for the incremental backup can be found using mysql-zrm-reporter command.

# mysqlbinlog mysqlserver-bin.10 > binarylog.out

Binary logs contains events (SQL statements) that modify data. All SQL statements (such as QUERY) that do not modify data do not have events in binary logs. The binary log format is documented in MySQL reference manual

Following example shows binary log from an incremental backup. The field log_pos shows the position in the log and drop database db is a database event:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
# at 4
#060829  9:48:12 server id 1  log_pos 4         Query   thread_id=10    exec_time=0     error_code=0
SET TIMESTAMP=1156825092;
create database db;
# at 55
#060829  9:48:32 server id 1  log_pos 55        Query   thread_id=10    exec_time=1     error_code=0
use db;
SET TIMESTAMP=1156825112;
create table dbtab(id varchar(10));
# at 122
#060829  9:48:57 server id 1  log_pos 122       Query   thread_id=10    exec_time=0     error_code=0
SET TIMESTAMP=1156825137;
drop table dbtab;
# at 171
#060829  9:49:24 server id 1  log_pos 171       Query   thread_id=10    exec_time=0     error_code=0
SET TIMESTAMP=1156825164;
drop database db;
# at 220
#060829  9:49:34 server id 1  log_pos 220       Rotate to mysqlserver-bin.000011  pos: 4

Restoring

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 (this restoration will recreate the database db that was dropped in the example in the last section):

# 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

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.

An example 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 587071 Bytes      15 seconds.
    backupSet2  20060829140819            0 587071 Bytes      26 seconds.
    backupSet2  20060829141001            0 587071 Bytes      10 seconds.

Using mysql-zrm-reporter tool, you can figure out what backup method was used to do the backup. 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 analyse the backups. 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.