mysql資料庫主從同步中斷的各種情況

1. 主庫上修改用戶權限
   原因1：在主庫上執行drop user 或者授權操作時，導致的從庫上報錯，停止恢復主庫的binlog

解決方法：重啟從庫同步，跳過出錯陳述句在從庫上執行，

mysql> stop slave;

mysql> set sql_slave_skip_counter=1;

mysql> start slave;

之后，檢查從庫是否可以正常同步，

注意：在主庫上對用戶進行操作/授權時，進入mysql提示符后，不要指定具體的資料庫，可以避免這類情況發生，

sql_slave_skip_counter以event為單位skip，直到skip完第N個event所在的event group才停止，對于事務表，一個event group對應一個事務；對于非事務表，一個event group對應一條SQL陳述句，一個event group包含多個events，
這里我只針對顯式事務模擬insert遇到Duplicate entry(1062錯誤)，知道了問題本質，delete/update中的1032錯誤類似去分析
set global sql_slave_skip_counter = N
This statement skips the next N events from the master.
(即是跳過N個events，這里最重要的是理解event的含義!在mysql中,對于sql的 binary log 實際上是由一連串的event組成的一個組,即事務組，)

在備庫上設定 global sql_slave_skip_counter =N 會跳過當前時間來自于master的之后N個事件,這對于恢復由某條SQL陳述句引起的從庫復制有效. 此陳述句只在當slave threads是停止時才有效,否則將發生一條錯誤…每忽略一個事件，N 減一，直到N減為0！

當然，你也可以在對用戶權限等進行操作時，強制不記錄binlog，即

mysql> set session sql_log_bin=0;

mysql> grant …..;/drop user …;

mysql> set session sql_log_bin=1;

========================

2. 主從版本不同
   原因2：主從mysql不同版本（例如主庫5.0，備庫4.0）導致在備庫上某些恢復無法進行，因而報錯，停止恢復主庫binlog

解決方法：

首先到從庫上記錄下出現問題的陳述句，具體是insert/update/delete/DDL；

在從庫上手動執行該陳述句，根據版本不同可能會稍微修改一下（注意從庫是read-only，需要root用戶權限執行）；

在從庫上跳過出錯的陳述句，繼續同步：

mysql> stop slave;

mysql> set sql_slave_skip_counter=1;

mysql> start slave;

========================

3. 主庫負載壓力大
   原因3：主庫IO壓力大，沒有及時向從庫發送binlog或者回應從庫的連接請求，從庫重試一定次數（master-connect-retry）/超時（slave-net-timeout）后，與主庫連接斷開

解決方法：先停止一下從庫同步，停一段時間后（主庫IO壓力沒那么大），再起同步

mysql> stop slave;

some time later…

mysql> start slave;

========================

4. 資料庫bug
   原因4：由于mysql的某些bug導致的中斷，這種情況可能的原因不確定

解決方法：重啟一下從庫的同步

（注意：該方法可以解決大部分主從同步例外，遇到問題時可以首先嘗試一下，如果不行再使用其他方法解決）

mysql> stop slave;

mysql> start slave;

========================

5. 主庫上的中斷操作
   原因5：主庫上中斷了某個操作（比如load data），可能會導致主從不一致，從庫中斷同步

解決方法：

首先需要確認之前的操作在主庫上確實已經停止；

然后對比主從上該表的資料是否一致，絕大部分情況主從資料會不一樣，通過檢查從庫上出現的錯誤，執行

mysql> show slave status\G;

mysql> stop slave;

mysql> set sql_slave_skip_counter=1; 
   //注意，若無錯誤，無需執行這一步

mysql> start slave;

========================

6. 主從出現不同錯誤號
   原因6：主從庫出現不同的錯誤號，出現這種情景的原因不確定

解決方法：可以通過重啟從庫同步，若不行再看show slave status列印的錯誤資訊，進行跳過

mysql> stop slave;

mysql> start slave;

========================

7. 主從資料不一致
   原因7：在主庫上操作了從庫上沒有的表或者行資料，導致從庫報錯，中斷同步

解決方法：重啟主從同步，跳過出錯的陳述句，但要注意一定要逐個跳過，否則會有問題

mysql> stop slave;

mysql> set sql_slave_skip_counter=1;

mysql> start slave;

========================

8. 主從資料庫不一致
   原因8：主庫上有A，B兩個庫，但從庫上只有B庫，且從庫上沒有設定Replicate_Ignore_DB，當主庫操作A庫時，會導致從庫報錯，中斷同步（此情況與原因7很類似）

解決方法：確認不同步A庫的話，可以在從庫上設定Replicate_Ignore_DB或者Replicate_Wild_Ignore_Table，之后重啟從庫mysql實體：

在從庫的組態檔my.cnf中添加：

replicate-wild-ignore-table = A.%

或者

replicate-ignore-DB = A

重啟從庫mysql實體

========================
MySQL主從復制中replicate-ignore-db replicate-wild-ignore-table的應用

replicate-ignore-db
replicate-wild-ignore-table
官方的解釋是：在主從同步的環境中，replicate-ignore-db用來設定不需要同步的庫，生產庫上不建議設定過濾規則，如果非要設定，那就用Replicate_Wild_Ignore_Table:

在實際生產主從復制環境中，配置Replicate_Wild_Ignore_Table:mysql.% 主庫賬戶寫，從庫賬戶讀

===================

9. 主庫的binlog損壞
   原因9：主庫向從庫傳輸的binlog包損壞，導致從庫恢復到某個sql時無法進行，中斷同步

解決方法：在從庫上，記錄同步點，并重新同步

mysql> stop slave;

mysql> change master to master_Log_File=‘mysql-bin.000001’,master_log_pos=488;

mysql> start slave;

注意：master_Log_File取自show slave status中Master_Log_File欄位，master_log_pos取自Exec_Master_Log_Pos欄位，

========================

10.   從庫SQL執行不通過
    

原因10：主庫執行正常的SQL在從庫上執行報錯，類似Error ‘You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 1’ on query. Default database: ‘’. Query: 'UPDATE FC_Word.wordext2 (ignored)

發生錯誤的SQL，手動在從庫上執行則成功，這是mysql一個bug，至今未解決，

Bug相關鏈接：http://bugs.mysql.com/bug.php?id=31686

解決方法：可以先試著讓從庫從失敗的位置再執行一次，如果仍不能通過，可以DBA手動執行，

1）重指向錯誤點恢復：

mysql> stop slave;

mysql> change master to master_Log_File='mysql-bin.000001',master_log_pos=488;

mysql> start slave;

2）DBA手動執行步驟：

mysql> stop slave;

mysql> set sql_slave_skip_counter=1;

mysql> update/insert/delete statement

mysql> start slave;

========================

11.  從庫上大事務產生的阻塞
    

原因11：對于某些允許前端業務寫操作的從庫（不推薦），從主庫同步過來的SQL或者存盤程序等與從庫本身的事務產生阻塞等待資源釋放（如表，行鎖），超過等待時間后，從庫與主庫的同步中斷

解決方法：若從主庫同步的SQL或者存盤程序先執行，則沒有問題，不會影響主從同步；

若從庫上的事務先執行且執行時間較長，會影響從庫的同步，可以：

mysql> stop slave;

mysql> start slave;

========================

12.  從庫上存盤程序執行報錯
    

原因12：對于存盤程序和觸發器等，在主庫上執行正常，從庫執行報錯，類似：

Last_Error: Error ‘Explicit or implicit commit is not allowed in stored function or trigger.’ on query. Default database: ‘’. Query: ‘update FC_Word.ideainfo1 set touch = touch+1, moduid = 2745112, modtime = ‘2011-07-20 12:08:08’ , ilong = 0 , ideastat3 = ((ideastat3|1)&(~4)) , ideastat4 = (case when ideastat4&8=8 then ideastat4&7 else ideastat4 end) where ideaid = 174568837 and isdel=0’

解決方法：重啟一下slave同步就可以了

mysql> stop slave;

mysql> start slave;

========================