mysqlbinlog恢复单表

ysoren

背景介绍：MySQL的版本是mysql-5.6.17。由于公司在前端程序出现了一个问题，程序端的误操作，在某个时刻删除了整个表，但是这个表在这个时刻后还是有数据写入，所以要恢复这个时刻之前的数据但是又不能破坏数据现在的写入。与是我用如下的方法进行了数据的恢复具体方法如下：
1，查看删除前的数据，以便在删除后对数据进行一致性的校验并进行delete操作（改方法至少进行的粗略的校验，具体校验可以使用pt-checkcum工具）

mysql> select count(*) from sdb_b2c_orders; +----------+ | count(*) | +----------+ |    35630 | +----------+ 1 row in set (0.01 sec) mysql>delete from sdb_b2c_orders

2，检查执行该操作的大概时间，并转换binlog（转换成人类能读懂的形式），并查找执行delete的时刻。

/opt/mysql-5.1.57/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -start-date='2015-06-24 10:10:00' --stop-date='2015-06-24 11:10:00' /data/mysql/data_3310/mysqld2-binlog.000023 > /tmp/recovery1.log [iyunv@localhost ~]# vi /tmp/recovery1.log #150624 11:18:57 server id 1  end_log_pos 308613577     Query   thread_id=979   exec_time=0     error_code=0 SET TIMESTAMP=1435115937/*!*/; /*!40000 ALTER TABLE `sdb_b2c_orders` ENABLE KEYS */ /*!*/; # at 308613577 #150624 11:18:57 server id 1  end_log_pos 308613604     Xid = 17217 COMMIT/*!*/; # at 308613604 #150624 11:21:00 server id 1  end_log_pos 308613674     Query   thread_id=981   exec_time=5     error_code=0 SET TIMESTAMP=1435116060/*!*/; SET @@session.foreign_key_checks=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; BEGIN /*!*/; # at 308613674 #150624 11:21:00 server id 1  end_log_pos 308613765     Query   thread_id=981   exec_time=5     error_code=0 SET TIMESTAMP=1435116060/*!*/; delete from sdb_b2c_orders /*!*/; # at 308613765 #150624 11:21:00 server id 1  end_log_pos 308613792     Xid = 17238 COMMIT/*!*/; # at 308613792 #150624 11:23:55 server id 1  end_log_pos 308613894     Query   thread_id=982   exec_time=0     error_code=0 SET TIMESTAMP=1435116235/*!*/; SET @@session.foreign_key_checks=0, @@session.unique_checks=0/*!*/; SET @@session.sql_mode=524288/*!*/; DROP TABLE IF EXISTS `sdb_b2c_orders` /*!*/; # at 308613894 #150624 11:23:55 server id 1  end_log_pos 308617447     Query   thread_id=982   exec_time=0     error_code=0 SET TIMESTAMP=1435116235/*!*/; CREATE TABLE `sdb_b2c_orders` (   `order_id` bigint(20) unsigned NOT NULL DEFAULT '0',   `order_bn` varchar(50) DEFAULT '0', 在文本中输入/delete进行查找

3，查找到时刻为11:21:00，再进二进制转换，确定更精确的时刻，以便进行数据恢复。

/opt/mysql-5.1.57/bin/mysqlbinlog --no-defaults --base64-output=decode-rows  --stop-date='2015-06-24 10:20:59' /data/mysql/data_3310/mysqld2-binlog.000023 > /tmp/recovery1.log

4，在装换的的日志中抽取要恢复的单表的sql，并查看抽取是sql格式是否正确（我这是sdb_b2c_orders表）。这里要注意的是grep的命令，如果想要深入的理解grep，可以在Linux命令大全（http://man.linuxde.net/）这个网址上查看。

cat /tmp/recovery1.log |grep  --ignore-case -E 'insert|update|delete' -A2 |grep -E 'sdb_b2c_orders|;' > /tmp/test1.sql [iyunv@localhost ~]# tail /tmp/test1.sql ……………… 'false','local',NULL,'pc',0,NULL) /*!*/; update sdb_b2c_orders set status='finish' where createtime>unix_timestamp('2015-05-27') and status='active' /*!*/; delete from sdb_b2c_orders where createtime<unix_timestamp('2015-05-27') and createtime>unix_timestamp('2015-05-26') /*!*/;

5，发现格式是正确的，于是将数据导入到数据库中，并校验数据是否一致，发现数据是一致的。

mysql -h127.0.0.1* -P3310 fuzhou sdb_b2c_orders < /tmp/test1.sql mysql> select count(*) from sdb_b2c_orders; +----------+ | count(*) | +----------+ |    35630 | +----------+ 1 row in set (0.01 sec)

小结：这种回复单表的方法是很简单的，但是如果是一张大表，比如超过一个G，那么回复的时间会比较长。