Cheap VPS & Xen Server

Residential Proxy Network - Hourly & Monthly Packages

How to perform a point in time restoration using ZRM for MySQL

ZRM for MySQL is a powerful, flexible and robust backup and recovery solution for MySQL databases for all storage engines. With ZRM for MySQL a Database Administrator can automate logical or raw backup to a local or remote disk. In this How To, we attempt to explain how to recover from an user error at any given point in time.

Our Scenario:

At approximately 2:30pm there were 5 tablespaces added into the MovieID table. At 3pm you get a call from a user, who was trying to delete some unused tablespaces but ended up deleting the last 5 that he just added. The last full backup you performed was the night before at 7pm. How do you recover back to right before the last 5 tablespaces were deleted? In this case we will demonstrate a point in time restore.


  • More information on ZRM for MySQL is available here.
  • To know more about configuring ZRM for MySQL you can look at
  • For this How To, we use ZRM for MySQL version 1.1.1.
  • To perform incremental backups the binary logging option must be turned on. Edit the /etc/my.cnf file and add the following line under the [mysqld] section:

    You will have restart your mysql daemon before this goes into effect.

1. We will verify that the last full backup ran successfully last night.

-bash-3.1# mysql-zrm-reporter -show restore-info –where backup-set=dailyrun

backup_set backup_date backup_level backup_directory
dailyrun Wed 18 Oct 2006 07:07:08 PM PDT 0 /var/lib/mysql-zrm/dailyrun/20061018190708

We can see above that the last full did run successfully last night at 7:07pm. 2. So now we will run an incremental backup manually to record all the changes between the last backup and now, by typing:

-bash-3.1# mysql-zrm-scheduler –now –backup-set dailyrun –backup-level 1

3. Next we will parse through the binary logs backed up in the last incremental backup.

-bash-3.1# mysql-zrm –action parse-binlogs –source-directory /var/lib/mysql-zrm/dailyrun/20061019151937 –backup-set dailyrun

Log filename | Log Position | Timestamp | Event Type | Event
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 4 | 06-11-19 14:09:58 | Start: binlog v 4, server v 5.0.22-log created 061019 14:09:58 |
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 98 | 06-11-19 14:34:27 | Query | use movies; INSERT INTO `MovieID` (`MovieID`, `Year`, `MovieTitle`) VALUES (‘17786’, ‘1999’, ‘Sopranos: Season 1 Disc 1’);
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 272 | 06-11-19 14:35:46 | Query | INSERT INTO `MovieID` (`MovieID`, `Year`, `MovieTitle`) VALUES (‘17787’, ‘1999’, ‘Sopranos: Season 1 Disc 2’);
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 446 | 06-11-19 14:36:02 | Query | INSERT INTO `MovieID` (`MovieID`, `Year`, `MovieTitle`) VALUES (‘17788’, ‘1999’, ‘Sopranos: Season 1 Disc 3’);
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 620 | 06-11-19 14:36:36 | Query | INSERT INTO `MovieID` (`MovieID`, `Year`, `MovieTitle`) VALUES (‘17789’, ‘1999’, ‘Sopranos: Season 1 Disc 4’);
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 794 | 06-11-19 14:36:53 | Query | INSERT INTO `MovieID` (`MovieID`, `Year`, `MovieTitle`) VALUES (‘17790’, ‘1999’, ‘Sopranos: Season 1 Disc 5’);
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 968 | 06-11-19 14:56:15 | Query | DELETE FROM `MovieID` WHERE `MovieID`.`MovieID` = 17786 LIMIT 1;
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 1096 | 06-11-19 14:56:15 | Query | DELETE FROM `MovieID` WHERE `MovieID`.`MovieID` = 17787 LIMIT 1;
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 1224 | 06-11-19 14:56:15 | Query | DELETE FROM `MovieID` WHERE `MovieID`.`MovieID` = 17788 LIMIT 1;
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 1352 | 06-11-19 14:56:15 | Query | DELETE FROM `MovieID` WHERE `MovieID`.`MovieID` = 17789 LIMIT 1;
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 1480 | 06-11-19 14:56:15 | Query | DELETE FROM `MovieID` WHERE `MovieID`.`MovieID` = 17790 LIMIT 1;
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 1608 | 06-11-19 15:19:37 | Rotate to mysql-bin.000003 pos: 4 |
INFO: Removing all of the uncompressed/unencrypted data

4. So now we will restore the database to what it looked like at approximately 2:45pm. Since the tables were added at 2:30pm and accidentally deleted at 3pm. Since we want the database back to the state it was at right before the delete.

-bash-3.1# mysql-zrm –action restore –source-directory /var/lib/mysql-zrm/dailyrun/20061019151937 –backup-set dailyrun –stop-datetime “20061019144500”

INFO: ZRM for MySQL Community Edition – version 1.1
INFO: Mail address: is ok
INFO: Input Parameters Used {
INFO: verbose=1
INFO: retention-policy=10D
INFO: backup-level=1
INFO: databases=movies
INFO: source-directory=/var/lib/mysql-zrm/dailyrun/20061019151937
INFO: html-reports=backup-status-info
INFO: password=******
INFO: backup-mode=logical
INFO: compress-plugin=/usr/bin/gzip
INFO: compress=/usr/bin/gzip
INFO: user=backup-user
INFO: stop-datetime=20061019144500
INFO: Getting mysql variables
INFO: mysqladmin –user=backup-user –password=***** variables
INFO: datadir is /var/lib/mysql/
INFO: mysql_version is 5.0.22-log
INFO: log_bin=ON
INFO: Uncompressing backup
INFO: Command used is ‘cat “/var/lib/mysql-zrm/dailyrun/20061019151937/backup-data” | “/usr/bin/gzip” -d | tar –same-owner -xpsC “/var/lib/mysql-zrm/dailyrun/20061019151937” 2>/tmp/HId0KZkvcS’
INFO: Restoring incremental to tmpfile
INFO: mysqlbinlog –user=backup-user –password=***** –stop-datetime=20061019144500 –database=movies -r /tmp/NNqSZFZa8R “/var/lib/mysql-zrm/dailyrun/20061019151937″/mysql-bin.[0-9]*
INFO: restoring using command mysql –user=backup-user –password=***** -e “source /tmp/NNqSZFZa8R;”
INFO: Incremental restore done for database movies
INFO: Shutting down MySQL
INFO: Removing all of the uncompressed/unencrypted data
INFO: Restore done in 2 seconds. MySQL server has been shutdown. Please restart after verification.

5. Once you restart the MySQL services you’ll notice that the database has been restored to what it looked at 2:45pm. Which means it has the last 5 tablespaces that were accidentally deleted at 3pm.