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
标签：10,00,pt,16,30T19,08,2022,online,schema From： https://www.cnblogs.com/zmc60/p/16842379.html