利用PerconaTookit工具在线修改表结构

在线修改表结构

在线修改表结构必须慎重 ，在业务系统运行的过程中随意修改、删改字段，会造成重大事故，而且在修改之前，必须对表做备份。

常规的做法是业务停机，维护表结构。（比如半夜做系统维护更新，12306、淘宝等…）

但是不影响正常业务的表结构是运行在线修改的。（比如INT不够用换成BIGINT，或者某个字段有唯一性约束，现在去掉唯一性约束，但是也多多少少也会影响性能 ）

ALTER TABLE修改表结构的弊病

这些修改表是属于DDL语句

• 由于修改表结构是表级锁，因此在修改表结构时，影响表写入操作
  • 锁住表禁止一切的读写，如果给1千万数据表中添加一个字段，这个时间会稍微有点长 ，期间不能做任何读写操作，对线上业务影响非常大，数据越多锁表时间越长。
• 如果修改表结构失败，必须还原表结构，所以耗时更长
• 大数据表记录多，修改表结构锁表时间很久

PerconaTookit工具利器

Percona Toolkit简称pt工具—PT-Tools，是Percona公司开发用于管理MySQL的工具，功能包括检查主从复制的数据一致性、检查重复索引、定位IO占用高的表文件、在线DDL等。

• pt-archive ----MySQL的在线归档，无影响生产
• pt-kill -----自定义查杀。确保慢查询及恶性攻击对生产无影响
• pt-online-schema-change ----在线DDL操作，对上亿的大表加索引加字段且对生产无影响
• pt-query-digest -----慢查询Log的分析。
• pt-slave-delay —就是指定从库比主库延迟多长时间
• pt-table-checksum & pt-table-sync-----检查主从是否一致性-----检查主从不一致之后用这个工具进行处理 这两个一搬是搭配使用
• pt-find —找出几天之前建立的表
• pt-slave-restart -----主从报错，跳过报错
• pt-summary —整个系统的的概述
• pt-mysql-summary —MySQL的表述，包括配置文件的描述
• pt-duplicate-key-checker —检查数据库重复索引

今天我们就用 pt-online-schema-change 进行在线修改表结构。

1、安装PerconaTookit依赖包

安装第三方依赖包

yum install -y perl-DBI
yum install -y perl-DBD-mysql
yum install -y perl-IO-Socket-SSL
yum install -y perl-Digest-MD5
yum install -y perl-TermReadKey
1
2
3
4
5

2、安装PerconaTookit工具

在线下载地址： https://www.percona.com/downloads/percona-toolkit/LATEST/

wget https://downloads.percona.com/downloads/percona-toolkit/3.3.1/binary/redhat/7/x86_64/percona-toolkit-3.3.1-1.el7.x86_64.rpm

rpm -ivh *.rpm
1
2
3

3、PT工具在线修改结构些参数

pt-online-schema-change OPTIONS DSN
1

sql举例1： my_shop库中的品牌表custom_address中的name字段varchar(200)改成varchar(20)

pt-online-schema-change --host=192.168.10.101 --port=3306 --user=root --password=123456 --alter "MODIFY `name` VARCHAR(20) NOT NULL COMMENT '名称'" D=my_shop,t=t_brand --print --execute
1

最后一个参数execute是真正修改执行，而--dry-run只是模拟执行的意思。

4、实例操作修改库表

mysql8.0以下无需执行，在执行之前还要执行以下2个sql语句：

ALTER  USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password by '123456'
1
2

为什么要执行呢？

因为在pt工具中连接mysql数据库用的是老的连接方式，mysql8.0引入了新的密码认证方式，PT工具是暂时不支持的，所以只能在数据库端做手脚，把数据库密码验证方式改成旧的认证方式。

再执行：

pt-online-schema-change --host=192.168.10.101 --port=3306 --user=root --password=123456 --alter "MODIFY name VARCHAR（20） NOT NULL COMMENT '收货人'" D=my_shop,t=t_customer_address --print --execute
1

出现Successsfully就代表成功了 。

再看数据库发现修改成功，过程中都没有锁表。

更多PT修改字段操作

#添加字段
--alter "add age varchar(10) NOT NULL DEFAULT '' COMMENT '年龄'" 
#修改默认值
-alter status set DEFAULT '0'  
#删除列
drop age 
1
2
3
4
5
6

反正跟mysql的DDL操作语句一样

在线修改表结构的原理

怎么在不锁表正常修改表结构的呢？比如我现在想修改订单表的结构。

1. PT工具先拷贝订单表结构，创建一个新的数据表。
2. 然后再新表上做结构修改
3. 再之后PT程序会在原来的表上创建触发器，（只要原来表有数据修改、添加、删除）动作就会同步执行到新的订单表
4. 还会把原表的数据拷贝到新表上
5. 这样双管齐下，数据同步后并且原订单没有写入的时候，把原表删除，把新名改回原表。
6. 全称没有锁表，没有耽误数据的读写操作。