mysql数据库在日常使用中会产生大量业务数据，备份是保障数据安全的重要手段，但未经处理的备份文件往往体积庞大，加上数据库本身可能存在空间浪费的情况，会给存储管理带来压力。掌握备份压缩和空间优化技巧，能有效解决这类问题。
mysql备份时的文件压缩方法
1. 使用mysqldump配合系统压缩工具
mysqldump是mysql官方提供的逻辑备份工具，本身不支持压缩功能，但可以结合系统的压缩命令实现备份文件压缩。常见的压缩工具包括gzip、bzip2、xz等，不同工具的压缩率和压缩速度有所差异。
以下是使用mysqldump配合gzip压缩备份文件的示例，备份test数据库：
# 备份test数据库，通过管道传输给gzip压缩，生成test_backup.sql.gz文件 mysqldump -u root -p test | gzip > /data/backup/test_backup.sql.gz
如果需要恢复该压缩备份文件，可以使用以下命令：
# 解压并恢复备份到test数据库 gunzip < /data/backup/test_backup.sql.gz | mysql -u root -p test
2. 使用mysqlpump内置压缩功能
mysqlpump是mysql 5.7及以上版本提供的备份工具，相比mysqldump支持并行备份，并且内置了压缩功能，不需要依赖系统外部压缩工具。
使用mysqlpump压缩备份的示例如下：
# 使用mysqlpump备份test数据库，指定压缩算法为zlib，压缩级别为5 mysqlpump -u root -p --compress-output=zlib --compression-level=5 test > /data/backup/test_pump_backup.sql.zlib
恢复该备份文件时，需要先解压再导入：
# 解压zlib格式的备份文件，再导入到test数据库 zlib-decompress < /data/backup/test_pump_backup.sql.zlib | mysql -u root -p test
3. 使用xtrabackup进行压缩备份
xtrabackup是percona提供的物理备份工具，适合大型mysql实例的备份，支持在备份过程中直接压缩文件，压缩效率较高。
使用xtrabackup压缩备份的示例如下：
# 使用xtrabackup备份mysql数据目录，指定压缩算法为gzip，压缩级别为6 xtrabackup --backup --user=root --password=your_password --target-dir=/data/backup/xtra_backup --compress --compress-threads=4 --compress-chunk-size=64K
mysql空间优化技巧
1. 清理冗余数据
数据库中可能存在大量无用的冗余数据，比如已经废弃的日志表、测试数据、过期业务数据等，定期清理这些数据能直接释放存储空间。
可以先查询数据库中各表的数据量，定位大表：
-- 查询test数据库中所有表的行数和存储大小
SELECT
table_name,
table_rows,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS table_size_mb
FROM information_schema.TABLES
WHERE table_schema = 'test'
ORDER BY table_size_mb DESC;
确认无用数据后，可以使用DELETE或者TRUNCATE语句清理，注意TRUNCATE会清空整表数据且无法回滚，操作前需要做好确认。
2. 优化表结构和索引
不合理的表结构和冗余索引会浪费大量存储空间，比如字段类型选择过大、存在重复索引、未使用的索引等。
可以使用以下语句查看表的索引情况，排查冗余索引：
-- 查看test表的所有索引信息 SHOW INDEX FROM test;
对于重复或者无用的索引，可以使用DROP INDEX语句删除。同时检查字段类型，比如存储手机号可以使用CHAR(11)而不是VARCHAR(255)，减少空间浪费。
3. 定期执行表碎片整理
mysql在频繁进行增删改操作后，表会产生碎片，导致实际占用的存储空间远大于数据本身的大小，定期整理碎片可以释放这部分空间。
对于InnoDB引擎的表，可以使用以下语句整理碎片：
-- 整理test表的碎片，会重建表释放碎片空间 OPTIMIZE TABLE test;
注意OPTIMIZE TABLE操作会锁表，建议在业务低峰期执行。
4. 开启表压缩功能
mysql的InnoDB引擎支持表级别的数据压缩，适合存储大量文本、blob类型数据的表，开启后可以减少数据占用的存储空间。
创建压缩表的示例如下：
-- 创建压缩表，指定压缩页大小为8K
CREATE TABLE test_compress (
id INT PRIMARY KEY,
content TEXT
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
也可以将已有的表修改为压缩表：
-- 修改已有表为压缩表 ALTER TABLE test ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
不同压缩方案对比
以下是常见mysql备份压缩方案的特点对比，用户可以根据自身需求选择：
| 压缩方案 | 压缩率 | 压缩速度 | 适用场景 |
|---|---|---|---|
| mysqldump+gzip | 中等 | 较快 | 中小型数据库，逻辑备份需求 |
| mysqlpump内置压缩 | 中等 | 快 | mysql 5.7及以上版本，需要并行备份的场景 |
| xtrabackup压缩 | 高 | 中等 | 大型数据库，物理备份需求 |
注意事项
- 备份压缩会消耗一定的CPU资源，需要根据服务器的CPU和磁盘性能选择合适的压缩级别，避免影响业务正常运行。
- 空间优化操作前一定要做好数据备份，避免误操作导致数据丢失。
- 表压缩功能会增加CPU开销，对于CPU资源紧张的业务实例需要谨慎开启。
- 定期验证备份文件的可恢复性，确保压缩后的备份文件可以正常恢复数据。