Mysql-zrm-backup

From The Open Source Backup Wiki (Amanda, MySQL Backup, BackupPC)
Revision as of 23:01, 22 April 2013 by Paddy (talk | contribs) (→‎Backup Method Options)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigationJump to search

NAME

mysql-zrm-backup - Backup utility for ZRM for MySQL.

SYNOPSIS

mysql-zrm-backup
                      [--backup-set name]
                      [Backup Source Options]
                      [MySQL Access Options]
                      [Backup Method Options]
                      [Backup Destination Options]
                      [Logging and Notification Options]
                      [--options-file conf_file]
                      [--help]

Backup Source Synopsis

 mysql-zrm-backup
                  [--all-databases] | [--databases "dbname1 dbname2"...] |
                  [--database "name" [--tables tname1 tname2 ...]]
                  [--exclude-pattern pattern]
                  [--routines | --no-routines]
                  [--mysql-binpath MySQL_command_dir]
                  [--mysql-binlog-path MySQL_binary_log_dir]
                  [--config-file-list="config_file_pathname"]

MySQL Access Synopsis

 mysql-zrm-backup
                            [--user user]
                            [--password password]
                            [--host hostname]
                            [--port portnumber]
                            [--socket name]
                            [--ssl-options "MySQL ssl options"]

Compression and Encryption Synopsis

  mysql-zrm-backup
                       [--compress | --no-compress ]
                       [--compress-mysqldump-onthefly  | --no-compress-mysqldump-onthefly]
                       [--compress-plugin plugin_name]
                       [--no-encrypt | --encrypt]
                       [--encrypt-plugin    plugin_name     [--decrypt-option option]]
                       [--passfile filename]

Plugin Synopsis

  mysql-zrm-backup
                       [--pre-backup-plugin plugin]
                       [--pre-backup-plugin-options "option1 option2 ..."]
                       [--copy-plugin plugin]
                       [--ssh-user user]
                       [--remote-mysql-binpath path]
                       [--socket-remote-port port]
                       [--post-backup-plugin plugin]
                       [--post-backup-plugin-options "option1 option2 ..."]

Backup Method Synopsis

  mysql-zrm-backup
         [--backup-level level]
         [--backup-mode raw | logical]
         [--logical-parallel 0 | 1 ]
         [--synchronous-checksum | --no-synchronous-checksum]
         [--replication | --noreplication]
         [--retention-policy backup_retention_time]
         [--default-character-set char_set_name]
         [--single\-transaction [always|never|only-innodb]]  
         [--extra-mysqldump-options mysqldump_options]
         [--extra-mydumper-options mydumper_options]
         [--compress-data-during-transport]

Snapshot Plugin Synopsis

  mysql-zrm-backup
                [--snapshot-plugin snapshot_plugin_path_name]
                [--check-innodb | --no-check-innodb]
                [--backup-type regular | --backup-type quick ]
                --lvm-snapshot size;
                                 (deprecated; use
                                 --snapshot-plugin lvm-snapshot
                                  --snapshot-size instead)

Backup Destination Synopsis

 mysql-zrm-backup
                             [--backup-name image_name]
                             [--destination dir_path]
                             [--tmpdir temp_dir

Logging and Notification Synopsis

      mysql-zrm-backup
                             [--mailto address]
                             [--mail-policy always|never|only-on-error]
                             [--comment "comment string"]
                             [--quiet|--no-quiet]
                             [--verbose]

DESCRIPTION

ZRM for MySQL provides automated scheduling of live MySQL database backup. You only need to configure it once to reflect the MySQL deployment, then schedule it to run unattended backups. Various options can help an administrator:

  • Verify the backups created.
  • Generate various types of reports about the backups created.
  • Specify compression and encryption as desired.
  • Report information about the backups via an email or RSS feed.
  • Access pre- and post- backup plugins to extend the suite further.
  • Control full and selective restore operations on the database, both locally and remotely.

mysql-zrm-backup provides the core backup functions of ZRM for MySQL databases.

Use the mysql-zrm.conf(5) configuration file to set mysql-zrm-backup options described on this page. Use the --action check option to verify the configuration of mysql-zrm-backup.

Use mysql-zrm-backup to create full or incremental backups of MySQL databases.

To display help on individual action options, append the --help option to the command line. For example: ’mysql-zrm-backup --options-file --help’

BACKUP SETS

ZRM for MySQL organizes backups into Backup Sets. Each backup set defines the list of databases or tables within a database, how it should be backed up and the backup schedule for the data. Backup sets are identified by a unique name.

Every backup run (each time mysql-zrm is executed) is associated with one backup set. Various parameters can be associated with each backup run.

mysql-zrm first reads the global configuration file (/etc/mysql-zrm/mysql-zrm.conf). The global configuration file can be overridden by backup set specific configuration file stored in /etc/mysql-zrm/backup set name directory. The mysql-zrm command line options will override the parameters specified in the configuration files.

BACKUP METHODS

mysql-zrm backups can be either full or incremental. Full backups can be logical backups or raw backups. Logical backups contain SQL statements to recreate the database. Raw backups are actual copies of the database files.

When logical backup is specified, the utility uses only mysqldump for backing up specified databases and tables.

When raw backup is specified, the utility has the ability to decide which backup mechanism to use for a particular database. If there are no transactional storage engine based tables in the specified database, then it uses mysqlhotcopy for backing up that database. Otherwise, it uses mysqldump for backing up the specified database.

Snapshot backups are discussed in the Snapshot Options section of this man page. When replication is specified and if the MySQL server is a replication slave, then it will also backup all replication related files.

When destination specified then the backups are stored in a sub-directory under the specified directory. Default path is /var/lib/mysql-zrm. If the destination specified does not exist, it will return an error and exit; the utility does not create the directory.

An index file is also created in the backup directory. The index is a file that contains the details of what was backed up, how much data was backed up, what parameters were used to backup the data, how much time it took to backup etc.

When an incremental backup is specified, the following options are ignored: backup-mode, snapshot-plugin, all-databases, databases, database, tables

OPTIONS

You can select a backup set by name on the command line, or select source database(s), backup methods, plugins, etc. by using the options described in the Options subsections that follow. You can also specify mysql-zrm-backup options in a configuration file specified using the --options switch.

--backup-set name
Specify backup-set for the backup run. This is a Command line option only and cannot be specified in the configuration file. The default value is BackupSet1. If such a value is already present, it will be used.
--options-file conf_file
Set the file along with full pathname that contains mysql-zrm-backup configuration options; this file has the same format as mysql-zrm.conf(5).
--help
Displays the help message and exits. This command-line-only parameter cannot be specified in the configuration file.

Backup Source Options

--all-databases
Opt for all databases to be backed up or restored. This is the default if database or databases are not specified.
--databases "name1 name2 ..."
List the databases to backup or restore.
--database name [--tables "name1 name2 ..."]
List the tables that should be backed up. This option is valid during the backup operation only.
--exclude-pattern "pattern"
All databases or tables that match the pattern are not backed up. If --all-databases or --databases are specified, the exclude pattern applies to database names. If --database is specified, the exclude pattern applies to table names. Wildcard characters * (match one or more characters), ? (match one character), [] (match one of the characters specified within parenthesis) and | (match one of the patterns) are supported. For example: Exclude pattern "*_db|dbase[123]|abcd" will match names tom_db, dbase1, dbase2, abcd. All tables or databases that match this pattern will be excluded. Character # is not permitted in the exclude pattern.
--routines|--no-routines
If --routines is specified then mysqldump will dump stored routines. The default is --no-routines. This parameter is optional.
--mysql-binpath mysql_command_dir
Set the full path where mysql binary commands are installed. For example: /opt/lampp/bin.
--mysql-binlog-path mysql_binary_log_location
Set the full path of the location of mysql binary logs. For example: /var/log/mysql
--config-file-list="list_file_path"
Intended for configuration file backup, this optional parameter lets you specify a list of files to backup during a full backup of any type (snapshot, logical, or raw). The list_file_path is the path to the file that contains the list of configuration files to back up. The list itself should specify full pathnames, separated by newlines.
The config-file-list is processed after the main data has been backed up, and the list file itself is copied to the destination directory along with the configuration files. The backup index will record the config-file-list parameter.
Files specified by config-file-list will be restored during a full restore to their original locations, preserving permissions and ownership.

MySQL Access Options

--user user
Specify MySQL backup/restore user. If no user is specified here, ensure that you have the MySQL user specified in the my.cnf or --options file hasthe necessary privileges.
--password password
Specify password for the MySQL user. If you wish to prevent the password from being sent in plaintext over the network, you may wish to configure the MySQL username and password using the MySQL my.cnf or --options file.
--host hostname
Specify MySQL server host name or IP name.
--port portnumber
Specify MySQL server port.
--socket name
Specify socket file to use for communication with MySQL server.
--ssl-options "MySQL ssl options"
Set any --ssl* options that MySQL supports. Refer to MySQL Documentation for SSL options details.

Backup Method Options

--backup-level level
Choose 0 for full backup. Choose 1 for incremental back up. Default is full backup.
--backup-mode raw|logical
Database/tables that use a transactional engine cannot be backed up by mysqldump. They should be backed up by mysqlhotcopy. Choose raw to use mysqlhotcopy to back up the database/tables that use a transactional engine. Choose logical to use mysqldump to back up the database/tables that don’t use a transactional engine. Default value is raw.
--logical-parallel 0|1
Select 0 if mysqldump should be used. Select 1 if mydumper multi-threaded logical backup method should be used for full backup. Default is mysqldump backup method.
--synchronous-checksum | --no-synchronous-checksum
Specifies whether a checksum should be produced during the backup process. Default is no-synchronous-checksum, in which case the checksum is produced only after the backup process is complete. Specifying --synchronous-checksum significantly increases overall backup times.
--replication | --noreplication
Choose replication to backup replication files. They will be backed up only if the host is a replication slave. Default option is --noreplication.
--default-character-set char_set_name
The default character set that mysqldump should use. If not specified utf8 is used. This parameter is optional.
--single-transaction [always|never|only-innodb]
The single-transaction option controls under what circumstances MySQL for ZRM will pass the --single-transaction option setting to mysqldump when executing a database dump. Specifying always causes --single-transaction to always be passed, never causes the --single-transaction to never be passed, and only-innodb specifies that --single-transaction will only be passed during innodb-only backups. For example, if only-innodb is specified, the --single-transaction will not be passed for backups that include myisam or other non-transactional tables.
--extra-mysqldump-options="mysqldump_options"
Use this parameter to pass any extra mysqldump options when ZRM for MySQL executes a mysqldump. For example, if you require mysqldump to be run with the --flush-logs and --no-create-info options, specify --extra-mysqldump-options="--flush-logs --no-create-info" in the mysql-zrm.conf file.
--extra-mydumper-options="mydumper options"
Use this parameter to pass any extra mydumper options when ZRM for MySQL executes mydumper command. For example, if you want to run mydumper command to run with --threads and --regex options, specify --extra-mydumper-options="--threads=4 --regex=Regular expression for matching db.table".
--compress-data-during-transport
If the value is set to 1, the backup is compressed when it transferred from MySQL server to ZRM server. Setting the value to zero, no compression is performed. No compression is performed by default. Data compression during transfer is independent of the Backup compression parameter.

Plugin Options

--pre-backup-plugin plugin
(Optional) Specify the plugin along with full pathname to be called before a backup run. All pre-backup plugin scripts must accept the following command-line parameters (which are passed to the plugin using --pre-backup-plugin-options)
  • --all-databases : Use when all databases on the 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.
--pre-backup-plugin-options "option1 option2 ..."
Pass options to the pre-backup-plugin as one of the command line arguments.
--copy-plugin plugin
Specify plugin along with full pathname to be used for transferring backup files to and from a remote MySQL server to the machine running ZRM for MySQL. Use this option when
1. remote incremental backup is required
2. copying replication related files from a remote machine
3. execute mysqlhotcopy (MySQL command) and copy the data from the remote machine
4. restore data to a remote machine using mysqlhotcopy(MySQL command). This parameter is optional.
--ssh-user user
Specifies the user to be used for SSH. This parameter is only used by the ssh-copy.pl plugin. The user specified should either be the user mysqld runs as or root. If not specified the default value used is mysql. This parameter is optional.
--ssh-identify-file filename
Selects a file from which the identity (private key) for RSA or DSA authentication is read. This identity file is used by the ssh-copy.pl plugin for authentication.
--remote-mysql-binpath path
Full path where mysql binaries are installed on the remote machine. For example: /opt/lampp/bin. This parameter is only used by the socket-copy.pl and ssh-copy.pl plugins. If not specified, the default value used is /usr/bin.
--socket-remote-port port
Port that the socket-plugin.pl should use to connect. This parameter is only used by the socket-copy.pl plugin. If not specified the default value used is 25300. This parameter is optional
--post-backup-plugin plugin
Set the plugin along with full pathname to be called after a backup run. This plugin is optional.
--post-backup-plugin-options "option1 option2 ..."
Pass options to the post-backup-plugin as one of the command line arguments

Compression and Encryption Options

--compress | --no-compress
Choose whether the files should be compressed using the utility specified by the --compress-plugin. The default is --no-compress.
--compress-mysqldump-onthefly | --no-compress-mysqldump-onthefly
Choose whether to compress the data dumped by mysqldump on the fly. Default compression command used is gzip. Default is no compression on the fly.
--compress-plugin plugin_name
Use plugin_name to specify a path to the utility to use for compression. The default is gzip.
--no-encrypt | --encrypt
Choose whether the files should be encrypted using the utility specified by the --encrypt-plugin. The default is --no-encrypt.
--encrypt-plugin plugin_name [--decrypt-option [option]
Use plugin_name to specify a utility to use for compression along with its full path. If decrypt-option is not specified then the -d option of encrypt-plugin is used for decrypting.
--passfile filename
The file containing the passphrase for encryption. This parameter is used only by the encrypt-plugin.pl. If not specified the value used is /etc/mysql-zrm/.passphrase. This parameter is optional.

Snapshot Plugin Options

--snapshot-plugin plugin-pathname
Specifies the plugin to be used for snapshot operations. The default is the lvm-snapshot plugin.
[--check-innodb | --no-check-innodb]
Specifies whether to check for innodb shared data and log files to back up during every snapshot-based based backup. If --check-innodb is specified, innodb shared data and logs are only backed up if the databases being backed up use innodb. If --no-check-innodb is specified (the default), the innodb databases and logs are backed up regardless of whether they are relevant. The default value is --no-check-innodb, which means innodb shared data and logs are always copied if the innodb engine is enabled in MySQL. In most cases, --no-check-innodb provides the best performance.
--snapshot-size size
Set the size of the lvm-snapshot to be generated. For raw backups, each specified database is first checked ensure that it is on a LVM volume, and then a snapshot of the specified size is created and used to backup the database. If the specified database is not on a LVM volume, either mysqlhotcopy or mysqldump will be used to create the backup based on the logic specified by the --backup-mode option. This option is ignored if the --backup-mode is logical.
--backup-type regular | --backup-type quick
Choose the method of snapshot backup. The regular option specifies that the snapshot should be copied into a standard ZRM for MySQL backup archive. The quick option specifies that the snapshot itself should be used as a nearline backup. Quick backups are convenient as they provide near-instantaneous restores, but because they remain on the MySQL server they do not protect against media or server failure. Note that if the quick option is specified, the compress and encrypt options are ignored, and the backup size will always be zero. In addition, no checksums are performed, which means that quick snapshot backups cannot be verified.

Backup Destination Options

--backup-name image_name
Specify a unique name to store the backup. If a name is not specified or it already exists in the --destination dirpath (see the next option), the backup image will be named with the current date and time.
--destination dir_path
Specify the directory to store the backup. The default location is /var/lib/mysql-zrm/. If a directory other than the default is specified, you must create it manually on both the MySQL server and the ZRM server, and it must have permissions set to allow read/write access to the mysql backup user.

Logging and Notification Options

--mailto address
Set the address to which a backup report will be sent.
--mail-policy always|never|only-on-error
Mail policy determines when email notification should be sent. The value always is the default value and email notification is sent for all events. If the mail policy is set to never, email notification is not sent for all events. If the mail policy is set to only-on-error, notification is sent when there is a backup failure if mailto is specified.
--comment "Comment string"
Insert a comment or a note about the backup run. This can be used to tag backup runs and the note can retrieved using the mysql-zrm-reporter(1) utility.
--quiet | --no-quiet
Choose quiet to suppress display of log messages on stdout. Choose no-quiet to display the log messages stdout. Default is --no-quiet.
--verbose
Provide verbose output in the log. Default is that verbose is turned off and a terse output is generated.

FILES

/var/lib/mysql-zrm
Default directory for storing all backup data.
/etc/mysql-zrm/backup set name/mysql-zrm.conf
Default location for the backup-set configuration file used by the mysql-zrm-backup utility.

RETURN VALUES

On success, zero is returned. On error, non-zero value is returned.

SEE ALSO

mysql-zrm(1), mysql-zrm-manage-backup(1), mysql-zrm-backup(1), mysql-zrm-restore(1), mysql-zrm-check(1), mysql-zrm-getconf(1), mysql-zrm-list(1), mysql-zrm-parse-binlogs(1), mysql-zrm-purge(1), mysql-zrm-extract-backup(1), mysql-zrm-verify-backup(1), mysql-zrm-scheduler(1), mysql-zrm-reporter(1), mysql-zrm.conf(5), mysqldump(1), mysqlbinlog(1), mysql(1), lvm(8).

AUTHOR

Zmanda Inc. (http:/www.zmanda.com/)