天达云 科技型企业 | 亚太互联网络信息中心(APNIC)成员 | 注册免费体验
天达云首页 > 互联网学习教程 > MySQL数据库 > MySQL ONLINE DDL和PT-ONLINE-SCHEMA-CHANGE有哪些区别

MySQL ONLINE DDL和PT-ONLINE-SCHEMA-CHANGE有哪些区别
更新：HHH   时间：2023-1-7

这篇文章主要介绍“MySQL ONLINE DDL和PT-ONLINE-SCHEMA-CHANGE有哪些区别”，在日常操作中，相信很多人在MySQL ONLINE DDL和PT-ONLINE-SCHEMA-CHANGE有哪些区别问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL ONLINE DDL和PT-ONLINE-SCHEMA-CHANGE有哪些区别”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

mysql5.6之前执行ddl语句会执行表锁，只允许查询不允许更新，执行ddl主要有两种方式copy方式和inplace方式，inplace方式又称为(fast index creation)，其中copy方式全称表锁，inplace方式只支持二级索引添加和删除。5.6之后可以利用Online DDL特性完成在线表结构调整，而pt-tools提供的pt-online-schema-change可以在几乎无表锁的情况下完成在线表结构调整，这里就针对mysql做下ddl的过程研究。
mysql原生的ddl方式实现形式有三种，分别是copy table、inplace、online ddl的方式，其中5.1-5.5版本实现是通过copy table的形式,5.6-5.7增加了inplace方式和Online ddl方式。
一、mysql 原生ddl实现方式
copy方式
　　(1).新建临时表
　　(2).锁原表，禁止DML，允许查询
　　(3).将原表数据拷贝到临时表(无排序，一行一行拷贝)
　　(4).删除原表，对临时表进行rename，升级字典锁，禁止读写
　　(5).完成DDL，释放锁
inplace方式
　　(1).新建索引的数据字典
　　(2).锁表，禁止DML，允许查询
　　(3).读取聚集索引，构造新的索引项，排序并插入新索引
　　(4).等待打开当前表的所有只读事务提交
　　(5).创建索引结束
online ddl实现
　　online方式实质也包含了copy和inplace方式，对于不支持online的ddl操作采用copy方式，比如修改列类型，删除主键等；对于inplace方式，mysql内部以“是否修改记录格式”为基准也分为两类，一类需要重建表(修改记录格式)，比如添加、删除列、修改列默认值等；另外一类是只需要修改表的元数据，比如添加、删除索引、修改列名等。Mysql将这两类方式分别称为rebuild方式和no-rebuild方式。online ddl主要包括3个阶段，prepare阶段，ddl执行阶段，commit阶段，rebuild方式比no-rebuild方式实质多了一个ddl执行阶段，prepare阶段和commit阶段类似。下面将主要介绍ddl执行过程中三个阶段的流程。
　　Prepare阶段：
　　创建新的临时frm文件
　　持有EXCLUSIVE-MDL锁，禁止读写
　　根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)
　　更新数据字典的内存对象
　　分配row_log对象记录增量
　　生成新的临时ibd文件
　　ddl执行阶段：
　　降级EXCLUSIVE-MDL锁，允许读写
　　扫描old_table的聚集索引每一条记录rec
　　遍历新表的聚集索引和二级索引，逐一处理
　　根据rec构造对应的索引项
　　将构造索引项插入sort_buffer块
　　将sort_buffer块插入新的索引
　　处理ddl执行过程中产生的增量(仅rebuild类型需要)
　　commit阶段
　　升级到EXCLUSIVE-MDL锁，禁止读写
　　重做最后row_log中最后一部分增量
　　更新innodb的数据字典表
　　提交事务(刷事务的redo日志)
　　修改统计信息
　　rename临时idb文件，frm文件
　　变更完成

Operation In-Place? Copies Table? Allows Concurrent DML? Allows Concurrent Query? Notes
添加索引 Yes* No* Yes Yes 对全文索引的一些限制
删除索引 Yes No Yes Yes 仅修改表的元数据
OPTIMIZE TABLE Yes Yes Yes Yes 从 5.6.17开始使用ALGORITHM=INPLACE，当然如果指定了old_alter_table=1或mysqld启动带--skip-new则将还是COPY模式。如果表上有全文索引只支持COPY
对一列设置默认值 Yes No Yes Yes 仅修改表的元数据
对一列修改auto-increment 的值 Yes No Yes Yes 仅修改表的元数据
添加 foreign key constraint Yes* No* Yes Yes 为了避免拷贝表，在约束创建时会禁用foreign_key_checks
删除 foreign key constraint Yes No Yes Yes foreign_key_checks 不影响
改变列名 Yes* No* Yes* Yes 为了允许DML并发, 如果保持相同数据类型，仅改变列名
添加列 Yes* Yes* Yes* Yes 尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作。当添加列是auto-increment，不允许DML并发
删除列 Yes Yes* Yes Yes 尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作
修改列数据类型 No Yes* No Yes 修改类型或添加长度，都会拷贝表，而且不允许更新操作
更改列顺序 Yes Yes Yes Yes 尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作
修改ROW_FORMAT
和KEY_BLOCK_SIZE 		Yes Yes Yes Yes 尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作
设置列属性NULL
或NOT NULL 		Yes Yes Yes Yes 尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作
添加主键 Yes* Yes Yes Yes 尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作。
如果列定义必须转化NOT NULL，则不允许INPLACE
删除并添加主键 Yes Yes Yes Yes 在同一个 ALTER TABLE 语句删除就主键、添加新主键时，才允许inplace；数据大幅重组,所以它仍然是一项昂贵的操作。
删除主键 No Yes No Yes 不允许并发DML，要拷贝表，而且如果没有在同一 ATLER TABLE 语句里同时添加主键则会收到限制
变更表字符集 No Yes No Yes 如果新的字符集编码不同，重建表


mysql 5.7在线修改表结构案例：
语法
alter table
| ALGORITHM [=] {DEFAULT|INPLACE|COPY}
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
online ddl的原理是，mysql把在ddl时间内的所有的 插入，更新和删除操作记录到一个日志文件， 然后再把这些增量数据应用到相应的表上(等表上的事务完全释放后)，这个临时日志文件的上限值由innodb_online_alter_log_max_size指定,每次扩展innodb_sort_buffer_size的大小 该参数如果太小有可能导致DDL失败

二、pt-online-schema-change
注意事项：
    （1）表存在主键或唯一建
    （2）磁盘容量估计
    （3）原表不存在触发器
    （4）原表进行批量DML操作时，会有一定影响，需特别注意锁等待等参数设置
    （5）如果更新的表是被子表外键引用的父表，那么需要相应的更新子表的外键指向
1、online ddl原理
（1）设置mysql会话参数
    SET SESSION innodb_lock_wait_timeout=1
    SET SESSION lock_wait_timeout=60
    SET SESSION wait_timeout=10000
    SET @@SQL_QUOTE_SHOW_CREATE = 1,@@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION' 
（2）检查表结构，是否存在主键、其他外键参考、触发器
    SHOW TRIGGERS FROM `dbtest` LIKE 't1'
    SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='dbtest' AND referenced_table_name='t1'
（3）创建新表
     Creating new table...
    CREATE TABLE `dbtest`.`_t1_new` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(30) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1809061 DEFAULT CHARSET=utf8
    Created new table dbtest._t1_new OK.
（4）对新表进行DDL操作
    Altering new table...
    ALTER TABLE `dbtest`.`_t1_new` ADD COLUMN phone varchar(15)
    Altered `dbtest`.`_t1_new` OK.
（5）对旧表创建触发器（insert/update/delete）
    2017-11-19T18:05:26 Creating triggers...
    CREATE TRIGGER `pt_osc_dbtest_t1_del` AFTER DELETE ON `dbtest`.`t1` FOR EACH ROW DELETE IGNORE FROM `dbtest`.`_t1_new` WHERE `dbtest`.`_t1_new`.`id` <=> OLD.`id`
    CREATE TRIGGER `pt_osc_dbtest_t1_upd` AFTER UPDATE ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)
    CREATE TRIGGER `pt_osc_dbtest_t1_ins` AFTER INSERT ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)
    2017-11-19T18:05:26 Created triggers OK.
（6）copy数据
    copy数据一个chunk后会检查thread_running负载、warning、从库信息决定是否继续copy，默认--chunk-time=0.5,根据这个时间copy的记录动态调整chunk-size，在Copy相关的chunk时，会对原表相关记录加S锁。
    2017-11-19T18:05:26 Copying approximately 1593410 rows...
    INSERT LOW_PRIORITY IGNORE INTO `dbtest`.`_t1_new` (`id`, `name`) SELECT `id`, `name` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 9157 copy nibble*/
    SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
    2017-11-19T18:05:45 Copied rows OK.
（7）分析新表、统计信息
    2017-11-19T18:05:45 Analyzing new table..
（8）新、旧表交换，将旧表t1重命名为_t1_old，将新表_t1_new重命名为t1，并删除旧表_t1_old
    2017-11-19T18:05:45 Swapping tables...
    RENAME TABLE `dbtest`.`t1` TO `dbtest`.`_t1_old`, `dbtest`.`_t1_new` TO `dbtest`.`t1`
    2017-11-19T18:05:45 Swapped original and new tables OK.
    2017-11-19T18:05:45 Dropping old table...
    DROP TABLE IF EXISTS `dbtest`.`_t1_old`
    2017-11-19T18:05:45 Dropped old table `dbtest`.`_t1_old` OK.
（9）删除触发器
    2017-11-19T18:05:45 Dropping triggers...
    DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_del`;
    DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_upd`;
    DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_ins`;
    2017-11-19T18:05:45 Dropped triggers OK.
（10）完成表结构在线修改
Successfully altered `dbtest`.`t1`.

点击(此处)折叠或打开

  1. 171119 17:53:00    66 Connect   dbuser@BX-128-28 on dbtest

  2.                    66 Query     SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'

  3.                    66 Query     SET SESSION innodb_lock_wait_timeout=1

  4.                    66 Query     SHOW VARIABLES LIKE 'lock\_wait_timeout'

  5.                    66 Query     SET SESSION lock_wait_timeout=60

  6.                    66 Query     SHOW VARIABLES LIKE 'wait\_timeout'

  7.                    66 Query     SET SESSION wait_timeout=10000

  8.                    66 Query SELECT @@SQL_MODE

  9.                    66 Query     SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/

  10.                    66 Query SELECT @@server_id /*!50038 , @@hostname*/

  11.                    67 Connect   dbuser@BX-128-28 on dbtest

  12.                    67 Query     SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'

  13.                    67 Query     SET SESSION innodb_lock_wait_timeout=1

  14.                    67 Query     SHOW VARIABLES LIKE 'lock\_wait_timeout'

  15.                    67 Query     SET SESSION lock_wait_timeout=60

  16.                    67 Query     SHOW VARIABLES LIKE 'wait\_timeout'

  17.                    67 Query     SET SESSION wait_timeout=10000

  18.                    67 Query SELECT @@SQL_MODE

  19.                    67 Query     SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/

  20.                    67 Query SELECT @@server_id /*!50038 , @@hostname*/

  21.                    66 Query     SHOW VARIABLES LIKE 'wsrep_on'

  22.                    66 Query     SHOW VARIABLES LIKE 'version%'

  23.                    66 Query     SHOW ENGINES

  24.                    66 Query     SHOW VARIABLES LIKE 'innodb_version'

  25.                    66 Query     SHOW VARIABLES LIKE 'innodb_stats_persistent'

  26.                    66 Query SELECT CONCAT(@@hostname, @@port)

  27.                    66 Query     SHOW TABLES FROM `dbtest` LIKE 't1'

  28.                    66 Query     SHOW TRIGGERS FROM `dbtest` LIKE 't1'

  29.                    66 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */

  30.                    66 Query     USE `dbtest`

  31.                    66 Query     SHOW CREATE TABLE `dbtest`.`t1`

  32.                    66 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */

  33.                    66 Query     EXPLAIN SELECT * FROM `dbtest`.`t1` WHERE 1=1

  34.                    66 Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='dbtest' AND referenced_table_name='t1'

  35.                    66 Query     SHOW VARIABLES LIKE 'wsrep_on'

  36.                    66 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */

  37.                    66 Query     USE `dbtest`

  38.                    66 Query     SHOW CREATE TABLE `dbtest`.`t1`

  39.                    66 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */

  40.                    66 Query     CREATE TABLE `dbtest`.`_t1_new` (

  41.   `id` int(11) NOT NULL AUTO_INCREMENT,

  42.   `name` varchar(30) DEFAULT NULL,

  43.   PRIMARY KEY (`id`)

  44. ) ENGINE=InnoDB AUTO_INCREMENT=1809061 DEFAULT CHARSET=utf8

  45.                    66 Query     ALTER TABLE `dbtest`.`_t1_new` ADD COLUMN phone varchar(15)

  46.                    66 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */

  47.                    66 Query     USE `dbtest`

  48.                    66 Query     SHOW CREATE TABLE `dbtest`.`_t1_new`

  49.                    66 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */

  50.                    66 Query     EXPLAIN SELECT * FROM `dbtest`.`t1` WHERE 1=1

  51.                    66 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) ORDER BY `id` LIMIT 1 /*first lower boundary*/

  52.                    66 Query     SHOW TABLES FROM `dbtest` LIKE '\_t1\_new'

  53.                    66 Query     DROP TABLE IF EXISTS `dbtest`.`_t1_new`

  54.                    67 Quit

  55.                    66 Quit

2、增加字段
预执行：pt-online-schema-change --user=dbuser --password=123456 --host=10.xx  --alter "ADD COLUMN phone varchar(15)" D=dbtest,t=t1 --print --dry-run

点击(此处)折叠或打开

  1. Operation, tries, wait:

  2.   analyze_table, 10, 1

  3.   copy_rows, 10, 0.25

  4.   create_triggers, 10, 1

  5.   drop_triggers, 10, 1

  6.   swap_tables, 10, 1

  7.   update_foreign_keys, 10, 1

  8. Starting a dry run. `dbtest`.`t1` will not be altered. Specify --execute instead of --dry-run to alter the table.

  9. Creating new table...

  10. CREATE TABLE `dbtest`.`_t1_new` (

  11.   `id` int(11) NOT NULL AUTO_INCREMENT,

  12.   `name` varchar(30) DEFAULT NULL,

  13.   PRIMARY KEY (`id`)

  14. ) ENGINE=InnoDB AUTO_INCREMENT=1809061 DEFAULT CHARSET=utf8

  15. Created new table dbtest._t1_new OK.

  16. Altering new table...

  17. ALTER TABLE `dbtest`.`_t1_new` ADD COLUMN phone varchar(15)

  18. Altered `dbtest`.`_t1_new` OK.

  19. Not creating triggers because this is a dry run.

  20. CREATE TRIGGER `pt_osc_dbtest_t1_del` AFTER DELETE ON `dbtest`.`t1` FOR EACH ROW DELETE IGNORE FROM `dbtest`.`_t1_new` WHERE `dbtest`.`_t1_new`.`id` <=> OLD.`id`

  21. CREATE TRIGGER `pt_osc_dbtest_t1_upd` AFTER UPDATE ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)

  22. CREATE TRIGGER `pt_osc_dbtest_t1_ins` AFTER INSERT ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)

  23. Not copying rows because this is a dry run.

  24. INSERT LOW_PRIORITY IGNORE INTO `dbtest`.`_t1_new` (`id`, `name`) SELECT `id`, `name` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 9137 copy nibble*/

  25. SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/

  26. Not swapping tables because this is a dry run.

  27. Not dropping old table because this is a dry run.

  28. Not dropping triggers because this is a dry run.

  29. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_del`;

  30. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_upd`;

  31. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_ins`;

  32. 2017-11-19T17:53:00 Dropping new table...

  33. DROP TABLE IF EXISTS `dbtest`.`_t1_new`;

  34. 2017-11-19T17:53:00 Dropped new table OK.

  35. Dry run complete. `dbtest`.`t1` was not altered.

正式执行：pt-online-schema-change --user=dbuser --password=123456 --host=10.xx  --alter "ADD COLUMN phone varchar(15)" D=dbtest,t=t1 --print --execute

点击(此处)折叠或打开

  1. No slaves found. See --recursion-method if host BX-128-28 has slaves.

  2. Not checking slave lag because no slaves were found and --check-slave-lag was not specified.

  3. Operation, tries, wait:

  4.   analyze_table, 10, 1

  5.   copy_rows, 10, 0.25

  6.   create_triggers, 10, 1

  7.   drop_triggers, 10, 1

  8.   swap_tables, 10, 1

  9.   update_foreign_keys, 10, 1

  10. Altering `dbtest`.`t1`...

  11. Creating new table...

  12. CREATE TABLE `dbtest`.`_t1_new` (

  13.   `id` int(11) NOT NULL AUTO_INCREMENT,

  14.   `name` varchar(30) DEFAULT NULL,

  15.   PRIMARY KEY (`id`)

  16. ) ENGINE=InnoDB AUTO_INCREMENT=1809061 DEFAULT CHARSET=utf8

  17. Created new table dbtest._t1_new OK.

  18. Altering new table...

  19. ALTER TABLE `dbtest`.`_t1_new` ADD COLUMN phone varchar(15)

  20. Altered `dbtest`.`_t1_new` OK.

  21. 2017-11-19T18:05:26 Creating triggers...

  22. CREATE TRIGGER `pt_osc_dbtest_t1_del` AFTER DELETE ON `dbtest`.`t1` FOR EACH ROW DELETE IGNORE FROM `dbtest`.`_t1_new` WHERE `dbtest`.`_t1_new`.`id` <=> OLD.`id`

  23. CREATE TRIGGER `pt_osc_dbtest_t1_upd` AFTER UPDATE ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)

  24. CREATE TRIGGER `pt_osc_dbtest_t1_ins` AFTER INSERT ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)

  25. 2017-11-19T18:05:26 Created triggers OK.

  26. 2017-11-19T18:05:26 Copying approximately 1593410 rows...

  27. INSERT LOW_PRIORITY IGNORE INTO `dbtest`.`_t1_new` (`id`, `name`) SELECT `id`, `name` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 9157 copy nibble*/

  28. SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/

  29. 2017-11-19T18:05:45 Copied rows OK.

  30. 2017-11-19T18:05:45 Analyzing new table...

  31. 2017-11-19T18:05:45 Swapping tables...

  32. RENAME TABLE `dbtest`.`t1` TO `dbtest`.`_t1_old`, `dbtest`.`_t1_new` TO `dbtest`.`t1`

  33. 2017-11-19T18:05:45 Swapped original and new tables OK.

  34. 2017-11-19T18:05:45 Dropping old table...

  35. DROP TABLE IF EXISTS `dbtest`.`_t1_old`

  36. 2017-11-19T18:05:45 Dropped old table `dbtest`.`_t1_old` OK.

  37. 2017-11-19T18:05:45 Dropping triggers...

  38. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_del`;

  39. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_upd`;

  40. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_ins`;

  41. 2017-11-19T18:05:45 Dropped triggers OK.

  42. Successfully altered `dbtest`.`t1`.

3、删除字段
pt-online-schema-change --user=dbuser --password=123456 --host=10.xx  --no-check-replication-filters  --recursion-method=none  --alter "DROP COLUMN phone " D=dbtest,t=t1 --print --execute

点击(此处)折叠或打开

  1. No slaves found. See --recursion-method if host BX-128-28 has slaves.

  2. Not checking slave lag because no slaves were found and --check-slave-lag was not specified.

  3. Operation, tries, wait:

  4.   analyze_table, 10, 1

  5.   copy_rows, 10, 0.25

  6.   create_triggers, 10, 1

  7.   drop_triggers, 10, 1

  8.   swap_tables, 10, 1

  9.   update_foreign_keys, 10, 1

  10. Altering `dbtest`.`t1`...

  11. Creating new table...

  12. CREATE TABLE `dbtest`.`_t1_new` (

  13.   `id` int(11) NOT NULL AUTO_INCREMENT,

  14.   `name` varchar(30) DEFAULT NULL,

  15.   `phone` varchar(15) DEFAULT NULL,

  16.   PRIMARY KEY (`id`)

  17. ) ENGINE=InnoDB AUTO_INCREMENT=1809061 DEFAULT CHARSET=utf8

  18. Created new table dbtest._t1_new OK.

  19. Altering new table...

  20. ALTER TABLE `dbtest`.`_t1_new` DROP COLUMN phone

  21. Altered `dbtest`.`_t1_new` OK.

  22. 2017-11-19T22:56:33 Creating triggers...

  23. CREATE TRIGGER `pt_osc_dbtest_t1_del` AFTER DELETE ON `dbtest`.`t1` FOR EACH ROW DELETE IGNORE FROM `dbtest`.`_t1_new` WHERE `dbtest`.`_t1_new`.`id` <=> OLD.`id`

  24. CREATE TRIGGER `pt_osc_dbtest_t1_upd` AFTER UPDATE ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)

  25. CREATE TRIGGER `pt_osc_dbtest_t1_ins` AFTER INSERT ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)

  26. 2017-11-19T22:56:33 Created triggers OK.

  27. 2017-11-19T22:56:33 Copying approximately 1597892 rows...

  28. INSERT LOW_PRIORITY IGNORE INTO `dbtest`.`_t1_new` (`id`, `name`) SELECT `id`, `name` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 9444 copy nibble*/

  29. SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/

  30. 2017-11-19T22:56:52 Copied rows OK.

  31. 2017-11-19T22:56:52 Analyzing new table...

  32. 2017-11-19T22:56:52 Swapping tables...

  33. RENAME TABLE `dbtest`.`t1` TO `dbtest`.`_t1_old`, `dbtest`.`_t1_new` TO `dbtest`.`t1`

  34. 2017-11-19T22:56:52 Swapped original and new tables OK.

  35. 2017-11-19T22:56:52 Dropping old table...

  36. DROP TABLE IF EXISTS `dbtest`.`_t1_old`

  37. 2017-11-19T22:56:52 Dropped old table `dbtest`.`_t1_old` OK.

  38. 2017-11-19T22:56:52 Dropping triggers...

  39. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_del`;

  40. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_upd`;

  41. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_ins`;

  42. 2017-11-19T22:56:52 Dropped triggers OK.

  43. Successfully altered `dbtest`.`t1`.

4、添加索引
pt-online-schema-change --user=dbuser --password=123456 --host=10.xx  --no-check-replication-filters  --recursion-method=none  --alter "add   key idx_name(name)" D=dbtest,t=t1 --print --execute

点击(此处)折叠或打开

  1. No slaves found. See --recursion-method if host BX-128-28 has slaves.

  2. Not checking slave lag because no slaves were found and --check-slave-lag was not specified.

  3. Operation, tries, wait:

  4.   analyze_table, 10, 1

  5.   copy_rows, 10, 0.25

  6.   create_triggers, 10, 1

  7.   drop_triggers, 10, 1

  8.   swap_tables, 10, 1

  9.   update_foreign_keys, 10, 1

  10. Altering `dbtest`.`t1`...

  11. Creating new table...

  12. CREATE TABLE `dbtest`.`_t1_new` (

  13.   `id` int(11) NOT NULL AUTO_INCREMENT,

  14.   `name` varchar(30) DEFAULT NULL,

  15.   PRIMARY KEY (`id`)

  16. ) ENGINE=InnoDB AUTO_INCREMENT=1809061 DEFAULT CHARSET=utf8

  17. Created new table dbtest._t1_new OK.

  18. Altering new table...

  19. ALTER TABLE `dbtest`.`_t1_new` add key(name)

  20. Altered `dbtest`.`_t1_new` OK.

  21. 2017-11-19T23:00:40 Creating triggers...

  22. CREATE TRIGGER `pt_osc_dbtest_t1_del` AFTER DELETE ON `dbtest`.`t1` FOR EACH ROW DELETE IGNORE FROM `dbtest`.`_t1_new` WHERE `dbtest`.`_t1_new`.`id` <=> OLD.`id`

  23. CREATE TRIGGER `pt_osc_dbtest_t1_upd` AFTER UPDATE ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)

  24. CREATE TRIGGER `pt_osc_dbtest_t1_ins` AFTER INSERT ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)

  25. 2017-11-19T23:00:40 Created triggers OK.

  26. 2017-11-19T23:00:40 Copying approximately 1559718 rows...

  27. INSERT LOW_PRIORITY IGNORE INTO `dbtest`.`_t1_new` (`id`, `name`) SELECT `id`, `name` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 9453 copy nibble*/

  28. SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/

  29. 2017-11-19T23:01:09 Copied rows OK.

  30. 2017-11-19T23:01:09 Analyzing new table...

  31. 2017-11-19T23:01:09 Swapping tables...

  32. RENAME TABLE `dbtest`.`t1` TO `dbtest`.`_t1_old`, `dbtest`.`_t1_new` TO `dbtest`.`t1`

  33. 2017-11-19T23:01:09 Swapped original and new tables OK.

  34. 2017-11-19T23:01:09 Dropping old table...

  35. DROP TABLE IF EXISTS `dbtest`.`_t1_old`

  36. 2017-11-19T23:01:09 Dropped old table `dbtest`.`_t1_old` OK.

  37. 2017-11-19T23:01:09 Dropping triggers...

  38. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_del`;

  39. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_upd`;

  40. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_ins`;

  41. 2017-11-19T23:01:09 Dropped triggers OK.

  42. Successfully altered `dbtest`.`t1`.

5、删除索引
pt-online-schema-change --user=dbuser --password=123456 --host=10.xx  --no-check-replication-filters  --recursion-method=none  --alter "DROP   key idx_name" D=dbtest,t=t1 --print --execute
6、改变字段类型、长度
pt-online-schema-change --user=dbuser --password=123456 --host=10.xx  --no-check-replication-filters  --recursion-method=none  --alter "modify name varchar(10)" D=dbtest,t=t1 --print --execute

到此，关于“MySQL ONLINE DDL和PT-ONLINE-SCHEMA-CHANGE有哪些区别”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注天达云网站，小编会继续努力为大家带来更多实用的文章！
返回MySQL数据库教程...
400-805-1963
7 * 24小时全天全国服务热线400电话
天达云·专业的云计算服务平台 全国400服务电话：400-805-1963
Copyright © 2011-2026 Tdyun.com. All Rights Reserved. 天达云 版权所有 粤ICP备18083267号-7