BinLog，又称二进制日志，是MySQL服务层的数据日志。所有的MySQL存储引擎都支持BinLog。BinLog记录了MySQL中的数据更新和可能引起数据更新的事件，可用于主从复制或数据恢复。本文将详细介绍BinLog的原理。BinLogMySQL的BinLog用于记录所有的MySQL数据变化和可能引起数据变化的事件。这些BinLog以二进制日志的形式依次存储在磁盘上。用户无法通过文本编辑器直接查看BinLog的内容，需要使用MySQL提供的mysqlbinlog工具查看文件。需要注意的是，MySQL的BinLog位于Server层，所有的数据库引擎都支持BinLog。MySQL的层次结构如下：启用BinLog在MySQL中，可以使用如下命令查看是否启用了BinLog。默认情况下，MySQL5.7中的BinLog是禁用的：showvariableslike'%log_bin%';可以在MySQL的配置文件中配置[mysqld]添加如下配置，然后重启MySQL服务即可达到开启BinLog的目的：[mysqld]log-bin=mysql-bin添加配置后重启容器，可以看到BinLog的状态变成了ON：文件为mysql-bin格式，我们可以通过以下命令查看正在写入的日志文件名：命令：刷新日志；每次重启MySQL服务，都会生成一个新的二进制日志文件，相当于二进制日志切换。切换二进制日志时，您会看到日志文件末尾的数字递增。另外，除了这些BinLog文件，MySQL还会生成一个DB-Server-bin.index文件，里面存放了所有二进制日志文件的列表，也称为二进制文件的索引。删除BinLogs，我们可以使用如下命令查看所有二进制文件的文件名：showbinarylogs;MySQL的BinLog可以手动删除，也可以设置自动清理。手动删除有如下删除命令：purgebinarylogstomysql-bin.000001：删除某个日志之前的所有二进制日志文件。该命令会修改索引中的相关数据；purgebinarylogsbefore'2017-03-1010:10:00'：清除某个时间点之前的二进制日志文件；purgemasterlogsbeforedate_sub(now(),interval7day)：清除7天前的二进制日志文件；resetmaster：清除所有二进制日志文件（目前没有主从复制关系）；可以通过设置expire_logs_days变量来启用自动清理，默认值为0，表示不启用自动过期删除功能，如果启用自动清理功能，则表示超过这个天数的二进制日志文件将被删除会被自动删除，自动删除一般发生在MySQL启动或者FLUSH日志的时候。BinLog格式MySQL共有三种BinLog格式，各有优缺点：BinLoginStatementformat：在这种模式下，MySQL会记录所有可能改变数据的SQL语句；BinLoginRowformat：在这种模式下，会记录数据库每一行的数据变化；BinLoginMixedformat：Statement和Row格式的混合；在MySQL中，可以通过以下命令查看BinLog的格式：showvariableslike'binlog_format'Statement格式的BinLogStatement格式的BinLog会记录每条可能修改数据库数据的SQL语句，相同的SQL可以在对应的机器上执行实现主从复制或数据恢复时的数据一致性。但是Statement不支持一些特殊的SQL语句，比如包含UUID函数的语句/LOADDATAINFILE语句。与如何启用BinLog类似，我们可以通过设置MySQL配置文件来修改BinLog的格式。通过如下配置，我们可以将MySQL的BinLog格式设置为Statement格式：[mysqld]log-bin=mysql-binbinlog-format="STATEMENT"修改配置文件后，重启MySQL，新生成的BinLog在Statement中format：也可以在MySQL启动的时候加上参数--binlog-format=STATEMENT，设置BinLog的格式为Statement。BinLog格式是Statement格式，我们切换到新建一个BinLog文件，往数据库表中插入数据：flushlogs;insertintouser_info(age,name)VALUES(1,'ssss')上面的语句执行完后，MySQL会生成一个新的BinLog文件，通过showbinlogeventsin'mysql-bin.000004'语句，我们可以看到BinLog中存储了上述Insert语句和对应的数据库信息：BinLoginRowformatBinLoginRowformat将记录每行数据的修改，但是Row格式的BinLog往往比较大。例如对于SQL语句updateuser_infosetname='test'where1=1，Statement格式的BinLog只会存储这条SQL语句，但是对于Row格式的BinLog，生成的日志大小取决于桌子。如果有1亿条数据，那么需要生成1亿条BinLog记录。与Statement格式类似，我们可以通过如下配置将MySQLBinLog格式设置为Row格式：[mysqld]log-bin=mysql-binbinlog-format="ROW"也可以加上参数--binlog-format=ROWMySQL启动时设置BinLog的格式为Row。修改配置文件后，重启MySQL，新生成的BinLog为ROW格式。同样，我们向数据库表中插入数据，切换到一个新的BinLog文件，一次更新多条数据：flushlogs;insertintouser_info(age,name)VALUES(2,'aaaa');insertintouser_info(age,name)VALUES(1,'aaaa');flushlogs;updateuser_infosetname='sss'where1=1;通过mysqlbinlogmysql-bin.000012-vv语句，我们可以看到上面语句的InsertBinLog信息。在Row格式中，BinLog记录每一行数据值的变化：Row格式中的BinLog也有不同的记录方式，可以通过参数binlog_row_format来设置。FULL：记录修改行的所有列数据；MINIMAL：只记录修改行中有数据变化的列；NOBOLB：和FULL类似，只有当blog或者text列没有被修改的时候，这些属性才不会被记录在Mixed格式的BinLog中通过上面的分析，我们知道BinLog的Statement和Row格式各有优缺点：Statementformat：优点：日志量小，节省磁盘和网络IO；缺点：需要记录语句的上下文（如时间等），非确定性函数（如UUID）无法复制；行格式：优点：可以记录数据库中的所有变化；缺点：如果单条SQL语句涉及的行很多，会导致日志量非常大；混合格式BinLog结合了Statement和Row格式的优点。对于普通的SQL语句，使用Statement格式的BinLog记录。对于一些特殊的SQL（比如包含UUID的SQL），会使用ROW格式的BinLog记录。对于数据库隔离级别为read-committed或read-uncommitted的场景，Mixed会使用ROW格式的BinLog来存储记录。与Statement格式类似，我们可以通过如下配置将MySQLBinLog格式设置为MIXED格式：[mysqld]log-bin=mysql-binbinlog-format="MIXED"也可以加上参数--binlog-format=MIXEDMySQL启动时设置BinLog的格式为MIXED。接下来，我们切换到一个新的BinLog文件，执行两条SQL，一条可以Statement格式记录到BinLog，一条不能：flushlogs;insertintouser_info(age,name)VALUES(1,'aaaa');insertintouser_info(age,name)VALUES(RAND(),'bbbb');使用mysqlbinlog--base64-output=DECODE-ROWS-vmysql-bin.000014命令分析下图从日志文件可以看出，对于第一条SQL语句insertintouser_info(age,name)VALUES(1,'aaaa');,BinLog使用Statement格式记录，对于第二条sql语句insertintouser_info(age,name)VALUES(RAND(),'bbbb');，由于insert语句中包含随机数，不能通过语句复制。MySQL使用Row格式的BinLog来记录行数据的变化。BinLog的作用MySQL的BinLog主要有以下两个功能：数据恢复：数据库数据丢失后，我们可以从某个时间节点的数据备份和该时间点之后的BinLog中恢复数据库的数据；主从复制：主从复制过程中，主库将自己的BinLog发送给从库，从库通过解析BinLog同步主库的数据变化，从而实现主从数据一致性;数据恢复MySQL数据库可以恢复某个时间点的状态，这个恢复过程是通过BinLog实现的。BinLog会记录数据库的所有逻辑操作，它采用“追加写入”的形式。如果你的DBA承诺半个月内可以恢复，那么备份系统会保存最近半个月的所有BinLog，系统会定时备份整个数据库。这里的“定期”取决于系统的重要性，可以是一天一次，也可以是一周一次。当你需要恢复到指定秒数时，比如某天下午2点，你发现中午12:00时不小心删除了一张表，你需要找回数据，那么你可以这样做this：首先，找到最新的全量备份，如果你运气好的话，可能是昨晚的备份，从这个备份恢复到临时库；然后从备份的时间点开始，把备份的BinLog一个一个取出来，重放到中午不小心删表前的那一刻。这样你的临时库就和误删前的在线库一样了，然后你就可以把表数据从临时库中取出来，根据需要恢复到在线库中。在主从复制的高并发场景下，单节点MySQL无法满足并发需求。在这种情况下，可以添加新的MySQL实例来提高性能。添加MySQL实例的方法有很多种。本节只介绍主从机制。MySQL的主从复制是一个异步复制过程。数据将从一个MySQL数据库（Master）复制到另一个MySQL数据库（Slave）。Master和Slave之间主从复制的整个过程是由三个线程参与完成的。其中两个线程（SQL线程和IO线程）在Slave端，另一个线程（I/O线程）在Master端。要实现MySQL主从复制，必须先在Master端开启binlog记录功能，否则无法实现。MySQL主从复制的步骤如下：根据上图对主从复制过程的分析，可以看出MYSQL主从复制包括以下步骤：在主机上执行startslave命令从服务器打开主从复制开关，开始主从复制。Slave服务器的IO线程会通过master上的授权复制用户权限请求连接到Master服务器，并请求执行binlog日志文件中指定位置（日志文件名和位置通过执行change指定配置主从复制服务时执行master命令。）并开始发送binlog日志内容。Master服务器收到Slave服务器IO线程的请求后，其上负责复制的IO线程会根据IO请求的信息，批量读取指定binlog日志文件指定位置后的binlog日志信息Slave服务器的线程，然后返回给Slave端IO线程。返回的信息除了binlog日志内容外，还包括Master服务器端记录的IO线程。返回的信息中，除了binlog中下一个指定的更新位置。当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件、位置点时，会将binlog日志内容写入RelayLog（中继日志）文件（Mysql-relay-bin.xxx)，并在master-info文件中记录新的binlog文件名和位置，以便下次在master端读取新的binlog日志时，可以告诉Master服务器从指定的文件开始读取和newbinlogloglocationNewbinloglogcontentSlaveserver上的SQL线程会实时检测本地RelayLog中IO线程新增的日志内容，然后将RelayLOG文件中的内容解析成SQL语句及时，并在自己的Slave服务器上解析SQL语句。依次执行应用的sql语句，将当前应用relaylog的文件名和位置记录在relay-log.info中。BinLog相关参数log_bin_basename：自-MySQL5.6.2起，用于指定二进制文件名，默认值为datadir+'/'+hostname+'-bin'。该参数不需要设置，也不能在my.cnf中设置，否则会报错；log_bin_index：Since-MySQL5.6.4，二进制日志的索引文件名，可在my.cnf中设置；log_bin_trust_function_creators：默认为OFF，这个参数开启会限制存储过程、函数、触发器的创建；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_disk_use）缓存的大量binlog_cache；max_binlog_cache_size：BinLog可以使用的最大内存缓存大小。在执行多语句事务时，如果max_binlog_cache_size不够大，系统可能会报错“Multi-statementtransactionrequiredmorethan'max_binlog_cache_size'bytesofstorage”；max_binlog_stmt_cache_size：max_binlog_cache_size是针对事务性语句的，max_binlog_stmt_cache_size是针对非事务性语句的，当我们发现Binlog_cache_disk_use或者Binlog_stmt_cache_disk_use比较大的时候，就需要考虑增加缓存的大小；max_binlog_size：表示二进制日志的最大值，一般设置为512M或1GB，但不能超过1GB。这个设置不能严格控制binarylog的大小，尤其是当binarylog比较近，遇到比较大的事务时。为了保证事务的完整性，不可能切换日志。所有的SQL都记录到当前日志中，直到事务结束；binlog_checksum：主从校验复制时的数据校验，NONE表示不生成校验和，CRC-32表示使用该算法校验binlog_format：指定二进制日志的类型，有STATEMENT、ROW、MIXED三个值。MySQL5.7.6之前默认为STATEMENT模式，MySQL5.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二进制日志与格式选择详解混合日志格式下的binlog（七）MySQL大师-slave复制与读写分离实战
