Mysql 无锁修改表
本帖最后由 鴻渊 于 2022-8-8 15:00 编辑#### 前言
最近要对一个旧项目进行mysql数据库储存空间进行优化，因发现碎片太多，导致占用了不少磁盘空间没有释放。不过因为项目是线上的目前还在使用，因而不敢乱动，后面偶然发现pst-osc软件可以无锁修改。现在弄完了，来分享下使用过程，初学 有啥写不到位的请见谅。
正常修改表接口也可以导致碎片清空，不过当一些表暂用比较大时，修改表结构不是一个很好的选择，因为修改表结构会触发锁表操作，导致期间表内数据更新/插入异常
```sql
-- 通过 information_schema.tables 表查看当前各表空间碎片清空select table_schema 数据库, table_name 表名, data_free / 1024 / 1024 '碎片(MB)'
from information_schema.tables
where table_schema not in ('information_schema', 'mysql')and data_free > 0;
```
#### 软件
- 使用pt-osc ，全称： pt-online-schema-change
- 找一台能连接到数据库的linux服务器
#### 安装教程
```shell
# 由于yum中没有pt-osc，因而要先下载软件包
wget https://www.percona.com/downloads/percona-toolkit/3.3.1/binary/redhat/7/x86_64/percona-toolkit-3.3.1-1.el7.x86_64.rpm
# 安装相关依赖
yum -y install perl-DBI perl-DBD-MySQL perl-Digest-MD5 perl-IO-Socket-SSL perl-TermReadKey
# 安装pt-osc
yum install -y percona-toolkit-3.3.1-1.el7.x86_64.rpm
# 检查是否安装成功
pt-online-schema-change --help
```
#### 执行流程
1. 自动在数据库中创建一张新表，表明一般以`_`开头，以`new`结尾
1. 在新表中先进行要修改表结构操作
1. 在原表中新增三个触发器，DELETE/UPDATE/INSERT，使得在处理过程中在原表中执行的语句也会在新表中执行
1. 将原表数据复制到新表中，然后通过修改表名，替换掉原表
#### 注意事项
- 要修改的表，索引空间 + 数据空间 < 当前数据库剩余存储空间
- 需确保表存在主键或者唯一索引
- 触发器
- 因整个过程是在线处理的，为了将改表过程中对原始表的更新同时更新到新表中，会创建相应的触发器，每当发生针对针对原表的增删改查操作，就会触发对新表的相应操作。所以原表上不能有其他触发器，因而存在其他触发器时，pt-osc执行会报错。类似`The table `localhost`.`MONITOR_LOG` has triggers. This tool needs to c ...`
```sql
-- pt_osc_localhost_MONITOR_LOG_ins触发器名称
DROP TRIGGER IF EXISTS pt_osc_localhost_MONITOR_LOG_ins;
```
- 外键
- 外键使改表操作变得更复杂，如果原始表上有外键的话，自动rename原表和新表的操作就不能顺利进行，必须要在复制数据之后将外键更新到新表中，可通过`--alter-foreign-keys-method`参数处理。外键改表前后必须持续的链接正确的表，当该工具rename原始表并用新表来取代原始表时，外键必须正确更新到新表上，并且原始表中的外键不再生效
- 如果检测到有replication filter，该工具会拒绝操作。详见--check-replication-filters选项。
- 若主从延迟大于--max-lag选项的值，则会停止执行，默认为1s。仅限于读写分离
- 检测到对服务器造成太多负载，也会停止操作。见--max-load和--critical-load选项。
- 该工具会设置innodb_lock_wait_timeout=1和(对于MySQL 5.5及更新的版本)lock_wait_timeout=60，因此它会更容易成为锁竞争的受害者，并更少破坏其它事务。这些值可以通过指定--set-vars来修改。
- 若Mysql是阿里云RDS，则命令中需加上`--no-version-check`
- 命令中需加上`--charset=utf8`，防止表和字段的 comment 中的中文变成问号
#### eg: 表碎片整理
```shell
# --host/user/password/port mysql连接信息
# D 表示database
# t 表示要修改的数据库表名
# --alter 要修改表结构的命令自动忽略alter table
# --charset 防止ddl中中文变？
pt-online-schema-change --host=127.0.0.1 --user=root --password=123465--port=3306 --no-version-check--alter='engine=innodb' D=db,t=tableName --ask-pass --critical-load='Threads_running=200' --charset=utf8 --execute
``` andyle 发表于 2022-8-8 17:11
感谢分享，在用的东西，随便动不是个好习惯啊
没办法，老板就是要求把磁盘空间降下来，备份下还是可以搞的 虽然我看不懂，但是我感觉是有用的东西，我目前只会简单的查询 生产库，还是要慎之又慎啊！！！ 收藏备用 快进到删库跑路{:1_918:} 感谢楼主分享！ 感谢分享，在用的东西，随便动不是个好习惯啊 我都不敢乱动表 使用主从库可以搞
页:
[1]
2