【Mysql】如何通过binlog恢复数据 - 服务器托管|北京服务器租用|机房托管租用|IDC托管租用|机房机柜带宽租用-价格及费用咨询

一、前言

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

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

二、准备工作

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

2.1 开启binlog日志

首先查看当前mysql是否已开启binlog，命令如下
```
show variables like 'log_bin';
```
如果未开启，修改配置文件my.ini 或 my.cnf，添加开启binlog配置，如下
```
[mysqld]
log-bin=mysql-bin
binlog-format=ROW
server_id=1
```
重启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的增量数据的方式来恢复，接下来介绍具体步骤

三、具体步骤

首先我们打开备份文件test.sql，找到MASTER_LOG_POS字样，并记住该偏移量799674，如下图

这说明我们的备份文件是在这个位置进行备份的
在mysql的data目录下找到对应的binlog文件，通过mysqlbinlog工具将binlog日志以明文的方式输出到test.binlog文件中，如下
mysqlbinlog --no-defaults ..datamysql-bin.000002 -vv>test.binlog
打开test.binlog文件，找到执行truncate语句的位置并记下来，如下图

这里偏移量为800469

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

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

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

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

在test数据库下运行备份文件test.sql，恢复到备份位置
```
use test;
source E:mysqlbintest.sql
```
再执行inc.binlog，恢复到truncate之前的位置，如下
```
source E:mysqlbininc.binlog
```
最终数据恢复到truncate之前

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net