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.000017
         Position: 120
     Binlog_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: 60
              Master_Log_File: mysql-bin.000017
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay-log.000016
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000017
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ...
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 613
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
                ...
        Seconds_Behind_Master: 0
        ...
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 3
                  Master_UUID: 2dbbf79b-5d9f-11e8-8004-000c29e28409
             Master_Info_File: /mysql_data/3307/data/master.info
                    SQL_Delay: 0
          SQL_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配置檔案，然後重啟。