mysql的binlog日志简介与恢复数据

一、mysql日志简介

mysql中日志主要分为四类：

1. 通用查询日志：记录建立的客户端连接和执行的语句。
2. 慢查询日志：记录所有执行时间超过long_query_time的所有查询或者不使用索引的查询。
3. 错误日志：记录mysql服务启动、运行或停止时出现的问题。
4. 二进制日志（binlog日志）：记录所有更改数据的语句，可以用于数据复制。

二、二进制日志

二进制日志主要记录mysql数据库的变化，日志中包含了所有更新了数据或者潜在更新了数据（没有匹配任何行的delete）的语句，语句以“事件”的形式保存，描述数据更改。

日志记录了每个更新数据库语句的执行时间信息，使用二进制日志的主要目的时最大可能的恢复数据库，因为二进制日志包含备份后进行的所有更新。

三、数据环境准备

实际生产环境中，一般都会配合备份库来执行数据恢复操作。这里我们先建一个库和表，并备份。

3.1、建库、建表、准备数据

CREATE DATABASE `mydb`;

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

INSERT INTO `table1` VALUES (1,'刘一'),(2,'陈二'),(3,'张三'),(4,'李四'),(5,'王五'),(6,'赵六'),(7,'孙七'),(8,'周八'),(9,'吴九'),(10,'郑十');

3.2、备份数据

进入mysql客户端的bin目录，或者将mysqldump命令配置为系统环境变量

mysqldump -uroot -p -h localhost -P3306 -B mydb >D:\mysqlbackup\mydb_bak_20210219.sql

3.3、操作数据

这里我们修改id为2的数据，并且删除修改后id为2的数据，然后再更新id为5的数据

UPDATE `mydb`.`table1` SET `name` = '陈二2' WHERE `id` = 2;
DELETE from `mydb`.`table1` where `id` =2;
UPDATE `mydb`.`table1` SET `name` = '王五5' WHERE `id` = 5;

此时的数据结果为

此时我发现我的删除操作不应该执行，我想要的只是保留着两个更新。这种情况下模拟的就是实际生产过程中的异常删除。

此时我们的恢复思路是：

第一步：查看binlog日志，得到我们想要恢复的操作在日志记录中的position

第二步：使用备份库将数据恢复到上次备份时的状态；

第三步：使用mysqlbinlog命令恢复数据

四、恢复数据

当我们发现数据被异常删除时，建议执行刷新日志的操作，生成一个新的日志。

这样做的目的是，线上正在执行的操作的后续日志会写入到这个新的日志文件中，我们可以在上一个日志中查看我们的异常操作，当然这一步刷新生成新日志不是必须的。

4.1、查看日志

这里我现有的日志是：huangtclog.000002，

连接MySQL服务器，在命令行界面执行刷新日志命令

flush logs;

刷新之后生产了一个新的日志huangtclog.000003，此时我在上一个日志文件huangtclog.000002中查看我的操作记录

连接MySQL服务器，在命令行界面执行下面命令

show binlog events in 'huangtclog.000002';

这里我们可以看到，当position为377时，我们执行了更新，当position为683时执行了delete操作，当position为984的时候执行了更新操作。

当然我们也可以退出mysql连接，直接通过命令行查看详细日志

mysqlbinlog D:\developSoft\mysql-8.0.20-winx64\data\log\huangtclog.000002

或者输入到文件中单独查看

mysqlbinlog D:\developSoft\mysql-8.0.20-winx64\data\log\huangtclog.000002 >D:\mysqlbackup\huangtc000002.log

这时，可以看到D:\mysqlbackup目录下生成了一个huangtc000002.log文件。可以使用文本工具打开该log文件。

4.2、使用备份库恢复数据

将现有的库删除或者备份，然后使用备份库恢复数据。

root用户连接MySQL服务器，使用以下命令导入备份文件

source D:\mysqlbackup\mydb_bak_20210219.sql;

4.3、使用binlog日志恢复操作

我们需要把position为377的update操作和position为984的update操作在恢复之后的新库上执行，因此执行以下命令

mysqlbinlog D:\developSoft\mysql-8.0.20-winx64\data\log\huangtclog.000002 --start-position=319 --stop-position=440 | mysql -uroot -p
mysqlbinlog D:\developSoft\mysql-8.0.20-winx64\data\log\huangtclog.000002 --start-position=926 --stop-position=1047 | mysql -uroot -p

此时数据结果为：

至此，我们已经使用binlog日志恢复了两次update操作的数据结果。

五、总结

理解下来，我们就可以发现，binlog日志之所以能够用来恢复数据，是因为它记录了每个更新数据库语句的执行信息，它不包含没有修改数据的语句，我们恢复数据的过程，就是在binlog日志中找到我们需要恢复的操作的节点，然后将这个操作在备份库中重新执行的过程。