PT工具使用介绍

PT工具的使用

目录

• PT工具的使用
  • pt-online-schema-change
    • 在线添加字段
    • 在线添加索引
    • 在线修改表字段
  • pt-archiver
    • 数据清理
    • 数据归档
    • 参数说明：
    • pt-archiverBug不会迁移max(id)那条数据的解决方法：
    • 删除老数据(单独的删数据操作不用指定字符集)
    • 复制数据到其他mysql实例，且不删除source的数据(指定字符集)：
    • 复制数据到其他mysql实例，并删source上的旧数据(指定字符集)：
    • 复制数据到其他mysql实例，不删除source数据，但是使用批量插入dest上新的数据(指定字符集)：
    • 导出数据到文件：
    • 导出数据到文件并删除数据库的相关行：
  • pt-query-digest 慢日志分析工具
    • 慢日志分析
    • 慢查询分析需要关注的点
  • pt-heartbeat 延迟分析工具
    • 延迟获取
  • pt-table-checksum 数据一致性校验
  • pt-slave-restart
• 跳过之前需要设置复制并行为0
• 跳过之后设置复制并行为8
  • 这里需要注意，由于开启了双主GTID复制,show master status和Executed_gtid_set会有两个GTID值，其中一个为自己的GTID，另一个为主的GTID。
  • 设置的时候只要把主库的GTID写进“”即可。自身的GTID不需要指明，但如果使用set gtid_purged的方式跳过，是需要可以指明两个GTID的
  • 设置后，插入一个空事务进行更新GTID。
  • 官方手册规定：精确的指定过一次GTID，并产生一次事务后，后面必须再次指定一次gtid_next的模式（是模式，不是精确值，官方手册这里没有写清楚）
  • “After this variable has been set to UUID:NUMBER, and a transaction has been committed or rolled back, an explicit SET GTID_NEXT statement must again be issued before any other statement.”
• 主库创建一张 t1 表：
• 主库插入两条数据到 t1 表，并且不写入 binlog 日志。
• 这时就会出现，主库有这两条数据，从库不存在着两条数据的情况。
• 主库删除刚刚插入的两条数据 2 3
• 此时，主从 1032 错误出现
• 192.168.3.14:3306 这是一台主库，环境不匹配！
• 前台运行
• 再次检查主从状态
• 主库：
• 从库插入一条数据，并且不写入binlog：
• 主库再次插入一条主键为3的数据：
• 主从报 1062 错误：
• 再次检查主从状态

pt-online-schema-change

工作原理：
1、如果存在外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理。没有使用 --alter-foreign-keys-method=rebuild_constraints 指定特定的值，该工具不予执行
2、创建一个和源表表结构相同的表（table_new），执行alter修改临时表结构
3、在原表上创建三个触发器，insert，delete，udpate对应的触发器，（用于copy数据的过程中，在原表的更新操作更新到新表）
4、从原表拷贝数据到新表，拷贝过程中在原表的写操作都会更新到新建的临时表
5、修改外键相关的子表，根据修改后的数据，修改外键关联的子表
6、rename源数据表为old，把新表rename为源表明，并将old表删除
7、删除触发器

执行条件：
1、操作的表必须有主键或者唯一索引，否则报错
2、该表不能定义触发器，否则报错

好处:
降低主从延时的风险
可以限速、限资源，避免操作时MySQL负载过高

建议:
在业务低峰期做，将影响降到最低

用法介绍：

pt-online-schema-change --host=ip --port=3306 --user=username --password='password' D=db_name,t=table_name --alter="modify order_id bigint(20) COMMENT '订单id';" --critical-load="Threads_running=200" --sleep=1 --charset=utf8mb4 --check-slave-lag="192.168.1.2,192.168.1.3" --check-interval=1 --execute

--dry-run 创建并修改新表，但不创建触发器，也不复制表，或者替换原表，与--execute互斥
--execute 这个参数的作用和前面工作原理的介绍的一样，会建立触发器，来保证最新变更的数据会影响至新表。注意：如果不加这个参数，这个工具会在执行一些检查后退出
--critical-load 每次chunk操作前后，会根据show global status统计指定的状态量的变化，默认是统计Thread_running。目的是为了安全，防止原始表上的触发器引起负载过高。这也是为了防止在线DDL对线上的影响。超过设置的阀值，就会终止操作，在线DDL就会中断。提示的异常如上报错信息
--charset=utf8 连接到MySQL后运行SET NAMES UTF8
--check-slave-lag 检查主从延迟
--check-replication-filters 检查复制中是否设置了过滤条件，如果设置了，程序将退出
--nocheck-replication-filters 不检查复制中是否设置了过滤条件
--set-vars 设置mysql的变量值
--sleep 每个chunk导入后与下一次chunk导入开始前sleep一会，sleep时间越长，对于磁盘IO的冲击就越小
--[no]drop-old-table rename新表后drop旧表，可以no-xxx来保留旧表
--[no]drop-new-table 如果复制原表失败则删除新表； 也可以no-xxx来保留新表
--chunk-size chunk的行数，默认1000
--chunk-index-columns 有复合索引的时候，指定索引列
--critical-load 默认Threads_running=50； 每次chunk执行后会自动用SHOW GLOBAL STATUS检查负载情况，如果超过阈值则放弃；
--execute 执行操作 与 --dry-run互斥
--force 强制运行，可能打破外键约束
--skip-check-slave-lag 检查SLAVE的时候，指定该SLAVE跳过；
--print 将会显示工具执行的命令
--null-to-not-null 修改允许null值为not null
--preserve-triggers 保留原表的触发器，不删除
--max-lag 默认1s， 如果主从延时的时间超过这个值，则复制会暂停"--check-interval"秒时间；然后再检查，直到主从延时小于该值；如果指定了"--check-slave-lag"，则只会检查指定slave延时，而不是检查所有slave;如果有任何SLAVE stop了，那么工具会一直等待下去；每次停止的时候都会打印报告
--ask-pass 连接的时候会要求提供密码
--max-load 选项定义一个阀值，在每次chunk操作后，查看show global status状态值是否高于指定的阀值。该参数接受一个mysql status状态变量以及一个阀值，
如果没有给定阀值，则定义一个阀值为为高于当前值的20%。
注意这个参数不会像--critical-load终止操作，而只是暂停操作。当status值低于阀值时，则继续往下操作。
是暂停还是终止操作这是--max-load和--critical-load的差别。

参数值为列表形式，可以指定show global status出现的状态值。比如，Thread_connect 等等。
格式如下：--critical-load="Threads_running=200"  或者--critical-load="Threads_running:200"。

在线添加字段

原sql:
alter table test add column library_type VARCHAR(10);

pt工具：
pt-online-schema-change -h localhost  -P 3306 -u root -p 'pass' -S /mysqldata/mysql/data/mysql3306.sock --alter "add column copyright_status VARCHAR(10);" D=dbname,t=table_name --execute --print --no-check-replication-filters --charset=utf8mb4 --no-check-unique-key-change --max-load="Threads_running=30" --critical-load="Threads_running=50" --recursion-method=none;

pt-online-schema-change   -uroot -p'pass' --alter "add RELATED_COPYRIGHT varchar(512) comment '关联资源类型及对应版权ID'" D=tyqk_dispatch,t='tb_copyright_summary'    --execute

参数说明：

--no-drop-old-table   若增加该参数，则原表会重新命名为_table_name_old ,并且保留该表，不会删除。

在线添加索引

原sql:
ALTER TABLE test ADD INDEX idx_library_type (library_type);

pt工具：
pt-online-schema-change -h localhost  -P 3306 -u root -p 'pass' -S /mysqldata/mysql/data/mysql3306.sock --alter "ADD INDEX idx_copyright_status(copyright_status);" D=dbname,t=table_name --execute --print --no-check-replication-filters --charset=utf8mb4 --no-check-unique-key-change --max-load="Threads_running=30" --critical-load="Threads_running=50" --recursion-method=none;

在线修改表字段

原sql:
alter table test1 modify column id int(20) NOT NULL AUTO_INCREMENT COMMENT 'id';

pt工具：
pt-online-schema-change -h localhost  -P 3306 -u root -p 'root' --alter "modify column id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id';" D=dmdata,t=test1 --no-drop-old-table --execute --print --no-check-replication-filters --charset=utf8mb4 --no-check-unique-key-change --max-load="Threads_running=30" --critical-load="Threads_running=50" --recursion-method=none;

--alter-foreign-keys-method 参数说明

昨天晚上某个表A新加了一个字段，今早收到业务告警。最后从日志中发现类似如下报错(B表的外键指向了不存在的表_A_old)：
Cannot add or update a child row: a foreign key constraint fails (db.B, CONSTRAINT B_ibfk_2 FOREIGN KEY (A_id) REFERENCES _A_old (id))
至于为啥会有外键...这属于历史遗留问题。

第一反应，昨天加字段的时候Inception调用pt-osc出问题了，导致外键没有链接到新表上去。
既然pt-osc会有问题，那就老老实实直接删除外键，重加吧
alter table B drop foreign key B_ibfk_2,add constraint new_key FOREIGN KEY (A_id) REFERENCES A(id)
悲剧的是B表实在是太大，线上跑了一个小时也无果，于是Kill回滚。

回过头来研究为毛B表会指向利用pt-osc进行字段增加时的旧表_A_old呢？
先看看pt-osc关于外键的参数--alter-foreign-keys-method：

（1）auto
自动决定采用哪个方法，如果可以就采用rebuild_constraints，如果不可以就采用drop_swap
（2）rebuild_constraints
该方法采用alter table来drop并re-add链接新表的外键。除非相关的子表太大使得alter过程花费时间过长，一般都采用该方法。这里的花费时间是通过比较子表中的行数和该工具将原始表数据拷贝到新表中的拷贝速率来评估的，如果评估后发现子表中数据能够在少于--chunk-time的时间内alter完成，就会采用该方法。另外，因为在MySQL中alter table比外部拷贝数据的速率快很多，所以拷贝速率是按照--chunk-size-limit来决定的
因为MySQL的限制，外键在改表前后的名字会不一样，改表后新表中的外键名前会加一个下划线，同样，会自动的更改外键相应的索引名字
（3）drop_swap
该方法禁止外键检查(FOREIGN_KEY_CHECKS=0)，然后在rename新表之前就将原始表drop掉，这个方法更快而且不会被阻塞，但是风险比较大，风险有二：
在drop掉原始表和rename新表之间有一个时间差，在这段时间里这个表是不存在的，这会导致查询报错
如果rename新表时发生了错误，那问题就大了，因为原始表已经被drop掉了，只能呵呵了
（4）none
这个方法类似没有"swap"的drop_swap，原始表中的所有外键都会被指定到一个不存在的表上
因为原始表(database.tablename)会被rename为database.tablename_old然后drop掉。这种处理外键的方法可以让DBA在需要时取消该工具的这种内置功能

以下是我关于这些参数的测试结果（参数auto略过了）：
1.有子表的表A使用最安全的--alter-foreign-keys-method=rebuild_constraints来进行线上更新时，
在copy完父表之后，子表进行更改的方式是alter table B drop foreign key old_key,add constraint new_key FOREIGN KEY (A_id) REFERENCES A(id)。如果子表B比较大，或者A表有好几个子表，那么我还有使用pt-osc的必要么？（对线上的影响可能远远大于直接alter table A带来的影响）

2.有子表的表A使用--alter-foreign-keys-method=none来进行线上更新时，
在copy完父表之后，数据库是直接执行set FOREIGN_KEY_CHECKS=0，然后drop旧表_A_old，然后rename新表_A_new为A。然后就收工了。
所以子表B的外键指向的仍然是pt-osc运行过程中的那张原始父表_A_old
很显然，Inception针对pt-osc的默认配置就是使用--alter-foreign-keys-method=none。

3.有子表的表A使用--alter-foreign-keys-method=drop_swap来进行线上更新时,
在copy完父表之后，数据库是直接执行set FOREIGN_KEY_CHECKS=0，然后drop旧表_A_old，然后rename新表_A_new为_A_old,最后再rename为A。
这样弄完之后父子表的关系仍然存在。（在rename_A_new为_A_old之后，drop旧表_A_old产生的错误指向就被带回来了）
--------------------------------------------------------------------------------------解决方法-----------------------------------------------------------------------------------------------
既然rename表之后，子表的外键关系能跟着变，那么最后线上问题的修正方法也就有了：
现在子表B的外键指向是一个不存在的表_A_old，那么我把现在的A表rename成_A_old，外键关系联系上了之后再重新rename回A表，不就好了么：
alter table A rename _A_old;
alter table _A_old rename A;
经测试没问题，秒恢复正常，然后线上执行OK。

最后回过头来检查Inception关于pt-osc对外键的配置发现：
inception_osc_alter_foreign_keys_method的默认配置是none（即它调用pt-osc的默认参数为--alter-foreign-keys-method=none），所以出现了外键指向不存在的表。
在这里我将配置改成了drop_swap

--------------------------------------------------------------------------------------华丽丽的分割线-----------------------------------------------------------------------------------------------
最后的反省：
数据库有变更的时候，变更完成后需要对相关的业务进行检查。这次虽然我们业务告警系统也在变更之后立即告警了，但却当成“狼来了”给略过了，此处需向老板好好反省......
关于外键，一直都是抵制的。对于新表，新需求一直都禁止使用外键。但旧的系统，很难推动去改掉它。
以前对于外键的一些细节，总是想着反正不会用到，所以遇到这样的细节直接就跳过了。
现在看来，虽然可能一般不会碰到，但这些细节还是需要好好的去理解掌握。
本文地址:https://www.cnblogs.com/ajiangg/p/9850902.html

pt-archiver

pt-archiver 是 percona toolkit 工具集中的工具，主要用于

• 数据删除
• 数据归档
• 数据迁移等场景。

数据清理

删除数据可以使用两种方式：
大表推荐使用PT工具删除,如果表上有主键字段删除的效率可以更高。
方法一：
使用PT 工具删除：

format_time=`date +"%Y-%m-%d_%H:%M:%S"`
echo " #########################start at $format_time#######################"
pt-archiver --source h=localhost,D=db_name,t=table_name,u=root,p="pass"  --where 'toneid not in(select toneid from dbname.table_name)' --purge --limit=10000 --no-check-charset --txn-size 10000 --bulk-delete --statistics  --progress 10000 --skip-foreign-key-checks --primary-key-only
end_time=`date +"%Y-%m-%d_%H:%M:%S"`
echo " #########################end at $end_time#######################"

--limit=10000  10000条提交一次
--txn-size 10000
--bulk-delete 批量提交
--primary-key-only 

方法二：
编写脚本delete.sh，使用存储过程分批次提交删除

delimiter $$
drop procedure if exists proc_delete_data_tb_catalog_info;
create procedure proc_delete_data_tb_catalog_info()
begin
set sql_log_bin=0;
lp : loop
    delete from tb_rbtresult_info where toneid not in(select toneid from TB_VRBTCONTENTINFO_INFO) limit 5000;
    if row_count() < 5000 then
        leave lp;
    end if;
    select sleep(1);
end loop;
end $$
delimiter ;
后台执行：
nohup sh delete.sh > delete.log &

数据归档

pt-archiver --source h=127.0.0.1,P=3306,u=root,p='xxx',D=xxx,t=matomo_log_link_visit_action --charset 'UTF8' --dest h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=visit_action_4 --no-version-check  --where "idlink_va <= 4383363" --statistics --no-delete --bulk-insert --progress 5000 --limit=500 --txn-size=100 >> xxx--matomo_log_link_visit_action--visit_action_4.log &

参数说明：

**连接数据库的参数

常用参数

下面几个参数都是互斥的，只能选其一

    "--ignore"and "--replace" are mutually exclusive. 
    "--txn-size"and "--commit-each" are mutually exclusive.
    "--low-priority-insert"and "--delayed-insert" are mutually exclusive.
    "--share-lock"and "--for-update" are mutually exclusive.
    "--analyze"and "--optimize" are mutually exclusive.
    "--no-ascend"and "--no-delete" are mutually exclusive.

常用的参数：
    --limit10000       每次取1000行数据用pt-archive处理，Number of rows to fetch and archive per statement.
    --txn-size  1000   设置1000行为一个事务提交一次，Number of rows pertransaction.
    --where'id<3000'   设置操作条件
    --progress5000     每处理5000行输出一次处理信息
    --statistics       输出执行过程及最后的操作统计。（只要不加上--quiet，默认情况下pt-archive都会输出执行过程的）
    --charset=UTF8     指定字符集为UTF8
    --bulk-delete      批量删除source上的旧数据(例如每次1000行的批量删除操作)
    --bulk-insert      批量插入数据到dest主机 (看dest的general log发现它是通过在dest主机上LOAD DATA LOCAL INFILE插入数据的)
    --replace          将insert into 语句改成replace写入到dest库
    --sleep120         每次归档了limit个行记录后的休眠120秒（单位为秒）
    --file'/root/test.txt'
    --purge             删除source数据库的相关匹配记录
    --header            输入列名称到首行（和--file一起使用）
    --no-check-charset  不指定字符集
    --check-columns    检验dest和source的表结构是否一致，不一致自动拒绝执行（不加这个参数也行。默认就是执行检查的）
    --no-check-columns    不检验dest和source的表结构是否一致，不一致也执行（会导致dest上的无法与source匹配的列值被置为null或者0）
    --chekc-interval      默认1s检查一次
    --local            不把optimize或analyze操作写入到binlog里面（防止造成主从延迟巨大）
    --retries         超时或者出现死锁的话，pt-archiver进行重试的间隔（默认1s）
    --no-version-check   目前为止，发现部分pt工具对阿里云RDS操作必须加这个参数
    --analyze=ds      操作结束后，优化表空间（d表示dest，s表示source）
    默认情况下，pt-archiver操作结束后，不会对source、dest表执行analyze或optimize操作，因为这种操作费时间，并且需要你提前预估有足够的磁盘空间用于拷贝表。一般建议也是pt-archiver操作结束后，在业务低谷手动执行analyze table用以回收表空间。

pt-archiverBug不会迁移max(id)那条数据的解决方法：

参考：http://www.ttlsa.com/mysql/pt-archiver-bug-cannot-migration-max-id-record/
   vim/usr/bin/pt-archiver +6285  ，如下：
    修改前： $first_sql .= " AND ($col < " . $q->quote_val($val) . ")";
    修改后： $first_sql .= " AND ($col <= " . $q->quote_val($val) .")";

删除老数据(单独的删数据操作不用指定字符集)

/usr/bin/pt-archiver \
--source h=localhost,u=root,p=1234,P=3306,D=test,t=t \
--no-check-charset --where 'a<=376' --limit 10000 --txn-size 1000 --purge

复制数据到其他mysql实例，且不删除source的数据(指定字符集)：

/usr/bin/pt-archiver \
--source h=localhost,u=root,p=1234,P=3306,D=test,t=t1\
--dest h=192.168.2.12,P=3306,u=archiver,p=archiver,D=test,t=t1_bak \
--progress 5000 --where 'mc_id<=125' \
--statistics --charset=UTF8 --limit=10000 --txn-size 1000 --no-delete

复制数据到其他mysql实例，并删source上的旧数据(指定字符集)：

/usr/bin/pt-archiver \
--source h=localhost,u=root,p=1234,P=3306,D=test,t=t1 \
--dest h=192.168.2.12,P=3306,u=archiver,p=archiver,D=test,t=t1_his \
--progress 5000 --where "CreateDate <'2017-05-01 00:00:00' " \
--statistics --charset=UTF8 --limit=10000 --txn-size 1000 --bulk-delete
### 官方文档说明：The normal method isto delete every row by its primary key. Bulk deletes might be a lot faster.They also mightnot be faster if you have a complex WHERE clause.

复制数据到其他mysql实例，不删除source数据，但是使用批量插入dest上新的数据(指定字符集)：

/usr/bin/pt-archiver \
--source h=localhost,u=archiver,p=archiver,P=3306,D=test,t=t1 \
--dest h=192.168.2.12,P=3306,u=archiver,p=archiver,D=test,t=t1_his \
--progress 5000 --where "c <'2017-05-01 00:00:00' " \
--statistics --charset=UTF8 --limit=10000 --txn-size 1000 --no-delete  --bulk-insert
### 测试用的一张只有3列元素的表，共计9万行数据。使用bulk-insert用时7秒钟。而常规insert用时40秒。

导出数据到文件：

/usr/bin/pt-archiver \
--source h=10.0.20.26,u=root,p=1234,P=3306,D=test,t=t \
--file '/root/test.txt' \
--progress 5000 --where 'a<12000' \
--no-delete --statistics --charset=UTF8 --limit=10000 --txn-size 1000

导出数据到文件并删除数据库的相关行：

/usr/bin/pt-archiver \
--source h=10.0.20.26,u=root,p=1234,P=3306,D=test,t=t \
--file '/root/test.txt' \
--progress 5000 --where 'a<12000' \
--statistics --charset=UTF8 --limit=10000 --txn-size 1000 --purge

pt-query-digest 慢日志分析工具

慢日志分析

pt-query-digest -uroot -S /tmp/mysql.sock -p'xxxx' /log/mysql/slow3306.log >> /tmp/slow_log.log

或者：
pt-query-digest /log/mysql/slow3306.log >slow.log

慢查询分析需要关注的点

1. 看sql 执行的频率，1小时大概执行的次数
2. 看每条sql的执行时间，一条sql每次执行多长时间
3. 看执行计划的情况，看是否走全表扫描等
4. 看分析的时间范围
5. 看数据量的大小
6. 看索引字段是否有创建索引的必要，看重复值多不多

pt-heartbeat 延迟分析工具

延迟获取

pt-heartbeat -uroot -p'xxxx' --host=10.25.150.200 -D pt --master-server-id=2013306 --check

pt-table-checksum 数据一致性校验

详情请查看数据的一致性校验

pt-slave-restart

pt-slave-restart的功能是监控和出错后重启MySQL复制。
用法如下：

pt-slave-restart [OPTIONS] [DSN]

pt-slave-restart监控一个或者多个MySQL复制slave的错误，然后当复制停止时试图重启。
pt-slave-restart监控一个或者多个MySQL复制slave，试图跳过引起错误的语句。它以指数变化的睡眠时间职能地检查slave。你可以指定要跳过的错误然后运行slave一直到一个确定的binlog位置。

虽然该工具可以帮助slave跳过错误继续运行，但是你不能依赖它去修复复制。如果slave错误频繁或者意外发生，你应该识别和修复其根本产生源。

pt-slave-restart一旦检测到slave有错误就会打印一行。默认情况下该打印行为：时间戳、连接信息、中继日志文件、中继日志位置以及上一个错误号。你可以使用--verbose选项添加更多信息，也可以使用--quiet选项阻止所有输出。
pt-slave-restart检查slave的过程中智能地睡眠。当前的睡眠时间是变化的。

初始睡眠时间通过--sleep选项给出。
如果检测发现错误，它对半之前的睡眠时间。
如果检测到没有错误，它倍增之前的睡眠时间。
通过--min-sleep和--max-sleep参数限定睡眠时间的下界和上界。
一旦检测到错误，pt-slave-restart假定接下来很可能发生另一个错误，因此它采用当前的睡眠时间或者初始睡眠时间，取决于哪个值更小。
    从Percona Toolkit 2.2.8版本起，pt-slave-restart开始支持由MySQL 5.6.5版本引入的GTID复制。重点牢记：

当采用多线程复制（slave_parallel_workers > 0）时，pt-slave-restart不能跳过事务。pt-slave-restart不能确定GTID事件是哪个特定slave线程执行失败的事务。
默认行为是跳过来自master的下一个事务。写可以来自不同的服务器，每个服务器都有它自己的UUID。参考--master-uuid选项。
``
以下为个人本地环境的测试数据。过程是master和slave上复制同步的一张表，首先是在slave上手动分5次删除5条数据，然后再去master上面replay同样的操作。因为slave上该5条数据已经不存在了，所以master上的复制事件到了slave就会出错，但是借助pt-slave-restart工具就可以智能地跳过保证复制工作继续进行了。

跳过之前需要设置复制并行为0

show variables like '%slave_parallel_workers%';
set global slave_parallel_workers=0;
否者会报如下错误：
Cannot skip transactions properly because GTID is enabled and slave_parallel_workers > 0. See 'GLOBAL TRANSACTION IDS' in the tool's documentation.

pt-slave-restart -h192.168.112.128 -P3306 -uroot -p123456 --sleep=11

pt-slave-restart --error-numbers=1062 -h localhost -uroot -pmysql -S /tmp/mysql.sock

跳过之后设置复制并行为8

show variables like '%slave_parallel_workers%';
set global slave_parallel_workers=8;

### 一次主从复制出错解决与pt-slave-restart工具使用
测试环境中，主库执行了DDL语句增加一个字段的长度后，从库报无法修改这个字段长的的问题，且这个字段的长度已经介于原来的长度和目标长度中间了

环境
5.7.19 GTID双主复制

解决步骤：

1.尝试手工修改字段长度，恢复到未修改前的长度，重启slave进程。结果：失败，报同样的错误，错误编号1677
2.尝试手工修改字段长度，同步到修改后长度，跳过这个事务：

手动跳过：

mysql>stop slave;

mysql>set gtid_next="d7c35015-9dd1-11e7-b70d-005056aa19c3:51629";

这里需要注意，由于开启了双主GTID复制,show master status和Executed_gtid_set会有两个GTID值，其中一个为自己的GTID，另一个为主的GTID。

设置的时候只要把主库的GTID写进“”即可。自身的GTID不需要指明，但如果使用set gtid_purged的方式跳过，是需要可以指明两个GTID的

mysql>begin;commit;

设置后，插入一个空事务进行更新GTID。

mysql>set gtid_next='automatic'；

官方手册规定：精确的指定过一次GTID，并产生一次事务后，后面必须再次指定一次gtid_next的模式（是模式，不是精确值，官方手册这里没有写清楚）

“After this variable has been set to UUID:NUMBER, and a transaction has been committed or rolled back, an explicit SET GTID_NEXT statement must again be issued before any other statement.”

mysql>start slave；

1.gtid_next是一个会话级别的参数，而gtid_purged则是一个全局级别的参数

但还是报同样的错误，一次次的这样操作也很麻烦，

**批量跳过复制错误有如下两个方法**

1.使用slave-skip-errors=123,456,789，但这个参数不是动态参数，需要写进配置文件并重启，而且也不方便观测

2.使用percona公司的pt-slave-restart工具

pt-slave-restart是percona-toolkit工具集中的一个专用于处理复制错误的工具
原理：根据设置，跳过从主库过来的指定错误事务
1.支持GTID复制，但是不支持多线程复制，工具分不清到底哪个线程复制出了问题，所以修复之前需要先关闭并行复制

**参数说明：**

--always :永不停止slave线程，手工停止也不行
--ask-pass :替换-p命令，防止密码输入被身后的开发窥屏
--error-numbers ：指定跳过哪些错误，可用,进行分隔
--error-text ：根据错误信息进行匹配跳过
--log ：输出到文件
--recurse ：在主端执行，监控从端
--runtime ：工具执行多长时间后退出：默认秒， m=minute,h=hours,d=days
--slave-user --slave-password ：从库的账号密码，从主端运行时使用
--skip-count ：一次跳过错误的个数，胆大的可以设置大些，不指定默认1个
--master-uuid :级联复制的时候，指定跳过上级或者上上级事务的错误
--until-master :到达指定的master_log_pos,file位置后停止，
格式：”file:pos“
--until-relay :和上面一样，但是时根据relay_log的位置来停止

**使用实例：**

pt-slave-restart --user=root --password=123456 --socket=/data/mysql/3304/tmp/mysql3304.sock --error-numbers=1677

## pt-slave-repair 
### MySQL 主从复制异常？试试 pt-slave-repair 吧！

“自动修复主从同步数据工具能够提高效率、降低风险、实时监控和响应、自动化运维以及快速故障恢复，可以极大地提升同步运行的稳定性和可靠性。” 

### 1 引言
在日常的工作中，我们经常遇到这样的问题：
- 1.业务方在主库进行批量删除数据，但是这些删除的数据在从库上没有记录，就导致了主从出现了 1032 错误。
-  2. 由于业务之前的不当操作，导致主从数据违反了唯一性约束，进而出现了 1062 错误。

**痛点**

这些问题，说起来大家都知道怎么回事，也知道怎么处理。但是出现一次两次还好说，如果频繁出现，不仅处理起来繁琐困难，也说明了主从基本上处于废置状态。生产环境中，通常数据量比较大，又有业务在进行读写，重置一次主从关系的成本比较高。

那么，针对频繁出现的 1032 和 1062 错误，今天就为大家介绍一款工具 pt-slave-repair[1] ，让大家花最小的成本来处理这个常见的主从故障问题。

### 2 工具介绍
pt-slave-restart[2] 是 percona-toolkit 工具集[3] 的一款 MySQL 主从异常处理工具。

pt-slave-repair 是对原有 pt-slave-restart 工具的补充，作者贺春旸。它提供自动修复 MySQL 主从同步复制的报错数据，以及恢复中断的 SQL_THEARD 复制线程，了解更多[4]。
![pt-repair](https://img2024.cnblogs.com/blog/2130401/202605/2130401-20260526164256086-282326586.png)

### 3 工作原理
在具体实操之前，先引用一段作者对 pt-slave-repair 工具的工作原理介绍。
- 1. 当检测到同步报错 1062（主键冲突、重复）和 1032（数据丢失）时，首先要进行 binlog 环境检查，如果 binlog_format 不等于 ROW 并且 binlog_row_image 不等于 FULL，则退出主程序。如果错误号非 1062 或 1032，则直接退出主程序。
- 2. 获取 show slave status 信息，得到 binlog、position、gtid 信息。
- 3. 连接到主库上解析 binlog，如果是 DELETE 删除语句，则直接跳过。
- 4. 关闭 slave_parallel_workers 多线程并行复制。如果开启 GITD 复制模式，启用 SET gtid_next 方式；
- 5. 如果开启位置点复制模式，启动 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1 方式。
- 6. 如果是 UPDATE/INSERT 语句，则把 BINLOG 解析为具体的 SQL，并且反转 SQL，将其转换为 REPLACE INTO。
- 7. 将解析后的 REPLACE INTO 语句反向插入 slave 上，使其数据保持一致，然后执行第 5 步操作。
- 8. 将 slave 设置为 read_only 只读模式。
- 9. 依次类推，最终使其 show slave status 同步为双 YES（同步正常）。

### 4 准备工作
先对复制用户 repl 添加相应权限

3306[(none)]>show grants for repl@'%';
+----------------------------------------------+
| Grants for repl@% |
+----------------------------------------------+
| GRANT REPLICATION SLAVE ON . TO 'repl'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)

3306[(none)]>GRANT SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON . TO 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)

3306[(none)]>GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO 'repl'@'%' ;
Query OK, 0 rows affected (0.00 sec)

3306[(none)]>GRANT SELECT ON performance_schema.* TO 'repl'@'%';
Query OK, 0 rows affected (0.01 sec)

3306[(none)]>show grants for repl@'%';
+-------------------------------------------------------------------------+
| Grants for repl@% |
+-------------------------------------------------------------------------+
| GRANT SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON . TO 'repl'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO 'repl'@'%' |
| GRANT SELECT ON performance_schema.* TO 'repl'@'%' |
+-------------------------------------------------------------------------+
3 rows in set (0.00 sec)

3306[(none)]>flush privileges;
Query OK, 0 rows affected (0.00 sec)

然后安装 pt-slave-repair 工具。

wget https://github.com/hcymysql/pt-slave-repair/releases/download/pt-slave-repair_v1.0.8/pt-slave-repair

chmod 755 pt-slave-repair

至此，pt-slave-repair工具使用前的准备工作完成。下面我们来讲解工具的使用。

### 5 使用方式
#### 1032 错误

主库创建一张 t1 表：

CREATE TABLE t1 (
id int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

主库插入两条数据到 t1 表，并且不写入 binlog 日志。

这时就会出现，主库有这两条数据，从库不存在着两条数据的情况。

set @@sql_log_bin=0;
insert into t1 values(2),(3);
set @@sql_log_bin=1;

主库删除刚刚插入的两条数据 2 3

delete from t1 where id=2;
delete from t1 where id=3;

此时，主从 1032 错误出现

3306[test]>show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.14
Master_User: repl
Master_Port: 3306
...
Slave_IO_Running: Yes
Slave_SQL_Running: No
...
Last_Errno: 1032
Last_Error: Could not execute Delete_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000001, end_log_pos 92274

#### MySQL 主从复制 1032 错误的根源
- 1.主库删除了一个从库不存在的数据。
**解决办法：**直接跳过。
- 2. 主库修改了一条从库不存在的用户。
**解决办法：**手动添加这条数据。
使用 pt-slave-repair 工具修复 1032 错误（一定要连接到从库上运行 ！！！）。

/root/pt-slave-repair -H 192.168.3.14 -P 3306 -u repl -p 123456 -d test

192.168.3.14:3306 这是一台主库，环境不匹配！

前台运行

/root/pt-slave-repair -H 192.168.3.13 -P 3306 -u repl -p 123456 -d test
2025-02-21 04:46:36,015 - ERROR - 主从复制报错. Slave_IO_Running状态值是：Yes | Slave_SQL_Running状态值是：No
Last_Error错误信息是：Could not execute Delete_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000001, end_log_pos 92274

Last_SQL_Error 错误信息是：Could not execute Delete_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000001, end_log_pos 92274
2025-02-21 04:46:36,024 - ERROR - 错误号是：1032
2025-02-21 04:46:36,024 - ERROR - 错误信息是：Could not execute Delete_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000001, end_log_pos 92274
2025-02-21 04:46:36,024 - ERROR - 报错时间是：2025-02-21 04:14:26

2025-02-21 04:46:36,024 - INFO - ----------------------------------------------------------------------------------------------------
2025-02-21 04:46:36,025 - INFO - 开始自动修复同步错误的数据......

2025-02-21 04:46:36,064 - INFO - 修复数据的SQL语句: delete
2025-02-21 04:46:36,393 - INFO - 成功修复了 【2】 行数据
2025-02-21 04:46:37,448 - ERROR - 主从复制报错. Slave_IO_Running状态值是：Yes | Slave_SQL_Running状态值是：No
Last_Error错误信息是：Could not execute Delete_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000001, end_log_pos 92527

Last_SQL_Error错误信息是：Could not execute Delete_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000001, end_log_pos 92527
2025-02-21 04:46:37,450 - ERROR - 错误号是：1032
2025-02-21 04:46:37,450 - ERROR - 错误信息是：Could not execute Delete_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000001, end_log_pos 92527
2025-02-21 04:46:37,450 - ERROR - 报错时间是：2025-02-21 04:46:35

2025-02-21 04:46:37,450 - INFO - ----------------------------------------------------------------------------------------------------
2025-02-21 04:46:37,450 - INFO - 开始自动修复同步错误的数据......

2025-02-21 04:46:37,488 - INFO - 修复数据的SQL语句: delete
2025-02-21 04:46:37,824 - INFO - 成功修复了 【2】 行数据
2025-02-21 04:46:38,887 - WARNING - 开启基于GTID全局事务ID复制，CHANGE MASTER TO MASTER_AUTO_POSITION = 1 需要设置为1.
2025-02-21 04:46:38,887 - INFO - 同步正常.

再次检查主从状态

3306[test]>show slave status \G
Master_Host: 192.168.3.14
Master_User: repl
Master_Port: 3306
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

后台运行该工具。

nohup /root/pt-slave-repair -H 192.168.3.13 -P 3306 -u repl -p 123456 -d test > /dev/null &

#### 1062 错误
1062 错误为主从同步时主键冲突提示，表示尝试插入或更新数据时违反了唯一约束。
主要出现在以下两种场景：
- 1. 插入一条记录，但主键或唯一索引的值已经存在。
- 2. 更新一条记录，但更新后的值与其他记录的唯一键冲突。

#### MySQL 主从复制 1062 错误的根本处理办法
- 1062错误主要是违反了已唯一性约束，在主从强一致性要求比较高的情况下，需要在从库删除主键冲突的这条数据。
- 有时候业务数据比较重要，一致性要求不高，需要跟开发、业务确认冲突的这条数据的重要性进行处理。

主库：

CREATE TABLE t2 (
id INT PRIMARY KEY, -- 主键，唯一约束
name VARCHAR(100)
);

INSERT INTO t2 (id, name) VALUES (1, 'Alice');
INSERT INTO t2 (id, name) VALUES (2, 'Bob');

从库插入一条数据，并且不写入binlog：

set @@sql_log_bin=0;
INSERT INTO t2 (id, name) VALUES (3, 'jiang');
set @@sql_log_bin=1;

主库再次插入一条主键为3的数据：

INSERT INTO t2 (id, name) VALUES (3, 'shifeng');

主从报 1062 错误：

3306[test]>show slave status \G
...
Master_Host: 192.168.3.14
Master_User: repl
Master_Port: 3306
...
Last_Errno: 1062
Last_Error: Could not execute Write_rows event on table test.t2; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 93519
Skip_Counter: 0
Exec_Master_Log_Pos: 93286

#### 使用 pt-slave-repair 工具修复1062错误（一定要连接到从库上运行 ！！！）。

[root@node4 ~]# /root/pt-slave-repair -H 192.168.3.13 -P 3306 -u repl -p 123456 -d test
2025-02-21 05:23:17,145 - ERROR - 主从复制报错. Slave_IO_Running状态值是：Yes | Slave_SQL_Running状态值是：No
Last_Error错误信息是：Could not execute Write_rows event on table test.t2; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 93519

Last_SQL_Error错误信息是：Could not execute Write_rows event on table test.t2; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 93519
2025-02-21 05:23:17,147 - ERROR - 错误号是：1062
2025-02-21 05:23:17,147 - ERROR - 错误信息是：Could not execute Write_rows event on table test.t2; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 93519
2025-02-21 05:23:17,147 - ERROR - 报错时间是：2025-02-21 05:20:02

2025-02-21 05:23:17,147 - INFO - ----------------------------------------------------------------------------------------------------
2025-02-21 05:23:17,147 - INFO - 开始自动修复同步错误的数据......

2025-02-21 05:23:17,201 - INFO - 修复数据的SQL语句: REPLACE INTO test.t2(id,name) VALUES (3'shifeng');
2025-02-21 05:23:17,553 - INFO - 成功修复了 【1】 行数据
2025-02-21 05:23:18,619 - WARNING - 开启基于GTID全局事务ID复制，CHANGE MASTER TO MASTER_AUTO_POSITION = 1 需要设置为1.
2025-02-21 05:23:18,620 - INFO - 同步正常.

再次检查主从状态

3306[test]>show slave status \G
...
Master_Host: 192.168.3.14
Master_User: repl
Master_Port: 3306
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

### 6.总结
- 在日常的操作中，从库一定要开启只读模式，尽量不要写入数据。
- 主库在更新操作的时候，要时刻写入 binlog 日志。
- 要严格遵守开发规范，不能为了图方便就忽视开发规范。