一、演示环境:
阿里ECS centos7.6 x86_64位，最小化安装
生产./pt-online-schema-change --version 软件版本:
pt-online-schema-change 3.0.13
MySQL版本官方GA 版 5.7.22
二、创建2张表 父表和子表(子表含外键)
在讲解pt-osc内部处理流程前，先创建2张表 父表和子表(子表含外键) 看看rename交换表后，子表的信息
------建表之前外键检测是开启的:
root@tidb05 16:56: [test001]> show variables like 'FOREIGN_KEY_CHECKS';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| foreign_key_checks | ON |
+--------------------+-------+
1 row in set (0.00 sec)
CREATE TABLE t_parent (
id int(11) NOT NULL auto_increment,
parent_id int,
PRIMARY KEY (id),
KEY idx_parent_id (parent_id)
) ENGINE=InnoDB;
-- 创建一个子表，外键是child_id，和父表parent_id做关联
CREATE TABLE t_child (
id int(11) NOT NULL auto_increment,
child_id int(11) default NULL,
PRIMARY KEY (id),
KEY idx_child_id (child_id),
FOREIGN KEY (child_id) REFERENCES t_parent (parent_id)
) ENGINE=InnoDB;
------子表的建表语句如下:
root@tidb05 16:55: [test001]> show create table t_child\G
*************************** 1. row ***************************
Table: t_child
Create Table: CREATE TABLE `t_child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`child_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_child_id` (`child_id`),
CONSTRAINT `t_child_ibfk_1` FOREIGN KEY (`child_id`) REFERENCES `t_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
------ 把父表改个名
rename table t_parent to t_parent_new;
-----此时子表外键child_id会自动执向新的父表表名t_parent_new，如下面所示：
root@tidb05 16:56: [test001]> show create table t_child\G
*************************** 1. row ***************************
Table: t_child
Create Table: CREATE TABLE `t_child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`child_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_child_id` (`child_id`),
CONSTRAINT `t_child_ibfk_1` FOREIGN KEY (`child_id`) REFERENCES `t_parent_new` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
三、验证pt-osc 对主表(父表)DDL操作
3.1、使用场景说明：
严格意义来讲，线上MySQL库最好不要使用外键约束。但是往往对于初创型公司，对数据库使用上还是会存在太多不规范的行为。有可能使用了MySQL的外键约束。
如果当前线上MySQL确实使用了外键，而且要对这个外键表的父表进行DDL操作（添加字段,添加索引，修改字段长度类型等）,而且线上的父表和外键子表都比较大,直接在线DDL操作,会严重锁表，这样的问题如何破呢？ 当然也是有办法处理的。
下面咱们就介绍下pt-osc工具 在尽可能减少对业务影响的情况下 对外键子表和父表进行DDL操作？
pt-online-schema-change对外键关系表进行DDL操作时 需要引入参数--alter-foreign-keys-method
但是整个工具又是如何把外键引用到新表?
在处理带有外键约束的表,以保证外键可以应用到新表.当重命名表的时候,外键关系会带到重命名后的表上。
########pt-osc工具有两种方法,可以自动找到子表,并修改约束关系，具体方法如下:
auto： 在rebuild_constraints和drop_swap两种处理方式中选择一个。
rebuild_constraints：使用 ALTER TABLE语句先删除外键约束,然后再添加.如果子表很大的话,会导致长时间的锁表阻塞。也有可能导致MySQL服务OOM
drop_swap： 执行FOREIGN_KEY_CHECKS=0,禁止外键约束,删除原表,再重命名新表。这种方式很快,也不会产生阻塞,但是有风险：
1, 在删除原表和重命名新表的短时间内,表是不存在的,程序会返回错误。
2, 如果重命名表出现错误,也不能回滚了.因为原表已经被删除。
none： 类似"drop_swap"的处理方式,但是它不删除原表,并且外键关系会随着重命名转到老表上面。
3.2、pt-osc工具内部执行流程
用pt-osc工具执行，通常内部的执行过程是：
1）创建一个临时表_t_parent_new
2）在临时表_t_parent_new中添加age字段
3）在原表t_parent上定义触发器，以便对原始表上的数据所做的更改也将应用于临时表_t_parent_new中
4）将数据从原表t_parent复制到_t_parent_new。
5）交换名字rename table t_parent to _t_parent_old, _t_parent_new to t_parent
6）删除原表 drop table _t_parent_old
7）删除增删改三个触发器
3.3 pt-osc对父表t_parent添加一个字段age tinyint(3)案例分析
现在我们要向父表t_parent添加一个字段age tinyint(3)，用pt-osc工具执行，内部的执行过程分析
对外键父表t_parent进行DDL操作时，没添加参数--alter-foreign-keys-method 的结果：
pt-online-schema-change \
--user=pt_tools --password='pt_tools321abc' --port=3306 --host=172.16.0.246 --charset=utf8 \
--alter "add column age tinyint(3) not null default '0' comment '年龄'" \
--check-alter \
--check-interval=5 \
--nocheck-replication-filters \
--chunk-size=2000 \
--chunk-size-limit=4 \
--chunk-time=1 \
--critical-load=threads_connected:4000,threads_running:300 \
--max-load=threads_connected:2500,threads_running:200 \
--max-lag=3 \
--recursion-method=none \
D=test001,t=t_parent \
--print --execute
报错：对外键关联表 主表进行DDL操作时，要求必须添加参数--alter-foreign-keys-method
具体报错内容如下:
[root@archery bin]# pt-online-schema-change --user=pt_tools --password='pt_tools321abc' --port=3306 --host=172.16.0.246 --charset=utf8 --alter "add column age tinyint(3) not null default '0' comment '年龄'" --check-alter --check-interval=5 --nocheck-replication-filters --chunk-size=2000 --chunk-size-limit=4 --chunk-time=1 --critical-load=threads_connected:4000,threads_running:300 --max-load=threads_connected:2500,threads_running:200 --max-lag=3 --recursion-method=none D=test001,t=t_parent --print --execute
No slaves found. See --recursion-method if host tidb05 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Child tables:
`test001`.`t_child` (approx. 2 rows)
You did not specify --alter-foreign-keys-method, but there are foreign keys that reference the table. Please read the tool's documentation carefully.
3.3和3.4都是采用下面的方法:--alter-foreign-keys-method=rebuild_constraints进行验证测试
3.3.1、执行前必须先进行--dry-run 检测下语法是否正确,检测通过后再执行--execute:
pt-online-schema-change \
--user=pt_tools --password='pt_tools321abc' --port=3306 --host=172.16.0.246 --charset=utf8 \
--alter "add column age tinyint(3) not null default '0' comment '年龄'" \
--alter-foreign-keys-method=rebuild_constraints \
--check-alter \
--check-interval=5 \
--nocheck-replication-filters \
--chunk-size=2000 \
--chunk-size-limit=4 \
--chunk-time=1 \
--critical-load=threads_connected:4000,threads_running:300 \
--max-load=threads_connected:2500,threads_running:200 \
--max-lag=3 \
--recursion-method=none \
D=test001,t=t_parent \
--print --dry-run
[root@archery bin]# pt-online-schema-change --user=pt_tools --password='pt_tools321abc' --port=3306 --host=172.16.0.246 --charset=utf8 --alter "add column age tinyint(3) not null default '0' comment '年龄'" --alter-foreign-keys-method=rebuild_constraints --check-alter --check-interval=5 --nocheck-replication-filters --chunk-size=2000 --chunk-size-limit=4 --chunk-time=1 --critical-load=threads_connected:4000,threads_running:300 --max-load=threads_connected:2500,threads_running:200 --max-lag=3 --recursion-method=none D=test001,t=t_parent --print --dry-run
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Child tables:
`test001`.`t_child` (approx. 2 rows)
Will use the rebuild_constraints method to update foreign keys.
Starting a dry run. `test001`.`t_parent` will not be altered. Specify --execute instead of --dry-run to alter the table.
Creating new table...
CREATE TABLE `test001`.`_t_parent_new` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
Created new table test001._t_parent_new OK.
Altering new table...
ALTER TABLE `test001`.`_t_parent_new` add column age tinyint(3) not null default '0' comment 'å¹´é¾'
Altered `test001`.`_t_parent_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
INSERT LOW_PRIORITY IGNORE INTO `test001`.`_t_parent_new` (`id`, `parent_id`) SELECT `id`, `parent_id` FROM `test001`.`t_parent` LOCK IN SHARE MODE /*pt-online-schema-change 26935 copy table*/
Not swapping tables because this is a dry run.
Not rebuilding foreign key constraints because this is a dry run.
ALTER TABLE `test001`.`t_child` DROP FOREIGN KEY `t_child_ibfk_1`, ADD CONSTRAINT `_t_child_ibfk_1` FOREIGN KEY (`child_id`) REFERENCES `test001`.`t_parent` (`parent_id`)
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_del`
DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_upd`
DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_ins`
2022-01-15T17:49:06 Dropping new table...
DROP TABLE IF EXISTS `test001`.`_t_parent_new`;
2022-01-15T17:49:06 Dropped new table OK.
Dry run complete. `test001`.`t_parent` was not altered.
此时172.16.0.246 tidb05实例开启了general log来跟踪MySQL具体的执行过程日志如下:
2022-01-15T17:53:09.291016+08:00 20168 Connect pt_tools@172.16.0.13 on test001 using TCP/IP
2022-01-15T17:53:09.291382+08:00 20168 Query /*!40101 SET NAMES "utf8"*/
2022-01-15T17:53:09.291711+08:00 20168 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2022-01-15T17:53:09.293578+08:00 20168 Query SET SESSION innodb_lock_wait_timeout=1
2022-01-15T17:53:09.293796+08:00 20168 Query SHOW VARIABLES LIKE 'lock\_wait_timeout'
2022-01-15T17:53:09.294964+08:00 20168 Query SET SESSION lock_wait_timeout=60
2022-01-15T17:53:09.295158+08:00 20168 Query SHOW VARIABLES LIKE 'wait\_timeout'
2022-01-15T17:53:09.296218+08:00 20168 Query SET SESSION wait_timeout=10000
2022-01-15T17:53:09.296412+08:00 20168 Query SELECT @@SQL_MODE
2022-01-15T17:53:09.296605+08:00 20168 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2022-01-15T17:53:09.296830+08:00 20168 Query SELECT @@server_id /*!50038 , @@hostname*/
2022-01-15T17:53:09.297949+08:00 20169 Connect pt_tools@172.16.0.13 on test001 using TCP/IP
2022-01-15T17:53:09.298177+08:00 20169 Query /*!40101 SET NAMES "utf8"*/
2022-01-15T17:53:09.298399+08:00 20169 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2022-01-15T17:53:09.299965+08:00 20169 Query SET SESSION innodb_lock_wait_timeout=1
2022-01-15T17:53:09.300167+08:00 20169 Query SHOW VARIABLES LIKE 'lock\_wait_timeout'
2022-01-15T17:53:09.301249+08:00 20169 Query SET SESSION lock_wait_timeout=60
2022-01-15T17:53:09.301451+08:00 20169 Query SHOW VARIABLES LIKE 'wait\_timeout'
2022-01-15T17:53:09.302503+08:00 20169 Query SET SESSION wait_timeout=10000
2022-01-15T17:53:09.302672+08:00 20169 Query SELECT @@SQL_MODE
2022-01-15T17:53:09.302836+08:00 20169 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2022-01-15T17:53:09.303015+08:00 20169 Query SELECT @@server_id /*!50038 , @@hostname*/
2022-01-15T17:53:09.303284+08:00 20168 Query SHOW VARIABLES LIKE 'wsrep_on'
2022-01-15T17:53:09.304499+08:00 20168 Query SHOW VARIABLES LIKE 'version%'
2022-01-15T17:53:09.306583+08:00 20168 Query SHOW ENGINES
2022-01-15T17:53:09.307027+08:00 20168 Query SHOW VARIABLES LIKE 'innodb_version'
2022-01-15T17:53:09.308662+08:00 20168 Query SHOW VARIABLES LIKE 'innodb_stats_persistent'
2022-01-15T17:53:09.309948+08:00 20168 Query SELECT CONCAT(@@hostname, @@port)
2022-01-15T17:53:09.310400+08:00 20168 Query SHOW TABLES FROM `test001` LIKE 't\_parent'
2022-01-15T17:53:09.310760+08:00 20168 Query SELECT VERSION()
2022-01-15T17:53:09.310974+08:00 20168 Query SHOW TRIGGERS FROM `test001` LIKE 't\_parent'
2022-01-15T17:53:09.311530+08:00 20168 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2022-01-15T17:53:09.311768+08:00 20168 Query USE `test001`
2022-01-15T17:53:09.311980+08:00 20168 Query SHOW CREATE TABLE `test001`.`t_parent`
2022-01-15T17:53:09.312280+08:00 20168 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-01-15T17:53:09.312703+08:00 20168 Query EXPLAIN SELECT * FROM `test001`.`t_parent` WHERE 1=1
2022-01-15T17:53:09.313207+08:00 20168 Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='test001' AND referenced_table_name='t_parent'
2022-01-15T17:53:09.486414+08:00 20168 Query EXPLAIN SELECT * FROM `test001`.`t_child` WHERE 1=1
2022-01-15T17:53:09.487439+08:00 20168 Query SHOW VARIABLES LIKE 'wsrep_on'
2022-01-15T17:53:09.488954+08:00 20168 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2022-01-15T17:53:09.489154+08:00 20168 Query USE `test001`
2022-01-15T17:53:09.489389+08:00 20168 Query SHOW CREATE TABLE `test001`.`t_parent`
2022-01-15T17:53:09.489648+08:00 20168 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-01-15T17:53:09.489931+08:00 20168 Query CREATE TABLE `test001`.`_t_parent_new` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
2022-01-15T17:53:09.500410+08:00 20168 Query ALTER TABLE `test001`.`_t_parent_new` add column age tinyint(3) not null default '0' comment '年龄'
2022-01-15T17:53:09.517025+08:00 20168 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2022-01-15T17:53:09.517223+08:00 20168 Query USE `test001`
2022-01-15T17:53:09.517447+08:00 20168 Query SHOW CREATE TABLE `test001`.`_t_parent_new`
2022-01-15T17:53:09.517725+08:00 20168 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-01-15T17:53:09.519149+08:00 20168 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 = 'test001' AND EVENT_OBJECT_TABLE = 't_parent'
2022-01-15T17:53:09.519900+08:00 20168 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 = 'test001' AND EVENT_OBJECT_TABLE = 't_parent'
2022-01-15T17:53:09.520541+08:00 20168 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 = 'test001' AND EVENT_OBJECT_TABLE = 't_parent'
2022-01-15T17:53:09.521161+08:00 20168 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 = 'test001' AND EVENT_OBJECT_TABLE = 't_parent'
2022-01-15T17:53:09.521758+08:00 20168 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 = 'test001' AND EVENT_OBJECT_TABLE = 't_parent'
2022-01-15T17:53:09.522324+08:00 20168 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 = 'test001' AND EVENT_OBJECT_TABLE = 't_parent'
2022-01-15T17:53:09.523062+08:00 20168 Query EXPLAIN SELECT * FROM `test001`.`t_parent` WHERE 1=1
2022-01-15T17:53:09.523619+08:00 20168 Query SHOW VARIABLES LIKE 'version%'
2022-01-15T17:53:09.524903+08:00 20168 Query SHOW ENGINES
2022-01-15T17:53:09.525347+08:00 20168 Query SHOW VARIABLES LIKE 'innodb_version'
2022-01-15T17:53:09.526764+08:00 20168 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2022-01-15T17:53:09.526958+08:00 20168 Query USE `test001`
2022-01-15T17:53:09.527165+08:00 20168 Query SHOW CREATE TABLE `test001`.`t_child`
2022-01-15T17:53:09.527444+08:00 20168 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-01-15T17:53:09.527855+08:00 20168 Query SHOW TABLES FROM `test001` LIKE '\_t\_parent\_new'
2022-01-15T17:53:09.528255+08:00 20168 Query DROP TABLE IF EXISTS `test001`.`_t_parent_new`
2022-01-15T17:53:09.533969+08:00 20169 Quit
2022-01-15T17:53:09.534038+08:00 20168 Quit
由于添加了参数--dry-run 从gener_log日志中看出并没有创建三个触发器以及迁移老表的数据到临时表，以及后续的raname动作和删除触发器的动作
3.3.2、在--dry-run 检测下语法通过后,添加参数--execute 操作
具体执行指令如下:
pt-online-schema-change \
--user=pt_tools --password='pt_tools321abc' --port=3306 --host=172.16.0.246 --charset=utf8 \
--alter "add column age tinyint(3) not null default '0' comment '年龄'" \
--alter-foreign-keys-method=rebuild_constraints \
--check-alter \
--check-interval=5 \
--nocheck-replication-filters \
--chunk-size=2000 \
--chunk-size-limit=4 \
--chunk-time=1 \
--critical-load=threads_connected:4000,threads_running:300 \
--max-load=threads_connected:2500,threads_running:200 \
--max-lag=3 \
--recursion-method=none \
D=test001,t=t_parent \
--print --execute
[root@archery bin]#
[root@archery bin]# pt-online-schema-change --user=pt_tools --password='pt_tools321abc' --port=3306 --host=172.16.0.246 --charset=utf8 --alter "add column age tinyint(3) not null default '0' comment '年龄'" --alter-foreign-keys-method=rebuild_constraints --check-alter --check-interval=5 --nocheck-replication-filters --chunk-size=2000 --chunk-size-limit=4 --chunk-time=1 --critical-load=threads_connected:4000,threads_running:300 --max-load=threads_connected:2500,threads_running:200 --max-lag=3 --recursion-method=none D=test001,t=t_parent --print --execute
No slaves found. See --recursion-method if host tidb05 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Child tables:
`test001`.`t_child` (approx. 2 rows)
Will use the rebuild_constraints method to update foreign keys.
Altering `test001`.`t_parent`...
Creating new table...
CREATE TABLE `test001`.`_t_parent_new` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
Created new table test001._t_parent_new OK.
Altering new table...
ALTER TABLE `test001`.`_t_parent_new` add column age tinyint(3) not null default '0' comment 'å¹´é¾'
Altered `test001`.`_t_parent_new` OK.
2022-01-15T17:55:46 Creating triggers...
2022-01-15T17:55:46 Created triggers OK.
2022-01-15T17:55:46 Copying approximately 2 rows...
INSERT LOW_PRIORITY IGNORE INTO `test001`.`_t_parent_new` (`id`, `parent_id`) SELECT `id`, `parent_id` FROM `test001`.`t_parent` LOCK IN SHARE MODE /*pt-online-schema-change 27268 copy table*/
2022-01-15T17:55:46 Copied rows OK.
2022-01-15T17:55:46 Analyzing new table...
2022-01-15T17:55:46 Swapping tables...
RENAME TABLE `test001`.`t_parent` TO `test001`.`_t_parent_old`, `test001`.`_t_parent_new` TO `test001`.`t_parent`
2022-01-15T17:55:46 Swapped original and new tables OK.
2022-01-15T17:55:46 Rebuilding foreign key constraints...
ALTER TABLE `test001`.`t_child` DROP FOREIGN KEY `t_child_ibfk_1`, ADD CONSTRAINT `_t_child_ibfk_1` FOREIGN KEY (`child_id`) REFERENCES `test001`.`t_parent` (`parent_id`)
2022-01-15T17:55:46 Rebuilt foreign key constraints OK.
2022-01-15T17:55:46 Dropping old table...
DROP TABLE IF EXISTS `test001`.`_t_parent_old`
2022-01-15T17:55:46 Dropped old table `test001`.`_t_parent_old` OK.
2022-01-15T17:55:46 Dropping triggers...
DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_del`
DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_upd`
DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_ins`
2022-01-15T17:55:46 Dropped triggers OK.
Successfully altered `test001`.`t_parent`.
此时172.16.0.246 tidb05实例开启了general log来跟踪MySQL具体的执行过程：
2022-01-15T17:55:46.646041+08:00 20484 Connect pt_tools@172.16.0.13 on test001 using TCP/IP
2022-01-15T17:55:46.646378+08:00 20484 Query /*!40101 SET NAMES "utf8"*/
2022-01-15T17:55:46.646658+08:00 20484 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2022-01-15T17:55:46.648353+08:00 20484 Query SET SESSION innodb_lock_wait_timeout=1
2022-01-15T17:55:46.648550+08:00 20484 Query SHOW VARIABLES LIKE 'lock\_wait_timeout'
2022-01-15T17:55:46.649780+08:00 20484 Query SET SESSION lock_wait_timeout=60
2022-01-15T17:55:46.649980+08:00 20484 Query SHOW VARIABLES LIKE 'wait\_timeout'
2022-01-15T17:55:46.651279+08:00 20484 Query SET SESSION wait_timeout=10000
2022-01-15T17:55:46.651546+08:00 20484 Query SELECT @@SQL_MODE
2022-01-15T17:55:46.651772+08:00 20484 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2022-01-15T17:55:46.651974+08:00 20484 Query SELECT @@server_id /*!50038 , @@hostname*/
2022-01-15T17:55:46.653008+08:00 20485 Connect pt_tools@172.16.0.13 on test001 using TCP/IP
2022-01-15T17:55:46.653237+08:00 20485 Query /*!40101 SET NAMES "utf8"*/
2022-01-15T17:55:46.653447+08:00 20485 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2022-01-15T17:55:46.655170+08:00 20485 Query SET SESSION innodb_lock_wait_timeout=1
2022-01-15T17:55:46.655414+08:00 20485 Query SHOW VARIABLES LIKE 'lock\_wait_timeout'
2022-01-15T17:55:46.657142+08:00 20485 Query SET SESSION lock_wait_timeout=60
2022-01-15T17:55:46.657347+08:00 20485 Query SHOW VARIABLES LIKE 'wait\_timeout'
2022-01-15T17:55:46.658484+08:00 20485 Query SET SESSION wait_timeout=10000
2022-01-15T17:55:46.658681+08:00 20485 Query SELECT @@SQL_MODE
2022-01-15T17:55:46.658871+08:00 20485 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2022-01-15T17:55:46.659062+08:00 20485 Query SELECT @@server_id /*!50038 , @@hostname*/
2022-01-15T17:55:46.659320+08:00 20484 Query SHOW VARIABLES LIKE 'wsrep_on'
2022-01-15T17:55:46.660968+08:00 20484 Query SHOW VARIABLES LIKE 'version%'
2022-01-15T17:55:46.662213+08:00 20484 Query SHOW ENGINES
2022-01-15T17:55:46.662628+08:00 20484 Query SHOW VARIABLES LIKE 'innodb_version'
2022-01-15T17:55:46.664063+08:00 20484 Query SHOW VARIABLES LIKE 'innodb_stats_persistent'
2022-01-15T17:55:46.665321+08:00 20484 Query SHOW GLOBAL STATUS LIKE 'threads_running'
2022-01-15T17:55:46.666204+08:00 20484 Query SHOW GLOBAL STATUS LIKE 'threads_connected'
2022-01-15T17:55:46.666965+08:00 20484 Query SHOW GLOBAL STATUS LIKE 'threads_running'
2022-01-15T17:55:46.667674+08:00 20484 Query SHOW GLOBAL STATUS LIKE 'threads_connected'
2022-01-15T17:55:46.668504+08:00 20484 Query SELECT CONCAT(@@hostname, @@port)
2022-01-15T17:55:46.668928+08:00 20484 Query SHOW TABLES FROM `test001` LIKE 't\_parent'
2022-01-15T17:55:46.669256+08:00 20484 Query SELECT VERSION()
2022-01-15T17:55:46.669468+08:00 20484 Query SHOW TRIGGERS FROM `test001` LIKE 't\_parent'
2022-01-15T17:55:46.669967+08:00 20484 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2022-01-15T17:55:46.670180+08:00 20484 Query USE `test001`
2022-01-15T17:55:46.670384+08:00 20484 Query SHOW CREATE TABLE `test001`.`t_parent`
2022-01-15T17:55:46.670666+08:00 20484 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-01-15T17:55:46.671137+08:00 20484 Query EXPLAIN SELECT * FROM `test001`.`t_parent` WHERE 1=1
2022-01-15T17:55:46.671585+08:00 20484 Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='test001' AND referenced_table_name='t_parent'
2022-01-15T17:55:46.833960+08:00 20484 Query EXPLAIN SELECT * FROM `test001`.`t_child` WHERE 1=1
2022-01-15T17:55:46.834945+08:00 20484 Query SHOW VARIABLES LIKE 'wsrep_on'
2022-01-15T17:55:46.836546+08:00 20484 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2022-01-15T17:55:46.836757+08:00 20484 Query USE `test001`
2022-01-15T17:55:46.836978+08:00 20484 Query SHOW CREATE TABLE `test001`.`t_parent`
2022-01-15T17:55:46.837250+08:00 20484 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-01-15T17:55:46.837528+08:00 20484 Query CREATE TABLE `test001`.`_t_parent_new` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
2022-01-15T17:55:46.848859+08:00 20484 Query ALTER TABLE `test001`.`_t_parent_new` add column age tinyint(3) not null default '0' comment '年龄'
2022-01-15T17:55:46.868632+08:00 20484 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2022-01-15T17:55:46.868838+08:00 20484 Query USE `test001`
2022-01-15T17:55:46.869076+08:00 20484 Query SHOW CREATE TABLE `test001`.`_t_parent_new`
2022-01-15T17:55:46.869327+08:00 20484 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-01-15T17:55:46.870825+08:00 20484 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 = 'test001' AND EVENT_OBJECT_TABLE = 't_parent'
2022-01-15T17:55:46.871535+08:00 20484 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 = 'test001' AND EVENT_OBJECT_TABLE = 't_parent'
2022-01-15T17:55:46.872194+08:00 20484 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 = 'test001' AND EVENT_OBJECT_TABLE = 't_parent'
2022-01-15T17:55:46.872860+08:00 20484 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 = 'test001' AND EVENT_OBJECT_TABLE = 't_parent'
2022-01-15T17:55:46.873424+08:00 20484 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 = 'test001' AND EVENT_OBJECT_TABLE = 't_parent'
2022-01-15T17:55:46.873981+08:00 20484 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 = 'test001' AND EVENT_OBJECT_TABLE = 't_parent'
2022-01-15T17:55:46.874594+08:00 20484 Query CREATE TRIGGER `pt_osc_test001_t_parent_del` AFTER DELETE ON `test001`.`t_parent` FOR EACH ROW DELETE IGNORE FROM `test001`.`_t_parent_new` WHERE `test001`.`_t_parent_new`.`id` <=> OLD.`id`
2022-01-15T17:55:46.877241+08:00 20484 Query CREATE TRIGGER `pt_osc_test001_t_parent_upd` AFTER UPDATE ON `test001`.`t_parent` FOR EACH ROW BEGIN DELETE IGNORE FROM `test001`.`_t_parent_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `test001`.`_t_parent_new`.`id` <=> OLD.`id`;REPLACE INTO `test001`.`_t_parent_new` (`id`, `parent_id`) VALUES (NEW.`id`, NEW.`parent_id`);END
2022-01-15T17:55:46.880265+08:00 20484 Query CREATE TRIGGER `pt_osc_test001_t_parent_ins` AFTER INSERT ON `test001`.`t_parent` FOR EACH ROW REPLACE INTO `test001`.`_t_parent_new` (`id`, `parent_id`) VALUES (NEW.`id`, NEW.`parent_id`)
2022-01-15T17:55:46.883653+08:00 20484 Query EXPLAIN SELECT * FROM `test001`.`t_parent` WHERE 1=1
2022-01-15T17:55:46.884569+08:00 20484 Query EXPLAIN SELECT `id`, `parent_id` FROM `test001`.`t_parent` LOCK IN SHARE MODE /*explain pt-online-schema-change 27268 copy table*/
2022-01-15T17:55:46.884951+08:00 20484 Query INSERT LOW_PRIORITY IGNORE INTO `test001`.`_t_parent_new` (`id`, `parent_id`) SELECT `id`, `parent_id` FROM `test001`.`t_parent` LOCK IN SHARE MODE /*pt-online-schema-change 27268 copy table*/
2022-01-15T17:55:46.886511+08:00 20484 Query SHOW WARNINGS
2022-01-15T17:55:46.886899+08:00 20484 Query SHOW GLOBAL STATUS LIKE 'threads_connected'
2022-01-15T17:55:46.887864+08:00 20484 Query SHOW GLOBAL STATUS LIKE 'threads_running'
2022-01-15T17:55:46.888683+08:00 20484 Query SHOW VARIABLES LIKE 'version%'
2022-01-15T17:55:46.889913+08:00 20484 Query SHOW ENGINES
2022-01-15T17:55:46.890323+08:00 20484 Query SHOW VARIABLES LIKE 'innodb_version'
2022-01-15T17:55:46.891611+08:00 20484 Query ANALYZE TABLE `test001`.`_t_parent_new` /* pt-online-schema-change */
2022-01-15T17:55:46.893987+08:00 20484 Query RENAME TABLE `test001`.`t_parent` TO `test001`.`_t_parent_old`, `test001`.`_t_parent_new` TO `test001`.`t_parent`
2022-01-15T17:55:46.909592+08:00 20484 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2022-01-15T17:55:46.909807+08:00 20484 Query USE `test001`
2022-01-15T17:55:46.910011+08:00 20484 Query SHOW CREATE TABLE `test001`.`t_child`
2022-01-15T17:55:46.910278+08:00 20484 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-01-15T17:55:46.910550+08:00 20484 Query ALTER TABLE `test001`.`t_child` DROP FOREIGN KEY `t_child_ibfk_1`, ADD CONSTRAINT `_t_child_ibfk_1` FOREIGN KEY (`child_id`) REFERENCES `test001`.`t_parent` (`parent_id`)
2022-01-15T17:55:46.934217+08:00 20484 Query DROP TABLE IF EXISTS `test001`.`_t_parent_old`
2022-01-15T17:55:46.940858+08:00 20484 Query DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_del`
2022-01-15T17:55:46.941111+08:00 20484 Query DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_upd`
2022-01-15T17:55:46.941339+08:00 20484 Query DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_ins`
根据上面的general_log日志分析对父表t_parent添加一个字段age tinyint(3)，用pt-osc工具执行的内部执行过程：
A.查看当前tidb05实例 threads_running，threads_connected数值。
和pt-online-schema-change --critical-load，--max-load值进行比对，要是大于pt-online-schema-change指定的数值，则pt-online-schema-change进入等待模式,直到pt-online-schema-change指定的critical-load，max-load参数值大于目标库中的数值，才继续执行
SHOW GLOBAL STATUS LIKE 'threads_running'
SHOW GLOBAL STATUS LIKE 'threads_connected'
SHOW GLOBAL STATUS LIKE 'threads_running'
SHOW GLOBAL STATUS LIKE 'threads_connected'
B.确定目标库中t_parent表是否存在，以及确定目标库中t_parent表是否存在关联触发器，一旦目标库中t_parent表不存在或者 提前存在关联触发器,则pt-online-schema-change 将提示父表t_parent存在触发器,退出执行
SHOW TABLES FROM `test001` LIKE 't\_parent'
SELECT VERSION()
SHOW TRIGGERS FROM `test001` LIKE 't\_parent'
C. 检查父表记录数和和父表关联的子表
EXPLAIN SELECT * FROM `test001`.`t_parent` WHERE 1=1
SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='test001' AND referenced_table_name='t_parent'
D.根据原表表结构建立临时表_t_parent_new
SHOW CREATE TABLE `test001`.`t_parent`
CREATE TABLE `test001`.`_t_parent_new` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
F.给test001._t_parent_new添加字段age
ALTER TABLE `test001`.`_t_parent_new` add column age tinyint(3) not null default '0' comment '年龄'
G.创建delete,update,insert 3个触发器,检测源父表t_parent记录变化，同步写入到临时表_t_parent_new:
CREATE TRIGGER `pt_osc_test001_t_parent_del` AFTER DELETE ON `test001`.`t_parent` FOR EACH ROW DELETE IGNORE FROM `test001`.`_t_parent_new` WHERE `test001`.`_t_parent_new`.`id` <=> OLD.`id`
CREATE TRIGGER `pt_osc_test001_t_parent_upd` AFTER UPDATE ON `test001`.`t_parent` FOR EACH ROW BEGIN DELETE IGNORE FROM `test001`.`_t_parent_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `test001`.`_t_parent_new`.`id` <=> OLD.`id`;REPLACE INTO `test001`.`_t_parent_new` (`id`, `parent_id`) VALUES (NEW.`id`, NEW.`parent_id`);END
CREATE TRIGGER `pt_osc_test001_t_parent_ins` AFTER INSERT ON `test001`.`t_parent` FOR EACH ROW REPLACE INTO `test001`.`_t_parent_new` (`id`, `parent_id`) VALUES (NEW.`id`, NEW.`parent_id`)
INSERT LOW_PRIORITY IGNORE INTO `test001`.`_t_parent_new` (`id`, `parent_id`) SELECT `id`, `parent_id` FROM `test001`.`t_parent` LOCK IN SHARE MODE /*pt-online-schema-change 27268 copy table*/
H.进行rename 改原表t_parent为_t_parent_old,临时表_t_parent_new为t_parent
ANALYZE TABLE `test001`.`_t_parent_new` /* pt-online-schema-change */
RENAME TABLE `test001`.`t_parent` TO `test001`.`_t_parent_old`, `test001`.`_t_parent_new` TO `test001`.`t_parent`
风险点提示:
rename交换名字后，子表的外键会执向_t_parent_old，并不会变成t_parent，这将极有带来数据不一致的后果。
验证测试:
验证测试:
root@tidb05 18:46: [test001]> create table _t_parent_new as select * from t_parent;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@tidb05 18:46: [test001]> show create table t_child \G
*************************** 1. row ***************************
Table: t_child
Create Table: CREATE TABLE `t_child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`child_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_child_id` (`child_id`),
CONSTRAINT `_t_child_ibfk_1` FOREIGN KEY (`child_id`) REFERENCES `t_parent` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
root@tidb05 18:47: [test001]> RENAME TABLE `test001`.`t_parent` TO `test001`.`_t_parent_old`, `test001`.`_t_parent_new` TO `test001`.`t_parent`;
Query OK, 0 rows affected (0.01 sec)
root@tidb05 18:48: [test001]> show create table t_child \G
*************************** 1. row ***************************
Table: t_child
Create Table: CREATE TABLE `t_child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`child_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_child_id` (`child_id`),
CONSTRAINT `_t_child_ibfk_1` FOREIGN KEY (`child_id`) REFERENCES `_t_parent_old` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
8.查看子表表结构,删除子表原先的外键t_child_ibfk_1,新加一个外键_t_child_ibfk_1 关联到新表t_parent:
SHOW CREATE TABLE `test001`.`t_child`
ALTER TABLE `test001`.`t_child` DROP FOREIGN KEY `t_child_ibfk_1`, ADD CONSTRAINT `_t_child_ibfk_1` FOREIGN KEY (`child_id`) REFERENCES `test001`.`t_parent` (`parent_id`)
说明：当子表非常大，这样的操作要对子表加锁，造成严重的阻塞,以及极有可能导致MySQL服务OOM掉
I.删除老表和删除触发器:
DROP TABLE IF EXISTS `test001`.`_t_parent_old`
DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_del`
DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_upd`
DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_ins`
3.4、采用线上库的主表t_customer进行pt-osc 修改字段类型进行测试分析
3.4.1、查看和主表t_customer关联的子表有哪些？
查看父表关联的子表sql:(发现存在12张表关联的这个t_customer父表，顿时想骂娘了，这种玩法简直是裤裆里面放炸弹，找死呢)
3.4.2、--dry-run 试运行pt-osc ：
pt-online-schema-change \
--user=pt_tools --password='pt_tools321abc' --port=3306 --host=172.16.0.246 --charset=utf8 \
--alter "modify column additional_files varchar(30) not null NOT NULL DEFAULT '' COMMENT '发回修改-需要补充的资料'" \
--alter-foreign-keys-method=rebuild_constraints \
--check-alter \
--check-interval=5 \
--nocheck-replication-filters \
--chunk-size=2000 \
--chunk-size-limit=4 \
--chunk-time=1 \
--critical-load=threads_connected:4000,threads_running:300 \
--max-load=threads_connected:2500,threads_running:200 \
--max-lag=3 \
--recursion-method=none \
D=db_,t=t_customer \
--print --dry-run
为了客户数据安全，此处就截图了：
发现关联得12张子表都要删除来源主键进行重建主键并且指向新的主表t_customer。整个操作过程风险极大。禁止这么干。他的代价要比直接在线针对单表操作操作大的多。
经过测试验证，对原表删除外键和新增外键 其实原理就是对原表进行添加索引和删除索引。都要对表进行加锁，这个过程表只能读。
3.4.3、--alter-foreign-keys-method=rebuild_constraints总结：
pt-osc在外键关联父表（主表）进行DDL操作时，要是和父表(主表)关联的子表很少(最好不多于2张),并且子表的记录数少于20万的情况下。
可以考虑采用pt-online-schema-change 参数进行添加--alter-foreign-keys-method=rebuild_constraints
要是主表关联的子表数量太多，而且子表的数据量有很大时，杜绝采用pt-online-schema-change --alter-foreign-keys-method=rebuild_constraints 方法操作。
3.5、采用pt-online-schema-change --alter-foreign-keys-method=drop_swap 方法对外键子表DDL操作：
3.5采用下面的方法:--alter-foreign-keys-method=drop_swap进行验证测试
3.5.1对删除子表外键和添加子表外键：
删除子表的外键相当于把父表的索引删除掉了
root@tidb05 23:59: [test001]> ALTER TABLE `test001`.`t_child` DROP FOREIGN KEY `_t_child_ibfk_1`;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@tidb05 23:45: [test001]> show create table t_parent\G
*************************** 1. row ***************************
Table: t_parent
Create Table: CREATE TABLE `t_parent` (
`id` int(11) NOT NULL DEFAULT '0',
`parent_id` int(11) DEFAULT NULL,
`age` tinyint(3) NOT NULL DEFAULT '0' COMMENT '年龄'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
root@tidb05 00:00: [test001]> show create table t_child\G
*************************** 1. row ***************************
Table: t_child
Create Table: CREATE TABLE `t_child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`child_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_child_id` (`child_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
在对子表添加外键：报错提示主表字段parent_id索引丢失：
root@tidb05 00:01: [test001]> ALTER TABLE `test001`.`t_child` ADD CONSTRAINT `t_child_ibfk_1` FOREIGN KEY (`child_id`) REFERENCES `test001`.`t_parent` (`parent_id`);
ERROR 1822 (HY000): Failed to add the foreign key constaint. Missing index for constraint 't_child_ibfk_1' in the referenced table 't_parent'
root@tidb05 00:02: [test001]> alter table t_parent add index idx_parent_id(parent_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@tidb05 00:03: [test001]> ALTER TABLE `test001`.`t_child` ADD CONSTRAINT `t_child_ibfk_1` FOREIGN KEY (`child_id`) REFERENCES `test001`.`t_parent` (`parent_id`);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
子表外键添加完成：
root@tidb05 00:03: [test001]> show create table `test001`.`t_child` \G
*************************** 1. row ***************************
Table: t_child
Create Table: CREATE TABLE `t_child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`child_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_child_id` (`child_id`),
CONSTRAINT `t_child_ibfk_1` FOREIGN KEY (`child_id`) REFERENCES `t_parent` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
3.5.2、pt-osc 对外键父表（主表）t_parent操作：
pt-online-schema-change \
--user=pt_tools --password='pt_tools321abc' --port=3306 --host=172.16.0.246 --charset=utf8 \
--alter "add column age tinyint(3) not null default '0' comment '年龄'" \
--alter-foreign-keys-method=drop_swap \
--check-alter \
--check-interval=5 \
--nocheck-replication-filters \
--chunk-size=2000 \
--chunk-size-limit=4 \
--chunk-time=1 \
--critical-load=threads_connected:4000,threads_running:300 \
--max-load=threads_connected:2500,threads_running:200 \
--max-lag=3 \
--recursion-method=none \
D=test001,t=t_parent \
--print --execute
[root@archery ~]# pt-online-schema-change --user=pt_tools --password='pt_tools321abc' --port=3306 --host=172.16.0.246 --charset=utf8 --alter "add column age tinyint(3) not null default '0' comment '年龄'" --alter-foreign-keys-method=drop_swap --check-alter --check-interval=5 --nocheck-replication-filters --chunk-size=2000 --chunk-size-limit=4 --chunk-time=1 --critical-load=threads_connected:4000,threads_running:300 --max-load=threads_connected:2500,threads_running:200 --max-lag=3 --recursion-method=none D=test001,t=t_parent --print --execute
No slaves found. See --recursion-method if host tidb05 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Child tables:
`test001`.`t_child` (approx. 1 rows)
Will use the drop_swap method to update foreign keys.
Altering `test001`.`t_parent`...
Creating new table...
CREATE TABLE `test001`.`_t_parent_new` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Created new table test001._t_parent_new OK.
Altering new table...
ALTER TABLE `test001`.`_t_parent_new` add column age tinyint(3) not null default '0' comment 'å¹´é¾'
Altered `test001`.`_t_parent_new` OK.
2022-01-16T00:22:29 Creating triggers...
2022-01-16T00:22:29 Created triggers OK.
2022-01-16T00:22:29 Copying approximately 1 rows...
INSERT LOW_PRIORITY IGNORE INTO `test001`.`_t_parent_new` (`id`, `parent_id`) SELECT `id`, `parent_id` FROM `test001`.`t_parent` LOCK IN SHARE MODE /*pt-online-schema-change 14179 copy table*/
2022-01-16T00:22:29 Copied rows OK.
2022-01-16T00:22:29 Drop-swapping tables...
2022-01-16T00:22:29 Analyzing new table...
SET foreign_key_checks=0
DROP TABLE IF EXISTS `test001`.`t_parent`
RENAME TABLE `test001`.`_t_parent_new` TO `test001`.`t_parent`
2022-01-16T00:22:29 Dropped and swapped tables OK.
Not dropping old table because --no-drop-old-table was specified.
2022-01-16T00:22:29 Dropping triggers...
DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_del`
DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_upd`
DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_ins`
2022-01-16T00:22:29 Dropped triggers OK.
Successfully altered `test001`.`t_parent`.
3.5.3、执行流程：
1）创建一个临时表_t_parent_new
2）在临时表_t_parent_new中添加age字段
3）在原表parent上定义触发器，以便对原始表上的数据所做的更改也将应用于临时表_parent_new中
4）将数据从原表parent复制到_t_parent_new
5) SET foreign_key_checks=0 #关闭外键检查
6) DROP TABLE IF EXISTS `test001`.`t_parent` ##drop掉原表，表大的话，会阻塞，删除的速度较慢的话（第7步不会执行），业务会受影响影响业务，同时造成复制延迟。这也是比较危险的。
7）RENAME TABLE `test001`.`_t_parent_new` TO `test001`.`t_parent` #把临时表该名称为原表
8）删除触发器
DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_del`
DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_upd`
DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_ins`
9）set FOREIGN_KEY_CHECKS=ON;
最终的外键子表表结构：
root@tidb05 00:30: [test001]> show create table t_child\G
*************************** 1. row ***************************
Table: t_child
Create Table: CREATE TABLE `t_child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`child_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_child_id` (`child_id`),
CONSTRAINT `t_child_ibfk_1` FOREIGN KEY (`child_id`) REFERENCES `t_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
四、验证pt-osc 对外键子表DDL操作
采用pt-online-schema-change --alter-foreign-keys-method=rebuild_constraints 方法对外键子表DDL操作：
4.1、检查t_customer_log外键子表是否有关联其他子表:
root@tidb05 22:41: [(none)]> SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='db_test' AND referenced_table_name='t_customer_log';
Empty set (0.16 sec)
root@tidb05 22:41: [(none)]>
4.2、--dry-run 试运行pt-osc ：
pt-online-schema-change \
--user=pt_tools --password='pt_tools321abc' --port=3306 --host=172.16.0.246 --charset=utf8 \
--alter "modify column user_ip varchar(55) DEFAULT NULL COMMENT '用户地址'" \
--alter-foreign-keys-method=rebuild_constraints \
--check-alter \
--check-interval=5 \
--nocheck-replication-filters \
--chunk-size=2000 \
--chunk-size-limit=4 \
--chunk-time=1 \
--critical-load=threads_connected:4000,threads_running:300 \
--max-load=threads_connected:2500,threads_running:200 \
--max-lag=3 \
--recursion-method=none \
D=db_risk,t=t_customer_log \
--print --dry-run
具体执行过程：
从日志看，这样的单独的子表（无关联其他表）就和普通表pt-osc DDL操作流程一样。只不过这样的外键子表pt-osc执行完后 原先的外键fk_clog_customer_id名称变成了_fk_clog_customer_id.但是不会对表写入造成影响 操作完成后的表结构:对于大的外键子表，最好是在业务低峰期操作，避免从库的复制延迟
采用pt-online-schema-change --alter-foreign-keys-method=drop_swap 方法对外键子表DDL操作：
采用pt-online-schema-change --alter-foreign-keys-method=drop_swap 方法对外键子表DDL操作 执行流程和上面的4.2一致，此处不再详细描述
采用pt-online-schema-change --alter-foreign-keys-method=none 方法对外键子表DDL操作：
none： 类似"drop_swap"的处理方式,但是它不删除原表,并且外键关系会随着重命名转到老表上面。
但是外键名称fk_clog_customer_id 变成了_fk_clog_customer_id
具体过程如下截图：
五、总结
总结:pt-osc --alter-foreign-keys-method=XXXX
1.drop_swap： 执行FOREIGN_KEY_CHECKS=0,禁止外键约束,删除原表,再重命名新表。这种方式很快,也不会产生阻塞,但是有风险：
1, 在删除原表和重命名新表的短时间内,表是不存在的,程序会返回错误。
2, 如果重命名表出现错误,也不能回滚了.因为原表已经被删除。
2.rebuild_constraints:
对外键主表进行pt-osc DDL 时，必须检查下更改的主表涉及到多少张子表，以及子表的数据量大小. 要是涉及到多张子表或者 子表数据量非常大的话，禁止采用本参数
3.查看主表关联的子表名称:
SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='db_risk' AND referenced_table_name='主表名称';
