使用binlog找回truncate数据

        使用binlog做数据恢复

弄一个简单的业务场景举例。

测试环境： MariaDB-10.0.13
日志格式： mixed 没起用gtid

表结构如下：

CREATE TABLE 'tb_test'(
'id' int(11) NOT NULL AUTO_INCREMENT,
'name' varchar(32) DEFAULT NULL,
PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

基于某个时间点有一个备份或是有全量的binlog是能恢复数据的一个唯一保证。 例如我们的备份就是一个表结构创建语句,binlog pos相关信息： binlog.000002 , 4336，然后进行了如下：

–t1时间 程序写入：

insert into tb_test(name) values('张三'),('李四');
insert into tb_test(name) values('隔壁老王');

–t2时间 某个人员失误：

truncate table tb_test;

–t3时间 程序写入：

insert into tb_test(name) values('老赵');
update tb_test set name='老赵赵' where id=1;

现在表里的数据情况：

mariadb>select * from tb_test;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 老赵赵 |
+----+-----------+
1 row in set (0.00 sec)

可以见truncate table操作后，表的自增id又变更为从1开始，原来写入的数据应该是：

+—-+———–+
| id | name |
+—-+———–+
| 1 | 张三 |
+—-+———–+
| 2 | 李四 |
+—-+———–+
| 3 | 隔壁老王 |
+—-+———–+

如果没生truncate table操作，实际的数据应该为：

+—-+———–+
| id | name |
+—-+———–+
| 1 | 张三 |
+—-+———–+
| 2 | 李四 |
+—-+———–+
| 3 | 隔壁老王 |
+—-+———–+
| 4 | 老赵赵 |
+—-+———–+

而且线上的恢复那个表时和序序开发人员了解才知道，原来那个id和缓存及其它地方有依赖，因为id乱了，也会造成程序错乱。这个时间修复id在程序层错乱的事，留给开发人员了关建是给他们讲明白恢复的结果是什么样，我们的关建任务是把数据恢复出来。好，接下来的工作是开始从binlog中恢复数据。
利用: show binary logs; 查看当的log文件分布， 然后利用show binlog events in ‘binary log文件’; 查看log文件的内容，目的是找到truncate发生的日志位置。
另外因为基于备份（由log的启始位置）或是从量log, 如果基于备份有log的起始位置，我们需要处理的log文件是启始位置到发生truncate的日值（后面的数据处理不了，会发生主建冲突的错误造成truncate后的数据不能恢复），
如果是全量日志，需要从创建完mysql后库后的日志去处理到当前的发生truncate的位置（后面数据会因为主建冲突写不进去）
恢复准备工作，创建一个库用于恢复数据，这里创建了一个re_test, 及原结构的表: tb_test （相当于恢复了备份，过程省略）

mariadb> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       264 |
| binlog.000002 |      5561 |
+---------------+-----------+
2 rows in set

我这里有一个备份文件就是那个创建表的sql语句，位置是binlog.000002 , 4202在这个案例里我只用cover住binlog.000002这个文件。

MariaDB [(none)]> show binlog events in 'binlog.000002';

+------------------+------+-------------+-----------+-------------+----------------------------------------------------+
| Log_name         | Pos | Event_type    | Server_id   | End_log_pos | Info |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------+
| binlog.000002 | 4202 | Query       |         1 |        4336 | CREATE DATABASE `test` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci                                                                                                                  |
| binlog.000002 | 4336 | Query       |         1 |        4571 | use `test`; CREATE TABLE `tb_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
| binlog.000002 | 4571 | Query       |         1 |        4639 | BEGIN                                                                                                                                                                                    |
| binlog.000002 | 4639 | Intvar      |         1 |        4667 | INSERT_ID=2                                                                                                                                                                              |
| binlog.000002 | 4667 | Query       |         1 |        4783 | use `test`; insert into tb_test(name) values('张三'),('李四')                                                                                                                            |
| binlog.000002 | 4783 | Xid         |         1 |        4810 | COMMIT /* xid=193 */                                                                                                                                                                     |
| binlog.000002 | 4810 | Query       |         1 |        4878 | BEGIN                                                                                                                                                                                    |
| binlog.000002 | 4878 | Intvar      |         1 |        4906 | INSERT_ID=4                                                                                                                                                                              |
| binlog.000002 | 4906 | Query       |         1 |        5017 | use `test`; insert into tb_test(name) values('隔壁老王')                                                                                                                                 |
| binlog.000002 | 5017 | Xid         |         1 |        5044 | COMMIT /* xid=194 */                                                                                                                                                                     |
| binlog.000002 | 5044 | Query       |         1 |        5129 | use `test`; truncate table tb_test                                                                                                                                                       |
| binlog.000002 | 5129 | Query       |         1 |        5197 | BEGIN                                                                                                                                                                                    |
| binlog.000002 | 5197 | Intvar      |         1 |        5225 | INSERT_ID=1                                                                                                                                                                              |
| binlog.000002 | 5225 | Query       |         1 |        5330 | use `test`; insert into tb_test(name) values('老赵')                                                                                                                                     |
| binlog.000002 | 5330 | Xid         |         1 |        5357 | COMMIT /* xid=196 */                                                                                                                                                                     |
| binlog.000002 | 5357 | Query       |         1 |        5425 | BEGIN                                                                                                                                                                                    |
| binlog.000002 | 5425 | Query       |         1 |        5534 | use `test`; update tb_test set name='老赵赵' where id=1                                                                                                                                  |
| binlog.000002 | 5534 | Xid         |         1 |        5561 | COMMIT /* xid=197 */                                                                                                                                                                     |
+---------------+------+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
72 rows in set (0.00 sec)

看到这个表刚开始就发生一次truncate, 那其实也可以说明我就恢复刚开始那个truncate到后来那个误操作的truncate table的语句之间的数据就是丢失的数据。
这个恢复可以从binlog.000002 pos: 4336到binlog.000002 pos: 5404 即：

mysqlbinlog --rewrite-db='test->re_test' --start-position=4336 --stop-position=5044 binlog.000002 |mysql -S /var/lib/mysql/mysql.sock re_test -uroot -p

恢复结果如下：

mysql -S /var/lib/mysql/mysql.sock re_test -uroot -p;
MariaDB [re_test]>select count(*) from tb_test;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.02 sec)

MariaDB [re_test]>select * from tb_test;
+----+--------------+
| id | name |
+----+--------------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 隔壁老王 |
+----+--------------+
3 rows in set (0.00 sec)

MariaDB [re_test]>insert into tb_test(name) select name from test.tb_test;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

MariaDB [re_test]>rename table test.tb_test to test.bak_tb_test;
Query OK, 0 rows affected (0.04 sec)

MariaDB [re_test]>rename table re_test.tb_test to test.tb_test;
Query OK, 0 rows affected (0.03 sec)

MariaDB [re_test]>select * from test.tb_test;
+----+--------------+
| id | name |
+----+--------------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 隔壁老王 |
| 4 | 老赵赵 |
+----+--------------+
4 rows in set (0.00 sec)

恢复完成。

我是根据吴炳锡老师的文章做的测试，下面附上原作地址。

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi@gmail.com