|
|
| (24 intermediate revisions by the same user 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.2
| | 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.X 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 - MySQL ZRM bug notification mailing list.
| | # [[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.
| |
| ; Backup verification : Verification can be performed on backed up data.
| |
| | |
| === 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 notification : 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.
| |
| | |
| * MySQL ZRM requires MySQL client commands to be installed on the machine where MySQL ZRM is running. This is required even if MySQL ZRM is backing up remote MySQL servers. MySQL ZRM depends on the following commands:
| |
| ** mysqladmin
| |
| ** mysqlshow
| |
| ** mysqlhotcopy
| |
| ** mysqldump
| |
| ** mysqlbinlog
| |
| ** mysql
| |
| | |
| * The MySQL client commands installed on MySQL ZRM machine should be compatible with the MySQL server. It is preferable to use the same version of MySQL on MySQL servers and on the MySQL ZRM machine (if they are running on different machines.
| |
| | |
| * 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.2-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
| |
| |-
| |
| |Libraries||/usr/lib/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.2-1
| |
|
| |
| * Check mysql-zrm man page
| |
| # man mysql-zrm
| |
| | |
| == Do I need to make changes to MySQL database configuration? ==
| |
| | |
| There are some changes required to MySQL database and server configuration for the following configurations:
| |
| | |
| ; Different user roles : Create a new [[#MySQL backup user|MySQL backup user]] as well as restore user. This is required only if the user with the root privileges will not be doing backup and/or recovery of MySQL databases.
| |
| | |
| ; Incremental backups : Enable [[#Binary logs|binary logs]] on the MySQL server if incremental backups of MySQL database have to be performed.
| |
| | |
| ; Secure communication between MySQL server and MySQL ZRM : Enable [[#SSL support on MySQL server|SSL on the MySQL server]].
| |
| | |
| === MySQL backup user ===
| |
| | |
| MySQL backup user should be created and sufficient privileges to do backups and recovery. Minimal set of MySQL privileges for
| |
| ; backup user : LOCK TABLES, SELECT, FILE, RELOAD
| |
| ; restore user : CREATE, DROP, INDEX, SHUTDOWN, INSERT, ALTER
| |
| | |
| If backups are being done from MySQL replication slave, backup user should have REPLICATION CLIENT, SUPER in addition to the above mentioned ones.
| |
| | |
| MySQL command to grant privileges for a user:
| |
| mysql> GRANT <privileges> ON <TABLE> {tbl_name | * | *.* | db_name.*} TO <user name> identified by PASSWORD <user password>;
| |
| Example: Command that grants minimal user privileges for backup user ''dba-backup'' to backup database ''expenses'' remotely from domain ''company.com'':
| |
| mysql> GRANT LOCK TABLES, SELECT, FILE, RELOAD
| |
| -> ON expenses.*
| |
| -> TO 'dba-backup'@'company.com'
| |
| -> IDENTIFIED BY 'obscure';
| |
| | |
| It is recommended to create a backup user instead of using MySQL ''root'' user. If it is necessary to use different MySQL user for backup and restoration, backup user should be specified in ''mysql-zrm.conf'' for the backup set. The restore user can be specified in the [[mysql-zrm]] command line using ''--user'' and ''--password'' options.
| |
| | |
| ===Binary logs===
| |
| | |
| To do MySQL 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 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 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 mysql-zrm 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 default is 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 [[Zmanda_Recovery_Manager_for_MySQL#MySQL_backup_user|MySQL backup user]] section for more information on granting privileges. If this field is not specified, information from MySQL client configuration 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 configuration 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 [[Zmanda_Recovery_Manager_for_MySQL#SSL_support_on_MySQL_server|SSL support on MySQL server]] section of this page.
| |
| | |
| ; msql-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''
| |
| | |
| '''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.
| |
| | |
| [[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 of [[mysql-zrm-scheduler]] should be used after MySQL ZRM configuration changes. The backup ''now'' option also checks the MySQL ZRM configuration before doing the backup.
| |
| | |
| 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
| |
| last-backup=/var/lib/mysql/dailyrun/20060828101657
| |
| backup-status=Backup succeeded
| |
| | |
| === 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. [[Zmanda_Recovery_Manager_for_MySQL#Binary_logs|Binary logging should be enabled]] in the MySQL server. Incremental backups uses binary logging and the binary log backups are not restricted to the list of databases or tables specified in the backup set. MySQL binary logs contain 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 occurred. 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 --where 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
| |
| | |
| === Verification of backup images ===
| |
| | |
| MySQL ZRM has the capability of verifying the backup images. It verifies the consistency of backup using backup checksums. It is a recommended practice to verify the newly created backup image after a backup run. Best practice is to verify the backup images for all backup sets on a regular basis.
| |
| | |
| The following command verifies the backup images of the last backup run for the backup set ''backup'':
| |
| # mysql-zrm --action verify-backup --backup-set backup
| |
| | |
| The following command verifies the specific backup run of backup set ''backup'':
| |
| # mysql-zrm --action verify-backup --backup-set backup \
| |
| --source-directory /var/lib/mysql-zrm/backup/20060829093000
| |
| | |
| mysql-zrm returns 0 if the verification is successful.
| |
| | |
| === 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: mysql-zrm log file entries from a restore of incremental backup:
| |
| | |
| 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 from a backup report? ==
| |
| | |
| MySQL ZRM stores policy, performance, database/tables information in a database. All the information is organized in terms of backup run. The [[mysql-zrm-reporter]] tool can be used to extract the information stored about the backup runs.
| |
| | |
| All backup parameter field names can be passed as parameters to ''fields'' argument to display select set of fields. If select set of field names are not provided as parameter, ''backup-set'', ''backup-date'', ''backup-directory'', ''backup-level'' and ''backup-status'' are displayed. The reporter tool can also search for a specific field value.
| |
| | |
| The list of information about a backup run that are available:
| |
| | |
| ===Backup parameters===
| |
| | |
| ; backup-set : Name of the Backup Set
| |
| | |
| ; comment : Database administrator comments about the backup set or the backup run
| |
| | |
| ; backup-date : Date and time stamp of when the backup was done.
| |
| | |
| ; mysql-version : Version of the MySQL server used to backup the backup set. When MySQL server is being upgraded to a newer version, this field can be used to check the MySQL version of the backup images.
| |
| | |
| ; backup-directory : The location of backup directory on the machine where MySQL ZRM is running.
| |
| | |
| ; backup-level : Backup level (full or incremental). Full backup is 0. Incremental backup is level 1.
| |
| | |
| ; retention-policy : Retention time for the backup image. The backup images will be removed after retention time since the ''backup-date'' value. The unit can be D (days), W (weeks), M (months), Y (years). 30 days in a month and 365 days in a year are assumed.
| |
| | |
| ===Tables/Databases that were backed up===
| |
| | |
| ; raw-databases : List of databases that have raw backups - backups done using the ''mysqlhotcopy'' command. If the ''raw-tables'' field is present, all the tables listed in ''raw-tables'' belong to the database in this parameter.
| |
| | |
| ; raw-tables : List of tables backed up using the ''mysqlhotcopy'' command. All the tables listed in this parameter belong to the database in the ''raw-databases'' field.
| |
| | |
| ; raw-databases-snapshot : List of databases backed up using LVM snapshots.
| |
| | |
| ; raw-tables-snapshot : List of tables backed up using LVM snapshots. All the tables listed in this parameter belong to the database in the ''raw-tables-snapshot'' field.
| |
| | |
| ; 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 - namely ''master.info'' and ''relay-log.info''
| |
| | |
| ; slave-load-files : Names of SQL_LOAD* files that were backed up
| |
| | |
| ; incremental : Names of Binary log files that are part of incremental backup.
| |
| | |
| ===Status and performance of backup run===
| |
| | |
| ; backup-time : Time taken by the backup run. Format is HH:MM:SS
| |
| | |
| ; backup-size : Size of backup image in MB.
| |
| | |
| ; 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. All modified database pages written from memory to the disk during backup for some backup methods. Format is HH:MM:SS.
| |
| | |
| ; backup-status : Status of the backup run. The values can be ''Backup Failed'', ''Backup done with errors'', and ''Backup succeeded''.
| |
| ** ''Backup Failed'' means there was a fatal error and backup was not completed.
| |
| ** ''Backup succeeded'' means the backup was successful.
| |
| ** ''Backup done with errors'' means there were errors during backup, not all tables/databases in the backup set were not backed up. To find out which databases or tables were backed up successfully, see mysql-zrm log file.
| |
| | |
| === Predefined backup reports ===
| |
| | |
| Predefined backup reports can using ''--show'' option to [[mysql-zrm-reporter]] tool. The list of predefined backup reports available are shown in the table below:
| |
| | |
| {| border="1"
| |
| !Backup report name!!Description!!Information available
| |
| |-
| |
| |backup-status-info||Status of backup runs||backup-set, backup-date, backup-level, backup-status, backup-comment
| |
| |-
| |
| |backup-method-info||Backup methods used||backup-set, raw-databases, raw-databases, logical-databases
| |
| |-
| |
| |backup-retention-info||How long are the backups retained?||backup-set, backup-date, backup-level, backup-size, retention-policy
| |
| |-
| |
| |backup-performance-info||Backup performance and impact on application||backup-set, backup-date, backup-level, backup-size, backup-time, read-locks-time, flush-logs-time
| |
| |-
| |
| |restore-full-info||Information for doing full/incremental restoration||backup-set, backup-date, backup-level,backup-directory
| |
| |-
| |
| |restore-incr-info||Information for selective restoration||backup-set, backup-date, incremental
| |
| |-
| |
| |replication-info||Replication files backed up||backup-set, backup-date, replication, slave-load-files
| |
| |}
| |
| | |
| Specific backup runs can be selected from the predefined backup reports using ''--where'' option.
| |
| | |
| Example: A backup status report
| |
| # mysql-zrm-reporter --where backup-set=backupSet1 --show backup-status-info
| |
| backup_set backup_date backup_level backup_status comment
| |
| -------------------------------------------------------------------------------------------
| |
| backupSet1 20060909100021 0 Backup Before application upgrade
| |
| succeeded
| |
| backupSet1 20060909100123 0 Backup After application upgrade
| |
| succeeded
| |
| backupSet1 20060909100300 0 Backup Nightly
| |
| succeeded
| |
| | |
| === Formatting backup reports ===
| |
| | |
| [[mysql-zrm-reporter]] report format can be controlled by specifying format in ''/etc/mysql-zrm/mysql-zrm-reporter.conf''. | |
| | |
| The configuration file allows user to specify the format for each backup parameter. User can specify the size of each parameter value and alignment in the backup reports. The configuration file is used for
| |
| predefined reports as well as custom reports generated by the user.
| |
| | |
| Each line in the file is for a specific backup run parameter. The syntax for each line is
| |
|
| |
| fieldname=width,alignment
| |
| | |
| ; ''width'' : the number of characters for the value. If the value exceeds the number of characters specified, the value will wrap around into multiple lines
| |
| | |
| ; ''alignment'' : values can be left ("<") or (">") aligned
| |
| | |
| == How do I recover data when there is a failure or data loss? ==
| |
| | |
| 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.
| |
| | |
| MySQL ZRM does not support restoring to live databases. All applications using the database that is being used must be stopped.
| |
| | |
| 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 --fields backup-set,backup-date,backup-level,backup-directory \
| |
| --where 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
| |
| | |
| After restoring the database or tables in the database, it is important to [[#Verifying_restoration|verify the restored database contents]] before restarting the MySQL server.
| |
| | |
| === 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.
| |
| | |
| === Selective restoration ===
| |
| | |
| Selective restoration of a backup set is possible only from an incremental backup. The prior full backup or incremental backup should have been restored before attempting selective restoration.
| |
| | |
| It is important to determine the list of database events that should included or excluded from selective restoration. The next section talks about how to browse incremental backups (MySQL binary logs) to determine the database events in the binary logs.
| |
| | |
| The database events that have to be selectively restored can be specified in terms of database events or in terms of time when the events actually occurred.
| |
| | |
| ==== Browsing MySQL binary logs ====
| |
| | |
| The [[mysql-zrm]] provides an option (''--action parse-binlogs'') to parse binary logs to determine the log positions and timestamp of database events. Information from [[mysql-zrm]] tool parse binary logs output can be used as input for [[mysql-zrm]] restore action. The binary logs output contains the binary log filename, position in the log, timestamp, type of event and actual database event. Binary logs only contain database events modify data or data attributes.
| |
| | |
| The backup directory location for the incremental backups can be found using [[mysql-zrm-reporter]] command.
| |
| | |
| The following mysql-zrm command displays binary logs from incremental backup directory ''/var/lib/mysql'':
| |
| | |
| # mysql-zrm --action parse-binlogs --source-directory=/var/lib/mysql
| |
|
| |
| Sample output from parse binary logs command:
| |
| | |
| ----------------------------------------------------------------------------
| |
| Log filename | Log Position | Timestamp | Event Type | Event
| |
| ----------------------------------------------------------------------------
| |
| /var/lib/mysql/my-bin.000015 | 9762 | 06-09-19 06:20:03 | Query | CREATE TABLE `table_InnoDB` ( `name` varchar(20) default NULL, `age` int(3) default NULL, `address` varchar(200) default NULL, `sex` char(1) default NULL, `DOB` date default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
| |
| /var/lib/mysql/my-bin.000015 | 10058 | 06-09-19 06:20:03 | Query |
| |
| /var/lib/mysql/my-bin.000015 | 10178 | 06-09-19 06:20:03 | Query | INSERT INTO `table_InnoDB` VALUES ('1kkg',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg1',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg2',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg3',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg4',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg5',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg6',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg7',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg8',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg9',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg10',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg11',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09');
| |
| /var/lib/mysql/my-bin.000015 | 11013 | 06-09-19 06:20:03 | Xid = 4413 | COMMIT;
| |
| /var/lib/mysql/my-bin.000015 | 11040 | 06-09-19 06:20:03 | Query |
| |
| /var/lib/mysql/my-bin.000015 | 11159 | 06-09-19 06:20:03 | Query | DROP TABLE IF EXISTS `table_MyISAM`;
| |
| /var/lib/mysql/my-bin.000015 | 11263 | 06-09-19 06:20:03 | Query | CREATE TABLE `table_MyISAM` ( `name` varchar(20) default NULL, `age` int(3) default NULL, `address` varchar(200) default NULL, `sex` char(1) default NULL, `DOB` date default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
| |
| /var/lib/mysql/my-bin.000015 | 11559 | 06-09-19 06:20:03 | Query |
| |
| /var/lib/mysql/my-bin.000015 | 11679 | 06-09-19 06:20:03 | Query | INSERT INTO `table_MyISAM` VALUES ('1kkg',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg1',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg2',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg3',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg4',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg5',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg6',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg7',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg8',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg9',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg10',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg11',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09');
| |
| /var/lib/mysql/my-bin.000015 | 12514 | 06-09-19 06:20:03 | Query |
| |
| /var/lib/mysql/my-bin.000015 | 12633 | 06-09-19 06:20:03 | Stop |
| |
| /var/lib/mysql/my-bin.000016 | 4 | 06-09-19 06:20:14 | Start: binlog v 4, server v 5.0.21-max-log created 060819 6:20:14 at startup | ROLLBACK;
| |
| /var/lib/mysql/my-bin.000016 | 98 | 06-09-19 06:25:10 | Stop |
| |
| /var/lib/mysql/my-bin.000017 | 4 | 06-09-19 19:15:13 | Start: binlog v 4, server v 5.0.21-max-log created 060819 19:15:13 at startup | ROLLBACK;
| |
| /var/lib/mysql/my-bin.000017 | 98 | 06-09-19 19:35:57 | Query | FLUSH TABLES /*!32323 `kkg123`.`table_ARCHIVE`, `kkg123`.`table_BerkeleyDB`, `kkg123`.`table_InnoDB`, `kkg123`.`table_MyISAM` */;
| |
| /var/lib/mysql/my-bin.000017 | 285 | 06-09-19 20:08:28 | Rotate to my-bin.000018 pos: 4 |
| |
| | |
| ==== 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 till 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.
| |
| | |
| ==== 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.
| |
| | |
| 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:
| |
| | |
| # 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
| |
| | |
| === Verifying restoration ===
| |
| | |
| After restoration of the database, MySQL ZRM shuts down the MySQL server. It is important to check the database(s)/table(s) that were restored before restarting the MySQL server. SQL command ''CHECK TABLE'' can be used for consistency checking. Use of EXTENDED option is recommended. EXTENDED option does a full key lookup for all rows in the table and will take significant time for a large database.
| |
| | |
| mysql> CHECK TABLE <table1>, <table2> EXTENDED;
| |
| | |
| No other application must be using the database during table consistency check.
| |
| | |
| == 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. The predefined ''backup-performance-info'' report is a useful tool to tune backup process or switch to a different backup method.
| |
| | |
| An example command 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 58MB 15 seconds.
| |
| backupSet2 20060829140819 0 59MB 26 seconds.
| |
| backupSet2 20060829141001 0 78MB 40 seconds.
| |
| | |
| Using [[mysql-zrm-reporter]] tool, you can figure out what backup method was used to do the backup (''backup-method-info'' report). 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 analyze the backups. The ''backup-performance-info'' report is a good starting point to analyze the bottlenecks in the backup strategy. 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.
| |
| | |
| Example: Backup performance report
| |
| | |
| # /usr/bin/mysql-zrm-reporter --where backup-set=backupSet1 --show backup-performance-info
| |
| backup_set backup_date backup_level backup_size backup_time read_locks_time flush_logs_time
| |
| -------------------------------------------------------------------------------------------------
| |
| backupSet1 20060909100021 0 4.26 MB 00:00:06 00:00:03 00:00:00
| |
| backupSet1 20060909100123 0 4.26 MB 00:00:03 00:00:02 00:00:00
| |
| backupSet1 20060909100300 0 4.26 MB 00:00:03 00:00:02 00:00:00
| |
