MySQL 大字段的DDL操作：加減字段、索引、修改字段屬性等，在5.1之前都是非常耗時(shí)耗力的，特別是會(huì)對(duì)MySQL服務(wù)產(chǎn)生影響。在5.1之后隨著Plugin Innodb的出現(xiàn)在線加索引的提高了很多，但是還會(huì)影響（時(shí)間縮短了），主要是出現(xiàn)了MDL鎖。不過(guò)5.6可以避免上面的情況，但目前大部分在用的版本都是5.6之前的。

創(chuàng)新互聯(lián)建站是一家集網(wǎng)站建設(shè),青島企業(yè)網(wǎng)站建設(shè),青島品牌網(wǎng)站建設(shè),網(wǎng)站定制,青島網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營(yíng)銷,網(wǎng)絡(luò)優(yōu)化,青島網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶成長(zhǎng)自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。

工作原理：

      模仿MySQL的alter，但不同的是在alter操作更改表結(jié)構(gòu)的時(shí)候不用鎖定表，也就是說(shuō)執(zhí)行alter的時(shí)候不會(huì)阻塞寫(xiě)和讀取操作，客戶端可以繼續(xù)都和修改數(shù)據(jù)。注意執(zhí)行這個(gè)工具的時(shí)候必須做好備份，操作之前最好詳細(xì)讀一下官方文檔。

1、如果存在外鍵，根據(jù)alter-foreign-keys-method參數(shù)的值，檢測(cè)外鍵相關(guān)的表，做相應(yīng)設(shè)置的處理。沒(méi)有使用 --alter-foreign-keys-method 指定特定的值，該工具不予執(zhí)行
2、創(chuàng)建一個(gè)新的表，表結(jié)構(gòu)為修改后的數(shù)據(jù)表，用于從源數(shù)據(jù)表向新表中導(dǎo)入數(shù)據(jù)。
3、創(chuàng)建觸發(fā)器，用于記錄從拷貝數(shù)據(jù)開(kāi)始之后，對(duì)源數(shù)據(jù)表繼續(xù)進(jìn)行數(shù)據(jù)修改的操作記錄下來(lái)，用于數(shù)據(jù)拷貝結(jié)束后，執(zhí)行這些操作，保證數(shù)據(jù)不會(huì)丟失。如果表中已經(jīng)定義了觸發(fā)器這個(gè)工具就不能工作了。
4、拷貝數(shù)據(jù)，從源數(shù)據(jù)表中拷貝數(shù)據(jù)到新表中。
5、修改外鍵相關(guān)的子表，根據(jù)修改后的數(shù)據(jù)，修改外鍵關(guān)聯(lián)的子表。
6、rename源數(shù)據(jù)表為old表，把新表rename為源表名，并將old表刪除。
7、刪除觸發(fā)器。

#####關(guān)鍵參數(shù)介紹，有些參數(shù)的使用根據(jù)實(shí)際來(lái)應(yīng)用，符合自己場(chǎng)景即可。

--user:

-u，連接的用戶名

--password：

-p，連接的密碼

--database：

-D，連接的數(shù)據(jù)庫(kù)

--port

-P，連接數(shù)據(jù)庫(kù)的端口

--host:

-h，連接的主機(jī)地址

--socket:

-S，連接的套接字文件

--ask-pass

隱式輸入連接MySQL的密碼

--charset

指定修改的字符集

--defaults-file

-F，讀取配置文件

--alter：

結(jié)構(gòu)變更語(yǔ)句，不需要alter table關(guān)鍵字?？梢灾付ǘ鄠€(gè)更改，用逗號(hào)分隔。如下場(chǎng)景，需要注意：

    不能用RENAME來(lái)重命名表。        

    列不能通過(guò)先刪除，再添加的方式進(jìn)行重命名，不會(huì)將數(shù)據(jù)拷貝到新列。

    如果加入的列非空而且沒(méi)有默認(rèn)值，則工具會(huì)失敗。即其不會(huì)為你設(shè)置一個(gè)默認(rèn)值，必須顯示指定。

    刪除外鍵(drop foreign key constrain_name)時(shí)，需要指定名稱_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

如何把外鍵引用到新表?需要特殊處理帶有外鍵約束的表,以保證它們可以應(yīng)用到新表.當(dāng)重命名表的時(shí)候,外鍵關(guān)系會(huì)帶到重命名后的表上。

該工具有兩種方法,可以自動(dòng)找到子表,并修改約束關(guān)系。

    auto： 在rebuild_constraints和drop_swap兩種處理方式中選擇一個(gè)。

    rebuild_constraints：使用 ALTER TABLE語(yǔ)句先刪除外鍵約束,然后再添加.如果子表很大的話,會(huì)導(dǎo)致長(zhǎng)時(shí)間的阻塞。

    drop_swap： 執(zhí)行FOREIGN_KEY_CHECKS=0,禁止外鍵約束,刪除原表,再重命名新表。這種方式很快,也不會(huì)產(chǎn)生阻塞,但是有風(fēng)險(xiǎn)：

    1, 在刪除原表和重命名新表的短時(shí)間內(nèi),表是不存在的,程序會(huì)返回錯(cuò)誤。

    2, 如果重命名表出現(xiàn)錯(cuò)誤,也不能回滾了.因?yàn)樵硪呀?jīng)被刪除。

    none： 類似"drop_swap"的處理方式,但是它不刪除原表,并且外鍵關(guān)系會(huì)隨著重命名轉(zhuǎn)到老表上面。

--[no]check-alter

默認(rèn)yes，語(yǔ)法解析。配合--dry-run 和 --print 一起運(yùn)行，來(lái)檢查是否有問(wèn)題（change column，drop primary key）。

--max-lag

默認(rèn)1s。每個(gè)chunk拷貝完成后，會(huì)查看所有復(fù)制Slave的延遲情況。要是延遲大于該值，則暫停復(fù)制數(shù)據(jù)，直到所有從的滯后小于這個(gè)值，使用Seconds_Behind_Master。如果有任何從滯后超過(guò)此選項(xiàng)的值，則該工具將睡眠--check-interval指定的時(shí)間，再檢查。如果從被停止，將會(huì)永遠(yuǎn)等待，直到從開(kāi)始同步，并且延遲小于該值。如果指定--check-slave-lag，該工具只檢查該服務(wù)器的延遲，而不是所有服務(wù)器。

--check-slave-lag

指定一個(gè)從庫(kù)的DSN連接地址,如果從庫(kù)超過(guò)--max-lag參數(shù)設(shè)置的值,就會(huì)暫停操作。

--recursion-method

默認(rèn)是show processlist，發(fā)現(xiàn)從的方法，也可以是host，但需要在從上指定report_host，通過(guò)show slave hosts來(lái)找到，可以指定none來(lái)不檢查Slave。

METHOD       USES

===========  ==================

processlist  SHOW PROCESSLIST

hosts        SHOW SLAVE HOSTS

dsn=DSN      DSNs from a table

none         Do not find slaves

指定none則表示不在乎從的延遲。

--check-interval 

默認(rèn)是1。--max-lag檢查的睡眠時(shí)間。 

--[no]check-plan 

默認(rèn)yes。檢查查詢執(zhí)行計(jì)劃的安全性。

--[no]check-replication-filters 

默認(rèn)yes。如果工具檢測(cè)到服務(wù)器選項(xiàng)中有任何復(fù)制相關(guān)的篩選，如指定binlog_ignore_db和replicate_do_db此類。發(fā)現(xiàn)有這樣的篩選，工具會(huì)報(bào)錯(cuò)且退出。因?yàn)槿绻碌谋鞰aster上存在，而Slave上不存在，會(huì)導(dǎo)致復(fù)制的失敗。使用–no-check-replication-filters選項(xiàng)來(lái)禁用該檢查。 

--[no]swap-tables 

默認(rèn)yes。交換原始表和新表，除非你禁止--[no]drop-old-table。 

--[no]drop-triggers 

默認(rèn)yes，刪除原表上的觸發(fā)器。 --no-drop-triggers 會(huì)強(qiáng)制開(kāi)啟 --no-drop-old-table 即：不刪除觸發(fā)器就會(huì)強(qiáng)制不刪除原表。 

--new-table-name 

復(fù)制創(chuàng)建新表的名稱，默認(rèn)%T_new。 

--[no]drop-new-table 

默認(rèn)yes。刪除新表，如果復(fù)制組織表失敗。 

--[no]drop-old-table 

默認(rèn)yes。復(fù)制數(shù)據(jù)完成重命名之后，刪除原表。如果有錯(cuò)誤則會(huì)保留原表。 

--max-load 

默認(rèn)為T(mén)hreads_running=25。每個(gè)chunk拷貝完后，會(huì)檢查SHOW GLOBAL STATUS的內(nèi)容，檢查指標(biāo)是否超過(guò)了指定的閾值。如果超過(guò)，則先暫停。這里可以用逗號(hào)分隔，指定多個(gè)條件，每個(gè)條件格式： status指標(biāo)=MAX_VALUE或者status指標(biāo):MAX_VALUE。如果不指定MAX_VALUE，那么工具會(huì)這只其為當(dāng)前值的120%。 

--critical-load 

默認(rèn)為T(mén)hreads_running=50。用法基本與--max-load類似，如果不指定MAX_VALUE，那么工具會(huì)這只其為當(dāng)前值的200%。如果超過(guò)指定值，則工具直接退出，而不是暫停。 

--default-engine 

默認(rèn)情況下，新的表與原始表是相同的存儲(chǔ)引擎，所以如果原來(lái)的表使用InnoDB的，那么新表將使用InnoDB的。在涉及復(fù)制某些情況下，很可能主從的存儲(chǔ)引擎不一樣。使用該選項(xiàng)會(huì)默認(rèn)使用默認(rèn)的存儲(chǔ)引擎。 

--set-vars 

設(shè)置MySQL變量，多個(gè)用逗號(hào)分割。默認(rèn)該工具設(shè)置的是： wait_timeout=10000 innodb_lock_wait_timeout=1 lock_wait_timeout=60 

--chunk-size-limit

當(dāng)需要復(fù)制的塊遠(yuǎn)大于設(shè)置的chunk-size大小,就不復(fù)制.默認(rèn)值是4.0，一個(gè)沒(méi)有主鍵或唯一索引的表,塊大小就是不確定的。

--chunk-time

在chunk-time執(zhí)行的時(shí)間內(nèi),動(dòng)態(tài)調(diào)整chunk-size的大小,以適應(yīng)服務(wù)器性能的變化，該參數(shù)設(shè)置為0,或者指定chunk-size,都可以禁止動(dòng)態(tài)調(diào)整。

--chunk-size

指定塊的大小,默認(rèn)是1000行,可以添加k,M,G后綴.這個(gè)塊的大小要盡量與--chunk-time匹配，如果明確指定這個(gè)選項(xiàng),那么每個(gè)塊就會(huì)指定行數(shù)的大小.

--[no]check-plan

默認(rèn)yes。為了安全,檢查查詢的執(zhí)行計(jì)劃.默認(rèn)情況下,這個(gè)工具在執(zhí)行查詢之前會(huì)先EXPLAIN,以獲取一次少量的數(shù)據(jù),如果是不好的EXPLAIN,那么會(huì)獲取一次大量的數(shù)據(jù)，這個(gè)工具會(huì)多次執(zhí)行EXPALIN,如果EXPLAIN不同的結(jié)果,那么就會(huì)認(rèn)為這個(gè)查詢是不安全的。

--statistics

打印出內(nèi)部事件的數(shù)目，可以看到復(fù)制數(shù)據(jù)插入的數(shù)目。

--dry-run

創(chuàng)建和修改新表，但不會(huì)創(chuàng)建觸發(fā)器、復(fù)制數(shù)據(jù)、和替換原表。并不真正執(zhí)行，可以看到生成的執(zhí)行語(yǔ)句，了解其執(zhí)行步驟與細(xì)節(jié)。--dry-run與--execute必須指定一個(gè)，二者相互排斥。和--print配合最佳。

--execute

確定修改表，則指定該參數(shù)。真正執(zhí)行。--dry-run與--execute必須指定一個(gè)，二者相互排斥。

--print

打印SQL語(yǔ)句到標(biāo)準(zhǔn)輸出。指定此選項(xiàng)可以讓你看到該工具所執(zhí)行的語(yǔ)句，和--dry-run配合最佳。

--progress

復(fù)制數(shù)據(jù)的時(shí)候打印進(jìn)度報(bào)告，二部分組成：第一部分是百分比，第二部分是時(shí)間。

--quiet

-q，不把信息標(biāo)準(zhǔn)輸出。

實(shí)踐過(guò)程：（這里需要注意字段之間的空格，只能有一個(gè)空格，多了會(huì)出問(wèn)題哦）

##檢驗(yàn)是否有問(wèn)題，具體可以看dry-run的解釋

[root@mysql-server01 dbbackup]# pt-online-schema-change -u dlan -h 172.16.10.60 -p root123 -P 3306 --alter="add column route_data_storage_mode1 tinyint(3)UNSIGNED DEFAULT 0 after travel_route" D=log,t=bike_use_record_detail --charset=utf8 --no-version-check --no-drop-old-table --statistics --critical-load="Threads_running:200" --max-load="Threads_running=25" --print --dry-run

執(zhí)行效果：

##執(zhí)行過(guò)程,在前面加個(gè)time，可以統(tǒng)計(jì)最后的執(zhí)行時(shí)間

[root@mysql-server01 dbbackup]#time pt-online-schema-change -u dlan -h 172.16.10.60 -p root123 -P 3306 --alter="add column route_data_storage_mode1 tinyint(3)UNSIGNED DEFAULT 0 after travel_route" D=log,t=bike_use_record_detail --charset=utf8 --no-version-check --no-drop-old-table --statistics --critical-load="Threads_running:200" --max-load="Threads_running=25" --print --execute

執(zhí)行效果：

##使用PT-OSC的時(shí)候：

    1、不建議在執(zhí)行一半的時(shí)候突然中止，會(huì)導(dǎo)致數(shù)據(jù)數(shù)據(jù)異常，因?yàn)椴粫?huì)刪除觸發(fā)器

    2、只能執(zhí)行單事務(wù)，因?yàn)槭褂玫挠|發(fā)器

    3、若開(kāi)啟事務(wù)，會(huì)導(dǎo)致執(zhí)行的延遲

    4、在執(zhí)行到99%的時(shí)候非常慢，可以通過(guò)show full processlist 查看數(shù)據(jù)加載到哪,詳細(xì)信息里有id值