Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the differences between MySQL ONLINE DDL and PT-ONLINE-SCHEMA-CHANGE". In daily operation, I believe many people have doubts about the differences between MySQL ONLINE DDL and PT-ONLINE-SCHEMA-CHANGE. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the questions of "what are the differences between MySQL ONLINE DDL and PT-ONLINE-SCHEMA-CHANGE?" Next, please follow the editor to study!

Executing ddl statements before mysql5.6 executes table locks, and only queries are allowed but not updates. There are two main ways to execute ddl: copy and inplace. Inplace is also known as (fast index creation), in which copy is the full name of table lock, and inplace only supports the addition and deletion of secondary indexes. After that, the online table structure adjustment can be completed by using the characteristics of Online DDL, while the pt-online-schema-change provided by pt-tools can complete the online table structure adjustment without table lock. This paper studies the process of ddl for mysql.

There are three native ddl implementations of mysql, which are copy table, inplace and online ddl. Version 5.1-5.5 is implemented in the form of copy table, and inplace and Online ddl are added in the 5.65.7.

1. Mysql native ddl implementation

Copy mode

(1)。 New temporary table

(2)。 Lock the original table, disable DML, allow query

(3)。 Copy the original table data to a temporary table (no sort, one-line copy)

(4)。 Delete the original table, rename the temporary table, upgrade the dictionary lock, and prohibit reading and writing

(5)。 Complete DDL and release the lock

Inplace mode

(1)。 Create a data dictionary for the index

(2)。 Lock table, disable DML, allow query

(3)。 Read clustered indexes, construct new index entries, sort and insert new indexes

(4)。 Waiting for all read-only transactions that open the current table to commit

(5)。 End of index creation

Online ddl implementation

In essence, online mode also includes copy and inplace methods. For ddl operations that do not support online, copy mode is used, such as changing column type, deleting primary key, etc. For inplace mode, mysql is also divided into two categories based on "whether to modify record format". One category needs to rebuild tables (modify record format), such as adding, deleting columns, modifying column default values, etc. The other is that you only need to modify the metadata of the table, such as adding, deleting indexes, changing column names, and so on. Mysql refers to these two methods as rebuild mode and no-rebuild mode respectively. Online ddl mainly includes three stages: prepare stage, ddl implementation stage and commit stage. Rebuild has essentially one more ddl implementation stage than no-rebuild, and the prepare stage is similar to the commit stage. The following will focus on the process of the three stages of ddl execution.

Prepare phase:

Create a new temporary frm file

Holds EXCLUSIVE-MDL lock, forbids reading and writing

Determine the execution mode (copy,online-rebuild,online-norebuild) according to the alter type

Update the memory object of the data dictionary

Assign row_log object record increments

Generate a new temporary ibd file

Ddl execution phase:

Downgrade EXCLUSIVE-MDL lock to allow read and write

Scan every record in old_table 's clustered index rec

Traverse the clustered index and secondary index of the new table, processing them one by one

Construct the corresponding index entry according to rec

Insert the construction index entry into the sort_buffer block

Insert a sort_buffer block into a new index

Handle the increments generated during ddl execution (required only by rebuild types)

Commit stage

Upgrade to EXCLUSIVE-MDL lock, disable reading and writing

Redo the last part of the increment in the last row_log

Update the data dictionary table of innodb

Commit the transaction (swipe the redo log of the transaction)

Modify statistics

Rename temporary idb file, frm file

Change completed

OperationIn-Place?Copies Table?Allows Concurrent DML?Allows Concurrent Query?Notes add Index Yes*No*YesYes some restrictions on full-text indexing YesNoYesYes only modifies the metadata of the table OPTIMIZE TABLEYesYesYesYes uses ALGORITHM=INPLACE from 5.6.17, of course, if you specify old_alter_table=1 or mysqld startup belt-- skip-new, it will still be in COPY mode. If there is a full-text index on the table that only supports COPY to set the default value for one column YesNoYesYes only modifies the metadata of the table to modify the value of auto-increment for one column YesNoYesYes only modifies the metadata of the table to add foreign key constraintYes*No*YesYes in order to avoid copying the table, foreign_key_checks deletion foreign key constraintYesNoYesYesforeign_key_checks is disabled when the constraint is created, does not affect changing the column name Yes*No*Yes*Yes in order to allow DML concurrency, if the same data type is maintained Just change the column name add column Yes*Yes*Yes*Yes although ALGORITHM=INPLACE is allowed, the data is greatly reorganized, so it is still an expensive operation. When adding columns is auto-increment, DML is not allowed to delete columns concurrently YesYes*YesYes, although ALGORITHM=INPLACE is allowed, but the data is greatly reorganized, so it is still an expensive operation to modify column data type NoYes*NoYes modify type or add length, will copy the table, and do not allow update operation to change column order YesYesYesYes although ALGORITHM=INPLACE is allowed, but the data is greatly reorganized, so it is still an expensive operation to modify ROW_FORMAT

And KEY_BLOCK_SIZEYesYesYesYes allow ALGORITHM=INPLACE, but the data is greatly reorganized, so it is still an expensive operation to set the column property NULL

Or NOT NULLYesYesYesYes although it allows ALGORITHM=INPLACE, but the data is greatly reorganized, so it is still an expensive operation to add the primary key Yes*YesYesYes although it allows ALGORITHM=INPLACE, but the data is greatly reorganized, so it is still an expensive operation.

If the column definition must be converted to NOT NULL, INPLACE is not allowed to delete and add primary key YesYesYesYes allows inplace; data to be substantially reorganized when the primary key is deleted and a new primary key is added in the same ALTER TABLE statement, so it is still an expensive operation. Deleting the primary key NoYesNoYes does not allow concurrent DML, so copy the table, and if you do not add the primary key to the same ATLER TABLE statement at the same time, you will receive a restricted change table character set NoYesNoYes. If the new character set code is different, rebuild the table.

Mysql 5.7Example of modifying table structure online:

Grammar

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]

The principle of online ddl is that mysql records all inserts, updates and deletions in ddl time to a log file, and then applies these incremental data to the corresponding table (after the transactions on the table are completely released). The upper limit of this temporary log file is specified by innodb_online_alter_log_max_size. If the size of each extension innodb_sort_buffer_size is too small, this parameter may cause DDL to fail.

II. Pt-online-schema-change

Note:

(1) there is a primary key or unique creation in the table.

(2) disk capacity estimation

(3) there is no trigger in the original table.

(4) when the original table performs batch DML operation, it will have a certain impact, so special attention should be paid to the setting of parameters such as lock waiting.

(5) if the updated table is the parent table referenced by the foreign key of the quilt table, the foreign key of the child table needs to be updated accordingly.

1. Online ddl principle

(1) set mysql session parameters

SET SESSION innodb_lock_wait_timeout=1

SET SESSION lock_wait_timeout=60

SET SESSION wait_timeout=10000

SET @ @ SQL_QUOTE_SHOW_CREATE = 1, which is called SQL models, but not automatically, but without Zero.

(2) check the table structure to see if there are primary keys, other foreign key references and triggers

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) create a new table

Creating new table...

CREATE TABLE `dbtest`.` _ t1new` (

`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 the new table

Altering new table...

ALTER TABLE `dbtest`.` _ T1 _ new` ADD COLUMN phone varchar (15)

Altered `dbtest`.` _ T1 _ new` OK.

(5) create a trigger on the old table (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`.` _ t1new`WHERE `dbtest`.` _ t1new`.`id`OLD.`id`

CREATE TRIGGER `pt_osc_dbtest_t1_ upd` AFTER UPDATE ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.` _ t1new` (`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`.` _ t1new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)

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

(6) copy data

Copy data after a chunk, it will check the thread_running load, warning, and slave database information to decide whether to continue copy. By default-- chunk-time=0.5, the chunk-size will be dynamically adjusted according to the records of copy at this time. When Copy-related chunk is used, S locks will be added to the records related to the original table.

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

INSERT LOW_PRIORITY IGNORE INTO `dbtest`.` _ t1new` (`id`, `name`) SELECT `id`, `name` FROM `dbtest`.`t1` FORCE INDEX (`PRIMARY`) WHERE ((`id` > =?)) AND ((`id` =?)) ORDER BY `id`LIMIT?, 2 / * next chunk boundary*/

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

(7) Analysis of new tables and statistical information

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

(8) Exchange new and old tables, rename the old table T1 to _ t1_old, rename the new table _ t1_new to T1, and delete the old table _ t1_old

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

RENAME TABLE `dbtest`.`t1` TO `dbtest`.` _ t1old`, `dbtest`.` _ t1new`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) delete trigger

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) complete the online modification of the table structure

Successfully altered `dbtest`.`t1`.

Click (here) to collapse or open

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 stroke 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 stroke 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

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

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. Add fields

Pre-execution: pt-online-schema-change-- user=dbuser-- password=123456-- host=10.xx-- alter "ADD COLUMN phone varchar (15)" Dempt1-- print-- dry-run

Click (here) to collapse or open

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`.` _ t1new` (

`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`.` _ t1new`WHERE `dbtest`.` _ t1new`.`id`OLD.`id`

CREATE TRIGGER `pt_osc_dbtest_t1_ upd` AFTER UPDATE ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.` _ t1new` (`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`.` _ t1new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)

Not copying rows because this is a dry run.

INSERT LOW_PRIORITY IGNORE INTO `dbtest`.` _ t1new` (`id`, `name`) SELECT `id`, `name` FROM `dbtest`.`t1` FORCE INDEX (`PRIMARY`) WHERE ((`id` > =?)) AND ((`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.

Official implementation: pt-online-schema-change-- user=dbuser-- password=123456-- host=10.xx-- alter "ADD COLUMN phone varchar (15)" Dempt1-- print-- execute

Click (here) to collapse or open

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`.` _ t1new` (

`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`.` _ t1new`WHERE `dbtest`.` _ t1new`.`id`OLD.`id`

CREATE TRIGGER `pt_osc_dbtest_t1_ upd` AFTER UPDATE ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.` _ t1new` (`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`.` _ t1new` (`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`.` _ t1new` (`id`, `name`) SELECT `id`, `name` FROM `dbtest`.`t1` FORCE INDEX (`PRIMARY`) WHERE ((`id` > =?)) AND ((`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`.` _ t1old`, `dbtest`.` _ t1new`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. Delete a field

Pt-online-schema-change-- user=dbuser-- password=123456-- host=10.xx-- no-check-replication-filters-- recursion-method=none-- alter "DROP COLUMN phone" Dempest dbtest no. T1-- print-- execute

Click (here) to collapse or open

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`.` _ t1new` (

`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`.` _ t1new`WHERE `dbtest`.` _ t1new`.`id`OLD.`id`

CREATE TRIGGER `pt_osc_dbtest_t1_ upd` AFTER UPDATE ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.` _ t1new` (`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`.` _ t1new` (`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`.` _ t1new` (`id`, `name`) SELECT `id`, `name` FROM `dbtest`.`t1` FORCE INDEX (`PRIMARY`) WHERE ((`id` > =?)) AND ((`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`.` _ t1old`, `dbtest`.` _ t1new`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. Add an index

Pt-online-schema-change-- user=dbuser-- password=123456-- host=10.xx-- no-check-replication-filters-- recursion-method=none-- alter "add key idx_name (name)" Dempt1-- print-- execute

Click (here) to collapse or open

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`.` _ t1new` (

`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`.` _ t1new`WHERE `dbtest`.` _ t1new`.`id`OLD.`id`

CREATE TRIGGER `pt_osc_dbtest_t1_ upd` AFTER UPDATE ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.` _ t1new` (`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`.` _ t1new` (`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`.` _ t1new` (`id`, `name`) SELECT `id`, `name` FROM `dbtest`.`t1` FORCE INDEX (`PRIMARY`) WHERE ((`id` > =?)) AND ((`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`.` _ t1old`, `dbtest`.` _ t1new`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. Delete the index

Pt-online-schema-change-- user=dbuser-- password=123456-- host=10.xx-- no-check-replication-filters-- recursion-method=none-- alter "DROP key idx_name" Dempest dbtest no. T1-- print-- execute

6. Change the field type and length

Pt-online-schema-change-- user=dbuser-- password=123456-- host=10.xx-- no-check-replication-filters-- recursion-method=none-- alter "modify name varchar (10)"

At this point, the study on "what is the difference between MySQL ONLINE DDL and PT-ONLINE-SCHEMA-CHANGE" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.