【MySQL】online ddl 工具之pt-online-schema-change

壹頁書發表於2015-09-17
MySql
MySQL ddl 的問題現狀
在運維mysql資料庫時，我們總會對資料表進行ddl 變更，修改新增欄位或者索引，對於mysql 而已，ddl 顯然是一個令所有MySQL dba 詬病的一個功能，因為在MySQL中在對錶進行ddl時，會鎖表，當表比較小比如小於1w上時，對前端影響較小，當時遇到千萬級別的表 就會影響前端應用對錶的寫操作。
目前InnoDB引擎是透過以下步驟來進行DDL的：
1 按照原始表（original_table）的表結構和DDL語句，新建一個不可見的臨時表（tmp_table）
2 在原表上加write lock，阻塞所有更新操作（insert、delete、update等）
3 執行insert into tmp_table select * from original_table
4 rename original_table和tmp_table，最後drop original_table
5 釋放 write lock。
我們可以看見在InnoDB執行DDL的時候，原表是隻能讀不能寫的。為此 perconal 推出一個工具 pt-online-schema-change ，其特點是修改過程中不會造成讀寫阻塞。
工作原理：
如果表有外來鍵，除非使用 --alter-foreign-keys-method 指定特定的值，否則工具不予執行。
1 建立一個和你要執行 alter 操作的表一樣的空表結構。
2 執行表結構修改，然後從原表中的資料到copy到 表結構修改後的表，
3 在原表上建立觸發器將 copy 資料的過程中，在原表的更新操作 更新到新表.
   注意:如果表中已經定義了觸發器這個工具就不能工作了。
4 copy 完成以後，用rename table 新表代替原表，預設刪除原表。

用法介紹：
pt-online-schema-change [OPTIONS] DSN
options 可以自行檢視 help，DNS 為你要操作的資料庫和表。這裡有兩個引數需要介紹一下：
--dry-run 
 這個引數不建立觸發器，不複製資料，也不會替換原表。只是建立和更改新表。
--execute 
這個引數的作用和前面工作原理的介紹的一樣，會建立觸發器，來保證最新變更的資料會影響至新表。注意：如果不加這個引數，這個工具會在執行一些檢查後退出。

依賴條件
1操作的表必須有主鍵否則 報如下錯誤。
[root@rac1 bin]#  ./pt-online-schema-change -u root -h 10.250.7.50  -p yang --alter='add column vid int ' --execute D=houyi,t=ga      
Cannot connect to D=houyi,h=127.0.0.1,p=...,u=root
Cannot chunk the original table `houyi`.`ga`: There is no good index and the table is oversized. at ./pt-online-schema-change line 5353.

測試例子：
1 新增欄位
[root@rac1 bin]#  ./pt-online-schema-change -u root -h 10.250.7.50  -p yang --alter='add column vid int ' --execute D=houyi,t=ga  
Cannot connect to D=houyi,h=127.0.0.1,p=...,u=root
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 `houyi`.`ga`...
Creating new table...
Created new table houyi._ga_new OK.
Altering new table...
Altered `houyi`.`_ga_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 746279 rows...
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping old table...
Dropped old table `houyi`.`_ga_old` OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `houyi`.`ga`.
2 新增索引
[root@rac1 bin]# ./pt-online-schema-change -u root -h 10.250.7.50  -p yang --alter='add key indx_vid(vid) ' --execute D=houyi,t=ga    
3 刪除欄位         
[root@rac1 bin]# ./pt-online-schema-change -u root -h 10.250.7.50  -p yang --alter='drop  column vid ' --execute D=houyi,t=ga             
詳細的教程請參看：

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1801721/，如需轉載，請註明出處，否則將追究法律責任。

相關文章