MySQL之 從復制延遲問題排查

一、從庫復制延遲問題

1、可能的原因如下
（1）主從服務器處于不同的網絡之中，由于網絡延遲導致；
（2）主從服務器的硬件配置不同，從服務器的硬件配置（包括內存，CPU，網卡等）遠低于主服務器；
（3）主庫上有大量的寫入操作，導致從庫無法實時重放主庫上的binlog；
（4）主庫上存在著大事務操作或者慢SQL，導致從庫在應用主庫binlog的過程過慢，形成延遲；
（5）數據庫實例的參數配置問題導致，如：從庫開啟了binlog，或者配置了每次事務都去做刷盤操作；

2、主從同步延遲問題判斷
（1）根據從庫上的狀態參數判斷

mysql-server-3307> SHOW SLAVE STATUS \G

在輸出結果中找到Seconds_Behind_Master參數，這個參數表示的是從庫上的IO線程和SQL線程相差的時間，然后根據該參數值判斷，這個值只是初步判斷，不能由這個值來下結論，有如下幾種情況：
a、0：表示無延遲，理想狀態；
b、NULL：表示從庫上的IO線程和SQL線程中，有某一個線程出現問題，可以再次查看Slave_IO_Running和Slave_SQL_Running的值是否都為Yes；
c、大于0：表示主從已經出現延遲，這個值越大，表示從庫和主庫之間的延遲越嚴重；
d、小于0：這個值在官方文檔中沒有說明，通常不會出現。如果出現，那恭喜你中獎了，撞見MySQL的bug了；

（2）根據主從庫上面當前應用的二進制日志文件名稱或者重放日志的位置來判斷
① 同時打開兩個MySQL的命令行窗口，分別打開主庫和從庫，在第一個窗口上執行查看主庫當前狀態的命令

mysql-server-3306> SHOW MASTER STATUS \G
*************************** 1. row ***************************File: mysql-bin.000017Position: 120Binlog_Do_DB: Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

② 在第二個從庫的命令行窗口執行如下命令

mysql-server-3307> SHOW SLAVE STATUS \G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send event...Connect_Retry: 60Master_Log_File: mysql-bin.000017Read_Master_Log_Pos: 120Relay_Log_File: relay-log.000016Relay_Log_Pos: 283Relay_Master_Log_File: mysql-bin.000017Slave_IO_Running: YesSlave_SQL_Running: Yes...Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 120Relay_Log_Space: 613Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0...Seconds_Behind_Master: 0...Replicate_Ignore_Server_Ids: Master_Server_Id: 3Master_UUID: 2dbbf79b-5d9f-11e8-8004-000c29e28409Master_Info_File: /mysql_data/3307/data/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULL

③ 比較從庫上的Master_Log_File和Relay_Master_Log_File文件之間是否有差異
a、如果有差異，則說明主從延遲很嚴重；
b、如果沒有差異，則比較Read_Master_Log_Pos和Exec_Master_Log_Pos的差異，這倆參數分別表示從庫當前讀取到的主庫的二進制日志文件位置點和已經執行到的位置點；
c、如果上述輸出都沒有差異，可以通過主庫上"show master status"和從庫上"show slave status"的結果作比較。主要比較主庫的"File"和從庫的"Master_Log_File"，主庫上的"Position"和從庫上的"Read_Master_Log_Pos"；

3、主從延遲解決辦法
（1）判斷是否由于網絡導致
方法：測試主從庫之間的網絡延遲，比如測試ping延遲。同時可以檢查主從同步的時候是否使用了主庫的域名來同步，而域名解析速度可能會特別慢。或者使用其他測試工具；
（2）判斷是否由于硬件環境導致
方法：確認主從庫的硬件配置是否相差較大，如果配置參數相差較大，可以排查從庫上的CPU，內存，IO使用率來判斷是否因為硬件配置導致；
（3）判斷是否在主庫上有大量的DML操作
方法：可以再主庫上通過"show full processlist"命令查看當前正在執行的sql，查看是否有大量正在執行的SQL，或者觀察主庫的CPU和內存使用率，判斷是否有高并發操作；
（4）判斷是否有慢SQl，可以再主庫上臨時打開慢SQL記錄，臨時打開方法如下

#開啟慢SQL功能并查看是否生效
mysql-server-3306> SET @@GLOBAL.slow_query_log = ON;
mysql-server-3306> SHOW VARIABLES LIKE 'slow_query_log';
#設置慢SQL的時間并查看是否生效，單位為s，表示大于多少秒的SQL會被記錄
mysql-server-3306> SET @@GLOBAL.long_query_time = 5;
mysql-server-3306> SHOW VARIABLES LIKE 'long_query_time';
#設置慢SQL記錄日志路徑并查看是否生效。注意，這個目錄必須對MySQL用戶有讀寫權限
mysql-server-3306> SET @@GLOBAL.slow_query_log_file = '/mysql_data/mysql-slow.log';
mysql-server-3306> SHOW VARIABLES LIKE 'slow_query_log_file';

（5）檢查從服務器參數配置是否合理
① 查看從庫是否開啟了binlog日志，從庫上執行如下命令查看

mysql-server-3307> SHOW VARIABLES LIKE?'log_bin';

如果開啟了binlog日志，而且從庫未充當其他庫的主庫時，可以將從庫上的binlog關閉，否則會增加從庫負擔，每次重放完成主庫的binlog還要記錄到自身的binlog

② 查看從庫上的sync_binlog參數的值，這個參數表示的是事務提交多少次之后，由MySQL來將binlog_cache中的數據刷新到磁盤，有以下幾種值：
0：表示事務提交之后，MySQL不做刷新binlog_cache到磁盤的操作，而是由操作系統來定時自動完成刷盤操作，這種操作對性能損耗最少，但是也最不安全；
n：表示提交n次事務之后，由MySQL將binlog_cache中的數據刷新到磁盤，如果開啟，會對性能有一定程度的損耗。所以，從庫上如果延遲很嚴重，可以考慮將該參數的值設為0；

mysql-server-3307> SET @@GLOBAL.sync_binlog = 0;
mysql-server-3307> SHOW VARIABLES LIKE 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 0     |
+---------------+-------+
1 row in set (0.00 sec)

③ 如果從庫中要同步的數據庫使用的是InnoDB存儲引擎，可以查看innodb_flush_log_at_trx_commit參數。這個參數表示事務執行完成之后，多久的頻率刷新一次日志到磁盤上，可用的值有如下幾種：
0：表示MySQL會將日志緩沖區中的數據每秒一次地寫入日志文件中，并且日志文件的刷盤操作同時進行。該模式下在事務提交的時候，不會主動觸發寫入磁盤的操作，效率最搞，但是安全性也比較低，可能會丟失數據；
1：每一次事務提交都需要把日志寫入磁盤，這個過程是特別耗時的操作；
2：每一次事務提交之后，不會自動觸發日志刷盤的操作，而是由操作系統來決定什么時候來做刷新日志的操作，在操作系統掛了的情況下才會丟失數據；
如果在主從延遲非常嚴重的情況下，可以將從庫的該參數設置為0，以提高從庫上重放主庫二進制日志的效率。

mysql-server-3307> SET @@GLOBAL.innodb_flush_log_at_trx_commit = 0;
mysql-server-3307> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 0     |
+--------------------------------+-------+
1 row in set (0.00 sec)

注意：上述設計到修改MySQL數據庫實例的操作中，修改之后會立刻生效，但是重啟實例之后，會失效，如果要永久修改，則需要編輯mysql配置文件，然后重啟。