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.

Note:

• More information on ZRM for MySQL is available here.

• To know more about configuring ZRM for MySQL you can look at http://www.zmanda.com/quick-mysql-backup.html.

• 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:

  log-bin=/var/lib/mysql/mysql-bin.log

  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

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: dba@zmanda.com is ok
INFO: Input Parameters Used {
INFO: verbose=1
INFO: retention-policy=10D
INFO: backup-level=1
INFO: mailto=dba@zmanda.com
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.

References:

ZRM for MySQL
ZRM for MySQL Wiki
Zmanda Forums