[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. --phpfensi.com 測試例子:
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='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.
（1）CREATETRIGGER mk_osc_del AFTER DELETE ON $table ” “FOR EACH ROW ” （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三種操作:
