亲宝软件园·资讯

展开

MySQL Binlog

​ 灵魂的沉浮   ​ 人气:0

前言：

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

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

配置文件参数说明

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 过期删除不是服务定时执行，是需要借助事件触发才执行，事件包括：

常用的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 相关。

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

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

Binlog文件以及扩展

binlog日志包括两类文件:

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

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写入过程

其实 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 和 fsync 的时机，是由参数 ****sync_binlog 控制的：

因此，在出现 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 无法替代的功能。

总之，由于现在包括 MySQL 高可用在内的很多系统机制都依赖于 binlog，所以单靠 redo log 还做不到。

Binlog 组提交机制

若事务为非只读事务，则每次事务提交时需要进行一次 fsync 操作，以保证 redo log 都写入了磁盘。为了提高磁盘 fsync 的效率，MySQL 提供了组提交（group commit）功能，即一次 fsync 能够将多个事务的日志刷新到磁盘的日志文件中，而不用将每个事务的日志单独刷新到磁盘文件中，从而大大提升了日志刷盘的效率。

我们知道，如果开启了 binlog，则 MySQL 为了保证 binlog 和事务日志的一致性，使用了两阶段提交。

在两阶段提交写 binlog 的过程中，实际上是分成两步的：

下图详细展示了在二阶段提交过程中的日志写入时机：

MySQL 为了让组提交的效果更好，把 redo log 做 fsync 的操作拖到了步骤 3 中。这么一来，binlog 也可以进行组提交了，因为在 binlog 的 write 和 fsync 操作之间有了一小段间隔，这允许其他提交的事务也将 binlog write 到操作系统缓存中，后续通过 fsync 一并刷新到磁盘中。这种实现方式称为二进制日志组提交（Binary Log Group Commit，BLGC）。

不过通常情况下第 3 步执行得会很快，所以 binlog 的 write 和 fsync 操作的间隔时间很短，导致能集合到一起持久化的 binlog 比较少，因此 binlog 的组提交的效果通常不如 redo log 的效果那么好。如果想提升 binlog 组提交的效果，

可设置如下参数：

这两个条件是或的关系，即只要有一个满足条件就会调用 fsync 操作。注意，除非有大量的事务不断地进行写入和更新操作，否则不建议修改这个变量的值，这是因为修改后可能会导致事务的响应时间变长。

Binlog的日志格式

记录在二进制日志中的事件的格式取决于二进制记录格式。支持三种格式类型：

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT，在 MySQL 5.7.7 及更高版本中，默认值是 ROW。日志格式通过 binlog-format 指定，如 binlog-format=STATEMENT、binlog-format=ROW、binlog-format=MIXED。

Statement

每一条会修改数据的sql都会记录在binlog中

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 提高了性能。

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行的时候相同的结果。另外mysql的复制，像一些特定函数的功能，slave与master要保持一致会有很多相关问题。

Row

5.1.5版本的MySQL才开始支持 row level 的复制,它不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题.

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

注：将二进制日志格式设置为ROW时，有些更改仍然使用基于语句的格式，包括所有DDL语句，例如CREATE TABLE， ALTER TABLE，或 DROP TABLE。

Mixed

从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。 在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

Binlog 相关参数

在 MySQL 中，输入如下命令可以查看与 binlog 相关的参数。

# 是否启用binlog日志
show variables like 'log_bin';

示例如下：

其中，几个重要的参数如下所示：

清理过期的Binlog日志

在开启MySQL的主从后，会产生大量的binlog日志文件，可能产生大量的磁盘空间

手工删除binlog

# 删除master的binlog，慎用
reset master;
# 删除slave的中继日志
reset slave;
# 删除指定日期以前的日志索引中binlog日志文件
purge master logs before ‘2019-11-22 16:39:01';
# 删除指定日志文件的日志索引中binlog日志文件
purge master logs to ‘binlog.000002';

自动删除binlog

通过binlog参数（expire_logs_days）来实现MySQL自动删除binlog

show binary logs;
show variables like ‘expire_logs_days';
set global expire_logs_days=3;

用途

主从同步

同步原理：

如下图所示，MySQL主备复制基于二进制日志binlog。任何数据更改都会写入二进制日志。

数据库管理员搭建主备复制时，只需要在备库change master to指定主库的IP、端口、同步开始的二进制文件和文件偏移量（MySQL 5.6以后支持GTID模式，二进制文件和文件偏移量可以用GTID号集合替换）就可以了。

备库通过IO线程连接主库，接收主库推送过来的二进制日志，并记录到本地的中继日志relaylog；同时也会启动SQL线程将中继日志的数据变更应用到备库本地数据库中.

主库接受到备库IO线程的请求，会专门对该slave启用独立的binlog dump线程，从IO线程指定的二进制文件和文件偏移量开始发送二进制日志；并且在主库有任何新的变更后，在记录到自己的二进制日志的同时也会通过网络推送给备库的IO线程。

复制线程

Master线程：

Slave线程：

主从复制优化

多线程复制：

在官方的 5.6 版本之前，MySQL 只支持单线程复制，因此在主库并发高、TPS 高时就会出现严重的主备延迟问题。从单线程复制到最新版本的多线程复制，中间的演化经历了好几个版本。但说到底，所有的多线程复制机制，都是要把之前从库中的单个 sql_thread 线程拆成多个线程，io 线程还是只有一个。即下面这个模型：

当 IO 线程将主库的 binlog 写入 relay log 后，会有一个多线程协调器（multithreaded slave coordinator）对多个 SQL 线程进行调度，让它们按照一定的规则去执行 relay log 中的事件。即图中的 coordinator 就是原来的 sql_thread，不过现在它不再直接更新数据了，而是只负责读取中转日志和分发事务。真正更新日志的变成了 worker 线程。而 work 线程的个数由参数 决定，默认为 0，即关闭多线程功能。

为了保证事务执行的先后顺序，coordinator 在分发时，需要满足以下这两个基本要求：

●不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker 中串行执行。
●同一个事务不能被拆开，必须放到同一个 worker 中。

在 MySQL 5.6 版本的并行复制中，支持的粒度是按库并行。这个策略的并行效果，取决于压力模型。如果在主库上有多个 DB，并且各个 DB 的压力均衡，使用这个策略的效果会很好。

半同步复制：

MySQL 的默认复制模式是异步模式，即 MySQL 的主服务器上的 I/O 线程，将数据写到 binlog 中就直接返回给客户端数据更新成功，不考虑数据是否传输到从服务器，以及是否写入到 relay log 中。在这种模式下，复制数据其实是有风险的，一旦数据只写到了主库的 binlog 中还没来得急同步到从库时主库宕机了，此时就会造成数据的丢失。但这种模式也是效率最高的，因为变更数据的功能都只是在主库中完成就可以了，从库复制数据不会影响到主库的写数据操作。

为了解决异步复制的数据可靠性问题，MySQL 从 5.5 版本开始允许通过以插件的形式开始支持半同步的主从复制模式。半同步复制模式是介于异步和同步之间的一种复制模式，主库在执行完客户端提交的事务后，要等待至少一个从库接收到 binlog 并将数据写入到 relay log 中才返回给客户端成功结果，可通过参数 配置至少得到 slave 的 ACK 个数，默认为 1。半同步复制模式比异步模式提高了数据的可用性，但也产生了一定的性能延迟。

半同步复制的原理是在 master 的 dump 线程去通知从库时，增加了一个 ACK 机制，也就是会确认从库是否收到事务的标志码，master 的 dump 线程不但要发送 binlog 到从库，还要负责接收 slave 的 ACK。当 slave 出现异常没有返回 ACK 时，主库将自动降级为异步复制，直到异常修复后再自动变为半同步复制。

但半同步复制模式也存在一定的数据风险，当事务在主库提交完后等待从库 ACK 的过程中，如果 master 宕机了，这个时候就会有两种情况的问题：

事务还没发送到 slave上：若事务还没发送 slave 上，客户端在收到失败结果后，会重新提交事务，因为重新提交的事务是在新的 master 上执行的，所以会执行成功，后面若是之前的 master 恢复后，会以 slave 的身份加入到集群中，此时，之前的事务就会被执行两次，第一次是之前此机器作为 master 时执行的，第二次是做为 slave 后从主库中同步过来的。

事务已经同步到 slave 上：因为事务已经同步到 slave 了，所以当客户端收到失败结果后，再次提交事务，那么此事务就会在当前 slave 机器上执行两次。

为了解决上面的隐患，MySQL 从 5.7 版本开始，增加了一种新的半同步方式。新的半同步方式的执行过程是将 "Storage Commit" 这一步移动到了 "Write Slave dump" 后面。这样保证了只有 slave 的事务 ACK 后，才提交主库事务。MySQL 5.7.2 版本新增了一个 参数用来配置半同步方式，该参数有两个值可配置：

AFTER_SYNC：参数值为AFTER_SYNC时，代表采用的是新的半同步复制方式。 AFTER_COMMIT：代表采用的是之前的旧方式的半同步复制模式。

MySQL 从 5.7.2 版本开始，默认的半同步复制方式就是 方式了，但这种复制方式也不是万能的，因为 AFTER_SYNC 方式是在事务同步到 slave 后才提交主库事务的，若是当主库等待 slave 同步成功的过程中 master 挂了，这个 master 事务提交就失败了，客户端也收到了事务执行失败的结果了，但是 slave 上已经将 binlog 的内容写到 relay log 里了，此时 slave 数据就会多了，但是多了数据一般问题不算严重，多了总比少了好。MySQL 在没办法解决分布式数据一致性问题的情况下，它只能保证的是不丢数据。

数据恢复

上面说过每一条 event 都有位点信息，如果我们当前的 MySQL 库被无操作或者误删除了，那么该如何通过 Binlog 来恢复到删除之前的数据状态呢？ 首先发现误操作之后，先停止 MySQL 服务，防止继续更新。 接着通过 mysqlbinlog命令对二进制文件进行分析，查看误操作之前的位点信息在哪里。 接下来肯定就是恢复数据，当前数据库的数据已经是错的，那么就从开始位置到误操作之前位点的数据肯定的都是正确的；如果误操作之后也有正常的数据进来，这一段时间的位点数据也要备份。 比如说： 误操作的位点开始值为 501，误操作结束的位置为705，之后到800的位点都是正确数据。 那么从 0 - 500 ，706 - 800 都是有效数据，接着我们就可以进行数据恢复了。 先将数据库备份并清空。 接着使用 mysqlbinlog 来恢复数据： 0 - 500 的数据：

mysqlbinlog --start-position=0  --stop-position=500  bin-log.000003 > /root/back.sql;

上面命令的作用就是将 0 -500 位点的数据恢复到自定义的 SQL 文件中。同理 706 - 800 的数据也是一样操作。之后我们执行这两个 SQL 文件就行了。

mysqlbinlog 命令的使用

服务器以二进制格式将binlog日志写入binlog文件，如何要以文本格式显示其内容，可以使用 mysqlbinlog 命令。

# 查看bin-log二进制文件（shell方式）
mysqlbinlog -v --base64-output=decode-rows /data/3306/binlog/mysql-bin.000005

# 查看bin-log二进制文件（带查询条件）
mysqlbinlog -v --base64-output=decode-rows /data/3306/binlog/mysql-bin.000005 \
    --start-position="5000"    \
    --stop-position="20000"    \
    --start-datetime="2021-05-01 08:01:00"  \
    --stop-datetime="2012-03-10 08:20:00"
    
# 截取日志（GTID）
cd /data/3306/binlog/
mysqlbinlog --skip-gtids --include-gtids='aeb87061-aa0a-11eb-8f23-000c2927c91a:2-629' mysql-bin.000002 mysql-bin.000005

mysqlbinlog --skip-gtids --include-gtids='aeb87061-aa0a-11eb-8f23-000c2927c91a:2-629' --exclude-gtids='aeb87061-aa0a-11eb-8f23-000c2927c91a:300-629' mysql-bin.000002 mysql-bin.000005

# 临时不记录binlog日志
set sql_log_bin=0;

#实时拉取远程主机binlog文件中的数据
mysqlbinlog  -R --host=10.0.0.52 --user=mha --password=mha --raw  --stop-never mysql-bin.000003 &

加载全部内容
相关教程
猜你喜欢
用户评论