Percona-Toolkit 之 pt-online-schema-change 總結

pt-online-schema-change - ALTER tables without locking them.

pt-online-schema-change alters a table’s structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully.

pt-online-schema-change是Percona-Toolkit工具集中的一個組件，很多DBA在使用Percona-Toolkit時第一個使用的工具就是它，同時也是使用最頻繁的一個工具。它可以做到在修改表結構的同時（即進行DDL操作）不阻塞資料庫表DML的進行，這樣降低了對生產環境資料庫的影響。在MySQL5.6之前是不支持Online DDL特性的，即使在添加二級索引的時候有FIC特性，但是在修改表欄位的時候還是會有鎖表並阻止表的DML操作，這樣對於DBA來說是非常痛苦的，好在有pt-online-schema-change工具在沒有Online DDL時解決了這一問題，關於Online DDL可以參考另一篇博文：MySQL InnoDB Online DDL學習。

本文是關於之前有關pt-online-schema-change工具使用的學習筆記進行重新整理，使用最新版本的工具同時也進行原理知識的梳理。

安裝

• 獲取

可以到官網：https://www.percona.com/downloads/percona-toolkit/LATEST/，選擇適合的操作系統版本和工具版本進行下載：

# wget https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/7/x86_64/percona-toolkit-3.0.13-1.el7.x86_64.rpm 

本文使用的安裝包是：percona-toolkit-3.0.13-1.el7.x86_64.rpm

• 安裝

因為下載的是RPM安裝包，所以採用本地安裝的方式進行：

-- 如果只使用本地rpm安裝的話，需要安裝以下依賴的perl模塊
# rpm -ivh percona-toolkit-3.0.13-1.el7.x86_64.rpm
warning: percona-toolkit-3.0.13-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
error: Failed dependencies:
        perl(DBI) >= 1.13 is needed by percona-toolkit-3.0.13-1.el7.x86_64
        perl(DBD::mysql) >= 1.0 is needed by percona-toolkit-3.0.13-1.el7.x86_64
        perl(IO::Socket::SSL) is needed by percona-toolkit-3.0.13-1.el7.x86_64
        perl(Digest::MD5) is needed by percona-toolkit-3.0.13-1.el7.x86_64
        perl(Term::ReadKey) is needed by percona-toolkit-3.0.13-1.el7.x86_64

-- 如果配置了線上的yum源，則使用yum安裝
# yum localinstall percona-toolkit-3.0.13-1.el7.x86_64.rpm

基本說明

pt-online-schema-change [OPTIONS] DSN

常用選項(OPTIONS)

--alter
變更結構選項，不需要ALTER TABLE關鍵字，如果表有多個變更可以使用逗號分隔。

限制：
1.在絕大部分情況下表都需要有主鍵或者是唯一索引。因為這個工具會在運行的時候創建一個DELETE觸發器，這是為了保證在變更中新表能夠與舊表保持更新一致性。值得註意的是，如果在需要變更的列上創建主鍵或是唯一索引時，則會以這些列創建觸發器；
2.不能使用RENAME子句為表進行重命名；
3.欄位不能通過刪除再重添加的方式進行重命名，這種方式是不會拷貝原欄位的數到新欄位上；
4.如果新增NOT NULL的列並且沒有指定default值，工具就會執行失敗，它並不會指定預設值；
5.涉及到刪除外鍵時，需要指定_constraint_name，工具會在新表上創建一個前面加了下劃線的外鍵名稱，這個外鍵名稱與原致。如需要刪除外鍵fk_foo，則指定'--alter "DROP FOREIGN KEY _fk_foo"'。

--alter-foreign-keys-method
採用何種方式修改外鍵以便關聯到新表上。有外鍵約束的表需要被特殊處理，為了確保外鍵依然能夠關聯到正確的表上。當工具重命名外鍵關聯的父表時，確保外鍵也必須關聯到重命名後的父表。
主要有以下幾種方式：

auto：讓工具自動選擇使用。優先選擇rebuild_constraints，如果不成功，則選擇drop_swap；
rebuild_constraints：這種方式使用ALTER TABLE先刪除然後重建外鍵關聯到新父表。這是首選的方式，如果一張或多張子表過大會導致ALTER需要很長時間，子表會被阻塞；

drop_swap：禁用外鍵約束(FOREIGN_KEY_CHECKS=0)，在進行重命名新父表之前刪除原父表，這與常規轉換舊表與新表的方式不同，這個RENAME操作是原子性的並且對應用客戶端無感知。
這種方式更快速並且不會阻塞，但是也有隱患：
1.刪除原父表以及重命名新表這段時間很短，如果這段時間更改子表有可能會報錯；
2.如果重命名新表發生失敗，而原父表已經永久刪除了，這時就需要人工進行干預了。
這種方式強制使用選項'--no-swap-tables'和'--no-drop-old-table'。

none：這種方式類似於drop_swap，不同在於不進行swap原父表。子表有任何外鍵關聯父表都將變成關聯一張不存在的表，這會使得子表的外鍵約束失效，可以通過SHOW ENGINE INNODB STATUS查看。

--[no]analyze-before-swap
預設值：yes
在新表與舊表完成轉換之前對新表執行ANALYZE TABLE操作，預設會在MySQL5.6及之後版本並且開啟innodb_stats_persistent的情況下執行。

--ask-pass
命令行提示密碼輸入，保護密碼安全，前提需安裝模塊perl-TermReadKey。

--[no]check-alter
預設值：yes
解析變更選項的內容，發出表變更警告，主要警告項為：
1.欄位重命名
在工具的早期版本中，通過指定CHANGE COLUMN name new_name進行欄位重命名會導致資料庫的丟失，現在的版本已經通過代碼解決了數據一致性問題。但這段代碼並不能保證能夠確保數據的不丟失。所以當涉及到欄位名變更時應通過添加選項'--dry-run'和'--print'查看變更是否可以正確執行。
2.刪除主鍵
如果'--alter'選項中包含DROP PRIMARY KEY刪除主鍵的操作，除非指定選項'--dry-run'，否則工具將退出。變更表的主鍵是十分危險的，工具變更時建立的觸發器，尤其是DELETE觸發器，是基於主鍵的，在做主鍵變更前先添加選項'--dry-run'和'--print'驗證觸發器是可用的。

--[no]check-replication-filters
預設值：yes
如果伺服器指定了任何主從複製過濾選項，該工具會查詢是否有複製過濾選項，一旦發現，工具都會中止並報錯。

--check-slave-lag
指定暫停舊表與新表的數據拷貝直到主從複製小於選項'--max-lag'指定的值。

--skip-check-slave-lag
DSN類型，可重覆使用
指定DSN連接從庫時跳過主從延遲檢查，可以指定多個從庫檢查。

--check-interval
預設值：1s
指定因為選項'--max-lag'檢查之間休眠時間。

--chunk-index
指定使用哪個索引對錶進行chunk分塊操作。預設情況下會選擇最優的索引，工具會在SQL語句中添加FORCE INDEX子句。

--chunk-index-columns
指定使用選項'--chunk-index'的索引使用最左首碼幾個索引欄位，只適用於複合索引。

--chunk-size
預設值：1000
指定表分塊的chunk大小，每個chunk需要拷貝的表行數，允許的尾碼單位為k、M、G。
當指定了這個選項會覆蓋工具預設動態調整chunk塊大小以便在選項'--chunk-time'指定時間內完成行拷貝的行為。

--chunk-time
預設值：0.5
動態調整每個chunk的大小使相應的表行數都在指定的時間內完成拷貝查詢。如果該選項值設置為0，則不會動態調整chunk的大小，就有可能造成每次拷貝查詢的時間不同，但每個chunk大小還是一致的。

--host，-h
指定連接的資料庫IP地址。

--port，-P
指定連接的資料庫Port埠。

--user，-u
指定連接的資料庫用戶。

--password，-p
指定連接的資料庫用戶密碼。

--database，-D
指定連接的資料庫。

--charset，-A
指定連接字元集。

--max-lag
預設值：1s
指定允許主從複製延遲時長的最大值，單位秒。如果在每次拷貝查詢之後主從延遲超過指定的值，則操作將暫停執行，暫停休眠時間為選項'--check-interval'指定的值。待休眠時間結束之後再次檢查主從延遲時長，檢查方法是通過從庫查詢的'Seconds_Behind_Master'值來確定。如果主從複製延遲一直大於該參數指定值或者從庫停止複製，則操作將一直等待直到從庫重新啟動並且延遲小於該參數指定值。

--max-load
數組類型，預設值：Threads_running = 25
在變更拷貝完每個chunk數據之後，運行SHOW GLOBAL STATUS檢查所指定變數值高於該參數指定變數的閾值時將暫停操作。如果有多個變數閾值，可以用','(逗號)進行分隔，參數指定型式可以為變數名=MAX_VALUE或變數名:MAX_VALUE。
如果只是指定變數名，沒有為其指定閾值，則檢查當前值並增加20%作為閾值。如：

    --max-load=Threads_running：沒有指定具體值，以當前查詢值增加20%作為閾值，如當前為100，閾值為120；
    --max-load=Threads_running:10：以當前指定值為閾值。

--critical-load
數組類型，預設值：Threads_running = 50
指定需中止操作的狀態變數閾值。用法可以參考選項'--max-load'。

--preserve-triggers
指定保留舊表的觸發器。
從MySQL5.7.2起開始支持在同一張給定的表上定義具有相同觸發事件和觸發時間的多個觸發器。這意味著如果表原來已有觸發器，那麼工具所需的觸發器也可以創建成功。如果指定了該選項，則工具將舊表上所有的觸發器複製到新表上，然後再進行表數據行的拷貝操作。

限制：
1.如果舊表上的觸發器引用了將被工具刪除的欄位，則觸發器失效；
2.該選項不能與選項'--no-drop-triggers'、'--no-drop-old-table'和'--no-swap-tables'一起使用，因為該選項需要刪除舊表的觸發器併在新表上重新創建，因為表不可能有多個同名的觸發器。

--null-to-not-null
指定可以將允許NULL的欄位轉換為NOT NULL欄位。其中如有包含NULL行的欄位值轉換為欄位預設值，如果沒有欄位值，則根欄位類型來分配預設值。如：字元串類型為''(空字元串)，數值類型為0。

--new-table-name
字元串類型，預設值：%T_new
指定舊表和新表交換之前新表的名稱。%T會替換為舊表名稱。

--[no]drop-new-table
預設值：yes
指定如果拷貝舊表數據到新表時失敗，則刪除新表。
如果指定選項'--no-drop-new-table'以及'--no-swap-tables'將保留一份變更後的副本，但不會對舊表進行修改。

限制：
當選項'--alter-foreign-keys-method'指定的方式為drop_swap時，選項'--no-drop-new-table'不生效。

--[no]drop-old-table
預設值：yes
指定在完成舊表與新表交換重命名之後刪除舊表。如果之間發生了錯誤，則會保留舊表。指定選項'--no-swap-tables'同樣不會刪除舊表。

--[no]drop-triggers
預設值：yes
指定舊表上刪除觸發器。如果指定了選項'--no-drop-triggers'就會強制指定'--no-drop-old-table'。

--[no]swap-tables
預設值：yes
指定變更交換舊表和新表。
如果指定選項'--no-swap-tables'也會運行整個過程，只是最後不進行舊表與新表的交換，並且刪除新表。

--dry-run
指定創建和變更新表，但是不創建觸發器，也不拷貝數據和變更原始表。

--execute
指定需要執行真正的變更操作。當確定要執行變更操作時必須指定該選項，如果不指定該選項，則工具會進行安全檢查之後退出。

--[no]check-unique-key-change
預設值：yes
當工具要進行添加唯一索引的變更時停止運行。因為工具使用語句INSERT IGNORE從舊表進行數據拷貝插入新表，如果插入的值違返唯一性約束，數據插入不會明確提示失敗但這樣會造成數據丟失。

--recursion-method
預設值：processlist，hosts
指定獲取從庫的方式。
METHOD       USES
===========  =============================================
processlist  SHOW PROCESSLIST   
hosts        SHOW SLAVE HOSTS   
dsn=DSN      DSNs from a table
none         Do not find slaves
==========================================================
processlist：通過SHOW PROCESSLIST方式找到slave，為預設方式，當SHOW SLAVE HOSTS不可用時。一旦實例運行在非3306埠上時，hosts方式就會變為預設方式；
hosts：通過SHOW SLAVE HOSTS方式找到slave，hosts方式要求從庫配置'--report_host'和'--report_port'這兩個參數；
dsn：通過讀取表中從庫的DSN信息進行連接。

--recurse
指定搜尋從庫的層級，預設無限級。

--set-vars
預設：
    wait_timeout=10000
    innodb_lock_wait_timeout=1
    lock_wait_timeout=60
運行檢查時指定參數值，如有多個用','(逗號)分隔。如`--set-vars=wait_timeout=5000`。

--sleep
預設值：0s
指定表變更拷貝數據時的間隔時間。

--print
列印工具執行過程中的語句到STDOUT。可以結合'--dry-run'一起使用。
 
--progress
列印工具執行過程的進度提示到STDERR。選項值有兩部分組成，用逗號進行分隔，第一部分為百分比，時間和迭代。第二部分為根據第一部分數據更新頻率，也分為百分比，時間和迭代。

--quiet，-q
不列印工具執行過程的信息到STDOUT(禁用'--progress')。但錯誤和警告還是列印到STDERR。

--statistics
列印內部計數的統計信息。

--version
顯示工具的版本並退出。

--[no]version-check
預設值：yes
檢查Percona Toolkit、MySQL和其他程式的最新版本。

DSN選項(DSN)

可以使用DSN方式來連接資料庫，DSN選項為key=value方式，在等號的兩側不能有空格出現，並且區分大小寫，多個選項之前以','(逗號)隔開，主要選項如下：

• A
  指定字元集
• D
  指定變更表所在資料庫
• t
  指定需要變更的表
• h
  指定要連接的HOST
• P
  指定要連接的PORT
• S
  指定連接所使用的SOCKET文件(Unix systems)
• u
  指定連接的用戶名
• p
  指定連接的用戶名密碼

示例：
h=192.168.58.3,P=3306,D=employees,t=employees

使用限制

1. 要求需要執行變更的表有主鍵(Primary key)或唯一索引(Unique index)，否則工具會執行失敗，參考選項--alter說明；
2. 如果檢測到表有外鍵約束(Foreign key)，工具除非選項--alter-foreign-keys-method，否則不會執行變更；
3. 如果檢測到主從複製中存在過濾，則工具不會執行，參考選項--[no]check-replication-filters說明；
4. 如果檢測到主從複製有延遲，則工具有可能會暫停數據拷貝，參考選項--max-lag說明；
5. 如果檢測到連接當前伺服器負載過高，則工具有可能暫停執行或中止退出，參考選項--max-load各--critical-load說明。

用法示例

• 測試數據準備

本文基於MySQL官方示例資料庫employee：Example Databases進行測試。

-- employees：
mysql root@localhost:employees> show create table employees\G;
***************************[ 1. row ]***************************
Table        | employees
Create Table | CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `idx_first_last` (`first_name`,`last_name`),
  KEY `idx_birth_hire` (`birth_date`,`hire_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.008s

-- dept_emp：
mysql root@localhost:employees> show create table dept_emp\G;
***************************[ 1. row ]***************************
Table        | dept_emp
Create Table | CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `dept_no` (`dept_no`),
  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.010s

-- departments：
mysql root@localhost:employees> show create table departments\G;
***************************[ 1. row ]***************************
Table        | departments
Create Table | CREATE TABLE `departments` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(40) NOT NULL,
  PRIMARY KEY (`dept_no`),
  UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.012s

mysql root@localhost:employees> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024   |
+----------+
1 row in set
Time: 0.342s
mysql root@localhost:employees> select count(*) from dept_emp;
+----------+
| count(*) |
+----------+
| 331603   |
+----------+
1 row in set
Time: 0.306s
mysql root@localhost:employees> select count(*) from departments;
+----------+
| count(*) |
+----------+
| 9        |
+----------+
1 row in set
Time: 0.050s

• 添加欄位

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "add comment varchar(50) not null default 'pt-osc'" --charset=utf8 

因為employees表中的emp_no欄位被其他表外建關聯，以下命令執行時會報如下錯誤：

You did not specify --alter-foreign-keys-method, but there are foreign keys that reference the table. Please read the tool's documentation carefully.

根據報錯信息的提示，加入選項--alter-foreign-keys-method重新執行並通過選項--dry-run查看執行過程主要信息：

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "add comment varchar(50) not null default 'pt-osc'" --alter-foreign-keys-method=auto --charset=utf8 --dry-run
Enter MySQL password:

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
Child tables:
  `employees`.`dept_emp` (approx. 331143 rows)
  `employees`.`dept_manager` (approx. 24 rows)
Will automatically choose the method to update foreign keys.
Starting a dry run.  `employees`.`employees` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table employees._employees_new OK.
Altering new table...
Altered `employees`.`_employees_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not determining the method to update foreign keys because this is a dry run.
Not swapping tables because this is a dry run.
Not updating foreign key constraints 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.
2019-03-25T13:30:05 Dropping new table...
2019-03-25T13:30:05 Dropped new table OK.
Dry run complete.  `employees`.`employees` was not altered.

-- 確保信息無誤之後可以真正執行變更操作
# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "add comment varchar(50) not null default 'pt-osc'" --alter-foreign-keys-method=auto --charset=utf8 --execute

……省略……
Will automatically choose the method to update foreign keys.
Altering `employees`.`employees`...
Creating new table...
Created new table employees._employees_new OK.
Altering new table...
Altered `employees`.`_employees_new` OK.
2019-03-25T13:35:25 Creating triggers...
2019-03-25T13:35:25 Created triggers OK.
2019-03-25T13:35:25 Copying approximately 299512 rows...
2019-03-25T13:35:31 Copied rows OK.
2019-03-25T13:35:31 Max rows for the rebuild_constraints method: 99266
Determining the method to update foreign keys...
2019-03-25T13:35:31   `employees`.`dept_emp`: too many rows: 331143; must use drop_swap
2019-03-25T13:35:31 Drop-swapping tables...
2019-03-25T13:35:31 Analyzing new table...
2019-03-25T13:35:31 Dropped and swapped tables OK.
Not dropping old table because --no-drop-old-table was specified.
2019-03-25T13:35:31 Dropping triggers...
2019-03-25T13:35:31 Dropped triggers OK.
Successfully altered `employees`.`employees`.

• 修改欄位

將表employees的comment欄位的字元集修改為utf8mb4：

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "modify column comment varchar(50) character set utf8mb4" --alter-foreign-keys-method=auto --charset=utf8 --execute

• 刪除欄位

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "drop column comment" --alter-foreign-keys-method=auto --charset=utf8 --execute

• 添加索引

為表dept_emp的欄位from_date和to_date創建複合索引idx_fr_to_date：

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "add index idx_fr_to_date(from_date,to_date)" --alter-foreign-keys-method=auto --charset=utf8 --execute

• 刪除索引

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "drop index idx_fr_to_date" --alter-foreign-keys-method=auto --charset=utf8 --execute

• 修改欄位允許NULL

將表dept_emp的欄位to_date指定為允許NULL：

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "modify column to_date date null" --alter-foreign-keys-method=auto --charset=utf8 --execute

• 修改欄位不允許NULL(NOT NULL)

為表employees添加欄位ptosc_num並允許NULL，欄位類型為int，沒有指定預設值。

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "add ptosc_num int null" --alter-foreign-keys-method=auto --charset=utf8 --execute

修改欄位ptosc_num為不允許NULL(NOT NULL)，需要通過指定選項--null-to-not-null，否則會報錯。

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "modify column ptosc_num int not null" --alter-foreign-keys-method=auto --null-to-not-null --charset=utf8 --execute

-- 因為欄位ptosc_num沒有指定預設值，欄位類型為int，所以預設值為0
mysql root@localhost:employees> select * from employees limit 5;
+--------+------------+------------+-----------+--------+------------+-----------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | ptosc_num |
+--------+------------+------------+-----------+--------+------------+-----------+
| 10001  | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 | 0         |
| 10002  | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 | 0         |
| 10003  | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 | 0         |
| 10004  | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 | 0         |
| 10005  | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 | 0         |
+--------+------------+------------+-----------+--------+------------+-----------+
5 rows in set
Time: 0.022s

• 刪除外鍵

需要為外鍵指定名稱為_forigen_key，因為在創建新表時候預設為新表上的外鍵創建這樣的名稱，如果沒這樣指定則無法刪除。

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "drop foreign key _dept_emp_ibfk_1" --alter-foreign-keys-method=auto --charset=utf8 --execute

• 重建表

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "engine=InnoDB" --alter-foreign-keys-method=auto --charset=utf8 --execute

• 變更後保留舊表

如果是涉及外鍵關聯的父表進行變更，則建議選項--alter-foreign-keys-method=rebuild_constraints，這樣在子表中會重命名外鍵約束名，如果選項--alter-foreign-keys-method有可能取值drop_swap時，則會強制使用選項--no-swap-tables和--no-drop-old-table，其中--no-swap-tables並不會有舊表的產生，就不存在保留之說了。

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "add comment varchar(50) notnull default 'pt-osc'" --no-drop-old-table --charset=utf8 --execute

以上語句執行完成後會在資料庫中生成名為_dept_emp_old的表，即變更之前的舊表。

• 變更後保留新表

顧名思義，就是先做一次完整的表變更操作，但是不進行舊表與新表的交換，也不刪除變更之後的新表，通過指定選項--no-drop-new-table和--no-swap-tables實現，可以通過選項--new-table-name指定新表名，當選項--alter-foreign-keys-method=drop_swap時，--no-drop-new-table不生效，與保留舊表的情形一致。

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "add comment varchar(50) notnull default 'pt-osc'" --no-drop-new-table --no-swap-tables --new-table-name='dept_emp_bak' --charset=utf8 --execute

以上語句執行完成後會在資料庫中生成名為dept_emp_bak的表，即變更之後的新表，但對舊表不會做任何修改。

• 添加主鍵

如果是InnoDB表沒有主鍵，真的不敢想像啊，但還是要進行測式下。這裡測試基於employees表創建employees_ptosc表：

mysql root@localhost:employees> create table employees_ptosc as select * from employees;
Query OK, 300024 rows affected
Time: 2.010s
mysql root@localhost:employees> show create table employees_ptosc;
+-----------------+--------------------------------------+
| Table           | Create Table                         |
+-----------------+--------------------------------------+
| employees_ptosc | CREATE TABLE `employees_ptosc` (     |
|                 |   `emp_no` int(11) NOT NULL,         |
|                 |   `birth_date` date NOT NULL,        |
|                 |   `first_name` varchar(14) NOT NULL, |
|                 |   `last_name` varchar(16) NOT NULL,  |
|                 |   `gender` enum('M','F') NOT NULL,   |
|                 |   `hire_date` date NOT NULL          |
|                 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------------+--------------------------------------+
1 row in set
Time: 0.022s

對employees_ptosc表添加主鍵：

-- 如果employees_ptosc表沒有任何索引和約束會報如下信息，工具執行失敗
Cannot chunk the original table `employees`.`employees_ptosc`: There is no good index and the table is oversized. at /usr/bin/pt-online-schema-change line 5882.

-- 先為employees_ptosc表創建基於first_name的索引idx_first_name，再次執行添加主鍵
mysql root@localhost:employees> create index idx_first_name on employees_ptosc(first_name);
Query OK, 0 rows affected
Time: 1.175s

-- 如果沒有加選項--no-check-unique-key-change會報如下信息
……省略……
Altering `employees`.`employees_ptosc`...
`employees`.`employees_ptosc` was not altered.
You are trying to add an unique key. This can result in data loss if the data is not unique.
Please read the documentation for the --check-unique-key-change parameter.
You can check if the column(s) contain duplicate content by running this/these query/queries:

SELECT IF(COUNT(DISTINCT emp_no) = COUNT(*),
       'Yes, the desired unique index currently contains only unique values',
       'No, the desired unique index contains duplicated values. There will be data loss'
) AS IsThereUniqueness FROM `employees`.`employees_ptosc`;

Keep in mind that these queries could take a long time and consume a lot of resources

大致意思就是工具無法確定需要創建主鍵基於的欄位值是否唯一，一旦有重覆值出現，在數據拷貝的時候容易出現數據丟失，並給出了檢查的語句。

mysql root@localhost:employees> SELECT IF(COUNT(DISTINCT emp_no) = COUNT(*),
                                       'Yes, the desired unique index currently contains only unique values',
                                       'No, the desired unique index contains duplicated values. There will be data loss'
                                ) AS IsThereUniqueness FROM `employees`.`employees_ptosc`;

+---------------------------------------------------------------------+
| IsThereUniqueness                                                   |
+---------------------------------------------------------------------+
| Yes, the desired unique index currently contains only unique values |
+---------------------------------------------------------------------+
1 row in set
Time: 0.274s

使用選項--no-check-unique-key-change再次執行添加主鍵操作：

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees_ptosc --user=admin --ask-pass --alter "add primary key(emp_no)" --charset=utf8 --no-check-unique-key-change --charset=utf8 --execute

工作流程

為了瞭解pt-online-schema-change工具是如何做到不阻塞DML的，還是通過general log來瞭解。

以添加欄位的執行語句獲得的general log為例說明：

-- 初始的一些檢查資料庫參數、負載信息這裡不再細說。
…………省略…………

-- 查看需要執行變更的表狀態
200 Query   SHOW TABLES FROM `employees` LIKE 'employees'
200 Query   SELECT VERSION()

-- 查看表是否存在觸發器
200 Query   SHOW TRIGGERS FROM `employees` LIKE 'employees'

-- 查看表的建表語句
200 Query   /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
200 Query   USE `employees`
200 Query   SHOW CREATE TABLE `employees`.`employees`
200 Query   /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */

-- 查詢表的執行計劃，確定表是否有外鍵關聯
200 Query   EXPLAIN SELECT * FROM `employees`.`employees` WHERE 1=1
200 Query   SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='employees' AND referenced_table_name='employees'
200 Query   EXPLAIN SELECT * FROM `employees`.`dept_emp` WHERE 1=1
200 Query   EXPLAIN SELECT * FROM `employees`.`dept_manager` WHERE 1=1
200 Query   SHOW VARIABLES LIKE 'wsrep_on'
200 Query   /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */

-- 創建'_'(下劃線)開頭相同表結構的新表，並先在新表上執行變更操作
200 Query   USE `employees`
200 Query   SHOW CREATE TABLE `employees`.`employees`
200 Query   /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
200 Query   CREATE TABLE `employees`.`_employees_new` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `idx_first_last` (`first_name`,`last_name`),
  KEY `idx_birth_hire` (`birth_date`,`hire_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
200 Query   ALTER TABLE `employees`.`_employees_new` add comment varchar(50) not null default 'pt-osc'

-- 在原表上分別創建DELETE、UPDATE、INSERT三個觸發器
200 Query   /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
200 Query   USE `employees`
200 Query   SHOW CREATE TABLE `employees`.`_employees_new`
200 Query   /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */

……省略……

200 Query   CREATE TRIGGER `pt_osc_employees_employees_del` AFTER DELETE ON `employees`.`employees` FOR EACH ROW DELETE IGNORE FROM `employees`.`_employees_new` WHERE `employees`.`_employees_new`.`emp_no` <=> OLD.`emp_no`
200 Query   CREATE TRIGGER `pt_osc_employees_employees_upd` AFTER UPDATE ON `employees`.`employees` FOR EACH ROW BEGIN DELETE IGNORE FROM `employees`.`_employees_new` WHERE !(OLD.`emp_no` <=> NEW.`emp_no`) AND `employees`.`_employees_new`.`emp_no` <=> OLD.`emp_no`;REPLACE INTO `employees`.`_employees_new` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (NEW.`emp_no`, NEW.`birth_date`, NEW.`first_name`, NEW.`last_name`, NEW.`gender`, NEW.`hire_date`);END
200 Query   CREATE TRIGGER `pt_osc_employees_employees_ins` AFTER INSERT ON `employees`.`employees` FOR EACH ROW REPLACE INTO `employees`.`_employees_new` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (NEW.`emp_no`, NEW.`birth_date`, NEW.`first_name`, NEW.`last_name`, NEW.`gender`, NEW.`hire_date`)

-- 根據執行計劃判斷chunk包含的行數，以chunk數為單位拷貝數據，為在拷貝過程中為這些行加共用讀鎖
200 Query   EXPLAIN SELECT * FROM `employees`.`employees` WHERE 1=1
200 Query   SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) ORDER BY `emp_no` LIMIT 1 /*first lower boundary*/
200 Query   SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX (`PRIMARY`) WHERE `emp_no` IS NOT NULL ORDER BY `emp_no` LIMIT 1 /*key_len*/
200 Query   EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * FROM `employees`.`employees` FORCE INDEX (`PRIMARY`) WHERE `emp_no` >= '10001' /*key_len*/
200 Query   EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) ORDER BY `emp_no` LIMIT 999, 2 /*next chunk boundary*/
200 Query   SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) ORDER BY `emp_no` LIMIT 999, 2 /*next chunk boundary*/
200 Query   EXPLAIN SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) AND ((`emp_no` <= '11000')) LOCK IN SHARE MODE /*explain pt-online-schema-change 31797 copy nibble*/
200 Query   INSERT LOW_PRIORITY IGNORE INTO `employees`.`_employees_new` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) AND ((`emp_no` <= '11000')) LOCK IN SHARE MODE /*pt-online-schema-change 31797 copy nibble*/

-- 每次拷貝完chunk中數據後，查看是否有警告，查看伺服器的負載情況，這是在每個chunk拷貝完成後進行的
200 Query   SHOW WARNINGS
200 Query   SHOW GLOBAL STATUS LIKE 'Threads_running'
200 Query   EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) ORDER BY `emp_no` LIMIT 12909, 2 /*next chunk boundary*/
200 Query   SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) ORDER BY `emp_no` LIMIT 12909, 2 /*next chunk boundary*/
200 Query   EXPLAIN SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) AND ((`emp_no` <= '23910')) LOCK IN SHARE MODE /*explain pt-online-schema-change 31797 copy nibble*/
200 Query   INSERT LOW_PRIORITY IGNORE INTO `employees`.`_employees_new` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) AND ((`emp_no` <= '23910')) LOCK IN SHARE MODE /*pt-online-schema-change 31797 copy nibble*/
200 Query   SHOW WARNINGS
200 Query   SHOW GLOBAL STATUS LIKE 'Threads_running'
200 Query   EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '23911')) ORDER BY `emp_no` LIMIT 19857, 2 /*next chunk boundary*/
200 Query   SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '23911')) ORDER BY `emp_no` LIMIT 19857, 2 /*next chunk boundary*/
200 Query   EXPLAIN SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '23911')) AND ((`emp_no` <= '43768')) LOCK IN SHARE MODE /*explain pt-online-schema-change 31797 copy nibble*/
200 Query   INSERT LOW_PRIORITY IGNORE INTO `employees`.`_employees_new` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '23911')) AND ((`emp_no` <= '43768')) LOCK IN SHARE MODE /*pt-online-schema-change 31797 copy nibble*/
200 Query   SHOW WARNINGS
200 Query   SHOW GLOBAL STATUS LIKE 'Threads_running'

……省略……

200 Query   EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '480121')) ORDER BY `emp_no` LIMIT 26664, 2 /*next chunk boundary*/
200 Query   SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '480121')) ORDER BY `emp_no` LIMIT 26664, 2 /*next chunk boundary*/
200 Query   SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) ORDER BY `emp_no` DESC LIMIT 1 /*last upper boundary*/
200 Query   EXPLAIN SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '480121')) AND ((`emp_no` <= '499999')) LOCK IN SHARE MODE /*explain pt-online-schema-change 31797 copy nibble*/
200 Query   INSERT LOW_PRIORITY IGNORE INTO `employees`.`_employees_new` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '480121')) AND ((`emp_no` <= '499999')) LOCK IN SHARE MODE /*pt-online-schema-change 31797 copy nibble*/
200 Query   SHOW WARNINGS
200 Query   SHOW GLOBAL STATUS LIKE 'Threads_running'

-- 當拷貝數據完成之後，及時分析表進行統計信息的收集
200 Query   EXPLAIN SELECT * FROM `employees`.`dept_emp` WHERE 1=1
200 Query   SHOW VARIABLES LIKE 'version%'
200 Query   SHOW ENGINES
200 Query   SHOW VARIABLES LIKE 'innodb_version'
200 Query   ANALYZE TABLE `employees`.`_employees_new` /* pt-online-schema-change */

-- 完成舊表與新表的交換，主要受選項--alter-foreign-keys-method取值不同來進行
'
當--alter-foreign-keys-method=drop_swap時，先禁用外鍵約束檢查，刪除舊表，將臨時表重命名為原舊表名，完成變更
'
200 Query   SET foreign_key_checks=0
200 Query   DROP TABLE IF EXISTS `employees`.`employees`
200 Query   RENAME TABLE `employees`.`_employees_new` TO `employees`.`employees`

'
當--alter-foreign-keys-method=rebuild_constraints時，做一個原子性的交換重命名錶的操作，刪除舊表的操作在刪除觸發器時一併操作
'
203 Query     ANALYZE TABLE `employees`.`_employees_new` /* pt-online-schema-change */
203 Query     RENAME TABLE `employees`.`employees` TO `employees`.`_employees_old`, `employees`.`_employees_new` TO `employees`.`employees`

-- 刪除3個觸發器
'
當--alter-foreign-keys-method=drop_swap時，直接刪除。
'
200 Query   DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_del`
200 Query   DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_upd`
200 Query   DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_ins`
200 Query   SHOW TABLES FROM `employees` LIKE '\_employees\_new'
201 Quit    
200 Quit

'
當--alter-foreign-keys-method=rebuild_constraints時，對於關聯的外鍵表執行重建外鍵操作，刪除舊表，完成變更。
'
203 Query     USE `employees`
203 Query     SHOW CREATE TABLE `employees`.`dept_emp`
203 Query     /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
203 Query     ALTER TABLE `employees`.`dept_emp` DROP FOREIGN KEY `_dept_emp_ibfk_1`, ADD CONSTRAINT `__dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees`.`employees` (`emp_no`) ON DELETE CASCADE
203 Query     /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */

203 Query     USE `employees`
203 Query     SHOW CREATE TABLE `employees`.`dept_manager`
203 Query     /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
203 Query     ALTER TABLE `employees`.`dept_manager` DROP FOREIGN KEY `__dept_manager_ibfk_1`, ADD CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees`.`employees` (`emp_no`) ON DELETE CASCADE

203 Query     DROP TABLE IF EXISTS `employees`.`_employees_old`
203 Query     DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_del`
203 Query     DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_upd`
203 Query     DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_ins`
203 Query     SHOW TABLES FROM `employees` LIKE '\_employees\_new'
204 Quit
203 Quit      

工作流程總結：

1. 查詢當前資料庫伺服器信息，包括參數設置，負載信息等，判斷表是否有存在觸發器，是否有外鍵關聯；
2. 創建一張與舊表結構相同的新表，表名為_舊表名；
3. 在新創建的表上做變更操作；
4. 舊表上創建DELETE、UPDATE、INSERT3個觸發器；
5. 拷貝舊表數據到新表上，以chunk為單位進行，拷貝期間涉及的行會持有共用讀鎖；
6. 拷貝期間如果舊表如有DML操作，則通過觸發器更新同步到新表上；
7. 當拷貝數據完成之後舊表與新表進行重命名；
8. 如果有涉及到外鍵，根據工具指定選項進行外鍵處理；
9. 刪除舊表；
10. 刪除舊表上觸發器。

總結

pt-online-schema-change工具對於任意的DDL語句都是通過創新表拷貝數據來進行，期間都支持DML，而Online DDL根據DDL類型的來區分是否需要對錶進行COPY TABLE操作，有點類似於工具的創建臨時表進行變更，而不需要COPY TABLE操作的DDL語句在執行期間支持DML。

關於在對錶進行DDL時使用MySQL原生的Online DDL特性還是使用pt-online-schema-change工具，通過以上對工具使用的說明與用法測試可以總結如下：

• 如果MySQL版本不支持Online DDL特性，比如早於5.6版本的MySQL，則使用pt-online-schema-change工具；
• 如果MySQL版本支持Online DDL特性，則優先考慮使用Online DDL，因為畢竟原生的支持較好，同時不容易產生不可預知的錯誤；
• 如果DDL語句在使用Online DDL時需要進行COPY TABLE操作，建議使用pt-online-schema-change工具，因為期間支持DML操作。
• 如果表存在觸發器的情況下，優先使用Online DDL，對於MySQL5.7.2之後版本則可以pt-online-schema-change工具並通過指定選項--preserve-triggers；
• 如果涉及外鍵關聯的表，優先考慮使用Online DDL。

關於pt-online-schema-change更多的說明可以參考官方文檔：https://www.percona.com/downloads/percona-toolkit/LATEST/

參考

https://www.percona.com/downloads/percona-toolkit/LATEST/
https://dev.mysql.com/doc/index-other.html

☆〖本人水平有限，文中如有錯誤還請留言批評指正！〗☆