Hello All,

Today we faced an issue that was very critical. Yes, we lost one table in production server and the bad scenario is that due to near realtime & live replica, our secondary server was also corrupted. To worse, our snapshots were not enough to restore db to what we expected. One good thing we had is that, since we had live replica enabled with MySQL Bin Log strategy, I had full set of bin log files in production and its not so easy to directly use binary log files to restore a specific table. (Note: What is MySQL binary log:: http://dev.mysql.com/doc/refman/5.0/en/binary-log.html)

Steps to solve this issue:

1. Copy MySQL Binary log files to another instance

2. Decode MySQL Binary log files to readable & executable statements

3. Grep / Awk to filter for one specific tables and output your sql statements to one single file and append them

4. Create java / any language based program to parse entire sql and find out from when you might need the data and execute only those

NOTE: BEFORE WE START THIS, KINDLY TAKE A SNAPSHOT OF PRODUCTION AS A GOOD PRACTISE. 

1. Copy MySQL Binary log files to another instance

Locate binary log files by carefully checking /etc/my.ini file. And then copy files to other server, this is very crucial step as the binary log files will have database and schema information embedded as part of the sql statements decoded. If you try to run the query by accident in production, that might be a catastrophic event of you. Be very careful and first transfer to some other instance.

2. Decode MySQL binary log files to readable & executable statements

Before going in to execution of MySQL Binary Log by using ‘mysqlbinlog’ command kindly refer this page http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html and then use appropriate arguments. In my case, I needed to filter data from beginning till June 27 th 2014 10:00 PM and hence my command was

>mysqlbinlog --stop-datetime="2014-06-29 22:00:00" mysql-bin.000001 --base64-output=decode-rows --verbose > decoded_after.sql

>mysqlbinlog --stop-datetime="2014-06-29 22:00:00" mysql-bin.000002 --base64-output=decode-rows --verbose >> decoded_after.sql

.....

>mysqlbinlog --stop-datetime="2014-06-29 22:00:00" mysql-bin.0000n --base64-output=decode-rows --verbose >> decoded_after.sql (where n is last file number)

Once all this is done, keep a copy of this decoded_after.sql file and use it further (This is just in case if our further steps corrupts the file, we shall continue from this step)

3. Grep / Awk to filter for one specific tables and output your sql statements to one single file and append them

Now this is very important and efficient step where we have to filter insert, update & delete statement of one specific table

# grep -e '' -B 6 decoded_after > a

should be very important and use right keyword to filter specific table based queries. This will execute in blazing speed when comported to any other methods. Kindly do this to get decoded and sql statements that corresponds to one specific table / criteria.

4. Create java / any language based program to parse entire sql and find out from when you might need the data and execute only those

In our case, we wrote a java code to return a result that filtered all the data (based on our specific case). In our case, we had to get a specific value of the table and revert it back to the same, because we ran a query and that nullified very important identifier and to get it back, we had to look for whatever id was there in specific row before delete statement was executed. Either from insert or from update. This way, we precisely were able to get the data and restored it safely in temporary instance and after several sanity checks, we did same in production.

This saved our day and we were out of this trouble with a perfect solution. I wanted to share this here as I didn’t find any clear steps for recovering specific table using mysqlbinlog command. I will be more than glad to help anyone in case of same issue.

Thanks,

Kousik Rajendran.