Pt-Online-schema-change of MySQL online DDL Tool

Pt-Online-schema-change of MySQL online DDL Tool

MySQL DDL:
DDL is a feature that makes all MySQL ddbas criticized, because in MySQL, when dDDL is performed on a table, it locks the table. When the table is small, such as less than 1 W rows, it has little impact on the front-end, if tens of millions of tables are encountered at that time, the front-end application's write operations on the tables will be affected!

The InnoDB engine uses the following steps to perform DDL operations:
1. Create an invisible temporary table (tmp_table) based on the table structure and DDL statements of the original table (original_table)
2. Add write lock to the original table to block all update operations (insert, delete, update, etc)
3. Execute insert into tmp_table select * from original_table
4. rename original_table and tmp_table, and finally drop original_table
5. Release the write lock.
You can see that when InnoDB executes DDL, the original table can only be read and cannot be written. Therefore, perconal has released the pt-online-schema-change tool, which is characterized by no read/write blocking during the modification process.

Working principle:
If the table has a foreign key, the tool will not execute unless you use -- alter-foreign-keys-method to specify a specific value.
1. Create an empty table with the same structure as the table you want to execute the alter operation.
2. Execute the table structure modification, and then copy the data from the original table to the table after the table structure modification,
3. Create a trigger on the original table to update the update operation of the original table to the new table during data copy.
Note: If the trigger has been defined in the table, this tool cannot work.
4. After the copy operation is complete, replace the original table with a new rename table. The original table is deleted by default.

Usage:
Pt-online-schema-change [OPTIONS] DSN
Options: You can view the help information on your own. DNS provides the databases and tables you want to operate on. There are two parameters to be introduced:
-- Dry-run
This parameter does not create a trigger, does not copy data, and does not replace the original table. Only create and change a new table.
-- Execute
The function of this parameter is the same as described in previous working principles. A trigger is created to ensure that the latest data changes will affect the new table. Note: If this parameter is not added, the tool will exit after performing some checks.

Dependency condition:
The table to be operated must have a primary key; otherwise, the following error is reported:
[Root @ bkjia ~] # Pt-online-schema-change-u root-pbkjia-h127.0.0.1 -- alter = 'add column vname varchar (20) '-- execute D = bkjia, t = bkjia
No slaves found. See -- recursion-method if host bkjia.com has slaves.
Not checking slave lag because no slaves were found and -- check-slave-lag was not specified.
Operation, tries, wait:
Copy_rows, 10, 0.25
Create_triggers, 10, 1
Drop_triggers, 10, 1
Swap_tables, 10, 1
Update_foreign_keys, 10, 1
Altering 'bkjia '. 'bkjia '...
Creating new table...
Created new table bkjia. _ bkjia_new OK.
Altering new table...
Altered 'bkjia '.' _ bkjia_new 'OK.
2016-01-08T17: 51: 43 Dropping new table...
2016-01-08T17: 51: 43 Dropped new table OK.
'Bkjia '. 'bkjia' was not altered.
The new table 'bkjia '.' _ bkjia_new 'does not have a primary key or a unique index which is required for the DELETE trigger.

The vname field is not added successfully!
Mysql> show create table bkjia \ G
* *************************** 1. row ***************************
Table: bkjia
Create Table: create table 'bkjia '(
'Actor _ id' smallint (8) unsigned not null default '0 ',
'First _ name' varchar (45) not null,
'Last _ name' varchar (45) not null,
'Last _ Update' timestamp not null default CURRENT_TIMESTAMP ON update CURRENT_TIMESTAMP
) ENGINE = InnoDB default charset = utf8

Add primary key:
Mysql> alter table bkjia modify actor_id smallint (8) unsigned primary key;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

[Root @ bkjia ~] # Pt-online-schema-change-u root-pbkjia-h127.0.0.1 -- alter = 'add column vname varchar (20) '-- execute D = bkjia, t = bkjia
No slaves found. See -- recursion-method if host bkjia.com has slaves.
Not checking slave lag because no slaves were found and -- check-slave-lag was not specified.
Operation, tries, wait:
Copy_rows, 10, 0.25
Create_triggers, 10, 1
Drop_triggers, 10, 1
Swap_tables, 10, 1
Update_foreign_keys, 10, 1
Altering 'bkjia '. 'bkjia '...
Creating new table...
Created new table bkjia. _ bkjia_new OK.
Altering new table...
Altered 'bkjia '.' _ bkjia_new 'OK.
2016-01-08T17: 57: 09 Creating triggers...
2016-01-08T17: 57: 09 Created triggers OK.
2016-01-08T17: 57: 09 Copying approximately 200 rows...
2016-01-08T17: 57: 09 Copied rows OK.
2016-01-08T17: 57: 09 Swapping tables...
2016-01-08T17: 57: 09 Swapped original and new tables OK.
2016-01-08T17: 57: 09 Dropping old table...
2016-01-08T17: 57: 09 Dropped old table 'bkjia '.' _ bkjia_old 'OK.
2016-01-08T17: 57: 09 Dropping triggers...
2016-01-08T17: 57: 09 Dropped triggers OK.
Successfully altered 'bkjia '. 'bkjia '.

 

Mysql> show create table bkjia \ G
* *************************** 1. row ***************************
Table: bkjia
Create Table: create table 'bkjia '(
'Actor _ id' smallint (8) unsigned not null,
'First _ name' varchar (45) not null,
'Last _ name' varchar (45) not null,
'Last _ Update' timestamp not null default CURRENT_TIMESTAMP ON update CURRENT_TIMESTAMP,
'Vname' varchar (20) default null,
Primary key ('actor _ id ')
) ENGINE = InnoDB default charset = utf8

Add multiple fields:
[Root @ bkjia ~] # Pt-online-schema-change-u root-pbkjia-h127.0.0.1 -- alter = 'add column aname varchar (20), add column bname varchar (30) '-- execute D = bkjia, t = bkjia
No slaves found. See -- recursion-method if host bkjia.com has slaves.
Not checking slave lag because no slaves were found and -- check-slave-lag was not specified.
Operation, tries, wait:
Copy_rows, 10, 0.25
Create_triggers, 10, 1
Drop_triggers, 10, 1
Swap_tables, 10, 1
Update_foreign_keys, 10, 1
Altering 'bkjia '. 'bkjia '...
Creating new table...
Created new table bkjia. _ bkjia_new OK.
Altering new table...
Altered 'bkjia '.' _ bkjia_new 'OK.
2016-01-08T18: 04: 25 Creating triggers...
2016-01-08T18: 04: 25 Created triggers OK.
2016-01-08T18: 04: 25 Copying approximately 200 rows...
2016-01-08T18: 04: 25 Copied rows OK.
2016-01-08T18: 04: 25 Swapping tables...
2016-01-08T18: 04: 26 Swapped original and new tables OK.
2016-01-08T18: 04: 26 Dropping old table...
2016-01-08T18: 04: 26 Dropped old table 'bkjia '.' _ bkjia_old 'OK.
2016-01-08T18: 04: 26 Dropping triggers...
2016-01-08T18: 04: 26 Dropped triggers OK.
Successfully altered 'bkjia '. 'bkjia '.

Mysql> show create table bkjia \ G
* *************************** 1. row ***************************
Table: bkjia
Create Table: create table 'bkjia '(
'Actor _ id' smallint (8) unsigned not null,
'First _ name' varchar (45) not null,
'Last _ name' varchar (45) not null,
'Last _ Update' timestamp not null default CURRENT_TIMESTAMP ON update CURRENT_TIMESTAMP,
'Vname' varchar (20) default null,
'Aname' varchar (20) default null,
'Bname' varchar (30) default null,
Primary key ('actor _ id ')
) ENGINE = InnoDB default charset = utf8

 

Delete field:
[Root @ bkjia ~] # Pt-online-schema-change-u root-pbkjia-h127.0.0.1 -- alter = 'drop column aname, drop column bname' -- execute D = bkjia, t = bkjia
No slaves found. See -- recursion-method if host bkjia.com has slaves.
Not checking slave lag because no slaves were found and -- check-slave-lag was not specified.
Operation, tries, wait:
Copy_rows, 10, 0.25
Create_triggers, 10, 1
Drop_triggers, 10, 1
Swap_tables, 10, 1
Update_foreign_keys, 10, 1
Altering 'bkjia '. 'bkjia '...
Creating new table...
Created new table bkjia. _ bkjia_new OK.
Altering new table...
Altered 'bkjia '.' _ bkjia_new 'OK.
2016-01-08T18: 05: 45 Creating triggers...
2016-01-08T18: 05: 45 Created triggers OK.
2016-01-08T18: 05: 45 Copying approximately 200 rows...
2016-01-08T18: 05: 45 Copied rows OK.
2016-01-08T18: 05: 45 Swapping tables...
2016-01-08T18: 05: 45 Swapped original and new tables OK.
2016-01-08T18: 05: 45 Dropping old table...
2016-01-08T18: 05: 45 Dropped old table 'bkjia '.' _ bkjia_old 'OK.
2016-01-08T18: 05: 45 Dropping triggers...
2016-01-08T18: 05: 46 Dropped triggers OK.
Successfully altered 'bkjia '. 'bkjia '.

 

Add an index:
[Root @ bkjia ~] # Pt-online-schema-change-u root-pbkjia-h127.0.0.1 -- alter = 'add key index_first (first_name) '-- execute D = bkjia, t = bkjia No slaves found. see -- recursion-method if host bkjia.com has slaves.
Not checking slave lag because no slaves were found and -- check-slave-lag was not specified.
Operation, tries, wait:
Copy_rows, 10, 0.25
Create_triggers, 10, 1
Drop_triggers, 10, 1
Swap_tables, 10, 1
Update_foreign_keys, 10, 1
Altering 'bkjia '. 'bkjia '...
Creating new table...
Created new table bkjia. _ bkjia_new OK.
Altering new table...
Altered 'bkjia '.' _ bkjia_new 'OK.
2016-01-08T18: 06: 38 Creating triggers...
2016-01-08T18: 06: 38 Created triggers OK.
2016-01-08T18: 06: 38 Copying approximately 200 rows...
2016-01-08T18: 06: 38 Copied rows OK.
2016-01-08T18: 06: 38 Swapping tables...
2016-01-08T18: 06: 38 Swapped original and new tables OK.
2016-01-08T18: 06: 38 Dropping old table...
2016-01-08T18: 06: 38 Dropped old table 'bkjia '.' _ bkjia_old 'OK.
2016-01-08T18: 06: 38 Dropping triggers...
2016-01-08T18: 06: 38 Dropped triggers OK.
Successfully altered 'bkjia '. 'bkjia '.

This article permanently updates the link address: