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 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:
$mysql-zrm--actionparse-binlogs--source-directory/var/lib/mysql-zrm/dailyrun/20061019151937--backup-setdailyrun
------------------------------------------------------------
Logfilename|LogPosition|Timestamp|EventType|Event
------------------------------------------------------------
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002|4|06-11-1914:09:58|Start:binlogv4,serverv5.0.22-logcreated06101914:09:58|/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002|98|06-11-1914:34:27|Query|usemovies;INSERTINTO`MovieID`(`MovieID`,`Year`,`MovieTitle`)VALUES('17786','1999','Sopranos: Season 1 Disc 1');/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002|272|06-11-1914:35:46|Query|INSERTINTO`MovieID`(`MovieID`,`Year`,`MovieTitle`)VALUES('17787','1999','Sopranos: Season 1 Disc 2');/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002|446|06-11-1914:36:02|Query|INSERTINTO`MovieID`(`MovieID`,`Year`,`MovieTitle`)VALUES('17788','1999','Sopranos: Season 1 Disc 3');/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002|620|06-11-1914:36:36|Query|INSERTINTO`MovieID`(`MovieID`,`Year`,`MovieTitle`)VALUES('17789','1999','Sopranos: Season 1 Disc 4');/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002|794|06-11-1914:36:53|Query|INSERTINTO`MovieID`(`MovieID`,`Year`,`MovieTitle`)VALUES('17790','1999','Sopranos: Season 1 Disc 5');/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002|968|06-11-1914:56:15|Query|DELETEFROM`MovieID`WHERE`MovieID`.`MovieID`=17786LIMIT1;/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002|1096|06-11-1914:56:15|Query|DELETEFROM`MovieID`WHERE`MovieID`.`MovieID`=17787LIMIT1;/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002|1224|06-11-1914:56:15|Query|DELETEFROM`MovieID`WHERE`MovieID`.`MovieID`=17788LIMIT1;/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002|1352|06-11-1914:56:15|Query|DELETEFROM`MovieID`WHERE`MovieID`.`MovieID`=17789LIMIT1;/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002|1480|06-11-1914:56:15|Query|DELETEFROM`MovieID`WHERE`MovieID`.`MovieID`=17790LIMIT1;/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002|1608|06-11-1915:19:37|Rotatetomysql-bin.000003pos:4|------------------------------------------------------------
INFO:Removingalloftheuncompressed/unencrypteddata
Restoration
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.
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.