pt-online-schema-change 使用教程

发表于 | 分类于
字数统计: | 阅读时长 ≈

介绍

MySQL生产更改表结构的方式有很多，MySQL官方的Online DDL，github开源的gh-ost，此文仅介绍percona的工具包其中的pt-online-schema-change

原理

general_log

在mysql实例中开启general_log参数，观察pt-online-schema-change的执行过程。

执行pt-online-schema-change的语句如下：

1
pt-online-schema-change --user=root  --password=xxx --socket=/opt/mysql3306/data/mysql.sock  D=test,t=employee1 --alter "engine=innodb"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute --critical-load="Threads_running:200" --charset=utf8mb4

以下为执行DDL过程中产生的general_log:

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
2018-09-20T14:22:32.070723+08:00	   52 Connect	root@localhost on test using Socket
2018-09-20T14:22:32.071066+08:00	   52 Query	SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2018-09-20T14:22:32.073617+08:00	   52 Query	SET SESSION innodb_lock_wait_timeout=1
2018-09-20T14:22:32.073760+08:00	   52 Query	SHOW VARIABLES LIKE 'lock\_wait_timeout'
2018-09-20T14:22:32.074808+08:00	   52 Query	SET SESSION lock_wait_timeout=60
2018-09-20T14:22:32.074930+08:00	   52 Query	SHOW VARIABLES LIKE 'wait\_timeout'
2018-09-20T14:22:32.075913+08:00	   52 Query	SET SESSION wait_timeout=10000
2018-09-20T14:22:32.076034+08:00	   52 Query	SELECT @@SQL_MODE
2018-09-20T14:22:32.076153+08:00	   52 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_ENGINE_SUBSTITUTION'*/
2018-09-20T14:22:32.076290+08:00	   52 Query	SELECT @@server_id /*!50038 , @@hostname*/
2018-09-20T14:22:32.077001+08:00	   53 Connect	root@localhost on test using Socket
2018-09-20T14:22:32.077212+08:00	   53 Query	SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2018-09-20T14:22:32.078706+08:00	   53 Query	SET SESSION innodb_lock_wait_timeout=1
2018-09-20T14:22:32.078829+08:00	   53 Query	SHOW VARIABLES LIKE 'lock\_wait_timeout'
2018-09-20T14:22:32.079847+08:00	   53 Query	SET SESSION lock_wait_timeout=60
2018-09-20T14:22:32.079964+08:00	   53 Query	SHOW VARIABLES LIKE 'wait\_timeout'
2018-09-20T14:22:32.080952+08:00	   53 Query	SET SESSION wait_timeout=10000
2018-09-20T14:22:32.081068+08:00	   53 Query	SELECT @@SQL_MODE
2018-09-20T14:22:32.081183+08:00	   53 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_ENGINE_SUBSTITUTION'*/
2018-09-20T14:22:32.081314+08:00	   53 Query	SELECT @@server_id /*!50038 , @@hostname*/
2018-09-20T14:22:32.081664+08:00	   52 Query	SHOW VARIABLES LIKE 'wsrep_on'
2018-09-20T14:22:32.083211+08:00	   52 Query	SHOW VARIABLES LIKE 'version%'
2018-09-20T14:22:32.084429+08:00	   52 Query	SHOW ENGINES
2018-09-20T14:22:32.084774+08:00	   52 Query	SHOW VARIABLES LIKE 'innodb_version'
2018-09-20T14:22:32.086177+08:00	   52 Query	SHOW VARIABLES LIKE 'innodb_stats_persistent'
2018-09-20T14:22:32.087447+08:00	   52 Query	SHOW GLOBAL STATUS LIKE 'Threads_running'
2018-09-20T14:22:32.088579+08:00	   52 Query	SHOW GLOBAL STATUS LIKE 'Threads_running'
2018-09-20T14:22:32.089437+08:00	   52 Query	SELECT CONCAT(@@hostname, @@port)
2018-09-20T14:22:32.089777+08:00	   52 Query	SHOW TABLES FROM `test` LIKE 'employee1'
2018-09-20T14:22:32.131978+08:00	   52 Query	SELECT VERSION()
2018-09-20T14:22:32.132209+08:00	   52 Query	SHOW TRIGGERS FROM `test` LIKE 'employee1'
2018-09-20T14:22:32.141352+08:00	   52 Query	/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2018-09-20T14:22:32.141549+08:00	   52 Query	USE `test`
2018-09-20T14:22:32.141731+08:00	   52 Query	SHOW CREATE TABLE `test`.`employee1`
2018-09-20T14:22:32.142086+08:00	   52 Query	/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2018-09-20T14:22:32.142497+08:00	   52 Query	EXPLAIN SELECT * FROM `test`.`employee1` WHERE 1=1
2018-09-20T14:22:32.142931+08:00	   52 Query	SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='test' AND referenced_table_name='employee1'
2018-09-20T14:22:32.216829+08:00	   52 Query	SHOW VARIABLES LIKE 'wsrep_on'
2018-09-20T14:22:32.219654+08:00	   52 Query	/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2018-09-20T14:22:32.219802+08:00	   52 Query	USE `test`
2018-09-20T14:22:32.220004+08:00	   52 Query	SHOW CREATE TABLE `test`.`employee1`
2018-09-20T14:22:32.220231+08:00	   52 Query	/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2018-09-20T14:22:32.220455+08:00	   52 Query	CREATE TABLE `test`.`_employee1_new` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employeeid` int(10) unsigned NOT NULL COMMENT '0',
  `employeename` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2018-09-20T14:22:32.336287+08:00	   52 Query	ALTER TABLE `test`.`_employee1_new` engine=innodb
2018-09-20T14:22:32.499232+08:00	   52 Query	/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2018-09-20T14:22:32.499400+08:00	   52 Query	USE `test`
2018-09-20T14:22:32.499611+08:00	   52 Query	SHOW CREATE TABLE `test`.`_employee1_new`
2018-09-20T14:22:32.499866+08:00	   52 Query	/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2018-09-20T14:22:32.500737+08:00	   52 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 = 'test'    AND EVENT_OBJECT_TABLE = 'employee1'
2018-09-20T14:22:32.502027+08:00	   52 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 = 'test'    AND EVENT_OBJECT_TABLE = 'employee1'
2018-09-20T14:22:32.502989+08:00	   52 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 = 'test'    AND EVENT_OBJECT_TABLE = 'employee1'
2018-09-20T14:22:32.503974+08:00	   52 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 = 'test'    AND EVENT_OBJECT_TABLE = 'employee1'
2018-09-20T14:22:32.504875+08:00	   52 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 = 'test'    AND EVENT_OBJECT_TABLE = 'employee1'
2018-09-20T14:22:32.505829+08:00	   52 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 = 'test'    AND EVENT_OBJECT_TABLE = 'employee1'
2018-09-20T14:22:32.513721+08:00	   52 Query	CREATE TRIGGER `pt_osc_test_employee1_del` AFTER DELETE ON `test`.`employee1` FOR EACH ROW DELETE IGNORE FROM `test`.`_employee1_new` WHERE `test`.`_employee1_new`.`id` <=> OLD.`id`
2018-09-20T14:22:32.552672+08:00	   52 Query	CREATE TRIGGER `pt_osc_test_employee1_upd` AFTER UPDATE ON `test`.`employee1` FOR EACH ROW BEGIN DELETE IGNORE FROM `test`.`_employee1_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `test`.`_employee1_new`.`id` <=> OLD.`id`;REPLACE INTO `test`.`_employee1_new` (`id`, `employeeid`, `employeename`) VALUES (NEW.`id`, NEW.`employeeid`, NEW.`employeename`);END
2018-09-20T14:22:32.583800+08:00	   52 Query	CREATE TRIGGER `pt_osc_test_employee1_ins` AFTER INSERT ON `test`.`employee1` FOR EACH ROW REPLACE INTO `test`.`_employee1_new` (`id`, `employeeid`, `employeename`) VALUES (NEW.`id`, NEW.`employeeid`, NEW.`employeename`)
2018-09-20T14:22:32.611701+08:00	   52 Query	EXPLAIN SELECT * FROM `test`.`employee1` WHERE 1=1
2018-09-20T14:22:32.613770+08:00	   52 Query	SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`employee1` FORCE INDEX(`PRIMARY`) ORDER BY `id` LIMIT 1 /*first lower boundary*/
2018-09-20T14:22:32.614250+08:00	   52 Query	SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`employee1` FORCE INDEX (`PRIMARY`) WHERE `id` IS NOT NULL ORDER BY `id` LIMIT 1 /*key_len*/
2018-09-20T14:22:32.614536+08:00	   52 Query	EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`employee1` FORCE INDEX (`PRIMARY`) WHERE `id` >= '1' /*key_len*/
2018-09-20T14:22:32.614982+08:00	   52 Query	EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`employee1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) ORDER BY `id` LIMIT 999, 2 /*next chunk boundary*/
2018-09-20T14:22:32.615319+08:00	   52 Query	SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`employee1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) ORDER BY `id` LIMIT 999, 2 /*next chunk boundary*/
2018-09-20T14:22:32.615860+08:00	   52 Query	EXPLAIN SELECT `id`, `employeeid`, `employeename` FROM `test`.`employee1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) AND ((`id` <= '1000')) LOCK IN SHARE MODE /*explain pt-online-schema-change 17673 copy nibble*/
2018-09-20T14:22:32.616300+08:00	   52 Query	INSERT LOW_PRIORITY IGNORE INTO `test`.`_employee1_new` (`id`, `employeeid`, `employeename`) SELECT `id`, `employeeid`, `employeename` FROM `test`.`employee1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) AND ((`id` <= '1000')) LOCK IN SHARE MODE /*pt-online-schema-change 17673 copy nibble*/
2018-09-20T14:22:32.652804+08:00	   52 Query	SHOW WARNINGS
2018-09-20T14:22:32.653297+08:00	   52 Query	SHOW GLOBAL STATUS LIKE 'Threads_running'
2018-09-20T14:22:32.654684+08:00	   52 Query	EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`employee1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1001')) ORDER BY `id` LIMIT 13729, 2 /*next chunk boundary*/
2018-09-20T14:22:32.655061+08:00	   52 Query	SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`employee1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1001')) ORDER BY `id` LIMIT 13729, 2 /*next chunk boundary*/
2018-09-20T14:22:32.657464+08:00	   52 Query	SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`employee1` FORCE INDEX(`PRIMARY`) ORDER BY `id` DESC LIMIT 1 /*last upper boundary*/
2018-09-20T14:22:32.657712+08:00	   52 Query	EXPLAIN SELECT `id`, `employeeid`, `employeename` FROM `test`.`employee1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1001')) AND ((`id` <= '10000')) LOCK IN SHARE MODE /*explain pt-online-schema-change 17673 copy nibble*/
2018-09-20T14:22:32.658105+08:00	   52 Query	INSERT LOW_PRIORITY IGNORE INTO `test`.`_employee1_new` (`id`, `employeeid`, `employeename`) SELECT `id`, `employeeid`, `employeename` FROM `test`.`employee1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1001')) AND ((`id` <= '10000')) LOCK IN SHARE MODE /*pt-online-schema-change 17673 copy nibble*/
2018-09-20T14:22:32.890346+08:00	   52 Query	SHOW WARNINGS
2018-09-20T14:22:32.890850+08:00	   52 Query	SHOW GLOBAL STATUS LIKE 'Threads_running'
2018-09-20T14:22:32.892398+08:00	   52 Query	SHOW VARIABLES LIKE 'version%'
2018-09-20T14:22:32.895063+08:00	   52 Query	SHOW ENGINES
2018-09-20T14:22:32.895463+08:00	   52 Query	SHOW VARIABLES LIKE 'innodb_version'
2018-09-20T14:22:32.897284+08:00	   52 Query	ANALYZE TABLE `test`.`_employee1_new` /* pt-online-schema-change */
2018-09-20T14:22:32.935080+08:00	   52 Query	RENAME TABLE `test`.`employee1` TO `test`.`_employee1_old`, `test`.`_employee1_new` TO `test`.`employee1`
2018-09-20T14:22:33.051130+08:00	   52 Query	DROP TABLE IF EXISTS `test`.`_employee1_old`
2018-09-20T14:22:33.147460+08:00	   52 Query	DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employee1_del`
2018-09-20T14:22:33.153099+08:00	   52 Query	DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employee1_upd`
2018-09-20T14:22:33.160364+08:00	   52 Query	DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employee1_ins`
2018-09-20T14:22:33.170939+08:00	   52 Query	SHOW TABLES FROM `test` LIKE '\_employee1\_new'
2018-09-20T14:22:33.227570+08:00	   53 Quit	
2018-09-20T14:22:33.230437+08:00	   52 Quit

执行过程

  1. 创建一个和要执行alter操作的表一样的新的空表，前缀为_开头，后缀以_new结尾。
  2. 在新表执行alter table 语句，因为是空表，执行速度很快。
  3. 在原表中创建3个触发器，分别对应insert,update,delete操作，在5.6版本中不允许存在其他触发器，会报错，5.7版本中无此限制。
  4. 以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表，注意这里INSERT和UPDATE采用INSERT LOW_PRIORITY IGNORE INTO操作，DELETE和UPDATE采用REPLACE INTO操作。
  5. 以原子重命名的方式，将原表名table修改为 _table_old, 将_table_new 表明修改为原表名。
  6. 如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理。
  7. 默认最后将旧原表删除，触发器删除。

时序问题

这里说明一下复制原表数据和触发器的执行顺序是否会产生数据不一致的问题

UPDATE

1.假设pt-online-schema-change在T(10:00:00)执行

2.假设id=2000050这行数据，在T2(10:01:00)时间进行了update，生产的数据通过触发器同步到了临时表，update方式为先DELETE IGNORE，然后REPLCAE INTO，会有2种情况：

1)如果id=2000050这行数据还没从原表复制到临时表，此时DELETE IGNORE不会匹配到数据，REPLCAE INTO会插入新数据；后面执行到分批从原表复制数据到临时表时，包括将id=2000050这行，由于采用的用INSERT LOW_PRIORITY IGNORE INTO，也就不会插入；

2)如果id=2000050这行数据已经从原表复制到临时表，此时DELETE IGNORE会匹配到数据并删除，REPLCAE INTO重新插入新数据；

3.继续往后执行

INSERT

1.假设pt-online-schema-change在T(10:00:00)执行

2.假设新插入数据id=10000001这行数据，在T2(10:30:00)时间进行，由于新插入的数据原表不存在，通过触发器，采用REPLACE INTO，临时表如果存在这条数据，覆盖掉，理论上通过pt-online-schema-change正常执行，临时表也不会存在此行数据

3.继续往后执行

DELETE

1.假设pt-online-schema-change在T(10:00:00)执行

2.假设id=2000050这行数据，在T2(10:01:00)时间进行了delete，生产的数据通过触发器同步到了临时表，delete方式为先DELETE IGNORE

1)如果id=2000050这行数据还没从原表复制到临时表，此时DELETE IGNORE不会匹配到数据；后面执行到分批从原表复制数据到临时表时，包括将id=2000050这行，由于采用的用INSERT LOW_PRIORITY IGNORE INTO，也就不会插入；

2)如果id=2000050这行数据已经从原表复制到临时表，此时DELETE IGNORE会匹配到数据并删除

3.继续往后执行

用法

pt-online-schema-change [OPTIONS] DSN

pt-online-schema-change在不阻塞读或写的情况下更改表的结构。在DSN中指定数据库和表。

sakila.actor增加字段：

1
pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor

sakila.actor更改存储引擎为InnoDB：

1
pt-online-schema-change --alter "ENGINE=InnoDB" D=sakila,t=actor

风险

Percona Toolkit已经成熟，并得到了验证，并经过了充分测试，但所有数据库工具都可能对系统和数据库服务器造成风险，在使用此工具前注意:

  • 阅读工具的文档
  • 查看工具已知的bug列表
  • 在非生产服务器上测试
  • 备份生产服务器并验证备份有效性

限制

  1. 表中无主键或唯一索引，pt-online-schema-change拒绝执行。
  2. 复制集群中存在过滤复制，pt-online-schema-change拒绝执行，修改参数为–[no]check-replication-filters
  3. 执行过程中检测到从库延迟，暂停数据复制，调节参数为–max-lag
  4. 执行过程中检测到负载过高，pt-online-schema-change暂停或中止执行，调节参数为–max-load和–critical-load
  5. 不能通过删除一列，然后再新增一列的方式来完成对列的重命名操作。
  6. 新增字段，如果这个字段是NOT NULL，必须要指定default值，否则报错，你必须指定默认值。
  7. 如果是DROP FOREIGN KEY constraint_name ， 那么必须指定 _ 加上 constraint_name ， 而不是 constraint_name。
  8. 默认会设置锁等待超时时间为1s来避免干扰其他事务的进行，调节参数为–lock-wait-timeout。
  9. 默认如果检测到外键冲突后会拒绝改表，调节参数为–alter-foreign-keys-method。
  10. 该工具不能在PXC（Percona XtraDB Cluster）集群中对myisam表进行改表操作。

参数

–dry-run 和 –execute 互斥,前者为打印,后者为执行

选项

参数 描述
–alter 通过这个选项，就不需要alter table关键字了，可以通过逗号来指定多个修改操作。
例如：ADD COLUMN c1 INT
–alter-foreign-keys-method 如何把外键引用到新表?需要特殊处理带有外键约束的表,以保证它们可以应用到新表.当重命名表的时候,外键关系会带到重命名后的表上。

该工具有两种方法,可以自动找到子表,并修改约束关系。
auto： 在rebuild_constraints和drop_swap两种处理方式中选择一个。
rebuild_constraints：使用 ALTER TABLE语句先删除外键约束,然后再添加.如果子表很大的话,会导致长时间的阻塞。
drop_swap： 执行FOREIGN_KEY_CHECKS=0,禁止外键约束,删除原表,再重命名新表。这种方式很快,也不会产生阻塞,但是有风险：
1.在删除原表和重命名新表的短时间内,表是不存在的,程序会返回错误。
2.如果重命名表出现错误,也不能回滚了.因为原表已经被删除。
none： 类似”drop_swap”的处理方式,但是它不删除原表,并且外键关系会随着重命名转到老表上面。
–[no]analyze-before-swap 默认 yes，在新表与旧表完成转换之前对新表执行ANALYZE TABLE操作，默认会在MySQL5.6及之后版本并且开启innodb_stats_persistent的情况下执行。
–ask-pass 命令行提示密码输入，保护密码安全，前提需安装模块perl-TermReadKey。
–channel 指定当主从复制环境是多源复制时需要进行同步哪个主库的数据，适用于多源复制中多个主库对应一个从库的情形。
–charset 指定连接字符集。
–[no]check-alter 默认 yes，解析变更选项的内容，发出表变更警告，主要警告项为：

1.字段重命名
在工具的早期版本中，通过指定CHANGE COLUMN name new_name进行字段重命名会导致数据库的丢失，现在的版本已经通过代码解决了数据一致性问题。但这段代码并不能保证能够确保数据的不丢失。所以当涉及到字段名变更时应通过添加选项’–dry-run’和’–print’查看变更是否可以正确执行。
2.删除主键
如果’–alter’选项中包含DROP PRIMARY KEY删除主键的操作，除非指定选项’–dry-run’，否则工具将退出。变更表的主键是十分危险的，工具变更时建立的触发器，尤其是DELETE触发器，是基于主键的，在做主键变更前先添加选项’–dry-run’和’–print’验证触发器是可用的。
–check-interval 指定出现从库滞后超过max-lag，则该工具将睡眠多长时间，默认1s，再检查。
–[no]check-plan 默认 yes，检查查询执行计划的安全性。
–[no]check-replication-filters 默认 yes，如果工具检测到服务器选项中有任何复制相关的筛选，如指定 binlog_ignore_db 和 replicate_do_db 此类。发现有这样的筛选，工具会报错且退出。因为如果更新的表 Master 上存在，而 Slave 上不存在，会导致复制的失败。使用–no-check-replication-filters 选项来禁用该检查。
–check-slave-lag 指定一个从库的 DSN 连接地址，如果从库超过 –max-lag 参数设置的值，就会暂停操作。
–chunk-index 指定使用哪个索引对表进行chunk分块操作。默认情况下会选择最优的索引，工具会在SQL语句中添加FORCE INDEX子句。
–chunk-index-columns 指定使用选项’–chunk-index’的索引使用最左前缀几个索引字段，只适用于复合索引。
–chunk-size 指定表分块的chunk大小，每个chunk对应的表行数，默认是 1000 行，也可以是数据块大小，当指定大小时允许的后缀单位为k、M、G。这个块的大小要尽量与 –chunk-time 匹配，如果明确指定这个选项，那么每个块就会指定行数的大小。如果块索引不是唯一的，那么块可能会比期望的大
–chunk-size-limit 当需要复制的块远大于设置的 chunk-size 大小，就不复制。默认值是 4.0，一个没有主键或唯一索引的表，块大小就是不确定的。
–chunk-time 在 chunk-time 执行的时间内，动态调整 chunk-size 的大小，以适应服务器性能的变化，该参数设置为 0, 或者指定 chunk-size, 都可以禁止动态调整。
–config 读取以逗号分隔的配置文件列表，如果指定，则必须要在命令行的第一个选项位置
–critical-load 默认为 Threads_running=50。用法基本与 –max-load 类似，如果不指定 MAX_VALUE，那么工具会这只其为当前值的 200%。如果超过指定值，则工具直接退出，而不是暂停。
–database 指定连接的数据库，如有多个则用’,’(逗号)隔开。
–default-engine 默认情况下，新的表与原始表是相同的存储引擎，所以如果原来的表使用 InnoDB 的，那么新表将使用 InnoDB 的。在涉及复制某些情况下，很可能主从的存储引擎不一样。使用该选项会默认使用默认的存储引擎。
–data-dir 使用DATA DIRECTORY特性在不同的分区上创建新表。只能在5.6以上版本中使用。如果与remove-data-dir同时使用，则忽略此参数。
–remove-data-dir 如果原始表是使用DATA DIRECTORY特性创建的，那么删除它并在MySQL默认目录中创建新表，而不创建新的isl文件。
–defaults-file 读取配置文件。
–[no]drop-new-table 默认 yes。如果拷贝旧表数据到新表时失败，则删除新表。如果指定选项’–no-drop-new-table’以及’–no-swap-tables’将保留一份变更后的副本，但不会对旧表进行修改。-no-drop-new-table不能用于修改-foreign-key -method drop_swap。
–[no]drop-old-table 默认 yes。复制数据完成重命名之后，删除原表，如果有错误则会保留原表，指定选项’–no-swap-tables’同样不会删除旧表。
–[no]drop-trigger 默认 yes，删除原表上的触发器。 –no-drop-triggers 会强制开启 –no-drop-old-table 即：不删除触发器就会强制不删除原表。
–dry-run 创建和修改新表，但不会创建触发器、复制数据、和替换原表。并不真正执行，可以看到生成的执行语句，了解其执行步骤与细节。–dry-run 与 –execute 必须指定一个，二者相互排斥。和 –print 配合最佳。
–execute 确定修改表，则指定该参数。真正执行。–dry-run 与 –execute 必须指定一个，二者相互排斥。
–[no]check-unique-key-change 默认值：yes，当工具要进行添加唯一索引的变更时停止运行。因为工具使用语句INSERT IGNORE从旧表进行数据拷贝插入新表，如果插入的值违返唯一性约束，数据插入不会明确提示失败但这样会造成数据丢失。
–force 强制运行，可能打破外键约束。
–help 打印帮助。
–host 连接的host。
–max-flow-ctl 类似-max-lag，适用于PXC集群。检查用于流控制的平均暂停时间集群，如果超过选项中指定的百分比，则使工具暂停。当检测到任何流控制活动时，值0将使工具暂停。默认情况下没有流控制检查。此选项适用于PXC版本5.6或更高版本。
–max-lag 默认 1s。每个 chunk 拷贝完成后，会查看所有复制 Slave 的延迟情况。要是延迟大于该值，则暂停复制数据，直到所有从的滞后小于这个值，使用 Seconds_Behind_Master。如果有任何从滞后超过此选项的值，则该工具将睡眠 –check-interval 指定的时间，再检查。如果从被停止，将会永远等待，直到从开始同步，并且延迟小于该值。如果指定 –check-slave-lag，该工具只检查该服务器的延迟，而不是所有服务器。
–max-load 默认为 Threads_running=25。每个 chunk 拷贝完后，会检查 SHOW GLOBAL STATUS 的内容，检查指标是否超过了tatus 指标 =MAX_VALUE 或者 status 指标：MAX_VALUE。如果不指定 MAX_VALUE，那么工具会这只其为当前值的 120%。
–preserve-triggers 指定保留旧表的触发器。从MySQL5.7.2起开始支持在同一张给定的表上定义具有相同触发事件和触发时间的多个触发器。这意味着如果表原来已有触发器，那么工具所需的触发器也可以创建成功。如果指定了该选项，则工具将旧表上所有的触发器复制到新表上，然后再进行表数据行的拷贝操作。

限制：
1.如果旧表上的触发器引用了将被工具删除的字段，则触发器失效；
2.该选项不能与选项’–no-drop-triggers’、’–no-drop-old-table’和’–no-swap-tables’一起使用，因为该选项需要删除旧表的触发器并在新表上重新创建，因为表不可能有多个同名的触发器。
–new-table-name 复制创建新表的名称，默认 %T_new。
–null-to-not-null 指定可以将允许NULL的字段转换为NOT NULL字段。其中如有包含NULL行的字段值转换为字段默认值，如果没有字段值，则根字段类型来分配默认值。如：字符串类型为’’(空字符串)，数值类型为0。
–only-same-schema-fks 仅在与原始表相同模式的表上检查外键。 此选项很危险，因为如果您有FL引用其他架构中的表，则不会检测到它们。
–password 连接的密码。
–pause-file 当此参数指定的文件存在时，执行将暂停。
–pid 创建给定的PID文件。 如果PID文件已经存在并且包含的PID与当前的PID不同，则该工具将无法启动。但是，如果存在PID文件，并且其中包含的PID不再运行，则该工具将使用当前PID覆盖PID文件。 工具退出后，PID文件将自动删除。
–plugin 定义”pt_online_schema_change_plugin”类的Perl模块文件。 使用插件，您可以编写一个Perl模块，该模块可以连接到pt-online-schema-change的许多部分。 这需要对Perl和Percona Toolkit约定的充分了解，而这些约定不在本文档的讨论范围之内。 如有疑问或需要帮助，请联系Percona。
–port 连接的端口。
–print 打印 SQL 语句到标准输出。指定此选项可以让你看到该工具所执行的语句，和 –dry-run 配合最佳。
–progress 复制数据的时候打印进度报告，二部分组成：第一部分是百分比，第二部分是时间。
–quiet -q，不把信息标准输出。
–recurse 指定搜寻从库的层级，默认无限级。
–recursion-method 默认是 show processlist，发现从的方法，也可以是 host，但需要在从上指定 report_host，通过 show slave hosts 来找到，可以指定 none 来不检查 Slave。

METHOD USES
===== ========
processlist SHOW PROCESSLIST
hosts SHOW SLAVE HOSTS
dsn=DSN DSNs from a table
none Do not find slaves
指定 none 则表示不在乎从的延迟。
–skip-check-slave-lag 检查SLAVE的时候，指定该SLAVE跳过。
–slave-user 设置用于连接到从库的用户。
–slave-password 设置用于连接到从库的密码。
–set-vars 默认：
wait_timeout=10000
innodb_lock_wait_timeout=1
lock_wait_timeout=60
运行检查时指定参数值，如有多个用’,’(逗号)分隔。如’–set-vars wait_timeout=500’。
–sleep 默认值：0s，每个chunk导入后与下一次chunk导入开始前sleep一会，sleep时间越长，对于磁盘IO的冲击就越小。
–socket 连接实例的socket。
–statistics 打印出内部事件的数目，可以看到复制数据插入的数目。
–[no]swap-tables 默认 yes。交换原始表和新表，除非你禁止 –[no]drop-old-table。
–tries 尝试几次关键操作。 如果某些操作由于非致命的可恢复错误而失败，则该工具将等待并再次尝试该操作。
这些是重试的操作，其默认尝试次数和两次尝试之间的等待时间（以秒为单位）：

OPERATION TRIES WAIT
======= ==== ====
create_triggers 10 1
drop_triggers 10 1
copy_rows 10 0.25
swap_tables 10 1
update_foreign_keys 10 1
analyze_table 10 1

要更改默认值，请指定新值，例如：–tries create_triggers:5:0.5,drop_triggers:5:0.5
–user 连接的用户。
–version 打印版本信息。
–[no]version-check 默认值：yes，检查Percona Toolkit、MySQL和其他程序的最新版本。

DSN选项

这些选项选用于创建DSN,每个选项都给出了option=value,选项区分大小写

参数 描述
A dsn: charset; copy: yes 默认字符集
D dsn: database; copy: yes 默认数据库
F dsn: mysql_read_default_file; copy: yes 仅从指定文件读取参数
h dsn: host; copy: yes 连接的host
p dsn: password; copy: yes 连接的密码
P dsn: port; copy: yes 连接的端口
S dsn: mysql_socket; copy: yes 连接的socket
u dsn: user; copy: yes 连接的用户
t 记录操作的表,通过–log-dsn指定

案例

创建测试表

通过存储过程，在test库下创建employee1表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DELIMITER $$
DROP PROCEDURE IF EXISTS my_test1$$
CREATE PROCEDURE my_test1(IN loop_times INT)
BEGIN
DECLARE var INT DEFAULT 0;

PREPARE MSQL FROM 'CREATE TABLE IF NOT EXISTS `employee1` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`employeeid` int(10) unsigned NOT NULL COMMENT ''0'',`employeename` varchar(64) NOT NULL DEFAULT '''',PRIMARY KEY (`id`)) ENGINE=InnoDB';
EXECUTE MSQL;

START TRANSACTION;
WHILE var<loop_times DO
SET var=var+1;
INSERT INTO employee1 (employeeid,employeename) VALUES (var,CONCAT('test',var));
 
END WHILE;
COMMIT;
END$$
DELIMITER ;

查看表信息及创建过程

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
[root@localhost][test][10:20:39]> CREATE PROCEDURE my_test1(IN loop_times INT)
    -> BEGIN
    -> DECLARE var INT DEFAULT 0;
    -> 
    -> PREPARE MSQL FROM 'CREATE TABLE IF NOT EXISTS `employee1` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`employeeid` int(10) unsigned NOT NULL COMMENT ''0'',`employeename` varchar(64) NOT NULL DEFAULT '''',PRIMARY KEY (`id`)) ENGINE=InnoDB';
    -> EXECUTE MSQL;
    -> 
    -> START TRANSACTION;
    -> WHILE var<loop_times DO
    -> SET var=var+1;
    -> INSERT INTO employee1 (employeeid,employeename) VALUES (var,CONCAT('test',var));
    ->  
    -> END WHILE;
    -> COMMIT;
    -> END$$
Query OK, 0 rows affected (0.02 sec)

[root@localhost][test][10:20:39]> DELIMITER ;
[root@localhost][test][10:20:39]> 
[root@localhost][test][10:20:43]> CALL my_test1(10000);
Query OK, 0 rows affected (0.93 sec)

[root@localhost][test][10:20:59]> select * from employee1 limit 10;
+----+------------+--------------+
| id | employeeid | employeename |
+----+------------+--------------+
|  1 |          1 | test1        |
|  2 |          2 | test2        |
|  3 |          3 | test3        |
|  4 |          4 | test4        |
|  5 |          5 | test5        |
|  6 |          6 | test6        |
|  7 |          7 | test7        |
|  8 |          8 | test8        |
|  9 |          9 | test9        |
| 10 |         10 | test10       |
+----+------------+--------------+
10 rows in set (0.00 sec)

[root@localhost][test][10:21:08]> show create table employee1\G
*************************** 1. row ***************************
       Table: employee1
Create Table: CREATE TABLE `employee1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employeeid` int(10) unsigned NOT NULL COMMENT '0',
  `employeename` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

添加字段

添加字段语句：

1
pt-online-schema-change --user=root  --password=111111 --socket=/opt/mysql3306/data/mysql.sock  D=test,t=employee1 --alter "ADD COLUMN update_time datetime  DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间';"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute --critical-load="Threads_running:200" --charset=utf8mb4

执行过程：

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
[root@VM_24_101_centos ~]# pt-online-schema-change --user=root  --password=111111 --socket=/opt/mysql3306/data/mysql.sock  D=test,t=employee1 --alter "ADD COLUMN update_time datetime  DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间';"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute --critical-load="Threads_running:200" --charset=utf8mb4
No slaves found.  See --recursion-method if host VM_24_101_centos 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
No foreign keys reference `test`.`employee1`; ignoring --alter-foreign-keys-method.
Altering `test`.`employee1`...
Creating new table...
CREATE TABLE `test`.`_employee1_new` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employeeid` int(10) unsigned NOT NULL COMMENT '0',
  `employeename` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Created new table test._employee1_new OK.
Altering new table...
ALTER TABLE `test`.`_employee1_new` ADD COLUMN update_time datetime  DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间';
Altered `test`.`_employee1_new` OK.
2018-09-21T10:33:08 Creating triggers...
2018-09-21T10:33:08 Created triggers OK.
2018-09-21T10:33:08 Copying approximately 10000 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_employee1_new` (`id`, `employeeid`, `employeename`) SELECT `id`, `employeeid`, `employeename` FROM `test`.`employee1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 10925 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`employee1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
2018-09-21T10:33:08 Copied rows OK.
2018-09-21T10:33:08 Analyzing new table...
2018-09-21T10:33:08 Swapping tables...
RENAME TABLE `test`.`employee1` TO `test`.`_employee1_old`, `test`.`_employee1_new` TO `test`.`employee1`
2018-09-21T10:33:08 Swapped original and new tables OK.
2018-09-21T10:33:08 Dropping old table...
DROP TABLE IF EXISTS `test`.`_employee1_old`
2018-09-21T10:33:08 Dropped old table `test`.`_employee1_old` OK.
2018-09-21T10:33:08 Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employee1_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employee1_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employee1_ins`
2018-09-21T10:33:08 Dropped triggers OK.
Successfully altered `test`.`employee1`.
[root@VM_24_101_centos ~]# mysql test -e "show create table employee1\G"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: employee1
Create Table: CREATE TABLE `employee1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employeeid` int(10) unsigned NOT NULL COMMENT '0',
  `employeename` varchar(64) NOT NULL DEFAULT '',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
[root@VM_24_101_centos ~]# mysql test -e "select * from employee1 limit 10"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------------+--------------+---------------------+
| id | employeeid | employeename | update_time         |
+----+------------+--------------+---------------------+
|  1 |          1 | test1        | 2018-09-21 10:33:08 |
|  2 |          2 | test2        | 2018-09-21 10:33:08 |
|  3 |          3 | test3        | 2018-09-21 10:33:08 |
|  4 |          4 | test4        | 2018-09-21 10:33:08 |
|  5 |          5 | test5        | 2018-09-21 10:33:08 |
|  6 |          6 | test6        | 2018-09-21 10:33:08 |
|  7 |          7 | test7        | 2018-09-21 10:33:08 |
|  8 |          8 | test8        | 2018-09-21 10:33:08 |
|  9 |          9 | test9        | 2018-09-21 10:33:08 |
| 10 |         10 | test10       | 2018-09-21 10:33:08 |
+----+------------+--------------+---------------------+
[root@VM_24_101_centos ~]#

删除字段

删除字段语句：

1
pt-online-schema-change --user=root  --password=111111 --socket=/opt/mysql3306/data/mysql.sock  D=test,t=employee1 --alter "DROP COLUMN update_time;"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute --critical-load="Threads_running:200"

执行过程：

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
[root@VM_24_101_centos ~]# pt-online-schema-change --user=root  --password=111111 --socket=/opt/mysql3306/data/mysql.sock  D=test,t=employee1 --alter "DROP COLUMN update_time;"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute --critical-load="Threads_running:200" 
No slaves found.  See --recursion-method if host VM_24_101_centos 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
No foreign keys reference `test`.`employee1`; ignoring --alter-foreign-keys-method.
Altering `test`.`employee1`...
Creating new table...
CREATE TABLE `test`.`_employee1_new` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employeeid` int(10) unsigned NOT NULL COMMENT '0',
  `employeename` varchar(64) NOT NULL DEFAULT '',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '????',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Created new table test._employee1_new OK.
Altering new table...
ALTER TABLE `test`.`_employee1_new` DROP COLUMN update_time;
Altered `test`.`_employee1_new` OK.
2018-09-21T10:36:47 Creating triggers...
2018-09-21T10:36:47 Created triggers OK.
2018-09-21T10:36:47 Copying approximately 10000 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_employee1_new` (`id`, `employeeid`, `employeename`) SELECT `id`, `employeeid`, `employeename` FROM `test`.`employee1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 11401 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`employee1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
2018-09-21T10:36:48 Copied rows OK.
2018-09-21T10:36:48 Analyzing new table...
2018-09-21T10:36:48 Swapping tables...
RENAME TABLE `test`.`employee1` TO `test`.`_employee1_old`, `test`.`_employee1_new` TO `test`.`employee1`
2018-09-21T10:36:48 Swapped original and new tables OK.
2018-09-21T10:36:48 Dropping old table...
DROP TABLE IF EXISTS `test`.`_employee1_old`
2018-09-21T10:36:48 Dropped old table `test`.`_employee1_old` OK.
2018-09-21T10:36:48 Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employee1_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employee1_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employee1_ins`
2018-09-21T10:36:48 Dropped triggers OK.
Successfully altered `test`.`employee1`.
[root@VM_24_101_centos ~]# mysql test -e "show create table employee1\G"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: employee1
Create Table: CREATE TABLE `employee1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employeeid` int(10) unsigned NOT NULL COMMENT '0',
  `employeename` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
[root@VM_24_101_centos ~]#

此时，需要将–charset=utf8mb4删除，不然会报错：Error creating new table: Wide character in print at /bin/pt-online-schema-change line 10510.

改变字段属性

改变字段属性的语句：

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
[root@VM_24_101_centos ~]# pt-online-schema-change --user=root  --password=111111 --socket=/opt/mysql3306/data/mysql.sock  D=test,t=employee1 --alter "MODIFY employeename varchar(300) NOT NULL DEFAULT '';"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute --critical-load="Threads_running:200" --charset=utf8mb4 
No slaves found.  See --recursion-method if host VM_24_101_centos 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
No foreign keys reference `test`.`employee1`; ignoring --alter-foreign-keys-method.
Altering `test`.`employee1`...
Creating new table...
CREATE TABLE `test`.`_employee1_new` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employeeid` int(10) unsigned NOT NULL COMMENT '0',
  `employeename` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Created new table test._employee1_new OK.
Altering new table...
ALTER TABLE `test`.`_employee1_new` MODIFY employeename varchar(300) NOT NULL DEFAULT '';
Altered `test`.`_employee1_new` OK.
2018-09-21T10:45:34 Creating triggers...
2018-09-21T10:45:34 Created triggers OK.
2018-09-21T10:45:34 Copying approximately 10000 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_employee1_new` (`id`, `employeeid`, `employeename`) SELECT `id`, `employeeid`, `employeename` FROM `test`.`employee1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 12515 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`employee1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
2018-09-21T10:45:34 Copied rows OK.
2018-09-21T10:45:34 Analyzing new table...
2018-09-21T10:45:34 Swapping tables...
RENAME TABLE `test`.`employee1` TO `test`.`_employee1_old`, `test`.`_employee1_new` TO `test`.`employee1`
2018-09-21T10:45:34 Swapped original and new tables OK.
2018-09-21T10:45:34 Dropping old table...
DROP TABLE IF EXISTS `test`.`_employee1_old`
2018-09-21T10:45:34 Dropped old table `test`.`_employee1_old` OK.
2018-09-21T10:45:34 Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employee1_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employee1_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employee1_ins`
2018-09-21T10:45:34 Dropped triggers OK.
Successfully altered `test`.`employee1`.
[root@VM_24_101_centos ~]# mysql test -e "show create table employee1\G"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: employee1
Create Table: CREATE TABLE `employee1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employeeid` int(10) unsigned NOT NULL COMMENT '0',
  `employeename` varchar(300) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

非分区表改为分区表

1.将原表的id自增改为非自增，使用mysql online ddl，不锁表的方式

1
mysql test -e "alter table employee1 modify id int(10) unsigned NOT NULL,algorithm=inplace,lock=none;"

2.删除分区字段内的空值，省略

3.删除原主键，添加联合主键

1
mysql test -e "alter table employee1 drop primary key,ADD PRIMARY KEY (update_time,id),algorithm=inplace,lock=none;"

4.使用pt-online-schema-change建分区

1
pt-online-schema-change --user=root  --password=111111 --socket=/opt/mysql3306/data/mysql.sock  D=test,t=employee1 --alter "partition by RANGE COLUMNS (update_time)(PARTITION P202003 VALUES LESS THAN ('2020-04-01'),PARTITION P202004 VALUES LESS THAN ('2020-05-01'),PARTITION P202005 VALUES LESS THAN ('2020-06-01'), PARTITION PMAX VALUES LESS THAN  MAXVALUE  ) "  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute --critical-load="Threads_running:200"

5.以下为执行日志：

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
[root@VM_24_101_centos ~]# mysql test -e "alter table employee1 modify id int(10) unsigned NOT NULL,algorithm=inplace,lock=none;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@VM_24_101_centos ~]# mysql test -e "alter table employee1 drop primary key,ADD PRIMARY KEY (update_time,id),algorithm=inplace,lock=none;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@VM_24_101_centos ~]# mysql test -e "show create table employee1\G"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: employee1
Create Table: CREATE TABLE `employee1` (
  `id` int(10) unsigned NOT NULL,
  `employeeid` int(10) unsigned NOT NULL COMMENT '0',
  `employeename` varchar(300) NOT NULL DEFAULT '',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`update_time`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
[root@VM_24_101_centos ~]# pt-online-schema-change --user=root  --password=111111 --socket=/opt/mysql3306/data/mysql.sock  D=test,t=employee1 --alter "partition by RANGE COLUMNS (update_time)(PARTITION P202003 VALUES LESS THAN ('2020-04-01'),PARTITION P202004 VALUES LESS THAN ('2020-05-01'),PARTITION P202005 VALUES LESS THAN ('2020-06-01'), PARTITION PMAX VALUES LESS THAN  MAXVALUE  ) "  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute --critical-load="Threads_running:200" --charset=utf8mb4
No slaves found.  See --recursion-method if host VM_24_101_centos 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
No foreign keys reference `test`.`employee1`; ignoring --alter-foreign-keys-method.
Altering `test`.`employee1`...
Creating new table...
CREATE TABLE `test`.`_employee1_new` (
  `id` int(10) unsigned NOT NULL,
  `employeeid` int(10) unsigned NOT NULL COMMENT '0',
  `employeename` varchar(300) NOT NULL DEFAULT '',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`update_time`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Created new table test._employee1_new OK.
Altering new table...
ALTER TABLE `test`.`_employee1_new` partition by RANGE COLUMNS (update_time)(PARTITION P202003 VALUES LESS THAN ('2020-04-01'),PARTITION P202004 VALUES LESS THAN ('2020-05-01'),PARTITION P202005 VALUES LESS THAN ('2020-06-01'), PARTITION PMAX VALUES LESS THAN  MAXVALUE  ) 
Altered `test`.`_employee1_new` OK.
2018-09-21T11:19:14 Creating triggers...
2018-09-21T11:19:14 Created triggers OK.
2018-09-21T11:19:14 Copying approximately 10000 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_employee1_new` (`id`, `employeeid`, `employeename`, `update_time`) SELECT `id`, `employeeid`, `employeename`, `update_time` FROM `test`.`employee1` FORCE INDEX(`PRIMARY`) WHERE ((`update_time` > ?) OR (`update_time` = ? AND `id` >= ?)) AND ((`update_time` < ?) OR (`update_time` = ? AND `id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 16863 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `update_time`, `update_time`, `id` FROM `test`.`employee1` FORCE INDEX(`PRIMARY`) WHERE ((`update_time` > ?) OR (`update_time` = ? AND `id` >= ?)) ORDER BY `update_time`, `id` LIMIT ?, 2 /*next chunk boundary*/
2018-09-21T11:19:15 Copied rows OK.
2018-09-21T11:19:15 Analyzing new table...
2018-09-21T11:19:15 Swapping tables...
RENAME TABLE `test`.`employee1` TO `test`.`_employee1_old`, `test`.`_employee1_new` TO `test`.`employee1`
2018-09-21T11:19:15 Swapped original and new tables OK.
2018-09-21T11:19:15 Dropping old table...
DROP TABLE IF EXISTS `test`.`_employee1_old`
2018-09-21T11:19:15 Dropped old table `test`.`_employee1_old` OK.
2018-09-21T11:19:15 Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employee1_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employee1_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employee1_ins`
2018-09-21T11:19:15 Dropped triggers OK.
Successfully altered `test`.`employee1`.
[root@VM_24_101_centos ~]# mysql test -e "show create table employee1\G"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: employee1
Create Table: CREATE TABLE `employee1` (
  `id` int(10) unsigned NOT NULL,
  `employeeid` int(10) unsigned NOT NULL COMMENT '0',
  `employeename` varchar(300) NOT NULL DEFAULT '',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`update_time`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(update_time)
(PARTITION P202003 VALUES LESS THAN ('2020-04-01') ENGINE = InnoDB,
 PARTITION P202004 VALUES LESS THAN ('2020-05-01') ENGINE = InnoDB,
 PARTITION P202005 VALUES LESS THAN ('2020-06-01') ENGINE = InnoDB,
 PARTITION PMAX VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
[root@VM_24_101_centos ~]#

注意事项

慎用参数–set-vars=’sql_log_bin=0’

生产环境，当表中数据量过大，如果遇到添加索引的需求，部分人可能会采用分别在主库和从库加索引的方式，当在主库添加索引的时候，使用了–set-vars=’sql_log_bin=0’参数，可能会导致主从中断。

原因：

因为 –set-vars=’sql_log_bin=0’的原因，创建表的DDL语句，无法通过binlog在从库建表，所以临时表主库存在，从库不存在。

由于增量数据通过触发器将对原表的操作同步到临时表中，此时，对原表的操作会产生binlog，从库在应用此部分的binlog时会报错，提示表不存在。

建议：

低峰期在主库执行，不要使用参数–set-vars=’sql_log_bin=0’。

谨慎在从库执行(ROW格式)

对于生产大表加字段，有种方式是：先在从库加字段，主从切换，再在原主上加字段，可能有效的减小加字段对生产的影响。但是，当复制格式为ROW时，可能会出现复制中断的情况。

原因：

当使用pt-online-schema-change在从库执行时，会先创建临时表，在临时表上做DDL变更，然后，通过创建触发器的方式同步增量数据。由于从库同步是应用主库的binlog，从库创建的触发器就不会产生效果，增量数据也就不会同步到临时表，最后在做了临时表和原表的原子重命名后，会丢失数据，复制就会报1032错误，找不到行记录，复制中断。

建议：

低峰期在主库执行，不要在从库执行。

自增锁

模拟一种场景：

1.创建测试表，创建测试临时表，同时在原表上创建更新的触发器，模拟pt-online-schema-change，由于是人工模拟，在触发器中添加了sleep(5)。

分别在session1更新，session2执行insert low_priority ignore into(session1执行后的5s内执行)

1
2
3
4
5
6
7
8
create table t1 (id int auto_increment primary key,name varchar(10));
insert into t1 select null,'A1';
insert into t1 select null,'B2';
insert into t1 select null,'C3';
insert into t1 select null,'D4';
insert into t1 select null,'E5';
create table  _t1_new like t1;
alter table _t1_new engine = innodb;

2.session1:更新原表

1
2
begin;
update t1 set name = 'DDD4' where id = 4;

3.session2:插入数据到临时表(session1执行后的5s内执行)

1
2
begin;
insert low_priority ignore into _t1_new(id,name) select id,name from t1 where id > 2 and id <5 lock in share mode;

4.session1出现死锁

1
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

5.以下为mysql5.7，rc隔离级别的测试案例

session1:

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
[root@localhost][test][08:44:06]> create table t1 (id int auto_increment primary key,name varchar(10));
Query OK, 0 rows affected (0.02 sec)

[root@localhost][test][08:44:07]> insert into t1 select null,'A1';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

[root@localhost][test][08:44:07]> insert into t1 select null,'B2';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

[root@localhost][test][08:44:07]> insert into t1 select null,'C3';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

[root@localhost][test][08:44:07]> insert into t1 select null,'D4';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

[root@localhost][test][08:44:07]> insert into t1 select null,'E5';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

[root@localhost][test][08:44:07]> create table  _t1_new like t1;
Query OK, 0 rows affected (0.02 sec)

[root@localhost][test][08:44:07]> alter table _t1_new engine = innodb;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

[root@localhost][test][08:44:08]> delimiter //
[root@localhost][test][08:44:11]> create trigger  pt_osc_test_t1_upd  after update on test.t1 for  each row  begin  declare x int; set x = sleep(5); delete  IGNORE from  test._t1_new  where  !(OLD.`id` <=> NEW.`id`) and  test._t1_new.id  <=> OLD.`id`;replace into test._t1_new  (`id`, `name`)  values  (NEW.`id`, NEW.`name`);END//
Query OK, 0 rows affected (0.00 sec)

[root@localhost][test][08:44:11]> delimiter ;
[root@localhost][test][08:44:16]> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | A1   |
|  2 | B2   |
|  3 | C3   |
|  4 | D4   |
|  5 | E5   |
+----+------+
5 rows in set (0.00 sec)

[root@localhost][test][08:44:16]> 
[root@localhost][test][08:44:25]> begin;
Query OK, 0 rows affected (0.00 sec)

[root@localhost][test][08:44:26]> update t1 set name = 'DDD4' where id = 4;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
[root@localhost][test][08:44:31]> 
[root@localhost][test][08:44:34]> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2020-05-28 08:44:38 0x7fa64c23e700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 28 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 26639 srv_active, 0 srv_shutdown, 395877 srv_idle
srv_master_thread log flush and writes: 422516
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 10330755
OS WAIT ARRAY INFO: signal count 23257527
RW-shared spins 0, rounds 26575782, OS waits 8009821
RW-excl spins 0, rounds 48751926, OS waits 641098
RW-sx spins 299480, rounds 8787074, OS waits 284218
Spin rounds per wait: 26575782.00 RW-shared, 48751926.00 RW-excl, 29.34 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-05-28 08:44:31 0x7fa64c23e700
*** (1) TRANSACTION:
TRANSACTION 4481653631, ACTIVE 1 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 36589, OS thread handle 140352217921280, query id 207646444 localhost root Sending data
insert low_priority ignore into _t1_new(id,name) select id,name from t1 where id > 2 and id <5 lock in share mode
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 346 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 4481653631 lock mode S locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 00010b209b7e; asc      ~;;
 2: len 7; hex 580004fd7008c3; asc X   p  ;;
 3: len 4; hex 44444434; asc DDD4;;

*** (2) TRANSACTION:
TRANSACTION 4481653630, ACTIVE 5 sec setting auto-inc lock, thread declared inside InnoDB 5000
mysql tables in use 2, locked 2
4 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
MySQL thread id 36587, OS thread handle 140352218720000, query id 207646446 localhost root update
replace into test._t1_new  (`id`, `name`)  values  (NEW.`id`, NEW.`name`)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 346 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 4481653630 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 00010b209b7e; asc      ~;;
 2: len 7; hex 580004fd7008c3; asc X   p  ;;
 3: len 4; hex 44444434; asc DDD4;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test`.`_t1_new` trx id 4481653630 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 4481653637
Purge done for trx's n:o < 4481653637 undo n:o < 0 state: running but idle
History list length 60
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421829777725264, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421829777727088, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 4481653631, ACTIVE 8 sec
6 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 36589, OS thread handle 140352217921280, query id 207646444 localhost root
--------
FILE I/O
--------
...

session2:

1
2
3
4
5
6
7
8
[root@localhost][test][08:44:29]> begin;
Query OK, 0 rows affected (0.00 sec)

[root@localhost][test][08:44:30]> insert low_priority ignore into _t1_new(id,name) select id,name from t1 where id > 2 and id <5 lock in share mode;
Query OK, 2 rows affected (1.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

[root@localhost][test][08:44:31]>

分析:

1.原表更新和触发器触发后的临时表更新在同一事务内，通过查看binlog可以看到

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180920  8:58:06 server id 1  end_log_pos 124 CRC32 0x9dc68bf8 	Start: binlog v 4, server v 8.0.18 created 180920  8:58:06
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
ngzPXg8BAAAAeAAAAHwAAAABAAQAOC4wLjE4AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgH4i8ad
'/*!*/;
# at 124
#180920  8:58:06 server id 1  end_log_pos 195 CRC32 0x16f7d4d2 	Previous-GTIDs
# 3e5359e7-6ca7-11e8-b856-525400931d92:470870-495654
# at 195
#180920  8:58:21 server id 1  end_log_pos 274 CRC32 0x5e21ec93 	GTID	last_committed=0	sequence_number=1	rbr_only=yes	original_committed_timestamp=1590627501692250	immediate_commit_timestamp=1590627501692250	transaction_length=474
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1590627501692250 (2020-05-28 08:58:21.692250 CST)
# immediate_commit_timestamp=1590627501692250 (2020-05-28 08:58:21.692250 CST)
/*!80001 SET @@session.original_commit_timestamp=1590627501692250*//*!*/;
/*!80014 SET @@session.original_server_version=80018*//*!*/;
/*!80014 SET @@session.immediate_server_version=80018*//*!*/;
SET @@SESSION.GTID_NEXT= '3e5359e7-6ca7-11e8-b856-525400931d92:495655'/*!*/;
# at 274
#180920  8:58:15 server id 1  end_log_pos 358 CRC32 0xedf75521 	Query	thread_id=781	exec_time=0	error_code=0
SET TIMESTAMP=1590627495/*!*/;
SET @@session.pseudo_thread_id=781/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 358
#180920  8:58:15 server id 1  end_log_pos 422 CRC32 0x3e869251 	Rows_query
# update t1 set name = 'DDD4' where id = 4
# at 422
#180920  8:58:15 server id 1  end_log_pos 478 CRC32 0x6c91a26c 	Table_map: `test`.`t1` mapped to number 287
# at 478
#180920  8:58:15 server id 1  end_log_pos 539 CRC32 0xe153a38c 	Table_map: `test`.`_t1_new` mapped to number 286
# at 539
#180920  8:58:15 server id 1  end_log_pos 593 CRC32 0xa2ff1eed 	Update_rows: table id 287
# at 593
#180920  8:58:15 server id 1  end_log_pos 638 CRC32 0xd6d9a8bf 	Write_rows: table id 286 flags: STMT_END_F

BINLOG '
pwzPXh0BAAAAQAAAAKYBAACAACh1cGRhdGUgdDEgc2V0IG5hbWUgPSAnRERENCcgd2hlcmUgaWQg
PSA0UZKGPg==
pwzPXhMBAAAAOAAAAN4BAAAAAB8BAAAAAAEABHRlc3QAAnQxAAIDDwIoAAIBAQACA/z/AGyikWw=
pwzPXhMBAAAAPQAAABsCAAAAAB4BAAAAAAEABHRlc3QAB190MV9uZXcAAgMPAigAAgEBAAID/P8A
jKNT4Q==
pwzPXh8BAAAANgAAAFECAAAAAB8BAAAAAAAAAgAC//8ABAAAAAJENAAEAAAABERERDTtHv+i
pwzPXh4BAAAALQAAAH4CAAAAAB4BAAAAAAEAAgAC/wAEAAAABERERDS/qNnW
'/*!*/;
### UPDATE `test`.`t1`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='D4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='DDD4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### INSERT INTO `test`.`_t1_new`
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='DDD4' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
# at 638
#180920  8:58:21 server id 1  end_log_pos 669 CRC32 0x4847dd4b 	Xid = 61574
COMMIT/*!*/;

其中，最后面的UPDATE test.t1和INSERT INTO test._t1_new可以看出来。

2.session1执行update t1 set name = ‘DDD4’ where id = 4;时，对应的show engine innodb status中的TRANSACTION 2

此时，TRANSACTION 2持有t1表id=4的x锁，由于触发器的原因，t1和_t1_new的更新在同一事务内，等待获取_t1_new表的auto-inc lock，等待的语句为：replace into test._t1_new (id, name) values (NEW.id, NEW.name)

3.session2执行insert low_priority ignore into _t1_new(id,name) select id,name from t1 where id > 2 and id <5 lock in share mode;时，对应的show engine innodb status中的TRANSACTION 1

此时，TRANSACTION 1持有_t1_new的auto-inc lock，等待获取t1的记录锁

4.session1和session2产生了死锁，回滚了TRANSACTION 2

注意：

MySQL8.0在上述实验场景中，并不会出现死锁。

总结

pt-online-schema-change是一个很优秀的在线DDL工具，在gh-ost出现之前，广泛应用于生产。同时，为了更好的使用他提高工作效率，需要了解其应用场景与内部工作原理，避免踩到坑。

-------------本文结束感谢您的阅读-------------