关于数据库:图文结合带你搞懂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 中共有八大日志，其中数据定义语句日志不是给用户查看的，在此篇尾部简略介绍，接下来图文联合系列还会持续推出新文章！

二进制日志(Binary log)

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

提醒:

1. log-bin=mysql-bin

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

1. binlog_expire_logs_seconds

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

1. 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 日志。

binlog 与 redolog 比照

• 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

GreatSQL 社区：

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

技术交换群：

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