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-binbinlog-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-binbinlog-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-binbinlog-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主从复制及读写拆散实战

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