Mysql binlog主从延迟，relay_log更新不完。磁盘io高

最新推荐文章于 2025-07-28 21:52:55 发布
原创 最新推荐文章于 2025-07-28 21:52:55 发布 · 2.2k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库

本文探讨了MySQL中UPDATE语句执行不改变数据却记录在binlog的现象，分析了ROW、STATEMENT和MIXED模式的影响，以及如何通过调整binlog_format和innodb_flush_log_at_trx_commit参数来优化主从延迟。

Mysql binlog主从延迟，relay_log更新不完

现象①

根据binlog发现imdb.im_alarm update执行次数过多问题，但实际不更新数据，重复update sql。例如state是0仍然update state=0.也就是说执行了update，但没有更新数据，并且该sql记录在binlog中。

mysqlbinlog --base64-output=decode-rows --verbose mysql-log.001491 > temp.txt

测试MySQL中update修改数据与原数据相同会再次执行

测试环境

Centos8
Mysql8.0.23
服务器12与服务器13做主主复制

查看当前配置

M_USER=root
M_AUTH=pass
mysql -u $M_USER -p$M_AUTH -e 'show variables like "binlog_row_image";' 2>/dev/null
mysql -u $M_USER -p$M_AUTH -e 'show variables like "binlog_format";' 2>/dev/null
mysql -u $M_USER -p$M_AUTH -e 'show variables like "transaction_isolation";' 2>/dev/null

ROW模式下测试Update

binlog_formant配置

[root@manyun mysql]# mysql -u $M_USER -p$M_AUTH -e 'show variables like "binlog_row_image";' 2>/dev/null
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
[root@manyun mysql]# mysql -u $M_USER -p$M_AUTH -e 'show variables like "binlog_format";' 2>/dev/null
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
[root@manyun mysql]# mysql -u $M_USER -p$M_AUTH -e 'show variables like "transaction_isolation;"' 2>/dev/null
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

测试开始

# 先创建一个测试库
CREATE DATABASE  IF NOT EXISTS test;
use test;
# 创建这个测试表
CREATE TABLE `info` (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


INSERT INTO `test`.`info`(`id`, `name`, `gender`, `age`) VALUES (1, 'wayne', '1', 27);

# 查看master postion
## 服务器12
*************************** 1. row ***************************
             File: mysql-log.000006
         Position: 3883
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 

## 服务器13
*************************** 1. row ***************************
             File: mysql-log.000015
         Position: 1241
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 

# 执行更新
mysql> UPDATE `test`.`info` SET `name` = 'wayne', `gender` = '1', `age` = 27 WHERE `id` = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
# 查看master postion
## 服务器12
*************************** 1. row ***************************
             File: mysql-log.000006
         Position: 3883
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)
## 服务器13
*************************** 1. row ***************************
             File: mysql-log.000015
         Position: 1241
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

Statement模式下测试update

binlog_formant配置

[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show variables like "binlog_row_image";' 2>/dev/null
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show variables like "binlog_format";' 2>/dev/null
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show variables like "transaction_isolation";' 2>/dev/null
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

测试开始

# 查看master postion
## 服务器12
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show  master status\G;' 2>/dev/null
*************************** 1. row ***************************
             File: mysql-log.000007
         Position: 156
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 

## 服务器13
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show  master status\G;' 2>/dev/null
*************************** 1. row ***************************
             File: mysql-log.000016
         Position: 156
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 

# 执行更新
mysql> UPDATE `test`.`info` SET `name` = 'wayne', `gender` = '1', `age` = 27 WHERE `id` = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

# 查看master postion
## 服务器12
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-log.000007
         Position: 537
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)
## 服务器13
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show  master status\G;' 2>/dev/null
*************************** 1. row ***************************
             File: mysql-log.000016
         Position: 156
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 

# 以防万一我又做了两遍update测试，发现postion还是变化了
mysql> UPDATE `test`.`info` SET `name` = 'wayne', `gender` = '1', `age` = 27 WHERE `id` = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-log.000007
         Position: 918
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

mysql> UPDATE `test`.`info` SET `name` = 'wayne', `gender` = '1', `age` = 27 WHERE `id` = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-log.000007
         Position: 1299
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

MIXED模式下测试update

binlog_formant配置

[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show variables like "binlog_row_image";' 2>/dev/null
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show variables like "binlog_format";' 2>/dev/null
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show variables like "transaction_isolation";' 2>/dev/null
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

测试开始

# 查看master postion
## 服务器12
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show  master status\G;' 2>/dev/null
*************************** 1. row ***************************
             File: mysql-log.000008
         Position: 156
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 

## 服务器13
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show  master status\G;' 2>/dev/null
*************************** 1. row ***************************
             File: mysql-log.000017
         Position: 156
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 

# 执行更新
mysql> UPDATE `test`.`info` SET `name` = 'wayne', `gender` = '1', `age` = 27 WHERE `id` = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

# 查看master postion
## 服务器12
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-log.000008
         Position: 529
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

## 服务器13
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show  master status\G;' 2>/dev/null
*************************** 1. row ***************************
             File: mysql-log.000017
         Position: 156
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 

# 以防万一我又做了三遍update测试，发现postion还是变化了
mysql> UPDATE `test`.`info` SET `name` = 'wayne', `gender` = '1', `age` = 27 WHERE `id` = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-log.000008
         Position: 902
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> UPDATE `test`.`info` SET `name` = 'wayne', `gender` = '1', `age` = 27 WHERE `id` = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-log.000008
         Position: 1275
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

mysql> UPDATE `test`.`info` SET `name` = 'wayne', `gender` = '1', `age` = 27 WHERE `id` = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-log.000008
         Position: 1648
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

解决问题

binlog_format说明

mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

① STATEMENT模式（SBR）

每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

②ROW模式（RBR）

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

③ MIXED模式（MBR）

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

业务逻辑

根据业务逻辑，不需要将update不变动的sql保留。根据binlog_format，statement是适合我们的。修改后。重复update不在记录。

add.binlog_format=row

现象②

即使修改了binlog_format后，主从仍然延迟。

解决问题

优化bin_log flush到磁盘的过程。

## 默认 0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作
## 2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作
innodb_flush_log_at_trx_commit=2
## 当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
sync_binlog=1000

优化参数解析

Mysql8.0优化参数项

MySQL Binlog监听服务延迟问题排查
停止更新
12-01 3983
问题现象线上部署了一个binlog监听服务，将自己伪装成从库，通过网络监听主库发来的row based binlog事件来进行一些业务上的处理。此前一直监听的是mysql A集群，一切正常；现在切换成B后，发现监听进度总是会落后，且平均每天落后1小时(当前发生的事件要到下一小时才能监听到)。通过观察日志，发现binlog落后时的现象为日志按固定时间间隔规律打印, 如总是每间200ms打印一次收到事件
Liunx 搭建MySQL集群（三）
tom的博客
03-06 904
本文讲解的是主备互备的过程。 上面已经讲过主备的搭建： Liunx 搭建MySQL集群（一） Liunx 搭建MySQL集群（二） 配置参数： #主备都需要新增 log-slave-updates = true #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启 #主自增长ID auto_increment_offset = 1 auto_increment_increment = 2 #备自增加ID auto_increment_offset = 2 auto_incr
mysql一主多从主库宕机切换步骤
康慧欣的博客
12-12 3797
1、首先停业务 2、所有从库都先停掉IO线程：stop slave io_thread; 再查看SQL线程的执行状态： show slave status\G; 查看Slave_SQL_Running_State项是否为Slave has read all relay log; waiting for more updates，如下： Slave_SQL_Running_State: Slave ...
mysql从库relay_log一直不变_mysql主从Relay log read failure解决
weixin_36486007的博客
02-27 1090
我的树莓派作为博客的mysql slave，一直在备份数据。前几日折腾树莓派，频繁重启树莓派，可能导致relay log损坏了，show slave status显式主从同步错误：Last_Error:Relay log read failure:Could not parse relay log event entry. The possible reasons are: the master’...
mysql复制的一例错误：Relay_Log_Pos不变
热门推荐
始终不够
01-02 1万+
最近公司的一台Mysql从机延迟了18W秒，两天前发现延迟时，观察SLAVE STATUS，一切正常，以为近期业务调整造成写操作过多，从机延迟的比较厉害，就没有做过多注意。元旦放假回来后发下延迟时间竟然已经有18W。
MySQL四种日志binlog/redolog/relaylog/undolog
程序猿进阶
08-08 1923
因为功能的不同，redo log 存储的是物理数据的变更，如果我们内存的数据已经刷到了磁盘了，那redo log的数据就无效了。这也应该容易理解，毕竟回滚嘛，跟需要修改的操作相反就好，这样就能达到回滚的目的。的存在为了：当我们修改的时候，写完内存了，但数据还没真正写到磁盘的时候。时，并不是马上就刷入中继日志里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘。中继日志里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量。我们还是会对这次的事务回滚，将无效的。
MySQL5.7主从延迟高排查优化思路
最新发布
菜鸟运维升级之路
07-28 1015
MySQL主从同步高延迟问题排查与解决 问题描述：基于GTID的MySQL 5.7.32主从环境，出现从库同步延迟高达4784150秒，执行50-100万数据的DELETE语句耗时异常。 排查思路： 检查从库状态和事务，发现存在长时间运行的DELETE大事务 服务器资源监控显示CPU使用正常但磁盘IO异常 使用fio工具测试SSD磁盘性能，发现： 磁盘利用率达99.91%，接近满负荷 读取IOPS 6810，写入IOPS 2665 读取吞吐112MB/s，写入47.9MB/s 解决方案： 优化大事务处理 检
MySQL主从延迟高，Reading event from the relay log
qq_43164571的博客
06-21 2577
问题现象： 收到邮件报警，mysql主从延迟>1h，导致新的数据无法进行同步。 Seconds_Behind_Master: 越来越大。 Slave_SQL_Running_State: 一直处于Reading event from the relay log 状态 mysql> show slave status\G *************************** 1. row *************************** Slave_I
MySQL主从延迟解决方案
7small7
03-16 1351
在讨论如何解决主从延迟之前，我们先了解下什么是主从延迟。为了完成主从复制，从库需要通过 I/O 线程获取主库中 dump 线程读取的 binlog 内容并写入到自己的中继日志 relay log 中，从库的 SQL 线程再读取中继日志，重做中继日志中的日志，相当于再执行一遍 SQL，更新自己的数据库，以达到数据的一致性。与数据同步有关的时间点主要包括以下三个：1、主库执行完一个事务，写入 binlog，将这个时刻记为 T1；2、之后传给从库，将从库接收完这个 binlog 的时刻记为 T2；
高效解决MySQL主从复制延迟问题
百态老人的博客
05-20 1071
主从复制延迟过高是数据库运维中的常见问题，主要原因包括硬件配置不足、架构设计缺陷、SQL与事务问题以及主库压力过大。通过大数据技术，可以量化分析延迟问题，监控关键指标如Seconds_Behind_Master、Binlog生成速率、硬件资源利用率等，并结合日志分析和根因定位算法，快速识别问题根源。解决方案涵盖架构优化（如启用多线程复制）、SQL优化（如拆分大事务、为无主键表添加主键）、硬件升级和网络优化。实时监控脚本可帮助及时发现并处理延迟问题。综合运用这些策略，可以有效减少主从复制延迟，提升数据库性能。
mysql 错误日志 占用硬盘_MySQL慢日志占满磁盘空间导致业务主库宕机解决
weixin_29081703的博客
01-19 517
在MySQL的日常维护中，我们总会遇到这样或那样的问题，对于那些经常发生且有处理经验的事故，不论是新手还是老司机都能在故障规定的容错时间内解决。而对于那些不常见、比较棘手的问题，新手上路可能就显得举足无措了，这个时候新手和老司机的差距就体现出来了。从知识储备还是工作经验，可能老司机比新手强一点，但如果一个新司机没有日志排错的意识，不具备日志排错的经验，那怎么能学会弯道超车、漂移的快感。我们知道数据...
MySQL主从同步的一个小问题解决
weixin_34113237的博客
01-15 238
由于历史遗留问题，我们的MySQL主从库的表结构不一致，主库的某个表tableA比从库表tableA少了一个字段。 当尝试在主库上更改表结构时，这行alter语句会随着binlog同步到从库，如果从库执行这行语句时出错，主从同步线程就会自动停止，那样只能人为手动处理错误，然后再启动slave上的主从同步线程。场景大概是下面这个样子： 1，在主库上执行alter table aaa add co...
DNS服务的部署
qq_41786285的博客
09-08 624
一、DNS分层结构 . 根域 国家域：cn、uk、hk 顶级域：com、edu、net 二级域：个性化名称 主机名（用途）：www（网页）、mail（邮件） 二、DNS解析过程 以www.baidu.com为例 一般客户机将解析的请求发送给它的DNS服务器，DNS服务器首先是从根DNS服务器(.)开始发送域名解析请求，根将COM域的IP反馈给客户机的本地DNS服务器，本地DNS服务器访问COM域服务器，COM域服务器反馈baidu IP给本地DNS服务器，本地DNS服务器访问baidu域服务器询问www域服
mysql主从binlog日志损坏_mysql主从复制问题之主从两端binlog日志不同步解决方案...
weixin_29514913的博客
01-19 499
主操作：进入主的数据库查看状态:mysql> show master statusG;*************************** 1. row ***************************File: mysql-bin.000001Position: 604Binlog_Do_DB:Binlog_Ignore_DB:1 row in set (0.01 sec)从操作：...
Mysql主备备同步位点不更新问题解决
huangzx3的博客
02-19 537
主备备同步架构图 现象 在主数据库上更新数据后，备1同步更新数据正常，但是备2数据一直不更新 原因 备1的binlog日志没有更新，导致备2一直未更新 解决方案 备1的mysql配置文件新增一个配置项：log-slave-updates，重启备1，并重做备1和备2之间的主备同步。 查看mysql官网对于此参数的解释： --log-slave-updates All transactions received by the replica (even from multiple sour
mysql中的binlog:
qq_59369367的博客
02-17 1669
概述 MySQL中的binlog是记录所有数据库表结构变更(例如 CREATE、ALTER TABLE) 以及表数据的修改(INSERT、UPDATE、DELETE)的二进制文件,,binlog 不会记录查询相关的操作,因为这类操作对于表的结构和数据本省没有进行修改.这些查询操作是可以通过查看系统通用日志来查看相关语句. binlog的主要目的是复制和恢复，且是事务安全型的。 binlog以事件形式记录.记录的操作都有对应的事件,statement格式中的DML操作对应的是query_event ;
mysql5.6解决binlog为row格式时增长量过大问题
隔壁wangshifu的博客
11-10 3701
binlog_format为row格式的时候记录的不是简单的sql，而是实际变更的行，一些大的DDL操作，会导致binlog量增加很大，消耗额外的IO、网络资源 可以通过设置binlog_row_image=minimal解决 测试： binlog_row_image默认值是full 对user表进行update 进入binlog里面查看更
mysql主从 效率_MySQL 主从延迟几万秒 Queueing master event to the relay log（转）
weixin_26938645的博客
02-18 336
数据库版本Server version: 5.6.24-log Source distribution问题描述数据采集平台业务数据库由于批量灌数据导致主从延迟上万秒。复制线程长期处于Queueing master event to the relay log状态。监控数据显示1.Seconds_Behind_Master 维持在6w秒左右，且有上升趋势。2.主库有大量的binlog积压无法同...
mysql sync relay log_MYSQL sync_relay_log对I/O thread的影响分析
weixin_42512183的博客
01-19 177
搭建好的一套从库，发现延迟很高，一直追不上，从库的bin_log没开，flush_log_at_trx_commit设置为0，简化的状态如下：mysql> show slave status \G*************************** 1. row ***************************Slave_IO_State: Queueing master event...
mysql从数据库relay-log配置
06-10
MySQL 的 relay-log 是主从复制过程中从库（Slave）用于存储主库（Master）二进制日志（binlog）的临时文件。这些日志文件在从库中起到关键作用，包括数据同步和崩溃恢复等。以下是关于 MySQL relay-log 的配置方法...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

大锅霍皮久

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值