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 time 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 on 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 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 tools 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, 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

This is 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 0 (i.e. 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 user running mysql zrm (in most cases the "root" 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 by default 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.

compress

This parameter is set to 1 if the backup images must be compressed. The default value is 0 (no compression). MySQL ZRM uses compression tools that are available in the server to do compression. The default compression program used is gzip (GNU zip tool). If different compression program should be used, it must be specified in compress-plugin parameter.

compress-plugin

This parameter specifies the compression program used for compressing backup images. Full path to the command must be specified. This parameter is used only if compress parameter is set to 1. The parameter is optional and default compression program is gzip. The compression command must be support -d option for uncompressing the backup images.

encrypt

This parameter is set to 1 if the backup images have to be encrypted. MySQL ZRM does not provide key management. MySQL ZRM uses encryption tools that are available in the server to do encrytion. By default, backup images are not encrypted. The encryption program has to be specified in encrypt-plugin parameter.

encrypt-plugin

This parameter specifies the command to be used for encryption. The full path to the encryption command is provided as parameter. This parameter must be specified if encrypt parameter is set to 1. There is no default encryption program. Sample encryption plugin using GPG (GNU Privacy Guard) is available in /usr/share/mysql-zrm/plugins/encrypt.pl. The decrypt-option parameter must be also specified.

decrypt-option

This parameter is the option passed to the encryption command specified by encrypt-plugin for decrypting the backup images. The default value is -d. If the encryption program has different command or multiple options to decrypt the backup image, a command wrapper will be required.

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

socket

Name of the socket file used for communication with the MySQL server. This parameter is optional and must be specified if MySQL clients have to connect to MySQL server only using sockets. Example: /var/lib/mysql/mysql.sock

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

mysql-binlogpath

The directory where MySQL binary logs can be found. MySQL binary logs are required for incremental MySQL backups. This parameter is optional and default value is /var/lib/mysql. Example: /var/log/mysql directory.

tmpdir

Directory on the MySQL ZRM host used for temporary storage during backup and recovery process. The default value is /tmp.

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.

html-reports

The list of pre-defined backup reports that are generated after each backup run for the backup set. This parameter is relevant only if html-report-directory parameter or webserver-url parameter are specified. If this parameter is not specified, backup-status-info pre-defined report is generated. Multiple pre-defined backup reports can be specified and must be separated by "," (comma).

html-report-directory

If this parameter is specified, backup reports are automatically generated after the backup run. The backup report is created for the last backup run. The list of backup reports that should be created automatically should be specified in html-reports. The value of this paramter is the directory location on the machine where MySQL ZRM runs where HTML/text reports are created by mysql-zrm-reporter. Full path to the directory must be specified. If this parameter is not specified, the backup reports are not automatically generated after the backup run. This parameter has no impact on email notifications after backup run. The HTML reports are stored under sub-directory "html" and text backup reports are stored under sub-directory "text".

This parameter also controls the directory where HTML reports are created when mysql-zrm-reporter command is run with -o output file option. If this parameter is not specified, the output file is created in current working directory.

webserver-url

If backup reports should be available as RSS feed, this parameter and rss-header-location parameter must be specified. The list of reports that will be generated is specified as html-reports parameter. The value of this parameter is the URL of the webserver location used to access the HTML reports and RSS feed. The html-report-directory must be accessible from the webserver document root. Example: http://www.company.com/reports/html

rss-header-location

Full path to RSS header file. Administrators are expected to customize the RSS channel properties using this file. A template of RSS header is available in /etc/mysql-zrm/RSS.header. This parameter must be specified if webserver-url parameter is specified.

ZRM plugin parameters

copy-plugin

Full path to copy plugin to be used for MySQL backups. Copy plugin is used to tranfer backup files from the MySQL server to the machine running MySQL ZRM in a secure manner. There are sample copy plugins based on ssh and socket available in /usr/share/mysql-zrm/plugins directory. This plugin must be specified for remote incremental backups and raw backups.

pre-backup-plugin

Full path to pre-backup plugin script. This plugin is optional. This plugin is executed before a backup run for the backup set. A template is available in /usr/share/mysql-zrm/plugins directory.

pre-backup-plugin-options

List of options that are passed as arguments to the Pre-backup plugin. The list of options is specified as quoted string. This parameter is optional.

post-backup-plugin

Full path to post-backup plugin script. This parameter is optional. Post backup plugin is executed after a backup run for the backup set. A template is available in /usr/share/mysql-zrm/plugins directory.

post-backup-plugin-options

List of options that are passed as arguments to the Post-backup plugin. The list of options is specified as quoted string. This parameter is optional.

pre-scheduler-plugin

This parameter is optional. This plugin if provided is used to determine the start time for a backup run. This plugin can be used to delay or skip a backup run based on other conditions such as server or application load.

ssh-user

This parameter is used by ssh copy plugin. This user should be available on the MySQL server for MySQL ZRM to securely transfer backup images from the MySQL server. SSH must be set up for this user. The recommended ssh user is the MySQL user mysql (the user that MySQL server deamon mysqld runs as).

pass-file

The location of passphrase for encryption plugin.

remote-mysql-binpath

The location of MySQL client binaries on the MySQL server. This parameter is used by copy plugins to execute MySQL commands on the MySQL server.

socket-remote-port

The TCP/IP port used by socket copy plugin to connect to the MySQL server. The default value is 25300. Changes to the parameter value would require to the changes in xinetd/inetd configuration file on the MySQL server for the socket copy plugin.

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.