关于mysql:Mysql如何通过binlog恢复数据

0次阅读

共计 1968 个字符，预计需要花费 5 分钟才能阅读完成。

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

大部分同学都晓得能够通过 binlog 来复原数据，然而具体如何操作可能还不是很分明，接下来就来介绍下如何通过 binlog 日志来复原数据

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

首先查看以后 mysql 是否已开启 binlog，命令如下
```
show variables like 'log_bin';
```
如果未开启，批改配置文件 my.ini 或 my.cnf，增加开启 binlog 配置，如下
```
[mysqld]
log-bin=mysql-bin
binlog-format=ROW
server_id=1
```
重启 Mysql

创立一个数据库 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

接下来持续往 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 ..\data\mysql-bin.000002 -vv>test.binlog
关上 test.binlog 文件，找到执行 truncate 语句的地位并记下来，如下图
这里偏移量为 800469

持续应用 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

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