MySQL主從不同步問題分析與處理思路

之前部署了Mysql主從複製環境（MySQL主從複製環境部署【http://blog.itpub.net/31015730/viewspace-2153251/】）以及總結了mysql主從複製的原理和相關知識（MySQL主從複製原理及必備知識總結【http://blog.itpub.net/31015730/viewspace-2154408/】），但是在mysql主從同步過程中會出現很多問題，導致資料同步異常，主要有兩個比較頭疼的問題：

1、主從資料不同步後如何處理？

2、主從同步延遲問題如何解決？

以下內容梳理了幾種主從同步中可能存在的問題以及解決思路:

（1）slave執行過慢不能與master同步，也就是MySQL資料庫主從同步延遲

MySQL資料庫slave伺服器延遲的現象是非常普遍的，MySQL複製允許從機進行SELECT操作，但是在實際生產環境下，由於從機延遲的關係，很難將讀取操作轉向到從機。這就導致了有了以下一些潛規則：“實時性要求不高的讀取操作可以放到slave伺服器，實時性要求高的讀取操作放到master伺服器”，“從機僅能做前一天的統計類查詢”。

slave滯後即slave不能快速執行來自於master的所有事件，從而不能避免更新slave資料延遲。

mysql的master-slave架構中master僅做寫入、更新、刪除操作，slave做select操作。造成slave滯後的原因有很多。

slave同步延遲的原理

MySQL的主從複製都是單執行緒的操作，主庫對所有DDL和DML產生的日誌寫進binlog，由於binlog是順序寫，所以效率很高。

Slave的IO Thread執行緒從主庫中bin log中讀取取日誌。

Slave的SQL Thread執行緒將主庫的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是隨即的，不是順序的，成本高很多。

由於SQL Thread也是單執行緒的，如果slave上的其他查詢產生lock爭用，又或者一個DML語句（大事務、大查詢）執行了幾分鐘卡住了，那麼所有之後的DML會等待這個DML執行完才會繼續執行，這就導致了延時。也許有人會質疑：主庫上那個相同的DDL也會執行幾分鐘，為什麼slave會延時？原因是master可以併發執行，而Slave_SQL_Running執行緒卻不可以。

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 root@192.168.1.102:/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檢視主從庫表資料否一致。

 文章連線：http://www.cnblogs.com/kevingrace/p/6261111.html