印刻数据库

外观

MySQL 磁盘空间不足问题 ​

空间占用分析 ​

识别空间占用大户 ​

  1. 查看数据目录总大小
bash
du -sh /var/lib/mysql/
  1. 查看各数据库大小
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 (data_length + index_length) DESC;
  1. 查看各表大小
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;
  1. 查看日志文件大小
bash
ls -lh /var/lib/mysql/*.log /var/lib/mysql/binlog.* /var/lib/mysql/relay-log.*

不同文件类型的空间占用 ​

  • 数据文件 (.ibd/.MYD)：存储表数据，通常是最大的空间占用者
  • 索引文件 (.ibd/.MYI)：存储索引数据，与数据文件大小相当
  • 二进制日志 (binlog.*)：记录所有数据更改，可能占用大量空间
  • 中继日志 (relay-log.*)：从主库复制的二进制日志，在从库上占用空间
  • 错误日志 (error.log)：记录错误信息，通常较小
  • 慢查询日志 (slow.log)：记录慢查询，可能随时间增长
  • 临时文件：排序、分组等操作产生的临时文件

版本差异 ​

  • MySQL 5.6及以下：默认使用MyISAM存储引擎，数据和索引分开存储
  • MySQL 5.7及以上：默认使用InnoDB存储引擎，数据和索引存储在同一文件中
  • MySQL 8.0：引入了更多的日志类型，如redo log和undo log的管理更精细

紧急处理步骤 ​

1. 清理临时文件 ​

bash
# 清理操作系统临时文件
rm -rf /tmp/mysql_*

# 清理MySQL临时表空间
# 注意：需谨慎操作，确保不会影响正在运行的事务

2. 清理二进制日志 ​

sql
# 查看当前正在使用的二进制日志
SHOW MASTER STATUS;

# 清理指定日期之前的二进制日志
PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';

# 保留最近7天的二进制日志
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

# 只保留指定数量的二进制日志
PURGE BINARY LOGS TO 'binlog.000100';

3. 清理慢查询日志 ​

bash
# 备份并清空慢查询日志
cp /var/lib/mysql/slow.log /var/lib/mysql/slow.log.bak
> /var/lib/mysql/slow.log

# 或使用MySQL命令
SET GLOBAL slow_query_log = 'OFF';
mv /var/lib/mysql/slow.log /var/lib/mysql/slow.log.bak
SET GLOBAL slow_query_log = 'ON';

4. 清理中继日志 ​

sql
# 在从库上清理中继日志
PURGE RELAY LOGS;

# 或清理到指定的中继日志
PURGE RELAY LOGS TO 'relay-log.000100';

5. 优化表空间 ​

sql
# 优化表，回收碎片空间
OPTIMIZE TABLE table_name;

# 或优化整个数据库
mysqlcheck -o database_name

6. 删除无用数据 ​

sql
# 删除指定条件的数据
DELETE FROM table_name WHERE created_at < '2023-01-01';

# 或使用TRUNCATE清空整个表（注意：无法回滚）
TRUNCATE TABLE table_name;

长期解决方案 ​

1. 实施数据归档策略 ​

  • 按时间归档：将历史数据迁移到归档表或归档数据库
  • 按业务重要性归档：将不常用的数据迁移到低成本存储
  • 使用分区表：按时间或其他维度分区，方便归档和查询

2. 优化存储引擎 ​

  • 从MyISAM迁移到InnoDB：InnoDB支持更高效的空间管理
  • 启用压缩：InnoDB支持表级压缩，减少存储空间
sql
CREATE TABLE table_name (...) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

3. 优化二进制日志配置 ​

  • 调整过期时间：设置合适的二进制日志过期时间
sql
SET GLOBAL expire_logs_days = 7;
  • 调整日志大小：设置合适的二进制日志文件大小
sql
SET GLOBAL max_binlog_size = 1073741824; -- 1GB

4. 优化临时表配置 ​

  • 调整临时表空间大小：设置合适的临时表空间大小
sql
SET GLOBAL tmp_table_size = 67108864; -- 64MB
SET GLOBAL max_heap_table_size = 67108864; -- 64MB
  • 使用独立的临时表空间：将临时表空间放在单独的磁盘上

5. 实施定期清理计划 ​

  • 定期清理日志文件：设置crontab定期清理日志
  • 定期优化表：定期运行OPTIMIZE TABLE优化表空间
  • 定期删除过期数据：设置定时任务删除过期数据

监控与预警 ​

1. 设置磁盘空间监控 ​

  • 使用操作系统工具：df命令监控磁盘使用率
bash
df -h
  • 使用MySQL监控工具：
    • MySQL Enterprise Monitor
    • Percona Monitoring and Management (PMM)
    • Zabbix
    • Prometheus + Grafana

2. 设置预警阈值 ​

  • 当磁盘使用率达到80%时，发送警告
  • 当磁盘使用率达到90%时，发送严重警告
  • 当磁盘使用率达到95%时，触发自动清理脚本

3. 配置自动扩展 ​

  • 在云环境中，可以配置自动扩展磁盘大小
  • 在物理机环境中，可以准备备用磁盘，以便快速扩展

生产环境最佳实践 ​

1. 磁盘规划 ​

  • 数据文件和日志文件分开存储
  • 二进制日志和中继日志放在独立磁盘
  • 临时表空间放在独立磁盘
  • 使用RAID 10提高性能和可靠性

2. 定期备份 ​

  • 定期备份数据，以便在需要时恢复
  • 备份到外部存储，减少主磁盘压力

3. 测试恢复流程 ​

  • 定期测试数据恢复流程，确保备份可用
  • 测试在磁盘空间不足情况下的恢复流程

4. 制定应急预案 ​

  • 制定详细的磁盘空间不足应急预案
  • 明确各角色的职责和操作流程
  • 定期演练应急预案

常见问题（FAQ） ​

Q1: 如何快速查看MySQL磁盘空间使用情况？ ​

A1: 可以使用以下命令快速查看：

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 (data_length + index_length) DESC;

-- 查看表大小
SELECT table_schema AS 'Database', 
       table_name AS 'Table', 
       ROUND(data_length + index_length) / 1024 / 1024 AS 'Size (MB)'
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC 
LIMIT 10;

Q2: 清理二进制日志会影响复制吗？ ​

A2: 清理二进制日志可能会影响复制，特别是当从库还没有应用这些日志时。因此，在清理二进制日志之前，应该：

  1. 查看从库正在读取的二进制日志：SHOW SLAVE STATUS
  2. 确保所有从库都已经应用了要清理的二进制日志
  3. 使用PURGE BINARY LOGS TO而不是PURGE BINARY LOGS BEFORE，更安全

Q3: 如何避免OPTIMIZE TABLE锁表？ ​

A3: OPTIMIZE TABLE会锁表，影响业务。可以考虑以下策略：

  1. 在业务低峰期执行
  2. 使用ALTER TABLE ... ENGINE=InnoDB代替，它会创建一个新表，然后原子替换，锁表时间更短
  3. 使用Percona Toolkit的pt-online-schema-change工具，它可以在线优化表，几乎不锁表

Q4: 磁盘空间不足导致MySQL无法启动怎么办？ ​

A4: 可以尝试以下方法：

  1. 清理磁盘空间，删除不必要的文件
  2. 移动一些非关键文件到其他磁盘
  3. 临时修改MySQL配置，减少一些缓存大小
  4. 如果是二进制日志导致的，可以手动删除旧的二进制日志文件

Q5: 如何设置二进制日志的自动清理？ ​

A5: 可以通过以下参数设置：

sql
-- MySQL 5.7及以下
SET GLOBAL expire_logs_days = 7;

-- MySQL 8.0及以上
SET GLOBAL binlog_expire_logs_seconds = 604800; -- 7天

Q6: 如何监控InnoDB表空间的使用情况？ ​

A6: 可以使用以下命令：

sql
-- 查看InnoDB表空间使用情况
SELECT NAME, SPACE, FILE_SIZE / 1024 / 1024 AS 'File Size (MB)', 
       ALLOCATED_SIZE / 1024 / 1024 AS 'Allocated Size (MB)', 
       FREE_EXTENTS * EXTENT_SIZE / 1024 / 1024 AS 'Free Size (MB)'
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES;

Q7: 临时表空间满了怎么办？ ​

A7: 可以尝试以下方法：

  1. 增加临时表空间大小：SET GLOBAL tmp_table_size = 67108864; SET GLOBAL max_heap_table_size = 67108864;
  2. 清理临时表：FLUSH TABLES;
  3. 重启MySQL服务，会重建临时表空间
  4. 调整临时表空间配置，使用独立的临时表空间文件

Q8: 如何避免慢查询日志过大？ ​

A8: 可以考虑以下策略：

  1. 设置合适的慢查询阈值：SET GLOBAL long_query_time = 2;
  2. 只记录没有使用索引的查询：SET GLOBAL log_queries_not_using_indexes = ON;
  3. 限制慢查询日志大小：使用日志轮转工具
  4. 定期清理慢查询日志

Q9: 磁盘空间不足时，如何优先清理哪些文件？ ​

A9: 建议按照以下优先级清理：

  1. 临时文件
  2. 慢查询日志
  3. 旧的二进制日志（确保从库已应用）
  4. 旧的中继日志
  5. 过期数据
  6. 优化表空间

Q10: 如何制定长期的磁盘空间管理策略？ ​

A10: 可以考虑以下策略：

  1. 实施数据归档策略，定期迁移历史数据
  2. 使用分区表，方便管理和查询
  3. 启用InnoDB压缩，减少存储空间
  4. 定期监控和预警
  5. 制定应急预案
  6. 定期演练磁盘空间不足的处理流程