一種修復(fù)MySQL主從數(shù)據(jù)差異的思路

IT那活兒發(fā)布于2023-01-11 13:19 / 531人閱讀

點(diǎn)擊上方“IT那活兒”公眾號，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了！！！

一

背景概述

MySQL主從復(fù)制技術(shù)應(yīng)用非常廣泛，M-S復(fù)制架構(gòu)、keepalived＋M-M復(fù)制架構(gòu)、MHA等高可用架構(gòu)都基于MySQL主從復(fù)制技術(shù)。

主從復(fù)制是基于binlog的邏輯復(fù)制，實(shí)際應(yīng)中,可能會因?yàn)楦鞣N原因出現(xiàn)主從數(shù)據(jù)不一致的情況,因此我們需要定期或不定期地開展主從復(fù)制數(shù)據(jù)一致性的校驗(yàn)和修復(fù)工作。

如果發(fā)生了mysql主從數(shù)據(jù)不一致的情況,我們應(yīng)該怎樣修復(fù)呢?

本次分享一種不影響主庫業(yè)務(wù)的修復(fù)思路。

二

環(huán)境準(zhǔn)備

2.1 Master節(jié)點(diǎn)

創(chuàng)建測試表t1和t2：

CREATE TABLE `t1` (

`id` int NOT NULL AUTO_INCREMENT,

`username` varchar(20) NOT NULL,

`phone` int(11) NOT NULL,

`create_time` varchar(20) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=innodb DEFAULT CHARSET=utf8mb4;

insert into t1(username,phone,create_time) values("張三","0123456789",now());

insert into t1(username,phone,create_time) values("張三","0123456789",now());

insert into t1(username,phone,create_time) values("張三","0123456789",now());

insert into t1(username,phone,create_time) values("李四","0123456789",now());

insert into t1(username,phone,create_time) values("李四","0123456789",now());

insert into t1(username,phone,create_time) values("李四","0123456789",now());


 CREATE TABLE `t2` (

`id` int NOT NULL AUTO_INCREMENT,

`username` varchar(20) NOT NULL,

`phone` int(11) NOT NULL,

`create_time` varchar(20) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=innodb DEFAULT CHARSET=utf8mb4;



insert into t2(username,phone,create_time) values("張三","0123456789",now());

insert into t2(username,phone,create_time) values("張三","0123456789",now());

insert into t2(username,phone,create_time) values("張三","0123456789",now());

insert into t2(username,phone,create_time) values("李四","0123456789",now());

insert into t2(username,phone,create_time) values("李四","0123456789",now());

insert into t2(username,phone,create_time) values("李四","0123456789",now());

2.2 Slave節(jié)點(diǎn)

檢查復(fù)制是否正常,確保復(fù)制正常。

三

錯誤模擬

3.1 Salve節(jié)點(diǎn)執(zhí)行以下操作

set sql_log_bin=0;

delete from t1 where id=3;

3.2 Master節(jié)點(diǎn)執(zhí)行

delete from t1 where id=3;

3.3 Slave節(jié)點(diǎn)檢查復(fù)制狀態(tài)

此時slave復(fù)制已發(fā)生異常,無法應(yīng)用主庫操作。

3.4 Master持續(xù)操作,模擬業(yè)務(wù)

insert into t1(username,phone,create_time) values("張三","0123456789",now());

insert into t1(username,phone,create_time) values("張三","0123456789",now());

insert into t1(username,phone,create_time) values("張三","0123456789",now());

insert into t1(username,phone,create_time) values("李四","0123456789",now());

insert into t2(username,phone,create_time) values("李四","0123456789",now());

insert into t2(username,phone,create_time) values("李四","0123456789",now());

四

數(shù)據(jù)修復(fù)

4.1 Master節(jié)點(diǎn)導(dǎo)出slave異常的數(shù)據(jù)表

mysqldump -uxxx-pxxxx -P3306 -S 3306.sock --single-
transaction --master-data=2 test t1 > t1.sql

查看gtid信息并記錄。

grep -A10 GLOBAL.GTID_PURGED t1.sql

SET @@GLOBAL.GTID_PURGED=fdc161ff-5aea-11eb-ab62-000c29e9d0c1:1-81;

4.2 Master持續(xù)模擬業(yè)務(wù)

insert into t1(username,phone,create_time) values("張三","0123456789",now());

insert into t1(username,phone,create_time) values("張三","0123456789",now());

insert into t1(username,phone,create_time) values("張三","0123456789",now());

insert into t1(username,phone,create_time) values("李四","0123456789",now());

insert into t2(username,phone,create_time) values("李四","0123456789",now());

insert into t2(username,phone,create_time) values("李四","0123456789",now());

4.3 Slave節(jié)點(diǎn)導(dǎo)入數(shù)據(jù)

檢查gtid信息：

導(dǎo)入數(shù)據(jù)：

導(dǎo)入數(shù)據(jù)時發(fā)生報錯,無法更新gtid信息,該錯誤不需理會,千萬不能為了解決該錯誤執(zhí)行resetmaster。

再次檢查gtid信息,未發(fā)生變化。

設(shè)置復(fù)制過濾并應(yīng)用日志：

此處設(shè)置的SQL_AFTER_GTIDS為備份文件中g(shù)tid,復(fù)制應(yīng)用日志到該gtid后停止應(yīng)用日志,此時異常的表數(shù)據(jù)已修復(fù),清除復(fù)制過濾規(guī)則后啟動sql應(yīng)用即可。

CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ();

start slave sql_thread;

五

數(shù)據(jù)驗(yàn)證

5.1 master節(jié)點(diǎn)

5.2 slave節(jié)點(diǎn)

演示中數(shù)據(jù)較少使用count(*)和select* 即可進(jìn)行比較,生產(chǎn)中數(shù)據(jù)量大,環(huán)境復(fù)雜建議使用pt-table-checksum進(jìn)行一致性校驗(yàn)。

演示環(huán)境為啟用GTID的主從復(fù)制,同樣適用于非GTID復(fù)制環(huán)境,根據(jù)情況選用合適的startslave選項(xiàng),參考官方文檔STARTSLAVE Statement部分:

START SLAVE [thread_types] [until_option] [connection_options] [channel_option]



thread_types:

    [thread_type [, thread_type] ... ]



thread_type:

    IO_THREAD | SQL_THREAD



until_option:

    UNTIL { {SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set

          | MASTER_LOG_FILE = log_name, MASTER_LOG_POS = log_pos

          | RELAY_LOG_FILE = log_name, RELAY_LOG_POS = log_pos

          | SQL_AFTER_MTS_GAPS }