Write by lyc at 2019-10-30
参考博文：
在线更改MySQL表结构工具pt-online-schema-change
pt-online-schema-change使用说明、限制与比较
pt-online-schema-change的原理解析与应用说明

一、pt-online-schema-change 介绍

1.pt-osc 特点

  • percona-toolkit里带的工具，安装percona-toolkit即可
  • 在线执行DDL操作，不会阻塞读写操作
  • 选择业务低谷时操作，操作前先备份数据
  • 操作前确认该表没有未提交的大事务或者慢查询

2.pt-osc 使用限制

  • 如果修改表有外键，除非使用 --alter-foreign-keys-method 指定特定的值，否则工具不予执行
  • 被修改表必须要有主键或者唯一索引，否则报错
  • 被修改表上不能有针对after delete|insert|update三个触发器，否则修改表结构操作失败

3. pt-osc 工作原理

  • 创建一个和要执行 Alter 操作的表一样的新的空表结构(是alter之前的结构)，新表名为tablename_new
  • 在新表执行 Alter TABLE 语句
  • 在原表中创建触发器3个触发器分别对应insert,update,delete操作
  • 以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表
  • RENAME 原表成old表，再新的临时表RENAME为原表名
  • 默认最后将旧原表删除，删除触发器
  • 另外如果存在外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理。

二、pt-online-schema-change 参数

1.基础参数

  • --user mysql用户名
  • --password mysql用户密码
  • --host 数据库ip
  • --port 数据库端口号
  • --socket 数据库socket文件
  • --alter DDL语句，省略ALTER TABLE table_name关键字后面的SQL
  • D=database,t=table_name 要操作DDL的库名，表名
  • --check-replication-filters 检查复制中是否设置了过滤条件，如果设置了，程序将退出
  • --no-check-replication-filters 不检查复制中是否设置了过滤条件
  • --alter-foreign-keys-method
    • auto 自动选择
    • yes 默认值

2.主从延迟参数

  • --recursion-method= master寻找slave的方式
    • none 主从环境，不在乎从的延迟
    • processlist 使用SHOW PROCESSLIST;命令查找从库 hosts
    • hosts 使用SHOW SLAVE HOSTS;命令查找从库
    • dsn=DSN 使用表格 tdsn存储从库信息，需要手动在需要DDL的数据库内创建固定表格
  • --max-lag 默认1s 检查从库延迟的时间，如果超过，则停止copy data，休息--check-interval秒后，再重新开始copy数据。查看通过延迟时间，是通过从库show slave status;，查看Seconds_Behind_Master 如果指定--check-slave-lag，该工具只检查该服务器的延迟，而不是所有服务器。
  • --check-interval 从库延迟超过指定的--max-lag，中断copy data休息的时间 默认为1s
  • --max-load copy data的过程，监控数据库当前正在运行的thread，如果超过指定的Threads_running值，则停止拷贝数据，会在输出的内容中答应 Pausing because Threads_runing=15，直到运行的线程数小于给定的值，恢复copy data，如此循环，直到拷贝数据结束。 Threads_runing默认为25 举例：--max-load=Thread_running=15

3.输出与执行参数

  • --print 详细打印alter过程，不指定的时候，简略打印
  • --dry-run 创建和修改新表，但不会创建触发器、复制数据、和替换原表。并不真正执行，可以看到生成的执行语句，了解其执行步骤与细节，和–print配合最佳。。
  • --execute 确定修改表，则指定该参数。真正执行alter。–dry-run与–execute必须指定一个，二者相互排斥

三、执行在线DDL（不考虑外键、不考虑从库延迟）

修改表引擎

1
2
3
4
5
6
7
8
9
10
11
12
13
$ /usr/local/bin/pt-online-schema-change \
--user=root \
--password=123456 \
--host=xxx.xxx.xxx.xxx \
--port=3306 \
--charset=utf8 \
--alter "ENGINE=InnoDB" \
D=db_name,t=tb_name \
--no-check-replication-filters \
--alter-foreign-keys-method=auto \
--recursion-method=none \
--print \
--execute

修改表行格式（开启压缩）

1
2
3
4
5
6
7
8
9
10
11
12
13
$ pt-online-schema-change \
--user=root \
--password=123456 \
--host=xxx.xxx.xxx.xxx \
--port=3306 \
--charset=utf8 \
--alter "ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8" \
D=db_name,t=tb_name \
--no-check-replication-filters \
--alter-foreign-keys-method=auto \
--recursion-method=none \
--print \
--execute

增加字段

1
2
3
4
5
6
7
8
9
10
11
12
$ pt-online-schema-change \
--user=root \
--password=123456 \
--socket=/data/mysql_data_3306/mysql_3306.sock  \
--charset=utf8 \
--alter "ADD COLUMN test_column INT(11) DEFAULT 0 NULL COMMENT '测试加字段' AFTER StatDateInt" \
D=database_name,t=table_name \
--no-check-replication-filters \
--alter-foreign-keys-method=auto \
--recursion-method=none \
--print \
--execute

删除字段

1
2
3
4
5
6
7
8
9
10
11
12
$ pt-online-schema-change \
--user=root \
--password=123456 \
--socket=/data/mysql_data_3306/mysql_3306.sock  \
--charset=utf8 \
--alter "DROP COLUMN test_column" \
D=database_name,t=table_name \
--no-check-replication-filters \
--alter-foreign-keys-method=auto \
--recursion-method=none \
--print \
--execute

修改字段类型

1
2
3
4
5
6
7
8
9
10
11
12
$ pt-online-schema-change \
--user=root \
--password=123456 \
--socket=/data/mysql_data_3306/mysql_3306.sock  \
--charset=utf8 \
--alter "modify column test_column char(11)" \
D=database_name,t=table_name \
--no-check-replication-filters \
--alter-foreign-keys-method=auto \
--recursion-method=none \
--print \
--execute

字段改名

1
2
3
4
5
6
7
8
9
10
11
12
13
$ pt-online-schema-change \
--user=root \
--password=123456 \
--socket=/data/mysql_data_3306/mysql_3306.sock  \
--charset=utf8 \
--alter "CHANGE COLUMN test_column test_column_new char(11)" \
D=database_name,t=table_name \
--no-check-alter \
--no-check-replication-filters \
--alter-foreign-keys-method=auto \
--recursion-method=none \
--print \
--execut

创建索引

1
2
3
4
5
6
7
8
9
10
11
12
13
$ pt-online-schema-change \
--user=root \
--password=123456 \
--socket=/data/mysql_data_3306/mysql_3306.sock  \
--charset=utf8 \
--alter "ADD INDEX Index_test_column_new(test_column_new)" \
D=database_name,t=table_name \
--no-check-alter \
--no-check-replication-filters \
--alter-foreign-keys-method=auto \
--recursion-method=none \
--print \
--execute

删除索引

1
2
3
4
5
6
7
8
9
10
11
12
13
$ pt-online-schema-change \
--user=root \
--password=123456 \
--socket=/data/mysql_data_3306/mysql_3306.sock  \
--charset=utf8 \
--alter "DROP INDEX Index_test_column_new" \
D=database_name,t=table_name \
--no-check-alter \
--no-check-replication-filters \
--alter-foreign-keys-method=auto \
--recursion-method=none \
--print \
--execute

四、执行在线DDL（不考虑外键、考虑从库延迟）

1.创建表格dsns，记录从库信息（2*slave+）

  • 2个及以上从库考虑使用 dsns表格来记录从库信息。
  • 1个从库可以直接使用参数--check-slave-lag指定从库
1
2
3
4
5
6
7
8
9
10
11
# 在被操作表的库下创建dsns表
CREATE TABLE `dsns` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

# 插入从库信息
insert into dsns(dsn) select "h=192.168.100.170,u=slave,p=TVKIYZ93n53RciV0yCHwygew,P=3306";
insert into dsns(dsn) select "h=192.168.100.172,u=slave,p=TVKIYZ93n53RciV0yCHwygew,P=3306";