It is possible to do selective restoration of a backup set from backups using mysql-zrm utility and the logs. ZRM for MySQL does not support restoring to live databases. All applications using the database that is being restored must be stopped.
The following types of selective restore can be done
- Restoring one or more databases from a full or incremental backup containing multiple databases
- Selective restoration of database events from an incremental backup
Selective restore is a multi-step process.
Restoring One or More Databases from a Full or Incremental Backup
mysql-zrm utility can be used to restore specific databases from a full/incremental backups using the restore action.
Example: Selective restoration of databases - db1 and db2 of backup set backupSet1 :
# mysql-zrm --action restore --backup-set backupSet1 \ --source-directory /var/lib/mysql-zrm/backupSet1/20060829140710 \ --databases "db1 db2" 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 of Database Events
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 during 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 utility option ( --action parse-binlogs ) parses binary logs to display the log positions and timestamp of database events. The binary logs output contains the binary log filename, position in the log, timestamp, type of event and actual database event. Note: Binary logs contain only those database events that modify data or data attributes.
This information should form the basis of mysql-zrm restore action.
The backup directory location for the incremental backups can be found using mysql-zrm-reporter command.
Example:
mysql-zrm command for displaying binary logs from incremental backup directory /var/lib/mysql-zrm :
# mysql-zrm --action parse-binlogs --source-directory=/var/lib/mysql-zrm
Sample output:
----------------------------------------------------------------------------
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 |
Note: It is possible to restore data from databases containing tables using different engines as shown above. The right restore point depends upon database events that have triggered the need to restore.
The MySQL binary logs can have lots of database events (millions of database events/transactions in many cases). The binary log parser plugin can help in the filtering the database events of interest.
Restoration Based on Timestamp: Point-In-Time Recovery
ZRM for MySQL can do point-in-time recovery either till a particular time or starting from a particular time.
For example:
A full backup of a backup set is done at 6 am and is followed by an incremental backup at 10 am. It is possible to recover the full backup and then recover the database to the state at 9 am from the incremental backup.
Example: Restoration of backup set backup to the state at 9 am on Aug 30, 2006.
# mysql-zrm --action restore --backup-set backup \ --source-directory /var/lib/mysql-zrm/backup/20060830020843 \ --stop-datetime "200608300900" MySQL server has been shutdown. Please restart after verification.
Restoration Based on Log Position
ZRM for MySQL 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 an errant SQL statement causes the drop of a database table between last full backup and last incremental backup. To recover from the error, first the last full backup should be restored. The incremental backup is then selectively restored after the last full backup till the errant statement.
- The mysql-zrm utility 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.
- It is followed by another restore skipping the errant statement.
- 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.
- The --stop-position refers to the log position in the last log file.
- The mysql-zrm utility also supports --offset parameter to specify an offset that can be skipped to skip N entries from the first log file.
- Specify the binary log file using --bin-logs option when there are more than one binary log file in the incremental backup.
Example:
Restore selectively starting from log position 4 to log position 22 incremental backup stored in /var/lib/mysql-zrm/backup/2006830020843 directory.
# 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.
Note: During database restoration MySQL server is stopped by the mysql-zrm utility.
Example:
Restore selectively from multiple binary log files using single connection to the MySQL server. Restore log position 100 onwards from /var/lib/mysql-zrm/backupset1/20060818121532/mysql-bin.00001
# 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 MySQL server has been shutdown. Please restart after verification.