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

网友投稿 298 2023-12-27

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有哪些区别”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!

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`.

点击(此处)折叠或打开

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

66 Query     SHOW VARIABLES LIKE innodb\_lock_wait_timeout

66 Query     SET SESSION innodb_lock_wait_timeout=1

                   66 Query     SHOW VARIABLES LIKE lock\_wait_timeout

                   66 Query     SET SESSION lock_wait_timeout=60

66 Query     SHOW VARIABLES LIKE wait\_timeout

                   66 Query     SET SESSION wait_timeout=10000

66 Query SELECT @@SQL_MODE

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

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

67 Connect   dbuser@BX-128-28 on dbtest

                   67 Query     SHOW VARIABLES LIKE innodb\_lock_wait_timeout

67 Query     SET SESSION innodb_lock_wait_timeout=1

                   67 Query     SHOW VARIABLES LIKE lock\_wait_timeout

67 Query     SET SESSION lock_wait_timeout=60

                   67 Query     SHOW VARIABLES LIKE wait\_timeout

67 Query     SET SESSION wait_timeout=10000

                   67 Query SELECT @@SQL_MODE

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

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

66 Query     SHOW VARIABLES LIKE wsrep_on

                   66 Query     SHOW VARIABLES LIKE version%

66 Query     SHOW ENGINES

                   66 Query     SHOW VARIABLES LIKE innodb_version

66 Query     SHOW VARIABLES LIKE innodb_stats_persistent

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

                   66 Query     SHOW TABLES FROM `dbtest` LIKE t1

66 Query     SHOW TRIGGERS FROM `dbtest` LIKE t1

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

66 Query     USE `dbtest`

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

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

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

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

                   66 Query     SHOW VARIABLES LIKE wsrep_on

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

                   66 Query     USE `dbtest`

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

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

66 Query     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

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

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

66 Query     USE `dbtest`

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

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

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

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

                   66 Query     SHOW TABLES FROM `dbtest` LIKE \_t1\_new

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

                   67 Quit

                   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

点击(此处)折叠或打开

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

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

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.

Altering new table...

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

Altered `dbtest`.`_t1_new` OK.

Not creating triggers because this is a dry run.

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`)

Not copying rows because this is a dry run.

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*/

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

Not swapping tables because this is a dry run.

Not dropping old table because this is a dry run.

Not dropping triggers because this is a dry run.

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-19T17:53:00 Dropping new table...

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

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

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

点击(此处)折叠或打开

No slaves found. See --recursion-method if host BX-128-28 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

Altering `dbtest`.`t1`...

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.

Altering new table...

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

Altered `dbtest`.`_t1_new` OK.

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.

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.

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

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.

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.

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

点击(此处)折叠或打开

No slaves found. See --recursion-method if host BX-128-28 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

Altering `dbtest`.`t1`...

Creating new table...

CREATE TABLE `dbtest`.`_t1_new` (

`id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(30) DEFAULT NULL,

  `phone` varchar(15) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1809061 DEFAULT CHARSET=utf8

Created new table dbtest._t1_new OK.

Altering new table...

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

Altered `dbtest`.`_t1_new` OK.

2017-11-19T22:56:33 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-19T22:56:33 Created triggers OK.

2017-11-19T22:56:33 Copying approximately 1597892 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 9444 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-19T22:56:52 Copied rows OK.

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

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

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

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

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

DROP TABLE IF EXISTS `dbtest`.`_t1_old`

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

2017-11-19T22:56:52 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-19T22:56:52 Dropped triggers OK.

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

点击(此处)折叠或打开

No slaves found. See --recursion-method if host BX-128-28 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

Altering `dbtest`.`t1`...

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.

Altering new table...

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

Altered `dbtest`.`_t1_new` OK.

2017-11-19T23:00:40 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-19T23:00:40 Created triggers OK.

2017-11-19T23:00:40 Copying approximately 1559718 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 9453 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-19T23:01:09 Copied rows OK.

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

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

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

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

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

DROP TABLE IF EXISTS `dbtest`.`_t1_old`

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

2017-11-19T23:01:09 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-19T23:01:09 Dropped triggers OK.

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

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:怎么理解show status的计数器
下一篇:怎么使用mysql的socket文件
相关文章

 发表评论

暂时没有评论,来抢沙发吧~