pt-online-schema-change是percona公司开发的一个工具,在percona-toolkit包里面可以找到这个功能,它可以在线修改表结构。而避免被锁表的情况出现。
1.1原理
2.1 pt-online安装
1.去官网下载对应的版本,官网下载地址:https://www.percona.com/downloads/percona-toolkit/LATEST/
2.下载解压之后就可以看到pt-online-schema-change
3.该工具需要一些依赖包,直接执行不成功时一般会有提示,这里可以提前yum安装
yum install perl-DBI yum install perl-DBD-MySQL yum install perl-Time-HiRes yum install perl-IO-Socket-SSL
2.2 常用参数说明
--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以上。
该工具有两种方法,可以自动找到子表,并修改约束关系。 1、auto: 在rebuild_constraints和drop_swap两种处理方式中选择一个。 2、rebuild_constraints:使用 ALTER TABLE语句先删除外键约束,然后再添加.如果子表很大的话,会导致长时间的阻塞。 3、drop_swap: 执行FOREIGN_KEY_CHECKS=0,禁止外键约束,删除原表,再重命名新表。这种方式很快,也不会产生阻塞,但是有风险: (1) 在删除原表和重命名新表的短时间内,表是不存在的,程序会返回错误。 (2) 如果重命名表出现错误,也不能回滚了.因为原表已经被删除。 4、none: 类似"drop_swap"的处理方式,但是它不删除原表,并且外键关系会随着重命名转到老表上面。
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)"
1. 禁止创建唯一索引,会丢失数据,更加不允许添加 --alter-check=no,--check-unique-key-change=no 2. 如果原表没有主键,或者也没有唯一索引,这些表是不允许用pt做DDL的 3. 禁止对外键的表进行pt ddl 4. 禁止对表进行重命名 5. 禁止对列进行重命名,如果一定要做,也必须先print出来检测清楚列名是否正确 6. 新增字段,NOT NULL必须要指定默认值 7. 不允许删除主键