MySQL binlog 学习及使用笔记

官方文档

一些基础知识

The binary log is not used for statements such as SELECT or SHOW that do not modify data. To log all statements (for example, to identify a problem query), use the general query log. See Section 5.4.3, “The General Query Log”.

Binlog 只记录修改数据的记录。SELECT 和 SHOW 这种不造成数据修改的查询是不会记录的。

Passwords in statements written to the binary log are rewritten by the server not to occur literally in plain text. See also Section 6.1.2.3, “Passwords and Logging”.

如果 SQL 语句中有密码信息，会自动被重写，不会以明文形式出现。

开启关闭 binlog

Binary logging is enabled by default (the log_bin system variable is set to ON). The exception is if you use mysqld to initialize the data directory manually by invoking it with the --initialize or --initialize-insecure option, when binary logging is disabled by default, but can be enabled by specifying the --log-bin option.

查看 binlog 是否开启：

1	show VARIABLES LIKE "log_bin";

In MySQL 5.7, a server ID had to be specified when binary logging was enabled, or the server would not start. In MySQL 8.0, the server_id system variable is set to 1 by default. The server can be started with this default ID when binary logging is enabled, but an informational message is issued if you do not specify a server ID explicitly using the server_id system variable. For servers that are used in a replication topology, you must specify a unique nonzero server ID for each server.

日志命名

--log-bin[=base_name]

MySQL binlog后面的编号如何取值？ | 《Linux就该这么学》

日志格式 Binary Logging Formats(–binlog-format)

官方文档：MySQL :: MySQL 8.0 Reference Manual :: 5.4.4.1 Binary Logging Formats

The format of the events recorded in the binary log is dependent on the binary logging format. Three format types are supported: row-based logging, statement-based logging and mixed-base logging. The binary logging format used depends on the MySQL version. For general descriptions of the logging formats, see Section 5.4.4.1, “Binary Logging Formats”. For detailed information about the format of the binary log, see MySQL Internals: The Binary Log.

默认三种日志格式：

STATEMENT：以 SQL 的形式记录所有变更。

ROW：记录每一行的变更。

MIXED：默认使用 STATEMENT 模式，部分情况下使用 ROW 模式。

注：至少从 MySQL 5.5 开始，都有着三个格式。

Note that the binary log format differs in MySQL 8.0 from previous versions of MySQL, due to enhancements in replication. See Section 17.5.2, “Replication Compatibility Between MySQL Versions”.

8.0 开始，日志格式有所不同。

binlog 配置参数

binlog_rows_query_log_events

This system variable affects row-based logging only. When enabled, it causes the server to write informational log events such as row query log events into its binary log. This information can be used for debugging and related purposes, such as obtaining the original query issued on the source when it cannot be reconstructed from the row updates.

These informational events are normally ignored by MySQL programs reading the binary log and so cause no issues when replicating or restoring from backup. To view them, increase the verbosity level by using mysqlbinlog’s --verbose option twice, either as -vv or --verbose --verbose.

这个参数只在 binlog 模式为 ROW 时起作用。当开启的时候，会记录下来 ROWS_QUERY_EVENT。

MySQL :: MySQL Internals Manual :: 14.10.3 ROWS_QUERY_EVENT

MySQL :: MySQL Internals Manual :: 14.9.4.3 Binlog Event Type

binlog_row_image

MySQL :: MySQL 8.0 Reference Manual :: 17.1.6.4 Binary Logging Options and Variables

安全考虑，binlog_row_image建议尽量使用FULL - MySQL分布式中间件DBLE - SegmentFault 思否

MySQL 5.7贴心参数之binlog_row_image - yayun - 博客园

参数binlog_row_image设置MINIMAL，你今天被坑了吗？-贺春旸的技术博客-51CTO博客

这个参数是 5.6 中引入的，那么在之前的版本中，没有这个参数时，数据库怎么处理这种情况呢？

Found the answer here, it does log all columns (in MySQL, and by logical extension MariaDB) before this parameter was supported:

https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_row_image quoted below:

“The default value is full. In MySQL 5.5 and earlier, full row images are always used for both before images and after images.”

——Replication and Binary Log System Variables - MariaDB Knowledge Base

在 MySQL 5.6 之前的版本中，可以认为 binlog_row_image 默认就是 full。

常用操作

查看 MySQL 的 binlog 配置

1	show variables like'log_bin%';

查看 binlog 保存/过期时间

建议使用：

1	SHOW VARIABLES LIKE '%expire%';

看一看具体有哪些参数。

查看 binlog 过期时间：

1	show variables like 'expire_logs_days';

或

1	select @@global.expire_logs_days;

expire_logs_days 为 0 表示 binlog 永不过期。

Mysql设置binlog过期时间并自动删除 - Ruthless - 博客园

MySQL 8.0 后加入了一个参数 binlog_expire_logs_seconds。

但是观察到 Azure 上的 Managed MySQL Service，虽然是 MySQL 5.7，但是也有这个参数，所以最好用 SHOW VARIABLES LIKE '%expire%'; 来看 binlog 过期参数设置，如果有 binlog_expire_logs_seconds 则需要注意。

MySQL :: MySQL 8.0 Reference Manual :: 17.1.6.4 Binary Logging Options and Variables

技术分享 | MySQL：你的 binlog_expire_logs_seconds 可能正在失效 - 腾讯云开发者社区-腾讯云

查看 binlog 文件大小限制

1	show variables like 'max_binlog_size';

限制单个 binlog 文件的大小。

MySQL - Binary Log File size growing more than the maximum limit - Database Administrators Stack Exchange

查看当前所有的 binlog 文件及文件大小

1	show binary logs;

查看 binlog 内容

查看第一个 binlog 文件的内容：

1	SHOW BINLOG EVENTS;

查看指定 binlog 文件的内容：

1	SHOW BINLOG EVENTS IN 'mysql-bin.000001';

指定起始 binlog position：

1	SHOW BINLOG EVENTS IN 'mysql-bin.000001' FROM 123;

这种情况下，指定的 binlog position 必须是一个有效的 binlog position，那么会报错：

1	Error when executing command SHOW BINLOG EVENTS: Wrong offset or I/O error

指定行号 offset 和 limit：

1	SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 0, 100;

MySQL :: MySQL 8.0 Reference Manual :: 13.7.7.2 SHOW BINLOG EVENTS Statement

坑点

修改配置文件没有用

修改 my.ini 的时候，注意，配置文件会被 [mysqld]、[mysql] 和 [client] 分为三段，要把关于 binlog 的配置写到 [mysqld] 对应的范围内。

跨库变更的记录问题

当 BINLOG 格式为 STATEMENT 的时候，部分跨库情况下的 binlog 记录和默认数据库有关系，详情可参考：

MySQL :: MySQL Replication :: 2.6.4 Binary Logging Options and Variables

同步相关

WRITE_ROW_EVENT、UPDATE_ROWS_EVENT、UPDATE_ROWS_EVENT 等事件之前总是有一个 TABLE_MAP_EVENT。

前后两个 event 通过 table_id 可以关联起来。

EventLength

MySQL 中规定 eventLength 的类型为 4 字节：https://dev.mysql.com/doc/internals/en/event-header-fields.html，意味着一个 event 最大长度不能超过 $2^{32}-1$。

事件类型

MySQL :: MySQL Internals Manual :: 14.9.4.3 Binlog Event Type

MySQL binlog中的事件类型 - iVictor - 博客园【MySQL 5.7】

常见事件类型

QUERY_EVENT

MySQL :: MySQL Internals Manual :: 14.9.4.9 QUERY_EVENT

UPDATE_ROWS_EVENT

既包含行 update 之前的值，也包含 update 之后的值。

MySQL Binlog解析 - 朱小厮的博客

其他

MySQL 8.0.1 更新，binlog 事件中包含列信息

More Metadata Is Written Into Binary Log | MySQL High Availability

编码

曾经遇到一个问题，读取 binlog 中的 DDL 的时候，解析出来的中文字段名都是乱码。排查后使用 Java 时没有设置为 UTF-8 编码，而 mysql-binlog-connector-java 读取 DDL 事件的时候使用的是 Java 的编码，导致解析出来的数据是乱码。启动 Java 时把编码设置为 UTF8（加上 -Dfile.encoding=UTF-8 启动参数）即可解决。

为什么把编码改成 UTF8 就没有问题呢？原因是 binlog 中保存的字段名等元数据信息都是以 UTF8 编码格式保存的，因为 MySQL 的系统编码（character_set_system）格式就是 UTF8 且是在源代码中硬编码不能修改的。所以读取 binlog 中的字段名等信息的时候也要以 UTF8 编码格式来读取，而 mysql-binlog-connector-java 这个库，读取 binlog 中的字段名的时候，使用的是 Java 设置的编码，如果 Java 设置的编码不是 UTF8，那么读取中文字段等非英文字符的时候就可能出现乱码问题。所以 mysql-binlog-connector-java 这个库有这个缺陷还需要优化。

关于 character_set_system，可以参考：

mysql 中有关字符集 character_set_xxx 系统参数的整理 - MySQL - NICECHI 博客

MySQL选择UTF-8作为元数据编码，用源码固定

——PolarDB 数据库内核月报

常见问题

查看 binlog 客户端同步进度

在 MySQL 服务器上执行

1	SHOW PROCESSLIST;

结果中 Command 为 Binlog Dump 的就是已经连接上的 binlog 客户端。

State 的可能值为：

Master has sent all binlog to slave; waiting for more updates，binlog 客户端已经完全接收完 binlog
Sending to client，binlog 客户端还在从数据库接收 binlog

示例：

Id	User	Host	db	Command	Time	State	Info
302	root	localhost:58953	fdldb	Sleep	2		NULL
355	root	localhost:64156	NULL	Binlog Dump	172569	Master has sent all binlog to slave; waiting for more updates	NULL
1162	root	localhost:64455	NULL	Binlog Dump	24	Sending to client	NULL

关于重置 binlog

1	RESET MASTER;

会删除所有 binlog 文件，重新开始 binlog 的记录。

参考资料

MySQL :: MySQL 8.0 Reference Manual :: 5.4.4 The Binary Log
MySQL :: MySQL 5.7 Reference Manual :: 4.2.2.2 Using Option Files
MySQL :: MySQL 5.7 Reference Manual :: 16.1.6.4 Binary Logging Options and Variables
maxwell实时同步mysql中binlog - kris12 - 博客园
腾讯工程师带你深入解析 MySQL binlog - 知乎
[MySQL Binlog(十一)——MySQL binlog event解析实例 | Win-Man’s Blog](https://win-man.github.io/2019/12/07/MySQL Binlog(十一)——MySQL binlog event解析实例/)
MySql-Binlog协议详解-报文篇 - 无毁的湖光-Al的个人空间 - OSCHINA - 中文开源技术交流社区
使用MySQLBinlog按时间查询二进制日志时易疏忽的地方 | 火丁笔记
浅析MySQL二进制日志 - iVictor - 博客园
如何远程备份MySQL binlog - iVictor - 博客园
Binlog中的时间戳 - 博文视点
MySQL :: MySQL 5.7 Reference Manual :: 13.4.1.2 RESET MASTER Statement
MySQL CDC, Streaming Binary Logs, and Asynchronous Triggers