pt-osc全解pt-online-schema-change

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

工作原理：

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

1、如果存在外鍵，根據alter-foreign-keys-method參數的值，檢測外鍵相關的表，做相應設置的處理。沒有使用 --alter-foreign-keys-method 指定特定的值，該工具不予執行
2、創建一個新的表，表結構為修改后的數據表，用于從源數據表向新表中導入數據。
3、創建觸發器，用于記錄從拷貝數據開始之后，對源數據表繼續進行數據修改的操作記錄下來，用于數據拷貝結束后，執行這些操作，保證數據不會丟失。如果表中已經定義了觸發器這個工具就不能工作了。
4、拷貝數據，從源數據表中拷貝數據到新表中。
5、修改外鍵相關的子表，根據修改后的數據，修改外鍵關聯的子表。
6、rename源數據表為old表，把新表rename為源表名，并將old表刪除。
7、刪除觸發器。

#####關鍵參數介紹，有些參數的使用根據實際來應用，符合自己場景即可。

--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，不把信息標準輸出。

實踐過程：（這里需要注意字段之間的空格，只能有一個空格，多了會出問題哦）

##檢驗是否有問題，具體可以看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

執行效果：

##執行過程,在前面加個time，可以統計最后的執行時間

[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

執行效果：

##使用PT-OSC的時候：

    1、不建議在執行一半的時候突然中止，會導致數據數據異常，因為不會刪除觸發器

    2、只能執行單事務，因為使用的觸發器

    3、若開啟事務，會導致執行的延遲

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