pt-online-schema-change

本文详细解析了在线DDL操作及Percona Toolkit中的pt-online-schema-change工具的使用方法,包括其限制条件、参数说明及具体示例。涵盖外键处理、触发器限制、索引与列修改等关键场景。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

限制条件
1 外键限制,配合使用–alter-foreign-keys-method参数
2 必须存在主键
3 表上不能存在after(增删改)三个触发器

alter-foreign-keys-method说明
两种方式(t1,t2有外键依赖于t1)
rebuild_constraints(优先)
它先通过alter table t2 drop fk1, add _fk1重建外键,指向新表
再rename t1 t1_old,_t1_new t1 交换表名,不影响客户端
删除旧表t1_old
如果t2表太大,以致alter操作耗时过长,pt-osc有可能会强制选择另外一种方式drop_swap
drop_swap
禁用t2表外键约束检查,设置FOREIGN_KEY_CHECKS = 0
然后drop t1原表 rename _t1_new t1
这种方式速度快,也不阻塞请求,但是有风险。drop表的瞬间到rename过程,t1表是不存在,遇到请求会报错

pt-osc和online ddl对比
1 ddl在必须copy table时成本很高,不宜采用?
2 pt-osc存在触发器时,就用不了
3 修改index,foreign-key,column-name优先使用ddl,并指定ALGORITHM=INPLACE

示例
1 添加新列
pt-online-schema-change --user=user --password=xxx --host=ip --port=33066 --alter “add column col1 VARCHAR(64) NULL COMMENT ‘订单号’” D=sy,t=t1 --execute --charset=utf8 --nocheck-replication-filters --max-load=“Threads_running=20”
2 修改列类型
pt-online-schema-change --user=user --password=xxx --host=ip --port=3306 --alter “convert to character set utf8mb4 collate utf8mb4_bin” D=db1,t=t1 --execute --nocheck-replication-filters --charset=utf8 --max-load=“Threads_running=20”
3 添加删除索引
pt-online-schema-change --user=user --password=xxx --host=ip --port=3306 --alter “DROP KEY cid,add key idx_game_id(game_id)” D=db1,t=t1 --execute --charset=utf8 --nocheck-replication-filters --max-load=“Threads_running=20”
4 修改主键(假设原主键是复合主键)
会涉及以下修改动作

  1. 删除复合主键
  2. 添加新的自增主键
  3. 原复合主键字段,修改成唯一索引
    ps: 修改主键影响最大的就是delete触发器,新表t2上的主键字段在旧表t1上不存在,无法根据主键条件出发删除新表t2数据,如果表上相关列上没有索引或者没有主键,那么删除的代价非常高,所以在删除主键的同时一定添加复合索引
    –alter “drop primary key, add column id auto_crement primary key,add unique key uk_pk_k(pk,k)”

5 重建表
pt-online-schema-change --user=user --password=xxx --host=ip --port=3306 --alter “engine=innodb” D=db1,t=t1 --execute --nocheck-replication-filters --charset=utf8 --max-load=“Threads_running=20” --max-lag=2s --chunk-time=1s

参数说明
execute :该参数用于执行alter操作,如果不加的话,只会做一些安全检查后退出。一定要确保知道如何使用该工具并有合适的备份后,再添加该参数。使用该参数时,除了对对象表所需的权限外,还需要SUPER, REPLICATION SLAVE两种权限。
nocheck-replication-filters:
max-lag:中断数据拷贝直到所有的复制延迟都少于这个值,默认为1S。每一个chunk拷贝完成后,OSC都会去show salve status通过Seconds_Behind_Master来确定所有的复制情况,任何相关的slave的复制延迟高于该值时,OSC就会停止数据拷贝–check-interval参数所指定的时间,然后重新发起检查,直到延迟降低到该值以下。
charset:
max-load:默认threads_running = 25, 每个chunk(chunk是啥)拷贝完,会检查show global status,检查指标是否超过了指定tr线程数的阀值,如果超过,数据拷贝暂停。
critical-load:默认threads_running = 50,每个chunk(chunk是啥)拷贝完,会检查show global status,检查指标是否超过了指定tr线程数的阀值,如果超过 终止pt进程
chunk-time:默认是0.5秒,工具会根据当前系统运行繁忙程度计算出在该指定时间内可以处理的数据行数(即chunks),(相比较chunk-size灵活)
chunk-size:指定chunk的大小

ps:
threads_running代表非sleeping状态的线程数量。
threads_running增长的原因:
1 客户端连接暴增
2 系统性能瓶颈,cpu io 内存 swap
3 异常sql

### 使用 `pt-online-schema-change` 进行数据库表结构变更 `pt-online-schema-change` 是一个由 Percona 提供的在线 DDL 工具,专门用于在不影响 MySQL 数据库正常运行的情况下进行表结构变更。它通过创建临时表并逐步迁移数据的方式,实现无锁操作,从而避免了传统 `ALTER TABLE` 命令可能带来的性能问题和锁表现象。以下是如何使用 `pt-online-schema-change` 工具进行数据库表结构变更的详细方法。 #### 1. 安装 `pt-online-schema-change` 工具 `pt-online-schema-change` 是 Percona Toolkit 的一部分,可以通过安装 Percona Toolkit 来获取该工具。在 Linux 系统中,可以通过以下命令安装: ```bash # 对于基于 Debian 的系统 sudo apt-get install percona-toolkit # 对于基于 Red Hat 的系统 sudo yum install percona-toolkit ``` 安装完成后,确保 `pt-online-schema-change` 命令可用。 #### 2. 基本使用方法 `pt-online-schema-change` 的基本命令格式如下: ```bash pt-online-schema-change [OPTIONS] D=database,t=table ``` 其中,`D=database` 指定数据库名称,`t=table` 指定表名,`[OPTIONS]` 是可选参数。 #### 3. 常用参数 - `--alter`: 指定要执行的表结构变更操作,例如添加列、修改列类型、删除列等。 - `--execute`: 表示立即执行变更操作。 - `--no-drop-old-table`: 在执行完成后不删除旧表,通常用于调试或备份。 - `--dry-run`: 模拟执行变更操作,不实际修改数据表。 - `--user` 和 `--password`: 指定数据库连接的用户名和密码。 - `--host`: 指定数据库服务器的地址。 #### 4. 示例:添加字段并修改存储引擎 假设需要将数据库 `test` 中的表 `user` 的存储引擎从 InnoDB 改为 MyISAM,并新增字段 `signup_time`,可以按照以下步骤进行操作。 ##### 步骤1:添加新字段 ```bash pt-online-schema-change \ --alter "ADD COLUMN signup_time DATETIME" \ --execute \ D=test,t=user ``` ##### 步骤2:修改存储引擎 ```bash pt-online-schema-change \ --alter "ENGINE=MyISAM" \ --no-drop-old-table \ --execute \ D=test,t=user ``` #### 5. 注意事项 - **锁表问题**: `pt-online-schema-change` 通过创建临时表并逐步迁移数据的方式,避免了传统 `ALTER TABLE` 命令可能带来的锁表现象。然而,在某些情况下,例如表结构变更涉及大量数据时,仍然可能会对数据库性能产生一定影响。 - **数据一致性**: 在使用 `pt-online-schema-change` 进行表结构变更时,确保数据库的主从复制配置正确,以避免数据不一致的问题。 - **测试环境**: 在生产环境中使用 `pt-online-schema-change` 之前,建议在测试环境中进行充分的验证,以确保变更操作的正确性和安全性。 #### 6. 替代方案 除了 `pt-online-schema-change`,还有一些其他工具可以用于在线表结构变更,例如: - **Online DDL in MySQL 5.6+**: MySQL 5.6 及以上版本引入了在线 DDL 功能,允许在不锁定表的情况下进行某些表结构变更操作。 - **gh-ost**: GitHub 开发的在线表结构变更工具,支持更复杂的变更场景,并提供了更高的灵活性和安全性。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值