mysql介绍 InnoDB学习（三）之BinLog

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-binmysql介绍，但是不指定 file_name，重启数据库后，MySQL的BinLog文件名称为 mysql-bin格式，我们可以通过以下命令查看正在写的日志文件名：

show master status

如果您想将当前写入的日志文件切换到下一个文件，可以执行以下命令进行切换：

[En]

If you want to switch the currently written log file to the next file, you can switch it by executing the following command:

flush logs;

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

BinLogs删除

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

show binary logs;

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

自动清理可以通过设置 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 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格式各有优缺点：

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，同时系统会定期做整库备份。这里的”定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。

当您需要恢复到指定的秒数时，例如，某天下午12：00发现错误删除了表，并且需要检索数据时，您可以这样做：

[En]

When you need to restore to a specified second, for example, when you find that there is an erroneous deletion of the table at 12:00 one afternoon, and you need to retrieve the data, you can do this:

首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；然后，从备份的时间点开始，将备份的BinLog依次取出来，重放到中午误删表之前的那个时刻。

这样，您的临时库与误删除前的在线库相同，然后可以根据需要将表数据从临时库中取出并恢复到在线库中。

[En]

In this way, your temporary library is the same as the online library before it was deleted by mistake, and then you can take the table data out of the temporary library and restore it to the online library as needed.

主从复制

在高并发的场景下，单节点的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， 在牺牲一定的一致性下，获取更高的并发和性能。

（编辑：云计算网_宿迁站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!