MySQL マスター/スレーブ レプリケーション データの整合性チェックと修復方法と自動実装

1. はじめに

「MySQL マスター/スレーブ レプリケーション」テクノロジーは、一般的な 1 マスター/スレーブ レプリケーション アーキテクチャ、keepalived + MySQL デュアル マスター (マスター/スレーブ) など、インターネット業界の一般的な高可用性アーキテクチャで広く使用されています。 ) レプリケーション アーキテクチャ、MHA + 1 つのマスターと 2 つのスレーブ レプリケーション アーキテクチャなどはすべて MySQL のマスター/スレーブ レプリケーション テクノロジを適用します。ただし、マスター/スレーブ レプリケーションは binlog に基づく論理レプリケーションであるため、レプリケートされたデータの不整合のリスクは避けられません。このリスクは、ユーザー データ アクセスの不整合のリスクを引き起こすだけでなく、後続のレプリケーションで 1032 エラーや 1062 エラーの原因にもなります。これは、レプリケーション アーキテクチャの停滞という隠れた危険を引き起こす可能性があります。この問題を時間内に発見して解決するには、マスターとスレーブのレプリケーション データの整合性の検証と修復作業を定期的または不定期に実行する必要があります。これをどのように達成するか。仕事？この作業を自動化するにはどうすればよいでしょうか?これらの質問について調べてみましょう。

2. データ整合性チェックサム修復方法

マスター/スレーブ レプリケーションのデータ整合性チェックサム修復を実現するには、まず、percona 社の pt-table-checksum と pt- という 2 つの一般的なツールをお勧めします。前者はマスター/スレーブ複製データの整合性を検証するために使用され、後者はデータを修復してデータの整合性を復元するために使用されます。

2.1 動作原理

pt-table-checksum は、SQL を通じてメイン データベース内のデータ ブロックの検証を実行し、同じステートメントをスレーブ データベースに送信し、スレーブ データベース上のデータ ブロックの検証を計算します。最後に、マスターとスレーブのライブラリ内の同じブロックのチェック値を比較して、マスターとスレーブのデータに矛盾があるかどうかを特定します。

pt-table-sync は、マスター/スレーブ レプリケーション データの不整合を修復して最終的に整合性を保つために使用されます。また、二重書き込みまたは多重書き込みを使用して複数のインスタンスまたは複数の無関係なデータベース インスタンスを修復するために使用することもできます。一貫性のある。同時にpt-table-checksumの検証機能も内部に統合しており、検証しながら修復したり、pt-table-checksumの計算結果に基づいて修復したりすることが可能です。

2.2 ダウンロード方法

これら 2 つのツールは、オンライン ダウンロード アドレス: https://www.percona.com/downloads/percona-toolkit/2.2.2/ の percona-toolkit に含まれています。

デバイスに直接ダウンロードする手順は次のとおりです。ダウンロード後、解凍して使用します: wget https://www.percona.com/downloads/percona-toolkit/2.2.2/percona-toolkit-2.2.2。 tar.gz

2.3 検証と修復方法

(1) メインデータベースに検証アカウントを作成

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) メインデータベースに検証情報テーブルを作成

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) 主キーを決定

主キーがない場合は、検証を実行します。データの検証と修復の最も重要な制約は、主キーまたは一意のインデックスがないと、修復が失敗します。

主キー判定文:

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) マスター/スレーブデータ検証

マスター/スレーブデータ検証は pt-table-checksum を使用して実装され、メインデータベース上で実行する必要があります。実行検証はパラメーターによって制御されます。データベース全体を検証する場合、テーブルはコア テーブルのみを検証します。

検証手順の例:

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

分析:

--no-check-binlog-format コピーされたものをチェックしませんバイナリログモード。

—nocheck-replication-filters レプリケーションフィルターをチェックしません。有効にすることをお勧めします。

--replicate=test.checksums チェック結果は、テスト ライブラリのチェックサム テーブルに書き込まれます。

--databases=db1 --tables=tb1 db1 データベース内の tb1 テーブルを確認します。パラメータがない場合は、データベース テーブル全体を確認します。

-h 192.168.XXX.XX -P 3306 メイン ライブラリの IP アドレスと 3306 ポート。

-u'hangxing' -p'PASSOWRD' アカウントのパスワードを確認します。

--recursion-method="processlist" processlist メソッドを使用してスレーブ ライブラリを検出します。結果 実行後の出力結果:

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

分析:

TS

: 検査が完了するまでの時間。

ERRORS

: チェック中に発生したエラーと警告の数。

DIFFS

: 0 は一貫性があることを意味し、0 より大きい場合は一貫性がないことを意味します。それは主に、この列に矛盾したデータがあるかどうかによって決まります。

ROWS

: テーブル内の行数。

CHUNKS

: テーブルに分割されたブロックの数。

SKIPPED

: エラーまたは警告、または大きすぎるためにスキップするブロックの数。

TIME

: 実行時間。

TABLE

: チェックされるテーブルの名前。

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

上記は、MySQL マスター/スレーブ レプリケーション データの整合性チェックと修復方法、および自動実装です。その他の関連コンテンツについては、PHP 中国語 Web サイト (www.php.cn) に注目してください。