关于数据库:图文结合带你搞懂MySQL日志之Binary-log二进制日志

往期回顾

图文联合带你搞定 MySQL 日志之 Undo log(回滚日志)
图文联合带你搞懂 InnoDB MVCC
图文联合带你搞懂 MySQL 日志之 Redo Log(重做日志)
图文联合带你搞懂 MySQL 日志之 Error Log（谬误日志）
图文联合带你搞懂 MySQL 日志之 Slow Query Log（慢查问日志）
图文联合带你搞懂 MySQL 日志之 relay log（中继日志）
图文联合带你搞懂 MySQL 日志之 General Query Log（通用查问日志）

此篇为图文联合搞懂 MySQL 日志的最初一篇，MySQL 中共有八大日志，其中数据定义语句日志不是给用户查看的，在此篇尾部简略介绍，接下来图文联合系列还会持续推出新文章！

binlog 能够说是 MySQL 中 比拟重要 的日志了，在日常开发及运维过程中，常常会遇到。

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

它以 事件模式 记录并保留在 二进制文件 中。通过这些信息，咱们能够再现数据更新操作的全过程。

如果想要记录所有语句（例如，为了辨认有问题的查问)，须要应用通用查问日志。

Binary log 次要利用场景：

一是用于 数据恢复，如果 MySQL 数据库意外进行，能够通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些批改，而后依据二进制日志文件中的记录来复原数据库服务器。
二是用于 数据复制，因为日志的延续性和时效性，master 把它的二进制日志传递给 slaves 来达到 master-slave 数据统一的目标。

能够说 MySQL数据库的数据备份、主备、单主、多主、MGR都离不开 Binary log, 须要依附 Binary log 来同步数据，保证数据一致性。

查看记录二进制日志是否开启：在 MySQL8 中默认状况下，二进制文件是开启的。

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                          |// 变更 sql 记录下来
+---------------------------------+-----------------------------+
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)。

形式 1：永久性形式

批改 MySQL 的 my.cnf 或 my.ini 文件能够设置二进制日志的相干参数：

[mysqld]
#启用二进制日志
log-bin=atguigu-bin
binlog_expire_logs_seconds= 600
max_binlog_size=100M

提醒:

log-bin=mysql-bin

关上日志(主机须要关上)，这个 mysql-bin 也能够自定义，这里也能够加上门路，如:/home/www/mysql_bin_log/mysql-bin

binlog_expire_logs_seconds

此参数管制二进制日志文件保留的时长单位是秒，默认 2592000 30 天 –14400 4 小时;86400 1 天; 259200 3 天;

max_binlog_size

管制单个二进制日志大小，以后日志文件大小超过此变量时，执行切换动作。此参数的 最大和默认值是 1GB，该设置并 不能严格控制 Binlog 的大小，尤其是 Binlog 比拟凑近最大值而又遇到一个比拟大事务时，为了保障事务的完整性，可能不做切换日志的动作只能将该事务的所有 SQL 都记录进以后日志，直到事务完结。个别状况下可采取默认值。

设置带文件夹的 bin-log 日志寄存目录

如果想扭转日志文件的目录和名称，能够对 my.cnf 或 my.ini 中的 log_bin 参数批改如下：

[mysqld]
log-bin="/var/lib/mysql/binlog/atguigu-bin"

留神：新建的文件夹须要应用 mysql 用户，应用上面的命令即可。

chown -R -v mysql:mysql binlog

提醒数据库文件 最好不要与日志文件放在同一个磁盘上!这样，当数据库文件所在的磁盘产生故障时，能够应用日志文件复原数据。

形式 2：临时性形式

如果不心愿通过批改配置文件并重启的形式设置二进制日志的话，还能够应用如下指令，须要留神的是在 mysql 8 中只有会话级别的设置，没有了 global 级别的设置。

# 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

# session 级别
mysql> SET sql_log_bin = 0 ;
Query OK, 0 rows affected (0.01 秒)

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

MySQL 服务重新启动一次，以“.000001”为后缀的文件就会减少一个，并且后缀名按 1 递增。即日志文件的数与 MySQL 服务启动的次数雷同；如果日志长度超过了 max_binlog_size 的下限（默认是 1GB），就会创立一个新的日志文件。

查看以后的二进制日志文件列表及大小。指令如下：

mysql> SHOW BINARY LOGS;
+--------------------+-----------+-----------+
| Log_name           | File_size | Encrypted |
+--------------------+-----------+-----------+
| atguigu-bin.000001 | 156       | No        |
+--------------------+-----------+-----------+
1 rows in set (0.00 sec)

所有对数据库的批改都会记录在 binglog 中。但 binlog 是二进制文件，无奈间接查看，借助 mysqlbinlog 命令工具了。指令如下: 在查看执行，先执行一条 sQL 语句，如下

update student set name='张三_back' where id=1;
[root@localhost ~]$ cd /var/lib/mysql
[root@localhost ~]$ mysqlbinlog  "/var/lib/mysql/lqhdb-binlog.000001"

执行后果能够看到，这是一个简略的日志文件，日志中记录了用户的一些操作，这里并没有呈现具体的 SQL 语句，这是因为 binlog 关键字前面的内容是通过编码后的二进制日志。

这里一个 update 语句蕴含如下事件

Query 事件负责开始一个事务(BEGIN)
Table_map 事件负责映射须要的表
Update_rows 事件负责写入数据
Xid 事件负责完结事务

上面命令将行事件以 伪 SQL的模式体现进去

mysqlbinlog -v "/var/lib/mysql/binlog/test.000002"

后面的命令同时显示 binlog 格局的语句，应用如下命令不显示它

mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/test.000002"

对于 mysqlbinlog 工具的应用技巧还有很多，例如只解析对某个库的操作或者某个时间段内的操作等。简略分享几个罕用的语句，更多操作能够参考官网文档。

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

# 查看最初 100 行
mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |tail - 100

# 依据 position 查找
mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |grep -A 
20 '4939002'

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

mysql> 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 日志文件，分成无效事件行的形式返回，并可应用 limit 指定 pos 点的起始偏移，查问条数。其它举例:

#a、查问第一个最早的 binlog 日志:
show binlog events\G ;

#b、指定查问 mysql-bin.088802 这个文件
show binlog events in 'atguigu-bin. 008002'\G;

#c、指定查问 mysql-bin. 080802 这个文件，从 pos 点:391 开始查起:
show binlog events in 'atguigu-bin.008802' from 391\G;

#d、指定查问 mysql-bin.000802 这个文件，从 pos 点:391 开始查起，查问 5 条（即 5 条语句)
show binlog events in 'atguigu-bin.000882' from 391 limit 5\G

#e、指定查问 mysql-bin.880002 这个文件，从 pos 点:391 开始查起，偏移 2 行〈即两头跳过 2 个）查问 5 条（即 5 条语句)。show binlog events in 'atguigu-bin.088882' from 391 limit 2,5\G;

binlog 格局查看

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   | // 行格局
+---------------+-------+
1 rows in set (0.00 sec)

除此之外，binlog 还有 2 种格局，别离是 Statemen 和Mixed

Statement 每一条会批改数据的 sql 都会记录在 binlog 中。长处：不须要记录每一行的变动，缩小了 binlog 日志量，节约了 IO，进步性能。
Row 5.1.5 版本的 MySQL 才开始反对 row level 的复制，它不记录 sql 语句上下文相干信息，仅保留哪条记录被批改。长处：row level 的日志内容会十分分明的记录下每一行数据批改的细节。而且不会呈现某些特定状况下的存储过程，或 function，以及 trigger 的调用和触发无奈被正确复制的问题。
Mixed 从 5.1.8 版本开始，MySQL 提供了 Mixed 格局，实际上就是 Statement 与 Row 的联合。

mysqlbinlog 复原数据的语法如下：

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

这个命令能够这样了解：应用 mysqlbinlog 命令来读取 filename 中的内容，而后应用 mysql 命令将这些内容复原到数据库中。

filename：是日志文件名。
option：可选项，比拟重要的两对 option 参数是–start-date、–stop-date 和 –start-position、–stop-position。
- –start-date 和 – -stop-date：能够指定复原数据库的起始工夫点和完结工夫点。
- –start-position 和–stop-position：能够指定复原数据的开始地位和完结地位。

留神：应用 mysqlbinlog 命令进行复原操作时，必须是编号小的先复原，例如 atguigu-bin.000001 必须在 atguigu-bin.000002 之前复原。

flush logs; #能够生成新的 binLog 文件，不然这个文件边复原边变大是不行的。show binary logs; # 显示有哪些 binLog 文件

复原数据

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

mysqlbinlog --no-defaults  --start-position=236  --stop-position=1071 --database=my_db1 /var/lib/mysql/lqhdb-bin.000002 | /usr/bin/mysql -root -p123456 -v my_db1

MySQL 的二进制文件能够配置主动删除，同时 MySQL 也提供了平安的手动删除二进制文件的办法。PURGE MASTER LOGS只删除指定局部的二进制日志文件，RESET MASTER删除所有的二进制日志文件。具体如下：

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

PURGE MASTER LOGS 语法如下：

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

举例 :应用 PURGE MASTER LOGS 语句删除创立工夫比 binlog.000005 早的所有日志

(1)屡次重新启动 MysSQL 服务，便于生成多个日志文件。而后用 SHOW 语句显示二进制日志文件列表

SHOW BINARY LOGS;

(2）执行 PURGE MASTER LOGS 语句删除创立工夫比 binlog.000005 早的所有日志

PURGE MASTER LOGS T0 "binlog. 000005";

(3）显示二进制日志文件列表

SHGW BINARY LOGS;

举例: 应用 PURGE MASTER LOGS 语句删除 2023 年 3 月 17 日前创立的所有日志文件。具体步骤如下:

(1) 显示二进制日志文件列表

SHOW BINARY LOGS;

(2）执行 mysqlbinlog 命令查看二进制日志文件 binlog.000005 的内容

mysqlbinlog --no-defaults "/var/lib/mysql/binlog/atguigu-bin.000005"

(3）应用 PURGE MASTER LOGS 语句删除 2023 年 3 月 17 日前创立的所有日志文件

PURGE MASTER LOGS before "20220317";

(4）显示二进制日志文件列表

SHOW BINARY LOGS;

2022 年 01 月 05 号之前的二进制日志文件都曾经被删除，最初一个没有删除，是因为以后在用，还未记录最初的工夫，所以未被删除。

2.RESET MASTER: 删除所有二进制日志文件

reset master;

二进制日志能够通过数据库的 全量备份 和二进制日志中保留的 增量信息 ，实现数据库的 无损失复原。然而，如果遇到数据量大、数据库和数据表很多（比方分库分表的利用）的场景，用二进制日志进行数据恢复，是很有挑战性的，因为起止地位不容易治理。

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

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

咱们能够通过 binlog_cache_size 参数管制单个线程 binlog cache 大，如果存储内容超过了这个参数，就要暂存到磁盘(Swap)。binlog 日志刷盘流程如下:

上图的 write，是指把日志写入到文件系统的 page cache，并没有把数据长久化到磁盘，所以速度比拟快。

上图的 fsync，才是将数据长久化到磁盘的操作

write 和 fsync 的机会，能够由参数 sync_binlog 管制，默认是 0。

为 0 的时候，示意每次提交事务都只 write，由零碎自行判断什么时候执行 fsync。尽管性能失去晋升，然而机器宕机，page cache 外面的 binglog 会失落。如下图：

为了平安起见，能够设置为 1，示意每次提交事务都会执行 fsync，就如同 redo log 刷盘流程一样。最初还有一种折中形式，能够设置为 N(N>1)，示意每次提交事务都 write，但累积 N 个事务后才 fsync。

在呈现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比拟大的值，能够晋升性能。同样的，如果机器宕机，会失落最近 N 个事务的 binlog 日志。

redo log 它是 物理日志，记录内容是“在某个数据页上做了什么批改”，属于 InnoDB 存储引擎层产生的。
而 binlog 是 逻辑日志，记录内容是语句的原始逻辑，相似于“给 ID=2 这一行的 c 字段加 1”，属于 MySQL Server 层
尽管它们都属于长久化的保障，然而则重点不同。
- redo log 让 InnoDB 存储引擎领有了解体恢复能力。
- binlog 保障了 MySQL 集群架构的数据一致性。

Enjoy GreatSQL :)

## 对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

相干链接：GreatSQL 社区 Gitee GitHub Bilibili

社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html

微信：扫码增加 GreatSQL 社区助手 微信好友，发送验证信息 加群。

关于数据库:图文结合带你搞懂MySQL日志之Binary-log二进制日志

二进制日志(Binary log)

查看默认状况

日志参数设置

查看日志

应用日志复原数据

删除二进制日志

其它场景

深刻了解二进制日志

写入机制

binlog 与 redolog 比照

GreatSQL 社区：

技术交换群：

Just My Socks（注册教程内含优惠码）

关于数据库:图文结合带你搞懂MySQL日志之Binary-log二进制日志

二进制日志(Binary log)

查看默认状况

日志参数设置

查看日志

应用日志复原数据

删除二进制日志

其它场景

深刻了解二进制日志

写入机制

binlog 与 redolog 比照

GreatSQL 社区：

技术交换群：

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）