Backup How

MySQL Integration Parameters

These options allow you to optimize backup performance by selecting from the various options described below.

MySQL Replication

Specifies whether ZRM for MySQL will use the MySQL Replication facility that has been enabled on the MySQL server that is to be backed up. Backing up a replication slave reduces impact on the production database near zero while backups are in progress. When this option is enabled and the mMySQL server is a replication slave, ZRM for MySQL will back up the master.info, relay-log.info and any SQL_LOAD-* files if they exist. For further details on the benefits of using a replication slave for backup, see this MySQL documentation. The master.info and relay-log.info files are described here. If you are backing up replication slave, you need to enable log-slave-updates in the replication slave in MySQL configuration file (my.cnf) for log incremental backups.

Dump Store Routine

To improve the performance of database functions and procedures, MySQL versions 5.0 and higher allow users to compile and store them as reusable routines (Stored routines). This option specifies whether stored routines should be include during logical backups. The default value is No. If your version of MySQL supports this feature, it should probably be set to Yes. Setting the value to Default implies Site specific value is used for this parameter.

Copy Binary Logs

By default, ZRM copies binary logs during full backups (Default value is Yes). This allows ZRM to restore to any event between last backup before full backup and the full backup.  You can turn this behavior off by setting value to No.

Force InnoDB Backup

Choose whether to force backup of InnoDB logs and shared data regardless of whether any tables in the backup set are stored by InnoDB. If set to No, ZRM for MySQL performs a check, and does not back up InnoDB logs and shared data unless the backup set has tables stored by InnoDB. In most cases, the default of Yes provides the best performance, because the check takes longer than just backing up the InnoDB tables.

Default Character Set

Specify the default character set that is used in the MySQL database. The default value is utf8; if the database uses a different character set, reset accordingly.

Logical Backup Options

Parameters to the mysqldump MySQL command. Logical backup uses mysqldump command. You can customize the options using this field. For example: "--max_allowed_packet=1G" can be specified as value. These parameters are used in addition to parameters passed by ZRM.

Plugin Parameters

Plugins allow you to specify various operations before before, during, and after the backup. Plugins are available for compression, encryption, and what copy mechanism to use. You can also integrate your own pre- and post-backup plugin operations.

Compression 

Backup compression is performed on the ZRM server. Allows you to compress the image using gzip on the ZRM server. If the storage engine already compresses data (such as ARCHIVE storage engine), there is no value in setting this to Yes.

OTF (On the Fly) Compression

(Applies only to logical backups) Specify whether backup image files stored on disk should be compressed as the backup progresses. The default is to start compression only after all backup files have been saved to disk. Turning this option on can save disk space, but results in slower backups.

Encryption

Backup encryption is performed on the ZRM server. Default encryption is performed using GPG. ZRM server should have GPG (GNU Privacy Guard) packages installed. These packages are part of Linux and Solaris distribution. The default encryption algorithm used is AES 256. If different key length or cryptographic algorithm is required, please modify gpg parameters in the script /usr/share/mysql-zrm/plugins/encrypt-plugin.pl on the ZRM server. Please contact Zmanda support team for help.

Path to passphrase file 

The full path to the encryption passphrase file. The encryption file is usually stored as /etc/mysql-zrm/<backup set name>/.passphrase on the ZRM server. The passphrase should be owned by mysql user and should have 400 permission. It is very important to protect the contents of the passphrase file. Passphrase file contains the sequence of words or a string that is used for encryption. It is important to choose a good passphrase. The longer and more random the passphrase, the more difficult to crack the encryption. The passphrase file should contain the passphrase in the first line (other lines in the file are not read). A method to generate passphrase is to use the following command: 

$ gpg --gen-random 1 16 | gpg --enarmor | sed -n 5p > /etc/mysql-zrm/<backup set name>/.passphrase

Backup Encryption Caution: It is very important to store the encrypt-plugin.pl and the passphrase file used for the backup set. These files are necessary 
for restoration. Without these files, it is impossible to restore the backup image and backup image is no longer useful.

Copy

The copy plugin is used to transfer backup files to and from a remote MySQL server to the ZRM server. Use this option in the following circumstances:

• backing up any Windows-based MySQL server; use the Windows copy plugin.
• remote incremental backup is required
• copying replication-related files from a remote machine
• executing mysqlhotcopy (MySQL command) to copy the data from the remote MySQL server
• executing mysqlhotcopy to restore the data to a remote MySQL server.
• backing up remote machines via snapshot backups.

For non-Windows remote MySQL servers, choose SSH or Socket, and then enter the appropriate parameters for the option selected. Available copy plugins are described below:

SSH

Secure Shell-based communication. Use when a higher level of security is required (such as when the client and server communicate across a firewall). You must then enter the SSH username on the remote MySQL server, and the path where MySQL commands are installed on the remote machine. For backup, it is necessary to set up password-less ssh configuration is required (i.e, mysql user on ZRM server should be able to ssh to MySQL server without being prompted for password). During restore, users will prompted for ssh password if needed. The user id and group id of mysql user must be same on the ZRM and MySQL server.

Socket

Socket-based communication. This is the default copy plugin. This requires that the ZRM client components are installed on the remote machine, as described in the Installation Instructions. You must then enter a port for the remote socket (the factory default is 25300. The path where MySQL commands are installed on the remote machine must be provided. The user id and group id of mysql user must be same on the ZRM and MySQL server.

Windows

The Windows copy plugin is required for backing up any Windows-based MySQL server. It requires the ZRM for MySQL Windows client components described in the Installation Instructions. You must then enter the communications ports to use during backup (default is 10080) and restore (default is 10081) operations

MySQL Enterprise Backup/InnoDB Hot Backup - MySQL Block backups

Select MySQL Enterprise Backup as the MySQL Block Backup Type to use Oracle's InnoDB Hot Backup or MySQL Enterprise Backup to perform the backup. Oracle's InnoDB Hot Backup must be purchased from www.innodb.com or use MySQL Enterprise Server Standard edition or higher. This option provides integration between ZRM and the Oracle product. This allows backup to proceed without setting any locks or impacting database operation. You must then supply the path to the InnoDB Hot Backup tools: the ibbackup binary and the mysqlbackup tool, which must be installed in the same path on the MySQL server being backed up. The default path is /usr/bin.  The mysql user should have permissions to execute the mysqlbackup tool. MEB disk throttle can be specified to throttle disk I/O during backup. It is specified in milliseconds, the backup sleeps for that time between disk I/O. Full, differential and chained differential backups are performed using this tool.

MySQL Enterprise Backup requires the innodb_data_home_dir to be specified explicitly in the my.cnf for the MySQL server being backed up. The directory cannot be specified as part of innodb_data_file_path. For example:

innodb_data_home_dir = /innodb/innodbdata/
innodb_data_file_path=ibdata1:50M;ibda2:50M;ibda3:40M;ibd4:20M:autoextend

Xtrabackup - MySQL block backups

Select XtraBackup as the MySQL Block Backup Type to Xtrabackup tool to perform the backup. Xtrabackup tool can be downloaded from Percona or SkySQL. It is open source and can be downloaded to MySQL server. This option provides integration between ZRM and Xtrabackup. This allows backup to proceed without setting any locks or impacting database operation. You must then supply the path to the Xtrabackup tools: the xtrabackup binary and the innobackupex tool, which must be installed in the same path on the MySQL server being backed up. The default path is /usr/bin.  The mysql user should have permissions to execute the innobackupex tool.  Xtrabackup backups can be throttled by specifying the number of disk I/Os performed by the backup tool in a second. Full, differential and chained differential backups are performed using this tool. This tool also users to restore a table from a database backup. Restores of table can be performed only to Percona MySQL servers.

Pre-backup

Choose Custom to activate the feature (unless you have set a site setting; the factory setting is to use no pre-backup plugin), then specify the full path and any command-line options to the script or utility you want executed before the backup run starts. This feature can be used to check and prepare the MySQL server environment for backup. For example, you could use it to notify all MySQL database users that a backup is about to begin.

ZRM for MySQL does not check if the path is a valid path, nor if the plugin is present at the given location. The recommended location for plugins is /usr/share/mysql-zrm/plugins directory. A template file (pre-backup.pl) is installed in this location; use the template when writing your own pre-backup plugin. All pre-backup plugin scripts must accept the following command-line parameters (which are passed to the plugin using the Plugin Option(s) field:

--all-databases

Use when all databases in the MySQL server are being backed up.

--database database1 database2...

Use when selected databases are being backed up.

--database database --tables table1 table2 ...

Use to specify the list of tables in the database that are being backed up.

The script should be written to return a non-zero value on error; pre-backup plugin failures should cancel the backup and generate a failure message for reports and logs.

Post-backup

Choose Custom to activate the feature (unless you have set a site setting; the factory setting is to use no post-backup plugin), then specify the full path and any command-line options to the script or utility you want executed after the backup run is completed. A template file (post-backup.pl) is installed in this location; use the template when writing your own pre-backup plugin. In addition the parameters for the pre-backup plugin described above, the post-backup plugin should be written to accept the following additional options:

--backup-directory path

The post-backup plugin is executed twice: before the checksum is performed, and after the checksum is complete. See the man page for mysql-zrm-backup(1) for more details on how the post-backup plugin operates and the flags it returns.

Note that although failures of the post-backup plugin are logged, they are not included in backup reports if the backup itself succeeded.

Snapshot

Select a snapshot plugin if desired. Snapshot plugins are described in more detail in the Snapshot Plugins section of this manual.

Backup Type

This field appears only when Snapshot plugins are used.  The values are shown below:

Standard (Copy)

Full and Incremental backups are copied to the ZRM server to the destination location specified in the Backup Where page.

Quick (No-Copy)

Full backups are maintained as snapshots. Full backup are not copied to the ZRM server. This backup is faster but full backup will not available if the storage system fails. So, it is important to convert quick backups to regular backups. Incremental backups are copied over to ZRM server.