MySQL主從資料庫同步延遲問題解決

目錄

一、mysql主從同步原理

1. MySQL資料庫主從同步延遲原理。

2. MySQL資料庫主從同步延遲是怎麼産生的。

3. MySQL資料庫主從同步延遲解決方案

判斷主從延時，通常有兩個方法：

二、原因詳解

slave同步延遲的可能原因

如何檢視同步延遲

三、減少同步延遲的操作方案

一、mysql主從同步原理

MySQL的主從同步是一個很成熟的架構，優點為：①在從伺服器可以執行查詢工作(即我們常說的讀功能)，降低主伺服器壓力;②在從主伺服器進行備份，避免備份期間影響主伺服器服務;③當主伺服器出現問題時，可以切換到從伺服器。

相信大家對于這些好處已經非常了解了，在項目的部署中也采用這種方案。但是MySQL的主從同步一直有從庫延遲的問題，那麼為什麼會有這種問題。這種問題如何解決呢？

1. MySQL資料庫主從同步延遲原理。

2. MySQL資料庫主從同步延遲是怎麼産生的。

3. MySQL資料庫主從同步延遲解決方案。

1. MySQL資料庫主從同步延遲原理。

答：談到MySQL資料庫主從同步延遲原理，得從mysql的資料庫主從複制原理說起，mysql的主從複制都是單線程的操作，主庫對所有DDL和 DML産生binlog，binlog是順序寫，是以效率很高，slave的Slave_IO_Running線程到主庫取日志，效率很比較高，下一步， 問題來了，slave的Slave_SQL_Running線程将主庫的DDL和DML操作在slave實施。DML和DDL的IO操作是随即的，不是順 序的，成本高很多，還可能可slave上的其他查詢産生lock争用，由于Slave_SQL_Running也是單線程的，是以一個DDL卡主了，需要 執行10分鐘，那麼所有之後的DDL會等待這個DDL執行完才會繼續執行，這就導緻了延時。有朋友會問：“主庫上那個相同的DDL也需要執行10分，為什 麼slave會延時？”，答案是master可以并發，Slave_SQL_Running線程卻不可以。

2. MySQL資料庫主從同步延遲是怎麼産生的。

答：當主庫的TPS并發較高時，産生的DDL數量超過slave一個sql線程所能承受的範圍，那麼延時就産生了，當然還有就是可能與slave的大型query語句産生了鎖等待。

3. MySQL資料庫主從同步延遲解決方案

答：最簡單的減少slave同步延時的方案就是在架構上做優化，盡量讓主庫的DDL快速執行。還有就是主庫是寫，對資料安全性較高，比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之類的設定，而slave則不需要這麼高的資料安全，完全可以講sync_binlog設定為0或者關閉binlog，innodb_flushlog也 可以設定為0來提高sql的執行效率。另外就是使用比主庫更好的硬體裝置作為slave。

mysql-5.6.3已經支援了多線程的主從複制。原理和丁奇的類似，丁奇的是以表做多線程，Oracle使用的是以資料庫(schema)為機關做多線程，不同的庫可以使用不同的複制線程。

基于區域網路的master/slave機制在通常情況下已經可以滿足'實時'備份的要求了。如果延遲比較大，就先确認以下幾個因素： 

1. 網絡延遲

2. master負載

3. slave負載

一般的做法是，使用多台slave來分攤讀請求，再從這些slave中取一台專用的伺服器，隻作為備份用，不進行其他任何操作，就能相對最大限度地達到'實時'的要求了

slave_net_timeout機關為秒 預設設定為 3600秒

參數含義：當slave從主資料庫讀取log資料失敗後，等待多久重建立立連接配接并擷取資料

master-connect-retry機關為秒 預設設定為 60秒

參數含義：當重建立立主從連接配接時，如果連接配接建立失敗，間隔多久後重試。

通常配置以上2個參數可以減少網絡問題導緻的主從資料同步延遲

判斷主從延時，通常有兩個方法：

1. Seconds_Behind_Master  vs  2. mk-heartbeat，下面具體說下兩者在實作功能的差别。

可以通過監控show slave status\G指令輸出的Seconds_Behind_Master參數的值來判斷，是否有發生主從延時。

其值有這麼幾種：

NULL - 表示io_thread或是sql_thread有任何一個發生故障，也就是該線程的Running狀态是No,而非Yes.

0 - 該值為零，是我們極為渴望看到的情況，表示主從複制良好，可以認為lag不存在。

正值 - 表示主從已經出現延時，數字越大表示從庫落後主庫越多。

負值 - 幾乎很少見，隻是聽一些資深的DBA說見過，其實，這是一個BUG值，該參數是不支援負值的，也就是不應該出現。

Seconds_Behind_Master是通過比較sql_thread執行的event的timestamp和io_thread複制好的 event的timestamp(簡寫為ts)進行比較，而得到的這麼一個內插補點。我們都知道的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發生了錯誤，比之前的小了，那麼當這種情況發生時，負值出現就成為可能。

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

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

二、原因詳解

slave同步延遲的可能原因

1--slave的I/O線程推遲讀取日志中的事件資訊；最常見原因是slave是在單線程中執行所有事務，而master有很多線程可以并行執行事務。

2--帶來低效連接配接的長查詢、磁盤讀取的I/O限制、鎖競争和innodb線程同步啟動等。

3--Master負載；Slave負載

4--網絡延遲

5--機器配置（cpu、記憶體、硬碟）

（主從同步延遲怎麼産生的？）總之，當主庫的TPS并發較高時，産生的DDL數量超過slave一個sql線程所能處理的承受範圍時，主從同步就會産生延時；或者當slave中有大型query語句産生了鎖等待也會産生延時。

如何檢視同步延遲

1--可以通過比對master、slave上的日志位置

2--通過"show slave status\G"檢視Seconds_Behind_Master的值，這個值代表主從同步延遲的時間，值越大說明延遲越嚴重。值為0為正常情況，正值表示已經出現延遲，數字越大從庫落後主庫越多。

3--使用percona-toolkit的pt-hearbeat工具進行檢視。

三、減少同步延遲的操作方案

1--減少鎖競争

    如果查詢導緻大量的表鎖定，需要考慮重構查詢語句，盡量避免過多的鎖。

2--負載均衡

    搭建多少slave，并且使用lvs或nginx進行查詢負載均衡，可以減少每個slave執行查詢的次數和時間，進而将更多的時間用于去處理主從同步。

3--salve較高的機器配置

4--slave調整參數

    為了保障較高的資料安全性，配置sync_binlog=1，innodb_flush_log_at_trx_commit=1等設定。而Slave可以關閉binlog，innodb_flush_log_at_trx_commit也可以設定為0來提高sql的執行效率（這兩個參數很管用）

5--并行複制

    即将單線程的複制改成多線程複制。

    從庫有兩個線程與複制相關：io_thread 負責從主庫拿binlog并寫到relaylog， sql_thread 負責讀relaylog并執行。

多線程的思路就是把sql_thread 變成分發線程，然後由一組worker_thread來負責執行。

幾乎所有的并行複制都是這個思路，有不同的，便是sql_thread 的分發政策。

MySQL5.7的真正并行複制enhanced multi-threaded slave（MTS）很好的解決了主從同步複制的延遲問題。

（2）slave同步狀态中出現Slave_IO_Running: NO

報錯：Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

原因1：清理資料導緻主從庫不同步（前提是主庫的binlog日志沒有被暴力删除或錯誤删除，即要確定正在使用的那個最新binlog檔案在master主庫機器上存在）。

解決辦法：

1）先進入slave中執行:"slave stop;"來停止從庫同步；

2）再去master中執行:"flush logs;"來清空日志；

3）然後在master中執行:"show master status;"檢視下主庫的狀态，主要是日志的檔案和position；

4）然後回到slave中，執行:"CHANGE MASTER TO ......執行同步指令

原因2：該錯誤發生在從庫的io程序從主庫拉取日志時，發現主庫的mysql_bin.index檔案中第一個檔案不存在。出現此類報錯可能是由于你的slave 由于某種原因停止了好長一段時間，當你重新開機slave 複制的時候，在主庫上找不到相應的binlog ,會報此類錯誤。或者是由于某些設定主庫上的binlog被删除了，導緻從庫擷取不到對應的binglog file。

解決辦法：

1）為了避免資料丢失，需要重新進行slave同步操作。

2）注意主庫binlog的清理政策，選擇基于時間過期的删除方式還是基于空間使用率的删除方式。

3）記住最好不要使用"rm -rf"指令删除binlog file，這樣不會同步修改mysql_bin.index 記錄的binlog 條目。在删除binlog的時候確定主庫保留了從庫"show slave status\G"的Relay_Master_Log_File對應的binlog file。任何時候都不能删除正在使用的那個最新binlog檔案；最好把bin-log檔案不要删除，最好給備份出來。

原因2的情況下，使用原因1的處理方案顯然是解決不了的！此時的解決方案是：

在從庫上執行：

mysql> stop slave;

mysql> reset slave;

mysql> start slave;

mysql> show slave status \G;

（3）slave同步狀态中出現Slave_IO_Running: Connecting

導緻這個錯誤的原因一般是：

1--網絡不通

2--權限問題（連接配接master的使用者名和密碼跟master授權不一緻）

3--連接配接時用的log file和pos節點跟"show master status"的結果不一緻

（4）slave同步狀态中出現Slave_SQL_Running: No ，即slave不同步！

解決辦法：

第一種方法：忽略錯誤後，繼續同步。

該方法适用于主從庫資料相差不大，或者要求資料可以不完全統一的情況，資料要求不嚴格的情況（下面均為在slave機器上的操作）

mysql> stop slave;

mysql> set global sql_slave_skip_counter =1;  //表示跳過一步錯誤，後面的數字可變；或者在my.cnf裡添加slave-skip-errors = all（上面已在配置中添加）

mysql> start slave;

mysql> show slave status\G      //檢視

第二種方法：重新做主從，完全同步

該方法适用于主從庫資料相差較大，或者要求資料完全統一的情況

1--master主庫上操作

mysql> flush tables with read lock;  //進行鎖表，防止資料寫入。注意該處是鎖定為隻讀狀态，語句不區分大小寫

#mysqldump --lock-all-tables --all-databases --flush-logs --master-data=2 > /root/allsql.sql   //主庫完全備份(如果是指定庫同步，就備份指定庫)，注意資料庫備份一定要定期進行，確定資料萬無一失

mysql> show master status;      //檢視master狀态，注意log file和pos節點，slave同步會用到

# scp mysql.bak.sql [email protected]:/tmp/   //把備份檔案傳到slave從庫機器，進行資料恢複

2--slave從庫操作

mysql> stop slave;

mysql> source /tmp/mysql.bak.sql

mysql> change master to master_host = '192.168.1.101', master_user = 'slave', master_port=3306.......；

mysql> start slave;

mysql> show slave status\G 

.......

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

此種方法中最為關鍵主要有兩步：

①主伺服器上鎖表做完全備份，并滾動日志；

②從伺服器上進行半道恢複.

（5）slave中繼日志relay-log損壞？

什麼是中繼日志？

relay-log存放在從伺服器上，從伺服器将主伺服器的二進制日志檔案拷貝到自己的主機上放在中繼日志中，然後調用SQL線程按照拷中繼日志檔案中的二進制日志檔案執行以便就可達到資料的同步 。

如何避免中繼日志損壞：

mysql 5.6版本後，在my.cnf檔案中開啟relay_log_recover=1即可避免。

（6）slave連接配接逾時且重新連接配接頻繁

若有多個slave，且沒有設定server_id或兩個slave設定相同的server_id，将有可能會出現伺服器的ID沖突。這種情況下，其中一台slave可能會頻繁逾時或丢失後重新連接配接序列。

是以一定要確定每台slave及master在my.cnf中都要設定不一樣的server_id。

（7）主庫與從庫使用不同的存儲引擎造成不同步

（8）從庫同步時，提示表不存在

錯誤：Last_Error: Error executing row event: 'Table 'test.t1' doesn't exist'

解決方法：在從庫重建這張表。

（9）max_allowed_packet設定過小導緻slave報錯

max_allowed_packet預設是16M，主從庫的max_allowed_packet值和備庫上的不比對。

在這情況下，主庫可能會記錄一個備庫認為過大的包。當備庫擷取到該二進制日志事件時，可能會碰到各種問題，如無限報錯和重試、中繼日志損壞等。

具體表現：

從庫的Slave_IO_Thread死掉了，檢視後，出現以下錯誤提示：

Got a packet bigger than 'max_allowed_packet' bytes

很明顯是由于max_allowed_packet的設定太小導緻的，然後查檢主從庫上的設定，主庫的設定大于從庫，因為max_allowed_packet是動态參數，先調整從庫上的max_allowed_packet 與主庫相同，重新單獨啟動I/O線程就正常了。

原理說明：binlog的事件以RBR格式記錄，且目前的事件長度大于了從庫的max_allowed_packet, 導緻無法Slave IO不能正常讀取master binlog event.

（10）在master上删除一條記錄時出現的故障

在master上删除一條記錄後，slave上因找不到這條記錄而報錯。

解決方法：

由于主庫上已經對這條語句進行了删除操作，故可以跳過。

在這種情況下，說明主從同步可能資料會有不一緻的情況發生，是以需要使用pt-table-checksum進行資料庫一緻性比對。

（11）在master更新一條記錄，而slave卻找不到。

主從資料不緻時，master有某條記錄，但在salve上沒有這條記錄，若在master上進行更新這條記錄，則在slave中可能報錯。

解決方法：

1--根據從庫發生異常的位置，查主庫上的二進制日志。

2--根據主庫二進制日志資訊，找到更新後的整條記錄。

3--在從庫上執行在主庫上找到的記錄資訊，進行insert操作。

4--跳過這條語句，再同步slave。

5--使用pt-table-checksum檢視主從庫表資料否一緻。