pt-online-schema-change 添加欄位過程解析

• 2019 年 10 月 4 日
• 筆記

對於大表的 DDL操作，我們一般使用 pt-online-schema-change  來進行。

具體的操作步驟如下：

1、創建一張新表_xxx_new ，對其做DDL操作

2、創建3個觸發器(deleteupdateinsert)，在複製數據開始之後，將對源數據表繼續進行數據修改的操作記錄下來，以便在數據複製結束後執行這些操作，保證數據不會丟失

3、複製數據，從源數據表複製數據到新表（分成多個chunk，小事務提交）

4、修改外鍵相關的子表，根據修改後的數據，修改外鍵關聯的子表

5、將源數據表重命名為old表，將新表更改為源表名

6、刪除原表

7、刪除觸發器

下面看一個例子，來親自驗證下這個過程。

如下是一條DDL測試語句：

ALTER TABLE tb_2 ADD COLUMN content text ;

對應的pt-osc寫法如下：

pt-online-schema-change –user=root –password=123456 -h localhost –alter "ADD COLUMN content text" D=db1,t=tb_2–no-check-replication-filters –alter-foreign-keys-method=auto –recursion-method=none –print –charset=utf8 –execute

下面是我在使用pt-osc執行上述DDL時候，generallog裡面記錄的內容（部分不太重要的地方有刪減..）

Connectroot@localhost on db1   set autocommit=1   SELECT @@SQL_MODE   /*!40101 SET NAMES "utf8"*/   SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'   SET SESSION innodb_lock_wait_timeout=1   SHOW VARIABLES LIKE 'lock_wait_timeout'   SET SESSION lock_wait_timeout=60   SHOW VARIABLES LIKE 'wait_timeout'   SET SESSION wait_timeout=10000   SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/   SELECT @@server_id /*!50038 , @@hostname*/  Connectroot@localhost on db1   set autocommit=1   SELECT @@SQL_MODE   /*!40101 SET NAMES "utf8"*/   SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'   SET SESSION innodb_lock_wait_timeout=1   SHOW VARIABLES LIKE 'lock_wait_timeout'   SET SESSION lock_wait_timeout=60   SHOW VARIABLES LIKE 'wait_timeout'   SET SESSION wait_timeout=10000   SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/   SELECT @@server_id /*!50038 , @@hostname*/   SHOW VARIABLES LIKE 'wsrep_on'   SHOW VARIABLES LIKE 'version%'   SHOW ENGINES   SHOW VARIABLES LIKE 'innodb_version'   SHOW GLOBAL STATUS LIKE 'Threads_running'   SHOW GLOBAL STATUS LIKE 'Threads_running'   SELECT CONCAT(@@hostname, @@port)   SHOW VARIABLES   SHOW TABLES FROM `db1` LIKE 'tb_2'  ### 查看原表是否已存在觸發器   SHOW TRIGGERS FROM `db1` LIKE 'tb_2'   /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */   USE `db1`   SHOW CREATE TABLE `db1`.`tb_2`   /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */   EXPLAIN SELECT * FROM `db1`.`tb_2` WHERE 1=1   SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='db1' AND referenced_table_name='tb_2'   SHOW VARIABLES LIKE 'wsrep_on'   /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */   USE `db1`   SHOW CREATE TABLE `db1`.`tb_2`   /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */  #### 創建新表，並對其做ALTER操作   CREATE TABLE `db1`.`_tb_2_new` (    `id` bigint(20) NOT NULL,    `url` varchar(2048) NOT NULL DEFAULT '',    `appid` smallint(6) NOT NULL,    `rand_code` int(11) NOT NULL DEFAULT '0' COMMENT '隨機碼',    `create_time` bigint(20) NOT NULL,    PRIMARY KEY (`id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8   ALTER TABLE `db1`.`_tb_2_new` ADD COLUMN content text   /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */   USE `db1`   SHOW CREATE TABLE `db1`.`_tb_2_new`   /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */  ##### 創建3個觸發器(delete、update、insert) (在原表上update，新臨時表上是replace into整行數據，所以達到有則更新，無則插入。同時配合後面的 insert ignore，保證這條數據不會因為重複而失敗)   CREATE TRIGGER `pt_osc_db1_tb_2_del` AFTER DELETE ON `db1`.`tb_2` FOR EACH ROW DELETE IGNORE FROM `db1`.`_tb_2_new` WHERE `db1`.`_tb_2_new`.`id` <=> OLD.`id`  CREATE TRIGGER `pt_osc_db1_tb_2_upd` AFTER UPDATE ON `db1`.`tb_2` FOR EACH ROW REPLACE INTO `db1`.`_tb_2_new` (`id`, `url`, `appid`, `rand_code`, `create_time`) VALUES (NEW.`id`, NEW.`url`, NEW.`appid`, NEW.`rand_code`, NEW.`create_time`)  CREATE TRIGGER `pt_osc_db1_tb_2_ins` AFTER INSERT ON `db1`.`tb_2` FOR EACH ROW REPLACE INTO `db1`.`_tb_2_new` (`id`, `url`, `appid`, `rand_code`, `create_time`) VALUES (NEW.`id`, NEW.`url`, NEW.`appid`, NEW.`rand_code`, NEW.`create_time`)   EXPLAIN SELECT * FROM `db1`.`tb_2` WHERE 1=1   SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db1`.`tb_2` FORCE INDEX(`PRIMARY`) ORDER BY `id` LIMIT 1 /*first lower boundary*/   SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db1`.`tb_2` FORCE INDEX (`PRIMARY`) WHERE `id` IS NOT NULL ORDER BY `id` LIMIT 1 /*key_len*/   EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * FROM `db1`.`tb_2` FORCE INDEX (`PRIMARY`) WHERE `id` >= '20000000' /*key_len*/  ### 分塊查詢數據，減小後續操作的持鎖範圍   EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db1`.`tb_2` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '20000000')) ORDER BY `id` LIMIT 999, 2 /*next chunk boundary*/   SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db1`.`tb_2` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '20000000')) ORDER BY `id` LIMIT 999, 2 /*next chunk boundary*/   EXPLAIN SELECT `id`, `url`, `appid`, `rand_code`, `create_time` FROM `db1`.`tb_2` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '20000000')) AND ((`id` <= '20000999')) LOCK IN SHARE MODE /*explain pt-online-schema-change 12296 copy nibble*/  ### 開始灌數據操作   INSERT LOW_PRIORITY IGNORE INTO `db1`.`_tb_2_new` (`id`, `url`, `appid`, `rand_code`, `create_time`) SELECT `id`, `url`, `appid`, `rand_code`, `create_time` FROM `db1`.`tb_2` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '20000000')) AND ((`id` <= '20000999')) LOCK IN SHARE MODE /*pt-online-schema-change 12296 copy nibble*/     SHOW WARNINGS   SHOW GLOBAL STATUS LIKE 'Threads_running'   。。。 對於操作期間有數據INSERT、UPDATE寫入的話，這裡還會出現REPLACE INTO 類型的SQL語句 。。。  #### 重命名新、老表名(這個操作期間是鎖表的，時間很短暫)   RENAME TABLE `db1`.`tb_2` TO `db1`.`_tb_2_old`, `db1`.`_tb_2_new` TO `db1`.`tb_2`  #### 刪除原表   DROP TABLE IF EXISTS `db1`.`_tb_2_old`  #### 刪除觸發器   DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_tb_2_del`   DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_tb_2_upd`   DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_tb_2_ins`   SHOW TABLES FROM `db1` LIKE '_tb_2_new'  Quit

Quit