5.pt-online-schema

pt-online-schema-change alters a table’s structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully.(主要作用是：在不阻塞读写的情况下进行修改表的结构)

主要工作流程：

  • 创建一个跟原表结构一样的新表(作为临时表)
  • 修改新表的结构
  • 在原表中创建insert、update、delete这3中类型的触发器，用于增量数据的迁移；注意：触发器对新表的修改操作和原sql语句在同一个事务中
  • 会以一定块大小(chunck-size)从原表拷贝数据到新表中；注意这里使用insert low_priority ignore into _table_new  select  from 旧表 lock in share mode的方式进行拷贝
  • 数据拷贝完成之后，rename表，原表被命名为旧表，新表命名为原表
  • 删除旧表和触发器

看日志：　

复制代码
2022-10-30T19:16:08.005175+08:00           35 Query     SET SESSION innodb_lock_wait_timeout=1
2022-10-30T19:16:08.007054+08:00           35 Query     SHOW VARIABLES LIKE 'lock\_wait_timeout'
2022-10-30T19:16:08.010484+08:00           35 Query     SET SESSION lock_wait_timeout=60
2022-10-30T19:16:08.011057+08:00           35 Query     SHOW VARIABLES LIKE 'wait\_timeout'
2022-10-30T19:16:08.012646+08:00           35 Query     SET SESSION wait_timeout=10000
2022-10-30T19:16:08.013158+08:00           35 Query     SELECT @@SQL_MODE
2022-10-30T19:16:08.020918+08:00           35 Query     SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2022-10-30T19:16:08.025225+08:00           35 Query     SELECT @@server_id /*!50038 , @@hostname*/
2022-10-30T19:16:08.027894+08:00           36 Connect   root@localhost on t using Socket
2022-10-30T19:16:08.030233+08:00           36 Query     SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2022-10-30T19:16:08.033612+08:00           36 Query     SET SESSION innodb_lock_wait_timeout=1
2022-10-30T19:16:08.034247+08:00           36 Query     SHOW VARIABLES LIKE 'lock\_wait_timeout'
2022-10-30T19:16:08.035848+08:00           36 Query     SET SESSION lock_wait_timeout=60
2022-10-30T19:16:08.036444+08:00           36 Query     SHOW VARIABLES LIKE 'wait\_timeout'
2022-10-30T19:16:08.038423+08:00           36 Query     SET SESSION wait_timeout=10000
2022-10-30T19:16:08.038911+08:00           36 Query     SELECT @@SQL_MODE
2022-10-30T19:16:08.039659+08:00           36 Query     SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2022-10-30T19:16:08.040325+08:00           36 Query     SELECT @@server_id /*!50038 , @@hostname*/
2022-10-30T19:16:08.040880+08:00           35 Query     SHOW VARIABLES LIKE 'wsrep_on'
2022-10-30T19:16:08.042914+08:00           35 Query     SHOW VARIABLES LIKE 'version%'
2022-10-30T19:16:08.045307+08:00           35 Query     SHOW ENGINES
2022-10-30T19:16:08.046561+08:00           35 Query     SHOW VARIABLES LIKE 'innodb_version'
2022-10-30T19:16:08.049400+08:00           35 Query     SHOW VARIABLES LIKE 'innodb_stats_persistent'
2022-10-30T19:16:08.051305+08:00           35 Query     SELECT @@SERVER_ID
2022-10-30T19:16:08.052026+08:00           35 Query     SHOW GRANTS FOR CURRENT_USER()
2022-10-30T19:16:08.052929+08:00           35 Query     SHOW FULL PROCESSLIST
2022-10-30T19:16:08.053692+08:00           35 Query     SHOW SLAVE HOSTS
2022-10-30T19:16:08.055006+08:00           35 Query     SHOW GLOBAL STATUS LIKE 'Threads_running'
2022-10-30T19:16:08.056895+08:00           35 Query     SHOW GLOBAL STATUS LIKE 'Threads_running'
2022-10-30T19:16:08.058533+08:00           35 Query     SELECT CONCAT(@@hostname, @@port)
2022-10-30T19:16:08.059512+08:00           35 Query     SHOW TABLES FROM `t` LIKE 't1'
2022-10-30T19:16:08.060075+08:00           35 Query     SELECT VERSION()
2022-10-30T19:16:08.060583+08:00           35 Query     SHOW TRIGGERS FROM `t` LIKE 't1'
2022-10-30T19:16:08.067575+08:00           35 Query     /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2022-10-30T19:16:08.068141+08:00           35 Query     USE `t`
2022-10-30T19:16:08.068568+08:00           35 Query     SHOW CREATE TABLE `t`.`t1`
2022-10-30T19:16:08.074985+08:00           35 Query     /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-10-30T19:16:08.081045+08:00           35 Query     EXPLAIN SELECT * FROM `t`.`t1` WHERE 1=1
2022-10-30T19:16:08.082280+08:00           35 Query     SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='t' AND referenced_table_name='t1'
2022-10-30T19:16:08.140201+08:00           35 Query     SHOW VARIABLES LIKE 'wsrep_on'
2022-10-30T19:16:08.162191+08:00           35 Query     /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2022-10-30T19:16:08.164151+08:00           35 Query     USE `t`
2022-10-30T19:16:08.164700+08:00           35 Query     SHOW CREATE TABLE `t`.`t1`
2022-10-30T19:16:08.165389+08:00           35 Query     /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-10-30T19:16:08.166674+08:00           35 Query     CREATE TABLE `t`.`_t1_new` (
  `id` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
2022-10-30T19:16:08.208210+08:00           35 Query     ALTER TABLE `t`.`_t1_new` drop column name
2022-10-30T19:16:08.237364+08:00           35 Query     /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2022-10-30T19:16:08.237870+08:00           35 Query     USE `t`
2022-10-30T19:16:08.238457+08:00           35 Query     SHOW CREATE TABLE `t`.`_t1_new`
2022-10-30T19:16:08.239126+08:00           35 Query     /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-10-30T19:16:08.241847+08:00           35 Query     SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,        CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING   FROM INFORMATION_SCHEMA.TRIGGERS  WHERE EVENT_MANIPULATION = 'DELETE'    AND ACTION_TIMING = 'AFTER'    AND TRIGGER_SCHEMA = 't'    AND EVENT_OBJECT_TABLE = 't1'
2022-10-30T19:16:08.243339+08:00           35 Query     SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,        CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING   FROM INFORMATION_SCHEMA.TRIGGERS  WHERE EVENT_MANIPULATION = 'UPDATE'    AND ACTION_TIMING = 'AFTER'    AND TRIGGER_SCHEMA = 't'    AND EVENT_OBJECT_TABLE = 't1'
2022-10-30T19:16:08.245271+08:00           35 Query     SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,        CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING   FROM INFORMATION_SCHEMA.TRIGGERS  WHERE EVENT_MANIPULATION = 'INSERT'    AND ACTION_TIMING = 'AFTER'    AND TRIGGER_SCHEMA = 't'    AND EVENT_OBJECT_TABLE = 't1'
2022-10-30T19:16:08.247396+08:00           35 Query     SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,        CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING   FROM INFORMATION_SCHEMA.TRIGGERS  WHERE EVENT_MANIPULATION = 'DELETE'    AND ACTION_TIMING = 'BEFORE'    AND TRIGGER_SCHEMA = 't'    AND EVENT_OBJECT_TABLE = 't1'
2022-10-30T19:16:08.248784+08:00           35 Query     SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,        CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING   FROM INFORMATION_SCHEMA.TRIGGERS  WHERE EVENT_MANIPULATION = 'UPDATE'    AND ACTION_TIMING = 'BEFORE'    AND TRIGGER_SCHEMA = 't'    AND EVENT_OBJECT_TABLE = 't1'
2022-10-30T19:16:08.250296+08:00           35 Query     SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,        CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING   FROM INFORMATION_SCHEMA.TRIGGERS  WHERE EVENT_MANIPULATION = 'INSERT'    AND ACTION_TIMING = 'BEFORE'    AND TRIGGER_SCHEMA = 't'    AND EVENT_OBJECT_TABLE = 't1'
2022-10-30T19:16:08.251249+08:00           35 Query     CREATE TRIGGER `pt_osc_t_t1_del` AFTER DELETE ON `t`.`t1` FOR EACH ROW DELETE IGNORE FROM `t`.`_t1_new` WHERE `t`.`_t1_new`.`id` <=> OLD.`id`
2022-10-30T19:16:08.256740+08:00           35 Query     CREATE TRIGGER `pt_osc_t_t1_upd` AFTER UPDATE ON `t`.`t1` FOR EACH ROW BEGIN DELETE IGNORE FROM `t`.`_t1_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `t`.`_t1_new`.`id` <=> OLD.`id`;REPLACE INTO `t`.`_t1_new` (`id`, `date`, `time`) VALUES (NEW.`id`, NEW.`date`, NEW.`time`);END
2022-10-30T19:16:08.262094+08:00           35 Query     CREATE TRIGGER `pt_osc_t_t1_ins` AFTER INSERT ON `t`.`t1` FOR EACH ROW REPLACE INTO `t`.`_t1_new` (`id`, `date`, `time`) VALUES (NEW.`id`, NEW.`date`, NEW.`time`)
2022-10-30T19:16:08.269085+08:00           35 Query     EXPLAIN SELECT * FROM `t`.`t1` WHERE 1=1
2022-10-30T19:16:08.270932+08:00           35 Query     EXPLAIN SELECT `id`, `date`, `time` FROM `t`.`t1` LOCK IN SHARE MODE /*explain pt-online-schema-change 5982 copy table*/
2022-10-30T19:16:08.271775+08:00           35 Query     INSERT LOW_PRIORITY IGNORE INTO `t`.`_t1_new` (`id`, `date`, `time`) SELECT `id`, `date`, `time` FROM `t`.`t1` LOCK IN SHARE MODE /*pt-online-schema-change 5982 copy table*/
2022-10-30T19:16:08.274726+08:00           35 Query     SHOW WARNINGS
2022-10-30T19:16:08.275775+08:00           35 Query     SHOW GLOBAL STATUS LIKE 'Threads_running'
2022-10-30T19:16:08.279752+08:00           35 Query     SHOW VARIABLES LIKE 'version%'
2022-10-30T19:16:08.284708+08:00           35 Query     SHOW ENGINES
2022-10-30T19:16:08.285603+08:00           35 Query     SHOW VARIABLES LIKE 'innodb_version'
2022-10-30T19:16:08.289180+08:00           35 Query     ANALYZE TABLE `t`.`_t1_new` /* pt-online-schema-change */
2022-10-30T19:16:08.296379+08:00           35 Query     RENAME TABLE `t`.`t1` TO `t`.`_t1_old`, `t`.`_t1_new` TO `t`.`t1`
2022-10-30T19:16:08.330268+08:00           35 Query     DROP TABLE IF EXISTS `t`.`_t1_old`
2022-10-30T19:16:08.341534+08:00           35 Query     DROP TRIGGER IF EXISTS `t`.`pt_osc_t_t1_del`
2022-10-30T19:16:08.345909+08:00           35 Query     DROP TRIGGER IF EXISTS `t`.`pt_osc_t_t1_upd`
2022-10-30T19:16:08.352083+08:00           35 Query     DROP TRIGGER IF EXISTS `t`.`pt_osc_t_t1_ins`
View Code
复制代码

该工具的限制：

  • 原表中必须要有主键索引或者唯一索引
  • 如果你添加一列时没有指定一个默认(default)值而是给它置为not null时，这个工具可能执行会失败，因为它不会试图为你去猜一个默认值，因此你必须要执行这个默认
  • 不支持rename语句对表进行重命名操作
  • 列不能通过删除 + 删除的方式来重命令，这样将不会copy原有列的数据到新列；
  • 如果删除外键，需要对外键名加下划线，如果删除外键fk_uid，修改语句为'drop foreign key _fk_uid'

用法：

Usage: pt-online-schema-change [OPTIONS] DSN
OPTIONS：
　　--user=xxx
　　--password=xxx
　　--socket=xxxx
　　--chunk-size=xxx :该参数默认是1000，表示每个拷贝块拷贝1000行
　　--chunk-time=xxx:动态调整块的大小，默认值是0.5
   --critical-load = A ：在每次执行后检查show global status 块，如果负载太高则中止(默认Threads_running=50)
　　--max-load=xxx:每一块执行之后检查show global status，默认thread_running=25,一旦大于25会暂缓pt任务的执行，可以视情况调大
 　
pt-online-schema-change   --user=root  --password=123  --socket=/data/3306/mysqld_3306.sock   --alter 'add  column name varchar(20) not null default ""' D=t,t=t1  --print --execute

　或者：

pt-online-schema-change --user= --password= --host= D=,t= --sock=/tmp/mysql.sock --alter '' --charset=utf8 --alter-foreign-keys-method=auto --max-load='Threads_running=35,Threads_connected=10000' --critical-load='Threads_running=200,Threads_connected=10000' --no-check-replication-filters -recursion-method=none --print --execute

使用该命令的风险：

　　1.给表增加唯一索引时一定要确保该列没有重复数据，否则会丢失数据，这里可以用count(distinct(c1))的值来判断

　　2.可能有几率触发死锁的情况

　　3.消费binlog可能有异常，要及时通知消费端

　　4.高负载情况下慎用

pt-osc强制中止清理

　　1.kill  pt-osc相关进程

　　2.drop triggers,如果出现MDL锁要进行MDL锁定位(一般为慢查询导致)

  参考：https://docs.percona.com/percona-toolkit/pt-online-schema-change.html#

　　　  https://www.cnblogs.com/danhuangpai/p/16111199.html

posted on   太白金星有点烦  阅读(60)  评论(0)  编辑  收藏  举报

相关博文：
·  52.Toad for Oracle 使用说明
·  60. analyze table和optimize table 、check table 、checksum table、repair table
·  转 pt-online-schema-change安装使用详解
·  mysql在线DDL工具--pt-online-schema-change 详细介绍
·  pt-online-schema-change使用
阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· 三行代码完成国际化适配，妙~啊~
· .NET Core 中如何实现缓存的预热？
· 如何调用 DeepSeek 的自然语言处理 API 接口并集成到在线客服系统

导航

< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
点击右上角即可分享
微信分享提示
SQL AI 助手