Methode zur Überprüfung und Reparatur der Datenkonsistenz bei der MySQL-Master-Slave-Replikation und automatisierte Implementierung

1. Einführung

Die „MySQL-Master-Slave-Replikations“-Technologie wird häufig in gängigen Hochverfügbarkeitsarchitekturen in der Internetbranche verwendet, beispielsweise in der gemeinsamen Ein-Master-Slave-Replikationsarchitektur Keepalived + MySQL Dual -Master-(Master-Slave-)Replikationsarchitektur, MHA + eine Master- und zwei Slave-Replikationsarchitektur usw. verwenden alle die MySQL-Master-Slave-Replikationstechnologie. Da es sich bei der Master-Slave-Replikation jedoch um eine auf Binlog basierende logische Replikation handelt, ist das Risiko einer Inkonsistenz bei den replizierten Daten unvermeidlich. Dieses Risiko führt nicht nur zu Inkonsistenzen beim Benutzerdatenzugriff, sondern führt auch zu 1032- und 1062-Fehlern bei der nachfolgenden Replikation wird die versteckte Gefahr einer Stagnation in der Replikationsarchitektur verursachen. Um dieses Problem rechtzeitig zu erkennen und zu lösen, müssen wir regelmäßig oder unregelmäßig Überprüfungs- und Reparaturarbeiten an der Datenkonsistenz der Master-Slave-Replikation durchführen. Wie kann diese Arbeit erreicht werden? Wie kann diese Arbeit automatisiert werden? Lassen Sie uns diese Fragen untersuchen.

2. Methode zur Reparatur der Datenkonsistenzprüfsumme

Um eine Datenkonsistenzreparatur bei der Master-Slave-Replikation zu erreichen, empfehlen wir zunächst zwei beliebte Tools. Sie sind Perconas pt -table-checksum und pt-table-sync Ersteres wird verwendet, um die Konsistenz von Master-Slave-Replikationsdaten zu überprüfen, und letzteres wird verwendet, um Daten zu reparieren und die Datenkonsistenz wiederherzustellen.

2.1 Funktionsprinzip

pt-table-checksum führt eine Datenblocküberprüfung in der Hauptdatenbank über SQL durch und überträgt dann dieselbe Anweisung an die Slave-Datenbank Berechnen Sie die Prüfsumme der Datenblöcke in der Slave-Datenbank und vergleichen Sie schließlich die Prüfsummen derselben Blöcke in der Master-Slave-Datenbank, um festzustellen, ob die Master-Slave-Daten inkonsistent sind.

pt-table-sync wird verwendet, um Inkonsistenzen in Master-Slave-Replikationsdaten zu reparieren, sodass diese schließlich konsistent sind. Es können auch mehrere Instanzen oder mehrere nicht verbundene Datenbankinstanzen implementiert werden, die Doppel- oder Mehrfachschreibvorgänge anwenden. Auf Konsistenz repariert. Gleichzeitig ist auch die Überprüfungsfunktion von pt-table-checksum intern integriert, die während der Überprüfung repariert oder basierend auf den Berechnungsergebnissen von pt-table-checksum repariert werden kann.

2.2 Download-Methode

Diese beiden Tools sind im Percona-Toolkit enthalten, Online-Download-Adresse: https://www.percona.com/downloads/percona-toolkit /2.2.2/.

Die Anweisungen zum direkten Herunterladen auf das Gerät lauten wie folgt: Nach dem Herunterladen dekomprimieren und verwenden: wget https://www.percona.com/downloads/percona-toolkit/2.2.2/percona-toolkit- 2.2.2.tar .gz

2.3 Verifizierungs- und Reparaturmethode

(1) Erstellen Sie ein Verifizierungskonto in der Hauptdatenbank

GRANTUPDATE,INSERT,DELETE,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'hangxing'@'MasterIP'identified by 'PASSWORD';
GRANTALL ON test.* TO 'hangxing'@'MasterIP' IDENTIFIED BY 'PASSWORD';

(2) In der Hauptdatenbank erstellt die Bibliothek eine Verifizierungsinformationstabelle

CREATETABLE IF NOT EXISTS checksums (
db char(64)NOT NULL,
tblchar(64) NOT NULL,
chunk intNOT NULL,
chunk_timefloat NULL,
chunk_indexvarchar(200) NULL,
lower_boundarytext NULL,
upper_boundarytext NULL,
this_crcchar(40) NOT NULL,
this_cntint NOT NULL,
master_crcchar(40) NULL,
master_cntint NULL,
tstimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(db, tbl, chunk),
INDEXts_db_tbl (ts, db, tbl)
)ENGINE=InnoDB;

(3) Bestimmen Sie den Primärschlüssel

Wenn kein Primärschlüssel für die Prüfsummenreparatur vorhanden ist, wird ein Sehr starke Auswirkungen auf die Leistung. Die wichtigste Einschränkung für die Reparatur von Datenprüfsummen ist, dass es sich um den Primärschlüssel handelt und es keinen Primärschlüssel oder eindeutigen Index gibt, was zu einer erfolglosen Reparatur führt.

Primärschlüssel-Beurteilungsaussage:

SELECTDISTINCT CONCAT(t.table_schema,'.',t.table_name) astbl,t.engine,IF(ISNULL(c.constraint_name),'NOPK','') AS nopk,
IF(s.index_type ='FULLTEXT','FULLTEXT','') as ftidx,IF(s.index_type = 'SPATIAL','SPATIAL','') asgisidx FROM information_schema.tables AS 
t LEFT JOINinformation_schema.key_column_usage AS c ON (t.table_schema =c.constraint_schema AND t.table_name = c.table_name 
AND c.constraint_name ='PRIMARY')LEFT JOIN information_schema.statistics AS s ON (t.table_schema =s.table_schema 
AND t.table_name = s.table_name AND s.index_type IN('FULLTEXT','SPATIAL')) WHERE t.table_schema NOT IN('information_schema','performance_schema','mysql') AND t.table_type = 'BASETABLE' 
AND (t.engine <> &#39;InnoDB&#39; OR c.constraint_name IS NULL ORs.index_type IN (&#39;FULLTEXT&#39;,&#39;SPATIAL&#39;)) ORDER BY t.table_schema,t.table_name;

(4) Master-Slave-Datenüberprüfung

Die Master-Slave-Datenüberprüfung wird mithilfe von pt-table-checksum implementiert, was erforderlich ist Wird in der Hauptbibliothek implementiert und ausgeführt. Die Ausführungsüberprüfung wird durch Parameter gesteuert, um die gesamte Datenbank und alle Tabellen oder nur die Kerntabelle zu überprüfen.

Beispiel für einen Prüfbefehl:

./pt-table-checksum--nocheck-binlog-format --nocheck-plan --nocheck-replication-filters--replicate=test.checksums --databases=db1--tables=tb1 -h 192.168.XXX.XX -P 3306-u'hangxing' -p'PASSOWRD' --recursion-method="processlist"

Parsing:

--no-check-binlog-format Den kopierten Binlog-Modus nicht überprüfen.

--nocheck-replication-filters Replikationsfilter nicht überprüfen, es wird empfohlen, sie zu aktivieren.

--replicate=test.checksums Die Prüfergebnisse werden in die Prüfsummentabelle der Testbibliothek geschrieben.

--databases=db1 --tables=tb1 Überprüfen Sie die Tabelle tb1 in der Datenbank db1. Wenn keine Parameter vorhanden sind, überprüfen Sie die gesamte Datenbanktabelle.

-h 192.168.XXX.XX -P 3306 IP-Adresse der Hauptbibliothek und 3306-Port.

-u'hangxing' -p'PASSOWRD' Kontopasswort überprüfen.

--recursion-method="processlist" Verwenden Sie die Methode „processlist“, um Slave-Bibliotheken zu erkennen.

Ausgabeergebnis nach der Ausführung:

TS   ERRORS      DIFFS      ROWS  CHUNKS  SKIPPED   TIME  TABLE
03-23T15:29:17    0     1    30000       1       0  1.270 testhx1.testhx1

Analyse:

TS : Zeit zum Abschluss der Prüfung.

FEHLER: Die Anzahl der Fehler und Warnungen, die während der Prüfung aufgetreten sind.

DIFFS : 0 bedeutet konsistent, größer als 0 bedeutet inkonsistent. Es hängt hauptsächlich davon ab, ob in dieser Spalte inkonsistente Daten vorhanden sind.

ROWS : Die Anzahl der Zeilen in der Tabelle.

CHUNKS : Die Anzahl der in der Tabelle aufgeteilten Blöcke.

SKIPPED : Die Anzahl der Blöcke, die aufgrund von Fehlern oder Warnungen übersprungen werden sollen oder zu groß sind.

TIME : Ausführungszeit.

TABELLE : Der Name der Tabelle, die überprüft wird.

    上述输出关键看DIFFS列，结果为0说明数据一致，无需进行数据修复，如果不为0则需要继续开展数据一致性修复工作。上述语句执行后也会将详细的内容会写入test库的checksums表中，可以查看这个库表得到详细的数据校验信息，此表中信息内容格式举例如下：  

        主库的test.checksums中输出this_crc和master_crc，无不一致。

mysql> select * fromtest.checksums;
+---------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| db      | tbl    | chunk | chunk_time |chunk_index |lower_boundary | upper_boundary | this_crc | this_cnt |master_crc| master_cnt |ts|
+---------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| testhx1 | testhx1 |     1 |  0.003661 | NULL        | NULL   | NULL| cac6c46f| 4 | cac6c46f  |  4 | 2016-03-23 15:29:16 |
+---------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------

------------+

1 row in set (0.00 sec)

从库的test.checksums中输出this_crc和master_crc，不一致。

mysql>select * from checksums;
+---------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
|db      | tbl     | chunk | chunk_time | chunk_index |lower_boundary | upper_boundary | this_crc |this_cnt |master_crc | master_cnt|ts                |
+---------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
|testhx1 | testhx1 |     1 |   0.003661 | NULL    | NULL   | NULL  | 7c2e5f75|  5 | cac6c46f  |  4 | 2016-03-23 15:29:16 |
+---------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
1row in set (0.00 sec)

 

（5）主从数据修复

用pt-table-checksum工具确定确实存在数据不一致的情况下开始修复数据，数据修复使用工具pt-table-sync，内带校验功能，但前提是修复的表必须要有主键，这个工具也要在主库上执行。

方法1:语句量大的情况下将修复的语句导入到sql文件中，再直接导入执行

在主库用pt-table-sync打印出修复不一致数据的SQL，后将修复语句在从库执行。

举例：

pt-table-sync --print--sync-to-master h='SlaveIP',P=3306,u=hangxing,p='PASSWORD' --databases=db1--tables=tb1 > /tmp/repair.sql

方法2:语句量不大的情况下，将修复的语句print出来，再execute

举例：

打印数据修复语句

pt-table-sync--print --sync-to-master h='SlaveIP',P=3306,u=hangxing,p=' PASSWORD '--databases=testhx1 --tables=testhx1
DELETE FROM`testhx1`.`testhx1` WHERE `id`='11' LIMIT 1 /*percona-toolkit src_db:testhx1src_tbl:testhx1 src_dsn:P=3306,h=’MasterIP’,
p=...,u=checksums dst_db:testhx1dst_tbl:testhx1 dst_dsn:P=3306,h='SlaveIP',p=...,u=checksums lock:1transaction:1 changing_src:1 replicate:0 bidirectional:0 
pid:24745 user:hangxinghost:XXXXXXXXXX*/;
REPLACEINTO `testhx1`.`testhx1`(`name`, `age`, `id`) VALUES ('bobby', '6', '7')/*percona-toolkit 
src_db:testhx1 src_tbl:testhx1 src_dsn:P=3306,h=’MasterIP’,
p=...,u=hangxingdst_db:testhx1 dst_tbl:testhx1 dst_dsn:P=3306,h=’SlaveIP’,p=...,u=hangxinglock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 
pid:24745user:root host: XXXXXXXXXX */;REPLACEINTO `testhx1`.`testhx1`(`name`, `age`, `id`) VALUES ('lily', '5', '9')/*percona-toolkit 
src_db:testhx1 src_tbl:testhx1 src_dsn:P=3306,h=’MasterIP’,p=...,u=hangxing  
dst_db:testhx1 dst_tbl:testhx1dst_dsn:P=3306,h=’SlaveIP’,p=...,u=hangxing lock:1 transaction:1 changing_src:1replicate:0 bidirectional:0 
pid:24745 user:root host: XXXXXXXXXX */;

执行数据修复语句

pt-table-sync--execute --sync-to-master h='SlaveIP',P=3306,u=hangxing,p='PASSWORD'--databases=testhx1 --tables=testhx1

（6）再次校验

   上述修复完成之后，需要再次执行一次数据校验，确保数据成功修复，校验方法同（4）主从数据校验。

2.4 值得注意的点

（1）校验修复工作每月定期开展；

（2）主从复制架构在割接操作前后均需执行数据校验和修复工作；

（3）主从复制出现故障后要开展数据校验和修复工作；

（4）校验修复需在业务低谷期进行，CPU利用率超过60%时不建议做数据校验和修复；

（5）校验和修复必须在主库进行；

（6）数据库的表要有主键，否则校验效率极差，并且修复不成功。

 

3.数据一致性校验和修复的自动化实现

理解上述方法后，我们可以顺利完成主从复制数据一致性的校验和修复工作，但是这项工作在MySQL主从复制架构维护中开展频率较高，包括定期和各种不定期的情况，每次都手工开展耗时耗力，并且容易出现人为错误及隐患，因此，我们考虑将这项工作通过脚本实现自动化。

3.1前提准备

创建校验账号，创建校验结果输出表，配置两台主机的ssh免密码登录。

3.2自动化实现

(1)部署自动化脚本和定时任务

理解数据一致性校验和修复的全部原理和详细步骤，将其转化为多个自动化脚本，分别部署在主从库上，每月定期执行可通过在主库制定crontab定时任务调用主从库脚本实现，不定期执行可通过手动调用主从库部署的自动化脚本来实现。

（2）自动化脚本实现步骤

a.将DB相关信息赋予对应参数，如账户密码、IP、端口、常用指令等等

dbuser=XXXX
dbpasswd="XXXXX"
port=3306
mysql_commend="mysql-u${dbuser} -p${dbpasswd} -P${port}"
master_ip=XXXXX
slave_ip=XXXXX
password="XXXXX"
date=`date+%Y%m%d`
logfile="XXXXX"
hostname=`XXXXX`

b.检查ssh免密码登录是否成功；

ssh_status=`XXXXX`
    if [ $ssh_status != $hostname ]; then
    echo -e "\nthe ssh should berepair" >$logfile
    exit
else
    echo -e "\nthe ssh is ok">$logfile
fi

c．脚本实现准备工作：包括账号密码的创建、建立校验结果输出表，代码可参见第2小节；

d．将主库的脚本执行校验和主键判断写成联合SQL语句，实现剔除无主键表的所有表的自动数据校验，并将结果存入所建表中；

selectXXXXX NOT IN XXXXX

e．从库部署检查校验结果输出表的脚本，主库执行d后自动登录从库调用这个脚本，实现对从库上输出表中校验字段的对比如master_crc 和 this_crc，找到数据不一致的表，并且通过执行调用修复工具的指令实现不一致数据修复语句的print；

master_cnt<> this_cnt OR master_crc <> this_crc OR isnull(master_crc)<> isnull(this_crc))

f．print结果自动存储从库的某个路径文件下；

intooutfile '/tmp/execute_sql.sh'

g．主库自动登录从库scp获取语句修复文件；

scp/tmp/execute_sql.sh root@$master_ip:/tmp/execute_sql.sh

h．主库上自动执行修复语句；

sh/tmp/execute_sql.sh

       i.清理掉各个中间文件，中间表等

       上述内容记录了该项工作的自动化实现思路及部分实现要点，自动化便是通过在这个思路的基础上编写主从库部署的脚本来实现，目前已亲测成功，已实现自动化的数据校验和修复，说明上述思路正确。

4.结语

    本文分享了MySQL复制数据一致性校验和修复的详细步骤及其自动化实现思路和方法，对MySQL复制架构运维中该项工作的实施及其自动化具有较好的借鉴意义。

Das Obige ist die Methode zur Überprüfung und Reparatur der MySQL-Master-Slave-Replikationsdaten sowie die automatische Implementierung. Weitere verwandte Inhalte finden Sie auf der chinesischen PHP-Website (www.php.cn)!