本帖最后由 鴻渊 于 2022-8-8 15:00 编辑
前言
最近要对一个旧项目进行mysql数据库储存空间进行优化，因发现碎片太多，导致占用了不少磁盘空间没有释放。不过因为项目是线上的目前还在使用，因而不敢乱动，后面偶然发现pst-osc软件可以无锁修改。现在弄完了，来分享下使用过程，初学 有啥写不到位的请见谅。
正常修改表接口也可以导致碎片清空，不过当一些表暂用比较大时，修改表结构不是一个很好的选择，因为修改表结构会触发锁表操作，导致期间表内数据更新/插入异常
-- 通过 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;
软件
安装相关依赖
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，该工具会拒绝操作。详见--[no]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: 表碎片整理
# --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
| 
发表于 2022-8-8 15:00
|
发表于 2022-8-8 17:16
