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 restored must be stopped.
Browsing backup images
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 information for restoring backups images (restore-info report) for a backup set backupSet1:
# /usr/bin/mysql-zrm-reporter -show restore-info --where backup-set=backupSet1
backup_set backup_date backup_level backup_directory
-------------------------------------------------------------------------------------------
backupSet1 Tue 29 Aug 2006 0 /var/lib/mysql-zrm/backupSet1/
02:07:10 PM PDT 20060829140710
backupSet1 Wed 30 Aug 2006 0 /var/lib/mysql-zrm/backupSet1/
01:56:15 PM PDT 20060830135615
backupSet1 Fri 15 Sep 2006 1 /var/lib/mysql-zrm/backupSet1/
10:16:13 AM PDT 20060915101613
backupSet1 Fri 22 Sep 2006 0 /var/lib/mysql-zrm/backupSet1/
10:19:05 AM PDT 20060922101905
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 backup \ --source-directory /var/lib/mysql-zrm/backup/20060829140710 MySQL server has been shutdown. Please restart after verification.
After restoring the database or tables in the database, it is important to verify the restored database contents before restarting the MySQL server.
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 |
Selective restoration of specific databases
If the backup image contains backups from multiple databases, the administrators can selectively restore specific databases that are present in the backup image. For example: The backup image was created using --all-databases (default option). The backup image contains the databases - db1, db2 and db3. The following command can be used to selectively restore database db1.
# mysql-zrm --action restore --source-directory /var/lib/mysql-zrm/backup/20060829140710 --databases "db1"
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 22 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/20060818121532/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.