Mysql空间清理

原创黄超 2020-08-31

6169

在Mysql环境下，常常由于数据磁盘满而导致Mysql故障。下面整理了如何在Mysql（5.7）环境下做好Mysql的空间清理。

一、日志文件清理

Mysql的日志日积月累，占用的磁盘空间越来越大，磁盘可用空间越来越少，需要自动清理或者手动清理

1.查看文件磁盘占用
1.1 查看磁盘空间占用

# df -lh
Filesystem             Size  Used Avail Use% Mounted on
devtmpfs               1.9G     0  1.9G   0% /dev
tmpfs                  1.9G   17M  1.9G   1% /dev/shm
tmpfs                  1.9G  177M  1.7G  10% /run
tmpfs                  1.9G     0  1.9G   0% /sys/fs/cgroup
/dev/vda1              4.8G  3.8G  833M  83% /
/dev/mapper/sys-lv--2  5.8G  3.0G  2.6G  54% /usr
/dev/vda2              980M   93M  820M  11% /boot
/dev/vdb                99G   41G   53G  44% /opt
/dev/mapper/sys-lv--3  4.8G  2.6G  2.0G  57% /var
/dev/mapper/sys-lv--4  976M  2.0M  907M   1% /root
/dev/mapper/sys-lv--5  7.8G  1.3G  6.1G  18% /tmp
tmpfs                  383M     0  383M   0% /run/user/6351
tmpfs                  383M     0  383M   0% /run/user/7831
tmpfs                  383M     0  383M   0% /run/user/0
复制

1.2 查看目录空间占用

# du -sh /opt
41G     /opt
复制

1.3 查看文件空间占用

# ll -h /mysql/data/binlog/binlog/mysql-bin.000175
-rw------- 1 mysql oinstall 110M Nov  8 09:15 /mysql/data/binlog/binlog/mysql-bin.000175
复制

2.Binlog日志清理
2.1定时自动清理Binlog日志

查看当前日志保存天数：

mysql>show variables like '%expire_logs_days%';
复制

这个默认是0，也就是logs不过期，可通过设置全局的参数，使他临时生效：
　
　mysql>set global expire_logs_days=10;

设置了只保留10天BINLOG, 下次重启mysql这个参数默认会失败，所以需在my.cnf中设置
expire_logs_days = 10
这样在下次重启mysql的时候，expire_logs_days也一样是10；

3.Slow日志清理
3.1Slow日志为TABLE模式
步骤一查看Slow日志模式为TABLE
mysql>show variables like ‘log_output%’;

步骤二 Slow表改名，生成同样表结构的新Slow表，删除原Slow表

mysql>use mysql;
mysql>SET GLOBAL slow_query_log = ‘OFF’;
mysql>ALTER TABLE slow_log RENAME slow_log_drop;
mysql>CREATE TABLE slow_log LIKE slow_log_drop;
mysql>SET GLOBAL slow_query_log = ‘ON’;
mysql>DROP TABLE slow_log_drop;

步骤二查看Slow日志文件位置

步骤三清空Slow日志

# cd /mysql/data/log/slow/
# echo “”>mysql-slow.log
复制

4.Error日志清理
步骤一查看error日志位置

步骤二查看error日志大小

# ll -h /mysql/data/log/error/mysqld.log
-rw------- 1 mysql oinstall 252K Nov 28 09:24 /mysql/data/log/error/mysqld.log
复制

步骤三清空error日志

# echo “”>/mysql/data/log/error/mysqld.log
复制

二、表清理

大表，指单个数据文件磁盘占用大于100G，或者单个表数据记录量大于1亿。可以根据自己具体环境确定。

1.查看表占空间和记录数

mysql>select table_schema,table_name, concat(round((data_length+index_length)/1024/1024/1024,2),‘G’) as tablesize_gb, table_rows from information_schema.tables where table_schema=‘admin’ order by tablesize_gb desc limit 5;
±-------------±----------------------±-------------±-----------+
| table_schema | table_name | tablesize_gb | table_rows |
±-------------±----------------------±-------------±-----------+
| admin | ef_cdr_format_def | 0.15G | 5139 |
| admin | t_bme_taskrunresult | 0.12G | 762584 |
| admin | ef_cdr_field_ext_rule | 0.08G | 1585 |
| admin | t_bme_operationlog | 0.07G | 206496 |
| admin | ef_factor_analysis | 0.06G | 148 |
±-------------±----------------------±-------------±-----------+
5 rows in set (0.90 sec)

table_schema：库名
table_name ：表名
tablesize_gb：表占空间大小，以G为单位
table_rows：行数

2．常规表数据清理
常规表指没达到大表标准的。

Delete
语法：Delete from table_name [ where condition]
Delete 只删除符合条件的数据，不会减少表所占空间。
Delete大量数据后，会存在碎片，需要整理回收碎片空间
optimize table table.name 或者 alter table table.name engine=‘innodb’ (会锁表，注意在业务低谷期执行)

Truncate
语法：Truncate table table_name
Truncate 删除全表数据，回收所占表空间。

Drop
语法：Drop table table_name
Drop 删除全表数据和表结构，回收所占表空间。

3．大表删除
大表 Drop 表的时候，所有Mysql的相关进程都会停止，直到Drop结束，耗时时间很长，若是生产环境，会阻塞业务访问。出现这情况是因为，在drop table的时候，innodb维护了一个全局锁，drop完毕锁才释放。

3.1表改名
new_t_test克隆原t_test的表结构
mysql>create table new_t_test like t_test;
原t_ test改名old_t_ test，新表new_t_ test改名t_ test;
mysql>rename table t_ test to old_t_ test,new_t_ test to t_ test;

3.2建立硬链接
命令：ln 源文件硬链接文件
mysql>system ln /mysql/data/workdbs/admin/t_test.ibd /mysql/data/workdbs/admin/t_test.ibd.hdlk
此时，文件目录如下所示

-rw-r----- 1 mysql oinstall 9023 8 18 05:21 t_test.frm
-rw-r----- 2 mysql oinstall 2356792000512 8 18 05:21 t_test.ibd
-rw-r----- 2 mysql oinstall 2356792000512 8 18 05:21 t_test.ibd.hdlk
你会发现，多了一个t_test.ibd.hdlk文件，且t_test.ibd和t_test.ibd.hdlk的inode均为2。也即当有多个文件名（如硬链接）指向同一innode时，这个innode的引用数大于1，此时，删除其中任何一个文件名都只会删除指向innode的指针而并不会直接删除物理文件块，因此会非常快，直至innode的引用计数等于1时才会真正删除对应的物理文件块，真正删除物理文件块时才会比较耗时。

3.3Drop大表
执行Drop table操作
mysql>drop table t_test;
Query OK, 0 rows affected (0.99 sec)
t_test表已从数据库中删除，但是磁盘空间还没释放，还剩一个文件t_test.ibd.hdlk，且innode的引用计数变为了1。也即刚才的DROP TABLE操作实施删除了物理文件的一个指针t_test.ibd ，因而非常快。

3.4删除物理文件
物理文件较大，删除大文件仍会引起较高的磁盘IO开销。因此可以使用少量逐次删除的方式来删除大的数据文件。truncate工具可以用于增加或缩减指定文件的尺寸，删除脚本如下：
for i in seq 100 -1 1 ;do sleep 2;sudo truncate -s ${i}G /mysql/data/workdbs/admin/t_test.ibd.hdlk;done
rm -rf /mysql/data/workdbs/admin/t_test.ibd.hdlk;
从100G开始，每次缩减1G，停2秒，继续，直到文件只剩1G，最后使用rm命令删除剩余的部分。

对于整个数据库的删除可以先删除其中较大的表，最后再执行DROP DATABASE删除整个库，对大表的删除可参见上面的方法。

注：在生产环境下，注意做好Mysql存储数据的磁盘使用率的监控和报警。

mysql