共计 7337 个字符，预计需要花费 19 分钟才能阅读完成。
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 格局，各有优劣：
- Statement 格局的 BinLog：此模式下 MySQL 会记录所有可能会变更数据的 SQL 语句；
- Row 格局的 BinLog：：此模式下会记录数据库每一行数据的变动状况；
- 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 次要有以下两个作用：
- 数据恢复：数据库数据失落后，咱们能够从某个工夫节点的数据备份和该工夫点之后的 BinLog 来复原数据库的数据；
- 主从复制：主从复制过程中，主数据库将本身的 BinLog 发送给从数据库，从数据库通过解析 BinLog 同步主数据库的数据变更，从而达到主从数据统一；
数据恢复
MySQL 数据库能够复原某个工夫点的状态，这个复原过程就是通过 BinLog 实现的。BinLog 会记录数据库所有的逻辑操作，并且是采纳“追加写”的模式。如果你的 DBA 承诺说半个月内能够复原，那么备份零碎中肯定会保留最近半个月的所有 BinLog，同时零碎会定期做整库备份。这里的“定期”取决于零碎的重要性，能够是一天一备，也能够是一周一备。
当须要复原到指定的某一秒时，比方某天下午两点发现中午十二点有一次误删表，须要找回数据，那你能够这么做：
- 首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份复原到长期库；
- 而后，从备份的工夫点开始，将备份的 BinLog 顺次取出来，重放到中午误删表之前的那个时刻。
这样你的长期库就跟误删之前的线上库一样了，而后你能够把表数据从长期库取出来，按须要复原到线上库去。
主从复制
在高并发的场景下，单节点的 MySQL 无奈满足并发量需要，这时就能够通过新增 MySQL 实例来晋升性能。新增 MySQL 实例有多种形式，本节只介绍主从机制。
MySQL 的主从复制是一个异步的复制过程，数据将从一个 MySQL 数据库（Master）复制到另一个 MySQL 数据库（Slave），在 Master 和 Slave 之间实现整个主从复制的过程是由三个线程参加实现的。其中两个线程（SQL 线程和 IO 线程）在 Slave 端，另一个线程（I/ O 线程）在 Master 端。
要实现 MySQL 的主从复制，首先必须关上 Master 端的 binlog 记录性能，否则就无奈实现。MySQL 主从复制的步骤如下所示：
依据上图剖析主从复制的流程，能够看出 MYSQL 主从复制蕴含以下步骤：
- 在 Slave 服务器上执行
start slave命令开启主从复制开关，开始进行主从复制。 - Slave 服务器的 IO 线程会通过在 master 上曾经受权的复制用户权限申请连贯 Master 服务器，并申请从执行 binlog 日志文件中的指定地位（日志文件名和地位就是在配置主从复制服务时执行 change master 命令指定的）之后开始发送 binlog 日志内容。
- Master 服务器接管来自 Slave 服务器的 IO 线程的申请后，其上负责复制的 IO 线程会依据 Slave 服务器的 IO 线程申请的信息分批读取指定 binlog 日志文件指定地位之后的 binlog 日志信息，而后返回给 Slave 端的 IO 线程。返回的信息中除了 binlog 日志内容外，还有在 Master 服务器端记录的 IO 线程。返回的信息中除了 binlog 中的下一个指定更新地位。
- 当 Slave 服务器的 IO 线程获取到 Master 服务器上 IO 线程发送的日志内容、日志文件及地位点后，会将 binlog 日志内容顺次写到 Slave 端本身的 RelayLog（即中继日志）文件（Mysql-relay-bin.xxx）的最末端，并将新的 binlog 文件名和地位记录到 master-info 文件中，以便下一次读取 master 端新 binlog 日志时能通知 Master 服务器从新 binlog 日志的指定文件及地位开始读取新的 binlog 日志内容
- Slave 服务器端的 SQL 线程会实时检测本地 Relay Log 中 IO 线程新增的日志内容，而后及时把 Relay LOG 文件中的内容解析成 sql 语句，并在本身 Slave 服务器上按解析 SQL 语句的地位程序执行利用这样 sql 语句，并在 relay-log.info 中记录以后利用中继日志的文件名和地位点
BinLog 相干参数
log_bin_basename：Since-MySQL 5.6.2，用于指定二进制文件名，默认值为 datadir + ‘/’ + hostname + ‘-bin’。该参数不须要设置，也不能在 my.cnf 中设置，否则会报错；log_bin_index：Since-MySQL 5.6.4，二进制日志的索引文件名，能够在 my.cnf 中设置；log_bin_trust_function_creators：默认为 OFF，这个参数开启会限度存储过程、Function、触发器的创立；sql_log_bin：管制会话级别二进制日志性能的开启或敞开，默认为 ON，示意启用二进制日志性能；expire_logs_days：BinLog 保留的时长；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）来缓存了；max_binlog_cache_size：BinLog 可能应用的最大内存缓存的大小。当执行多语句事务时，max_binlog_cache_size 如果不够大，零碎可能会报出“Multi-statement transaction required more than‘max_binlog_cache_size’bytes of storage”的谬误；max_binlog_stmt_cache_size：max_binlog_cache_size 针对事务语句，max_binlog_stmt_cache_size 针对非事务语句，当咱们发现 Binlog_cache_disk_use 或者 Binlog_stmt_cache_disk_use 比拟大时就须要思考增大 cache 的大小；max_binlog_size：示意二进制日志的最大值，个别设置为 512M 或 1GB，但不能超过 1GB。该设置并不能严格控制二进制日志的大小，尤其是二进制日志比拟凑近为不而又遇到一根比拟大事务时，为了保障事务的完整性，不可能做切换日志的动作，只能将该事务的所有 SQL 都记录进以后日志，直到事务完结；binlog_checksum：主从校检复制时的数据校验，NONE 示意不生成 checksum，CRC-32 示意应用这个算法做校检binlog_format：指定二进制日志的类型，别离有 STATEMENT、ROW、MIXED 三种值，MySQL 5.7.6 之前默认为 STATEMENT 模式，MySQL 5.7.7 之后默认为 ROW 模式，这个参数次要影响主从复制。-
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 主从复制及读写拆散实战
本文最先公布至微信公众号，版权所有，禁止转载！
