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  或 -u user_name	MySQL 用户名
--password[=password]  或 -p[password]	MySQL 密码
--host=host_name  或 -h host_name	连接的主机名
--port=port_num  或 -P port_num	连接的端口号
--socket=path  或 -S 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  或 -d	不备份表数据，只备份结构

输出选项

选项	描述
--result-file=file_name  或 -r 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. 性能考虑

1. 并行度选择：根据服务器CPU核心数和负载情况选择适当的并行度

2. 内存使用：并行备份会增加内存使用量

3. 服务器负载：避免在生产高峰期执行大型备份

4. 网络带宽：远程备份时考虑网络带宽限制

9. 与 mysqldump 的比较

特性	mysqlpump	mysqldump
并行处理	支持	不支持
进度显示	支持	不支持
压缩输出	内置支持	需要外部工具
性能影响	通常较低	通常较高
成熟度	较新	非常成熟
灵活性	过滤选项更多	选项较少

10. 限制与注意事项

1. MySQLpump 需要 MySQL 5.7 或更高版本

2. 并行备份可能导致锁等待问题

3. 某些情况下可能不如 mysqldump 稳定

4. 备份过程中如果中断，可能需要手动清理临时对象

5. 不备份存储过程、函数、触发器的创建时间戳

11. 最佳实践

1. 测试备份：定期验证备份的完整性和可恢复性

2. 监控进度：大型备份时监控进度和资源使用情况

3. 合理调度：安排在低峰期执行大型备份

4. 安全存储：妥善保管备份文件，特别是包含敏感数据的备份

5. 版本兼容性：注意 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

脚本说明：

1. 并行处理：

   • 使用 --default-parallelism=4 设置默认并行度为4

   • 使用 --parallel-schemas=4:testdb 对指定数据库使用4个线程并行备份

2. 压缩功能：

   • 添加 --compress-output=LZ4 直接输出LZ4压缩格式，减少备份文件大小

3. 性能优化：

   • --defer-table-indexes 延迟创建索引，加快恢复速度

   • --skip-definer 省略DEFINER子句，避免权限问题

4. 系统库排除：

   • 自动排除系统库(mysql,sys等)减少不必要的备份内容

5. 结果文件：

   • 使用 --result-file 直接指定输出文件，而不是重定向

使用建议：

1. 根据服务器CPU核心数调整 PARALLEL_THREADS 值，通常设置为CPU核心数的50-75%

2. 对于非常大的数据库，可以考虑分批备份不同表或使用更多并行线程

3. 恢复备份时需要先解压：

   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