pt-online-schema-change(pt-osc)

说明

  • pt-osc是percona公司下percona-toolkit中的一款 在线变更表结构 的工具
  • 实现是基于触发器
  • 变更表结构流程：
    • 先创建变更表结构后的新表，旧表绑定增、删、改的触发器
    • 分段加锁，将旧表的数据插入新表中
    • 数据拷贝完成后，新旧表加锁，更改新旧表表名，删除旧表及旧表触发器，完成数据拷贝

参数说明

  • –max-load “Threads_running=25”：执行时，查询 Threads_running ，如果超过指定值，则 waiting ，等降低后在继续执行
    • Threads_running 查询：show global status like ‘Threads_running’
  • –critical-load：默认 50，Threads_running 超过 50 时会自动中止
    • –critical-load “Threads_running=125”
  • –no-drop-old-table：执行结束后，不删除旧表
  • –no-drop-triggers：拷贝结束后，不删除旧表触发器
  • –no-swap-tables：拷贝结束后，不切换新旧表(结合 –no-drop-triggers –no-drop-old-table 可拷贝生成新表并保持数据实时同步)
  • –new-table-name：指定生成的临时新表的表名，需为不存在的表，否则无法继续
  • –charset=utf8：指定使用 utf8 编码
  • –chunk-size：单次转储数据的条数
    • 针对转储数据过程中频繁触发死锁的情况使用，可设置为比较小的值，但同时也会延长转储时间

主从相关

  • –check-interval：两次检验主从延迟间的间隔
  • –max-lag：容忍的主从延迟时间
  • –recursion-method=none：配置监控主从延迟的方式
    • processlist：通过 show processlist 获取 slave 节点监控延迟，适用于使用 3306 端口的情况，实测 3.3.0+ 版本会报错
    • hosts：通过SHOW SLAVE HOSTS获取从库，实测获取不到 IP，仅能获取端口
    • DSN：通过 DSN 指定从库节点
  • –check-slave-lag DSN：配置连接单个从库监控延迟

使用

  • 常用变更语句关键字
1
2
3
4

-- 更改字段编码
MODIFY COLUMN card_number varchar(100) COLLATE utf8_general_ci,CONVERT TO CHARSET utf8mb4
-- 重建主键，整理表空间
engine=InnoDB
  • 例：
1
2
3
4
5

pt-online-schema-change --alter="engine=InnoDB" \
--host=127.0.0.1 --user=xxx --port=3306 --ask-pass \
--charset=utf8 --max-load="Threads_connected=500,Threads_running=50" \
--critical-load="Threads_connected=800,Threads_running=100" \
--execute D=test,t=mall_orders

主从场景下的使用

  • 3.3.0 以上版本(不包含 3.3.0)，使用自动获取 slave 节点时存在问题，会出现如下报错，需通过参数指定 slave 节点连接方式
1
2

`test`.`mall_orders` was not altered.
        (in cleanup) 2023-06-15T17:16:41 Error copying rows from `test`.`mall_orders` to `test`.`_mall_orders_new`: Use of uninitialized value in string eq at /usr/bin/pt-online-schema-change line 4321.

监控单个从库延迟

  • 通过 –check-slave-lag 指定
1

--check-slave-lag h=192.168.212.102,P=3306,u=xxx,p=123456

example

1
2
3
4
5
6
7

pt-online-schema-change --alter="MODIFY COLUMN update_time varchar(900)  AFTER creation_time" \
--host=127.0.0.1 --user=xxx --port=3306 --ask-pass \
--charset=utf8 --check-interval 5 --max-lag 2  \
--max-load="Threads_connected=500,Threads_running=50" \
--critical-load="Threads_connected=800,Threads_running=100" \
--execute D=test,t=mall_orders \
--check-slave-lag h=192.168.212.102,P=3306,u=xxx,p=123456

监控多个从库延迟

  • 通过 –recursion-method 参数的 DSN 实现

  • 使用此配置需要先创建一个表，然后 在参数中指定从这个表中读取从节点的 DSN 信息

  • 建表语句

1
2
3
4
5
6

CREATE TABLE `slavedsns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

例：

  • 创建库表
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 创建库
CREATE DATABASE `percona` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
-- 切换库
use percona;
-- 创建表
CREATE TABLE `slavedsns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);
  • 写入从节点信息
1
2

insert into slavedsns(dsn) values("h=192.168.212.102,u=xxx,p=123456,P=3306");
insert into slavedsns(dsn) values("h=192.168.212.103,u=xxx,p=123456,P=3306");
  • 更改表结构
1
2
3
4
5
6
7

pt-online-schema-change --alter="MODIFY COLUMN update_time varchar(900)  AFTER creation_time" \
--host=127.0.0.1 --user=xxx --port=3306 --ask-pass \
--charset=utf8 --check-interval 5 --max-lag 2  \
--max-load="Threads_connected=500,Threads_running=50" \
--critical-load="Threads_connected=800,Threads_running=100" \
--execute D=test,t=mall_orders \
--recursion-method dsn=D=percona,t=slavedsns

使用 pt-online-schema-change 拷贝表并保持同步

  • 可以通过 –no-swap-tables –no-drop-old-table –no-drop-triggers 参数，跳过新旧表变更表名，删除触发器，删除旧表这几步
  • 通过 –new-table-name 指定执行拷贝时生成的新表表名，实现自定义拷贝后新表表名的目的

实践

1
2
3
4
5

# 拷贝表并不删除触发器，保持数据实时同步
sudo pt-online-schema-change  --ask-pass --alter "engine=InnoDB"  \
h=127.0.0.1,P=3306,u=user,t=tbl_name,D=db_name \
--new-table-name=new_tbl_name --charset=utf8 \
--no-swap-tables --no-drop-old-table --no-drop-triggers --execute

参考