tm 工具 資料庫 SQL MySQL MySQL ddl 的問題現狀 在 運維mySQL資料庫時，我們總會對數據表進行ddl 變更，修改添加欄位或者索引，對於mysql 而已，ddl 顯然是一個令所有MySQL DBA 詬病的一個功能，因為在MySQL中在對錶進行ddl時，會鎖表，當表比較小比如小 ...
| 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 詳細的教程請參看： http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html |
