|
|
| (39 intermediate revisions by 2 users not shown) |
| Line 1: |
Line 1: |
| == What is Zmanda Recovery Manager for MySQL ==
| | '''Please see [[Zmanda Recovery Manager for MySQL Users Manual]] for release 1.1.3 and later releases |
| | ''' |
|
| |
|
| 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. | | 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 reports on the backup of MySQL Databases. MySQL ZRM has plugin interfaces that allow ZRM to be customized to the IT environment. |
|
| |
|
| The current release is 1.0.1
| | MySQL ZRM supports backups of MySQL databases residing on the local server as well as remote servers. It does backup to direct attach or SAN attached disk storage or NAS/SAN storage appliances. Following diagram shows a server running MySQL ZRM backing up 2 MySQL servers with multiple MySQL databases and the local MySQL server. |
|
| |
|
| == 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
| | [[Image:MySQL_ZRM.png|center]] |
| likely work in other Linux and Unix platforms.
| |
|
| |
|
| MySQL ZRM RPMs and Source tar ball are available at [http://www.zmanda.com/downloads.html 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.
| | This manual discusses installation, configuration, backup, recovery and reporting features of MySQL ZRM. This document was prepared for ZRM for MySQL release 1.1.3 |
|
| |
|
| == I have questions/suggestions/bug fixes. How do I contact other users/developers? ==
| | # [[What does MySQL ZRM run on?]] |
| | | # [[I have questions/suggestions/bug fixes. How do I contact other users/developers? ]] |
| === Forums ===
| | # [[What can MySQL ZRM do?]] |
| | | # [[What will be implemented in future releases?]] |
| Community support for MySQL ZRM is available on [http://forums.zmanda.com/ MySQL ZRM] forums. Any one interested in MySQL ZRM can register and participate.
| | # [[How do you install MySQL ZRM?]] |
| | | # [[Quick start example]] |
| === Mailing lists ===
| | # [[Do I need to make changes to MySQL database configuration?]] |
| | | # [[How do I configure MySQL ZRM?]] |
| There are three mailing lists for the MySQL ZRM project.
| | # [[Finally, Can I do MySQL backups?]] |
| | | # [[Backups failed. What do I do?]] |
| * mysql-zrm-announce -Mailing [moderated] list used for announcing MySQL ZRM related information. This is a low traffic mailing list
| | # [[Backup compression and encryption]] |
| * mysql-zrm-users - Mailing list to discuss MySQL ZRM deployments, questions and enhancement requests. All users and developers should subscribe to this list.
| | # [[What information can be obtained from a backup report?]] |
| * mysql-zrm-bugs - Mailing list that is notificed of bugs regarding the MySQL ZRM
| | # [[How do I recover data when there is a failure or data loss?]] |
| | | # [[How did the MySQL ZRM do the job?]] |
| To subscribe to the mailing lists, please see http://www.zmanda.com/mailing-lists.php page.
| | # [[How to create custom plugins for MySQL ZRM?]] |
| | | # [[How do I tune MySQL backup performance?]] |
| === Bug reporting ===
| |
| | |
| Please report bugs in [http://forums.zmanda.com/bugzilla 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 [http://forums.zmanda.com/bugzilla 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 [http://www.zmanda.com/downloads.html 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:
| |
| | |
| {| border=1 align="center"
| |
| !!!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 [http://dev.mysql.com/doc/refman/5.0/en/binary-log.html 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 [http://dev.mysql.com/doc/refman/5.0/en/secure-using-ssl.html 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 [http://amanda.zmanda.com 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.
| |
| | |
| ; 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''.
| |
| | |
| ; 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.
| |
| | |
| ; 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
| |
| | |
| ; 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.
| |
| | |
| '''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 [[Zmanda_Recovery_Manager_for_MySQL#MySQL_backup_user|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.
| |
| | |
| ; 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 [[Zmanda_Recovery_Manager_for_MySQL#SSL_support_on_MySQL_server|SSL support on MySQL server]] section of this page.
| |
| | |
| '''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. 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.
| |
| | |
| === 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.
| |
| | |
| [[Zmanda_Recovery_Manager_for_MySQL#Scheduling Backups|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]]:
| |
| <br/>
| |
| *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:
| |
| # Run ''mysql-zrm-scheduler --query'' to list the current scheduled backup runs.
| |
| # 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 [[Zmanda_Recovery_Manager_for_MySQL#Backup_set_parameters|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 [http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html 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.
| |
