【Mysql 实战】问题分析利器之 binlog
一 摘要
在【Mysql-InnoDB 系列】事务提交过程及系列文章中，对 mysql（InnoDB 引擎）的 redolog、undolog、binlog 及事务的提交过程有了一些介绍，本篇将尝试去实践 binlog 在日常操作中的查看、分析方式，以及可能遇到的问题和解决方法。
二 binlog 的位置
2.1 寻找方式
2.1.1 文件遍历
直接简单粗暴地根据命名查找，因为 binlog 文件通常都是 mysql-bin.000001 这样的明明方式，所以 sudo find / -name mysql-bin.000001 即可。但效率低下，所以一般不会这样查找。
2.1.2 配置文件
可以通过配置文件查看，Linux/Mac 系统中通常是/etc/my.cnf。在实际查找时，也可以查看配置文件使用的优先级，mysql 提供了相关的命令：mysqld --verbose --help | grep -A 1 'Default options'
在本地执行后的结果如下：
可见，mysql 加载配置文件的优先级是：
1、/etc/my.cnf
2、/etc/mysql/my.cnf
3、/usr/local/etc/my.cnf
4、~/.my.cnf
本地配置了/etc/my.cnf，所以直接 vi /etc/my.cnf 查看即可。内容如下：
标红的部分就是 bin-log 的配置位置。
此时目录下的文件列表：
2.2 客户端常用命令
2.2.1 查看开启状态及基本配置
命令： show variables like 'log_%';
可以查看 binlog 的相关信息（也可以通过这种方式查看 binlog 位置），log_bin 的 value，ON 表示开启，否则关闭：
2.2.2 查看所有 binlog 日志列表
命令：show master logs;
2.2.3 查看最新一个 binlog 日志的编号名称，及其最后一个操作事件结束点
命令：show master status;
2.2.4 清空 binlog 日志
命令：reset master;
三 查看方法
3.1 直接查看内容
cat mysql-bin.000022 查看文件内容：
有很多乱码，同时也能大概看出一部分以往的操作，例如两个建表语句。
3.2 正确方式
3.2.1 mysqlbinlog
mysql 本身提供了 binlog 的查看工具： mysqlbinlog，与 mysqld、mysql 同位于 mysql 的 bin 目录下。使用方式为： mysqlbinlog 【binlog 文件路径】。例如在我们刚才查到的 binlog 目录下，mysqlbinlog mysql-bin.000022：
很遗憾，出了点小问题，抛了 unknown variable 'default-character-set=utf8mb4'的错误。到本文上面翻一下 my.cnf，可以看到其中配置了默认字符集，而 mysqlbinlog 表示无法识别。
3.2.2 解决方法
Mysql操作binlog时报错这篇文章中提到过两种方法，第一种是修改配置，把 default-character-set=utf8mb4 修改为 character-set-server = utf8mb4，但这种方式需要修改 my.cnf 并重启 mysql 服务。生产环境显然不适合。而且本地版本（5.7.28）如此操作后，发现也并没有解决问题，所以跳过。
方法二：
mysqlbinlog --no-defaults mysql-bin.000022
经验证可以生效，查询结果如下：
与图 6 直接查看文本的结果对比，可以看到不再有乱码，我们可以据此分析 mysql 操作过程中产生的 binlog，并进而分析线上问题。
四 一个真实的问题分析案例
4.1 案例操作重现
案例篇 | 记一次 MySQL 主从双写导致的数据丢失问题 这篇文章中提到了两台互为主从的机器上都进行了写入导致数据丢失的排查记录，使用的主要方法就是分析 binlog（Row 格式下的 RelayLog 重放）。
我们尝试在本地复现其中章节： 二、Row 格式下 RelayLog 的重放 的内容，在本地分析 update 语句带来的 binlog 变更：
mysqlbinlog --no-defaults mysql-bin.000025 查看，binlog 的内容如下：
奇怪的是，BINLOG 下出现了类似 base64 编码后的结果，而不是 Update 语句，是否是日志格式的问题？查看 binlog 格式配置，使用命令: show variables like '%binlog%';
日志格式确实是 ROW，有讨论说在 MIX 格式下会出现乱码，那么我们的这个就不是 binlog_format 的问题了，继续尝试其他方法。在mysqlbinlog解析binlog乱码问题解密 中找到了一种方法，通过--base64-output=DECODE-ROWS -v 参数来格式化输出 binlog 中的 Base64 部分，输出如下（因为后续有操作，又生成了一个新的 binlog 文件）：
在第 32 行，终于看到了预期的结果：
4.2 binlog 格式问题分析
Mysql 官方文档中有 binlog 格式的详细描述：5.4.4.1 Binary Logging Formats，包括:STATEMENT，ROW，MIXED 三种格式。
Replication capabilities in MySQL originally were based on propagation of SQL statements from source to replica. This is called statement-based logging. You can cause this format to be used by starting the server with --binlog-format=STATEMENT.
In row-based logging, the source writes events to the binary log that indicate how individual table rows are affected. It is important therefore that tables always use a primary key to ensure rows can be efficiently identified. You can cause the server to use row-based logging by starting it with --binlog-format=ROW.
A third option is also available: mixed logging. With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases as described below. You can cause MySQL to use mixed logging explicitly by starting mysqld with the option --binlog-format=MIXED.
翻译如下：
1、MySQL 中的复制功能最初是基于 SQL 语句从源到副本的传播。这称为基于语句的日志记录。可以通过 mysqld 命令，指定--binlog-format=STATEMENT 参数来启动服务；
2、在基于行的日志记录中，源将事件写入二进制日志，以指示单个表行如何受到影响。因此，表始终使用主键来确保可以有效地标识行是很重要的；可以通过 mysqld 命令，指定--binlog-format=ROW 参数来启动服务；
3、还有第三种选择：混合日志记录。对于混合日志记录，默认情况下使用基于语句的日志记录，但在某些情况下，日志记录模式会自动切换到基于行的日志记录。可以通过 mysqld 命令，指定--binlog-format=MIXED 参数来启动服务。
不过需要注意，STATEMENT 格式在生产环境应该很少使用，因为类似于：
这里的一些函数最好用 row 模式，因为主从复制的时候，uuid 已经 now()等会造成时间延迟，故而为了数据一致性，STATEMENT 格式不是最佳选择。
5.4.4.2 Setting The Binary Log Format也指出，可以通过下面语句在全局中设置 binlog_format：
也可以仅在当前会话中设置：
五 总结
本文由一个看到的真实案例分享开始，引起对 binlog 的学习和基础操作。在复现操作的过程中，遇到并解决了一些问题。希望可以对大家有一定的参考作用。
版权声明: 本文为 InfoQ 作者【程序员架构进阶】的原创文章。
原文链接:【http://xie.infoq.cn/article/292a5239487cd93588e67a3c7】。文章转载请联系作者。
磨炼中成长，痛苦中前行 2017.10.22 加入
微信公众号【程序员架构进阶】。多年项目实践，架构设计经验。曲折中向前，分享经验和教训
评论