MySQL:無鎖變更工具pt-online-schema-change

一、MySQL常用的無鎖變更工具

1. Online Schema Change：Online Schema Change（OSC）工具是MySQL官方提供的一種無鎖變更工具，它可以在不停止MySQL伺服器的情況下對表結構進行修改。OSC利用了InnoDB存儲引擎的特性，使用複制和重放日志的方式來實作無鎖變更。
2. pt-online-schema-change：pt-online-schema-change是Percona Toolkit中的一個工具，它可以在不停止MySQL伺服器的情況下對表結構進行修改。pt-online-schema-change使用了InnoDB存儲引擎的特性來實作無鎖變更。與OSC不同的是，pt-online-schema-change使用了一個代理表來實作表結構變更，而不是直接在原表上進行修改。
3. gh-ost：gh-ost是GitHub開源的一個工具，它可以在不停止MySQL伺服器的情況下對表結構進行修改。gh-ost使用了InnoDB存儲引擎的特性來實作無鎖變更。與pt-online-schema-change不同的是，gh-ost使用了一個ghost表來實作表結構變更，而不是使用代理表。
4. Facebook OSC：Facebook OSC是Facebook開源的一個工具，它可以在不停止MySQL伺服器的情況下對表結構進行修改。Facebook OSC使用了自己開發的存儲引擎MyRocks來實作無鎖變更。與其他工具不同的是，Facebook OSC可以在進行表結構變更的同時進行資料的轉換、清理和處理。

二、pt-online-schema-change原理

pt-online-schema-change 是一個用于線上更改 MySQL 表結構的工具，它是 Percona Toolkit 的一部分。它的原理是通過線上複制表資料，同時在新表上應用修改，進而避免了直接修改原始表結構導緻的鎖表和性能下降問題。以下是pt-online-schema-change的基本工作原理：

1. 建立一個與原表結構相同的新表，同時應用使用者指定的表結構更改。
2. 在新表上建立觸發器，将對原表的寫操作（如 INSERT、UPDATE 和 DELETE）同步到新表上。
3. 逐漸将原表的資料複制到新表，以便在新表上保持資料的一緻性。
4. 資料複制完成後，将原表和新表互換，然後删除原表以及相關的觸發器。

三、使用場景

pt-online-schema-change 在以下場景中特别有用：

1. 修改大型表的結構：對于包含數百萬甚至數十億行的大型表，直接修改表結構可能導緻長時間的鎖表和性能下降。pt-online-schema-change 通過線上方式避免了這些問題。
2. 避免業務中斷：在需要修改生産環境資料庫表結構時，pt-online-schema-change 可以在不影響業務正常運作的情況下進行表結構更改。
3. 相容各種存儲引擎：pt-online-schema-change 支援各種 MySQL 存儲引擎，如 InnoDB 和 MyISAM。

四、使用示例

以下是一個使用 pt-online-schema-change 修改表結構的示例。

4.1 準備環境

首先，確定已經安裝了 Percona Toolkit。如果沒有安裝，請參考 Percona Toolkit 官方文檔 進行安裝。

4.2 示例表結構

假設我們有一個名為 employees 的表，包含以下字段：

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;           

現在，我們需要添加一個新的字段 email 到 employees 表，并将其設定為唯一限制。

4.3 使用 pt-online-schema-change

添加新字段

運作以下指令以線上方式添加新字段：

pt-online-schema-change --alter "ADD COLUMN email VARCHAR(255) UNIQUE" D=my_database,t=employees --execute           

上述指令中的參數：

• --alter：指定要執行的表結構更改。
• D=my_database：指定包含目标表的資料庫名稱。
• t=employees：指定要更改的表名稱。
• --execute：執行表結構更改，而不僅僅是列印更改。

執行完成後，employees 表将包含新的 email 字段，并具有唯一限制。

修改字段

将表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           

添加索引

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           

删除外鍵

需要為外鍵指定名稱為_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           

添加主鍵

使用選項--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 時，需要注意以下幾點：

1. 在執行過程中，避免對原表進行更改，否則可能導緻資料不一緻。
2. 確定在執行前進行充分的測試，以確定修改後的表結構符合預期。
3. 在執行過程中，可能會對資料庫性能産生一定影響，是以最好在業務低峰期進行操作。

總之，pt-online-schema-change 是一個強大且靈活的工具，可以幫助您在不影響業務正常運作的情況下線上更改 MySQL 表結構。使用它時，請確定充分了解其工作原理和注意事項，以確定順利完成表結構更改。