MySQL中的 Binlog 深度解析及使用详情_Mysql_

前言：

MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

Binlog日志的两个最重要的使用场景

• MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
• 数据恢复：通过使用 mysqlbinlog工具来使恢复数据

配置文件参数说明

Binlog 日志功能默认是开启的，线上情况下 Binlog 日志的增长速度是很快的，在 MySQL 的配置文件 my.cnf 中提供一些参数来对 Binlog 进行设置。

[mysqld] 设置此参数表示启用binlog功能，并制定二进制日志的存储目录，开启binlog日志大概会有1%的性能损耗 log-bin=/home/mysql/binlog/ # 高版本MySQL需要server-id这个参数，提供一个集群中不重复的id值即可 server-id=1 #mysql-bin.*日志文件最大字节（单位：字节） #设置最大100MB max_binlog_size=104857600 #设置了只保留7天BINLOG（单位：天） expire_logs_days = 7 #binlog日志只记录指定库的更新 #binlog-do-db=db_name #binlog日志不记录指定库的更新 #binlog-ignore-db=db_name #写缓冲多少次，刷一次磁盘，默认0 sync_binlog=0

需要注意的是： max_binlog_size ：Binlog 最大和默认值是 1G，该设置并不能严格控制 Binlog 的大小，尤其是 Binlog 比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性不可能做切换日志的动作，只能将该事务的所有 SQL 都记录进当前日志直到事务结束。所以真实文件有时候会大于 max_binlog_size 设定值。 expire_logs_days ：Binlog 过期删除不是服务定时执行，是需要借助事件触发才执行，事件包括：

• 服务器重启
• 服务器被更新
• 日志达到了最大日志长度 max_binlog_size
• 日志被刷新

常用的Binlog操作命令

# 是否启用binlog日志 show variables like 'log_bin'; # 查看binlog的目录 show global variables like "%log_bin%"; # 查看当前服务器使用的biglog文件个数及大小 show binary logs; # 查看最新一个binlog日志文件名称和Position show master status; # 事件查询命令 ## IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件) ## FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算) ## LIMIT [offset,] ：偏移量(不指定就是0) ## row_count ：查询总条数(不指定就是所有行) show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]; # 查看具体一个binlog文件的内容 （in 后面为binlog的文件名） show binlog events in 'master.000003'; #分页显示、过滤日志 pager less pager grep "drop" # 设置binlog文件保存事件，过期删除，单位天 set global expire_log_days=3; # 删除当前的binlog文件 reset master; # 删除slave的中继日志 reset slave; # 删除指定日期前的日志索引中binlog日志文件 purge master logs before '2019-03-09 14:00:00'; # 删除指定日志文件 purge master logs to 'master.000003';

写Binlog的时机

对支持事务的引擎如InnoDB而言，必须要提交了事务才会记录binlog。

binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关。

• 如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新；
• 如果设置为不为0的值，则表示每 sync_binlog 次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。
• 设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响。

如果 sync_binlog=0 或 sync_binlog大于1，当发生电源故障或操作系统崩溃时，可能有一部分已提交但其binlog未被同步到磁盘的事务会被丢失，恢复程序将无法恢复这部分事务。

在MySQL 5.7.7之前，默认值 sync_binlog 是0，MySQL 5.7.7和更高版本使用默认值1，这是最安全的选择。一般情况下会设置为100或者0，牺牲一定的一致性来获取更好的性能。

Binlog文件以及扩展

binlog日志包括两类文件:

• 二进制日志索引文件（文件名后缀为.index）用于记录所有有效的的二进制文件
• 二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML语句事件

当遇到以下3种情况时，MySQL会重新生成一个新的日志文件，文件序号递增：

• MySQL服务器停止或重启时；
• 使用 flush logs 命令；
• 当 binlog 文件大小超过 max_binlog_size 变量的值时；

max_binlog_size 的最小值是4096字节，最大值和默认值是 1GB (1073741824字节)。事务被写入到binlog的一个块中，所以它不会在几个二进制日志之间被拆分。因此，如果你有很大的事务，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的日志都记录到当前日志文件中，直到事务结束，你可能会看到binlog文件大于 max_binlog_size 的情况。

Binlog与Redo log区别

最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。binlog 和 redo log 在一定程度上都能恢复数据，但是二者有着本质的区别，具体内容如下：

• binlog 是 MySQL 本身就拥有的，不管使用何种存储引擎，binlog 都存在，而 redo log 是 InnoDB 存储引擎特有的，只有 InnoDB 存储引擎才会输出 redo log。
• binlog 是一种逻辑日志，记录的是这个语句的原始逻辑，比如 "给 ID=2 这一行的 c 字段加 1"。而 redo log 是一种物理日志，记录的是 "在某个数据页上做了什么修改"。
• redo log 具有幂等性，多次操作的前后状态是一致的，而 binlog 不具有幂等性，记录的是所有影响数据库的操作。例如插入一条数据后再将其删除，则 redo log 前后的状态未发生变化，而 binlog 就会记录相应的插入操作和删除操作。
• binlog 只会在事务提交时一次性写入，其日志的记录方式与事务的提交顺序有关，并且一个事务的 binlog 中间不会插入其他事务的 binlog。而 redo log 记录的是物理页的修改，最后一个提交的事务记录会覆盖之前所有未提交的事务记录，并且一个事务的 redo log 中间会插入其他事务的 redo log。
• binlog 是追加写入，写完一个日志文件再写下一个日志文件，不会覆盖使用，而 redo log 是循环写入，日志空间的大小是固定的，会覆盖使用。
• binlog 一般用于主从复制和数据恢复，并且不具备崩溃自动恢复的能力，而 redo log 是在服务器发生故障后重启 MySQL，用于恢复事务已提交但未写入数据表的数据。

Binlog写入过程

其实 binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache（用于缓存 binlog 的内存缓冲区）中，等到事务提交时，再把 binlog cache 写到 binlog 文件中。注意，这里是每个事务线程都有一个自己的缓冲区。一个事务的 binlog 不能被拆分，因此不论这个事务多大，也会确保一个事务中产生的 binlog 要被一次性写入到磁盘中，所以一个事务的 binlog 是完整的，中间不会插入其他事务的 binlog。

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。事务提交时，执行器会把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。

可以看到，每个线程有自己 binlog cache，但是共用同一份 binlog 文件。

• 图中的 write 是指把日志写入到文件系统的 page cache，但并没有把数据持久化到磁盘。
• 图中的 fsync 才是将数据持久化到磁盘的操作。一般我们认为 fsync 才占磁盘的 IOPS。

write 和 fsync 的时机，是由参数 ****sync_binlog 控制的：

• sync_binlog = 0 时，表示每次提交事务都只 write，不 fsync，fsync 交由操作系统去实现。
• sync_binlog = 1 时，表示每次提交事务都会执行 fsync。
• sync_binlog = N(N>1) 时，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。但是对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。但我建议你设置成 1，这样可以保证 MySQL 异常重启后 binlog 不丢失。

二阶段提交

MySQL 事务在提交的时候，会记录事务日志和二进制日志，也就是 redo log 和 binlog。这里就存在一个问题：对于事务日志和二进制日志，MySQL 会先记录哪种呢？我们通过下面这个语句，来看一下 MySQL 在执行这个简单的 UPDATE 语句时的内部流程：

mysql> update T set c=c+1 where ID=2;

执行流程如下图所示：

可以看到，MySQL 将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是两阶段提交。两阶段提交的目的是为了让两份日志之间的逻辑一致。由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者反过来。那这两种方式会有什么问题呢？假设在执行 UPDATE 语句的过程中在写完第一个日志后，第二个日志还没写完时发生了 crash，会出现什么情况？

假设先写 redo log，那么当 redo log 写完，binlog 还没有写完时发生了 crash。因为 MySQL 崩溃恢复时依赖的是 redo log 做数据恢复，所以恢复后存在这条更新语句。但由于 binlog 没写完就 crash 了，所以 binlog 里就没有这条语句。因为 MySQL 数据复制依赖的是 binlog，所以如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的数据就会与原库不同。

假设先写 binlog，那么当 binlog 写完，redo log 还没有写完时发生了 crash。崩溃恢复后这个事务是无效的。但 binlog 里已经记录了这个改动。所以，在之后用 binlog 来恢复时就多了一个事务出来，也与原库数据不同。

两阶段提交是怎么保证逻辑一致的呢？

当未开启 binlog 时，如果要执行一条 UPDATE 语句，MySQL 会先写 redo log buffer（便于事务回滚），然后再在 Buffer Pool 中修改对应的缓存页，当准备提交事物时会把 redo log 刷新到磁盘，然后事务就提交了。如果开启 binlog 后，我们就不能简单地写完 redo log 就提交事务了，否则 redo log 与 binlog 之间的逻辑是不一致的。此时，写完 redo log 文件后并不直接提交事务，而是将事务标记为处于 prepare 阶段，等到 binlog 也写入到文件后，再将事务标记为 commit 状态，表示可以提交事务了，此时才会提交事务。

当 binlog 写完，redo log 还没 commit 前发生 crash，那崩溃恢复后 MySQL 如何处理？

MySQL 在崩溃恢复时会判断 redo log 中记录的事务日志是否完整，即是否有 commit 标识。如果有 commit 标识则直接提交事务，如果没有则需要判断对应的事务在 binlog 上是否存在并完整。如果在 binlog 上是完整的则也要提交事务（因为 binlog 已经写入了，之后会被从库用，所以主库也要提交这个事务），否则回滚事务。因此如果在上图中的时刻 B 发生了 crash，崩溃恢复后该事务会被提交。

因为每个事务都有一个唯一的事务 id，redo log 和 binlog 在记录日志时都会关联相应的事务 id，所以 redo log 和 binlog 就通过事务 id 关联了起来。

另外，在 MySQL 5.6.2 版本后，还引入了 binlog-checksum 参数，用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因，可能会在日志中间出错的情况，MySQL 可以通过校验 checksum 值来发现。

redo 与 binlog 的刷盘时机

在两阶段提交过程中，时序上 redo log 先 prepare，再写 binlog 文件，最后再把 redo log 修改为 commit。这个过程中 redo log 文件需要修改两次。如果把 innodb_flush_log_at_trx_commit 参数设置成 1，那么 redo log 在 prepare 阶段就要进行一次持久化，由于崩溃恢复逻辑可以依赖于 prepare 的 redo log 加上 binlog 来恢复，以及每秒一次的后台轮询对 redo log 的刷盘操作。因此，InnoDB 认为 redo log 在 commit 时就不需要再 fsync 了，只 write 到文件系统的 page cache 中就够了，所以，redo log 的 commit 阶段就不会刷盘了。

通常所说的 MySQL 的双 1 配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。

能否只用 redo log 不要 binlog？

如果只从崩溃恢复的角度来讲是可以的。你可以把 binlog 关掉，这样就没有两阶段提交的过程了，而系统依然是 crash-safe 的。但 binlog 有着 redo log 无法替代的功能。

• 一个是归档。redo lo

  提示： 本文由神整理自网络，如有侵权请联系本站删除！
  本站声明：
  1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持；
  2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！

  上一篇：MySQL安装时一直卡在starting server的问题及解决方法_Mysql_

  下一篇：MySQL一些常用高级SQL语句详解_Mysql_