备份环境要求
- 支持版本与工具
- MySQL 5.7 / 8.0（推荐使用与服务器同主版本的客户端工具）
- 官方备份工具：
- 逻辑全量：mysqldump（随 MySQL 客户端提供）
- 物理全量热备：MySQL Enterprise Backup（mysqlbackup，需 MySQL Enterprise 许可）
- 权限要求
- 逻辑备份（mysqldump）：SELECT、SHOW VIEW、TRIGGER、EVENT（如需锁表则还需 LOCK TABLES）
- 物理备份（mysqlbackup）：PROCESS、RELOAD、LOCK TABLES、REPLICATION CLIENT；在 8.0 上优先使用 BACKUP_ADMIN（如可用）
- 资源与存储
- 备份目标磁盘可用空间 ≥ 数据实际占用大小（逻辑备份通常小于或等于数据库大小，物理备份≈数据目录大小）
- 备份期间建议避免大事务和模式变更
- 一致性与日志
- InnoDB：使用 --single-transaction 获取一致性快照（无阻塞）
- 非 InnoDB（如 MyISAM）：需加锁或停写窗口以确保一致性
- 建议开启二进制日志（binlog），并设置合理保留周期（例如 binlog_expire_logs_seconds），便于后续点时间恢复（PITR）
- 密码与安全
- 避免将明文密码写入命令行（使用 -p 交互输入或客户端选项文件 ~/.my.cnf）
- 备份文件请保存到受限访问的路径，并纳入离站/异地存放策略
示例：创建备份专用账号（根据实际需要裁剪权限）
CREATE USER 'backup'@'%' IDENTIFIED BY 'Strong!Passw0rd';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES ON *.* TO 'backup'@'%';
-- 如使用 mysqlbackup，再授予：
GRANT PROCESS, RELOAD, REPLICATION CLIENT ON *.* TO 'backup'@'%';
-- 8.0 可选：
GRANT BACKUP_ADMIN ON *.* TO 'backup'@'%';
FLUSH PRIVILEGES;
预期输出示例：
Query OK, 0 rows affected
备份策略选择
- 首选方案（通用）：mysqldump 全库逻辑全量备份
- 优点：跨版本/跨平台、可读可检视、可选择性恢复对象
- 缺点：大库备份/恢复速度较慢；占用 CPU/IO
- 适用：中小体量库、对恢复灵活性有要求的场景
- 高可用/大规模方案：MySQL Enterprise Backup（mysqlbackup）物理全量热备
- 优点：热备、速度快、支持压缩/加密、可直接物理恢复
- 缺点：需要企业版授权
- 适用：大型库、严格 RTO/RPO 要求的生产环境
- 特殊场景（停机冷备）：关库后复制数据目录
- 优点：简洁可靠
- 缺点：需停机；不适合高可用生产时段
- 适用：维护窗口内的离线完整拷贝
建议：
- 库主要为 InnoDB，且对备份时间敏感：优先 mysqlbackup
- 库体量适中、需跨版本迁移或细粒度恢复：优先 mysqldump
- 同时配合 binlog 保留实现 PITR（本指南聚焦全量备份；PITR 可在恢复章节提示）
详细操作步骤
方案A：mysqldump 逻辑全量备份（在线，一致性）
- 预检查
mysql -ubackup -p -h127.0.0.1 -P3306 -e "SELECT VERSION(), @@GLOBAL.gtid_mode, @@GLOBAL.enforce_gtid_consistency\G"
预期输出示例（摘要）：
VERSION(): 8.0.36
@@GLOBAL.gtid_mode: ON
@@GLOBAL.enforce_gtid_consistency: ON
- 执行全库一致性备份（GTID 环境推荐）
- 说明
- --single-transaction：开启一致性快照（InnoDB 无锁）
- --quick：流式读取，降低内存占用
- --routines/--events/--triggers：备份程序、事件、触发器
- --set-gtid-purged=ON：在 dump 中记录 GTID 信息，便于恢复
- --order-by-primary：按主键顺序导出，加速恢复
- --default-character-set=utf8mb4：统一字符集
- --hex-blob：二进制列安全导出
- --comments：写入元信息注释
- --all-databases：全库（含系统库，恢复时会跳过某些系统库对象）
命令：
DUMP_FILE=/backup/mysql/full_$(date +%F_%H%M%S).sql
mysqldump \
-ubackup -p -h127.0.0.1 -P3306 --protocol=TCP \
--all-databases \
--single-transaction --quick \
--routines --events --triggers \
--set-gtid-purged=ON \
--order-by-primary \
--default-character-set=utf8mb4 \
--hex-blob \
--comments \
--result-file="$DUMP_FILE"
交互提示示例：
Enter password:
完成后检查：
echo $?
tail -n 5 "$DUMP_FILE"
预期输出示例：
0
-- Dump completed on 2025-12-02 10:21:37
可选：记录当下 binlog 位点（非 GTID 环境常用）
mysql -ubackup -p -e "SHOW MASTER STATUS\G"
预期输出示例：
File: binlog.000123
Position: 456789
Executed_Gtid_Set: 0f23a1d3-...:1-34567
注意：
- 若包含非 InnoDB 表，需考虑使用 --lock-tables（会阻塞写入），或在低峰执行。
- 如严格避免短暂的全局读锁，不要同时使用 --master-data 与 --single-transaction；改用 GTID 记录。
- 存储与权限
ls -lh "$DUMP_FILE"
chmod 600 "$DUMP_FILE"
预期输出示例：
-rw------- 1 mysql mysql 28G /backup/mysql/full_2025-12-02_102137.sql
方案B：MySQL Enterprise Backup 物理全量热备（推荐大库）
- 预检查与目录准备
mysqlbackup --version
mkdir -p /backup/meb
预期输出示例：
mysqlbackup: MySQL Enterprise Backup version 8.0.36
- 执行全量热备并应用日志
- 说明
- backup-and-apply-log：一步完成备份与可恢复准备（apply-log）
- --backup-dir：备份输出目录
- --with-timestamp：自动创建带时间戳子目录
- --compress：官方压缩（可选）
- --encrypt/--encrypt-key-file：官方加密（可选且推荐）
- 为避免密码暴露，推荐交互或使用 --passwords-from-stdin
命令（交互输入密码示例）：
mysqlbackup \
--host=127.0.0.1 --port=3306 \
--user=backup --password \
--backup-dir=/backup/meb --with-timestamp \
--compress \
backup-and-apply-log
预期输出摘要：
mysqlbackup: INFO: Starting with following command line ...
mysqlbackup: INFO: Backup completed OK!
mysqlbackup: INFO: Apply-log completed OK!
- 验证备份目录
ls -l /backup/meb
预期输出示例：
drwxr-x--- 2 mysql mysql 4096 2025-12-02 10:25 2025-12-02_10-25-14
方案C（可选）：停机冷备（保证一致性，需维护窗口）
- 安全停止实例
sudo systemctl stop mysqld
# 或
mysqladmin -uroot -p shutdown
预期输出示例：
[OK] Stopped MySQL Server
- 复制数据目录与配置
grep -E "datadir|log_error|innodb_undo_directory" /etc/my.cnf /etc/mysql/my.cnf
- 备份数据目录与重要文件（含 my.cnf、数据目录、二进制日志目录等）
tar -C / -czpf /backup/mysql/cold_full_$(date +%F_%H%M%S).tar.gz \
etc/my.cnf var/lib/mysql
预期输出示例：
tar: Creating archive...
- 启动实例
sudo systemctl start mysqld
预期输出示例：
[OK] Started MySQL Server
备份验证方法
-
完整性与可读性（逻辑备份）
- 文件存在且非空
test -s "$DUMP_FILE" && echo "Dump exists and non-empty"
- 预检 SQL 语法（对测试库做 dry-run 恢复或抽样）
head -n 200 "$DUMP_FILE" | sed -n '1,50p'
- 元信息检查
grep -E "Dump completed on|GTID_PURGED" "$DUMP_FILE"
-
可用性验证（推荐在隔离测试实例）
-
业务校验
- 样本表行数对比
mysql -ubackup -p -e "SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema') ORDER BY TABLE_ROWS DESC LIMIT 10;"
对比源库与测试库计数（大表可做 sampling 或校验主键范围）
恢复测试流程
从 mysqldump 全量备份恢复
前提：目标实例版本不低于源库，具备足够空间；若使用 GTID，确保全新实例或已协调 GTID 状态。
- 准备空实例（测试用）
mysqld --initialize-insecure --datadir=/var/lib/mysql
systemctl start mysqld
- 若备份包含 SET @@GLOBAL.gtid_purged（--set-gtid-purged=ON）
- 目标实例需：
SET GLOBAL gtid_mode = ON;
SET GLOBAL enforce_gtid_consistency = ON;
-- 确保 gtid_executed 为空（全新实例或执行 RESET MASTER）
-- 注意：生产环境谨慎使用 RESET MASTER
RESET MASTER;
预期输出：
Query OK, 0 rows affected
- 导入全量
- 验证关键对象与数据
mysql -uroot -p -e "SELECT COUNT(*) FROM db1.table_critical;"
预期输出：
COUNT(*)
1234567
- 如需基于 binlog 做增量回放（PITR）（可选，超出全量范围）
- 记录的位点或 GTID 为起点，使用 mysqlbinlog 回放到目标时间点
从 mysqlbackup 物理全量备份恢复
- 确保备份已 apply-log（若未执行，可先运行）
mysqlbackup --backup-dir=/backup/meb/2025-12-02_10-25-14 apply-log
预期输出：
mysqlbackup: INFO: Apply-log completed OK!
- 停止目标实例并清空数据目录（谨慎）
systemctl stop mysqld
mv /var/lib/mysql /var/lib/mysql.bak_$(date +%s)
mkdir -p /var/lib/mysql
chown mysql:mysql /var/lib/mysql
- 执行物理恢复
mysqlbackup \
--backup-dir=/backup/meb/2025-12-02_10-25-14 \
--datadir=/var/lib/mysql \
copy-back
chown -R mysql:mysql /var/lib/mysql
预期输出：
mysqlbackup: INFO: copy-back completed OK!
- 启动与验证
systemctl start mysqld
mysql -uroot -p -e "SHOW DATABASES;"
预期：实例正常启动且数据完整
常见问题排查
-
问题：mysqldump 期间出现锁等待或写入阻塞
- 原因：使用 --master-data 会触发短暂 FTWRL；或存在 MyISAM 表且使用 --lock-tables
- 解决：仅使用 --single-transaction（InnoDB）；避免 --master-data，改用 --set-gtid-purged=ON；对非 InnoDB 表安排低峰或迁移为 InnoDB
-
问题：恢复时报 GTID_PURGED 不为空或冲突
- 原因：目标实例已存在 gtid_executed
- 解决：全新实例或在确认安全的前提下使用 RESET MASTER；或在备份时使用 --set-gtid-purged=OFF，并通过其他方式衔接位点
-
问题：导入出现字符集/乱码
- 原因：源/目标字符集不一致
- 解决：备份时指定 --default-character-set=utf8mb4；目标实例配置 character_set_server=utf8mb4；导入前 SET NAMES utf8mb4
-
问题：视图/存储程序导入失败（DEFINER 不存在）
- 原因：备份文件中包含 DEFINER=...，目标实例缺少对应账号
- 解决：在目标库创建相应 DEFINER 账号，或在变更评审后调整对象的 DEFINER（建议在测试验证流程中提前处理）
-
问题：大库 mysqldump/恢复耗时过长
- 建议：使用 --order-by-primary、--quick；在恢复时适当调整 innodb_buffer_pool_size、innodb_flush_log_at_trx_commit（测试/迁移阶段谨慎调整）；或采用 mysqlbackup 物理备份/恢复
-
问题：备份文件过大
- 解决：mysqlbackup 可使用 --compress；逻辑备份可结合存储层压缩策略与分层存储（请遵循合规与安全策略）
-
问题：权限不足报错（如锁表、事件导出）
- 解决：为备份账号补齐 SELECT、SHOW VIEW、TRIGGER、EVENT（mysqldump），以及 PROCESS/RELOAD/LOCK TABLES（mysqlbackup）
-
问题：非 InnoDB 表不一致
- 原因：未加锁的情况下 mysqldump 对非事务引擎无一致性保证
- 解决：使用 --lock-tables 或安排停机冷备；建议将关键表迁移至 InnoDB
提示与最佳实践
- 使用与服务器同主版本的客户端工具（mysqldump/mysqlbackup）执行备份与恢复
- 将备份与恢复操作纳入自动化与审计（包含校验、告警、离站复制）
- 定期做恢复演练（至少季度），并保留演练报告（RTO/RPO 评估）
- 保留备份与 binlog 至少覆盖业务要求的恢复时间窗口（如近7～30天）
- 严格控制备份文件的访问权限与保存周期，满足合规要求（含加密、销毁流程）
