恢复前通过mysqldump缩减数据库磁盘占用空间

首先得搞清楚为什么你的原数据库（100GB）比mysqldump备份（42GB）大这么多——通常是因为碎片化、未释放的空闲空间、或者InnoDB共享表空间的特性（ibdata文件只会膨胀不会自动收缩）。下面是一些经过验证的实操方法，能帮你把恢复后的数据库体积控制在87-90GB的目标范围内：

1. 先清理原库冗余数据再重新备份 #

这是最直接的方式，从源头上减少数据量：

• 检查并删除过期/无用数据：比如归档日志、超过保留期限的业务记录、测试数据等。删除后记得对涉及的表执行OPTIMIZE TABLE your_table_name;，让数据库释放这些数据占用的磁盘空间。
• 排查重复数据：用SELECT column1, column2, COUNT(*) FROM your_table GROUP BY column1, column2 HAVING COUNT(*) > 1;找出重复条目，根据业务逻辑清理冗余行。

2. 针对不同存储引擎优化表空间 #

MyISAM引擎 #

直接执行OPTIMIZE TABLE your_table_name;，这个命令会重建表、整理数据碎片，并将未使用的空间返还给操作系统。注意：这个操作会锁表，务必在业务低峰期执行。

InnoDB引擎 #

• 如果你的库开启了innodb_file_per_table=ON（默认是开启的，可通过SHOW VARIABLES LIKE 'innodb_file_per_table';确认），同样可以用OPTIMIZE TABLE your_table_name;，它会重建表并释放空闲空间给系统。
• 如果是共享表空间模式（ibdata1文件），OPTIMIZE TABLE不会生效，因为ibdata文件不会自动收缩。这时候需要执行全库迁移来重置表空间：
  1. 先导出全库：mysqldump --all-databases --routines --triggers > full_backup.sql
  2. 停止MySQL服务
  3. 删除ibdata1和ib_logfile*文件
  4. 在my.cnf/my.ini中确保innodb_file_per_table=ON
  5. 重启MySQL服务，重新导入备份文件
     这个操作会让InnoDB为每个表创建独立的.ibd文件，彻底释放原来共享表空间中积压的空闲空间。

3. 恢复前调整新库的InnoDB配置 #

在恢复备份到新库之前，提前配置以下参数，避免后续空间浪费：

• 确保innodb_file_per_table=ON：让每个表的空间独立管理，方便后续回收空闲空间。
• 合理设置innodb_log_file_size：建议设置为1-4GB（根据业务TPS调整），避免日志文件过度膨胀。
• 关闭不必要的自动增长：如果某些表的自增列有大量空闲ID，可根据实际数据量调整自增起始值（ALTER TABLE your_table AUTO_INCREMENT = new_value;），不过这个对磁盘占用影响很小，仅作补充。

4. 在线优化大表（生产环境友好） #

如果你的库是生产环境，直接执行OPTIMIZE TABLE会锁表影响业务，推荐用Percona的pt-online-schema-change工具来在线优化表：

pt-online-schema-change --alter="ENGINE=InnoDB" D=database,t=table --execute

这个工具会创建一个临时表，逐步迁移数据，最后替换原表，全程不锁表，对业务影响极小。

重要提醒 #

• 所有操作前一定要备份原数据库，避免数据丢失！
• 大型表的优化操作耗时较长，要提前评估时间窗口，最好在业务低峰期执行。

内容的提问来源于stack exchange，提问作者Felipe Garcia