mysql pt

下面來看一個mysql pt-online-schema-change工具的使用方法,希望這個例子能幫助到各位同學哦。

OSC是DBA比較熟悉的工具之一,也是PT的TOOLKIT裡面最重量級的工具.
在mysql-5.6支持OLDDL以後,大部分人可能覺著這個工具已經沒有意義了,其實在一些特殊環境下,這個工具還是很有用的.
這篇文章除了介紹普通青年如何使用OSC，還會介紹一種文藝青年使用OSC的方法,那就是用來實現master到slave的數據差異恢復。

+++++++++++++++++++++++++++普通青年分割線+++++++++++++++++++++++++++++++++++++++
此段內容轉自：http://www.dataguru.cn/article-3460-1.html
目前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
++++++++++++++++++++++++++++文藝青年分割線+++++++++++++++++++++++++++
所謂的文藝用法,就是通過OSC實現slave和master數據差異時候的恢復.有人說,這個是pt-table-sync該干的事情.但是在表數據差異較大的時候,使用OSC可能效率更好,而且更加簡單可靠.
OSC如何實現master到slave的數據差異恢復的?
由於OSC的原理是新建表和使用觸發器.然後把原表的數據insert into select from的方式導入新表.如果這個時候,我們把binlog改成row格式.那麼insert into記錄的肯定是源表的數據了.觸發器在row格式的時候,也是在日志中記錄的源表數據.也就是說,通過OSC可以邏輯的,無阻塞的把源表的數據同步到所有slave.

pt-online-schema-change –set-vars ‘binlog_format=ROW’ –alter ‘engine=INNODB’ D=test,t=tt1,h=localhost –execute
如果你本來就工作在row格式下,那麼–set-vars ‘binlog_format=ROW’就可以不設置了.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
詳細原理解剖,轉自http://hi.baidu.com/ytjwt/item/7ea9c637c29079f6e6bb7a0d
pt-online-schema-change在線更改表結構的實現核心有如下幾個過程：
（注：在跟改過程中涉及到三個表：原表、tmp_table即作為原表導數據的臨時表，old_table在最後rename 原表的結果表）
1、CREATE TABLE `$db`.`$tmp_tbl` LIKE`$db`.`$tbl`” 新建tmp_table，表結構同原表
2、在tmp_table上更改表結構為需要的表結構
3、在原表上建立三個觸發器，如下：
（1）CREATETRIGGER mk_osc_del AFTER DELETE ON $table ” “FOR EACH ROW ”
“DELETE IGNORE FROM $new_table “”WHERE$new_table.$chunk_column = OLD.$chunk_column”;
（2）CREATETRIGGER mk_osc_ins AFTER INSERT ON $table ” “FOR EACH ROW ”
“REPLACEINTO $new_table ($columns) ” “VALUES($new_values)”;
（3）CREATETRIGGER mk_osc_upd AFTER UPDATE ON $table ” “FOR EACH ROW ”
“REPLACE INTO $new_table ($columns) “”VALUES ($new_values)”;

我們可以看到這三個觸發器分別對應於INSERT、UPDATE、DELETE三種操作：
（1）mk_osc_del，DELETE操作，我們注意到DELETEIGNORE，當新有數據時，我們才進行操作，也就是說，當在後續導入過程中，如果刪除的這個數據還未導入到新表，那麼我們可以不在新表執行操作，因為在以後的導入過程中，原表中改行數據已經被刪除，已經沒有數據，那麼他也就不會導入到新表中；
（2）mk_osc_ins，INSERT操作，所有的INSERT INTO全部轉換為REPLACEINTO，為了確保數據的一致性，當有新數據插入到原表時，如果觸發器還未把原表數據未同步到新表，這條數據已經被導入到新表了，那麼我們就可以利用replaceinto進行覆蓋，這樣數據也是一致的
（3）mk_osc_upd UPDATE操作，所有的UPDATE也轉換為REPLACEINTO，因為當跟新的數據的行還未同步到新表時，新表是不存在這條記錄的，那麼我們就只能插入該條數據，如果已經同步到新表了，那麼也可以進行覆蓋插入，所有數據與原表也是一致的；
我們也能看出上述的精髓也就這這幾條replaceinto操作，正是因為這幾條replaceinto才能保證數據的一致性
4、拷貝原表數據到臨時表中，在腳本中使用如下語句
INSERT IGNORE INTO $to_table ($columns) ” “SELECT $columns FROM $from_table “”WHERE ($chunks->[$chunkno])”，我們能看到他是通過一些查詢（基本為主鍵、唯一鍵值）分批把數據導入到新的表中，在導入前，我們能通過參數–chunk-size對每次導入行數進行控制，已減少對原表的鎖定時間，並且在導入時，我們能通過—sleep參數控制，在每個chunk導入後與下一次chunk導入開始前sleep一會，sleep時間越長，對於磁盤IO的沖擊就越小
5、Rename 原表到old表中，在把臨時表Rename為原表，
“RENAME TABLE `$db`.`$tmp_tbl`TO `$db`.`$tbl`”; 在rename過程，其實我們還是會導致寫入讀取堵塞的，所以從嚴格意思上說，我們的OSC也不是對線上環境沒有一點影響，但由於rename操作只是一個修改名字的過程，也只會修改一些表的信息，基本是瞬間結束，故對線上影響不太大
6、清理以上過程中的不再使用的數據，如OLD表。