MySQL ONLINE DDL和PT-ONLINE-SCHEMA-CHANGE有哪些区别

这篇文章主要介绍“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文件
　　变更完成

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有哪些区别”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！