mysql的数据恢复

数据库数据被误删除是经常看到的事情，数据的恢复也就自然成为了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

[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.用sed替换’###’:

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.替换’*/’为’,’:

[email protected] # sed -i ‘s/\*\//\*\/,/g’ /tmp/xx.log

@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 */,

5.替换日志中的最后一个’,’为’;’:

a.delete前加’;’:

sed -i ‘s/delete/;delete/g’ /tmp/xx.log

;delete from master.del_table

b.delete 前的’,;’替换为’;’:

vi /tmp/xx.log —–>:%s/,$\n^ ;/;/g

@1=29 /* 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 */,

@13=null /* bit(1) meta=0 nullable=1 is_null=1 */

@1,@2,@3….对应的是表的字段；

6.最后将delete from table xx where 改为insert into xx values(”,”,”,”…..)既可以；

通过上面的6个步骤就可以从binlog中恢复出删除的数据，看上去很繁琐，所以parse_binlog 工具就产生了，这个工具是@俊达 所写，可以将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’);

replace into t1.t1 values ( 11 , ‘ni hao1’);

这样dba就可以方便的进行数据的恢复了；

最近@plinux已经完成该mysql闪回方案的补丁，在row模式的binlog下，记录了每个row的完整信息，insert会包含每个字段的值，delete也会包含每个字段的值，update会在set和where部分包含所有的字段值。因此binlog就是个完整的逻辑redo，把它的操作逆过来，就是需要的“undo”；@吴炳锡 这个好人已经把他编译好了放在开源社区上，可以在这里下载:

mysql> show master logs;

+——————+———–+

| log_name | file_size |

| mysql-bin.000004 | 2293035 |

mysql> delete from t1 where id=15;

query ok, 1 row affected (0.00 sec)

| 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’

不得不说开源的力量真大.