Introduction:

Working process of Pt OSC
1. Create a new empty table structure (the structure before the alter) just like the table to perform the alter operation
1. Execute alter table statement in the new table (it should be fast)
1. Create three triggers in the original table corresponding to insert, update and delete operations
1. Copy the data from the original table to the temporary table with a certain block size. During the copying process, the write operations in the original table through the triggers on the original table will be updated to the new temporary table
1. Rename the original table to the old table, and rename the temporary table to the original table
1. If there is a foreign key referring to the table, according to the value of the alter foreign keys method parameter, detect the table related to the foreign key, and do the corresponding settings
1. Delete the old original table by default

Before using, please refer to the installation of percona toolkit tutorial reference:

Percona Toolkit

Here is an example of Pt online scheme change:

Create a new table pt:

CREATE TABLE `pt` (
 `id` int(11) NOT NULL DEFAULT '0',
 `disktype` enum('sas','shannon','memdisk') NOT NULL,
 `blocksize` enum('4K','64K','256K','1M') NOT NULL,
 `testmode` enum('seqwr','seqrewr','seqrd','rndrd','rndwr','rndrw') NOT NULL,
 `thread` enum('1','2','4','8','16','32') NOT NULL,
 `bandwidth` float(10,2) unsigned NOT NULL,
 `resptime` float(10,2) unsigned NOT NULL DEFAULT '0.00',
 PRIMARY KEY (`id`),
 UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Let's change the above field to responsetime

#Modify ip, account and password by yourself
------------------------------------------------------------
~ # pt-online-schema-change --host=127.0.0.1 --user=root --password=123456 --alter "CHANGE resptime responsetime float(10,2) unsigned not NULL default '0'" D=test,t='pt' --execute --print --statistics --no-check-alter  


#Execute the whole process output
------------------------------------------------------------
No slaves found.  See --recursion-method if host localhost.localdomain has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 together with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at /usr/bin/pt-online-schema-change line 6948.
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 together with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at /usr/bin/pt-online-schema-change line 6948.

# A software update is available:
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 `test`.`pt`...
Renaming columns:
  resptime to responsetime
Creating new table...
CREATE TABLE `test`.`_pt_new` (
  `id` int(11) NOT NULL DEFAULT '0',
  `disktype` enum('sas','shannon','memdisk') NOT NULL,
  `blocksize` enum('4K','64K','256K','1M') NOT NULL,
  `testmode` enum('seqwr','seqrewr','seqrd','rndrd','rndwr','rndrw') NOT NULL,
  `thread` enum('1','2','4','8','16','32') NOT NULL,
  `bandwidth` float(10,2) unsigned NOT NULL,
  `resptime` float(10,2) unsigned NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Created new table test._pt_new OK.
Altering new table...
ALTER TABLE `test`.`_pt_new` CHANGE resptime responsetime float(10,2) unsigned not NULL default '0'
Altered `test`.`_pt_new` OK.
2018-01-07T16:36:29 Creating triggers...
2018-01-07T16:36:29 Created triggers OK.
2018-01-07T16:36:29 Copying approximately 1 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_pt_new` (`id`, `disktype`, `blocksize`, `testmode`, `thread`, `bandwidth`, `responsetime`) SELECT `id`, `disktype`, `blocksize`, `testmode`, `thread`, `bandwidth`, `resptime` FROM `test`.`pt` LOCK IN SHARE MODE /*pt-online-schema-change 7706 copy table*/
2018-01-07T16:36:29 Copied rows OK.
2018-01-07T16:36:29 Analyzing new table...
2018-01-07T16:36:29 Swapping tables...
RENAME TABLE `test`.`pt` TO `test`.`_pt_old`, `test`.`_pt_new` TO `test`.`pt`
2018-01-07T16:36:29 Swapped original and new tables OK.
2018-01-07T16:36:29 Dropping old table...
DROP TABLE IF EXISTS `test`.`_pt_old`
2018-01-07T16:36:29 Dropped old table `test`.`_pt_old` OK.
2018-01-07T16:36:29 Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_pt_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_pt_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_pt_ins`
2018-01-07T16:36:29 Dropped triggers OK.
# Event  Count
# ====== =====
# INSERT     1
Successfully altered `test`.`pt`.

Successfully saw:

mysql> show create table pt;

| pt    | CREATE TABLE `pt` (
  `id` int(11) NOT NULL DEFAULT '0',
  `disktype` enum('sas','shannon','memdisk') NOT NULL,
  `blocksize` enum('4K','64K','256K','1M') NOT NULL,
  `testmode` enum('seqwr','seqrewr','seqrd','rndrd','rndwr','rndrw') NOT NULL,
  `thread` enum('1','2','4','8','16','32') NOT NULL,
  `bandwidth` float(10,2) unsigned NOT NULL,
  `responsetime` float(10,2) unsigned NOT NULL DEFAULT '0.00',#Changed
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Add a field p1

------------------------------------------------------------
~ # pt-online-schema-change --host=127.0.0.1 --user=root --password=123456 --alter "add p1 float(10,2) not NULL default '0'" D=test,t='pt' --execute --print --statistics --no-check-alter

Successfully saw:

----------------------------------------------------------+
| pt    | CREATE TABLE `pt` (
  `id` int(11) NOT NULL DEFAULT '0',
  `disktype` enum('sas','shannon','memdisk') NOT NULL,
  `blocksize` enum('4K','64K','256K','1M') NOT NULL,
  `testmode` enum('seqwr','seqrewr','seqrd','rndrd','rndwr','rndrw') NOT NULL,
  `thread` enum('1','2','4','8','16','32') NOT NULL,
  `bandwidth` float(10,2) unsigned NOT NULL,
  `responsetime` float(10,2) unsigned NOT NULL DEFAULT '0.00',
  `p1` float(10,2) NOT NULL DEFAULT '0.00',  #Add a new column
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |