mysql大表修改工具: pt-online-schame-change

小韩金服

在表数据量很大的时候直接添加字段，以及其他表结构修改，会严重影响线上使用，而且耗费时间很长；使用这个工具可以很好的在线修改表结构。

好处:

1. 降低主从延时的风险
3. 可以限速、限资源，避免操作时MySQL负载过高

复制代码

建议:

1. 在业务低峰期做，将影响降到最低

复制代码

直接原表修改缺点：

1. 当表的数据量很大的时候，如果直接在线修改表结构，严重影响线上环境，而且耗时不可预估

复制代码

注意：

需要确认表必须包含主键或者唯一索引

工具会创建触发器，所以原表上不能有触发器

有外键的表需要注意使用参数--alter-foreign-keys-method(现在业务上不建议表中外键关联，建议在业务中控制)

原理:

• 首先它会新建一张一模一样的表，表名一般是_new后缀
  
• 然后在这个新表执行更改字段操作
  
• 然后在原表上加三个触发器，DELETE/UPDATE/INSERT，将原表中要执行的语句也在新表中执行
  
• 最后将原表的数据拷贝到新表中，然后替换掉原表
  

1.数据备份

​                        无论操作多么有把握，也要把备份做好(万一很可怕)**
2.安装

下载安装包：

1. wget  https://downloads.percona.com/downloads/percona-toolkit/3.3.1/source/tarball/percona-toolkit-3.3.1.tar.gz

复制代码

解压：

1. tar -xvf percona-toolkit-3.3.1.tar.gz

复制代码

安装一些依赖包：

1. yum install perl-DBIyum install perl-DBD-MySQLyum install perl-Time-HiResyum install perl-IO-Socket-SSLyum -y install perl-Digest-MD5

复制代码

3.测试可用

在解压包的bin目录下执行,看是否正常，查看命令

1. ./pt-online-schema-change --help

复制代码

4.参数字段及含义

参数含义--user=连接用户名--password=连接密码--host=连接IPP=端口--alter=执行表变更的语句D=database 库名t=table 表名--charset=utf8使用utf8编码，避免中文乱码--no-check-alter不检查alter语句--print打印操作日志--execute执行修改表结构，真正的执行alter，–dry-run与–execute必须指定一个，二者相互排斥–dry-run创建和修改新表，但不会创建触发器、复制数据、和替换原表。并不真正执行，与--print配合查看执行细节5.具体操作

• 添加一个字段
  

如果执行失败，检查alter语句，如果确认无误 可以避免检查 --no-check-alter

1. ./pt-online-schema-change --user=xxxx --password=xxxx --host=xxx.xxx.xxx.xxxx --alter "add column group_id bigint(20) not NULL default '0' comment 'test' " P=30306,D=h_pushcenter,t=h_message --charset=utf8 --no-version-check --print --execute

复制代码

• 修改字段
  sql语句：
  

1. ALTER TABLE `h_message` MODIFY COLUMN `group_id` int(20)  NOT NULL DEFAULT '1';ALTER TABLE `h_message` MODIFY COLUMN `group_id` int(20)  NOT NULL DEFAULT '1';

复制代码

pt命令:

1. --alter "MODIFY COLUMN group_id int(20) NOT NULL DEFAULT '1'"

复制代码

• 修改字段名
  

sql语句:

1. ALTER TABLE `h_message` CHANGE column group_id group_id_0 bigint(20);

复制代码

pt命令:

1. --alter "CHANGE group_id group_id_0 bigint(20)"

复制代码

• 添加索引
  

sql语句:

1. ALTER TABLE `h_message` ADD INDEX h_message_n1(group_id);

复制代码

pt命令：

1. --alter "ADD INDEX h_message_n1(group_id)"

复制代码

6.操作日志

• 创建new结尾的新表
  

1. Creating new table...CREATE TABLE `h_pushcenter`.`_h_message_new` .....Created new table h_pushcenter._h_message_new OK.

复制代码

• 新表执行alter操作
  

1. Altering new table...
2. ALTER TABLE `h_pushcenter`.`_h_message_new` add column  group_id bigint(20) not NULL default '0'  comment 'test'
3. Altered `h_pushcenter`.`_h_message_new` OK.

复制代码

• 原表上创建3个触发器
  

1. Creating triggers...
2. Event : DELETE
3. Event : UPDATE
4. Event : INSERTCreated triggers OK.

复制代码

• 复制数据到新表
  

1. Copying approximately 8187 rows...
2. Copied rows OK.

复制代码

• 重命名新旧两个表，然后替换，删除旧表
  

1. 2021-05-19T10:33:08 Swapping tables...RENAME TABLE `h_pushcenter`.`h_message` TO `h_pushcenter`.`_h_message_old`, `h_pushcenter`.`_h_message_new` TO `h_pushcenter`.`h_message`2021-05-19T10:33:09 Swapped original and new tables OK.2021-05-19T10:33:09 Dropping old table...DROP TABLE IF EXISTS `h_pushcenter`.`_h_message_old`2021-05-19T10:33:09 Dropped old table `h_pushcenter`.`_h_message_old` OK.

复制代码

• 删除触发器
  

1. 2021-05-19T10:33:09 Dropping triggers...DROP TRIGGER IF EXISTS `h_pushcenter`.`pt_osc_h_pushcenter_h_message_del`DROP TRIGGER IF EXISTS `h_pushcenter`.`pt_osc_h_pushcenter_h_message_upd`DROP TRIGGER IF EXISTS `h_pushcenter`.`pt_osc_h_pushcenter_h_message_ins`2021-05-19T10:33:09 Dropped triggers OK.

复制代码

• 完成
  

传送门：https://raokun.top/raokun/archives/mysql-da-biao-xiu-gai-gong-ju-pt-online-schame-change
链接：
mysql好用工具: pt-online-schame-change

来源:https://www.cnblogs.com/raok/p/17037226.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！