MySQL运维-PT-OSC工具在线修改唯一索引

1.需求

变更sbtest1表唯一索引`UNI_TEST_OLD` (`k`)为`UNI_TEST` (`k`,`pad`)

 2.表结构

3.环境信息

3.1版本信息

OS版本	MySQL版本	PT版本
Centos 7	5.7.22	3.3.0-1

3.2表数据量大小

3.3磁盘/内存剩余空间

3.4binlog大小

3.5cpu核数

3.6主从同步状况

4.安装pt工具

4.1 PT工具下载

地址：Software Downloads - Percona

4.2安装

安装依赖：

yum install perl-DBI -y
yum install perl-devel -y
yum install perl-DBD-MySQL -y
yum install perl-Time-HiRes -y
yum install perl-IO-Socket-SSL -y
yum install perl-TermReadKey -y
yum install perl-Digest-MD5  -y

 安装pt工具：

4.3pt-online-schema-change 介绍

4.3.1原理

(1)创建一个与原表结构相同的空表，表名是 _new 后缀；

(2)修改步骤 1 创建的空表的表结构；

(3)在原表上加三个触发器：delete/update/insert，用于 copy 数据过程中，将原表中要执行的语句在新表中执行；

(4)将原表数据以数据块（chunk）的形式 copy 到新表；

(5)rename 原表为 old 表，并把新表 rename 为原表名，然后删除旧表；

(6)删除触发器。

4.3.2限制

1. 原表上要有 primary key 或 unique index，因为当执行该工具时会创建一个 DELETE 触发器来更新新表；注意：一个例外的情况是 --alter 指定的子句中是在原表中的列上创建 primary key 或 unique index，这种情况下将使用这些列用于 DELETE 触发器。

2.如果表上已有同类型的触发器，则无法使用pt-ocs进行修改表结构。

5.添加唯一索引

5.1检查添加字段是否具备唯一性

Sbtest1表pad列distinct值：

mysql> select count(distinct pad),count(*) from sbtest1;

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

| count(distinct pad) |  count(*) |

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

|  200000000    |   200000000  |

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

注意：此步骤必须执行，pt-online-schema-change工具无字段唯一性检查功能，若非唯一字段添加唯一索引，会导致重复部分数据丢失。

5.2执行检查

pt-online-schema-change --print --statistics \
--progress time,30 --user=test --password=test --alter 'ADD UNIQUE UNI_TEST(k,pad)' \
h=10.186.59.82,P=3306,D=test,t=sbtest1 \
--slave-user=universe_op --slave-password=P@ssw0rd  --preserve-triggers \
--pause-file=/tmp/aa.txt --max-load=threads_running=100,threads_connected=200 \
--critical-load=threads_running=1000 --chunk-size=1000 \
--nocheck-unique-key-change  --no-check-replication-filters \
--alter-foreign-keys-method auto  --dry-run

5.3执行

使用screen 工具，执行过程较长，使用screen工具开窗口放到后端执行，避免会话退出中断进程。

Screen 工具用法：

1. 开启命令：screen

1. 正常退出：

在窗口内执行exit命令来关闭窗口

1. 指定screen执行退出命令：

使用screen -S screenID -X quit命令，其中screenID是你要关闭的screen会话的ID。首先，你可以使用screen -ls命令来查看所有正在运行的Screen会话，并从中获取会话的ID

1. 退出screen界面：

使用快捷键Ctrl + A Ctrl + D来退出当前的screen界面，但不会关闭窗口或会话，只是将screen会话放入后台运行

nohup pt-online-schema-change --print --statistics \
--progress time,30 --user=test --password=test --alter 'ADD UNIQUE UNI_TEST(k,pad)' \
h=10.186.59.82,P=3306,D=test,t=sbtest1 \
--slave-user=universe_op --slave-password=P@ssw0rd  --preserve-triggers \
--pause-file=/tmp/aa.txt --max-load=threads_running=100,threads_connected=200 \
--critical-load=threads_running=1000 --chunk-size=1000 \
--nocheck-unique-key-change  --no-check-replication-filters  \
--no-drop-old-table \
--alter-foreign-keys-method auto --execute  &> /opt/pt/run_command_time.log &

• --print: 打印出将要执行的 SQL 语句，但不实际执行它们。
• --statistics: 打印出关于复制延迟的统计信息。
• --progress time,30: 每 30 秒报告一次进度。
• --user=test 和 --password=test: 用于连接到 MySQL 服务器的用户名和密码。
• --alter 'ADD UNIQUE UNI_TEST(k,pad)': 要执行的表结构更改，即添加一个唯一索引。
• h=10.186.59.82,P=3306,D=test,t=sbtest1: 定义了连接到 MySQL 服务器的主机地址、端口、数据库名和表名。
• --slave-user=universe_op 和 --slave-password=P@ssw0rd 从库用户和密码
• --preserve-triggers: 保留原始表上的触发器。
• --pause-file=/tmp/aa.txt: 当该文件存在时，pt-online-schema-change 将暂停操作，删除该文件时进程继续更改，用于临时暂停变更，比如发现主库压力大时，可以建/tmp/aa.txt文件，则进程暂停，删除后继续变更。
• --max-load=threads_running=100,threads_connected=200
• 当 threads_running 超过 100 或 threads_connected 超过 200 时，工具将尝试减缓更改的速度。
• --critical-load=threads_running=1000: 如果 threads_running 达到 1000，工具将暂停更改操作。
• --chunk-size=1000: 定义每个数据块的大小，工具将数据分成多个块来处理。
• --nocheck-unique-key-change: 不检查唯一键更改，这一步手动检查字段唯一性。
• --alter-foreign-keys-method auto: 自动处理外键约束的更改。当设置为 auto 时，pt-online-schema-change 会自动选择最佳的方式来处理外键的修改。
•  --no-check-replication-filters 如果工具检测到服务器选项中有任何复制相关的筛选，如指定binlog_ignore_db和replicate_do_db此类。发现有这样的筛选，工具会报错且退出。因为如果更新的表Master上存在，而Slave上不存在，会导致复制的失败。使用–no-check-replication-filters选项来禁用该检查.
• --no-drop-old-table 不删除旧表 --execute: 执行实际的在线表结构更改

 查看日志：cat /opt/pt/run_command_time.log

 

 5.4根据报错信息更改后重新执行

6.变更后检查

6.1检查表结构

原表被重命名为_sbtest1_old

新表已添加唯一索引UNIQUE KEY `UNI_TEST` (`k`,`pad`) 

6.2数据完整性

6.2.1总行数

6.2.2数据内容完整性检查

可使用pt-table-checksum工具对比_sbtest1_old和sbtest1表。缺点是需要在执行完pt-online-schema-change变更时断流，两张表保持一致状态，检查耗时较长。且会产生大量binlog数据。两表总行数一致则数据内容基本一致，pt-online-schema-change工具在变更过程中使用了触发器保证数据一致，且在拷贝数据块(chunk)过程中若有数据损坏则会停止进程，变更失败。

6.3唯一性约束检查

测试新建唯一索引是否生效

 6.4表大小

 注意：原表数据大小108G，变更前执行过较多更新操作，表占用磁盘空间未释放。变更后67G，原因是pt-online-schema-change工具建唯一索引过程中对数据进行重新排序或压缩

6.5外键约束验证

表中无外键约束，暂略

6.6 主从健康状态

6.7删除表的唯一索引`UNI_TEST_OLD` (`k`)

6.8业务逻辑验证

测试关键业务，观察是否报错

7.回滚方案

7.1pt-online-schema-change未执行成功回滚

7.1.1删除*_new 表

 7.1.2删除触发器

8.总结

pt-osc工具优点：

(1)online DDL,对业务影响较小,rename table时短暂影响

(2)可限制变更过程中资源占用

(3)可实现主从无延迟变更表结构

pt-osc工具缺点：

(1)执行时间长(本次测试用时12.5小时)

(2)唯一索引列若有重复值，可能导致重复部分数据丢失。

9.注意事项

1. 生产环境执行变更操作前做好备份，上述为测试环境备份部分省略。
2. 执行前保证主从库所在服务器磁盘空间充足，剩余空间大于1.5倍表大小
3. 生产环境执行前建议测试环境再测试一遍
4. 生产环境执行建议低峰期执行
5. 本次测试基于MySQL5.7.22，centos 7,pt 3.3.0-1版本，其他版本可能有兼容性问题，若版本不同需在测试环境再次验证。