【Mysql】如何通过binlog恢复数据

一、前言

在实际工作中，可能有些刚工作不久的同学会误删了生产或测试环境的数据，这时怎么恢复Mysql中误删的数据呢？

大部分同学都知道可以通过binlog来恢复数据，但是具体如何操作可能还不是很清楚，接下来就来介绍下如何通过binlog日志来恢复数据

二、准备工作

这里我使用的是windows的Mysql5.7版本

2.1 开启binlog日志

1. 首先查看当前mysql是否已开启binlog，命令如下

   show variables like 'log_bin';

2. 如果未开启，修改配置文件my.ini 或 my.cnf，添加开启binlog配置，如下

   [mysqld]
   log-bin=mysql-bin
   binlog-format=ROW
   server_id=1

3. 重启Mysql

2.2 创建测试数据并备份

创建一个数据库test，并创建table1表，表结构如下

CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入5条数据，如下

INSERT INTO `test`.`table1`(`id`, `name`) VALUES (1, '111');
INSERT INTO `test`.`table1`(`id`, `name`) VALUES (2, '222');
INSERT INTO `test`.`table1`(`id`, `name`) VALUES (3, '333');
INSERT INTO `test`.`table1`(`id`, `name`) VALUES (4, '444');
INSERT INTO `test`.`table1`(`id`, `name`) VALUES (5, '555');

OK，到这里我们的table1表里已经有5条测试数据了。

现在使用mysqldump工具对test库进行备份，备份文件名为test.sql，命令如下

mysqldump -uroot -p test --master-data=2 --single-transaction > test.sql

2.3 模拟误删操作

接下来继续往table1表中添加3条数据，如下

INSERT INTO `test`.`table1`(`id`, `name`) VALUES (6, '666');
INSERT INTO `test`.`table1`(`id`, `name`) VALUES (7, '777');
INSERT INTO `test`.`table1`(`id`, `name`) VALUES (8, '888');

这时我们通过清空该表来模拟误删操作

truncate table table1;

那么接下来我们如何来恢复table1表中的数据（8条）？
其实就是通过备份文件 + binlog的增量数据的方式来恢复，接下来介绍具体步骤

三、具体步骤

1. 首先我们打开备份文件test.sql，找到MASTER_LOG_POS字样，并记住该偏移量799674，如下图
   
   这说明我们的备份文件是在这个位置进行备份的
2. 在mysql的data目录下找到对应的binlog文件，通过mysqlbinlog工具将binlog日志以明文的方式输出到test.binlog文件中，如下
   mysqlbinlog --no-defaults ..\data\mysql-bin.000002 -vv>test.binlog
3. 打开test.binlog文件，找到执行truncate语句的位置并记下来，如下图
   
   这里偏移量为800469

4. 继续使用mysqlbinlog工具，指定起始位置和终止位置，导出sql语句到inc.binlog文件中，命令如下

   mysqlbinlog --no-defaults --database=test ..\data\mysql-bin.000002 --start-position=799674 --stop-position=800469 -vv >inc.binlog

   如果起始位置和终止位置不在同一个binlog中，可以使用如下命令

   mysqlbinlog --no-defaults --database=test ..\data\mysql-bin.000002 ..\data\mysql-bin.000003 --start-position=起始位置 --stop-position=终止位置 -vv >inc.binlog

5. 在test数据库下运行备份文件test.sql，恢复到备份位置

   use test;
   source E:\mysql\bin\test.sql

6. 再执行inc.binlog，恢复到truncate之前的位置，如下

   source E:\mysql\bin\inc.binlog

7. 最终数据恢复到truncate之前