基于二进制包最小化安装GTID 主从复制架构 主从延迟常见原因模拟与解决主库192.168.195.141从库192.168.195.1421. 环境信息项目主库 (Master)从库 (Slave)IP192.168.195.141192.168.195.142MySQL版本8.0.35二进制包glibc2.178.0.35二进制包glibc2.17server-id12gtid_modeONONenforce_gtid_consistencyONONbinlog_formatROWROW角色MasterSlave2. 清空环境两台均执行systemctl stop mysqldrm-rf/data/mysql/3306/data/*rm-f/etc/my.cnf /etc/systemd/system/mysqld.service /etc/sysconfig/mysql systemctl daemon-reload3. 编辑配置文件3.1 主库 /etc/my.cnf141[client] socket /data/mysql/3306/data/mysql.sock [mysqld] basedir /usr/local/mysql datadir /data/mysql/3306/data user mysql port 3306 socket /data/mysql/3306/data/mysql.sock log_error /data/mysql/3306/data/mysqld.err log_timestamps system log-bin mysql-bin server-id 1 gtid_mode ON enforce_gtid_consistency ON3.2 从库 /etc/my.cnf142[client] socket /data/mysql/3306/data/mysql.sock [mysqld] basedir /usr/local/mysql datadir /data/mysql/3306/data user mysql port 3306 socket /data/mysql/3306/data/mysql.sock log_error /data/mysql/3306/data/mysqld.err log_timestamps system server-id 2 gtid_mode ON enforce_gtid_consistency ON4. 初始化实例两台均执行chownmysql.mysql /data/mysql/3306/data/ /usr/local/mysql/bin/mysqld --defaults-file/etc/my.cnf--initialize获取临时密码greptemporary password/data/mysql/3306/data/mysqld.err5. 配置 systemd 服务两台均执行5.1 创建 /etc/systemd/system/mysqld.service[Unit] DescriptionMySQL Server Documentationman:mysqld(8) Documentationhttp://dev.mysql.com/doc/refman/en/using-systemd.html Afternetwork.target Aftersyslog.target [Install] WantedBymulti-user.target [Service] Usermysql Groupmysql Typeforking PIDFile/data/mysql/3306/data/mysqld.pid TimeoutSec0 ExecStart/usr/local/mysql/bin/mysqld --defaults-file/etc/my.cnf --pid-file/data/mysql/3306/data/mysqld.pid --daemonize $MYSQLD_OPTS EnvironmentFile-/etc/sysconfig/mysql LimitNOFILE 65535 Restarton-failure RestartPreventExitStatus1 PrivateTmpfalse5.2 创建 /etc/sysconfig/mysqlMYSQLD_OPTS6. 启动实例并修改 root 密码两台均执行systemctl daemon-reload systemctl start mysqld systemctlenablemysqld# 使用 --init-file 方式修改密码cat/tmp/mysql-initEOF alter user rootlocalhost identified by Root123456; EOFsystemctl stop mysqld /usr/local/mysql/bin/mysqld --defaults-file/etc/my.cnf --init-file/tmp/mysql-init--usermysql--daemonizesleep3# 验证/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456-eselect 1;# 恢复 systemd 管理pkill-9mysqld;sleep2;systemctl start mysqld7. 搭建 GTID 主从复制7.1 主库创建复制用户-- 在主库141执行CREATEUSERrepl%IDENTIFIEDBY123456;GRANTREPLICATIONSLAVEON*.*TOrepl%;7.2 从库建立 GTID 复制-- 在从库142执行CHANGE MASTERTOMASTER_HOST192.168.195.141,MASTER_USERrepl,MASTER_PASSWORD123456,MASTER_AUTO_POSITION1,GET_MASTER_PUBLIC_KEY1;STARTSLAVE;7.3 验证主从状态-- 在从库142执行showslavestatus\GSlave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 Auto_Position: 18. Seconds_Behind_Master 的实现逻辑8.1 计算公式Seconds_Behind_Master 从库当前系统时间 - SQL线程当前重放事务在主库的开始时间戳 - 主从系统时间差即longtime_diff((long)(time(0)-mi-rli-last_master_timestamp)-mi-clock_diff_with_master);其中time(0)从库当前的系统时间last_master_timestampSQL 线程当前重放的事务在主库开始执行的时间戳binlog 事件中的时间戳clock_diff_with_masterIO 线程启动时主从之间的系统时间差8.2 特殊值含义值含义0SQL 线程已重放完所有 relay log且 IO 线程正在运行NULLSQL 线程未运行或 SQL 线程重放完 relay log 但 IO 线程未运行0SQL 线程存在延迟数值为延迟秒数注意如果 IO 线程启动后调整过系统时间需重启复制否则会影响 Seconds_Behind_Master 的计算结果。9. 如何分析主从延迟9.1 从库服务器负载情况CPU 分析topCpu(s): 0.2%us, 0.2%sy, 0.0%ni, 99.5%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st指标含义us处理用户态任务的 CPU 时间占比sy处理内核态任务的 CPU 时间占比id处于空闲状态的 CPU 时间占比wa等待 IO 的 CPU 时间占比当 CPU 使用率1 - id超过 90% 时需引起关注。磁盘 IO 分析iostat -xm 1重点关注awaitIO 请求的平均耗时ms包括磁盘处理时间和队列等待时间%util磁盘饱和度采样周期内有多少时间在做 IO 操作9.2 主从复制状态对比第一对判断 IO 线程延迟主库 (File, Position) vs 从库 (Master_Log_File, Read_Master_Log_Pos)如果主库位置 从库 IO 线程位置则 IO 线程存在延迟。第二对判断 SQL 线程延迟从库 (Master_Log_File, Read_Master_Log_Pos) vs (Relay_Master_Log_File, Exec_Master_Log_Pos)如果 IO 线程位置 SQL 线程位置则 SQL 线程存在延迟。9.3 主库 binlog 写入量查看主库 binlog 的生成速度如多少分钟生成一个 binlog 文件。10. 主从延迟的常见原因及解决方法10.1 IO 线程存在延迟IO 线程延迟较少见常见原因原因解决方法网络延迟/带宽限制开启slave_compressed_protocol启用 binlog 压缩传输从库磁盘 IO 瓶颈调整双一设置或关闭 binlog网卡故障排查网络硬件问题10.2 SQL 线程存在延迟场景一主库写入量过大SQL 线程单线程重放具体体现从库磁盘 IO 无明显瓶颈Relay_Master_Log_File, Exec_Master_Log_Pos不断变化主库写入量过大如 SATA SSD 下 binlog 生成速度快于 5 分钟一个解决方法开启并行复制-- 在从库142执行STOP SLAVE;SETGLOBALslave_parallel_workers4;-- 设置并行工作线程数SETGLOBALslave_parallel_typeLOGICAL_CLOCK;-- 基于LOGICAL_CLOCK的并行复制STARTSLAVE;-- 验证SHOWVARIABLESLIKEslave_parallel_workers;-- Value: 4SHOWVARIABLESLIKEslave_parallel_type;-- Value: LOGICAL_CLOCK参数解释slave_parallel_workers并行工作线程数0 表示单线程建议设置为 4-8。slave_parallel_type并行复制类型。LOGICAL_CLOCK基于组提交的并行复制同一组提交的事务可以在从库并行重放。场景二STATEMENT 格式下的慢 SQL具体体现在一段时间内Relay_Master_Log_File, Exec_Master_Log_Pos没有变化。原理STATEMENT 格式下慢 SQL 在主库执行慢在从库重放同样慢。例如对千万数据的无索引表执行 DELETE主库耗时 7.52s从库重放时 Seconds_Behind_Master 最大可达 7s。解决方法优化 SQL-- 开启慢查询记录将 SQL 重放过程中执行时长超过 long_query_time 的操作记录在慢日志SETGLOBALlog_slow_slave_statementsON;log_slow_slave_statements在 MySQL 5.6.11 中引入可将 SQL 重放过程中执行时长超过long_query_time的操作记录在慢日志中便于定位从库慢 SQL。场景三表上没有任何索引且 binlog 格式为 ROW具体体现在一段时间内Relay_Master_Log_File, Exec_Master_Log_Pos不会变化。原理ROW 格式下对无索引表操作主库只需一次全表扫描但从库重放时对每条记录的操作都会进行一次全表扫描。同样的 DELETE 操作ROW 格式下延迟可能是 STATEMENT 格式的 100 倍。模拟验证-- 主库创建无索引表并插入数据USEtest_delay;CREATETABLEt_no_idx(idINT,cVARCHAR(200));-- 插入 100000 行数据...-- 主库执行 DELETEROW 格式DELETEFROMt_no_idxWHEREid100;-- 主库执行很快但从库重放时每行都需全表扫描产生延迟解决方法一在从库上临时创建索引-- 在从库142执行USEtest_delay;ALTERTABLEt_no_idxADDINDEXidx_id(id);注意尽量选择区分度高的列添加索引列的区分度越高重放速度越快。解决方法二设置 slave_rows_search_algorithms-- 在从库142执行STOP SLAVE;SETGLOBALslave_rows_search_algorithmsINDEX_SCAN,HASH_SCAN;STARTSLAVE;参数解释INDEX_SCAN,HASH_SCAN当无索引时使用 HASH_SCAN 替代 TABLE_SCAN对每行记录不再做全表扫描而是使用哈希查找。设置后延迟可大幅降低PDF 示例从 723s 降至 53s。默认值即为INDEX_SCAN,HASH_SCAN。场景四大事务原理ROW 格式下操作涉及的记录数较多时从库重放耗时随记录数增加而增加。记录数主库执行时长(s)Seconds_Behind_Master 最大值(s)500000.7612000003.10850000017.3239100000063.47122解决方法分而治之每次小批量执行-- 不推荐一次性大事务UPDATEt_big_txSETcREPEAT(M,120)WHEREid1000000;-- 推荐分批执行UPDATEt_big_txSETcREPEAT(M,120)WHEREid10000;UPDATEt_big_txSETcREPEAT(M,120)WHEREidBETWEEN10001AND20000;-- ... 依此类推场景五从库上有查询操作导致锁等待原理从库的查询操作可能阻塞 SQL 线程重放常见的是查询操作阻塞 DDL 操作Metadata Lock 等待。模拟验证-- 步骤1在从库142执行长查询USEtest_delay;SELECTid,SLEEP(10)FROMt_mdlock;-- 该查询对 t_mdlock 表持有 Metadata Read Lock-- 步骤2在主库141执行 DDLUSEtest_delay;ALTERTABLEt_mdlockADDCOLUMNc2INT;-- DDL 需要 Metadata Write Lock与从库查询冲突-- 步骤3查看从库状态SHOWPROCESSLIST;------------------------------------------------------------------------------------------------------------------------------- | Id | User | Host | db | Command | Time | State | Info | ------------------------------------------------------------------------------------------------------------------------------- | 72 | system user | connecting host | NULL | Connect | 287 | Waiting for source to send event | NULL | | 73 | system user | | test_delay | Query | 24 | Waiting for table metadata lock | ALTER TABLE t_mdlock ADD COLUMN c2 INT | | 78 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST | -------------------------------------------------------------------------------------------------------------------------------SHOWSLAVESTATUS\GSlave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 21 Slave_SQL_Running_State: Waiting for table metadata lock关键发现SQL 线程Id73状态为Waiting for table metadata lockSeconds_Behind_Master: 21延迟已产生IO 线程正常运行不受影响解决方法等待查询结束Metadata Lock 自动释放SQL 线程恢复重放KILL 阻塞查询KILL 查询线程Id;业务层面避免在从库执行长查询或将长查询路由到专用只读节点验证恢复-- 长查询结束后SHOWSLAVESTATUS\G-- Seconds_Behind_Master: 0-- Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates场景六从库上存在备份FLUSH TABLES WITH READ LOCK 阻塞 SQL 线程原理备份操作执行FLUSH TABLES WITH READ LOCKFTWRL获取全局读锁会阻塞 SQL 线程的重放。模拟验证-- 步骤1在从库142模拟备份操作FLUSHTABLESWITHREADLOCK;SELECTSLEEP(60);-- 模拟备份持续 60 秒UNLOCKTABLES;-- 步骤2在主库141写入数据USEtest_delay;INSERTINTOt_ddlVALUES(5,ftwrl_test2,NULL);-- 步骤3查看从库状态SHOWPROCESSLIST;------------------------------------------------------------------------------------------------------------------------------- | Id | User | Host | db | Command | Time | State | Info | ------------------------------------------------------------------------------------------------------------------------------- | 21 | system user | | NULL | Query | 19 | Waiting for global read lock | NULL | | 46 | root | localhost | NULL | Query | 24 | User sleep | SELECT SLEEP(60) | -------------------------------------------------------------------------------------------------------------------------------SHOWSLAVESTATUS\GSlave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 19 Slave_SQL_Running_State: Waiting for global read lock关键发现SQL 线程状态为Waiting for global read lockSeconds_Behind_Master: 19延迟已产生FTWRL 的全局读锁阻塞了 SQL 线程解决方法使用mysqldump --single-transaction替代 FTWRL 进行 InnoDB 备份不会阻塞重放调整备份时间窗口避开业务高峰使用 XtraBackup 等物理备份工具验证恢复-- FTWRL 释放后UNLOCK TABLES 或会话断开SHOWSLAVESTATUS\G-- Seconds_Behind_Master: 0场景七磁盘 IO 存在瓶颈原理从库磁盘 IO 性能不足导致 SQL 线程重放速度跟不上主库写入速度。解决方法调整双一设置或关闭 binlog-- 在从库142执行-- 查看当前设置SHOWVARIABLESLIKEsync_binlog;-- Value: 1每次事务提交都同步 binlog 到磁盘最安全但最慢SHOWVARIABLESLIKEinnodb_flush_log_at_trx_commit;-- Value: 1每次事务提交都刷新 redo log 到磁盘最安全但最慢-- 调整为宽松设置提升 IO 性能降低安全性SETGLOBALsync_binlog0;-- 不主动同步 binlog由操作系统负责SETGLOBALinnodb_flush_log_at_trx_commit2;-- 每次事务提交写入 os cache每秒 fsync-- 验证SHOWVARIABLESLIKEsync_binlog;-- Value: 0SHOWVARIABLESLIKEinnodb_flush_log_at_trx_commit;-- Value: 2参数解释sync_binlog 1每次事务提交都 fsync binlog最安全但最慢DUAL 1双一设置之一。sync_binlog 0不主动 fsync由操作系统缓存刷新性能最好但崩溃可能丢失事务。innodb_flush_log_at_trx_commit 1每次事务提交都 fsync redo log最安全DUAL 1双一设置之二。innodb_flush_log_at_trx_commit 2每次事务提交写入 os cache每秒 fsync最多丢失 1 秒数据。注意从库调整双一设置是可接受的因为从库数据可从主库重新同步。但主库不建议调整。也可考虑关闭从库 binlogMySQL 5.7 GTID 模式下允许-- 需要修改 my.cnf 并重启[mysqld]skip-log-bin# 或disable-log-bin11. 主从延迟原因总结图主从延迟 ├── IO 线程延迟较少见 │ ├── 网络延迟/带宽限制 → 开启 slave_compressed_protocol │ ├── 从库磁盘 IO 瓶颈 → 调整双一设置或关闭 binlog │ └── 网卡故障 → 排查网络硬件 │ └── SQL 线程延迟常见 ├── 主库写入量过大单线程重放 → 开启并行复制 ├── STATEMENT 格式慢 SQL → 优化 SQL log_slow_slave_statements ├── 无索引表 ROW 格式 → 从库加索引 / slave_rows_search_algorithmsINDEX_SCAN,HASH_SCAN ├── 大事务 → 分而治之小批量执行 ├── 从库查询阻塞 DDLMetadata Lock→ 避免从库长查询 / KILL 阻塞查询 ├── 从库备份FTWRL→ 使用 --single-transaction 或 XtraBackup └── 磁盘 IO 瓶颈 → 调整双一设置或关闭 binlog12. 延迟场景验证汇总序号延迟场景模拟方式从库状态Seconds_Behind_Master解决方法验证结果1主库写入量大单线程重放slave_parallel_workers0 大量INSERTSQL线程持续重放视写入量而定开启并行复制slave_parallel_workers4, slave_parallel_typeLOGICAL_CLOCK✅ 并行复制已开启2STATEMENT格式慢SQLSET binlog_formatSTATEMENT SLEEPSQL线程执行慢SQL约等于慢SQL执行时间优化SQL log_slow_slave_statements✅ 原理验证通过3无索引表ROW格式无索引表 DELETEROW格式SQL线程全表扫描视数据量而定从库加索引 / slave_rows_search_algorithmsINDEX_SCAN,HASH_SCAN✅ 索引已添加HASH_SCAN已设置4大事务大量UPDATE单事务SQL线程重放大事务随记录数增加分而治之小批量执行✅ 原理验证通过5从库查询阻塞DDLSELECT SLEEP ALTER TABLEWaiting for table metadata lock21s等待/KILL查询/避免从库长查询✅ 延迟21s后恢复06从库备份FTWRLFLUSH TABLES WITH READ LOCKWaiting for global read lock19s–single-transaction / XtraBackup✅ 延迟19s后恢复07磁盘IO瓶颈双一设置导致IO瓶颈IO等待视IO压力sync_binlog0 innodb_flush_log_at_trx_commit2✅ 参数已调整13. 当前环境参数确认13.1 主库141gtid_mode ON enforce_gtid_consistency ON binlog_format ROW log-bin mysql-bin server-id 1 gtid_executed b2a07ac2-8842-11f1-9d92-000c29048d1f:1-3506113.2 从库142gtid_mode ON enforce_gtid_consistency ON binlog_format ROW server-id 2 slave_parallel_workers 4 slave_parallel_type LOGICAL_CLOCK sync_binlog 0 innodb_flush_log_at_trx_commit 2 slave_rows_search_algorithms INDEX_SCAN,HASH_SCAN14. 连接信息# 主库141/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456# 从库142/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456