MySQL-8.0.42 主從延遲常見原因及解決方法

1 查看從庫延時方法

2 如何分析主從延遲

3 主從延遲的常見原因及解決方法

1 查看從庫延時方法

mysql> show slave status \G
*************************** 1. row ***************************
? ? ? ? ? ? ?Slave_IO_Running: Yes ? ? #兩個yes說明主從復制正常
? ? ? ? ? ? Slave_SQL_Running: Yes
? ? ? ? Seconds_Behind_Master: 0?? ? ? #表示延時時間，單位是秒，0表示沒有延時

2 如何分析主從延遲

2.1 從庫服務器的負載情況：從CPU、內存、磁盤IO和網絡的資源使用情況入手，重點關注CPU和磁盤IO。
#查看CPU和內存使用信息
root@u24-mysql-52:~# top
top - 21:12:55 up 26 min, ?1 user, ?load average: 0.00, 0.01, 0.05
Tasks: 214 total, ? 1 running, 213 sleeping, ? 0 stopped, ? 0 zombie
%Cpu(s): ?7.1 us, ?7.1 sy, ?0.0 ni, 82.1 id, ?3.6 wa, ?0.0 hi, ?0.0 si, ?0.0 st?
MiB Mem : ? 3866.7 total, ? 2817.9 free, ? ?859.0 used, ? ?416.0 buff/cache ? ??
MiB Swap: ? 3866.0 total, ? 3866.0 free, ? ? ?0.0 used. ? 3007.7 avail Mem?

? ? PID USER ? ? ?PR ?NI ? ?VIRT ? ?RES ? ?SHR S ?%CPU ?%MEM ? ? TIME+ COMMAND ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? ?1600 root ? ? ?20 ? 0 ? 11912 ? 5660 ? 3484 R ?14.3 ? 0.1 ? 0:00.05 top ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? ? ? 1 root ? ? ?20 ? 0 ? 22104 ?13076 ? 9492 S ? 0.0 ? 0.3 ? 0:09.19 systemd ? ?

參數說明
load average: 0.00, 0.01, 0.05 ??
#1 分鐘、5 分鐘、15 分鐘的平均負載

%Cpu(s): ?7.1 us, ?7.1 sy, ?0.0 ni, 82.1 id, ?3.6 wa, ?0.0 hi, ?0.0 si, ?0.0 st
#us (user)：用戶空間進程的 CPU 占用百分比（如應用程序）。
#sy (system)：內核空間進程的 CPU 占用百分比（如系統調用）。 ?

MiB Mem : ? 3866.7 total, ? 2817.9 free, ? ?859.0 used, ? ?416.0 buff/cache
#total：總內存/交換空間大小。
#free：完全未使用的內存。
#used：已使用的內存（包括緩存和緩沖區）。
#buff/cache：內核緩存和緩沖區占用的內存（可被快速釋放供程序使用）。
#avail Mem：估算的可用內存（包含緩存和緩沖區的可回收部分）。

#查看磁盤IO
root@u24-mysql-52:~# iostat?
Linux 6.11.0-26-generic (u24-mysql-52) ?05/30/2025 ? ? ?_x86_64_ ? ? ? ?(2 CPU)

avg-cpu: ?%user ? %nice %system %iowait ?%steal ? %idle
? ? ? ? ? ?0.74 ? ?0.05 ? ?1.30 ? ?0.06 ? ?0.00 ? 97.85

Device ? ? ? ? ? ? tps ? ?kB_read/s ? ?kB_wrtn/s ? ?kB_dscd/s ? ?kB_read ? ?kB_wrtn ? ?kB_dscd
loop0 ? ? ? ? ? ? 0.00 ? ? ? ? 0.00 ? ? ? ? 0.00 ? ? ? ? 0.00 ? ? ? ? 10 ? ? ? ? ?0 ? ? ? ? ?0
sda ? ? ? ? ? ? ? 5.97 ? ? ? 189.95 ? ? ? ?57.82 ? ? ? ? 0.00 ? ? 465358 ? ? 141664 ? ? ? ? ?0
sr0 ? ? ? ? ? ? ? 0.02 ? ? ? ? 0.85 ? ? ? ? 0.00 ? ? ? ? 0.00 ? ? ? 2092 ? ? ? ? ?0 ? ? ? ? ?0

#查看網絡
#顯示網絡軟件棧統計和狀態
root@u24-mysql-52:~# netstat -alutp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address ? ? ? ? ? Foreign Address ? ? ? ? State ? ? ? PID/Program name ? ?
tcp ? ? ? ?0 ? ? ?0 _localdnsstub:domain ? ?0.0.0.0:* ? ? ? ? ? ? ? LISTEN ? ? ?646/systemd-resolve?
tcp ? ? ? ?0 ? ? ?0 _localdnsproxy:domain ? 0.0.0.0:* ? ? ? ? ? ? ? LISTEN ? ? ?646/systemd-resolve?
tcp ? ? ? ?0 ? ? ?0 u24-mysql-52:57448 ? ? ?192.168.254.51:mysql ? ?ESTABLISHED 1469/mysqld ? ? ? ??
tcp6 ? ? ? 0 ? ? ?0 [::]:33060 ? ? ? ? ? ? ?[::]:* ? ? ? ? ? ? ? ? ?LISTEN ? ? ?1469/mysqld ? ? ? ??
tcp6 ? ? ? 0 ? ? ?0 [::]:ssh ? ? ? ? ? ? ? ?[::]:* ? ? ? ? ? ? ? ? ?LISTEN ? ? ?1/init ? ? ? ? ? ? ?
tcp6 ? ? ? 0 ? ? ?0 [::]:mysql ? ? ? ? ? ? ?[::]:* ? ? ? ? ? ? ? ? ?LISTEN ? ? ?1469/mysqld ? ? ? ??
tcp6 ? ? ? 0 ? ?140 u24-mysql-52:ssh ? ? ? ?192.168.254.1:63340 ? ? ESTABLISHED 1298/sshd: root@pts?
udp ? ? ? ?0 ? ? ?0 _localdnsproxy:domain ? 0.0.0.0:* ? ? ? ? ? ? ? ? ? ? ? ? ? 646/systemd-resolve?
udp ? ? ? ?0 ? ? ?0 _localdnsstub:domain ? ?0.0.0.0:* ? ? ? ? ? ? ? ? ? ? ? ? ? 646/systemd-resolve?

2.2 主從復制狀態
#主庫狀態
mysql> show master status \G
*************************** 1. row ***************************
? ? ? ? ? ? ?File: mysql-bin.000010
? ? ? ? ?Position: 197
? ? ?Binlog_Do_DB:?
?Binlog_Ignore_DB:?
Executed_Gtid_Set: 5d7fd409-3bd6-11f0-9d66-000c29704177:1
1 row in set (0.00 sec)

#從庫狀態
mysql> show slave status \G
*************************** 1. row ***************************
? ? ? ? ? ? ? ?Slave_IO_State: Waiting for source to send event
? ? ? ? ? ? ? ? ? Master_Host: 192.168.254.51
? ? ? ? ? ? ? ? ? Master_User: repl
? ? ? ? ? ? ? ? ? Master_Port: 3306
? ? ? ? ? ? ? ? Connect_Retry: 60
? ? ? ? ? ? ? Master_Log_File: mysql-bin.000010
? ? ? ? ? Read_Master_Log_Pos: 197
? ? ? ? ? ? ? ?Relay_Log_File: u24-mysql-52-relay-bin.000005
? ? ? ? ? ? ? ? Relay_Log_Pos: 413
? ? ? ? Relay_Master_Log_File: mysql-bin.000010
? ? ? ? ? ? ?Slave_IO_Running: Yes
? ? ? ? ? ? Slave_SQL_Running: Yes
? ? ? ? ? ? ? Replicate_Do_DB:?
? ? ? ? ? Replicate_Ignore_DB:?
? ? ? ? ? ?Replicate_Do_Table:?
? ? ? ?Replicate_Ignore_Table:?
? ? ? Replicate_Wild_Do_Table:?
? Replicate_Wild_Ignore_Table:?
? ? ? ? ? ? ? ? ? ?Last_Errno: 0
? ? ? ? ? ? ? ? ? ?Last_Error:?
? ? ? ? ? ? ? ? ?Skip_Counter: 0
? ? ? ? ? Exec_Master_Log_Pos: 197
? ? ? ? ? ? ? Relay_Log_Space: 886
? ? ? ? ? ? ? Until_Condition: None
? ? ? ? ? ? ? ?Until_Log_File:?
? ? ? ? ? ? ? ? Until_Log_Pos: 0
? ? ? ? ? ?Master_SSL_Allowed: No
? ? ? ? ? ?Master_SSL_CA_File:?
? ? ? ? ? ?Master_SSL_CA_Path:?
? ? ? ? ? ? ? Master_SSL_Cert:?
? ? ? ? ? ? Master_SSL_Cipher:?
? ? ? ? ? ? ? ?Master_SSL_Key:?
? ? ? ? Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
? ? ? ? ? ? ? ? Last_IO_Errno: 0
? ? ? ? ? ? ? ? Last_IO_Error:?
? ? ? ? ? ? ? ?Last_SQL_Errno: 0
? ? ? ? ? ? ? ?Last_SQL_Error:?
? Replicate_Ignore_Server_Ids:?
? ? ? ? ? ? ?Master_Server_Id: 51
? ? ? ? ? ? ? ? ? Master_UUID: 5d7fd409-3bd6-11f0-9d66-000c29704177
? ? ? ? ? ? ?Master_Info_File: mysql.slave_master_info
? ? ? ? ? ? ? ? ? ? SQL_Delay: 0
? ? ? ? ? SQL_Remaining_Delay: NULL
? ? ? Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
? ? ? ? ? ?Master_Retry_Count: 86400
? ? ? ? ? ? ? ? ? Master_Bind:?
? ? ? Last_IO_Error_Timestamp:?
? ? ?Last_SQL_Error_Timestamp:?
? ? ? ? ? ? ? ?Master_SSL_Crl:?
? ? ? ? ? ?Master_SSL_Crlpath:?
? ? ? ? ? ?Retrieved_Gtid_Set: 5d7fd409-3bd6-11f0-9d66-000c29704177:1
? ? ? ? ? ? Executed_Gtid_Set: 5d7fd409-3bd6-11f0-9d66-000c29704177:1
? ? ? ? ? ? ? ? Auto_Position: 1
? ? ? ? ?Replicate_Rewrite_DB:?
? ? ? ? ? ? ? ? ?Channel_Name:?
? ? ? ? ? ?Master_TLS_Version:?
? ? ? ?Master_public_key_path:?
? ? ? ? Get_master_public_key: 1
? ? ? ? ? ? Network_Namespace:?
1 row in set, 1 warning (0.00 sec)

重點關注參數
Master_Log_File: mysql-bin.000010
Relay_Master_Log_File: mysql-bin.000010
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

如果主庫(File,Position)大于從庫(Master_Log_File, Read_Master_Log_Pos),說明IO線程存在延遲。
如果從庫(Relay_Master_Log_File,Exec_Master_Log_Pos) < (Master_Log_File,Read_Master_Log_Pos),說明SQL線程存在延遲。

2.3 主庫binlog的寫入量多少，如果寫入量大于從庫IO讀取量也會出現延遲。

3 主從延遲的常見原因及解決方法

3.1 IO線程存在延遲
#網絡延遲
檢查網絡問題，啟用binlog的壓縮傳輸

#磁盤IO存在瓶頸
關閉binlog

#網絡問題
在主庫使用rsync 拷貝一個文件到從庫，簡單可以判斷

3.2 SQL線程存在延遲
#主庫寫入量過大，而SQL線程單線程重放
解決方法：可以啟動并行復制,默認參數值可以，通過修改slave_parallel_workers數量來提高
slave_parallel_type=LOGICAL_CLOCK? ? ? ?
slave_parallel_workers=4? ? ? ? ? ? ? ? ? ?
slave_preserve_commit_order=ON
binlog_transaction_dependency_tracking=WRITESET_SESSION
transaction_write_set_extraction=XXHASH64
binlog_transaction_dependency_history_size=25000
binlog_format=ROW?

#大事務
指二進制日志格式為row的情況下，操作涉及的記錄數比較多，特別是update和delete操作
解決方法：建議把大事務拆分，每一次小批量執行