MySQL多线程备份工具mysqlpump详解!
MySQLPUMP备份工具详解
1. 概述
MySQLPump 是 MySQL 5.7 引入的一个客户端备份工具，用于替代传统的 mysqldump 工具。它提供了并行处理、进度状态显示、更好的压缩支持等新特性，能够更高效地执行 MySQL 数据库备份操作。
2. 主要特性
-
并行处理：可以并行备份多个数据库或表，提高备份速度
-
进度显示：提供备份进度信息
-
压缩支持：支持在备份时直接压缩输出
-
用户和权限备份：可选择备份用户账户和权限
-
更灵活的过滤选项：可以更精确地选择要备份的对象
-
性能优化：减少对服务器性能的影响
3. 安装与使用
MySQLpump 随 MySQL 5.7+ 一起安装，通常位于 MySQL 的 bin 目录下。
基本使用语法
mysqlpump [options] [db_name [tbl_name ...]]
4. 常用选项
连接选项
| 选项 | 描述 |
|---|---|
--user=user_name 或 | MySQL 用户名 |
--password[=password] 或 | MySQL 密码 |
--host=host_name 或 | 连接的主机名 |
--port=port_num 或 | 连接的端口号 |
--socket=path 或 | 连接的 socket 文件 |
备份选项
| 选项 | 描述 |
|---|---|
--default-parallelism=N | 默认并行度（默认2） |
--parallel-schemas=[N:]db_list | 指定哪些数据库并行备份 |
--single-transaction | 对支持事务的表使用事务隔离 |
--compress-output=algorithm | 使用指定算法压缩输出（LZ4或ZLIB） |
--defer-table-indexes | 延迟创建索引到数据加载后 |
--exclude-databases=db_list | 排除指定的数据库 |
--exclude-tables=table_list | 排除指定的表 |
--include-databases=db_list | 只包含指定的数据库 |
--include-tables=table_list | 只包含指定的表 |
--users | 备份用户账户 |
--skip-definer | 省略创建对象的 DEFINER 子句 |
--skip-dump-rows 或 | 不备份表数据，只备份结构 |
输出选项
| 选项 | 描述 |
|---|---|
--result-file=file_name 或 | 输出到指定文件 |
--set-gtid-purged=value | 控制是否添加 SET @@GLOBAL.GTID_PURGED |
5. 使用示例
基本备份示例
备份整个 MySQL 实例：
mysqlpump -u root -p --all-databases > full_backup.sql
并行备份示例
并行备份多个数据库（默认并行度2）：
mysqlpump -u root -p --parallel-schemas=4:db1,db2 --parallel-schemas=3:db3,db4,db5 > backup.sql
压缩备份示例
使用 LZ4 压缩备份：
mysqlpump -u root -p --compress-output=LZ4 --all-databases > backup.lz4
解压缩：
lz4_decompress backup.lz4 backup.sql
选择性备份示例
只备份特定数据库：
mysqlpump -u root -p --include-databases=db1,db2 > selected_dbs.sql
备份数据库结构但不备份数据：
mysqlpump -u root -p --skip-dump-rows db1 > db1_structure.sql
备份用户账户
mysqlpump -u root -p --users > users_backup.sql
6. 高级用法
使用并行处理优化大型数据库备份
对于大型数据库，可以调整并行度以提高备份速度：
mysqlpump -u root -p --default-parallelism=8 --parallel-schemas=4:large_db1,large_db2 > large_backup.sql
排除特定表
mysqlpump -u root -p db1 --exclude-tables=large_table1,large_table2 > db1_filtered.sql
延迟索引创建
减少恢复时间，先加载数据再创建索引：
mysqlpump -u root -p --defer-table-indexes db1 > db1_deferred_indexes.sql
7. 恢复备份
使用 mysql 客户端恢复备份：
mysql -u root -p < backup.sql
对于压缩备份，先解压再恢复：
lz4_decompress backup.lz4 backup.sql
mysql -u root -p < backup.sql
8. 性能考虑
-
并行度选择：根据服务器CPU核心数和负载情况选择适当的并行度
-
内存使用：并行备份会增加内存使用量
-
服务器负载：避免在生产高峰期执行大型备份
-
网络带宽：远程备份时考虑网络带宽限制
9. 与 mysqldump 的比较
| 特性 | mysqlpump | mysqldump |
|---|---|---|
| 并行处理 | 支持 | 不支持 |
| 进度显示 | 支持 | 不支持 |
| 压缩输出 | 内置支持 | 需要外部工具 |
| 性能影响 | 通常较低 | 通常较高 |
| 成熟度 | 较新 | 非常成熟 |
| 灵活性 | 过滤选项更多 | 选项较少 |
10. 限制与注意事项
-
MySQLpump 需要 MySQL 5.7 或更高版本
-
并行备份可能导致锁等待问题
-
某些情况下可能不如 mysqldump 稳定
-
备份过程中如果中断，可能需要手动清理临时对象
-
不备份存储过程、函数、触发器的创建时间戳
11. 最佳实践
-
测试备份：定期验证备份的完整性和可恢复性
-
监控进度：大型备份时监控进度和资源使用情况
-
合理调度：安排在低峰期执行大型备份
-
安全存储：妥善保管备份文件，特别是包含敏感数据的备份
-
版本兼容性：注意 MySQL 版本间的兼容性问题
12. 故障排除
问题1：备份过程中出现锁等待超时
-
解决方案：增加锁等待超时时间
--lock-wait-timeout=seconds
问题2：内存不足
-
解决方案：减少并行度或分批备份
问题3：GTID 相关错误
-
解决方案：适当设置
--set-gtid-purged选项
问题4：备份文件损坏
-
解决方案：检查磁盘空间，验证备份完整性
备份实战：
mysqlpump 备份脚本演示;
#!/bin/bash
# MySQLpump 备份脚本
# 基础配置
DB_HOST="192.168.0.1"
DB_PORT=3306
DB_USER="backup_user"
DB_PASS="xxxxxxxxxxx"
DATABASES="testdb"
BACKUP_DIR="/data/backup"
TS=$(date +%Y_%m_%d)
BACKUP_FILE="${BACKUP_DIR}/mysqlbak_${DATABASES}_${TS}.sql"
LOG_FILE="${BACKUP_DIR}/backup_${TS}.log"
PARALLEL_THREADS=4 # 设置并行线程数(根据CPU数，一般设置为CPU一半)# 记录开始时间
echo"[$(date +'%F %T')] 备份开始，使用 ${PARALLEL_THREADS} 个线程" | tee -a ${LOG_FILE}# 执行备份命令
mysqlpump -h${DB_HOST} -u${DB_USER} -p"${DB_PASS}" -P${DB_PORT} \--default-parallelism=${PARALLEL_THREADS} \--parallel-schemas=${PARALLEL_THREADS}:${DATABASES} \--single-transaction \--set-gtid-purged=off \--default-character-set=utf8mb4 \--compress-output=LZ4 \--defer-table-indexes \--skip-definer \--exclude-databases=mysql,sys,information_schema,performance_schema \--log-error=${LOG_FILE} \--result-file=${BACKUP_FILE} \2>> ${LOG_FILE}# 检查结果
if [ $? -eq 0 ] && [ -f ${BACKUP_FILE} ]; then# 获取压缩后的文件大小BACKUP_SIZE=$(du -sh ${BACKUP_FILE} | awk '{print $1}')echo"[$(date +'%F %T')] 备份成功，文件：${BACKUP_FILE} (大小: ${BACKUP_SIZE})" | tee -a ${LOG_FILE}# 可选：解压备份文件验证完整性# lz4_decompress ${BACKUP_FILE} ${BACKUP_FILE%.lz4}# echo "[$(date +'%F %T')] 备份已解压验证" | tee -a ${LOG_FILE}
elseecho"[$(date +'%F %T')] 备份失败！请检查日志：${LOG_FILE}" >&2exit 1
fi
脚本说明：
-
并行处理：
-
使用
--default-parallelism=4设置默认并行度为4 -
使用
--parallel-schemas=4:testdb对指定数据库使用4个线程并行备份
-
-
压缩功能：
-
添加
--compress-output=LZ4直接输出LZ4压缩格式，减少备份文件大小
-
-
性能优化：
-
--defer-table-indexes延迟创建索引，加快恢复速度 -
--skip-definer省略DEFINER子句，避免权限问题
-
-
系统库排除：
-
自动排除系统库(mysql,sys等)减少不必要的备份内容
-
-
结果文件：
-
使用
--result-file直接指定输出文件，而不是重定向
-
使用建议：
-
根据服务器CPU核心数调整
PARALLEL_THREADS值，通常设置为CPU核心数的50-75% -
对于非常大的数据库，可以考虑分批备份不同表或使用更多并行线程
-
恢复备份时需要先解压：
lz4_decompress mysqlbak_testdb_2025_03_28.sql.lz4 mysqlbak_testdb_2025_03_28.sql mysql -h${DB_HOST} -u${DB_USER} -p"${DB_PASS}" < /data/backup/mysqlbak_testdb_2025_03_28.sql