[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三种操作:
