认真学习MySQL中的二进制日志(binlog)与中继日志（Relay log）_mysql 二进制日志

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

binlog即binary log，二进制日志文件，也叫作变更日志（update log）。它记录了数据库所有执行的DDL和DML等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、show等）。

它以事件形式记录并保存在二进制文件中。通过这些信息我们可以再现数据更新操作的全过程。

如果想要记录所有语句（例如，为了识别有问题的查询），需要使用通用查询日志。

binlog主要应用场景：

一是用于数据恢复，如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。
二是用于数据复制，由于日志的延续性和时效性，master把它的二进制日志传递给slave来达到master-slave数据一致的目的。

可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依赖binlog来同步数据，保证数据一致性。

认真学习MySQL中的二进制日志(binlog)与中继日志（Relay log）_MySQL

【1】查看与操作

① 查看默认情况

Windows下：

show VARIABLES like '%log_bin%'

log_bin ON
log_bin_basename  C:\ProgramData\MySQL\MySQL Server 8.0\Data\LAPTOP-GDNPUCQ7-bin
log_bin_index C:\ProgramData\MySQL\MySQL Server 8.0\Data\LAPTOP-GDNPUCQ7-bin.index
log_bin_trust_function_creators OFF
log_bin_use_v1_row_events OFF
sql_log_bin ON

Linux下如下所示：

mysql> show VARIABLES like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | ON                          |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)

log_bin_basename : 是binlog日志的基本文件名，后面会追加标识来表示每一个文件
log_bin_index：是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录
log_bin_trust_function_creators：限制存储过程。前面我们已经讲过了，这是因为二进制日志的一个中药功能是用于主从复制，而存储函数有可能导致主从的数据不一致。所以当开启了二进制日志后，需要限制存储函数的创建、修改、调用。
log_bin_use_v1_row_events：此只读系统变量已弃用。ON表示使用版本1二进制日志行，OFF表示使用版本2二进制日志行（MySQL5.6的默认值为2）。

MySQL服务每重启一次就会生成一个新的binlog文件。

认真学习MySQL中的二进制日志(binlog)与中继日志（Relay log）_二进制日志_02

② 日志参数设置

方式1：永久性方式

修改MySQL的my.cnf 或 my.ini文件可以设置二进制日志的相关参数：

[mysqld]
# 启用二进制日志 可自定义文件名也可以加上路径 这个对log_bin_basename log_bin_index有影响
log-bin=mysql-bin
binlog_expire_logs_seconds=60000
max_binlog_size=300M

binlog_expire_logs_seconds：此参数控制二进制日志文件保留的时长，单位是秒，默认是2592000,30天。
max_binlog_size：控制单个二进制日志大小，当前日志文件大小超过此变量时，执行切换动作。此参数的最大和默认值是1GB，该设置并不能严格控制binlog的大小，尤其是binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，可能不做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束。一般情况下可采取默认值。

数据库文件最好不要与日志文件放在同一个磁盘上，这样当数据库文件所在的磁盘发生故障时，可以使用日志文件恢复数据。

方式2：临时性方式

如果不希望通过修改配置文件并重启的方式设置二进制日志的话，还可以使用如下指令，需要注意的是在mysql8中只有会话级别的设置，没有global级别的设置。

mysql> set global sql_log_bin=0;
ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION variable and can't be used with SET GLOBAL
mysql> set  sql_log_bin=0;
Query OK, 0 rows affected (0.01 sec)

③ 查看日志

当MySQL创建二进制日志文件时，先创建一个以"filename"为名称，以".index"为后缀的文件，再创建一个以"filename"为名称、以".000001"为后缀的文件。

MySQL服务重新启动一次，以".000001"为后缀的文件就会增加一个，并且后缀名称按1递增。即日志文件的个数与MySQL服务启动的次数相同。如果日志长度超过了 max_binlog_size 的上限（默认是1GB），就会创建一个新的日志文件。

查看当前的二进制日志文件列表及大小：

mysql> show binary logs;
+---------------+------------+-----------+
| Log_name      | File_size  | Encrypted |
+---------------+------------+-----------+
| binlog.000006 |   66374693 | No        |
| binlog.000007 |    8162695 | No        |
| binlog.000008 |  205361347 | No        |
| binlog.000009 |   10917409 | No        |
| binlog.000010 | 1073743498 | No        |
| binlog.000011 |  469251392 | No        |
+---------------+------------+-----------+
6 rows in set (0.21 sec)

所有对数据库的修改都会记录在binlog中。但binlog是二进制文件，无法直接查看，想要更直观的观测它就要借助mysqlbinlog命令工具了。指令如下：

mysqlbinlog "/var/lib/mysql/binlog.000011"

如果遇到如下错误：

mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'.

尝试下面两种措施：

在MySQL的配置 my.cnf 中将default-character-set=utf8mb4 修改为 character-set-server = utf8mb4，但是这需要重启MySQL服务
增加加参数 --no-defaults ，mysqlbinlog --no-defaults "/var/lib/mysql/binlog.000011"

认真学习MySQL中的二进制日志(binlog)与中继日志（Relay log）_MySQL_03

执行结果可以看到，这是一个比较简单的日志文件，日志中记录了用户的一些操作，这里并没有出现具体的SQL语句，这是因为binlog关键字后面的内容是经过编码后的二进制日志。

这里面一个update语句包含如下事件：

Query事件，负责开始一个事务（begin）
Table_map事件，负责映射需要的表
Update_rows事件，负责写入数据
Xid事件，负责结束事务

下面命令将行事件以伪SQL的形式表现出来( -v 选项)：

mysqlbinlog  --no-defaults -v  "/var/lib/mysql/binlog.000011"

认真学习MySQL中的二进制日志(binlog)与中继日志（Relay log）_数据库_04

前面的命令同时显式binlog格式的语句，使用如下命令可以隐藏binlog格式的语句：

mysqlbinlog  --no-defaults -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog.000011"

认真学习MySQL中的二进制日志(binlog)与中继日志（Relay log）_数据库_05

关于mysqlbinlog工具的使用技巧还有很多，例如只解析对某个库的操作或者某个时间段内的操作等。下面是几个常用语句，更多操作可以参考官方文档。

# 可查看参数帮助
mysqlbinlog  --no-defaults --help

#查看最后100行
mysqlbinlog  --no-defaults -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog.000011"|tail -100

#根据position查找
mysqlbinlog  --no-defaults -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog.000011"|grep -A 20 '4939002'

上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息，下面介绍一种更为方便的查询命令：

show binlog events [in 'log_name'] [from pos] [limit [offset,] row_count];

in ‘log_name’ ：指定要查询的binlog文件名（不指定就是第一个binlog文件）
from pos：指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）
limit [offset]：偏移量（不指定就是0）
row_count：查询总条数（不指定就是所有行）

应用举例：

# 查询第一个最早的binlog日志
show binlog events;

# 指定查询日志文件
show binlog events in 'binlog.000011';

#指定pos点
show binlog events in 'binlog.000011' from 2000;

# 从pos点开始查询100条
show binlog events in 'binlog.000011' from 2000 limit 100;

# 从pos掉开始，偏移2行（即中间跳过2行）查询100条
show binlog events in 'binlog.000011' from 2000 limit 2,100;

上面都是基于binlog的默认格式，我们可以查看binlog的格式：

mysql> show VARIABLES like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

除此之外，binlog还有2种格式，分表是Statement和Mixed。

Statement

每一条会修改数据的SQL都会记录在binlog中。优点是不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。

5.1.5版本的MySQL才开始支持row level的复制，它不记录SQL语句上下文相关信息，仅保存哪条记录被修改。优点是row level的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。

Mixed

从5.1.8开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。

④ 删除二进制日志

MySQL的二进制文件可以配置自动删除，同时MySQL也提供了安全的手动删除二进制文件的方法。PURGE MASTER LOGS只删除指定部分的二进制日志文件，RESET MASTER删除所有的二进制日志文件。

① PURGE MASTER LOGS：删除指定日志文件

PURGE MASTER LOGS语法如下：

PURGE {MASTER | BINARY} LOGS TO '指定日志文件名'
PURGE {MASTER | BINARY} LOGS BEFORE '指定日期'

举例：

#查看二进制日志文件列表
show binary logs;

# 删除创建时间比binlog.000013早的日志
PURGE MASTER LOGS TO "binlog.000013"

# 删除20230105前创建的所有日志文件
PURGE MASTER LOGS before "20230105"

② RESET MASTER：删除所有二进制日志文件

使用 RESET MASTER语句，清空所有的binlog日志。MySQL会重新创建二进制文件，新的日志文件扩展名将重新从000001开始编号。

# 删除所有日志文件
RESET MASTER;

⑤ 其他场景

二进制日志可以通过数据库的全量备份和二进制日志中保存的增量信息，完成数据库的无损失恢复。但是，如果遇到数据量大、数据库和数据表很多（比如分库分表的应用）的场景，用二进制日志进行数据恢复，是很有挑战性的，因为起止位置不容易管理。

在这种情况下，一个有效的解决办法是配置主从数据库服务器，甚至是一主多从的架构，把二进制日志文件的内容通过中继日志，同步到从数据库服务器中，这样就可以有效避免数据库故障导致的数据异常问题。

⑥ binlog 与redo log对比

redo log是物理日志，记录内容是“在某个数据页上做了什么修改”，属于InnoDB存储引擎层产生的。

而binlog是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的C字段加1”，属于MySQL Server层。

虽然它们都属于持久化的保证，但是侧重点不同：

redo log 让InnoDB存储引擎拥有了崩溃恢复能力
binlog保证了MySQL集群架构的数据一致性。

【2】使用日志恢复数据

如果MySQL服务器启用了二进制日志，在数据库出现意外丢失数据时，可以使用mysqlbinlog工具从指定时间点开始（例如，最后一次备份）直到现在或另一个指定的时间点的日志中恢复数据。

mysqlbinlog恢复数据的语法如下：

mysqlbinlog [option] filename|mysql -uuser -ppass;

命令可以这样理解：使用mysqlbinlog命令来读取filename中的内容，然后使用mysql命令将这些内容恢复到数据库中。

filename：是日志文件名
option：可选项，比较重要的两对option参数是 --start-datetime、–stop-datetime 和 --start-position、–stop-position。
–start-datetime和–stop-datetime：可以指定恢复数据库的起始时间点和结束时间点
–start-position和–stop-position：可以指定恢复数据的开始位置和结束位置

注意：使用mysqlbinlog命令进行恢复操作时，必须是编号小的先恢复。例如binlog.000011必须在binlog.000012之前恢复。

这里有个小细节需要注意的是，假设你要恢复的操作在binlog.000011文件中，那么建议先执行 flush logs; 这样会新生成一个binlog.000012，对当前数据库的修改操作会记录到新的binlog日志文件中，不会影响binlog.000011。

mysql> show binary logs;
+---------------+------------+-----------+
| Log_name      | File_size  | Encrypted |
+---------------+------------+-----------+
| binlog.000006 |   66374693 | No        |
| binlog.000007 |    8162695 | No        |
| binlog.000008 |  205361347 | No        |
| binlog.000009 |   10917409 | No        |
| binlog.000010 | 1073743498 | No        |
| binlog.000011 |  469251392 | No        |
+---------------+------------+-----------+
6 rows in set (0.10 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.34 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000011 | 469251436 | No        |
| binlog.000012 |       156 | No        |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)

① 基于pos进行数据恢复

假设我们误删除了student表的数据：

# 1.首先插入两条数据
mysql> insert into student (name,class) values ('jane','一班');
Query OK, 1 row affected (0.01 sec)

mysql> insert into student (name,class) values ('janus',' 二班);
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+-------+--------+
| id | name  | class  |
+----+-------+--------+
|  5 | jane  | 一班   |
|  6 | janus | 二班   |
+----+-------+--------+
2 rows in set (0.00 sec)

#2.误删除了数据
mysql> delete from student;
Query OK, 2 rows affected (0.01 sec)

此时先执行flush logs;命令新生成一个binlog文件，然后我们查看上一个binlog文件。

mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000011 | 469251436 | No        |
| binlog.000012 |       200 | No        |
| binlog.000013 |       531 | No        |
| binlog.000014 |       179 | No        |
| binlog.000015 |      1146 | No        |
| binlog.000016 |       156 | No        |
+---------------+-----------+-----------+
6 rows in set (0.01 sec)

即，这里我们要读取恢复的是binlog.000015文件。如果我们想依赖于pos进行恢复，可以使用下面语句查看：

mysql> show binlog events in 'binlog.000015';
+---------------+------+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+------+----------------+-----------+-------------+--------------------------------------+
| binlog.000015 |    4 | Format_desc    |         1 |         125 | Server ver: 8.0.26, Binlog ver: 4    |
| binlog.000015 |  125 | Previous_gtids |         1 |         156 |                                      |
| binlog.000015 |  156 | Anonymous_Gtid |         1 |         235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000015 |  235 | Query          |         1 |         315 | BEGIN                                |
| binlog.000015 |  315 | Table_map      |         1 |         382 | table_id: 96 (testindex.student)     |
| binlog.000015 |  382 | Write_rows     |         1 |         434 | table_id: 96 flags: STMT_END_F       |
| binlog.000015 |  434 | Xid            |         1 |         465 | COMMIT /* xid=1045 */                |
| binlog.000015 |  465 | Anonymous_Gtid |         1 |         544 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000015 |  544 | Query          |         1 |         624 | BEGIN                                |
| binlog.000015 |  624 | Table_map      |         1 |         691 | table_id: 96 (testindex.student)     |
| binlog.000015 |  691 | Write_rows     |         1 |         744 | table_id: 96 flags: STMT_END_F       |
| binlog.000015 |  744 | Xid            |         1 |         775 | COMMIT /* xid=1061 */                |
| binlog.000015 |  775 | Anonymous_Gtid |         1 |         854 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000015 |  854 | Query          |         1 |         934 | BEGIN                                |
| binlog.000015 |  934 | Table_map      |         1 |        1001 | table_id: 96 (testindex.student)     |
| binlog.000015 | 1001 | Delete_rows    |         1 |        1071 | table_id: 96 flags: STMT_END_F       |
| binlog.000015 | 1071 | Xid            |         1 |        1102 | COMMIT /* xid=1069 */                |
| binlog.000015 | 1102 | Rotate         |         1 |        1146 | binlog.000016;pos=4                  |
+---------------+------+----------------+-----------+-------------+--------------------------------------+

基于pos的恢复关键点在于起始pos的选择。如下图所示我们红色箭头指向了三个事务（两个insert一个delete）的begin位置。那么我们只需要恢复两个insert即可。针对当前binlog文件，我们起始pos可以设置为 --start-position=4 --stop-position=775 ，那么我们的恢复命令如下所示：

mysqlbinlog --start-position=4 --stop-position=775 --database=testindex /var/lib/mysql/binlog.000015|mysql -uroot -p123456 -v testindex

认真学习MySQL中的二进制日志(binlog)与中继日志（Relay log）_binlog_06

认真学习MySQL中的二进制日志(binlog)与中继日志（Relay log）_二进制日志_07

② 基于时间点进行数据恢复

那么基于时间点呢？这时候需要先使用如下命令找到时间点：

mysqlbinlog  --no-defaults -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog.000015"

认真学习MySQL中的二进制日志(binlog)与中继日志（Relay log）_mysql_08

认真学习MySQL中的二进制日志(binlog)与中继日志（Relay log）_binlog_09

也就是我们将发生在这个时间区间[2023-01-08 15:31:53,2023-01-08 15:32:26]的事件再重新执行一遍。

mysqlbinlog --start-datetime="2023-01-08 15:31:53" --stop-datetime="2023-01-08 15:32:26" --database=testindex /var/lib/mysql/binlog.000015|mysql -uroot -p123456 -v testindex

可能出现一个事务执行时间过短，那么就是同样的时间，一秒内执行完成，此时我们找到下一个事务的开始时间即可，多计算一些时间就可以了。即利用binlog进行数据恢复的本质是找到需要恢复的事件起始时间或起始pos，然后重新执行即可。

【3】二进制日志写入机制

binlog的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

我们可以通过 binlog_cache_size 参数控制单个线程binlog cache大小，如果存储内容超过了这个参数，就要暂存到磁盘（swap）。binlog日志刷盘流程如下：

认真学习MySQL中的二进制日志(binlog)与中继日志（Relay log）_数据库_10

上图的write，是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快
上图的fsync，才是将数据持久化到磁盘的操作

write和fsync的时机，可以由参数 sync_binlog 控制，默认是0。为 0 的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync（等同redolog中innodb_flush_log_at_trx_commit=2的场景）。虽然性能得到提升，但是机器宕机，page cache里面的binlog会丢失。如下图所示：

认真学习MySQL中的二进制日志(binlog)与中继日志（Relay log）_MySQL_11