您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

mysql在线修改表结构,如何避免锁表?

时间:2019-10-21 17:45:55  来源:  作者:

一、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-DBI
yum install perl-DBD-MySQL
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL

2.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"的处理方式,但是它不删除原表,并且外键关系会随着重命名转到老表上面。
  • --host=xxx --user=xxx --password=xxx
  • 连接数据库的主机、用户和密码,可以缩写-h xxx -u xxx -p xxx,密码可以使用参数--ask-pass 手动输入。
  • D=db_name,t=table_name
  • 指定要ddl的数据库名和表名
  • --charset
  • 最好设置为MySQL默认字符集: utf8
  • --[no]swap-tables
  • 默认yes。交换原始表和新表,除非你禁止--[no]drop-old-table。
  • --check-interval
  • 默认1秒,检测--max-lag
  • --[no]check-replication-filters
  • 默认值为yes,如果发现任何服务器有 binlog_ignore_db and replicate_do_db , 那么就报错。
  • --check-slave-lag
  • 指定一个从库的DSN连接地址,如果从库超过--max-lag参数设置的值,就会暂停操作。
  • --[no]swap-tables
  • 默认yes。交换原始表和新表,除非你禁止--[no]drop-old-table。
  • --max-lag
  • 默认1s。每个chunk拷贝完成后,会查看所有复制Slave的延迟情况。
  • 要是延迟大于该值,则暂停复制数据,直到所有从的滞后小于这个值。
  • 如果有任何从滞后超过此选项的值,则该工具将睡眠--check-interval指定的时间,再检查。如果从被停止,将会永远等待,直到从开始同步,并且延迟小于该值。
  • 如果指定--check-slave-lag,该工具只检查该服务器的延迟,而不是所有服务器。
  •  
  • --print
  • 打印SQL语句到标准输出。指定此选项可以让你看到该工具所执行的语句,和--dry-run配合最佳。
  • --progress
  • 复制数据的进度报告,二部分组成:第一部分是百分比,第二部分是时间
  • --set-vars
  • 设置mysql变量,多个用逗号分割。默认该工具设置的是: wait_timeout=10000 innodb_lock_wait_timeout=1 lock_wait_timeout=60

三、pt-online-schema-change使用展示

1.参数

./bin/pt-online-schema-change --help 可以查看参数的使用,我们只是要修改个表结构,只需要知道几个简单的参数就可以了

--user= 连接mysql的用户名
--password= 连接mysql的密码
--host= 连接mysql的地址
P=3306 连接mysql的端口号
D= 连接mysql的库名
t= 连接mysql的表名
--alter 修改表结构的语句
--execute 执行修改表结构
--charset=utf8 使用utf8编码,避免中文乱码
--no-version-check 不检查版本,在阿里云服务器中一般加入此参数,否则会报错

2.为避免每次都要输入一堆参数,写个脚本pt.sh

#!/bin/bash
table=$1
alter_conment=$2
​
cnn_host='127.0.0.1'
cnn_user='user'
cnn_pwd='password'
cnn_db='database_name'
​
echo "$table"
echo "$alter_conment"
/root/percona-toolkit-2.2.19/bin/pt-online-schema-change --charset=utf8 --no-version-check --user=${cnn_user} --password=${cnn_pwd} --host=${cnn_host} P=3306,D=${cnn_db},t=$table --alter 
"${alter_conment}" --execute

3.添加表字段

如添加表字段SQL语句为:

ALTER TABLE `tb_test` ADD COLUMN `column1` tinyint(4) DEFAULT NULL;

那么使用pt-online-schema-change则可以这样写

sh pt.sh tb_test "ADD COLUMN column1 tinyint(4) DEFAULT NULL"

4.修改表字段

SQL语句:

ALTER TABLE `tb_test` MODIFY COLUMN `num` int(11) unsigned NOT NULL DEFAULT '0';

pt-online-schema-change工具:

sh pt.sh tb_test "MODIFY COLUMN num int(11) unsigned NOT NULL DEFAULT '0'"

5.修改表字段名

SQL语句:

ALTER TABLE `tb_test` CHANGE COLUMN age adress varchar(30);

pt-online-schema-change工具:

sh pt.sh tb_test "CHANGE COLUMN age address varchar(30)"

6.添加索引

SQL语句:

ALTER TABLE `tb_test` ADD INDEX idx_address(address);

pt-online-schema-change工具:

sh pt.sh tb_test "ADD INDEX idx_address(address)"

四、注意事项

  • 禁止的一些ddl
1. 禁止创建唯一索引,会丢失数据,更加不允许添加 --alter-check=no,--check-unique-key-change=no
2. 如果原表没有主键,或者也没有唯一索引,这些表是不允许用pt做DDL的
3. 禁止对外键的表进行pt ddl
4. 禁止对表进行重命名
5. 禁止对列进行重命名,如果一定要做,也必须先print出来检测清楚列名是否正确
6. 新增字段,NOT NULL必须要指定默认值
7. 不允许删除主键
  • 由于rowcopy会产业很多的binlog,所以做之前要确保binlog空间、数据空间有足够空间可用。
  • 禁止在业务高峰期进行pt-online-schema-change操作
  • 原表不能有触发器
  • MySQL最好设置为innodb_autoinc_lock_mode=2,否则在高并发的写入情况下,很容易产生所等待以及死锁
  • master的表结构必须跟slave的表结构一致,不允许异构,否则pt-online-schema-change的原理就是会rename,然后slave不一致的表结构会被master覆盖,切记!

五、小结

  • pt-online-schema-change工具是在线修改表结构的利器,除了上述参数还有其他参数,不过上述常规参数基本能满足业务需要。
  • 一定要在业务低峰期做,这样才能确保万无一失,切记!


Tags:mysql 表结构   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
一、pt-online-schema-change介绍pt-online-schema-change是percona公司开发的一个工具,在percona-toolkit包里面可以找到这个功能,它可以在线修改表结构。而避免被锁表的情况...【详细内容】
2019-10-21  Tags: mysql 表结构  点击:(248)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(32)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(28)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条