美文网首页
MySQL---在线DDL工具pt-osc

MySQL---在线DDL工具pt-osc

作者: 丶Daniel | 来源:发表于2020-02-22 17:47 被阅读0次

    导读:

    MySQL原生的Online DDL还是有很多限制的,还是会遇到data meta lock的问题等诸多不便,然后就有了我们今天的话题,通过pt-osc在线执行DDL。

    一、pt-osc的工作原理

    1、创建一个和源表一样表结构的新表

    2、在新表执行DDL语句(空表嘛,所以。。。)

    3、在源表创建三个触发器分别对应insert、update、delete操作

    4、从源表拷贝数据到新表,拷贝过程中源表通过触发器把新的DML操作更新到新表中

    5、rename源表到old表中,把新表rename为源表,默认最后删除源表
    二、pt-osc 工具的限制

    1 、源表不能有触发器存在

    显然不是不能有任何触发器,只是不能有针对insert、update、delete的触发器存在,因为一个表上不能有两个相同类型的触发器

    2 、源表必须要有主键
    源表没有主键会报错:

    Cannot chunk the original table houyi.ga: There is no good index and the table is oversized. at ./pt-online-schema-change line 5353.
    3 、源表有外键,必须使用--alter-foreign-keys-method 指定特定的值

    三、pt-osc 与原生MySQL5.6 Online DDL 对比

    l Online DDL在必须copy table时成本较高,不宜采用

    l Pt-osc在存在触发器时,不适用

    l 其他情况使用pt-osc

    l 选择在业务低峰期进行online ddl
    四、pt-osc 常用参数

    --host=xxx --user=xxx --password=xxx
    连接实例信息,缩写-h xxx -u xxx -p xxx,密码可以使用参数--ask-pass 手动输入。

    --alter
    结构变更语句,不需要 ALTER TABLE关键字。与原始ddl一样可以指定多个更改,用逗号分隔。

    D=db_name,t=table_name
    指定要ddl的数据库名和表名

    --dry-run
    创建和修改新表,但不会创建触发器、复制数据、和替换原表。并不真正执行,可以看到生成的执行语句,了解其执行步骤与细节,和--print配合最佳。。

    --execute
    确定修改表,则指定该参数。真正执行alter。–dry-run与–execute必须指定一个,二者相互排斥

    五、pt-osc****使用示例

    1****、添加新列

    [root@bogon ~]# pt-online-schema-change --user=root --password=123456 --host=localhost --alter "add column age int(4) default 0" D=test,t=test01 --print --execute
    No slaves found. See --recursion-method if host bogon has slaves.
    Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
    Operation, tries, wait:
    analyze_table, 10, 1
    copy_rows, 10, 0.25
    create_triggers, 10, 1
    drop_triggers, 10, 1
    swap_tables, 10, 1
    update_foreign_keys, 10, 1
    Altering test.test01...
    Creating new table...
    CREATE TABLE test._test01_new (
    name varchar(3) DEFAULT NULL,
    id varchar(4) NOT NULL,
    PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    Created new table test._test01_new OK.
    Altering new table...
    ALTER TABLE test._test01_new add column age int(4) default 0
    Altered test._test01_new OK.
    2016-11-30T02:54:32 Creating triggers...

    CREATE TRIGGER pt_osc_test_test01_del AFTER DELETE ON test.test01 FOR EACH ROW DELETE IGNORE FROM test._test01_new WHERE test._test01_new.id <=> OLD.id

    CREATE TRIGGER pt_osc_test_test01_upd AFTER UPDATE ON test.test01 FOR EACH ROW REPLACE INTO test._test01_new (name, id) VALUES (NEW.name, NEW.id)

    CREATE TRIGGER pt_osc_test_test01_ins AFTER INSERT ON test.test01 FOR EACH ROW REPLACE INTO test._test01_new (name, id) VALUES (NEW.name, NEW.id)

    2016-11-30T02:54:32 Created triggers OK.
    2016-11-30T02:54:32 Copying approximately 4 rows...

    INSERT LOW_PRIORITY IGNORE INTO test._test01_new (name, id) SELECT name, id FROM test.test01 LOCK IN SHARE MODE /pt-online-schema-change 21439 copy table/

    2016-11-30T02:54:32 Copied rows OK.
    2016-11-30T02:54:32 Analyzing new table...
    2016-11-30T02:54:32 Swapping tables...

    RENAME TABLE test.test01 TO test._test01_old, test._test01_new TO test.test01

    2016-11-30T02:54:32 Swapped original and new tables OK.
    2016-11-30T02:54:32 Dropping old table...

    DROP TABLE IF EXISTS test._test01_old

    2016-11-30T02:54:32 Dropped old table test._test01_old OK.
    2016-11-30T02:54:32 Dropping triggers...

    DROP TRIGGER IF EXISTS test.pt_osc_test_test01_del;
    DROP TRIGGER IF EXISTS test.pt_osc_test_test01_upd;
    DROP TRIGGER IF EXISTS test.pt_osc_test_test01_ins;

    2016-11-30T02:54:32 Dropped triggers OK.
    Successfully altered test.test01.
    2 、修改列类型

    [root@bogon ~]# pt-online-schema-change h=localhost,P=3306,u=root,p=123456,D=test,t=test01 --alter "change women age int(4) default 0" --print --execute --no-check-alter
    3 、添加删除索引

    [root@bogon ~]# pt-online-schema-change h=localhost,P=3306,u=root,D=test,t=test01 --ask-pass --alter "drop key index_name,add key index_age(age)" --print --execute
    4 、修改主键

    使用pt-osc去修改删除主键,务必同时添加原主键为 UNIQUE KEY,否则很有可能导致性能问题

    [root@bogon ~]# pt-online-schema-change h=localhost,u=root,p=123456,D=test,t=test01 --alter "drop primary key,add primary key(age)" --print --execute --no-check-alter
    七、报错案例

    1 、报错语句

    The tool should handle this correctly, but you should test it first because if it fails the renamed columns' data will be lost! Specify --no-check-alter to disable this check and perform the --alter.
    介个直接看着报错就可以解决了

    2 、报错语句

    The table db_name.table_name has triggers. This tool needs to create its own triggers, so the table cannot already have triggers.
    存在触发器,表不能存在触发器

    4 、pt-osc 产生死锁

    当一个事务在做DDL操作,一个事务在做DML操作,有可能会造成死锁

    5 、pt-osc 导致丢表

    当在做DDL的时候,还未提交,此时如果实例crash,就会导致表丢失。

    作者:zwb_jianshu
    链接:https://www.jianshu.com/p/c739d12afbef
    来源:简书
    著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

    相关文章

      网友评论

          本文标题:MySQL---在线DDL工具pt-osc

          本文链接:https://www.haomeiwen.com/subject/fqtrqhtx.html