欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql主从同步-percona-toolkit工具的使用整理

程序员文章站 2022-06-20 22:55:08
在mysql工作中接触最多的就是mysql replication,mysql在复制方面还是会有一些常规问题,比如主库宕机或者从库宕机有可能会导致复制中断,通常需要进行人为修复,或者很多时候需要把一...

mysql工作中接触最多的就是mysql replication,mysql在复制方面还是会有一些常规问题,比如主库宕机或者从库宕机有可能会导致复制中断,通常需要进行人为修复,或者很多时候需要把一个从库提升为主库,但对从库和主库的数据一致性不能保证一样。这种情况下就需要使用percona-toolkit工具的pt-table-checksum来检查主从数据的一致性;如果发现不一致的数据,可以通过pt-table-sync修复;还可以通过pt-heartbeat监控主从复制延迟。当然如果数据量小,slave只是当做一个备份使用,那么出现数据不一致完全可以重做,或者通过其他方法解决。如果数据量非常大,重做就是非常蛋碎的一件事情了。比如说,线上做了主从同步环境,数据库在进行了迁移后,需要对mysql迁移(replication)后的数据一致性进行校验,但又不能对生产环境使用造成影响,pt-table-checksum成为了绝佳也是唯一的检查工具。

percona-toolkit介绍

percona-toolkit是一组高级命令行工具的集合,用来执行各种通过手工执行非常复杂和麻烦的mysql和任务,这些任务包括:

1)检查master和slave数据的一致性

2)有效地对记录进行归档

3)查找重复的索引

4)对服务器信息进行汇总

5)分析来自日志和tcpdump的查询

6)当系统出问题的时候收集重要的系统信息

percona-toolkit源自maatkit和aspersa工具,这两个工具是管理mysql的最有名的工具。不过,现在maatkit工具已经不维护了,所以以后推荐还是使用percona-toolkit工具!

这些工具主要包括开发、性能、配置、监控、复制、系统、实用六大类,作为一个优秀的dba,里面有的工具非常有用,如果能掌握并加以灵活应用,将能极大的提高工作效率。

percona-toolkit工具中最主要的三个组件分别是:

1)pt-table-checksum 负责监测mysql主从数据一致性

2)pt-table-sync 负责当主从数据不一致时修复数据,让它们保存数据的一致性

3)pt-heartbeat 负责监控mysql主从同步延迟

下面就对这三个组件的使用做一记录,当然percona-toolkit工具也有很多其他组件,后面会一一说明。

percona-toolkit工具安装(建议主库和从库服务器上都安装)

软件下载并在主库服务器上安装[百度云盘下载地址:https://pan.baidu.com/s/1bp1oogf (提取密码:y462)]

[root@master-server src]# wgethttps://www.percona.com/downloads/percona-toolkit/2.2.7/rpm/percona-toolkit-2.2.7-1.noarch.rpm

[root@master-server src]# rpm -ivh percona-toolkit-2.2.7-1.noarch.rpm //安装后,percona-toolkit工具的各个组件命令就有有了(输入ht-,按tab键就会显示)

安装该工具依赖的软件包

[root@master-server src]# yum install perl-io-socket-ssl perl-dbd-mysql perl-time-hires perl perl-dbi -y

一、pt-table-checksum使用梳理

pt-table-checksum 是 percona-toolkit的组件之一,用于检测mysql主、从库的数据是否一致。其原理是在主库执行基于statement的sql语句来生成主库数据块的checksum,把相同的sql语句传递到从库执行,并在从库上计算相同数据块的checksum,最后,比较主从库上相同数据块的checksum值,由此判断主从数据是否一致。检测过程根据唯一索引将表按row切分为块(chunk),以为单位计算,可以避免锁表。检测时会自动判断复制延迟、 master的负载, 超过阀值后会自动将检测暂停,减小对线上服务的影响。

pt-table-checksum 默认情况下可以应对绝大部分场景,官方说,即使上千个库、上万亿的行,它依然可以很好的工作,这源自于设计很简单,一次检查一个表,不需要太多的内存和多余的操作;必要时,pt-table-checksum 会根据服务器负载动态改变 chunk 大小,减少从库的延迟。

为了减少对数据库的干预,pt-table-checksum还会自动侦测并连接到从库,当然如果失败,可以指定--recursion-method选项来告诉从库在哪里。它的易用性还体现在,复制若有延迟,在从库 checksum 会暂停直到赶上主库的计算时间点(也通过选项--设定一个可容忍的延迟最大值,超过这个值也认为不一致)。

为了保证主数据库服务的安全,该工具实现了许多保护措施:

1)自动设置 innodb_lock_wait_timeout 为1s,避免引起

2)默认当数据库有25个以上的并发查询时,pt-table-checksum会暂停。可以设置 --max-load 选项来设置这个阀值

3)当用 ctrl+c 停止任务后,工具会正常的完成当前 chunk 检测,下次使用 --resume 选项启动可以恢复继续下一个 chunk

pt-table-checksum [options] [dsn]

pt-table-checksum:在主(master)上通过执行校验的查询对复制的一致性进行检查,对比主从的校验值,从而产生结果。dsn指向的是主的地址,该工具的退出状态不为零,如果发现有任何差别,或者如果出现任何警告或错误。注意:第一次运行的时候需要加上--create-replicate-table参数,生成checksums表!!如果不加这个参数,那么就需要在对应库下手工添加这张表了,表结构sql如下:

[plain]view plaincopy

createtablechecksums(

dbchar(64)notnull,

tblchar(64)notnull,

chunkintnotnull,

chunk_timefloatnull,

chunk_indexvarchar(200)null,

lower_boundarytextnull,

upper_boundarytextnull,

this_crcchar(40)notnull,

this_cntintnotnull,

master_crcchar(40)null,

master_cntintnull,

tstimestampnotnull,

primarykey(db,tbl,chunk),

indexts_db_tbl(ts,db,tbl)

)engine=innodb;

常用参数解释:

--nocheck-replication-filters:不检查复制过滤器,建议启用。后面可以用--databases来指定需要检查的数据库。

--no-check-binlog-format: 不检查复制的binlog模式,要是binlog模式是row,则会报错。

--replicate-check-only:只显示不同步的信息。

--replicate=:把checksum的信息写入到指定表中,建议直接写到被检查的数据库当中。

--databases=:指定需要被检查的数据库,多个则用逗号隔开。

--tables=:指定需要被检查的表,多个用逗号隔开

h=:master的地址

u=:用户名

p=:密码

p=:端口

最重要的一点就是:

要在主库上授权,能让主库ip访问。这一点不能忘记!(实验证明从库上可以不授权,但最好还是从库也授权)

注意:

1)根据测试,需要一个即能登录主库,也能登录从库的账号;

2)只能指定一个host,必须为主库的ip;

3)在检查时会向表加s锁;

4)运行之前需要从库的同步io和sql进程是yes状态。

例如:(本文例子中:192.168.1.101是主库ip,192.168.1.102是从库ip)

在主库执行授权(一定要对主库ip授权,授权的用户名和密码可以自行定义,不过要保证这个权限能同时登陆主库和从库)

mysql> grant select, process, super, replication slave,create,delete,insert,update on *.* to 'root'@'192.168.1.101' identified by '123456';

mysql> flush privileges;

在从库上执行授权

mysql> grant select, process, super, replication slave on *.* to 'root'@'192.168.1.101' identified by '123456';

mysql> flush privileges;

如下,在主库上执行的一个检查主从数据一致性的命令(别忘了第一次运行的时候需要添加--create-replicate-table参数,后续再运行时就不需要加了):

下面命令中的192.168.1.101是主库ip

检查的是huanqiu库下的haha表的数据(当然,命令中也可以不跟表,直接检查某整个库的数据;如下去掉--tables=haha表,直接检查huanqiu库的数据)

[root@master-server ~]#pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=huanqiu.checksums --create-replicate-table--databases=huanqiu --tables=haha h=192.168.1.101,u=root,p=123456,p=3306

[plain]view plaincopy

diffscannotbedetectedbecausenoslaveswerefound.pleasereadthe--recursion-methoddocumentationforinformation.

tserrorsdiffsrowschunksskippedtimetable

01-08t04:04:54004100.009huanqiu.haha

上面有报错:

diffs cannot be detected because no slaves were found. please read the --recursion-method documentation for information

上面的提示信息很清楚,因为找不到从,所以执行失败,提示用参数--recursion-method 可以指定模式解决。

其实是因为从库的slave关闭了。

在主库上执行:

[plain]view plaincopy

mysql>showprocesslist;

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

|id|user|host|db|command|time|state|info|

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

|10|root|localhost|null|query|0|init|showprocesslist|

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

发现没有slave在运行。

在从库上开启slave

mysql> start slave;

mysql> show slave status\g;

再在主库上执行:

[plain]view plaincopy

mysql>showprocesslist;

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

|id|user|host|db|command|time|state|info|

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

|10|root|localhost|null|query|0|init|showprocesslist|

|18|slave|192.168.1.102:37115|null|binlogdump|5|masterhassentallbinlogtoslave;waitingforbinlogtobeupdated|null|

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

发现已有slave在运行。

再次执行检查命令:

[root@master-server ~]#pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=huanqiu.checksums --databases=huanqiu --tables=haha h=192.168.1.101,u=root,p=123456,p=3306

[plain]view plaincopy

tserrorsdiffsrowschunksskippedtimetable

01-08t04:11:03004101.422huanqiu.haha

解释:

ts :完成检查的时间。

errors :检查时候发生错误和警告的数量。

diffs :0表示一致,1表示不一致。当指定--no-replicate-check时,会一直为0,当指定--replicate-check-only会显示不同的信息。

rows :表的行数。

chunks :被划分到表中的块的数目。

skipped :由于错误或警告或过大,则跳过块的数目。

time :执行的时间。

table :被检查的表名。

二、pt-table-sync用法梳理

如果通过pt-table-checksum 检查找到了不一致的数据表,那么如何同步数据呢?即如何修复mysql主从不一致的数据,让他们保持一致性呢?

这时候可以利用另外一个工具pt-table-sync。

使用方法:

pt-table-sync: 高效的同步mysql表之间的数据,他可以做单向和双向同步的表数据。他可以同步单个表,也可以同步整个库。它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。

假如上面检查数据时发现主从不一致

[root@master-server ~]#pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=huanqiu.checksums --databases=huanqiu --tables=haha h=192.168.1.101,u=root,p=123456,p=3306

[plain]view plaincopy

tserrorsdiffsrowschunksskippedtimetable

01-08t04:18:07014100.843huanqiu.haha

现在需要diffs为1可知主从数据不一致,需要修复!修复命令如下:

先master的ip,用户,密码,然后是slave的ip,用户,密码

[root@master-server ~]#pt-table-sync --replicate=huanqiu.checksums h=192.168.1.101,u=root,p=123456 h=192.168.1.102,u=root,p=123456 --print

[plain]view plaincopy

replaceinto`huanqiu`.`haha`(`id`,`name`)values('1','wangshibo')/*percona-toolkitsrc_db:huanqiusrc_tbl:hahasrc_dsn:h=192.168.1.101,p=...,u=rootdst_db:huanqiudst_tbl:hahadst_dsn:h=192.168.1.102,p=...,u=rootlock:1transaction:1changing_src:huanqiu.checksumsreplicate:huanqiu.checksumsbidirectional:0pid:23676user:roothost:master-server*/;

replaceinto`huanqiu`.`haha`(`id`,`name`)values('2','wangshikui')/*percona-toolkitsrc_db:huanqiusrc_tbl:hahasrc_dsn:h=192.168.1.101,p=...,u=rootdst_db:huanqiudst_tbl:hahadst_dsn:h=192.168.1.102,p=...,u=rootlock:1transaction:1changing_src:huanqiu.checksumsreplicate:huanqiu.checksumsbidirectional:0pid:23676user:roothost:master-server*/;

replaceinto`huanqiu`.`haha`(`id`,`name`)values('3','limeng')/*percona-toolkitsrc_db:huanqiusrc_tbl:hahasrc_dsn:h=192.168.1.101,p=...,u=rootdst_db:huanqiudst_tbl:hahadst_dsn:h=192.168.1.102,p=...,u=rootlock:1transaction:1changing_src:huanqiu.checksumsreplicate:huanqiu.checksumsbidirectional:0pid:23676user:roothost:master-server*/;

replaceinto`huanqiu`.`haha`(`id`,`name`)values('4','wanghi')/*percona-toolkitsrc_db:huanqiusrc_tbl:hahasrc_dsn:h=192.168.1.101,p=...,u=rootdst_db:huanqiudst_tbl:hahadst_dsn:h=192.168.1.102,p=...,u=rootlock:1transaction:1changing_src:huanqiu.checksumsreplicate:huanqiu.checksumsbidirectional:0pid:23676user:roothost:master-server*/;

参数解释:

--replicate= :指定通过pt-table-checksum得到的表,这2个工具差不多都会一直用。

--databases= : 指定执行同步的数据库。

--tables= :指定执行同步的表,多个用逗号隔开。

--sync-to-master :指定一个dsn,即从的ip,他会通过show processlist或show slave status 去自动的找主。

h= :服务器地址,命令里有2个ip,第一次出现的是master的地址,第2次是slave的地址。

u= :帐号。

p= :密码。

--print:打印,但不执行命令。

--execute:执行命令。

上面命令介绍完了,接下来开始执行修复:

通过(--print)打印出来了修复数据的sql语句,可以手动的在slave从库上执行,让他们数据保持一致性,这样比较麻烦!

可以直接在master主库上执行修复操作,通过--execute参数,如下:

[root@master-server ~]#pt-table-sync --replicate=huanqiu.checksums h=192.168.1.101,u=root,p=123456 h=192.168.1.102,u=root,p=123456--execute

如上修复后,再次检查,发现主从库数据已经一致了!

[root@master-server ~]#pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=huanqiu.checksums --databases=huanqiu --tables=haha h=192.168.1.101,u=root,p=123456,p=3306

[plain]view plaincopy

tserrorsdiffsrowschunksskippedtimetable

01-08t04:36:43004100.040huanqiu.haha

-----------------------------------------------------------------------------------------------------------------------

建议:

修复数据的时候,最好还是用--print打印出来的好,这样就可以知道那些数据有问题,可以人为的干预下。

不然直接执行了,出现问题之后更不好处理。总之还是在处理之前做好数据的备份工作。

注意:要是表中没有唯一索引或则主键则会报错:

can't make changes on the master because no unique index exists at /usr/local/bin/pt-table-sync line 10591.

-----------------------------------------------------------------------------------------------------------------------

为了确保主从数据的一致性,可以编写监控脚本,定时检查。当检查到主从数据不一致时,强制修复数据。

[root@master-server ~]# cat /root/pt_huanqiu.sh

[plain]view plaincopy

#!/bin/bash

num=$(/usr/bin/pt-table-checksum--nocheck-replication-filters--no-check-binlog-format--replicate=huanqiu.checksums--databases=huanqiuh=192.168.1.101,u=root,p=123456,p=3306|awk-f""'{print$3}'|sed-n'2p')

if[$num-eq1];then

/usr/bin/pt-table-sync--replicate=huanqiu.checksumsh=192.168.1.101,u=root,p=123456h=192.168.1.102,u=root,p=123456--print

/usr/bin/pt-table-sync--replicate=huanqiu.checksumsh=192.168.1.101,u=root,p=123456h=192.168.1.102,u=root,p=123456--execute

else

echo"dataisok"

fi

[root@master-server ~]# cat /root/pt_huanpc.sh

[plain]view plaincopy

#!/bin/bash

num=$(/usr/bin/pt-table-checksum--nocheck-replication-filters--no-check-binlog-format--replicate=huanpc.checksums--databases=huanpch=192.168.1.101,u=root,p=123456,p=3306|awk-f""'{print$3}'|sed-n'2p')

if[$num-eq1];then

/usr/bin/pt-table-sync--replicate=huanpc.checksumsh=192.168.1.101,u=root,p=123456h=192.168.1.102,u=root,p=123456--print

/usr/bin/pt-table-sync--replicate=huanpc.checksumsh=192.168.1.101,u=root,p=123456h=192.168.1.102,u=root,p=123456--execute

else

echo"dataisok"

fi

[root@master-server ~]# crontab -l

#检查主从huanqiu库数据一致性

* * * * * /bin/bash -x /root/pt_huanqiu.sh > /dev/null 2>&1

* * * * * sleep 10;/bin/bash -x /root/pt_huanqiu.sh > /dev/null 2>&1

* * * * * sleep 20;/bin/bash -x /root/pt_huanqiu.sh > /dev/null 2>&1

* * * * * sleep 30;/bin/bash -x /root/pt_huanqiu.sh > /dev/null 2>&1

* * * * * sleep 40;/bin/bash -x /root/pt_huanqiu.sh > /dev/null 2>&1

* * * * * sleep 50;/bin/bash -x /root/pt_huanqiu.sh > /dev/null 2>&1

#检查主从huanpc库数据一致性

* * * * * /bin/bash -x /root/root/pt_huanpc.sh > /dev/null 2>&1

* * * * * sleep 10;/bin/bash -x /root/pt_huanpc.sh > /dev/null 2>&1

* * * * * sleep 20;/bin/bash -x /root/pt_huanpc.sh > /dev/null 2>&1

* * * * * sleep 30;/bin/bash -x /root/pt_huanpc.sh > /dev/null 2>&1

* * * * * sleep 40;/bin/bash -x /root/pt_huanpc.sh > /dev/null 2>&1

* * * * * sleep 50;/bin/bash -x /root/pt_huanpc.sh > /dev/null 2>&1

-----------------------------------------------------------------------------------------------------------------------

最后总结:

pt-table-checksum和pt-table-sync工具很给力,工作中常常在使用。注意使用该工具需要授权,一般select, process, super, replication slave等权限就已经足够了。

-----------------------------------------------------------------------------------------------------------------------

另外说一个问题:

在上面的操作中,在主库里添加pt-table-checksum检查的权限(从库可以不授权)后,进行数据一致性检查操作,会在操作的库(实例中是huanqiu、huanpc)下产生一个checksums表!

这张checksums表是pt-table-checksum检查过程中产生的。这张表一旦产生了,默认是删除不了的,并且这张表所在的库也默认删除不了,删除后过一会儿就又会出来。

[plain]view plaincopy

mysql>usehuanqiu;

readingtableinformationforcompletionoftableandcolumnnames

youcanturnoffthisfeaturetogetaquickerstartupwith-a

databasechanged

mysql>showtables;

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

|tables_in_huanqiu|

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

|checksums|

|haha|

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

2rowsinset(0.00sec)

mysql>droptablechecksums;

queryok,0rowsaffected(0.01sec)

mysql>showtables;

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

|tables_in_huanqiu|

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

|haha|

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

1rowinset(0.00sec)

mysql>showtables;//过一段时间再次查看,发现checksums表还在

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

|tables_in_huanqiu|

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

|checksums|

|haha|

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

2rowsinset(0.00sec)

不仅这张表删除不了,这张表所在的库也删除不了,删除后过一会儿就是自动生成。

mysql>dropdatabasehuanqiu;

queryok,1rowaffected(0.01sec)

mysql>dropdatabasehuanpc;

queryok,1rowaffected(0.02sec)

mysql>showdatabases;

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

|database|

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

|information_schema|

|huanqiu|

|mysql|

|performance_schema|

|test|

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

5rowsinset(0.00sec)

mysql>dropdatabasehuanqiu;

queryok,1rowaffected(0.01sec)

mysql>showdatabases;

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

|database|

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

|information_schema|

|huanpc|

|huanqiu|

|mysql|

|performance_schema|

|test|

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

6rowsinset(0.00sec)

要想删除的话,一定要先把pt-table-checksum检查前添加的权限收回!

[plain]view plaincopy

mysql>showgrantsfor'root'@'192.168.1.101';

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

|grantsforroot@192.168.1.101|

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

|grantselect,insert,update,delete,create,process,super,replicationslaveon*.*to'root'@'192.168.1.101'identifiedbypassword'*6bb4837eb74329105ee4568dda7dc67ed2ca2ad9'|

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

1rowinset(0.00sec)

mysql>revokeselect,insert,update,delete,create,process,super,replicationslaveon*.*from'root'@'192.168.1.101';

queryok,0rowsaffected(0.01sec)

mysql>showgrantsfor'root'@'192.168.1.101';

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

|grantsforroot@192.168.1.101|

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

|grantusageon*.*to'root'@'192.168.1.101'identifiedbypassword'*6bb4837eb74329105ee4568dda7dc67ed2ca2ad9'|

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

1rowinset(0.00sec)

mysql>selectuser,host,passwordfrommysql.user;

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

|user|host|password|

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

|root|localhost|*6bb4837eb74329105ee4568dda7dc67ed2ca2ad9|

|root|master-server||

|root|127.0.0.1||

|root|::1||

||localhost||

||master-server||

|root|192.168.1.101|*6bb4837eb74329105ee4568dda7dc67ed2ca2ad9|

|slave|192.168.1.102|*4f0ff134cc4c1a2872d972373a6aa86ca0a81872|

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

8rowsinset(0.00sec)

mysql>deletefrommysql.userwhereuser="root"andhost="192.168.1.101";//这一步其实不必操作,此步删除操作不能在上面revoke执行前进行,否则revoke回收权限失败!

queryok,1rowaffected(0.00sec)

mysql>selectuser,host,passwordfrommysql.user;

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

|user|host|password|

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

|root|localhost|*6bb4837eb74329105ee4568dda7dc67ed2ca2ad9|

|root|master-server||

|root|127.0.0.1||

|root|::1||

||localhost||

||master-server||

|slave|192.168.1.102|*4f0ff134cc4c1a2872d972373a6aa86ca0a81872|

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

7rowsinset(0.00sec)

权限删除后,就能成功删除checksums这张表和它所在的库了!

主库的checksums删除了,从库的这张表也会跟着删除

[plain]view plaincopy

mysql>usehuanpc;

databasechanged

mysql>showtables;

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

|tables_in_huanpc|

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

|checksums|

|heihei|

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

2rowsinset(0.00sec)

mysql>droptablechecksums;

queryok,0rowsaffected(0.01sec)

mysql>showtables;

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

|tables_in_huanpc|

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

|heihei|

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

1rowinset(0.01sec)

mysql>usehuanqiu;

readingtableinformationforcompletionoftableandcolumnnames

youcanturnoffthisfeaturetogetaquickerstartupwith-a

databasechanged

mysql>showtables;

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

|tables_in_huanqiu|

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

|checksums|

|haha|

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

2rowsinset(0.00sec)

mysql>droptablechecksums;

queryok,0rowsaffected(0.00sec)

mysql>showtables;

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

|tables_in_huanqiu|

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

|haha|

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

1rowinset(0.00sec)

也就是说,checksums表一旦产生,不仅这张表默认删除不了,连同它所在的库,要是想删除它们,只能如上操作先撤销权限。

pt-heartbeat监控mysql主从复制延迟整理