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

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

工作原理：

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

1、如果存在外鍵，根據(jù)alter-foreign-keys-method參數(shù)的值，檢測外鍵相關(guān)的表，做相應(yīng)設(shè)置的處理。沒有使用 --alter-foreign-keys-method 指定特定的值，該工具不予執(zhí)行
2、創(chuàng)建一個新的表，表結(jié)構(gòu)為修改后的數(shù)據(jù)表，用于從源數(shù)據(jù)表向新表中導(dǎo)入數(shù)據(jù)。
3、創(chuàng)建觸發(fā)器，用于記錄從拷貝數(shù)據(jù)開始之后，對源數(shù)據(jù)表繼續(xù)進行數(shù)據(jù)修改的操作記錄下來，用于數(shù)據(jù)拷貝結(jié)束后，執(zhí)行這些操作，保證數(shù)據(jù)不會丟失。如果表中已經(jīng)定義了觸發(fā)器這個工具就不能工作了。
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ù)實際來應(yīng)用，符合自己場景即可。

--user:

-u，連接的用戶名

--password：

-p，連接的密碼

--database：

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

--port

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

--host:

-h，連接的主機地址

--socket:

-S，連接的套接字文件

--ask-pass

隱式輸入連接MySQL的密碼

--charset

指定修改的字符集

--defaults-file

-F，讀取配置文件

--alter：

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

    不能用RENAME來重命名表。        

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

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

    刪除外鍵(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

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

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

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

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

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

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

    2, 如果重命名表出現(xiàn)錯誤,也不能回滾了.因為原表已經(jīng)被刪除。

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

--[no]check-alter

默認yes，語法解析。配合--dry-run 和 --print 一起運行，來檢查是否有問題（change column，drop primary key）。

--max-lag

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

--check-slave-lag

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

--recursion-method

默認是show processlist，發(fā)現(xiàn)從的方法，也可以是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。檢查查詢執(zhí)行計劃的安全性。

--[no]check-replication-filters 

默認yes。如果工具檢測到服務(wù)器選項中有任何復(fù)制相關(guān)的篩選，如指定binlog_ignore_db和replicate_do_db此類。發(fā)現(xiàn)有這樣的篩選，工具會報錯且退出。因為如果更新的表Master上存在，而Slave上不存在，會導(dǎo)致復(fù)制的失敗。使用–no-check-replication-filters選項來禁用該檢查。 

--[no]swap-tables 

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

--[no]drop-triggers 

默認yes，刪除原表上的觸發(fā)器。 --no-drop-triggers 會強制開啟 --no-drop-old-table 即：不刪除觸發(fā)器就會強制不刪除原表。 

--new-table-name 

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

--[no]drop-new-table 

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

--[no]drop-old-table 

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

--max-load 

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

--critical-load 

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

--default-engine 

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

--set-vars 

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

--chunk-size-limit

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

--chunk-time

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

--chunk-size

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

--[no]check-plan

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

--statistics

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

--dry-run

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

--execute

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

--print

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

--progress

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

--quiet

-q，不把信息標(biāo)準(zhǔ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í)行過程,在前面加個time，可以統(tǒng)計最后的執(zhí)行時間

[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的時候：

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

    2、只能執(zhí)行單事務(wù)，因為使用的觸發(fā)器

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

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

    

當(dāng)前文章：pt-osc全解pt-online-schema-change
當(dāng)前網(wǎng)址：http://aaarwkj.com/article40/jeioho.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信公眾號、網(wǎng)頁設(shè)計公司、外貿(mào)網(wǎng)站建設(shè)、定制網(wǎng)站、用戶體驗、營銷型網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)