MySQL学习笔记（五）Binlog解读

我们在使用和维护MySQL时，一定经常听到binlog这个概念。binlog在主从复制，数据恢复等场景都有着重要作用。本篇文章主要介绍binlog的概念，功能及常用操作，旨在帮助大家对binlog有更深的了解。

基本概念

binlog是一个二进制格式的文件，记录所有对数据库表结构产生变更的操作（insert，update，delete，create，alter...）。binlog文件存放在/var/log/mysql/目录下，包括两类文件，索引文件mysql-bin.index和日志文件mysql-bin.00000x。索引文件记录所有的binlog日志文件，日志文件则记录SQL语句。

主要用途

数据恢复：可用于回档或故障迁移恢复。比如腾讯云MySQL实例的回档功能基于冷备数据+binlog实现，可以将云数据库或表回档到指定时间。

主从复制：MySQL通过主从复制保持所有主从节点的数据一致性，这个过程依赖于binlog的传输回放等。具体流程在下面有详细介绍。

数据审计：通过binlog记录的SQL语句，可用来审计追踪用户操作。

binlog相关参数

max_binlog_size

指定单个binlog文件的最大值。如果超过该值，就会创建一个新的binlog文件，后缀名+1，并记录到index索引文件中。腾讯云mysql实例默认值为256MB，开源mysql从5.0起默认值为1GB. 要注意的是，这个参数并不能严格控制Binlog的大小，如果一个binlog文件容量快满了，但正好遇到一个大事务写入。为保证事务的完整性，不能直接切换到新日志，只能将该事务的所有SQL都记录进当前binlog，直到事务结束。此时binlog文件大小就会超过设置最大值。

binlog_do_db binlog_ingore_db

这两个参数表示需要写入或需要忽略写入哪些库的日志到binlog中。默认都为空，表示需要将所有库的日志都同步到binlog.

binlog_format

指定binlog记录模式，有statement，row，mixed三种。详细介绍见文章下一部分“binlog记录模式”。

binlog_cache_size

当使用支持事务的存储引擎（例如InnoDB引擎），所有未提交（uncommitted）的binlog首先会记录到一个缓存中，等该事务提交（committed）时，再将缓存中的数据写入binlog文件里。这个缓冲就是binlog_cache，binlog_cache_size则决定该缓冲的大小，默认是32KB. 此外，binlog_cache_size 参数是基于会话的，当线程开始一个事务时，MySQL会按照这个参数大小自动分配缓存，用于记录binlog。

sync_binlog

上面我们提到，事务提交时，Mysql将binlog_cache中的数据写入binlog文件，但不是每次写都会及时同步到磁盘。如果数据库异常宕机，可能会有最后一部分binlog没能写入磁盘，导致丢失。sync_binlog 参数决定了每写缓冲多少次就同步到磁盘。

sync_binlog=0 表示当事务提交后，不执行同步写磁盘指令，而是让文件系统自行决定什么时候来做同步。该参数默认值为0，这种方案性能最佳，但风险也最大。一旦数据库宕机，在文件系统缓存中的所有binlog数据都会丢失；

sync_binlog=1 表示每次事务提交， 均执行一次同步写磁盘指令，将binlog文件同步刷新到磁盘。这种方案风险最低，数据库宕机时，系统最多只会丢失1个事务的数据。高安全性的代价就是牺牲了性能，写binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，还是对MySQL和IO性能有很大影响。所以通常sync_binlog会设置为100或者0，通过牺牲一定的一致性，可以获得更高的并发和性能；

sync_binlog=n 表示在进行n次事务提交之后，执行一次同步写磁盘指令，将binlog文件同步刷新到磁盘。

binlog记录模式

在MySQL5.1版本之前，binlog的记录格式都是基于SQL语句的statement格式。5.1版本后开始引入binlog_format参数，该参数可设的值也增加到了3种：statement，row，mixed.

statement：基于 SQL 语句的复制。

优点：binlog日志量占用小，节约了IO，提高性能。

缺点：有些SQL语句对于复制有一定要求，例如rand，uuid，now等函数，需要同时记录这些语句在执行的时候的某些相关信息，才能保证在主从节点上执行得到相同结果。这种情况下，只复制SQL语句可能导致数据不一致甚至出错。另外，statement格式下不支持将InnoDB的事务隔离级别设置为READ COMMITTED，有可能造成主从数据不一致。

row：基于行的复制，记录每行实际数据的变更。

优点：复制过程准确，不会出现某些特定情况下的存储过程，函数，以及触发器的调用和触发无法被正确复制的问题。且mysql5.1版本开始，row格式下可以将InnoDB的事务隔离级别设置为READ COMMITTED，从而获得更好的并发性。

缺点：大量增加binlog的磁盘占用，在一些大表中清除大量数据时在 binlog 中会生成很多条语句，可能导致从库延迟变大。

mixed：在该模式下，MySQL会根据执行的每一条具体的sql语句来灵活选择记录格式。一般的语句修改使用statment格式来提高性能，在遇到一些statement无法完成主从复制的操作时，则采用row格式保存binlog。

优点：综合其他两种模式的特点，准确性强，文件大小适中。

缺点：可能导致主从不一致。

mysql主从复制

为保证高可用（High Availability），目前MySQL多采用主从（master-slave）架构，一个主节点加若干从节点。主从架构不仅可以进行读写分离，还可以在主节点宕机时快速拉起一个从节点充当主节点，恢复高可用。MySQL通过主从复制保证数据在所有节点中的一致性。

主从复制原理

MySQL主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个(I/O thread, SQL thread)运行在从节点。

首先，当从节点连接主节点时，主节点会为每一个从节点创建一个log dump 线程，用于发送binlog. 每个从节点会创建一个I/O线程用来请求接收主节点上的binlog。成功接收到的更新会被I/O线程保存在本地的relay log中。从节点上的SQL线程负责读取relay log中的内容，解析成具体的操作在本地执行，完成整个复制过程。过程的原理图如下图所示。

主从复制原理

主从复制模式

MySQL主从复制有3种模式

1. 异步复制

异步复制如下图所示，这种模式下主节点将所有更新写入binlog文件之后就返回客户端结果，而不会主动推送binlog到从节点。一旦主节点宕机，从节点会因为没有同步到最新binlog导致数据丢失。相对地，该模式处理效率更高，响应速度快。mysql 主从复制默认是异步模式。

异步复制

2. 半同步复制

半同步复制如下图所示，这种模式下主节点在本地写入binlog后，会主动推送binlog到从节点。在接收到其中一台从节点的返回信息后，再返回给客户端，否则需要等待直到超时时间然后切换成异步模式再提交。相比异步复制，提高了数据安全性，也降低了主从延迟。但性能上会有一定的降低，响应时间会变长。

半同步模式不是MySQL内置的，从MySQL 5.5开始集成，需要主从节点安装插件开启半同步模式。

半同步复制

3. 全同步复制

全同步复制是指主节点和全部从节点都执行了commit并确认才会向客户端返回成功。在牺牲了效率的前提下，保证了数据的强一致性。

binlog相关操作

介绍常见的操作命令，以下命令均在腾讯云MySQL实例上执行。

1. 查看是否开启binlog，云实例默认是开启状态，开源MySQL默认是关闭状态。

show variables like 'log_bin';

查看是否开启binlog

2. 查看、修改当前使用的binlog_format.

show variables like 'binlog_format';

查看当前使用的binlog_format

修改全局binlog_format时需要谨慎操作，确保更改后不会对当前复制带来影响。为保证安全，腾讯云MySQL实例禁止修改该参数，默认为row.

set globle binlog_format='MIXED';

修改binlog_format

4.查看当前主库正在写入的binlog文件位置

show master status\G;

查看当前主库正在写入的binlog文件位置

5. 查看指定binlog文件的内容

show binlog events in 'mysql-bin.00000x';

查看指定binlog文件的内容

6. 获取binlog文件列表

show binary logs;

获取binlog文件列表

7. 查看单个binlog文件的最大值

show variables like '%max_binlog%';

查看单个binlog文件的最大值（腾讯云mysql实例中该参数默认是256MB）

8. 手动刷新binlog文件，生成新的binlog文件。注意，服务器在重启时，也会调用flush logs操作。所以重启服务器也会自动生成新的binlog文件。

flush logs;

手动刷新binlog文件

9. 删除所有binlog，从001文件开始重新记录（谨慎操作！）

reset master;

删除所有binlog

参考文档：

[1] 姜成尧 . MySQL技术内幕：InnoDB存储引擎[M]．北京：机械工业出版社，2010.11.