MySQL 磁盘空间不足处理方法

磁盘空间监控

1. 操作系统层面监控

使用df命令查看磁盘使用情况：

bash

# 查看所有磁盘分区的使用情况
df -h

# 查看指定目录所在分区的使用情况
df -h /var/lib/mysql

使用du命令查看目录大小：

bash

# 查看MySQL数据目录大小
du -sh /var/lib/mysql

# 查看MySQL数据目录下各子目录大小
du -h --max-depth=1 /var/lib/mysql

监控磁盘IO：

bash

# 使用iostat监控磁盘IO
iostat -xm 1

# 使用iotop监控进程IO
iotop

2. MySQL层面监控

查看数据目录位置：
sql
```
SHOW VARIABLES LIKE 'datadir';
```

查看各数据库大小：

sql

SELECT table_schema AS 'Database', 
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

查看各表大小：

sql

SELECT table_schema AS 'Database',
       table_name AS 'Table',
       ROUND(data_length / 1024 / 1024, 2) AS 'Data Size (MB)',
       ROUND(index_length / 1024 / 1024, 2) AS 'Index Size (MB)',
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Total Size (MB)'
FROM information_schema.tables
ORDER BY (data_length + index_length) DESC;

查看Binlog文件大小：
sql
```
SHOW BINARY LOGS;
```

3. 自动监控工具

Nagios/Zabbix：配置磁盘空间告警，当磁盘使用率超过阈值时发送告警
Prometheus + Grafana：监控磁盘空间趋势，设置告警规则
MySQL Enterprise Monitor：提供MySQL磁盘空间监控和告警
脚本监控：编写Shell脚本定期检查磁盘空间，发送邮件或短信告警

磁盘空间清理策略

1. 清理Binlog日志

查看Binlog保留策略：

sql

SHOW VARIABLES LIKE 'expire_logs_days';
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

手动清理旧Binlog：

sql

-- 设置Binlog保留时间为7天
SET GLOBAL expire_logs_days = 7;

-- MySQL 8.0+使用此参数
SET GLOBAL binlog_expire_logs_seconds = 604800;

-- 手动清理到指定Binlog文件
PURGE BINARY LOGS TO 'mysql-bin.000100';

-- 手动清理指定时间之前的Binlog
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';

注意事项：
- 确保主从复制已完成，避免清理正在使用的Binlog
- 对于主从架构，确保从库已应用要清理的Binlog

2. 清理Relay Log

查看Relay Log配置：
sql
```
SHOW VARIABLES LIKE 'relay_log%';
```

手动清理Relay Log：

sql

-- 在从库上执行
RESET SLAVE ALL;

-- 或者
STOP SLAVE;
RESET SLAVE;
START SLAVE;

3. 清理Slow Query Log

查看Slow Query Log配置：
sql
```
SHOW VARIABLES LIKE 'slow_query_log%';
```

清理Slow Query Log：

bash

# 停止慢查询日志
mysql -u root -p -e "SET GLOBAL slow_query_log = 0;"

# 清理日志文件
rm -f /var/log/mysql/slow-query.log

# 重新创建日志文件并设置权限
touch /var/log/mysql/slow-query.log
chown mysql:mysql /var/log/mysql/slow-query.log

# 开启慢查询日志
mysql -u root -p -e "SET GLOBAL slow_query_log = 1;"

4. 清理General Log

查看General Log配置：
sql
```
SHOW VARIABLES LIKE 'general_log%';
```

清理General Log：

bash

# 停止通用日志
mysql -u root -p -e "SET GLOBAL general_log = 0;"

# 清理日志文件
rm -f /var/log/mysql/general.log

# 重新创建日志文件并设置权限
touch /var/log/mysql/general.log
chown mysql:mysql /var/log/mysql/general.log

# 开启通用日志（生产环境不建议开启）
mysql -u root -p -e "SET GLOBAL general_log = 1;"

5. 清理临时文件

查看临时文件目录：
sql
```
SHOW VARIABLES LIKE 'tmpdir';
```

清理临时文件：

bash

# 清理临时目录下的文件（注意：确保没有正在使用的临时文件）
rm -f /tmp/#sql* /tmp/mysql*

6. 清理冗余数据

删除过期数据：

sql

-- 删除30天前的日志数据
DELETE FROM log_table WHERE log_time < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 使用分区表，直接删除过期分区
ALTER TABLE log_table DROP PARTITION p202301;

优化表空间：

sql

-- 优化单个表
OPTIMIZE TABLE table_name;

-- 优化所有表（谨慎使用，会锁表）
mysqlcheck -u root -p --optimize --all-databases

清理碎片：

sql

-- 查看表碎片情况
SELECT table_schema, table_name, 
       data_free / 1024 / 1024 AS data_free_mb,
       table_rows
FROM information_schema.tables
WHERE data_free > 0
ORDER BY data_free DESC;

-- 优化表以清理碎片
OPTIMIZE TABLE fragmented_table;

磁盘空间扩容方案

1. 扩展现有分区

使用LVM扩展分区：

bash

# 1. 查看PV、VG、LV信息
pvs
vgs
lvs

# 2. 扩展LV
lvextend -L +100G /dev/mapper/vg_mysql-lv_mysql

# 3. 扩展文件系统
# 对于ext4文件系统
resize2fs /dev/mapper/vg_mysql-lv_mysql

# 对于xfs文件系统
xfs_growfs /dev/mapper/vg_mysql-lv_mysql

使用fdisk扩展分区：

bash

# 1. 查看分区信息
fdisk -l

# 2. 删除并重新创建分区（注意：数据有风险，建议先备份）
fdisk /dev/sda

# 3. 扩展文件系统
resize2fs /dev/sda1

2. 添加新磁盘

挂载新磁盘到现有目录：

bash

# 1. 分区并格式化新磁盘
fdisk /dev/sdb
mkfs.ext4 /dev/sdb1

# 2. 挂载新磁盘到临时目录
mount /dev/sdb1 /mnt/new_disk

# 3. 复制MySQL数据到新磁盘
cp -a /var/lib/mysql/* /mnt/new_disk/

# 4. 卸载新磁盘并重新挂载到MySQL数据目录
umount /mnt/new_disk
mount /dev/sdb1 /var/lib/mysql

# 5. 更新/etc/fstab实现开机自动挂载
echo "/dev/sdb1 /var/lib/mysql ext4 defaults 0 0" >> /etc/fstab

3. 迁移MySQL数据目录

迁移步骤：

bash

# 1. 停止MySQL服务
systemctl stop mysqld

# 2. 创建新的数据目录
mkdir -p /new/mysql/data
chown mysql:mysql /new/mysql/data

# 3. 复制数据到新目录
rsync -av /var/lib/mysql/ /new/mysql/data/

# 4. 修改MySQL配置文件
sed -i 's|datadir=/var/lib/mysql|datadir=/new/mysql/data|' /etc/my.cnf

# 5. 更新SELinux上下文（如果启用了SELinux）
semanage fcontext -a -t mysqld_db_t /new/mysql/data
restorecon -R /new/mysql/data

# 6. 启动MySQL服务
systemctl start mysqld

4. 使用外部存储

NFS挂载：适合中小规模应用

bash

# 挂载NFS存储
mount -t nfs nfs_server:/export/mysql /var/lib/mysql

iSCSI挂载：适合大规模应用，性能较好

bash

# 发现iSCSI目标
iscsiadm -m discovery -t st -p iscsi_server

# 登录iSCSI目标
iscsiadm -m node -T iqn.2024-01.com.example:target1 -p iscsi_server -l

# 格式化并挂载
mkfs.ext4 /dev/sdb
mount /dev/sdb /var/lib/mysql

云存储：如AWS EBS、Azure Disk、阿里云云盘等

bash

# AWS EBS示例
aws ec2 attach-volume --volume-id vol-12345678 --instance-id i-12345678 --device /dev/sdf

磁盘空间不足应急处理

1. 紧急清理步骤

检查并停止占用大量磁盘空间的进程：

bash

# 查看占用磁盘空间最多的10个进程
lsof -s | sort -k 7 -r -n | head -10

紧急清理Binlog：

sql

-- 清理所有Binlog（仅在紧急情况下使用，会导致主从复制中断）
RESET MASTER;

临时移动大文件：

bash

# 将大文件临时移动到其他磁盘
mv /var/lib/mysql/large_table.ibd /tmp/

2. 数据库层面应急处理

限制查询结果集：

sql

-- 限制查询返回的行数
SELECT * FROM large_table LIMIT 100;

暂停写入操作：

sql

-- 锁定表，禁止写入
LOCK TABLES table_name READ;

-- 或者暂停复制（在从库上）
STOP SLAVE;

临时关闭慢查询日志：
sql
```
SET GLOBAL slow_query_log = 0;
```

预防措施

1. 配置合理的日志策略

Binlog策略：

txt

# 在my.cnf中配置
# MySQL 5.7及以下
expire_logs_days = 7

# MySQL 8.0+
binlog_expire_logs_seconds = 604800

# 限制单个Binlog大小
max_binlog_size = 1G

Slow Query Log策略：

txt

# 在my.cnf中配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = 0

General Log策略：

txt

# 生产环境建议关闭
general_log = 0

2. 实施分区表

创建分区表：

sql

CREATE TABLE log_table (
    id INT AUTO_INCREMENT,
    log_time DATETIME,
    content TEXT,
    PRIMARY KEY (id, log_time)
) PARTITION BY RANGE (YEAR(log_time) * 100 + MONTH(log_time)) (
    PARTITION p202401 VALUES LESS THAN (202402),
    PARTITION p202402 VALUES LESS THAN (202403),
    PARTITION p202403 VALUES LESS THAN (202404)
);

定时添加和删除分区：

sql

-- 添加新分区
ALTER TABLE log_table ADD PARTITION (
    PARTITION p202404 VALUES LESS THAN (202405)
);

-- 删除旧分区
ALTER TABLE log_table DROP PARTITION p202301;

3. 定期清理策略

编写清理脚本：

bash

#!/bin/bash

# 清理30天前的慢查询日志
find /var/log/mysql -name "slow-query.log.*" -mtime +30 -delete

# 清理10天前的临时文件
find /tmp -name "#sql*" -mtime +10 -delete

# 优化有碎片的表
mysql -u root -p -e "SELECT CONCAT('OPTIMIZE TABLE ', table_schema, '.', table_name, ';') FROM information_schema.tables WHERE data_free > 104857600" > optimize.sql
mysql -u root -p < optimize.sql

设置定时任务：

bash

# 添加到crontab，每天凌晨2点执行
0 2 * * * /path/to/cleanup_script.sh

4. 监控告警配置

Zabbix告警配置：
- 创建触发器：当磁盘使用率>90%时触发告警
- 告警级别：严重
- 告警方式：邮件、短信、微信

Prometheus告警规则：

yaml

groups:
- name: disk-space-alerts
  rules:
  - alert: DiskSpaceRunningFull
    expr: (node_filesystem_size_bytes{mountpoint="/var/lib/mysql"} - node_filesystem_free_bytes{mountpoint="/var/lib/mysql"}) / node_filesystem_size_bytes{mountpoint="/var/lib/mysql"} * 100 > 90
    for: 5m
    labels:
      severity: critical
    annotations:
      summary: "Disk space running full on {{ $labels.instance }}"
      description: "Disk space on {{ $labels.instance }} at {{ $value | printf \"%.2f\" }}%"

不同MySQL版本的差异

MySQL 5.7及以下

Binlog保留策略：使用expire_logs_days参数
慢查询日志：默认使用文件存储，支持表存储
临时表空间：使用ibtmp1文件，大小无限制
数据字典：使用.frm文件存储表结构

MySQL 8.0+

Binlog保留策略：推荐使用binlog_expire_logs_seconds参数
慢查询日志：支持JSON格式
临时表空间：支持多个临时表空间文件
数据字典：使用InnoDB表存储，不再使用.frm文件
undo表空间：支持独立的undo表空间，可配置自动截断

常见问题（FAQ）

Q1: 如何快速定位MySQL占用大量磁盘空间的原因？

A1: 快速定位步骤：

使用df -h查看MySQL数据目录所在分区的使用情况
使用du -h --max-depth=1 /var/lib/mysql查看各子目录大小
使用SHOW DATABASES和SELECT table_schema, SUM(data_length + index_length) FROM information_schema.tables GROUP BY table_schema查看各数据库大小
检查Binlog、Relay Log、Slow Query Log的大小
查看表碎片情况，使用SELECT table_name, data_free FROM information_schema.tables WHERE data_free > 0

Q2: 清理Binlog会影响主从复制吗？

A2: 清理Binlog可能会影响主从复制：

确保从库已应用要清理的Binlog
使用SHOW SLAVE STATUS查看从库的Relay_Master_Log_File和Exec_Master_Log_Pos
只清理从库已应用的Binlog
对于主从架构，建议使用expire_logs_days或binlog_expire_logs_seconds自动管理

Q3: OPTIMIZE TABLE会锁表吗？

A3: OPTIMIZE TABLE的锁表情况：

对于MyISAM表：会锁表，阻塞所有读写操作
对于InnoDB表：在MySQL 5.6+中，使用ALGORITHM=INPLACE时，不会锁表（但会产生大量IO）
建议在业务低峰期执行OPTIMIZE TABLE操作
对于大表，考虑使用分区表或其他方式清理碎片

Q4: 如何处理临时表空间（ibtmp1）过大的问题？

A4: 处理ibtmp1过大的方法：

重启MySQL服务，ibtmp1会自动重建

在my.cnf中配置临时表空间大小限制：

txt

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:1G

优化查询，减少临时表的使用
检查是否有长时间运行的查询

Q5: 如何预防磁盘空间不足问题？

A5: 预防措施：

配置合理的日志保留策略
实施分区表，定期删除过期数据
定期清理日志和临时文件
监控磁盘空间，设置告警阈值
规划合理的磁盘容量，考虑数据增长
使用自动化脚本定期清理和优化

Q6: 迁移MySQL数据目录需要注意什么？

A6: 迁移注意事项：

迁移前先备份数据
停止MySQL服务后再迁移
保持文件权限一致（mysql:mysql）
更新MySQL配置文件中的datadir参数
对于启用SELinux的系统，更新SELinux上下文
迁移后验证数据完整性
测试应用连接是否正常

Q7: 如何处理分区表的磁盘空间问题？

A7: 分区表磁盘空间处理：

定期删除过期分区，释放磁盘空间
对大分区进行拆分
考虑使用水平分表或分库分表
监控各分区的空间使用情况

Q8: 云环境下如何处理MySQL磁盘空间不足？

A8: 云环境处理方法：

使用云服务提供商的磁盘扩容功能（如AWS EBS卷扩容）
迁移到更大的云磁盘
使用云存储服务（如S3）存储备份和归档数据
利用云平台的监控和告警服务
考虑使用云数据库服务，自动管理磁盘空间

MySQL 磁盘空间不足处理方法 ​

磁盘空间监控 ​

1. 操作系统层面监控 ​

2. MySQL层面监控 ​

3. 自动监控工具 ​

磁盘空间清理策略 ​

1. 清理Binlog日志 ​

2. 清理Relay Log ​

3. 清理Slow Query Log ​

4. 清理General Log ​

5. 清理临时文件 ​

6. 清理冗余数据 ​

磁盘空间扩容方案 ​

1. 扩展现有分区 ​

2. 添加新磁盘 ​

3. 迁移MySQL数据目录 ​

4. 使用外部存储 ​

磁盘空间不足应急处理 ​

1. 紧急清理步骤 ​

2. 数据库层面应急处理 ​

预防措施 ​

1. 配置合理的日志策略 ​

2. 实施分区表 ​

3. 定期清理策略 ​

4. 监控告警配置 ​

不同MySQL版本的差异 ​

MySQL 5.7及以下 ​

MySQL 8.0+ ​

常见问题（FAQ） ​

Q1: 如何快速定位MySQL占用大量磁盘空间的原因？ ​

Q2: 清理Binlog会影响主从复制吗？ ​

Q3: OPTIMIZE TABLE会锁表吗？ ​

Q4: 如何处理临时表空间（ibtmp1）过大的问题？ ​

Q5: 如何预防磁盘空间不足问题？ ​

Q6: 迁移MySQL数据目录需要注意什么？ ​

Q7: 如何处理分区表的磁盘空间问题？ ​

Q8: 云环境下如何处理MySQL磁盘空间不足？ ​

MySQL 磁盘空间不足处理方法

磁盘空间监控

1. 操作系统层面监控

2. MySQL层面监控

3. 自动监控工具

磁盘空间清理策略

1. 清理Binlog日志

2. 清理Relay Log

3. 清理Slow Query Log

4. 清理General Log

5. 清理临时文件

6. 清理冗余数据

磁盘空间扩容方案

1. 扩展现有分区

2. 添加新磁盘

3. 迁移MySQL数据目录

4. 使用外部存储

磁盘空间不足应急处理

1. 紧急清理步骤

2. 数据库层面应急处理

预防措施

1. 配置合理的日志策略

2. 实施分区表

3. 定期清理策略

4. 监控告警配置

不同MySQL版本的差异

MySQL 5.7及以下

MySQL 8.0+

常见问题（FAQ）

Q1: 如何快速定位MySQL占用大量磁盘空间的原因？

Q2: 清理Binlog会影响主从复制吗？

Q3: OPTIMIZE TABLE会锁表吗？

Q4: 如何处理临时表空间（ibtmp1）过大的问题？

Q5: 如何预防磁盘空间不足问题？

Q6: 迁移MySQL数据目录需要注意什么？

Q7: 如何处理分区表的磁盘空间问题？

Q8: 云环境下如何处理MySQL磁盘空间不足？