Mysql資料庫熱更新：pt-online-schema-change

總所周知，資料庫最昂貴的操作莫過于DDL操作，因為MySQL在修改表期間會阻塞任何讀寫操作的，基本上你的業務出于癱瘓狀态了。對于龐大的可能曆時好幾個小時才完成，簡直就是個惡夢，沒法容忍的操作。

Percona開發了一系列工具Percona Toolkit包，其中有一工具pt-online-schema-change可以線上執行DDL操作，不會阻塞讀寫操作進而影響業務程式。當然啦，MySQL 5.6也增強了一些線上DDL功能。下面主要是說pt-online-schema-change線上更改表結構。

pt-online-schema-change原理

1、如果存在外鍵，根據alter-foreign-keys-method參數的值，檢測外鍵相關的表，做相應設定的處理。

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

3、建立觸發器，用于記錄從拷貝資料開始之後，對源資料表繼續進行資料修改的操作記錄下來，用于資料拷貝結束後，執行這些操作，保證資料不會丢失。

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

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

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

7、删除觸發器。

安裝percona toolkit

# wget https://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-3.noarch.rpm

# rpm -Uvh percona-release-0.1-3.noarch.rpm 

# yum install percona-toolkit

使用方法

pt-online-schema-change [OPTIONS] DSN

參數清單

--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，不把資訊标準輸出。

測試

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

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

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

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

添加字段

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

删除字段

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

修改字段

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

字段改名

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

增加索引

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

删除索引

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

原文：https://blog.csdn.net/lovelichao12/article/details/73549939