mysql複製延遲原因和解決法「mysql主從複製出現差異怎麼解決」

一、MySQL主從不同步情況

1.1 網路的延遲

由於mysql主從複製是基於binlog的一種非同步複製

通過網路傳送binlog文件，理所當然網路延遲是主從不同步的絕大多數的原因，特別是跨機房的數據同步出現這種幾率非常的大，所以做讀寫分離，注意從業務層進行前期設計。

1.2 主從兩台機器的負載不一致

由於mysql主從複製是主資料庫上面啟動1個io線程，而從上面啟動1個sql線程和1個io線程，當中任何一台機器的負載很高，忙不過來，導致其中的任何一個線程出現資源不足，都將出現主從不一致的情況。

1.3 max_allowed_packet設置不一致

主資料庫上面設置的max_allowed_packet比從資料庫大，當一個大的sql語句，能在主資料庫上面執行完畢，從資料庫上面設置過小，無法執行，導致的主從不一致。

1.4 自增鍵不一致

key自增鍵開始的鍵值跟自增步長設置不一致引起的主從不一致。

1.5 同步參數設置問題

mysql異常宕機情況下，如果未設置sync_binlog=1或者
innodb_flush_log_at_trx_commit=1很有可能出現binlog或者relaylog文件出現損壞，導致主從不一致。

1.6 自身bug

mysql本身的bug引起的主從不同步

1.7 版本不一致

特別是高版本是主，低版本為從的情況下，主資料庫上面支持的功能，從資料庫上面不支持該功能。

1.8 主從不一致優化配置

基於以上情況，先保證max_allowed_packet，自增鍵開始點和增長點設置一致

再者犧牲部分性能在主上面開啟sync_binlog，對於採用innodb的庫，推薦配置下面的內容

innodb_flush_logs_at_trx_commit = 1

innodb-support_xa = 1 # Mysql 5.0 以上

innodb_safe_binlog # Mysql 4.0

同時在從上面推薦加入下面兩個參數

skip_slave_start

read_only

二、解決主從不同步的方法

2.1 主從不同步場景描述

今天發現Mysql的主從資料庫沒有同步

先上Master庫：

mysql>show processlist;

查看下進程是否Sleep太多。發現很正常。

show master status;

查看主庫狀態也正常。

mysql> show master status;FilePositionBinlog_Do_DBBinlog_Ignore_DBmysqld-bin.0000013260mysql,test,information_schema

1 row in set (0.00 sec)

複製代碼再到Slave上查看

mysql> show slave statusG

Slave_IO_Running: Yes

Slave_SQL_Running: No

複製代碼由此可見是Slave不同步

2.2 解決方法一：忽略錯誤後，繼續同步

該方法適用於主從庫數據相差不大，或者要求數據可以不完全統一的情況，數據要求不嚴格的情況

解決：

stop slave;

複製代碼

表示跳過一步錯誤，後面的數字可變

set global sql_slave_skip_counter =1;

start slave;

複製代碼

之後再用mysql> show slave statusG 查看：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

複製代碼ok，現在主從同步狀態正常了。。。

2.3 方式二：重新做主從，完全同步

該方法適用於主從庫數據相差較大，或者要求數據完全統一的情況

解決步驟如下：

1.先進入主庫，進行鎖表，防止數據寫入

使用命令：

mysql> flush tables with read lock;

注意：該處是鎖定為只讀狀態，語句不區分大小寫

2.進行數據備份

把數據備份到mysql.bak.sql文件

[root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql

這裡注意一點：資料庫備份一定要定期進行，可以用shell腳本或者python腳本，都比較方便，確保數據萬無一失

3.查看master 狀態

mysql> show master status;

+——————-+———-+————–+——————————-+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————-+———-+————–+——————————-+

| mysqld-bin.000001 | 3260 | | mysql,test,information_schema |

+——————-+———-+————–+——————————-+

1 row in set (0.00 sec)

複製代碼

4.把mysql備份文件傳到從庫機器，進行數據恢復

使用scp命令

[root@server01 mysql]# scp mysql.bak.sql root@192.168.1.206:/tmp/

5.停止從庫的狀態

mysql> stop slave;

6.然後到從庫執行mysql命令，導入數據備份

mysql> source /tmp/mysql.bak.sql

7.設置從庫同步，注意該處的同步點，就是主庫show master status信息里的| File| Position兩項

change master to master_host = 『192.168.1.206』, master_user = 『rsync』, master_port=3306, master_password=」, master_log_file = 『mysqld-bin.000001』, master_log_pos=3260;

8.重新開啟從同步

mysql> start slave;

9.查看同步狀態

mysql> show slave statusG 查看：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

好了，同步完成啦

三、如何監控mysql主從之間的延遲

3.1 前言：

日常工作中，對於MYSQL主從複製的檢查有兩方面

保證複製的整體結構是否完整；

需要檢查數據是否一致；

對於前者我們可以通過監控複製線程是否工作正常以及主從延時是否在容忍範圍內，對於後者則可以通過分別校驗主從表中數據的md5碼是否一致，來保證數據一致，可以使用Maatkit工具包中的mk-table-checksum工具去檢查。

本文檔介紹下關於如何檢查主從延遲的問題。

主從延遲判斷的方法，通常有兩種方法：Seconds_Behind_Master和mk-heartbeat

3.2方法1.

通過監控show slave statusG命令輸出的Seconds_Behind_Master參數的值來判斷，是否有發生主從延時。

mysql> show slave statusG;

1. row **

 Slave_IO_State: Waiting for master to send event
 Master_Host: 192.168.1.205
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 30
 Master_Log_File: edu-mysql-bin.000008
 Read_Master_Log_Pos: 120
 Relay_Log_File: edu-mysql-relay-bin.000002
 Relay_Log_Pos: 287
 Relay_Master_Log_File: edu-mysql-bin.000008
 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: 120
 Relay_Log_Space: 464
 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: 205
 Master_UUID: 7402509d-fd14-11e5-bfd0-000c2963dd15
 Master_Info_File: /home/mysql/data/master.info
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
 Master_Retry_Count: 86400
 Master_Bind: 
 Last_IO_Error_Timestamp: 
 Last_SQL_Error_Timestamp: 
 Master_SSL_Crl: 
 Master_SSL_Crlpath: 
 Retrieved_Gtid_Set: 
 Executed_Gtid_Set: 
 Auto_Position: 0

1 row in set (0.00 sec)

複製代碼以上是show slave statusG的輸出結果，這些結構給我們的監控提供了很多有意義的參數。

Slave_IO_Running

該參數可作為io_thread的監控項，Yes表示io_thread的和主庫連接正常並能實施複製工作，No則說明與主庫通訊異常，多數情況是由主從間網路引起的問題；

Slave_SQL_Running

該參數代表sql_thread是否正常，具體就是語句是否執行通過，常會遇到主鍵重複或是某個表不存在。

Seconds_Behind_Master

是通過比較sql_thread執行的event的timestamp和io_thread複製好的event的timestamp(簡寫為ts)進行比較，而得到的這麼一個差值；NULL—表示io_thread或是sql_thread有任何一個發生故障，也就是該線程的Running狀態是No，而非Yes。0 — 該值為零，是我們極為渴望看到的情況，表示主從複製良好，可以認為lag不存在。

正值 — 表示主從已經出現延時，數字越大表示從庫落後主庫越多。負值 — 幾乎很少見，我只是聽一些資深的DBA說見過，其實，這是一個BUG值，該參數是不支持負值的，也就是不應該出現。

備註Seconds_Behind_Master的計算方式可能帶來的問題

我們都知道的relay-log和主庫的bin-log裡面的內容完全一樣，在記錄sql語句的同時會被記錄上當時的ts，所以比較參考的值來自於binlog，其實主從沒有必要與NTP進行同步，也就是說無需保證主從時鐘的一致。你也會發現，其實比較真正是發生在io_thread與sql_thread之間，而io_thread才真正與主庫有關聯，於是，問題就出來了，

當主庫I/O負載很大或是網路阻塞

io_thread不能及時複製binlog（沒有中斷，也在複製），而sql_thread一直都能跟上io_thread的腳本，這時Seconds_Behind_Master的值是0，

也就是我們認為的無延時，但是，實際上不是，你懂得。

這也就是為什麼大家要批判用這個參數來監控資料庫是否發生延時不準的原因，但是這個值並不是總是不準，

如果當io_thread與master網路很好的情況下，那麼該值也是很有價值的。』『之前，提到Seconds_Behind_Master這個參數會有負值出現，我們已經知道該值是io_thread的最近跟新的ts與sql_thread執行到的ts差值，

前者始終是大於後者的，唯一的肯能就是某個event的ts發生了錯誤，比之前的小了，那麼當這種情況發生時，負值出現就成為可能。

3.2 方法2.

mk-heartbeat：Maatkit萬能工具包中的一個工具，被認為可以準確判斷複製延時的方法。

mk-heartbeat的實現也是藉助timestmp的比較實現的，它首先需要保證主從伺服器必須要保持一致，通過與相同的一個NTP server同步時鐘。它需要在主庫上創建一個heartbeat的表，裡面至少有id與ts兩個欄位，id為server_id，ts就是當前的時間戳now()，該結構也會被複制到從庫上，表建好以後，會在主庫上以後台進程的模式去執行一行更新操作的命令，定期去向表中的插入數據，這個周期默認為1秒，同時從庫也會在後台執行一個監控命令，與主庫保持一致的周期去比較，複製過來記錄的ts值與主庫上的同一條ts值，差值為0表示無延時，差值越大表示延時的秒數越多。我們都知道複製是非同步的ts不肯完全一致，所以該工具允許半秒的差距，在這之內的差異都可忽略認為無延時。這個工具就是通過實打實的複製，巧妙的借用timestamp來檢查延時。