BinLog 又称为二进制日志，是 MySQL 服务层的数据日志，MySQL 所有的存储引擎都反对 BinLog。BinLog 记录了 MySQL 中的数据更新和可能导致数据更新的事件，能够用于主从复制或数据恢复。本文会对 BinLog 的原理进行具体介绍。

BinLog

MySQL 的 BinLog 用于记录 MySQL 的所有数据变更和可能造成数据变更的事件，这些 BinLog 以二进制日志的模式顺序存储在磁盘中。用户不能间接通过文本编辑器查看 BinLog 的内容，须要借助 MySQL 提供的 mysqlbinlog 工具能力查看文件。

须要留神的是，MySQL 的 BinLog 位于 Server 层，所有的数据库引擎都反对 BinLog。MySQL 的分层构造如下所示：

BinLog 的开启

MySQL 中能够通过以下命令查看 BinLog 是否开启，默认状况下 MySQL5.7 的 BinLog 处于敞开状态：

show variables like '%log_bin%';

能够通过在 MySQL 配置文件 [mysqld] 中增加如下配置，而后重启 MySQL 服务，达到开启 BinLog 的目标：

[mysqld]
log-bin=mysql-bin

增加配置并重启容器后，能够看到 BinLog 的状态曾经变为ON：

BinLog 的切换

如果在 my.cnf 外面只设置 log-bin=mysql-bin，然而不指定file_name，重启数据库后，MySQL 的 BinLog 文件名称为mysql-bin 格局，咱们能够通过以下命令查看正在写的日志文件名：

show master status 

如果你心愿切换以后写的日志文件为下一个文件，能够通过执行以下命令进行切换：

flush logs;

每次重启 MySQL 服务也会生成一个新的二进制日志文件，相当于二进制日志切换。切换二进制日志时，你会看到日志文件开端的数字会一直递增。另外，除了这些 BinLog 文件外，MySQL 还会生成了一个 DB-Server-bin.index 的文件，这个文件中存储所有二进制日志文件的清单，又称为二进制文件的索引。

BinLogs 删除

咱们能够通过以下命令查看所有二进制文件的文件名称：

show binary logs;

MySQL 的 BinLog 能够手工删除，也能够设置主动清理，手工删除有以下删除命令：

• purge binary logs to mysql-bin.000001：删除某个日志之前的所有二进制日志文件。这个命令会批改 index 中相干数据；
• purge binary logs before '2017-03-10 10:10:00'：革除某个工夫点以前的二进制日志文件；
• purge master logs before date_sub(now(), interval 7 day)：革除 7 天前的二进制日志文件；
• reset master：革除所有的二进制日志文件（以后不存在主从复制关系）；

主动清理能够通过设置 expire_logs_days 变量来启用，默认值为 0, 示意不启用过期主动删除性能，如果启用了主动清理性能，示意超出此天数的二进制日志文件将被主动删除，主动删除工作通常产生在 MySQL 启动时或 FLUSH 日志时。

BinLog 的格局

MySQL 有三种 BinLog 格局，各有优劣：

1. Statement 格局的 BinLog：此模式下 MySQL 会记录所有可能会变更数据的 SQL 语句；
2. Row 格局的 BinLog：：此模式下会记录数据库每一行数据的变动状况；
3. Mixed 格局的 BinLog：Statement 和 Row 格局的混合；

MySQL 中能够通过以下命令查看 BinLog 的格局：

show variables like 'binlog_format'

Statement 格局的 BinLog

Statement 格局的 BinLog 会记录每一条可能批改数据库数据的 sql 语句，主从复制或数据恢复时能够在对应机器上执行同样的 SQL 来达到数据的统一。然而 Statement 不反对一些非凡的 SQL 语句，如语句中蕴含 UUID 函数 /LOAD DATA IN FILE 语句等。

和启用 BinLog 的形式相似，咱们能够通过设置 MySQL 的配置文件来批改 BinLog 的格局，通过如下配置咱们能够设置 MySQL 的 BinLog 格局为 Statement 格局：

[mysqld]
log-bin=mysql-bin
binlog-format="STATEMENT"

批改配置文件之后，重启 MySQL，新生成的 BinLog 就是 Statement 格局了：

也能够在 MySQL 启动时增加参数 --binlog-format=STATEMENT 设置 BinLog 的格局为 Statement.

BinLog 格局为 Statement 格局下，咱们切换到新的 BinLog 文件，并向数据库的表中插入数据：

flush logs;
insert into user_info (age, name) VALUES (1,'ssss')

上述语句执行完之后，MySQL 会生成一个新的 BinLog 文件，通过 show binlog events in 'mysql-bin.000004' 语句，咱们能够看到 BinLog 中存储了上述的 Insert 语句以及对应的数据库等信息：

Row 格局的 BinLog

Row 格局的 BinLog 会记录每一行数据被批改的状况，然而 Row 格局的 BinLog 往往会比拟大。比方对于 SQL 语句update user_info set name='test' where 1=1，Statement 格局的 BinLog 只会存储这条 SQL 语句，然而对于 Row 格局的 BinLog，生成日志的大小就取决于表的大小，如果表中有 1 亿条数据，那么就须要生成 1 亿条 BinLog 记录。

和 Statement 格局相似，咱们能够通过如下配置设置 MySQL 的 BinLog 格局为 Row 格局：

[mysqld]
log-bin=mysql-bin
binlog-format="ROW"

也能够在 MySQL 启动时增加参数 --binlog-format=ROW 设置 BinLog 的格局为 Row.

批改配置文件之后，重启 MySQL，新生成的 BinLog 就是 ROW 格局了。同样的，咱们向数据库的表中插入数据，切换搭到新的 BinLog 文件，并一次更新多条的数据：

flush logs;
insert into user_info (age, name) VALUES (2,'aaaa');
insert into user_info (age, name) VALUES (1,'aaaa');

flush logs;
update user_info set name='sss' where 1=1;

通过 mysqlbinlog mysql-bin.000012 -vv 语句，咱们能够看查看到上述的 Insert 语句的 BinLog 信息。Row 格局下，BinLog 记录了每一行数据值的变更状况：

Row 格局的 BinLog 也有不同的记录形式，能够通过参数 binlog_row_format 设置。FULL: 记录批改行的所有列数据；MINIMAL: 仅记录批改行中有产生数据变动的列；NOBOLB: 和 FULL 形式类似，仅仅是当 blog 或 text 这些列没有进行批改时，不会记录这些属性的列

Mixed 格局的 BinLog

通过下面的剖析，咱们晓得 BinLog 的 Statement 和 Row 格局各有优缺点：

• Statement 格局：长处：日志量小，节约磁盘和网络 IO；毛病：须要记录语句的上下文 (如工夫等)，不具备确定性的函数(如 UUID) 无奈复制；
• Row 格局：长处：能够记录数据库的所有变更；毛病：如果单个 SQL 语句波及的行均比拟多，那么会导致日志量十分大；

Mixed 格局的 BinLog 联合了 Statement 和 Row 格局的长处，对于一般的 SQL 语句应用 Statement 格局的 BinLog 记录，对于一些非凡的 SQL（如蕴含 UUID 的 SQL），应用 ROW 格局的 BinLog 记录。

对于数据库隔离级别为读已提交或读未提交的场景，Mixed 会应用会应用 ROW 格局的 BinLog 存储记录。

和 Statement 格局相似，咱们能够通过如下配置设置 MySQL 的 BinLog 格局为 MIXED 格局：

[mysqld]
log-bin=mysql-bin
binlog-format="MIXED"

也能够在 MySQL 启动时增加参数 --binlog-format=MIXED 设置 BinLog 的格局为 MIXED.

接下来咱们切换搭到新的 BinLog 文件，并执行两条 SQL，一条能够用 Statement 格局的 BinLog 记录，另外一条不能够：

flush logs;
insert into user_info (age, name) VALUES (1,'aaaa');
insert into user_info (age, name) VALUES (RAND(),'bbbb');

从下图应用 mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000014 命令解析的日志文件能够看出，对于第一条 SQL 语句insert into user_info (age, name) VALUES (1,'aaaa');，BinLog 应用 Statement 格局记录，对于第二条 SQL 语句insert into user_info (age, name) VALUES (RAND(),'bbbb');，因为插入语句中蕴含随机数，无奈通过 Statement 复制，MySQL 应用了 Row 格局的 BinLog 记录了行数据的变更。

BinLog 的作用

MySQL 的 BinLog 次要有以下两个作用：

1. 数据恢复：数据库数据失落后，咱们能够从某个工夫节点的数据备份和该工夫点之后的 BinLog 来复原数据库的数据；
2. 主从复制：主从复制过程中，主数据库将本身的 BinLog 发送给从数据库，从数据库通过解析 BinLog 同步主数据库的数据变更，从而达到主从数据统一；

数据恢复

MySQL 数据库能够复原某个工夫点的状态，这个复原过程就是通过 BinLog 实现的。BinLog 会记录数据库所有的逻辑操作，并且是采纳“追加写”的模式。如果你的 DBA 承诺说半个月内能够复原，那么备份零碎中肯定会保留最近半个月的所有 BinLog，同时零碎会定期做整库备份。这里的“定期”取决于零碎的重要性，能够是一天一备，也能够是一周一备。

当须要复原到指定的某一秒时，比方某天下午两点发现中午十二点有一次误删表，须要找回数据，那你能够这么做：

1. 首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份复原到长期库；
2. 而后，从备份的工夫点开始，将备份的 BinLog 顺次取出来，重放到中午误删表之前的那个时刻。

这样你的长期库就跟误删之前的线上库一样了，而后你能够把表数据从长期库取出来，按须要复原到线上库去。

主从复制

在高并发的场景下，单节点的 MySQL 无奈满足并发量需要，这时就能够通过新增 MySQL 实例来晋升性能。新增 MySQL 实例有多种形式，本节只介绍主从机制。

MySQL 的主从复制是一个异步的复制过程，数据将从一个 MySQL 数据库（Master）复制到另一个 MySQL 数据库（Slave），在 Master 和 Slave 之间实现整个主从复制的过程是由三个线程参加实现的。其中两个线程（SQL 线程和 IO 线程）在 Slave 端，另一个线程（I/ O 线程）在 Master 端。

要实现 MySQL 的主从复制，首先必须关上 Master 端的 binlog 记录性能，否则就无奈实现。MySQL 主从复制的步骤如下所示：

依据上图剖析主从复制的流程，能够看出 MYSQL 主从复制蕴含以下步骤：

1. 在 Slave 服务器上执行 start slave 命令开启主从复制开关，开始进行主从复制。
2. Slave 服务器的 IO 线程会通过在 master 上曾经受权的复制用户权限申请连贯 Master 服务器，并申请从执行 binlog 日志文件中的指定地位（日志文件名和地位就是在配置主从复制服务时执行 change master 命令指定的）之后开始发送 binlog 日志内容。
3. Master 服务器接管来自 Slave 服务器的 IO 线程的申请后，其上负责复制的 IO 线程会依据 Slave 服务器的 IO 线程申请的信息分批读取指定 binlog 日志文件指定地位之后的 binlog 日志信息，而后返回给 Slave 端的 IO 线程。返回的信息中除了 binlog 日志内容外，还有在 Master 服务器端记录的 IO 线程。返回的信息中除了 binlog 中的下一个指定更新地位。
4. 当 Slave 服务器的 IO 线程获取到 Master 服务器上 IO 线程发送的日志内容、日志文件及地位点后，会将 binlog 日志内容顺次写到 Slave 端本身的 RelayLog（即中继日志）文件（Mysql-relay-bin.xxx）的最末端，并将新的 binlog 文件名和地位记录到 master-info 文件中，以便下一次读取 master 端新 binlog 日志时能通知 Master 服务器从新 binlog 日志的指定文件及地位开始读取新的 binlog 日志内容
5. Slave 服务器端的 SQL 线程会实时检测本地 Relay Log 中 IO 线程新增的日志内容，而后及时把 Relay LOG 文件中的内容解析成 sql 语句，并在本身 Slave 服务器上按解析 SQL 语句的地位程序执行利用这样 sql 语句，并在 relay-log.info 中记录以后利用中继日志的文件名和地位点

BinLog 相干参数

1. log_bin_basename：Since-MySQL 5.6.2，用于指定二进制文件名，默认值为 datadir + ‘/’ + hostname + ‘-bin’。该参数不须要设置，也不能在 my.cnf 中设置，否则会报错；
2. log_bin_index：Since-MySQL 5.6.4，二进制日志的索引文件名，能够在 my.cnf 中设置；
3. log_bin_trust_function_creators：默认为 OFF，这个参数开启会限度存储过程、Function、触发器的创立；
4. sql_log_bin：管制会话级别二进制日志性能的开启或敞开，默认为 ON，示意启用二进制日志性能；
5. expire_logs_days：BinLog 保留的时长；
6. binlog_cache_size：为每个客户端调配 binlog_cache_size 大小的缓存，默认值 32768。BinLog 缓存应用的前提条件是服务器端应用了反对事务的引擎以及开启了 BinLog 性能，它是 MySQL 用来进步 BinLog 的效率而设计的一个用于短时间内长期缓存 BinLog 数据的内存区域。一般来说，如果咱们的数据库中没有什么大事务，写入也不是特地频繁，2MB～4MB 是一个适合的抉择。然而如果咱们的数据库大事务较多或多事务语句，写入量比拟大，可适当调高 binlog_cache_size。同时，咱们能够通过 binlog_cache_use 以及 binlog_cache_disk_use 来剖析设置的 binlog_cache_size 是否足够，是否有大量的 binlog_cache 因为内存大小不够而应用临时文件（binlog_cache_disk_use）来缓存了；
7. max_binlog_cache_size：BinLog 可能应用的最大内存缓存的大小。当执行多语句事务时，max_binlog_cache_size 如果不够大，零碎可能会报出“Multi-statement transaction required more than‘max_binlog_cache_size’bytes of storage”的谬误；
8. max_binlog_stmt_cache_size：max_binlog_cache_size 针对事务语句，max_binlog_stmt_cache_size 针对非事务语句，当咱们发现 Binlog_cache_disk_use 或者 Binlog_stmt_cache_disk_use 比拟大时就须要思考增大 cache 的大小；
9. max_binlog_size：示意二进制日志的最大值，个别设置为 512M 或 1GB，但不能超过 1GB。该设置并不能严格控制二进制日志的大小，尤其是二进制日志比拟凑近为不而又遇到一根比拟大事务时，为了保障事务的完整性，不可能做切换日志的动作，只能将该事务的所有 SQL 都记录进以后日志，直到事务完结；
10. binlog_checksum：主从校检复制时的数据校验，NONE 示意不生成 checksum，CRC-32 示意应用这个算法做校检
11. binlog_format：指定二进制日志的类型，别离有 STATEMENT、ROW、MIXED 三种值，MySQL 5.7.6 之前默认为 STATEMENT 模式，MySQL 5.7.7 之后默认为 ROW 模式，这个参数次要影响主从复制。

12. sync_binlog：这个参数对于 Mysql 零碎来说是至关重要的，它不仅影响到二进制日志文件对 MySQL 所带来的性能损耗，而且还影响到 MySQL 中数据的完整性：

    sync_binlog=0，当事务提交后，Mysql 仅仅是将 binlog_cache 中的数据写入 binlog 文件，但不执行 fsync 之类的磁盘同步指令告诉文件系统将缓存刷新到磁盘，而是让 Filesystem 自行决定什么时候来做同步。MySQL 中默认的设置是 sync_binlog=0，即不作任何强制性的磁盘刷新指令，这个设置性能是最好的，但危险也是最大的。一旦零碎解体（Crash），在文件系统缓存中的所有二进制日志信息都会失落。从而带来数据不残缺问题。sync_binlog=n，在进行 n 次事务提交当前，Mysql 将执行一次 fsync 之类的磁盘同步指令，同时文件系统将 Binlog 文件缓存刷新到磁盘。能够适当的调整 sync_binlog，在就义肯定的一致性下，获取更高的并发和性能。

我是御狐神，欢送大家关注我的微信公众号:wzm2zsd

参考文档

MySQL 官网文档
Binlog 详解
binlog 浅析
mysql 二进制日志格式化_Mysql 二进制日志及格局抉择
彻底解析 Mixed 日志格局的 binlog
(七) MySQL 主从复制及读写拆散实战

本文最先公布至微信公众号，版权所有，禁止转载！