6.5.2 逻辑备份（mysqldump/mysqlpump）与恢复流程

逻辑备份以 SQL 语句形式导出数据与对象，适用于跨版本迁移、表级恢复与小中型库备份。常用工具为 mysqldump 与 mysqlpump：前者稳定通用，后者支持并行导出提升大库效率，但对版本与参数兼容性要求更高。执行前需确认一致性需求、锁策略、字符集与存储引擎特性，并评估备份窗口与资源占用。

原理草图（导出/导入流程）：

安装与准备（以常见发行版为例）：

# Debian/Ubuntu
sudo apt-get update
sudo apt-get install -y mysql-client

# RHEL/CentOS/Rocky
sudo yum install -y mysql

# 验证工具
mysqldump --version
mysqlpump --version

备份流程要点与命令解释：
- 一致性控制：InnoDB 优先使用 --single-transaction 获取一致性快照；MyISAM 需 --lock-tables 或低峰停写。
- 对象范围：按需导出库/表；注意包含存储过程、函数、触发器、事件（--routines --triggers --events）。
- 导出格式：默认 SQL 文本；GTID 环境可用 --set-gtid-purged=OFF 规避冲突。
- 性能与并行：mysqlpump 可用 --default-parallelism 并行导出。
- 可靠性：--quick 逐行读取；大表分片导出；输出压缩降低存储。
- 安全与隔离：最小权限备份用户；避免命令行明文密码，使用 --defaults-extra-file。

备份用户与配置示例（含最小权限）：

-- 在源库创建备份用户
CREATE USER 'backup'@'10.%' IDENTIFIED BY 'StrongPass!123';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES ON *.* TO 'backup'@'10.%';
FLUSH PRIVILEGES;

# /etc/mysql/backup.cnf
[client]
user=backup
password=StrongPass!123
host=10.0.0.10
port=3306

常用备份示例（含完整可执行命令）：

# 1) 全库备份（含过程/触发器/事件）
mysqldump --defaults-extra-file=/etc/mysql/backup.cnf \
  --single-transaction --routines --triggers --events \
  --databases db1 > /backup/db1_$(date +%F).sql

# 2) 指定表备份
mysqldump --defaults-extra-file=/etc/mysql/backup.cnf \
  --single-transaction db1 t1 t2 > /backup/db1_t1_t2.sql

# 3) 并行导出（mysqlpump）
mysqlpump --defaults-extra-file=/etc/mysql/backup.cnf \
  --databases db1 --default-parallelism=4 > /backup/db1_pump.sql

# 4) 大表分片导出（按ID范围）
mysqldump --defaults-extra-file=/etc/mysql/backup.cnf \
  --single-transaction db1 big_table \
  --where="id BETWEEN 1 AND 1000000" > /backup/big_table_1.sql

备份文件压缩与校验：

# 压缩
gzip -c /backup/db1_$(date +%F).sql > /backup/db1_$(date +%F).sql.gz

# 校验
sha256sum /backup/db1_$(date +%F).sql.gz > /backup/db1_$(date +%F).sha256

恢复流程（含参数解释与加速）：

# 1) 解压（如有）
gunzip -c /backup/db1_2024-01-01.sql.gz > /backup/db1_2024-01-01.sql

# 2) 预建库（如需指定字符集）
mysql -uroot -p -e "CREATE DATABASE IF NOT EXISTS db1 DEFAULT CHARSET=utf8mb4;"

# 3) 导入（适当放大包大小）
mysql -uroot -p --max_allowed_packet=512M --net_buffer_length=1M < /backup/db1_2024-01-01.sql

# 4) 导入加速（可选，导入前后再恢复）
mysql -uroot -p -e "SET GLOBAL foreign_key_checks=0; SET GLOBAL unique_checks=0;"
# 导入完成后恢复
mysql -uroot -p -e "SET GLOBAL foreign_key_checks=1; SET GLOBAL unique_checks=1;"

GTID/复制环境注意事项：

# 在备份时关闭 GTID 写入，避免导入冲突
mysqldump --defaults-extra-file=/etc/mysql/backup.cnf \
  --single-transaction --set-gtid-purged=OFF \
  --databases db1 > /backup/db1.sql

# 在从库恢复，需先停止复制
mysql -uroot -p -e "STOP SLAVE;"
mysql -uroot -p < /backup/db1.sql
mysql -uroot -p -e "START SLAVE;"

验证恢复（行数/校验和/对象完整性）：

# 行数对比
mysql -uroot -p -e "SELECT COUNT(*) FROM db1.t1;"

# 表校验和（简单一致性验证）
mysql -uroot -p -e "CHECKSUM TABLE db1.t1;"

# 过程/触发器/事件检查
mysql -uroot -p -e "SHOW PROCEDURE STATUS WHERE Db='db1';"
mysql -uroot -p -e "SHOW TRIGGERS FROM db1;"
mysql -uroot -p -e "SHOW EVENTS FROM db1;"

常见排错与处理：
- 导出慢：启用 mysqlpump 并行或分片导出；避开高峰；确保磁盘写入与网络不成为瓶颈。
- 备份不一致：确认 --single-transaction 且无 DDL 并发；必要时加 --master-data 记录位点。
- 导入失败：检查字符集、sql_mode、权限与引擎支持；先导入结构再导入数据。
- 连接中断：增大 --max_allowed_packet，检查网络与超时参数（如 net_read_timeout）。

练习（动手验证）：
1. 创建一个包含触发器与事件的库，使用 mysqldump 导出并验证对象是否完整。
2. 使用 mysqlpump 对 3 个表并行导出，对比导出耗时与文件大小。
3. 在测试库中模拟大表分片导出与分片导入，并验证总行数一致。
4. 在从库上执行恢复流程，观察复制状态变化并恢复后重新启用复制。