《一條更新操作引起的MySQL主從復(fù)制異?！芬c(diǎn)：
本文介紹了一條更新操作引起的MySQL主從復(fù)制異常，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

生產(chǎn)環(huán)境異地機(jī)房主從數(shù)據(jù)庫(kù),數(shù)據(jù)量過(guò)百G,數(shù)據(jù)庫(kù)版本社區(qū)版本5.6.25.

同事根據(jù)開(kāi)發(fā)提供的SQL在Master節(jié)點(diǎn)執(zhí)行了一個(gè)大表的的全表更新操作,導(dǎo)致從節(jié)點(diǎn)Slave IO線程中斷.

1)相關(guān)參數(shù)

my.cnf中有兩個(gè)參數(shù)設(shè)置：

expire_logs_days = 7????????#binlog保留時(shí)間7天
max_binlog_size = 1G??????#binlog大小

2)表大小,執(zhí)行SQL

Table: v_clda?? 5.8G
Sql: update v_clda set uploadtime ＝now(); 主庫(kù)執(zhí)行成功

3)主庫(kù),大事物產(chǎn)生的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)異地從庫(kù)報(bào)錯(cuò)

[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 已經(jīng)無(wú)法同步數(shù)據(jù).

一個(gè)事物只能寫(xiě)入一個(gè)binlog日志中,默認(rèn)情況下,binlog日志達(dá)到設(shè)定值后(max_binlog_size),會(huì)自動(dòng)生成一個(gè)新的日志文件,也會(huì)根據(jù)過(guò)期參數(shù)(expire_logs_days)設(shè)置自動(dòng)刪除binlog日志.如果生成了一個(gè)超大的binlog日志,很可能是由于大事物引起的.

嘗試從啟slave線程,多次嘗試后失敗.

嘗試跳過(guò)事物,具體方法如下：

從節(jié)點(diǎn)執(zhí)行(基于GTID)

stop slave;
SET @@SESSION.GTID_NEXT= ‘498815d6-20a9-11e6-a7d6-fa163e5770cc:53’; –根據(jù)實(shí)際情況
BEGIN; COMMIT;
SET SESSION GTID_NEXT = AUTOMATIC;
START SLAVE;
show slave status\G; 主從復(fù)制恢復(fù)正常

從節(jié)點(diǎn)執(zhí)行更新操作,同步數(shù)據(jù)

set session sql_log_bin=0;
update v_clda set uploadtime ＝now();

在執(zhí)行大事物前關(guān)閉 set session sql_log_bin=0; (默認(rèn)是開(kāi)啟的),尤其是異地機(jī)房,網(wǎng)絡(luò)帶寬有限,而且VPN通道不是十分穩(wěn)定的情況下.不允許它生成大量binlog日志.

如果像本例中,已經(jīng)執(zhí)行了,而且生成了大量的binlog,最終導(dǎo)致復(fù)制異常,可以考慮使用跳過(guò)事物的方法來(lái)解決這個(gè)問(wèn)題.

最笨的方法就是重新搭建主從,由于數(shù)據(jù)量比較大,還是異地不可取.

根本解決方法還是要拆分大事物,進(jìn)行批量提交操作.賀春旸老師的MySQL管理之道一書(shū)中第四章4.4節(jié)有具體的解決方法.

參考改為用存儲(chǔ)過(guò)程,每刪除10000條事務(wù)就提交一次,循環(huán)操作直至刪除完畢.經(jīng)過(guò)優(yōu)化,行鎖的范圍變小了,性能也就變好了.相關(guān)代碼如下：

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 ? ;

作者：康壯 | 文章來(lái)源微信公眾號(hào)：DBAplus社群

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/4274.html