MySQL 마스터-슬레이브 복제 데이터 일관성 확인 및 복구 방법 및 자동화 구현

1. 소개

"MySQL 마스터-슬레이브 복제" 기술은 일반적인 단일 마스터-슬레이브 복제 아키텍처, Keepalived + MySQL 듀얼 등 인터넷 업계의 일반적인 고가용성 아키텍처에서 널리 사용됩니다. -master(마스터-슬레이브) 복제 아키텍처, MHA + 1개의 마스터와 2개의 슬레이브 복제 아키텍처 등은 모두 MySQL 마스터-슬레이브 복제 기술을 적용합니다. 그러나 마스터-슬레이브 복제는 binlog 기반의 논리적 복제이기 때문에 복제된 데이터의 불일치 위험은 불가피하다. 이러한 위험은 사용자 데이터 액세스에 불일치를 초래할 뿐만 아니라 후속 복제에서도 1032, 1062 오류로 이어질 수 있다. 복제 아키텍처에 정체라는 숨겨진 위험이 발생할 수 있습니다. 이 문제를 적시에 발견하고 해결하려면 마스터-슬레이브 복제 데이터 일관성 확인 및 복구 작업을 정기적으로 또는 비정기적으로 수행해야 하는데 이 작업을 어떻게 수행할 수 있습니까? 이 작업을 자동화하는 방법은 무엇입니까? 이러한 질문을 살펴보겠습니다.

2. 데이터 일관성 체크섬 복구 방법

마스터-슬레이브 복제 데이터 일관성 체크섬 복구를 위해서는 먼저 널리 사용되는 두 가지 도구를 각각 권장합니다. Percona의 pt입니다. -table-checksum 및 pt-table-sync. 전자는 마스터-슬레이브 복제 데이터의 일관성을 확인하는 데 사용되고, 후자는 데이터를 복구하고 일관성을 복원하는 데 사용됩니다.

2.1 작동 원리

pt-table-checksum은 SQL을 통해 기본 데이터베이스에서 데이터 블록 검증을 수행한 후 동일한 명령문을 슬레이브 데이터베이스로 전송합니다. , 슬레이브 데이터베이스의 데이터 블록의 체크섬을 계산하고 마지막으로 마스터-슬레이브 데이터베이스의 동일한 블록의 체크섬을 비교하여 마스터-슬레이브 데이터가 일치하지 않는지 확인합니다.

pt-table-sync는 마스터-슬레이브 복제 데이터의 불일치를 복구하여 결과적으로 일관성을 유지하는 데 사용됩니다. 또한 이중 쓰기 또는 다중 쓰기를 적용하는 여러 인스턴스 또는 관련되지 않은 여러 데이터베이스 인스턴스를 구현할 수도 있습니다. 일관성을 유지하도록 수정되었습니다. 동시에 내부적으로 pt-table-checksum의 검증 기능도 통합하여 검증 중에 복구하거나 pt-table-checksum의 계산 결과를 기반으로 복구할 수 있습니다.

2.2 다운로드 방법

이 두 도구는 percona-toolkit에 포함되어 있습니다. 온라인 다운로드 주소: https://www.percona.com/downloads/ percona-toolkit /2.2.2/.

기기에 직접 다운로드하는 방법은 다운로드 후 압축을 풀어서 사용하는 방법입니다: 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) 기본 키 결정

검증 및 복구를 위한 기본 키가 없으면 성능에 미치는 영향이 매우 심각합니다. 데이터 확인 및 복구에 있어 가장 중요한 제약 조건은 기본 키 또는 고유 인덱스가 없으면 복구가 실패한다는 것입니다.

1차 핵심 판단문:

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 데이터 검증

Master-slave 데이터 검증은 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 복사된 binlog 모드를 확인하지 않습니다.

--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 중국어 홈페이지(www.php.cn)를 참고해주세요!