转自:http://www.taobaodba.com/html/1430_mysql%E7%9A%84%E6%95%B0%E6%8D%AE%E6%81%A2%E5%A4%8D.html
数据库数据被误删除是经常看到的事情,数据的恢复也就自然成为了DBA很重要的一门基本功夫,比较笨拙的办法是拉出历史的备份到另外的一台机器恢复出来,但是这种方法如果数据量比较大的话,往往会耗费较长的时间,以前在使用oracle的时候,提供了很多数据恢复的办法,常用的办法就是采用闪回flashback,或者通过logmnr在分析日志完成数据的恢复,但是在mysql中,数据的恢复变成了很困难的一件事情。
上周一同事的数据库就由于开发人员的数据订正误操作,导致了一张表的所有数据被清空,由于该库的数据容量已经达到了几百G,从备份中恢复需要很长的时间,所以联系到我帮助恢复,由于数据库采用的是row模式,删除的操作在binlog中会一行一行的记录,所以恢复操作就是将binlog中的内容进行解析为对应的插入语句,恢复步骤如下:
1.用mysqlbing将binlog文件进行解析:
2.由于被误删除的表有13个字段,在加上两行delete和where,所以取其中的15行:
3.用sed替换’###’:
4.替换’*/’为’,':
5.替换日志中的最后一个’,'为’;':
6.最后将delete from table xx where 改为insert into xx values(”,”,”,”…..)既可以;
通过上面的6个步骤就可以从binlog中恢复出删除的数据,看上去很繁琐,所以[url=http://hidba.org/wp-content/uploads/2012/10/parse_binlog.txt]parse_binlog[/url] 工具就产生了,这个工具是@俊达 所写,可以将row模式的binlog转换为对应的sql语句:
这样DBA就可以方便的进行数据的恢复了;
最近[url=http://www.penglixun.com/]@plinux[/url]已经完成该mysql闪回[url=http://www.penglixun.com/tech/database/mysql_flashback_feature.html]方案[/url]的[url=http://mysql.taobao.org/index.php/Patch_source_code#Add_flashback_feature_for_mysqlbinlog]补丁[/url],在row模式的binlog下,记录了每个ROW的完整信息,INSERT会包含每个字段的值,DELETE也会包含每个字段的值,UPDATE会在SET和WHERE部分包含所有的字段值。因此binlog就是个完整的逻辑redo,把它的操作逆过来,就是需要的“undo”;[url=http://www.mysqlsupport.cn/mysqlbinlog-flashback/]@吴炳锡[/url] 这个好人已经把他编译好了放在开源社区上,可以在这里[url=https://github.com/wubx/mysql-binlog-statistic/blob/master/bin/mysqlbinlog]下载[/url]:
不得不说开源的力量真大.
数据库数据被误删除是经常看到的事情,数据的恢复也就自然成为了DBA很重要的一门基本功夫,比较笨拙的办法是拉出历史的备份到另外的一台机器恢复出来,但是这种方法如果数据量比较大的话,往往会耗费较长的时间,以前在使用oracle的时候,提供了很多数据恢复的办法,常用的办法就是采用闪回flashback,或者通过logmnr在分析日志完成数据的恢复,但是在mysql中,数据的恢复变成了很困难的一件事情。
上周一同事的数据库就由于开发人员的数据订正误操作,导致了一张表的所有数据被清空,由于该库的数据容量已经达到了几百G,从备份中恢复需要很长的时间,所以联系到我帮助恢复,由于数据库采用的是row模式,删除的操作在binlog中会一行一行的记录,所以恢复操作就是将binlog中的内容进行解析为对应的插入语句,恢复步骤如下:
1.用mysqlbing将binlog文件进行解析:
mysqlbinlog -vvv /home/mysql/data3006/mysql/mysql-bin.000004 >/tmp/master.log.20120925
2.由于被误删除的表有13个字段,在加上两行delete和where,所以取其中的15行:
grep “###” master.log.20120925 | grep “DELETE FROM master.agentgroup” -A 15 >/tmp/xx.log
root@db1.com # 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.用sed替换’###’:
root@db1.com # 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.替换’*/’为’,':
root@db1.com # sed -i ‘s/\*\//\*\/,/g’ /tmp/xx.log
root@db1.com # 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.替换日志中的最后一个’,'为’;':
a.delete前加’;':
sed -i ‘s/DELETE/;DELETE/g’ /tmp/xx.log
root@db1.com # 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.delete 前的’,;’替换为’;':
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….对应的是表的字段;
6.最后将delete from table xx where 改为insert into xx values(”,”,”,”…..)既可以;
通过上面的6个步骤就可以从binlog中恢复出删除的数据,看上去很繁琐,所以[url=http://hidba.org/wp-content/uploads/2012/10/parse_binlog.txt]parse_binlog[/url] 工具就产生了,这个工具是@俊达 所写,可以将row模式的binlog转换为对应的sql语句:
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′);
这样DBA就可以方便的进行数据的恢复了;
最近[url=http://www.penglixun.com/]@plinux[/url]已经完成该mysql闪回[url=http://www.penglixun.com/tech/database/mysql_flashback_feature.html]方案[/url]的[url=http://mysql.taobao.org/index.php/Patch_source_code#Add_flashback_feature_for_mysqlbinlog]补丁[/url],在row模式的binlog下,记录了每个ROW的完整信息,INSERT会包含每个字段的值,DELETE也会包含每个字段的值,UPDATE会在SET和WHERE部分包含所有的字段值。因此binlog就是个完整的逻辑redo,把它的操作逆过来,就是需要的“undo”;[url=http://www.mysqlsupport.cn/mysqlbinlog-flashback/]@吴炳锡[/url] 这个好人已经把他编译好了放在开源社区上,可以在这里[url=https://github.com/wubx/mysql-binlog-statistic/blob/master/bin/mysqlbinlog]下载[/url]:
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 |
+——————+———–+
root@db.com # ./mysqlbinlog.txt -v –base64-output=decode-rows -B –start-position=2293035 /home/mysql/data3006/mysql/mysql-bin.000004 >/tmp/1.sql
root@db.com # 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 ;
不得不说开源的力量真大.