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

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.

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.