|
|
| (9 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?]] |
| == How do I recover data when there is a failure or data loss? ==
| | # [[Finally, Can I do MySQL backups?]] |
| | | # [[Backups failed. What do I do?]] |
| 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.
| | # [[Backup compression and encryption]] |
| | | # [[What information can be obtained from a backup report?]] |
| MySQL ZRM does not support restoring to live databases. All applications using the database that is being used must be stopped. | | # [[How do I recover data when there is a failure or data loss?]] |
| | | # [[How did the MySQL ZRM do the job?]] |
| 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.
| | # [[How to create custom plugins for MySQL ZRM?]] |
| | | # [[How do I tune MySQL backup performance?]] |
| 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
| |
