在线修改大表结构之pt-online-schema-change (alter-foreign-keys-method)

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/hpulfc/article/details/87938724

安装

sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo yum install percona-toolkit

查看触发器:

方式一:

use information_schema;
select * from TRIGGERS;

方式二:

use database_name;
show triggers;

删除触发器:

drop trigger trigger_name

有触发器pt-online-schema-change将不工作
会出现如下提示:The table fr.xaxa has triggers. This tool needs to create its own triggers, so the table cannot already have triggers.

基本原理:

  1. 使用用户密码登录
  2. 然后检查表的创建语句show create table table_name进行临时表的创建,
  3. 根据l--alter参数对临时表进行结构更改
  4. 创建触发器,在更新、删除、插入的时候对临时表的修改
  5. 从旧表向临时表中拷贝数据
  6. 修改旧表名字, 修改临时表名字
  7. 删除旧表,删除触发器

使用实例:为表加唯一索引, 添加列, 删除列

time pt-online-schema-change --user=kratos --password=3vUbY52IJ2fJq7KwWPeItNrz8 --host=127.0.0.1 --port=3306  --alter="add unique index tel_name(tel, name)" D=test,t=tb_user --nocheck-replication-filters --nocheck-unique-key-change --max-lag=5  --check-interval=2 --alter-foreign-keys-method=auto --execute --print
# 添加列 --alter="add column address varchar(50) not null default ''"
# 删除列 --alter="drop column address"

不同的操作就是改一下--alter里面的内容

问题:

主从复制 --max-lag=5 --check-interval=2 (设定延迟) 这里使用默认也可以
外键约束 --alter-foreign-keys-method=auto
auto 表示在下面两种方式中进行选择

  • 如果使用 drop_swrap 的时候,会先删除旧表 然后将 新表重命名为原表名
    (原因是在rename的时候原来的外键约束名称也会更改,这里不通过名称交换再删除就是为了防止原来的约束名称改变)

  • 如果使用 rebuild_constraints 的方式,则是通过交换表名的方式进行的,只不过是再重新构建了外键约束,通过改表名,外键约束对应的表名也被更改。
    需要注意的是: 如果行不存在重复数据的情况下的时候是没什么问题的,如果建立唯一所以导致重复数据删除,在重建外键约束的时候就会报错*

  • 另外,对于特别大的表进行删除的时候会消耗一定的时间,drop_swap的方式是先删原表则会造成在 查询的时候 原表不存在而报错,虽然官方文档说删表速度很快,
    但是网上说IO很慢,导致删表变慢, 这里需要对官方说法进行验证,以确保在删表上pt做了优化(这个括号里面的是在验证后的内容,在2千万的带有外键约束的表,删除的时候是很快的)**

  • 一种做法是在删除的时候给数据文件加上另外一个硬链接,这样在删除表的时候,就只是删除硬链接的时间,很快,然后手动删除真实数据文件即可!
    然而,如果表使用的是共享空间,而不是单个表的空间 就… ! 使用SHOW VARIABLES LIKE 'innodb_file_per_table'; 查看*

另外在插入删除的时候,因为触发器的存在,不会造成影响。
磁盘空间(1倍)
–no-drop-old-table 不删除原来的表

性能
–critical-load=“Threads_running=200”

特别参数
–nocheck-unique-key-change
–nocheck-replication-filters

测试过程
-–dry-run --print

使用
–execute

权威 文档

查看主从状态

SHOW SLAVE STATUS \G
SHOW status;

扩展:类似工具gh-ost

展开阅读全文

没有更多推荐了,返回首页