260810-mysql-mysqldump-gzip全库备份(可排除表)

使用 mysqldump 配合 gzip，可以在导出 MySQL 全部数据库的同时直接压缩备份文件，减少磁盘占用。如果某些日志表、缓存表或大数据表不需要备份，还可以通过 --ignore-table 将其排除。

一、全库压缩备份

最简单的全库备份命令如下：

1
2
3
4
5
6
7
8
9
mysqldump -u root -p \
  --all-databases \
  --single-transaction \
  --routines \
  --events \
  --triggers \
  --hex-blob \
  --set-gtid-purged=OFF \
  | gzip > mysql_all_$(date +%Y%m%d_%H%M%S).sql.gz

执行后会提示输入 MySQL 密码。备份数据通过管道直接交给 gzip 压缩，不会先生成未压缩的 SQL 文件。

几个常用参数的含义：

参数 作用
--all-databases 导出全部数据库
--single-transaction 在事务中获得一致性备份，适合 InnoDB 表
--routines 导出存储过程和函数
--events 导出事件调度器中的事件
--triggers 导出触发器，通常默认启用，这里明确写出
--hex-blob 将二进制字段按十六进制格式导出
--set-gtid-purged=OFF 避免在备份文件中写入 GTID 设置语句

二、排除指定表

排除表时，需要写完整的 数据库名.表名。每排除一张表，就增加一个 --ignore-table 参数：

1
2
3
4
5
6
7
8
9
10
11
12
mysqldump -u root -p \
  --all-databases \
  --single-transaction \
  --routines \
  --events \
  --triggers \
  --hex-blob \
  --set-gtid-purged=OFF \
  --ignore-table=app_db.operation_log \
  --ignore-table=app_db.request_log \
  --ignore-table=monitor_db.history_data \
  | gzip > mysql_all_$(date +%Y%m%d_%H%M%S).sql.gz

需要注意，--ignore-table 会同时排除表结构和表数据。如果希望保留表结构，只排除其中的数据，可以先正常备份全库结构，再在数据备份时使用 --ignore-table，或者针对相关数据库使用 --ignore-table-data（使用前需确认当前 MySQL 客户端版本是否支持）。

三、可复用的备份脚本

下面的脚本方便放入 Linux 定时任务中使用：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
#!/usr/bin/env bash

set -o pipefail

BACKUP_DIR="/data/mysql_backup"
BACKUP_TIME="$(date +%Y%m%d_%H%M%S)"
BACKUP_FILE="${BACKUP_DIR}/mysql_all_${BACKUP_TIME}.sql.gz"

EXCLUDE_TABLES=(
  "app_db.operation_log"
  "app_db.request_log"
  "monitor_db.history_data"
)

mkdir -p "${BACKUP_DIR}"

IGNORE_ARGS=()
for TABLE_NAME in "${EXCLUDE_TABLES[@]}"; do
  IGNORE_ARGS+=("--ignore-table=${TABLE_NAME}")
done

mysqldump \
  --defaults-extra-file=/root/.my.cnf \
  --all-databases \
  --single-transaction \
  --quick \
  --routines \
  --events \
  --triggers \
  --hex-blob \
  --set-gtid-purged=OFF \
  "${IGNORE_ARGS[@]}" \
  | gzip > "${BACKUP_FILE}"

if [ "$?" -eq 0 ] && gzip -t "${BACKUP_FILE}"; then
  echo "备份成功：${BACKUP_FILE}"
else
  echo "备份失败：${BACKUP_FILE}" >&2
  exit 1
fi

为了不在脚本和进程列表中暴露密码，可以创建 /root/.my.cnf：

1
2
3
4
5
[client]
host=127.0.0.1
port=3306
user=backup_user
password=替换为实际密码

然后限制配置文件权限：

1
chmod 600 /root/.my.cnf

备份账号需要具有读取待备份对象所需的权限。实际授权范围应根据 MySQL 版本、是否备份视图、存储过程及是否启用 GTID 等情况进行调整，尽量遵循最小权限原则。

四、恢复备份

恢复前可以先检查压缩文件是否完整：

1
gzip -t mysql_all_20260810_103000.sql.gz

确认无误后进行恢复：

1
gunzip -c mysql_all_20260810_103000.sql.gz | mysql -u root -p

也可以使用 zcat：

1
zcat mysql_all_20260810_103000.sql.gz | mysql -u root -p

全库备份通常包含创建数据库、建表和写入数据的语句。恢复操作可能覆盖或修改现有数据，因此应先在测试环境验证备份文件，并确认目标实例和恢复时间点。

五、注意事项

  1. --single-transaction 主要保证 InnoDB 表的一致性，对 MyISAM 等非事务表不能提供同样的保证。
  2. 备份期间尽量不要执行 ALTER TABLE、DROP TABLE 等结构变更操作。
  3. mysqldump 属于逻辑备份，数据库较大时，备份和恢复速度可能比较慢。
  4. 备份完成后不仅要检查文件是否存在，还应使用 gzip -t 检查压缩文件，并定期进行实际恢复演练。
  5. 备份文件中可能包含敏感数据，应设置合理的文件权限，并按需要进行加密和异地保存。
  6. 定时清理旧备份时要先确认目录和保留周期，避免误删其他文件。

总结来说，核心方式就是：使用 mysqldump --all-databases 导出全库，通过多个 --ignore-table=数据库名.表名 排除不需要的表，最后利用管道交给 gzip 压缩保存。