|
|
| (10 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. |
|
| |
|
| == [[What does MySQL ZRM run on?]] ==
| | 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. |
|
| |
|
| == [[I have questions/suggestions/bug fixes. How do I contact other users/developers? ]]==
| |
|
| |
|
| == [[What can MySQL ZRM do?]] ==
| | [[Image:MySQL_ZRM.png|center]] |
|
| |
|
| == [[What will be implemented in future releases?]] ==
| |
|
| |
|
| == [[How do you install MySQL ZRM?]] ==
| | 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 |
|
| |
|
| == [[Do I need to make changes to MySQL database configuration?]] ==
| | # [[What does MySQL ZRM run on?]] |
| | | # [[I have questions/suggestions/bug fixes. How do I contact other users/developers? ]] |
| == [[How do I configure MySQL ZRM?]] ==
| | # [[What can MySQL ZRM do?]] |
| | | # [[What will be implemented in future releases?]] |
| == [[Finally, Can I do MySQL backups?]]==
| | # [[How do you install MySQL ZRM?]] |
| | | # [[Quick start example]] |
| == What information can be obtained from a backup report? ==
| | # [[Do I need to make changes to MySQL database configuration?]] |
| | | # [[How do I configure MySQL ZRM?]] |
| 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. | | # [[Finally, Can I do MySQL backups?]] |
| | | # [[Backups failed. What do I do?]] |
| 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.
| | # [[Backup compression and encryption]] |
| | | # [[What information can be obtained from a backup report?]] |
| The list of information about a backup run that are available:
| | # [[How do I recover data when there is a failure or data loss?]] |
| | | # [[How did the MySQL ZRM do the job?]] |
| ===Backup parameters===
| | # [[How to create custom plugins for MySQL ZRM?]] |
| | | # [[How do I tune MySQL backup performance?]] |
| ; 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
| |
