全网最详细之pt-osc 处理MySQL外键表流程分析 _流程

古人已用三冬足，年少今开万卷余。这篇文章主要讲述全网最详细之pt-osc 处理MySQL外键表流程分析相关的知识，希望能为你提供帮助。
一、演示环境:

阿里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
【全网最详细之pt-osc 处理MySQL外键表流程分析】具体报错内容如下:

[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 tools 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 ?1′é?
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:0020168 Connect pt_tools@172.16.0.13 on test001 using TCP/IP
2022-01-15T17:53:09.291382+08:0020168 Query /*!40101 SET NAMES "utf8"*/
2022-01-15T17:53:09.291711+08:0020168 Query SHOW VARIABLES LIKE innodb\\_lock_wait_timeout
2022-01-15T17:53:09.293578+08:0020168 Query SET SESSION innodb_lock_wait_timeout=1
2022-01-15T17:53:09.293796+08:0020168 Query SHOW VARIABLES LIKE lock\\_wait_timeout
2022-01-15T17:53:09.294964+08:0020168 Query SET SESSION lock_wait_timeout=60
2022-01-15T17:53:09.295158+08:0020168 Query SHOW VARIABLES LIKE wait\\_timeout
2022-01-15T17:53:09.296218+08:0020168 Query SET SESSION wait_timeout=10000
2022-01-15T17:53:09.296412+08:0020168 Query SELECT @@SQL_MODE
2022-01-15T17:53:09.296605+08:0020168 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:0020168 Query SELECT @@server_id /*!50038 , @@hostname*/
2022-01-15T17:53:09.297949+08:0020169 Connect pt_tools@172.16.0.13 on test001 using TCP/IP
2022-01-15T17:53:09.298177+08:0020169 Query /*!40101 SET NAMES "utf8"*/
2022-01-15T17:53:09.298399+08:0020169 Query SHOW VARIABLES LIKE innodb\\_lock_wait_timeout
2022-01-15T17:53:09.299965+08:0020169 Query SET SESSION innodb_lock_wait_timeout=1
2022-01-15T17:53:09.300167+08:0020169 Query SHOW VARIABLES LIKE lock\\_wait_timeout
2022-01-15T17:53:09.301249+08:0020169 Query SET SESSION lock_wait_timeout=60
2022-01-15T17:53:09.301451+08:0020169 Query SHOW VARIABLES LIKE wait\\_timeout
2022-01-15T17:53:09.302503+08:0020169 Query SET SESSION wait_timeout=10000
2022-01-15T17:53:09.302672+08:0020169 Query SELECT @@SQL_MODE
2022-01-15T17:53:09.302836+08:0020169 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:0020169 Query SELECT @@server_id /*!50038 , @@hostname*/
2022-01-15T17:53:09.303284+08:0020168 Query SHOW VARIABLES LIKE wsrep_on
2022-01-15T17:53:09.304499+08:0020168 Query SHOW VARIABLES LIKE version%
2022-01-15T17:53:09.306583+08:0020168 Query SHOW ENGINES
2022-01-15T17:53:09.307027+08:0020168 Query SHOW VARIABLES LIKE innodb_version
2022-01-15T17:53:09.308662+08:0020168 Query SHOW VARIABLES LIKE innodb_stats_persistent
2022-01-15T17:53:09.309948+08:0020168 Query SELECT CONCAT(@@hostname, @@port)
2022-01-15T17:53:09.310400+08:0020168 Query SHOW TABLES FROM `test001` LIKE t\\_parent
2022-01-15T17:53:09.310760+08:0020168 Query SELECT VERSION()
2022-01-15T17:53:09.310974+08:0020168 Query SHOW TRIGGERS FROM `test001` LIKE t\\_parent
2022-01-15T17:53:09.311530+08:0020168 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:0020168 Query USE `test001`
2022-01-15T17:53:09.311980+08:0020168 Query SHOW CREATE TABLE `test001`.`t_parent`
2022-01-15T17:53:09.312280+08:0020168 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-01-15T17:53:09.312703+08:0020168 Query EXPLAIN SELECT * FROM `test001`.`t_parent` WHERE 1=1
2022-01-15T17:53:09.313207+08:0020168 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:0020168 Query EXPLAIN SELECT * FROM `test001`.`t_child` WHERE 1=1
2022-01-15T17:53:09.487439+08:0020168 Query SHOW VARIABLES LIKE wsrep_on
2022-01-15T17:53:09.488954+08:0020168 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:0020168 Query USE `test001`
2022-01-15T17:53:09.489389+08:0020168 Query SHOW CREATE TABLE `test001`.`t_parent`
2022-01-15T17:53:09.489648+08:0020168 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-01-15T17:53:09.489931+08:0020168 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:0020168 Query ALTER TABLE `test001`.`_t_parent_new` add column age tinyint(3) not null default 0 comment 年龄
2022-01-15T17:53:09.517025+08:0020168 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:0020168 Query USE `test001`
2022-01-15T17:53:09.517447+08:0020168 Query SHOW CREATE TABLE `test001`.`_t_parent_new`
2022-01-15T17:53:09.517725+08:0020168 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-01-15T17:53:09.519149+08:0020168 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMINGFROM INFORMATION_SCHEMA.TRIGGERSWHERE EVENT_MANIPULATION = DELETEAND ACTION_TIMING = AFTERAND TRIGGER_SCHEMA = test001AND EVENT_OBJECT_TABLE = t_parent
2022-01-15T17:53:09.519900+08:0020168 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMINGFROM INFORMATION_SCHEMA.TRIGGERSWHERE EVENT_MANIPULATION = UPDATEAND ACTION_TIMING = AFTERAND TRIGGER_SCHEMA = test001AND EVENT_OBJECT_TABLE = t_parent
2022-01-15T17:53:09.520541+08:0020168 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMINGFROM INFORMATION_SCHEMA.TRIGGERSWHERE EVENT_MANIPULATION = INSERTAND ACTION_TIMING = AFTERAND TRIGGER_SCHEMA = test001AND EVENT_OBJECT_TABLE = t_parent
2022-01-15T17:53:09.521161+08:0020168 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMINGFROM INFORMATION_SCHEMA.TRIGGERSWHERE EVENT_MANIPULATION = DELETEAND ACTION_TIMING = BEFOREAND TRIGGER_SCHEMA = test001AND EVENT_OBJECT_TABLE = t_parent
2022-01-15T17:53:09.521758+08:0020168 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMINGFROM INFORMATION_SCHEMA.TRIGGERSWHERE EVENT_MANIPULATION = UPDATEAND ACTION_TIMING = BEFOREAND TRIGGER_SCHEMA = test001AND EVENT_OBJECT_TABLE = t_parent
2022-01-15T17:53:09.522324+08:0020168 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMINGFROM INFORMATION_SCHEMA.TRIGGERSWHERE EVENT_MANIPULATION = INSERTAND ACTION_TIMING = BEFOREAND TRIGGER_SCHEMA = test001AND EVENT_OBJECT_TABLE = t_parent
2022-01-15T17:53:09.523062+08:0020168 Query EXPLAIN SELECT * FROM `test001`.`t_parent` WHERE 1=1
2022-01-15T17:53:09.523619+08:0020168 Query SHOW VARIABLES LIKE version%
2022-01-15T17:53:09.524903+08:0020168 Query SHOW ENGINES
2022-01-15T17:53:09.525347+08:0020168 Query SHOW VARIABLES LIKE innodb_version
2022-01-15T17:53:09.526764+08:0020168 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:0020168 Query USE `test001`
2022-01-15T17:53:09.527165+08:0020168 Query SHOW CREATE TABLE `test001`.`t_child`
2022-01-15T17:53:09.527444+08:0020168 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-01-15T17:53:09.527855+08:0020168 Query SHOW TABLES FROM `test001` LIKE \\_t\\_parent\\_new
2022-01-15T17:53:09.528255+08:0020168 Query DROP TABLE IF EXISTS `test001`.`_t_parent_new`
2022-01-15T17:53:09.533969+08:0020169 Quit
2022-01-15T17:53:09.534038+08:0020168 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 ?1′é?
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:0020484 Connect pt_tools@172.16.0.13 on test001 using TCP/IP
2022-01-15T17:55:46.646378+08:0020484 Query /*!40101 SET NAMES "utf8"*/
2022-01-15T17:55:46.646658+08:0020484 Query SHOW VARIABLES LIKE innodb\\_lock_wait_timeout
2022-01-15T17:55:46.648353+08:0020484 Query SET SESSION innodb_lock_wait_timeout=1
2022-01-15T17:55:46.648550+08:0020484 Query SHOW VARIABLES LIKE lock\\_wait_timeout
2022-01-15T17:55:46.649780+08:0020484 Query SET SESSION lock_wait_timeout=60
2022-01-15T17:55:46.649980+08:0020484 Query SHOW VARIABLES LIKE wait\\_timeout
2022-01-15T17:55:46.651279+08:0020484 Query SET SESSION wait_timeout=10000
2022-01-15T17:55:46.651546+08:0020484 Query SELECT @@SQL_MODE
2022-01-15T17:55:46.651772+08:0020484 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:0020484 Query SELECT @@server_id /*!50038 , @@hostname*/
2022-01-15T17:55:46.653008+08:0020485 Connect pt_tools@172.16.0.13 on test001 using TCP/IP
2022-01-15T17:55:46.653237+08:0020485 Query /*!40101 SET NAMES "utf8"*/
2022-01-15T17:55:46.653447+08:0020485 Query SHOW VARIABLES LIKE innodb\\_lock_wait_timeout
2022-01-15T17:55:46.655170+08:0020485 Query SET SESSION innodb_lock_wait_timeout=1
2022-01-15T17:55:46.655414+08:0020485 Query SHOW VARIABLES LIKE lock\\_wait_timeout
2022-01-15T17:55:46.657142+08:0020485 Query SET SESSION lock_wait_timeout=60
2022-01-15T17:55:46.657347+08:0020485 Query SHOW VARIABLES LIKE wait\\_timeout
2022-01-15T17:55:46.658484+08:0020485 Query SET SESSION wait_timeout=10000
2022-01-15T17:55:46.658681+08:0020485 Query SELECT @@SQL_MODE
2022-01-15T17:55:46.658871+08:0020485 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:0020485 Query SELECT @@server_id /*!50038 , @@hostname*/
2022-01-15T17:55:46.659320+08:0020484 Query SHOW VARIABLES LIKE wsrep_on
2022-01-15T17:55:46.660968+08:0020484 Query SHOW VARIABLES LIKE version%
2022-01-15T17:55:46.662213+08:0020484 Query SHOW ENGINES
2022-01-15T17:55:46.662628+08:0020484 Query SHOW VARIABLES LIKE innodb_version
2022-01-15T17:55:46.664063+08:0020484 Query SHOW VARIABLES LIKE innodb_stats_persistent
2022-01-15T17:55:46.665321+08:0020484 Query SHOW GLOBAL STATUS LIKE threads_running
2022-01-15T17:55:46.666204+08:0020484 Query SHOW GLOBAL STATUS LIKE threads_connected
2022-01-15T17:55:46.666965+08:0020484 Query SHOW GLOBAL STATUS LIKE threads_running
2022-01-15T17:55:46.667674+08:0020484 Query SHOW GLOBAL STATUS LIKE threads_connected
2022-01-15T17:55:46.668504+08:0020484 Query SELECT CONCAT(@@hostname, @@port)
2022-01-15T17:55:46.668928+08:0020484 Query SHOW TABLES FROM `test001` LIKE t\\_parent
2022-01-15T17:55:46.669256+08:0020484 Query SELECT VERSION()
2022-01-15T17:55:46.669468+08:0020484 Query SHOW TRIGGERS FROM `test001` LIKE t\\_parent
2022-01-15T17:55:46.669967+08:0020484 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:0020484 Query USE `test001`
2022-01-15T17:55:46.670384+08:0020484 Query SHOW CREATE TABLE `test001`.`t_parent`
2022-01-15T17:55:46.670666+08:0020484 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-01-15T17:55:46.671137+08:0020484 Query EXPLAIN SELECT * FROM `test001`.`t_parent` WHERE 1=1
2022-01-15T17:55:46.671585+08:0020484 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:0020484 Query EXPLAIN SELECT * FROM `test001`.`t_child` WHERE 1=1
2022-01-15T17:55:46.834945+08:0020484 Query SHOW VARIABLES LIKE wsrep_on
2022-01-15T17:55:46.836546+08:0020484 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:0020484 Query USE `test001`
2022-01-15T17:55:46.836978+08:0020484 Query SHOW CREATE TABLE `test001`.`t_parent`
2022-01-15T17:55:46.837250+08:0020484 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-01-15T17:55:46.837528+08:0020484 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:0020484 Query ALTER TABLE `test001`.`_t_parent_new` add column age tinyint(3) not null default 0 comment 年龄
2022-01-15T17:55:46.868632+08:0020484 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:0020484 Query USE `test001`
2022-01-15T17:55:46.869076+08:0020484 Query SHOW CREATE TABLE `test001`.`_t_parent_new`
2022-01-15T17:55:46.869327+08:0020484 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-01-15T17:55:46.870825+08:0020484 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMINGFROM INFORMATION_SCHEMA.TRIGGERSWHERE EVENT_MANIPULATION = DELETEAND ACTION_TIMING = AFTERAND TRIGGER_SCHEMA = test001AND EVENT_OBJECT_TABLE = t_parent
2022-01-15T17:55:46.871535+08:0020484 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMINGFROM INFORMATION_SCHEMA.TRIGGERSWHERE EVENT_MANIPULATION = UPDATEAND ACTION_TIMING = AFTERAND TRIGGER_SCHEMA = test001AND EVENT_OBJECT_TABLE = t_parent
2022-01-15T17:55:46.872194+08:0020484 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMINGFROM INFORMATION_SCHEMA.TRIGGERSWHERE EVENT_MANIPULATION = INSERTAND ACTION_TIMING = AFTERAND TRIGGER_SCHEMA = test001AND EVENT_OBJECT_TABLE = t_parent
2022-01-15T17:55:46.872860+08:0020484 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMINGFROM INFORMATION_SCHEMA.TRIGGERSWHERE EVENT_MANIPULATION = DELETEAND ACTION_TIMING = BEFOREAND TRIGGER_SCHEMA = test001AND EVENT_OBJECT_TABLE = t_parent
2022-01-15T17:55:46.873424+08:0020484 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMINGFROM INFORMATION_SCHEMA.TRIGGERSWHERE EVENT_MANIPULATION = UPDATEAND ACTION_TIMING = BEFOREAND TRIGGER_SCHEMA = test001AND EVENT_OBJECT_TABLE = t_parent
2022-01-15T17:55:46.873981+08:0020484 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMINGFROM INFORMATION_SCHEMA.TRIGGERSWHERE EVENT_MANIPULATION = INSERTAND ACTION_TIMING = BEFOREAND TRIGGER_SCHEMA = test001AND EVENT_OBJECT_TABLE = t_parent
2022-01-15T17:55:46.874594+08:0020484 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:0020484 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:0020484 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:0020484 Query EXPLAIN SELECT * FROM `test001`.`t_parent` WHERE 1=1
2022-01-15T17:55:46.884569+08:0020484 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:0020484 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:0020484 Query SHOW WARNINGS
2022-01-15T17:55:46.886899+08:0020484 Query SHOW GLOBAL STATUS LIKE threads_connected
2022-01-15T17:55:46.887864+08:0020484 Query SHOW GLOBAL STATUS LIKE threads_running
2022-01-15T17:55:46.888683+08:0020484 Query SHOW VARIABLES LIKE version%
2022-01-15T17:55:46.889913+08:0020484 Query SHOW ENGINES
2022-01-15T17:55:46.890323+08:0020484 Query SHOW VARIABLES LIKE innodb_version
2022-01-15T17:55:46.891611+08:0020484 Query ANALYZE TABLE `test001`.`_t_parent_new` /* pt-online-schema-change */
2022-01-15T17:55:46.893987+08:0020484 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:0020484 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:0020484 Query USE `test001`
2022-01-15T17:55:46.910011+08:0020484 Query SHOW CREATE TABLE `test001`.`t_child`
2022-01-15T17:55:46.910278+08:0020484 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2022-01-15T17:55:46.910550+08:0020484 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:0020484 Query DROP TABLE IF EXISTS `test001`.`_t_parent_old`
2022-01-15T17:55:46.940858+08:0020484 Query DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_del`
2022-01-15T17:55:46.941111+08:0020484 Query DROP TRIGGER IF EXISTS `test001`.`pt_osc_test001_t_parent_upd`
2022-01-15T17:55:46.941339+08:0020484 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

到此这篇关于全网最详细之pt-osc 处理MySQL外键表流程分析内容已经讲完，如果未能解决您的问题，请参考下面的文章：

推荐阅读

乐苑环游世界第一站（最美的风景在路上“拉萨-林芝”（自驾篇））

宝宝频繁吃奶的原因

鱼没有腌好有点腥怎样补救

Android M(6.0) 权限爬坑之旅

荣耀9XPro手机无法开机的原因

新冠疫苗|辉瑞新冠疫苗已致两人死亡，国产疫苗引发全球抢购

G610手机参数华为G610还能升级系统吗

【SpringCloud-Alibaba系列教程】4.服务管理

redis client有哪些 redis客户端有哪些

大金中央空调u1故障代码解决步骤,考虑这几点原因

大商道人物结局

吃太多香蕉会致人死亡？科学分析香蕉的“毒”

早上调皮幽默的问候句子早安调皮句子超棒的幽默早安问候语

兰花的颜色有哪些特点兰花的颜色有哪些

西门子燃气灶点火按压熄火出现这种故障怎么办,主要是看这个指标

《王者荣耀》“王炸流”狄仁杰崛起,可贴脸站撸铠皇,排位胜率接近90%,应该怎么玩？

蛙泳怎么提高呼吸技术

冰箱旁边放植物好养吗? 冰箱上面能放植物吗

2022长沙市养老金上调发放时间退休人员养老金上调标准

灵川县景点

#yyds干货盘点# Spring的@Autowired依赖注入原来这么多坑！

Kubernetes官方java客户端之六（OpenAPI基本操作）

Spring认证中国教育管理中心-Spring Data Couchbase教程九

特定页面模板的WP自定义元框

WP（向WooCommerce单一产品添加div）

WordPress-wp_list_authors可获取多个作者图像和简历

WordPress wp_list_categories()格式

WordPress WP_Customize_Image_Control()无法使用icon(.ico)文件

WordPress（Visual Composer卡在加载中）