Méthode de vérification et de réparation de la cohérence des données de réplication maître-esclave MySQL et mise en œuvre automatisée

1. Introduction

La technologie de « réplication maître-esclave MySQL » est largement utilisée dans les architectures à haute disponibilité courantes de l'industrie Internet, telles que l'architecture de réplication commune maître-esclave, keepalived + MySQL dual. -L'architecture de réplication maître (maître-esclave), l'architecture de réplication MHA + un maître et deux esclaves, etc. appliquent toutes la technologie de réplication maître-esclave MySQL. Cependant, étant donné que la réplication maître-esclave est une réplication logique basée sur binlog, le risque d'incohérence dans les données répliquées est inévitable. Ce risque entraînera non seulement un risque d'accès incohérent aux données utilisateur, mais entraînera également des erreurs 1032 et 1062 lors de la réplication ultérieure. , ce qui entraînera un danger caché de stagnation dans l'architecture de réplication. , Afin de découvrir et de résoudre ce problème à temps, nous devons effectuer régulièrement ou irrégulièrement des travaux de vérification et de réparation de la cohérence des données de réplication maître-esclave, alors comment y parvenir. travail? Comment automatiser ce travail ? Explorons ces questions.

2. Méthode de réparation de la somme de contrôle de cohérence des données

Afin de réaliser la réparation de la somme de contrôle de cohérence des données de réplication maître-esclave, nous recommandons d'abord deux outils populaires, respectivement. Ils sont le point de Percona. -table-checksum et pt-table-sync Le premier est utilisé pour vérifier la cohérence des données de réplication maître-esclave, et le second est utilisé pour réparer les données et restaurer la cohérence des données.

2.1 Principe de fonctionnement

pt-table-checksum effectue une vérification des blocs de données dans la base de données principale via SQL, puis transmet la même instruction à la base de données esclave , et calcule la somme de contrôle des blocs de données sur la base de données esclave, et enfin compare les sommes de contrôle des mêmes blocs dans la base de données maître-esclave pour identifier si les données maître-esclave sont incohérentes.

pt-table-sync est utilisé pour réparer les incohérences dans les données de réplication maître-esclave afin qu'elles soient finalement cohérentes. Il peut également implémenter plusieurs instances ou plusieurs instances de base de données non liées qui appliquent une double écriture ou une multi-écriture. Réparé à la cohérence. Dans le même temps, il intègre également la fonction de vérification de pt-table-checksum en interne, qui peut être réparée lors de la vérification, ou réparée en fonction des résultats de calcul de pt-table-checksum.

2.2 Méthode de téléchargement

Ces deux outils sont inclus dans percona-toolkit, adresse de téléchargement en ligne : https://www.percona.com/downloads/ percona-toolkit /2.2.2/.

Les instructions pour télécharger directement sur l'appareil sont les suivantes Après le téléchargement, décompressez et utilisez : wget https://www.percona.com/downloads/percona-toolkit/2.2.2/percona-toolkit-. 2.2.2.tar .gz

2.3 Méthode de vérification et de réparation

(1) Créer un compte de vérification dans la base de données principale

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) Dans la base de données principale La bibliothèque crée une table d'informations de vérification

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) Déterminez la clé primaire

S'il n'y a pas de clé primaire pour la réparation de la somme de contrôle, elle aura un un impact très important sur les performances. La contrainte la plus importante pour la réparation de la somme de contrôle des données est qu'il s'agit de la clé primaire et qu'il n'y a pas de clé primaire ou d'index unique, ce qui entraînera un échec de la réparation.

Déclaration de jugement de clé primaire :

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) Vérification des données maître-esclave

La vérification des données maître-esclave est mise en œuvre à l'aide de pt-table-checksum, qui doit être implémenté dans la bibliothèque principale Exécuté le, la vérification de l'exécution est contrôlée par des paramètres pour vérifier l'intégralité de la base de données et toutes les tables ou uniquement la table principale.

Exemple de commande check :

./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"

Analyse :

--no-check-binlog-format Ne vérifie pas le mode binlog copié.

--nocheck-replication-filters Ne vérifiez pas les filtres de réplication, il est recommandé de les activer.

--replicate=test.checksums Les résultats du contrôle sont écrits dans le tableau des sommes de contrôle de la bibliothèque de tests.

--databases=db1 --tables=tb1 Vérifiez la table tb1 dans la base de données db1. S'il n'y a pas de paramètres, vérifiez l'intégralité de la table de la base de données.

-h 192.168.XXX.XX -P 3306 Adresse IP de la bibliothèque principale et port 3306.

-u'hangxing' -p'PASSOWRD' Vérifiez le mot de passe du compte.

--recursion-method="processlist" Utilisez la méthode processlist pour découvrir les bibliothèques esclaves.

Résultat de sortie après exécution :

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

Analyse :

TS  : Il est temps de terminer la vérification.

ERREURS : Le nombre d'erreurs et d'avertissements survenus lors de la vérification.

DIFFS : 0 signifie cohérent, supérieur à 0 signifie incohérent. Cela dépend principalement de la présence ou non de données incohérentes dans cette colonne.

ROWS : Le nombre de lignes dans le tableau.

CHUNKS : Le nombre de blocs divisés dans le tableau.

SKIPPED  : Le nombre de blocs à ignorer en raison d'erreurs ou d'avertissements ou trop grand.

TIME : temps d'exécution.

TABLE  : Le nom de la table en cours de vérification.

    上述输出关键看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复制架构运维中该项工作的实施及其自动化具有较好的借鉴意义。

Ce qui précède est la méthode de vérification et de réparation de la cohérence des données de réplication maître-esclave MySQL et la mise en œuvre automatique. Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (www.php.cn) !