数据库系列—MySQL 的二进制日志恢复数据库数据

MySQL Binary Log也就是常说的bin-log,，是mysql执行改动产生的二进制日志文件，其主要作用有两个：
* 数据恢复
* 主从数据库。用于slave端执行增删改，保持与master同步。

1、开启 binlog 功能及基本操作

要使用 MySQL 的 binlog 日志功能，首先要在 MySQL 的配置文件中开启该功能，操作很简单。找到 MySQL 的配置文件，在文件中添加一行 log_bin = mysql-bin 。

（配置文件为mysql安装目录\MySQL Server 8.0下的my.ini，添加一句log_bin = mysql_bin即可）

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录# 切记此处一定要用双斜杠
basedir=D:\\DevPackTool\\mysql-8.0.22-winx64 
# 设置mysql数据库的数据的存放目录
datadir=D:\\DevPackTool\\mysql-8.0.22-winx64\\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=UTF8MB4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
#开启binlog
log-bin=mysql-bin
server-id=1  
[mysql]
# 设置mysql客户端默认字符集
default-character-set=UTF8MB4

[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=UTF8MB4

方便大家统一环境。提供一个自己的配置。

其实在我安装的各种 MySQL 环境中，该功能通常都是默认开启的。

开启 binlog 功能后，在 MySQL 的数据库目录下就会有诸如 mysql-bin.000001、mysql-bin.000002等文件，这就是 MySQL 的二进制日志文件。每当 MySQL 启动或手动刷新日志后都会新建一个二进制日志文件。

首先我们 MySQL 命令行中，用 show master logs 命令查看已有的 binlog 文件。

 

2、往数据库添加数据

 

3、刷新 binlog 日志

此前 MySQL 的 binlog 文件为

+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       179 | No        |
| binlog.000002 |       952 | No        |
| binlog.000003 |       179 | No        |
| binlog.000004 |       179 | No        |
| binlog.000005 |       179 | No        |
| binlog.000006 |       179 | No        |
| binlog.000007 |   2172794 | No        |
| binlog.000008 |    433836 | No        |
| binlog.000009 |      1651 | No        |
+---------------+-----------+-----------+

现在往数据库中添加了三条数据。现在我们刷新 binlog 日志，会生成新的 mysql-bin.000010 文件（有的默认是binlog）我的是mysql8，如下：

flush logs;
show master logs;

4、删除数据

这里我把刚才添加的三条数据都删除掉。

先添加

再删除 

 

5、binlog 日志内容解析

MySQL 的二进制日志文件记录的 MySQL 的操作，比如刚才的删除操作，我们来看下日志文件的具体内容。

使用 MySQL 的 mysqlbinlog 命令：

mysqlbinlog /data/mysql/mysql-bin.000010

注意：因为我本地 mysqlbinlog 无法识别 binlog 配置中的 default-character-set=utf8，所以这里我在命令中加上了 –no-defaults才起作用，大家引以为鉴。

 

6、恢复指定数据

在通过 MySQL 的 binlog 日志恢复数据时，我们可以指定恢复到具体时间点，这有点像服务器快照管理。所以我们现在要恢复刚才删除的那3条数据，可以从删除之前找一个时间点，并恢复到那个时间点即可。

有关 mysqlbinlog 命令的使用方法，我们可以通过 mysqlbinlog 的帮助命令进行查看，如下：

mysqlbinlog –no-defaults –help

如帮助文档所示，可以通过指定时间或指定位置来恢复数据，这里我以指定时间为例给大家演示。

我们来查看下日志文件 mysql-bin.000009，（就是当前binlog日志之前的日志，当前日志是000010）如下：

命令如下：

>mysqlbinlog --no-defaults --stop-datetime="2020-12-08 16:03:09" binlog.000009 |mysql -uroot -p

这时我们在看后台，发现刚才删除的三篇文章都已恢复回来了，从而到达我们期望的目的。

总结

如何通过 MySQL 的二进制日志文件恢复数据，在平时要做好网站数据备份，数据是网站的命脉，做好数据备份以避免后期不必要的麻烦或损失。

 最后介绍几个bin_log的操作：
   1.查看最后一个bin日志文件是那个，现在位置。

show master status;

  2.启用新的日志文件，一般备份完数据库后执行。

flush logs;

3.清空现有的所用bin-log 

reset master;