天天看點
傳回

percona-toolkit 之 【pt-online-schema-change】說明

背景：

      MySQL 大字段的DDL操作：加減字段、索引、修改字段屬性等，在5.1之前都是非常耗時耗力的，特别是會對MySQL服務産生影響。在5.1之後随着Plugin Innodb的出現線上加索引的提高了很多，但是還會影響（時間縮短了），主要是出現了MDL鎖。不過5.6可以避免上面的情況，但目前大部分在用的版本都是5.6之前的，是以DDL操作一直是運維人員“頭疼"的事。那如何在不鎖表的情況下安全快速地更新表結構？現在來說明下percona-toolkit 的 pt-online-schema-change 的使用說明，可以很好的解決上述的問題。

工作原理：

      模仿MySQL的alter，但不同的是在alter操作更改表結構的時候不用鎖定表，也就是說執行alter的時候不會阻塞寫和讀取操作，用戶端可以繼續都和修改資料。注意執行這個工具的時候必須做好備份，操作之前最好詳細讀一下官方文檔。

1、如果存在外鍵，根據alter-foreign-keys-method參數的值，檢測外鍵相關的表，做相應設定的處理。沒有使用 --alter-foreign-keys-method 指定特定的值，該工具不予執行

2、建立一個新的表，表結構為修改後的資料表，用于從源資料表向新表中導入資料。

3、建立觸發器，用于記錄從拷貝資料開始之後，對源資料表繼續進行資料修改的操作記錄下來，用于資料拷貝結束後，執行這些操作，保證資料不會丢失。如果表中已經定義了觸發器這個工具就不能工作了。

4、拷貝資料，從源資料表中拷貝資料到新表中。

5、修改外鍵相關的子表，根據修改後的資料，修改外鍵關聯的子表。

6、rename源資料表為old表，把新表rename為源表名，并将old表删除。

7、删除觸發器。

使用方法：

pt-online-schema-change [OPTIONS] DSN

具體說下 OPTIONS 的一些常用的參數：

--user:
-u，連接配接的使用者名

--password：
-p，連接配接的密碼

--database：
-D，連接配接的資料庫

--port
-P，連接配接資料庫的端口

--host:
-h，連接配接的主機位址

--socket:
-S，連接配接的套接字檔案

--ask-pass
隐式輸入連接配接MySQL的密碼

--charset
指定修改的字元集

--defaults-file
-F，讀取配置檔案

--alter：
結構變更語句，不需要alter table關鍵字。可以指定多個更改，用逗号分隔。如下場景，需要注意：
    不能用RENAME來重命名表。        
    列不能通過先删除，再添加的方式進行重命名，不會将資料拷貝到新列。
    如果加入的列非空而且沒有預設值，則工具會失敗。即其不會為你設定一個預設值，必須顯示指定。
    删除外鍵(drop foreign key constrain_name)時，需要指定名稱_constraint_name，而不是原始的constraint_name。
    如：CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)，需要指定：--alter "DROP FOREIGN KEY _fk_foo"

--alter-foreign-keys-method
如何把外鍵引用到新表?需要特殊處理帶有外鍵限制的表,以保證它們可以應用到新表.當重命名表的時候,外鍵關系會帶到重命名後的表上。
該工具有兩種方法,可以自動找到子表,并修改限制關系。
    auto： 在rebuild_constraints和drop_swap兩種處理方式中選擇一個。
    rebuild_constraints：使用 ALTER TABLE語句先删除外鍵限制,然後再添加.如果子表很大的話,會導緻長時間的阻塞。
    drop_swap： 執行FOREIGN_KEY_CHECKS=0,禁止外鍵限制,删除原表,再重命名新表。這種方式很快,也不會産生阻塞,但是有風險：
    1, 在删除原表和重命名新表的短時間内,表是不存在的,程式會傳回錯誤。
    2, 如果重命名表出現錯誤,也不能復原了.因為原表已經被删除。
    none： 類似"drop_swap"的處理方式,但是它不删除原表,并且外鍵關系會随着重命名轉到老表上面。
      
--[no]check-alter
預設yes，文法解析。配合--dry-run 和 --print 一起運作，來檢查是否有問題（change column，drop primary key）。

--max-lag
預設1s。每個chunk拷貝完成後，會檢視所有複制Slave的延遲情況。要是延遲大于該值，則暫停複制資料，直到所有從的滞後小于這個值，使用Seconds_Behind_Master。如果有任何從滞後超過此選項的值，則該工具将睡眠--check-interval指定的時間，再檢查。如果從被停止，将會永遠等待，直到從開始同步，并且延遲小于該值。如果指定--check-slave-lag，該工具隻檢查該伺服器的延遲，而不是所有伺服器。

--check-slave-lag
指定一個從庫的DSN連接配接位址,如果從庫超過--max-lag參數設定的值,就會暫停操作。

--recursion-method
預設是show processlist，發現從的方法，也可以是host，但需要在從上指定report_host，通過show slave hosts來找到，可以指定none來不檢查Slave。      
METHOD       USES
===========  ==================
processlist  SHOW PROCESSLIST
hosts        SHOW SLAVE HOSTS
dsn=DSN      DSNs from a table
none         Do not find slaves
指定none則表示不在乎從的延遲。      
--check-interval 
預設是1。--max-lag檢查的睡眠時間。 

--[no]check-plan 
預設yes。檢查查詢執行計劃的安全性。

--[no]check-replication-filters 
預設yes。如果工具檢測到伺服器選項中有任何複制相關的篩選，如指定binlog_ignore_db和replicate_do_db此類。發現有這樣的篩選，工具會報錯且退出。因為如果更新的表Master上存在，而Slave上不存在，會導緻複制的失敗。使用–no-check-replication-filters選項來禁用該檢查。 

--[no]swap-tables 
預設yes。交換原始表和新表，除非你禁止--[no]drop-old-table。 

--[no]drop-triggers 
預設yes，删除原表上的觸發器。 --no-drop-triggers 會強制開啟 --no-drop-old-table 即：不删除觸發器就會強制不删除原表。 

--new-table-name 
複制建立新表的名稱，預設%T_new。 

--[no]drop-new-table 
預設yes。删除新表，如果複制組織表失敗。 

--[no]drop-old-table 
預設yes。複制資料完成重命名之後，删除原表。如果有錯誤則會保留原表。 

--max-load 
預設為Threads_running=25。每個chunk拷貝完後，會檢查SHOW GLOBAL STATUS的内容，檢查名額是否超過了指定的門檻值。如果超過，則先暫停。這裡可以用逗号分隔，指定多個條件，每個條件格式： status名額=MAX_VALUE或者status名額:MAX_VALUE。如果不指定MAX_VALUE，那麼工具會這隻其為目前值的120%。 

--critical-load 
預設為Threads_running=50。用法基本與--max-load類似，如果不指定MAX_VALUE，那麼工具會這隻其為目前值的200%。如果超過指定值，則工具直接退出，而不是暫停。 

--default-engine 
預設情況下，新的表與原始表是相同的存儲引擎，是以如果原來的表使用InnoDB的，那麼新表将使用InnoDB的。在涉及複制某些情況下，很可能主從的存儲引擎不一樣。使用該選項會預設使用預設的存儲引擎。 

--set-vars 
設定MySQL變量，多個用逗号分割。預設該工具設定的是： wait_timeout=10000 innodb_lock_wait_timeout=1 lock_wait_timeout=60 

--chunk-size-limit
當需要複制的塊遠大于設定的chunk-size大小,就不複制.預設值是4.0，一個沒有主鍵或唯一索引的表,塊大小就是不确定的。

--chunk-time
在chunk-time執行的時間内,動态調整chunk-size的大小,以适應伺服器性能的變化，該參數設定為0,或者指定chunk-size,都可以禁止動态調整。

--chunk-size
指定塊的大小,預設是1000行,可以添加k,M,G字尾.這個塊的大小要盡量與--chunk-time比對，如果明确指定這個選項,那麼每個塊就會指定行數的大小.	

--[no]check-plan
預設yes。為了安全,檢查查詢的執行計劃.預設情況下,這個工具在執行查詢之前會先EXPLAIN,以擷取一次少量的資料,如果是不好的EXPLAIN,那麼會擷取一次大量的資料，這個工具會多次執行EXPALIN,如果EXPLAIN不同的結果,那麼就會認為這個查詢是不安全的。	

--statistics
列印出内部事件的數目，可以看到複制資料插入的數目。

--dry-run
建立和修改新表，但不會建立觸發器、複制資料、和替換原表。并不真正執行，可以看到生成的執行語句，了解其執行步驟與細節。--dry-run與--execute必須指定一個，二者互相排斥。和--print配合最佳。

--execute
确定修改表，則指定該參數。真正執行。--dry-run與--execute必須指定一個，二者互相排斥。

--print
列印SQL語句到标準輸出。指定此選項可以讓你看到該工具所執行的語句，和--dry-run配合最佳。

--progress
複制資料的時候列印進度報告，二部分組成：第一部分是百分比，第二部分是時間。

--quiet
-q，不把資訊标準輸出。

更多的參數資訊請見官方文檔。

測試：

測試一：檢視其實作方式、原理

測試表：
CREATE TABLE `online_table` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
)engine = innodb default charset utf8;      
zhoujy@zhoujy:~$ pt-online-schema-change --user=root --password=123456 --host=192.168.200.25  --alter "ADD COLUMN content text" D=test,t=online_table --print --dry-run

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
#需要指定--execute去執行
Starting a dry run.  `test`.`online_table` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
#新表
CREATE TABLE `test`.`_online_table_new` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Created new table test._online_table_new OK.
#修改新表
Altering new table...
ALTER TABLE `test`.`_online_table_new` ADD COLUMN content text
Altered `test`.`_online_table_new` OK.
#删除新表，因為指令沒有進行複制，是以預設下會删除新表，除非指定：--no-drop-new-table
Dropping new table...
DROP TABLE IF EXISTS `test`.`_online_table_new`;
Dropped new table OK.
#指令執行完成，因為沒有指定--execute，是以原表沒有被修改
Dry run complete.  `test`.`online_table` was not altered.
#原表需要一個主鍵或則唯一索引，因為删除的觸發器需要，否則資料不會被複制
The new table `test`.`_online_table_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.

上面已經說明了該工具的實作方式，下面來執行看是否有效。先為原表添加主鍵：

[email protected] : test 10:14:21>alter table online_table add primary key (id),modify id int not null auto_increment;
Query OK, 0 rows affected (0.28 sec)      
zhoujy@zhoujy:~$ pt-online-schema-change --user=root --password=123456 --host=192.168.200.25  --alter "ADD COLUMN content text" D=test,t=online_table --print --execute

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 `test`.`online_table`...
#建立新表
Creating new table...
CREATE TABLE `test`.`_online_table_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Created new table test._online_table_new OK.
Altering new table...
#修改新表
ALTER TABLE `test`.`_online_table_new` ADD COLUMN content text
Altered `test`.`_online_table_new` OK.
Creating triggers...
#觸發器建立
CREATE TRIGGER `pt_osc_test_online_table_del` AFTER DELETE ON `test`.`online_table` FOR EACH ROW DELETE IGNORE FROM `test`.`_online_table_new` WHERE `test`.`_online_table_new`.`id` <=> OLD.`id`
CREATE TRIGGER `pt_osc_test_online_table_upd` AFTER UPDATE ON `test`.`online_table` FOR EACH ROW REPLACE INTO `test`.`_online_table_new` (`id`, `name`, `age`) VALUES (NEW.`id`, NEW.`name`, NEW.`age`)
CREATE TRIGGER `pt_osc_test_online_table_ins` AFTER INSERT ON `test`.`online_table` FOR EACH ROW REPLACE INTO `test`.`_online_table_new` (`id`, `name`, `age`) VALUES (NEW.`id`, NEW.`name`, NEW.`age`)
Created triggers OK.
#執行資料複制的操作，原表資料插入到新表
Copying approximately 1 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_online_table_new` (`id`, `name`, `age`) SELECT `id`, `name`, `age` FROM `test`.`online_table` LOCK IN SHARE MODE /*pt-online-schema-change 6167 copy table*/
Copied rows OK.
#插入完畢後交換表
Swapping tables...
RENAME TABLE `test`.`online_table` TO `test`.`_online_table_old`, `test`.`_online_table_new` TO `test`.`online_table`
Swapped original and new tables OK.
#資料複制完畢之後，删除原表
Dropping old table...
DROP TABLE IF EXISTS `test`.`_online_table_old`
Dropped old table `test`.`_online_table_old` OK.
#删除觸發器
Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_online_table_del`;
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_online_table_upd`;
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_online_table_ins`;
Dropped triggers OK.
Successfully altered `test`.`online_table`.

上面指定了--execute，輸出的資訊裡面也表示原表已經被修改成功并且記錄了很詳細的操作資訊，檢視表是否已經被修改：

[email protected] : test 10:15:12>desc online_table;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(10) | YES  |     | NULL    |                |
| age     | int(11)     | YES  |     | NULL    |                |
| content | text        | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

确定表已經添加了字段。通過上面的測試，已經基本了解該工具的工作原理了，下面開始測試一些正式場景下的使用方法。

測試二：主從環境的基本操作

測試表
[email protected] : crm_production 11:25:48>desc tmp_test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+      
zhoujy@zhoujy:~$ pt-online-schema-change --user=zjy --host=192.168.200.111  --alter "ADD COLUMN content text" D=crm_production,t=tmp_test --ask-pass  --print --execute
Enter MySQL password: 
#報錯，因為該工具在檢測到伺服器選項中有任何複制相關的篩選會退出，需要指定：--no-check-replication-filters
Replication filters are set on these hosts:
  database2
    replicate_do_db = crm_production
Please read the --check-replication-filters documentation to learn how to solve this problem. at /usr/local/bin/pt-online-schema-change line 8015, <STDIN> line 2.

加上參數：--no-check-replication-filters

zhoujy@zhoujy:~$ pt-online-schema-change --user=zjy --host=192.168.200.111  --alter "ADD COLUMN content text" D=crm_production,t=tmp_test --ask-pass --no-check-replication-filters  --print --execute
Enter MySQL password: 

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 `crm_production`.`tmp_test`...
Creating new table...
CREATE TABLE `crm_production`.`_tmp_test_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Created new table crm_production._tmp_test_new OK.
Altering new table...
ALTER TABLE `crm_production`.`_tmp_test_new` ADD COLUMN content text
Altered `crm_production`.`_tmp_test_new` OK.
Creating triggers...
CREATE TRIGGER `pt_osc_crm_production_tmp_test_del` AFTER DELETE ON `crm_production`.`tmp_test` FOR EACH ROW DELETE IGNORE FROM `crm_production`.`_tmp_test_new` WHERE `crm_production`.`_tmp_test_new`.`id` <=> OLD.`id`
CREATE TRIGGER `pt_osc_crm_production_tmp_test_upd` AFTER UPDATE ON `crm_production`.`tmp_test` FOR EACH ROW REPLACE INTO `crm_production`.`_tmp_test_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)
CREATE TRIGGER `pt_osc_crm_production_tmp_test_ins` AFTER INSERT ON `crm_production`.`tmp_test` FOR EACH ROW REPLACE INTO `crm_production`.`_tmp_test_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)
Created triggers OK.
Copying approximately 1 rows...
INSERT LOW_PRIORITY IGNORE INTO `crm_production`.`_tmp_test_new` (`id`, `name`) SELECT `id`, `name` FROM `crm_production`.`tmp_test` LOCK IN SHARE MODE /*pt-online-schema-change 6982 copy table*/
Copied rows OK.
Swapping tables...
RENAME TABLE `crm_production`.`tmp_test` TO `crm_production`.`_tmp_test_old`, `crm_production`.`_tmp_test_new` TO `crm_production`.`tmp_test`
Swapped original and new tables OK.
Dropping old table...
DROP TABLE IF EXISTS `crm_production`.`_tmp_test_old`
Dropped old table `crm_production`.`_tmp_test_old` OK.
Dropping triggers...
DROP TRIGGER IF EXISTS `crm_production`.`pt_osc_crm_production_tmp_test_del`;
DROP TRIGGER IF EXISTS `crm_production`.`pt_osc_crm_production_tmp_test_upd`;
DROP TRIGGER IF EXISTS `crm_production`.`pt_osc_crm_production_tmp_test_ins`;
Dropped triggers OK.
Successfully altered `crm_production`.`tmp_test`.

已經修改：

[email protected] : crm_production 11:15:39>desc tmp_test;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(10) | YES  |     | NULL    |                |
| content | text        | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+

除了add column，也可以modify column，drop column，對于change column 則需要指定：--no-check-alter

測試三：主外鍵表的基本操作

測試表（隻能是INNODB表）
create table tt(id int not null auto_increment,name varchar(10),primary key(id))engine =innodb default charset utf8;
create table xx(id int not null auto_increment,tt_id int not null,name varchar(10),primary key(id))engine =innodb default charset utf8;
alter table xx add foreign key fk_xx_tt_id(tt_id) references tt(id);      
zhoujy@zhoujy:~$ pt-online-schema-change --user=root --password=123456 --host=192.168.200.25  --alter "ADD COLUMN content text" D=aaa,t=tt --no-check-replication-filters --print --execute


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
Child tables:
  `aaa`.`xx` (approx. 3 rows)
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 參數來操作有外鍵的表。要是沒有外鍵而加了參數的話會出現：

No foreign keys reference `aaa`.`xx`; ignoring --alter-foreign-keys-method。

zhoujy@zhoujy:~$ pt-online-schema-change --user=root --password=123456 --host=192.168.200.25  --alter "ADD COLUMN content text" D=aaa,t=tt --no-check-replication-filters --alter-foreign-keys-method auto --print --execute

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
#子表
Child tables:
  `aaa`.`xx` (approx. 3 rows)
Will automatically choose the method to update foreign keys.
Altering `aaa`.`tt`...
#建立新表
Creating new table...
CREATE TABLE `aaa`.`_tt_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
Created new table aaa._tt_new OK.
#修改新表
Altering new table...
ALTER TABLE `aaa`.`_tt_new` ADD COLUMN content text
Altered `aaa`.`_tt_new` OK.
Creating triggers...
CREATE TRIGGER `pt_osc_aaa_tt_del` AFTER DELETE ON `aaa`.`tt` FOR EACH ROW DELETE IGNORE FROM `aaa`.`_tt_new` WHERE `aaa`.`_tt_new`.`id` <=> OLD.`id`
CREATE TRIGGER `pt_osc_aaa_tt_upd` AFTER UPDATE ON `aaa`.`tt` FOR EACH ROW REPLACE INTO `aaa`.`_tt_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)
CREATE TRIGGER `pt_osc_aaa_tt_ins` AFTER INSERT ON `aaa`.`tt` FOR EACH ROW REPLACE INTO `aaa`.`_tt_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)
Created triggers OK.
#複制資料
Copying approximately 3 rows...
INSERT LOW_PRIORITY IGNORE INTO `aaa`.`_tt_new` (`id`, `name`) SELECT `id`, `name` FROM `aaa`.`tt` LOCK IN SHARE MODE /*pt-online-schema-change 8969 copy table*/
Copied rows OK.
#處理外鍵的方式，選擇的是auto，會根據情況進行選擇：重建或則禁用外鍵檢測。
Max rows for the rebuild_constraints method: 17898
Determining the method to update foreign keys...
  `aaa`.`xx`: 3 rows; can use rebuild_constraints
#交換表
Swapping tables...
RENAME TABLE `aaa`.`tt` TO `aaa`.`_tt_old`, `aaa`.`_tt_new` TO `aaa`.`tt`
Swapped original and new tables OK.
#重建外鍵
Rebuilding foreign key constraints...
ALTER TABLE `aaa`.`xx` DROP FOREIGN KEY `xx_ibfk_1`, ADD CONSTRAINT `_xx_ibfk_1` FOREIGN KEY (`tt_id`) REFERENCES `aaa`.`tt` (`id`)
Rebuilt foreign key constraints OK.
Dropping old table...
DROP TABLE IF EXISTS `aaa`.`_tt_old`
Dropped old table `aaa`.`_tt_old` OK.
Dropping triggers...
DROP TRIGGER IF EXISTS `aaa`.`pt_osc_aaa_tt_del`;
DROP TRIGGER IF EXISTS `aaa`.`pt_osc_aaa_tt_upd`;
DROP TRIGGER IF EXISTS `aaa`.`pt_osc_aaa_tt_ins`;
Dropped triggers OK.
Successfully altered `aaa`.`tt`.

對可靠性要求不高可以用auto模式更新，要是可靠性要求高則需要用rebuild_constraints模式。即：

--alter-foreign-keys-method=rebuild_constraints

其他：

上面的測試都是把原表删除了，要是不删除原表則： --no-drop-old-table，這樣會讓原表（_test_binlog_old）保留。

pt-online-schema-change --user=root --password=123456 --host=192.168.200.25  --alter "ADD COLUMN a text" D=aaa,t=test_binlog --no-check-replication-filters --no-drop-old-table --print --execute

要是線上上環境上添加字段，但又不想影響到服務，可以用參數：--max-load 去執行該工具，預設是 Threads_running=25，即目前有這麼多線程在運作的時候就暫停資料的複制，等少于該值則繼續複制資料到新表：

pt-online-schema-change --user=root --password=123456 --host=192.168.200.25  --alter "add INDEX idx_address(address)" D=aaa,t=tmp_test --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --max-load=Threads_running=2 --statistics --print --execute

暫停的時候标準輸出裡面會有：

Pausing because Threads_running=2。等到運作的線程數小于給定的值，則就繼續複制資料，直到完成。

總結：

       1、當業務量較大時，修改操作會等待沒有資料修改後，執行最後的rename操作。是以，在修改表結構時，應該盡量選擇在業務相對空閑時，至少修改表上的資料操作較低時，執行較為妥當。

       2、如果對外鍵表操作時，四種外鍵操作類型需要根據表的資料量和可靠程度，進行選擇。處于可靠性的原因，盡量使用rebuild_constraints類型，如果沒有可靠性要求，可以使用auto類型。

       3、由于可能存在一定的風險，在操作之前，建議對資料表進行備份，可以使得操作更安全、可靠。

       使用該工具的前提是處理的表需要有主鍵或則唯一索引。當處理有外鍵的表時，需要加--alter-foreign-keys-method參數，值可以根據情況設定。當是主從環境，不在乎從的延遲，則需要加--recursion-method=none參數。當需要盡可能的對服務産生小的影響，則需要加上--max-load參數。

      通過上面的測試，總結下該工具的使用方法：

表：
CREATE TABLE `tmp_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

1，增加字段：

pt-online-schema-change --user=root --password=123456 --host=192.168.200.25  --alter "ADD COLUMN content text" D=aaa,t=tmp_test --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute

2，删除字段：

pt-online-schema-change --user=root --password=123456 --host=192.168.200.25  --alter "DROP COLUMN content " D=aaa,t=tmp_test --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --execute

3，修改字段：

pt-online-schema-change --user=root --password=123456 --host=192.168.200.25  --alter "MODIFY COLUMN age TINYINT NOT NULL DEFAULT 0" D=aaa,t=tmp_test --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --execute

4，字段改名：

pt-online-schema-change --user=root --password=123456 --host=192.168.200.25  --alter "CHANGE COLUMN age address varchar(30)" D=aaa,t=tmp_test --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --execut

5，增加索引：

pt-online-schema-change --user=root --password=123456 --host=192.168.200.25  --alter "ADD INDEX idx_address(address)" D=aaa,t=tmp_test --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute

6，删除索引：

pt-online-schema-change --user=root --password=123456 --host=192.168.200.25  --alter "DROP INDEX idx_address" D=aaa,t=tmp_test --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute

 使用方法就介紹到這裡，更多的資訊見官方文檔。在執行過程中，是否會鎖住其他操作，大家可以自行測試。

更多資訊：

http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html

http://blog.chinaunix.net/uid-28437434-id-3977100.html

http://blog.chinaunix.net/uid-26896862-id-3476953.html

~~~~~~~~~~~~~~~

萬物之中,希望至美

~~~~~~~~~~~~~~~

Online DDL pt-online-schema-change pt-tool
上一篇: 添加自定義監控項目,zabbix配置告警-添加郵箱,zabbix配置告警-添加使用者,測試告警
下一篇: uwp 自定義語音識别規則

繼續閱讀