Accidentally deleting database data is a common occurrence, so data recovery naturally becomes a very important basic skill for DBAs. The more clumsy approach is to pull a historical backup and restore it onto another machine, but if the data volume is large, this method often takes a long time. When using Oracle in the past, there were many data recovery methods available; common approaches included using flashback or analyzing logs via logmnr to complete data recovery. However, in MySQL, data recovery becomes a much more difficult task.
Last week, a colleague’s database had all data in a table emptied due to a developer’s erroneous data correction operation. Since the database’s data capacity had reached hundreds of gigabytes, restoring from backup would take a very long time, so they contacted me for help with recovery. Because the database uses row-based replication, the delete operation is recorded row by row in the binlog, so the recovery operation involves parsing the content in the binlog into corresponding insert statements. The recovery steps are as follows:
1. Parse the binlog file using mysqlbinlog:
mysqlbinlog -vvv /home/mysql/data3006/mysql/mysql-bin.000004 >/tmp/master.log.20120925
2. Since the accidentally deleted table has 13 fields, plus two lines for delete and where, extract 15 lines:
grep “###” master.log.20120925 | grep “DELETE FROM master.agentgroup” -A 15 >/tmp/xx.log
[email protected] # more /tmp/xx.log
### DELETE FROM master.del_table
### WHERE
### @1=15 /* INT meta=0 nullable=0 is_null=0 */
### @2=1 /* INT meta=0 nullable=0 is_null=0 */
### @3=2010-09-07 18:03:13 /* DATETIME meta=0 nullable=0 is_null=0 */
### @4=1 /* INT meta=0 nullable=0 is_null=0 */
### @5=2012-09-24 01:13:56 /* DATETIME meta=0 nullable=0 is_null=0 */
### @6=’yahoo_yst’ /* VARSTRING(384) meta=384 nullable=0 is_null=0 */
### @7=5259 /* INT meta=0 nullable=1 is_null=0 */
### @8=22 /* INT meta=0 nullable=1 is_null=0 */
### @9=b’0′ /* BIT(1) meta=1 nullable=0 is_null=0 */
### @10=b’1′ /* BIT(1) meta=1 nullable=0 is_null=0 */
### @11=NULL /* BIT(1) meta=0 nullable=1 is_null=1 */
### @12=b’0′ /* BIT(1) meta=1 nullable=1 is_null=0 */
### @13=18170 /* INT meta=0 nullable=1 is_null=0 */
3. Use sed to replace ’###’:
[email protected] # more /tmp/xx.log
DELETE FROM master.del_table
WHERE
@1=15 /* INT meta=0 nullable=0 is_null=0 */
@2=1 /* INT meta=0 nullable=0 is_null=0 */
@3=2010-09-07 18:03:13 /* DATETIME meta=0 nullable=0 is_null=0 */
@4=1 /* INT meta=0 nullable=0 is_null=0 */
@5=2012-09-24 01:13:56 /* DATETIME meta=0 nullable=0 is_null=0 */
@6=’yahoo_yst’ /* VARSTRING(384) meta=384 nullable=0 is_null=0 */
@7=5259 /* INT meta=0 nullable=1 is_null=0 */
@8=22 /* INT meta=0 nullable=1 is_null=0 */
@9=b’0′ /* BIT(1) meta=1 nullable=0 is_null=0 */
@10=b’1′ /* BIT(1) meta=1 nullable=0 is_null=0 */
@11=NULL /* BIT(1) meta=0 nullable=1 is_null=1 */
@12=b’0′ /* BIT(1) meta=1 nullable=1 is_null=0 */
@13=18170 /* INT meta=0 nullable=1 is_null=0 */
4. Replace ’*/’ with ’,':
[email protected] # sed -i ‘s//*////*//,/g’ /tmp/xx.log
[email protected] # more /tmp/xx.log
DELETE FROM master.del_table
WHERE
@1=15 /* INT meta=0 nullable=0 is_null=0 */,
@2=1 /* INT meta=0 nullable=0 is_null=0 */,
@3=2010-09-07 18:03:13 /* DATETIME meta=0 nullable=0 is_null=0 */,
@4=1 /* INT meta=0 nullable=0 is_null=0 */,
@5=2012-09-24 01:13:56 /* DATETIME meta=0 nullable=0 is_null=0 */,
@6=’yahoo_yst’ /* VARSTRING(384) meta=384 nullable=0 is_null=0 */,
@7=5259 /* INT meta=0 nullable=1 is_null=0 */,
@8=22 /* INT meta=0 nullable=1 is_null=0 */,
@9=b’0′ /* BIT(1) meta=1 nullable=0 is_null=0 */,
@10=b’1′ /* BIT(1) meta=1 nullable=0 is_null=0 */,
@11=NULL /* BIT(1) meta=0 nullable=1 is_null=1 */,
@12=b’0′ /* BIT(1) meta=1 nullable=1 is_null=0 */,
@13=18170 /* INT meta=0 nullable=1 is_null=0 */,
DELETE FROM master.del_table
5. Replace the last ’,' in the log with ’;':
a. Add ’;' before delete:
sed -i ‘s/DELETE/;DELETE/g’ /tmp/xx.log
[email protected] # more /tmp/xx.log
DELETE FROM master.del_table
WHERE
@1=15 /* INT meta=0 nullable=0 is_null=0 */,
@2=1 /* INT meta=0 nullable=0 is_null=0 */,
@3=2010-09-07 18:03:13 /* DATETIME meta=0 nullable=0 is_null=0 */,
@4=1 /* INT meta=0 nullable=0 is_null=0 */,
@5=2012-09-24 01:13:56 /* DATETIME meta=0 nullable=0 is_null=0 */,
@6=’yahoo_yst’ /* VARSTRING(384) meta=384 nullable=0 is_null=0 */,
@7=5259 /* INT meta=0 nullable=1 is_null=0 */,
@8=22 /* INT meta=0 nullable=1 is_null=0 */,
@9=b’0′ /* BIT(1) meta=1 nullable=0 is_null=0 */,
@10=b’1′ /* BIT(1) meta=1 nullable=0 is_null=0 */,
@11=NULL /* BIT(1) meta=0 nullable=1 is_null=1 */,
@12=b’0′ /* BIT(1) meta=1 nullable=1 is_null=0 */,
@13=18170 /* INT meta=0 nullable=1 is_null=0 */,
;DELETE FROM master.del_table
b. Replace ’,;’ before delete with ’;':
vi /tmp/xx.log —–>:%s/,$/n^ ;/;/g
DELETE FROM master.del_table
WHERE
@1=29 /* INT meta=0 nullable=0 is_null=0 */,
@2=1 /* INT meta=0 nullable=0 is_null=0 */,
@3=2010-09-07 18:03:13 /* DATETIME meta=0 nullable=0 is_null=0 */,
@4=1 /* INT meta=0 nullable=0 is_null=0 */,
@5=2012-06-01 13:05:00 /* DATETIME meta=0 nullable=0 is_null=0 */,
@6=’alipay_front_jx’ /* VARSTRING(384) meta=384 nullable=0 is_null=0 */,
@7=5267 /* INT meta=0 nullable=1 is_null=0 */,
@8=58 /* INT meta=0 nullable=1 is_null=0 */,
@9=b’0′ /* BIT(1) meta=1 nullable=0 is_null=0 */,
@10=b’1′ /* BIT(1) meta=1 nullable=0 is_null=0 */,
@11=NULL /* BIT(1) meta=0 nullable=1 is_null=1 */,
@12=b’0′ /* BIT(1) meta=1 nullable=1 is_null=0 */,
@13=NULL /* BIT(1) meta=0 nullable=1 is_null=1 */
;DELETE FROM master.del_table
@1, @2, @3, etc. correspond to the table’s fields;
6. Finally, change “delete from table xx where” to “insert into xx values(”,”,”,”…..)”;
Through the above 6 steps, you can recover deleted data from the binlog. It seems very cumbersome, which is why the parse_binlog tool was created. This tool, written by @俊达, can convert row-based binlog into corresponding SQL statements:
mysql> USE T1
Database changed
mysql> delete from t1 where id<12;
Query OK, 2 rows affected (0.00 sec)
mysqlbinlog -vvv /home/mysql/data3006/mysql/mysql-bin.000004 |/root/parse_binlog.pl >/tmp/parse.sql1
more /tmp/parse/sql1
–DML type: DELETE, num of cols: 2
replace into t1.t1 values ( 10 , ‘ni hao1′);
–DML type: DELETE, num of cols: 2
replace into t1.t1 values ( 11 , ‘ni hao1′);
This allows DBAs to conveniently perform data recovery;
Recently, @plinux has completed the patch for this MySQL flashback solution. Under row-based binlog, complete information for each ROW is recorded: INSERT includes the values of every field, DELETE also includes the values of every field, and UPDATE includes all field values in both the SET and WHERE parts. Therefore, the binlog is a complete logical redo; reversing its operations yields the desired “undo”. @吴炳锡, a kind soul, has already compiled it and put it on the open-source community, available for download here:
mysql> show master logs;
+——————+———–+
| Log_name | File_size |
+——————+———–+
| mysql-bin.000004 | 2293035 |
+——————+———–+
mysql> use t1
Database changed
mysql> delete from t1 where id=15;
Query OK, 1 row affected (0.00 sec)
mysql> show master logs;
+——————+———–+
| Log_name | File_size |
+——————+———–+
| mysql-bin.000004 | 2293211 |
+——————+———–+
[email protected] # ./mysqlbinlog.txt -v –base64-output=decode-rows -B –start-position=2293035 /home/mysql/data3006/mysql/mysql-bin.000004 >/tmp/1.sql
[email protected] # more /tmp/1.sql
DELIMITER ;
#121004 19:59:35 server id 3703006010 end_log_pos 2293211 Xid = 13145226
COMMIT/*!*/;
#121004 19:59:35 server id 3703006010 end_log_pos 2293143 Table_map: `t1`.`t1` mapped to number 1584
#121004 19:59:35 server id 3703006010 end_log_pos 2293184 Delete_rows: table id 1584 flags: STMT_END_F
### INSERT INTO t1.t1
### SET
### @1=15
### @2=’ni xxx’
DELIMITER ;
It has to be said, the power of open source is truly great.