Zmanda Recovery Manager for MySQL: Difference between revisions
| Line 19: | Line 19: | ||
== [[Finally, Can I do MySQL backups?]]== | == [[Finally, Can I do MySQL backups?]]== | ||
== What information can be obtained from a backup report? | == [[What information can be obtained from a backup report?]] == | ||
== How do I recover data when there is a failure or data loss? == | == How do I recover data when there is a failure or data loss? == | ||
Revision as of 03:40, 19 September 2006
What is Zmanda Recovery Manager for MySQL
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.
What does MySQL ZRM run on?
I have questions/suggestions/bug fixes. How do I contact other users/developers?
What can MySQL ZRM do?
What will be implemented in future releases?
How do you install MySQL ZRM?
Do I need to make changes to MySQL database configuration?
How do I configure MySQL ZRM?
Finally, Can I do MySQL backups?
What information can be obtained from a backup report?
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 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