江苏龙网

  1. 主页 > 生活百科 > >

mysql在线修改表结构，如何避免锁表？

mysql表结构

一、pt-online-schema-change介绍pt-online-schema-change是percona公司开发的一个工具 ， 在percona-toolkit包里面可以找到这个功能 ， 它可以在线修改表结构 。而避免被锁表的情况出现 。

mysql在线修改表结构，如何避免锁表？

文章插图
 
1.1原理
  • step1: 它会新建一张一模一样的表 ， 表名一般是_new后缀
  • step2: 在这个新表执行更改字段操作
  • step3: 在原表上加三个触发器 ， DELETE/UPDATE/INSERT ， 将原表中要执行的语句也在新表中执行
  • step4: 最后将原表的数据拷贝到新表中 ， 然后替换掉原表
二、pt-online-schema-change安装2.1 pt-online安装
1.去官网下载对应的版本 ， 官网下载地址:https://www.percona.com/downloads/percona-toolkit/LATEST/
2.下载解压之后就可以看到pt-online-schema-change
mysql在线修改表结构，如何避免锁表？

文章插图
 
3.该工具需要一些依赖包 ， 直接执行不成功时一般会有提示 ， 这里可以提前yum安装
yum install perl-DBIyum install perl-DBD-MySQLyum install perl-Time-HiResyum install perl-IO-Socket-SSL2.2 常用参数说明
  • --dry-run ：打印输出
  • --execute：执行
  • --alter:通过此选项 ， 不需要alter table关键字了 。可以通过逗号指定多个修改操作 。
--alter使用一些限制:?1、 原来必须有主键或唯一键 ， 因为delete触发器需要用到 。否则会报错 。2、 rename子句 ， 不允许给表重命令3、 不能通过删除一列 ， 然后再新增一列的方式来完成对列的重命名操作 。4、 新增字段如果是not null ， 必须指定default值 ， 否则报错 。5、 如果是DROP FOREIGN KEY constraint_name  ，  那么必须指定 _ 加上 constraint_name  ，  而不是 constraint_name 。举例： CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`) 你必须指定： --alter "DROP FOREIGN KEY _fk_foo" 而不是 --alter "DROP FOREIGN KEY fk_foo".6、 确保数据库版本在5.0以上 。
  • --alter-foreign-keys-method
该工具有两种方法,可以自动找到子表,并修改约束关系 。1、auto： 在rebuild_constraints和drop_swap两种处理方式中选择一个 。2、rebuild_constraints：使用 ALTER TABLE语句先删除外键约束,然后再添加.如果子表很大的话,会导致长时间的阻塞 。3、drop_swap： 执行FOREIGN_KEY_CHECKS=0,禁止外键约束,删除原表,再重命名新表 。这种方式很快,也不会产生阻塞,但是有风险： （1） 在删除原表和重命名新表的短时间内,表是不存在的,程序会返回错误 。（2） 如果重命名表出现错误,也不能回滚了.因为原表已经被删除 。4、none： 类似"drop_swap"的处理方式,但是它不删除原表,并且外键关系会随着重命名转到老表上面 。

上一篇：香薰机可以放热水吗 挂烫机可以加香薰吗

下一篇：从淘宝MySQL数据库经典案例来看innodb如何设计主键索引