reign_key_checks=0
200 Query DROP TABLE IF EXISTS `employees`.`employees`
200 Query RENAME TABLE `employees`.`_employees_new` TO `employees`.`employees`
'
当--alter-foreign-keys-method=rebuild_constraints时,做一个原子性的交换重命名表的操作,删除旧表的操作在删除触发器时一并操作
'
203 Query ANALYZE TABLE `employees`.`_employees_new` /* pt-online-schema-change */
203 Query RENAME TABLE `employees`.`employees` TO `employees`.`_employees_old`, `employees`.`_employees_new` TO `employees`.`employees`
-- 删除3个触发器
'
当--alter-foreign-keys-method=drop_swap时,直接删除。
'
200 Query DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_del`
200 Query DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_upd`
200 Query DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_ins`
200 Query SHOW TABLES FROM `employees` LIKE '\_employees\_new'
201 Quit
200 Quit
'
当--alter-foreign-keys-method=rebuild_constraints时,对于关联的外键表执行重建外键操作,删除旧表,完成变更。
'
203 Query USE `employees`
203 Query SHOW CREATE TABLE `employees`.`dept_emp`
203 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
203 Query ALTER TABLE `employees`.`dept_emp` DROP FOREIGN KEY `_dept_emp_ibfk_1`, ADD CONSTRAINT `__dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees`.`employees` (`emp_no`) ON DELETE CASCADE
203 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
203 Query USE `employees`
203 Query SHOW CREATE TABLE `employees`.`dept_manager`
203 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
203 Query ALTER TABLE `employees`.`dept_manager` DROP FOREIGN KEY `__dept_manager_ibfk_1`, ADD CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees`.`employees` (`emp_no`) ON DELETE CASCADE
203 Query DROP TABLE IF EXISTS `employees`.`_employees_old`
203 Query DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_del`
203 Query DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_upd`
203 Query DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_ins`
203 Query SHOW TABLES FROM `employees` LIKE '\_employees\_new'
204 Quit
203 Quit
工作流程总结:
- 查询当前数据库服务器信息,包括参数设置,负载信息等,判断表是否有存在触发器,是否有外键关联;
- 创建一张与旧表结构相同的新表,表名为
_旧表名
;
- 在新创建的表上做变更操作;
- 旧表上创建
DELETE
、UPDATE
、INSERT
3个触发器;
- 拷贝旧表数据到新表上,以
chunk
为单位进行,拷贝期间涉及的行会持有共享读锁;
- 拷贝期间如果旧表如有
DML
操作,则通过触发器更新同步到新表上;
- 当拷贝数据完成之后旧表与新表进行重命名;
- 如果有涉及到外键,根据工具指定选项进行外键处理;
- 删除旧表;
- 删除旧表上触发器。
总结
pt-online-schema-change工具对于任意的DDL语句都是通过创新表拷贝数据来进行,期间都支持DML,而Online DDL根据DDL类型的来区分是否需要对表进行COPY TABLE操作,有点类似于工具的创建临时表进行变更,而不需要COPY TABLE操作的DDL语句在执行期间支持DML。
关于在对表进行DDL时使用MySQL原生的Online DDL特性还是使用pt-online-schema-change工具,通过以上对工具使用的说明与用法测试可以总结如下:
- 如果MySQL版本不支持Online DDL特性,比如早于5.6版本的MySQL,则使用pt-online-schema-change工具;
- 如果MySQL版本支持Online DDL特性,则优先考虑使用Online DDL,因为毕竟原生的支持较好,同时不容易产生不可预知的错误;
- 如果DDL语句在使用Online DDL时需要进行COPY TABLE操作,建议使用pt-online-schema-change工具,因为期间支持DML操作。
- 如果表存在触发器的情况下,优先使用Online DDL,对于MySQL5.7.2之后版本则可以pt-online-schema-change工具并通过指定选项
--preserve-triggers
;
- 如果涉及外键关联的表,优先考虑使用Online DDL。
关于pt-online-schema-change更多的说明可以参考官方文档:https://www.percona.com/downloads/percona-toolkit/LATEST/
参考
https://www.percona.com/dow