《一條更新操作引起的MySQL主從復制異常》要點：
本文介紹了一條更新操作引起的MySQL主從復制異常，希望對您有用。如果有疑問，可以聯系我們。
生產環境異地機房主從數據庫,數據量過百G,數據庫版本社區版本5.6.25.
同事根據開發提供的SQL在Master節點執行了一個大表的的全表更新操作,導致從節點Slave IO線程中斷.
1)相關參數
my.cnf中有兩個參數設置：
expire_logs_days = 7????????#binlog保留時間7天
max_binlog_size = 1G??????#binlog大小
2)表大小,執行SQL
Table: v_clda?? 5.8G
Sql: update v_clda set uploadtime ＝now(); 主庫執行成功
3)主庫,大事物產生的binlog
-rw-rw—- 1 mysql mysql 1.1G Mar 16 02:49 mysql-bin.000159
-rw-rw—- 1 mysql mysql 8.0G Mar 16 15:28 mysql-bin.000160
-rw-rw—- 1 mysql mysql 7.4G Mar 16 18:13 mysql-bin.000161
-rw-rw—- 1 mysql mysql 1.1G Mar 16 23:55 mysql-bin.000162
-rw-rw—- 1 mysql mysql 1.1G Mar 17 12:15 mysql-bin.000163
-rw-rw—- 1 mysql mysql 1.1G Mar 18 16:54 mysql-bin.000164
4)異地從庫報錯
[ERROR] Slave I/O: Unexpected master’s heartbeat data: heartbeat is not compatible with local info;the event’s data:og_file_name mysql-bin.000160<90>ó°Y log_pos 121238917, Error_code: 1623
[ERROR] Slave I/O: Relay log write failure: could not queue event from master, Error_code: 1595
[Note] Slave I/O thread exiting, read up to log ‘mysql-bin.000160’, position 3626103968
[Note] Error reading relay log event: slave SQL thread was killed
Slave 已經無法同步數據.
一個事物只能寫入一個binlog日志中,默認情況下,binlog日志達到設定值后(max_binlog_size),會自動生成一個新的日志文件,也會根據過期參數(expire_logs_days)設置自動刪除binlog日志.如果生成了一個超大的binlog日志,很可能是由于大事物引起的.
嘗試從啟slave線程,多次嘗試后失敗.
嘗試跳過事物,具體方法如下：
從節點執行(基于GTID)
stop slave;
SET @@SESSION.GTID_NEXT= ‘498815d6-20a9-11e6-a7d6-fa163e5770cc:53’; –根據實際情況
BEGIN; COMMIT;
SET SESSION GTID_NEXT = AUTOMATIC;
START SLAVE;
show slave status\G; 主從復制恢復正常
從節點執行更新操作,同步數據
set session sql_log_bin=0;
update v_clda set uploadtime ＝now();
在執行大事物前關閉 set session sql_log_bin=0; (默認是開啟的),尤其是異地機房,網絡帶寬有限,而且VPN通道不是十分穩定的情況下.不允許它生成大量binlog日志.
如果像本例中,已經執行了,而且生成了大量的binlog,最終導致復制異常,可以考慮使用跳過事物的方法來解決這個問題.
最笨的方法就是重新搭建主從,由于數據量比較大,還是異地不可取.
根本解決方法還是要拆分大事物,進行批量提交操作.賀春旸老師的MySQL管理之道一書中第四章4.4節有具體的解決方法.
參考改為用存儲過程,每刪除10000條事務就提交一次,循環操作直至刪除完畢.經過優化,行鎖的范圍變小了,性能也就變好了.相關代碼如下：
DELIMITER ? $$
USE ? BIGDB$$
DROP ? PROCEDURE IF EXISTS BIG_table_delete_10k$$
CREATE ? PROCEDURE BIG_table_delete_10k(IN v_UserId INT)
BEGIN
del_10k:LOOP
delete ? from BIGDB.BIGTABLE where UserId = v_UserId limit 10000;
select ? row_count() into @count;
IF ? @count = 0 THEN
select CONCAT(‘BIGDB.BIGTABLE UserId = ? ‘,v_UserId,’ is ‘,@count,’ rows.’) as BIGTABLE_delete_finish;
LEAVE ? del_10k;
END IF;
select ? sleep(1);
END LOOP ? del_10k;
END$$
DELIMITER ? ;
作者：康壯 | 文章來源微信公眾號：DBAplus社群
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/4274.html
