MySQL在线DDL工具pt-online-schema-change

￡神魔★判官ぃ 2021-06-24 14:35 382阅读 0赞

原理  
pt-online-schema-change模拟了MySQL内部alter table的方式，但是其操作所更新的是复制表，所以原表不会被锁住。其原理我们通过示例进行解读：

shell> pt-online-schema-change –nocheck-replication-filters –recursion-method=none –alter “add newcol int” h=192.168.10.118,P=3306,u=sup,p=xxxx,D=dbteamdb,t=user –alter-foreign-keys-method rebuild\_constraints –print –statistics –execute  
Operation, tries, wait:  
copy\_rows, 10, 0.25  
create\_triggers, 10, 1  
drop\_triggers, 10, 1  
swap\_tables, 10, 1  
update\_foreign\_keys, 10, 1  
Child tables:  
\`dbteamdb\`.\`user\_privs\` (approx. 1 rows)  
Will use the rebuild\_constraints method to update foreign keys.  
Altering \`dbteamdb\`.\`user\`…  
Creating new table…

\#步骤1，创建空表，其命名规则是\_+原表名+\_new

CREATE TABLE \`dbteamdb\`.\`\_user\_new\` (  
  \`id\` int(10) unsigned NOT NULL AUTO\_INCREMENT,  
  \`host\` varchar(20) COLLATE utf8\_bin NOT NULL,  
  \`eng\_name\` varchar(20) COLLATE utf8\_bin NOT NULL,  
  \`chn\_name\` varchar(20) COLLATE utf8\_bin NOT NULL,  
  \`create\_time\` datetime NOT NULL,  
  \`update\_time\` datetime NOT NULL,  
  \`status\` tinyint(3) unsigned NOT NULL,  
  PRIMARY KEY (\`id\`),  
  UNIQUE KEY \`uk\_user\_\_host\` (\`host\`)  
) ENGINE=InnoDB AUTO\_INCREMENT=48 DEFAULT CHARSET=utf8 COLLATE=utf8\_bin  
Created new table dbteamdb.\_user\_new OK.  
Altering new table…

\#步骤2，根据语句更新新表结构  
ALTER TABLE \`dbteamdb\`.\`\_user\_new\` add newcol int  
Altered \`dbteamdb\`.\`\_user\_new\` OK.  
Creating triggers…

\#步骤3，在原表上创建触发器，以便后续原表上的操作同步到新表

CREATE TRIGGER \`pt\_osc\_dbteamdb\_user\_del\` AFTER DELETE ON \`dbteamdb\`.\`user\` FOR EACH ROW DELETE IGNORE FROM \`dbteamdb\`.\`\_user\_new\` WHERE \`dbteamdb\`.\`\_user\_new\`.\`id\` <=> OLD.\`id\`  
CREATE TRIGGER \`pt\_osc\_dbteamdb\_user\_upd\` AFTER UPDATE ON \`dbteamdb\`.\`user\` FOR EACH ROW REPLACE INTO \`dbteamdb\`.\`\_user\_new\` (\`id\`, \`host\`, \`eng\_name\`, \`chn\_name\`, \`create\_time\`, \`update\_time\`, \`status\`) VALUES (NEW.\`id\`, NEW.\`host\`, NEW.\`eng\_name\`, NEW.\`chn\_name\`, NEW.\`create\_time\`, NEW.\`update\_time\`, NEW.\`status\`)  
CREATE TRIGGER \`pt\_osc\_dbteamdb\_user\_ins\` AFTER INSERT ON \`dbteamdb\`.\`user\` FOR EACH ROW REPLACE INTO \`dbteamdb\`.\`\_user\_new\` (\`id\`, \`host\`, \`eng\_name\`, \`chn\_name\`, \`create\_time\`, \`update\_time\`, \`status\`) VALUES (NEW.\`id\`, NEW.\`host\`, NEW.\`eng\_name\`, NEW.\`chn\_name\`, NEW.\`create\_time\`, NEW.\`update\_time\`, NEW.\`status\`)  
Created triggers OK.  
Copying approximately 44 rows…

\#步骤4，拷贝原表数据到新表，数据量大时会根据主键进行分段chunk插入

INSERT LOW\_PRIORITY IGNORE INTO \`dbteamdb\`.\`\_user\_new\` (\`id\`, \`host\`, \`eng\_name\`, \`chn\_name\`, \`create\_time\`, \`update\_time\`, \`status\`) SELECT \`id\`, \`host\`, \`eng\_name\`, \`chn\_name\`, \`create\_time\`, \`update\_time\`, \`status\` FROM \`dbteamdb\`.\`user\` LOCK IN SHARE MODE /\*pt-online-schema-change 9378 copy table\*/  
Copied rows OK.  
Swapping tables…

\#步骤5，拷贝完成后，移走原表，用新表代替(RENAME TABLE)。其通过一个RENAME TABLE同时处理两个表，实现原子操作。

RENAME TABLE \`dbteamdb\`.\`user\` TO \`dbteamdb\`.\`\_user\_old\`, \`dbteamdb\`.\`\_user\_new\` TO \`dbteamdb\`.\`user\`  
Swapped original and new tables OK.  
Rebuilding foreign key constraints…

\#如果更新的表是被子表外键引用的父表，那么需要相应的更新子表的外键指向。具体见下文”外键的影响”。  
ALTER TABLE \`dbteamdb\`.\`user\_privs\` DROP FOREIGN KEY \`fk\_user\_privs\_\_user\_id\`, ADD CONSTRAINT \`\_fk\_user\_privs\_\_user\_id\` FOREIGN KEY (\`user\_id\`) REFERENCES \`dbteamdb\`.\`user\` (\`id\`)  
Rebuilt foreign key constraints OK.

\#最后清除临时生成的表、触发器。默认情况下会删除原表（此时是已经重命名过的\_user\_old）。

Dropping old table…  
DROP TABLE IF EXISTS \`dbteamdb\`.\`\_user\_old\`  
Dropped old table \`dbteamdb\`.\`\_user\_old\` OK.  
Dropping triggers…  
DROP TRIGGER IF EXISTS \`dbteamdb\`.\`pt\_osc\_dbteamdb\_user\_del\`;  
DROP TRIGGER IF EXISTS \`dbteamdb\`.\`pt\_osc\_dbteamdb\_user\_upd\`;  
DROP TRIGGER IF EXISTS \`dbteamdb\`.\`pt\_osc\_dbteamdb\_user\_ins\`;  
Dropped triggers OK.  
\# Event              Count  
\# ================== =====  
\# INSERT                 1  
\# rebuilt\_constraint     1  
Successfully altered \`dbteamdb\`.\`user\`.

注意：如果原表上已经有触发器，那么将无法使用本工具！

性能  
数据的拷贝是基于小块(根据chunk-time参数指定)进行的，而且根据主键或者索引进行选择，所以对整体服务器性能影响较小。

相关选项：

–alter-foreign-keys-method “<string>”  设定该表被外键引用时该如何处理。因为涉及到RENAME，而引用它的表会跟随它，而不会自动切换到新表，所以需要将外键切到新表。可选值：  
rebuild\_constraints 使用ALTER TABLE将删除重建引用到更新表的子表的相应外键。但是如果子表很大的话，就不合适了。

drop\_swap 禁用外键检测(foreign\_key\_checks=0)，然后在新表替代旧表前，直接drop掉新表，这与默认的方式不同。默认情况下，使用RENAME TABLE操作两张表，是原子操作，对于客户端来说是透明的；而启用这个选项，是先DROP老表，然后RENAME TABLE将新表重命名为老表，这种情况下，由于不是原子操作，在两个操作的间隙表就不存在了，会导致针对该表的操作错误！而且，如果RENAME操作失败，那么也无法回滚！  
auto 自动选择。其倾向于使用rebuild\_constraints，如果预估子表的更新时间小于–chunk-time则选择rebuild\_constraints，反之则选drop\_swap。

none 会对原表RENAME成\_TAB\_old，然后set foreign\_key\_checks=0，再drop \_TAB\_old。这样就会导致子表依赖的父表被删除！这样后续需要DBA参与处理。  
外键还有一个问题，就是新建表的外键名称和旧表的外键名称重复，工具保证最终表与原表有同样的外键和索引，但是名称可能会稍有不同，会添加前缀下划线。

输出  
本工具会活动信息输出到标准输出STDOUT。在数据拷贝阶段，会打印–progress报告到STDERR。指定–print可以得到额外的信息。如果指定–statistics，会进一步打印出内部事件的数目。

其他选项说明  
–max-laq <time> 默认为1s。每个chunk拷贝完成后，这个工具会查看所有复制Slave的延迟情况。其方式是连接到Slave，检查其Seconds\_Behind\_Master。如果有一个Slave延迟超过max-lag，则工具会睡眠–check-interval秒然后再检查。至于如何查询所有Slave，则根据–recursion-method（默认通过show processlist，可以指定none来不检查Slave）。

–max-load <array> 默认为Threads\_running=25。每个chunk拷贝完后，会检查SHOW GLOBAL STATUS的内容，检查指标是否超过了指定的阈值。如果超过，则先暂停。这里可以用逗号分隔，指定多个条件，每个条件格式： status指标=MAX\_VALUE或者status指标:MAX\_VALUE。如果不指定MAX\_VALUE，那么工具会这只其为当前值的120%。

–critical-load  <array> 默认为Threads\_running=50。用法基本与–max-load类似，如果不指定MAX\_VALUE，那么工具会这只其为当前值的200%。如果超过指定值，则工具直接退出，而不是暂停。

–lock-wait-timeout <整型> 默认为1，即会话innodb\_lock\_wait\_timeout的值为1秒。这个选项只针对InnoDB引擎。

–alter <string> 结构变更语句，不需要alter table关键字。可以指定多个更改，用逗号分隔。如下场景，需要注意：  
不能用RENAME来重命名表。        \* 列不能通过先删除，再添加的方式进行重命名，不会将数据拷贝到新列。  
如果加入的列非空而且没有默认值，则工具会失败。即其不会为你设置一个默认值，必须显示指定。  
删除外键(drop foreign key constrain\_name)时，需要指定名称\_constraint\_name，而不是原始的constraint\_name。  
–dry-run 并不真正执行，可以看到生成的执行语句，了解其执行步骤与细节  
–execute 真正执行。–dry-run与–execute必须指定一个  
–\[no\]drop-old-table   删除RENAME后的原表。默认是yes。

典型用法

1）添加一列，并不真正执行  
pt-online-schema-change –alter “add column c1 int” D=mydb,t=mytable –dry-run

2）更新存储引擎为InnoDB，不删除原表  
pt-online-schema-change –alter “ENGINE=InnoDB” –no-drop-old-table –print –statistics –execute D=mydb,t=mytable –execute

3）复制环境下，忽略日志筛选和Slave复制延迟，删除表字段  
pt-online-schema-change –no-check-replication-filters –recursion-method=none –alter “drop company\_type,drop channel\_code” h=192.168.10.14,P=3370,u=user1,p=pass1,D=db1,t=table1 –print –statistics –execute

4）更新被子表引用到的父表  
pt-online-schema-change –alter “add newcol int” h=192.168.10.14,P=3370,u=user1,p=pass1,D=db1,t=table1 –alter-foreign-keys-method auto –print –statistics –execute

5）在我们的双主复制环境中，设定了忽略mysql库的复制，不是很在乎复制的延迟，有时有外键影响，希望尽量保留原表数据，必要时自行删除。  
pt-online-schema-change –no-check-replication-filters –recursion-method=none –alter “drop newcol” h=192.168.10.14,P=3370,u=user1,p=pass1,D=db1,t=table1 –alter-foreign-keys-method auto –no-drop-old-table –print –statistics –execute