centos mysql 磁盘 mysql磁盘空间不足

删库跑路必备目录

• 前言
• 导致空间不足情况及优化方案

• 索引太多
• 大字段导致空间不足
• 空闲表空间太多导致空间不足
• 临时表空间过大导致空间不足
• 日志过大（Binlog日志、Slow日志、Error日志）
• 大表清理
• 常规表数据清理

• 扩展

• 报错处理
• MySQL删除数据的方式
• OPTIMIZE TABLE

前言

在数据库运维过程中会遇到磁盘空间占用过大情况，这个时候就需要对空间进行扩容或者优化磁盘空间了。

导致空间不足情况及优化方案

索引太多

通常表上除了主键索引，还存在二级索引，二级索引越多，整个表空间就越大。
优化数据结构，减少二级索引的数量。

大字段导致空间不足

如果表结构定义中有blob、text等大字段或很长的varchar字段，也会占用更大的表空间。
压缩数据后再插入。

空闲表空间太多导致空间不足

空闲表空间太多是指InnoDB表的碎片率高。InnoDB是按页（Page）管理表空间的，如果Page写满记录，然后部分记录又被删除，后续这些删除的记录位置又没有新的记录插入，就会产生很多空闲空间。
这些空间空间会占据原来数据的空间，所以文件的大小没有改变。这些空间在以后插入数据的时候可能会被再度利用起来，当然也有可能一直存在。这空间不仅额外增加了存储代价，同时也因为数据碎片化降低了表的扫描效率。

可以通过命令show table status like ‘<表名>’；

Index_length 代表索引的数量 Data_free 代表碎片数量

查看表上空闲的空间，如果空闲空间过多，可以执行命令optimize table ‘<表名>’ ；整理表空间。

InnoDB类型的表是无法使用optimize table命令的。强行使用会返回如下结果：

MySQL5.7已经推荐对于InnoDB的table使用

alter table table_name engine=innodb;

的方式来进行表碎片优化。可以同样达到释放空洞的效果。这是由于在转换数据引擎（即便没有真正转换）的时候，mysql会将表中的数据读取出来，再重新写入，在这个过程中，空洞就自然没有了。
在对数据量小的表操作时，optimze table是挺快的，但是对一张有海量数据的表进行optimze table操作时，不但执行时间会很长，而且会锁表。这个时候就应该考虑使用一些运维手段避免现网的服务受到影响。

临时表空间过大导致空间不足

半连接(Semi-join)、去重(distinct)、不走索引的排序等操作，会创建临时表，如果涉及的数据量过多，可能导致临时表空间过大。
DDL操作重建表空间时，如果表特别大，创建索引排序时产生的临时文件也会特别大。RDS MySQL 5.6和5.7不支持即时增加字段，很多DDL是通过创建新表实现的，DDL执行结束再删除旧表，DDL过程中会同时存在两份表。

可以查看执行计划，确认是否包含Using Temporary 。 大表DDL需要注意实例的空间是否足够，不足的话请提前扩容磁盘。

日志过大（Binlog日志、Slow日志、Error日志）

定时自动清理Binlog日志

mysql>show variables like ‘%expire_logs_days%’; --mysql 5.7
mysql> show variables like ‘%binlog_expire_logs_seconds%’ --mysql8.0
mysql8.0 mysql 8开始 expire_logs_days 废弃
启用binlog_expire_logs_seconds设置binlog自动清除日志时间 保存时间 以秒为单位；默认2592000 30天
14400 4小时；86400 1天；259200 3天；
mysql> set global binlog_expire_logs_seconds=86400; mysql5.7
这个默认是0，也就是logs不过期，可通过设置全局的参数，使他临时生效： mysql>set global
expire_logs_days=10;

手动删除Binlog日志

第一步：登陆进入mysql，并使用 show binary logs; 查看日志文件。 mysql>show binary logs;
第二步：查看正在使用的日志文件：show master status; mysql>show master status;
当前正在使用的日志文件是mysqlhost01-bin.000010，那么删除日志文件的时候应该排除掉该文件。
删除日志文件的命令：purge binary logs to ‘mysqlhost01-bin.000010’;
mysql>purge binary logs to ‘mysqlhost01-bin.000010’;
删除除mysqlhost01-bin.000010以外的日志文件 也可以指定其他文件名，例如mysql-bin.000003。
删除后就能释放大部分空间。

Slow日志清理

步骤一 查看slow日志模式
mysql>show variables like ‘log_output%’;
步骤二查看Slow日志文件位置
show variables like ‘%slow%’;
步骤三 清空Slow日志
[root@mysqlhost01 /]# cd /usr/local/mysql57/mysql5730/data
[root@mysqlhost01 data]# echo “”>mysqlhost01-slow.log

Error日志清理

步骤一 查看error日志位置
mysql>show variables like ‘log_error’;
步骤二 查看error日志大小
[root@mysqlhost01 data]# ll -h log.err
步骤三 清空error日志
echo “”>/usr/local/mysql57/mysql5730/data/log.err

大表清理

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
order by tablesize_gb desc limit 5;

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

常规表数据清理

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 删除全表数据和表结构，回收所占表空间。

扩展

报错处理

未开启二进制文件会报错

show binary logs
1381 - You are not using binary logging

查看二进制开启状态

show variables like ‘log_bin’

打开mysql 的配置文件my.ini
在mysqld配置项下面加

[mysqld]
#开启
log_bin=mysql-bin
#唯一id
server_id=11234
#二进制日志保存时间
expire_logs_days=7
binlog_format=ROW

二进制日志 Binary Log

它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。一般来说开启二进制日志大概会有一定的性能损耗。

二进制日志有两个最重要的使用场景:

（1）Mysql主从复制模式下，Master（主机）把它的二进制日志传递给Slaves（从机）来达到master-slave数据一致的目的。
（2）数据恢复，如果出现数据误删通过使用mysqlbinlog工具进行数据恢复。

二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

MySQL删除数据的方式

常用方式通过 delete、truncate、drop 关键字进行删除

从执行速度上来说

drop > truncate >> delete

DELETE

DELETE属于数据库DML操作语言，只删除数据不删除表的结构，会走事务，执行时会触发trigger；
DELETE from TABLE_NAME

在 InnoDB 中，DELETE其实并不会真的把数据删除，mysql 实际上只是给删除的数据打了个标记为已删除，因此 delete
删除表中的数据时，表文件在磁盘上所占空间不会变小，存储空间不会被释放，只是把删除的数据行设置为不可见。虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以重用这部分空间（重用→ 覆盖）。

DELETE执行时，会先将所删除数据缓存到rollback segement中，事务commit之后生效;

delete from table_name删除表的全部数据,对于MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间;

对于delete from table_name where xxx 带条件的删除, 不管是InnoDB还是MyISAM都不会释放磁盘空间;
delete 操作是一行一行执行删除的，并且同时将该行的的删除操作日志记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，生成的大量日志也会占用磁盘空间。

TRUNCATE

属于数据库DDL定义语言，不走事务，原数据不放到 rollback segment 中，操作不触发 trigger。 Truncate
table TABLE_NAME

执行后立即生效，无法找回

truncate table table_name 立刻释放磁盘空间 ，不管是 InnoDB和MyISAM 。truncate table其实有点类似于drop table 然后creat,只不过这个create table的过程做了优化，比如表结构文件之前已经有了等等。所以速度上应该是接近drop table的速度;
truncate能够快速清空一个表。并且重置auto_increment的值。

但对于不同的类型存储引擎需要注意的地方是：
对于MyISAM，truncate会重置auto_increment（自增序列）的值为1。而delete后表仍然保持auto_increment。对于InnoDB，truncate会重置auto_increment的值为1。delete后表仍然保持auto_increment。但是在做delete整个表之后重启MySQL的话，则重启后的auto_increment会被置为1。也就是说，InnoDB的表本身是无法持久保存auto_increment。delete表之后auto_increment仍然保存在内存，但是重启后就丢失了，只能从1开始。实质上重启后的auto_increment会从 SELECT 1+MAX(ai_col) FROM t 开始。

DROP

属于数据库DDL定义语言，同Truncate；
Drop table Tablename
执行后立即生效，无法找回

drop table table_name 立刻释放磁盘空间 ，不管是 InnoDB 和 MyISAM;
drop语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index);
依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

OPTIMIZE TABLE

OPTIMIZE TABLE通过制作原来的表的一个临时副本来工作
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …
如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有 VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。

对于MyISAM表，OPTIMIZE TABLE按如下方式操作：

如果表已经删除或分解了行，则修复表。
如果未对索引页进行分类，则进行分类。
如果表的统计数据没有更新（并且通过对索引进行分类不能实现修复），则进行更新。

对于BDB表，OPTIMIZE TABLE目前被映射到ANALYZE TABLE上。对于InnoDB表，OPTIMIZE
TABLE被映射到ALTER TABLE上，这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间

OPTIMIZE

TABLE语句被写入到二进制日志中，除非使用了自选的NO_WRITE_TO_BINLOG关键词（或其别名LOCAL）。已经这么做了，因此，用于
MySQL服务器的OPTIMIZE TABLE命令的作用相当于一个复制主服务器，在默认情况下，这些命令将被复制到复制从属服务器中。