MySQL5.7使用mysqldump数据备份🌵

MySQL5.7使用mysqldump数据备份🌵

 

一、 mysqldump 在 MySQL 5.7 的工作原理

 

1. 逻辑备份工具

  • mysqldump 是一个 逻辑备份工具，通过客户端连接数据库，把表结构和数据导出为 SQL 语句（CREATE TABLE、INSERT 等）。

  • 恢复时只需执行这些 SQL，就能在另一台服务器上重建数据库。

  • 与物理备份（直接复制数据文件）不同，逻辑备份更灵活，适合跨版本迁移。

2. 一致性快照

  • InnoDB 表： 使用 --single-transaction，在导出开始时开启一个事务，保证整个导出过程看到的是同一个一致性快照，不会锁表。

  • MyISAM 表： 因为不支持事务，必须用 --lock-tables 来加读锁，否则可能出现数据不一致。

  • 在混合引擎场景下，mysqldump 会同时使用事务和锁，保证数据尽可能一致。

3. 对象导出

  • 默认只导出表结构和数据。

  • 如果加上 --routines --triggers --events，还能导出存储过程、触发器和事件。

  • --hex-blob 确保二进制字段（BLOB/TEXT）在导出时不会乱码。

  • MySQL 5.7 在对象导出上和 5.6 基本一致，但更强调 DEFINER 和 SQL SECURITY 属性的保留。

4. 复制位点与 GTID

  • --master-data=2：在导出文件里写入当前 binlog 文件和位置（作为注释），方便主从复制或点时间恢复。

  • GTID 支持：

    • MySQL 5.7 默认支持 GTID，mysqldump 会提示是否要导出 GTID 集合。

    • 如果不想在恢复时包含 GTID，可以加 --set-gtid-purged=OFF，避免恢复时 GTID 冲突。

    • 如果要完整迁移 GTID 环境，可以用 --set-gtid-purged=ON，但必须确保备份包含所有事务。

5. 性能优化

  • --quick：逐行读取数据并写入文件，避免客户端内存爆炸。

  • --extended-insert：使用多行 INSERT，减少 SQL 语句数量，加快恢复速度。

  • 压缩：mysqldump 本身不压缩，通常配合 gzip 使用，例如：

    [root@localhost ~]#mysqldump ... | gzip > backup.sql.gz

6. 恢复过程

  • 恢复时直接执行：

    [root@localhost ~]# gunzip < backup.sql.gz | mysql --defaults-extra-file=/etc/mysqldump57_backup.cnf

  • mysqldump 在导出时会自动关闭外键检查，恢复时再开启，避免因表顺序导致外键错误。

  • 大库恢复时可以临时关闭 UNIQUE_CHECKS 和 FOREIGN_KEY_CHECKS，提高速度。

 

⚠️ 局限性

  • 大库性能差：TB 级数据库用 mysqldump 会非常慢，恢复更慢。

  • 非事务表不一致：MyISAM 表无法保证一致性，必须锁表。

  • 跨版本风险：5.7 导出的 SQL 在 8.0 恢复时可能遇到字符集、排序规则不兼容问题。

 

MySQL 5.6 和 MySQL5.7 mysqldump 逻辑备份深度对比表

维度 MySQL 5.6 MySQL 5.7 核心差异/注意事项
一致性快照 支持 --single-transaction，仅对 InnoDB 有效；MyISAM 需加锁。 支持 --single-transaction，仅对 InnoDB 有效；MyISAM 需加锁。 相同。依靠 MVCC 实现不锁表在线备份。
对象导出 默认导出表结构和数据；需显式加 --routines --triggers --events。 默认行为相同，但更强调保留 DEFINER 和 SQL SECURITY 属性。 5.7 更严。若目标库缺失 DEFINER 用户，恢复后对象可能无法运行。
GTID 支持 引入阶段。mysqldump 会提示警告；需显式 --set-gtid-purged=OFF 避免恢复冲突。 成熟阶段。mysqldump 默认处理；可用 ON/OFF 精确控制。建议设为 OFF 提高兼容性。 5.7 必须显式处理。否则备份文件在含 GTID 的实例间迁移易报错。
复制位点 --master-data=1/2 记录 binlog 文件和位置，用于主从。 同样支持 --master-data，但在 5.7 中更多与 GTID 结合使用。 相同。是重建从库、修复 1452 错误的关键参数。
性能优化 支持 --quick 流式导出、--extended-insert 批量插入；适合中小型库。 参数相同。但在 5.7 环境下对元数据（Metadata）处理效率更高。 5.7 略优。在大表场景下，5.7 的流式导出稳定性更好。
数据精度 对微秒级时间戳（如 DATETIME(6)）支持有限。 完美支持高精度时间类型导出。 数据一致性。5.7 导出的数据精度更高。
恢复过程 需注意外键顺序；mysqldump 默认会自动关闭/开启 foreign_key_checks。 行为一致。但在 GTID 环境下需额外关注 gtid_purged 的设置。 5.7 恢复更严。5.7 默认开启严格模式，对非法日期等数据拦截更狠。
跨版本兼容性 导出 SQL 在 5.7/8.0 运行可能遇到字符集或排序规则（Collation）差异。 更好支持跨版本。但在导入 8.0 时需注意 sql_mode (如 NO_AUTO_CREATE_USER)。 向下兼容好。通常建议“用高版本工具备份，往低版本导入”需谨慎。
安全机制 命令行明文密码会报警，支持 --defaults-extra-file。 同样的报警机制，但对密码文件的权限检查（如 600 权限）要求更严。 相同。5.7 对 .cnf 文件的安全性检测逻辑更完善。

 

✅ 总结：MySQL 5.7 的 mysqldump 备份原理和 5.6 基本一致，核心是 逻辑导出 + 一致性快照 + 对象完整性 + GTID 处理。相比 5.6，5.7 更强调 GTID 的正确处理和对象属性的保留，是跨版本迁移和主从复制场景下的重要工具。

 

二、执行备份

 使用mysqldump命令来执行备份

[root@localhost ~]#  /usr/local/mysql/bin/mysqldump \
  --defaults-extra-file=/etc/mysqldump57_backup.cnf \
  --single-transaction \
  --routines \
  --hex-blob \
  --triggers \
  --events \
  --all-databases \
  --flush-logs \
  --master-data=2 \
  --set-gtid-purged=OFF \
  | gzip > /data/mysql57/backup/mysqldump_backup/mysqldump_$(date '+%F_%H-%M-%S').sql.gz

📌 参数含义逐条解释

  • --defaults-extra-file=/etc/mysqldump57_backup.cnf 指定一个额外的配置文件，里面写好用户名、密码、host、port 等信息。这样避免在命令里明文写密码，提高安全性。

  • --single-transaction 在 InnoDB 表上使用一致性快照（事务隔离），保证导出数据的一致性，同时不锁表，业务可以继续写入。

  • --routines 导出存储过程和函数。默认不导出，必须显式加上。

  • --hex-blob 把 BLOB/TEXT/BINARY 字段用十六进制格式导出，避免乱码或特殊字符导致恢复失败。

  • --triggers 导出触发器。默认会导出，但显式写上更清晰。

  • --events 导出事件调度器里的事件（EVENT）。默认不导出，需要显式指定。

  • --all-databases 导出所有数据库。如果只想导出某个库，可以改成 --databases dbname。

  • --flush-logs 在导出时刷新 binlog，生成新的日志文件，方便后续恢复或复制。

  • --master-data=2 在导出文件里写入当前 binlog 文件和位置（作为注释），用于主从复制或点时间恢复。

    • =1：写成可执行 SQL。

    • =2：写成注释，避免恢复时自动执行。

  • --set-gtid-purged=OFF 在启用 GTID 的环境下，避免导出时写入 SET @@GLOBAL.gtid_purged，防止恢复时 GTID 冲突。

  • | gzip > ... 把导出的 SQL 文件压缩成 .sql.gz，节省存储空间。

 

三、编写备份脚本

编写基于MySQL5.6的mysqldump备份脚本

脚本目录结构：

[root@localhost ~]# tree -L 5 /data/
/data/
└── mysql57
    ├── backup
    │   └── mysqldump_backup
    │       ├── logs
    │       │   ├── mysqldump_2026-01-05_09-40-21.log
    │       │   ├── mysqldump_2026-01-05_09-46-33.log
    │       │   └── mysqldump_2026-01-05_11-06-00.log
    │       ├── mysqldump_2026-01-05_09-40-21.sql.gz
    │       ├── mysqldump_2026-01-05_09-46-33.sql.gz
    │       └── mysqldump_2026-01-05_11-06-00.sql.gz
    └── scripts
        └── mysqldump8_backup.sh

5 directories, 7 files

备份脚本：

[root@localhost ~]# vim mysqldump57_backup.sh 
#!/bin/bash
#
# ==============================================================================
# MySQL 逻辑备份脚本（基于 mysqldump）
# 适用于 MySQL 5.7，支持定时任务
# 作者：Noleaf
# 日期：2026-01-05
# ==============================================================================

#更安全的错误管理
set -euo pipefail

# 时间戳
Timestamp=$(date '+%F_%H-%M-%S')

# 备份根目录
BACKUP_BASE=/data/mysql57/backup/mysqldump_backup

# 备份文件名称
BACKUP_NAME="mysqldump_${Timestamp}.sql.gz"

# 日志目录及文件
LOG_DIR=${BACKUP_BASE}/logs
LOG_FILE=${LOG_DIR}/mysqldump_${Timestamp}.log

# 保留天数
KEEP_DAYS=30

# MySQL 配置文件（包含用户名和密码）
MYSQL_CNF=/etc/mysqldump57_backup.cnf

#=============================创建目录==========================================
mkdir -p "$BACKUP_BASE" "$LOG_DIR"

echo "========================== MySQL 5.7 mysqldump备份开始于 [$Timestamp] ===================" | tee -a "$LOG_FILE"

#=============================执行备份==========================================
#注意配置mysqldump文件路径
if /usr/local/mysql/bin/mysqldump --defaults-extra-file=${MYSQL_CNF} \
    --single-transaction \
    --routines \
    --hex-blob \
    --triggers \
    --events \
    --all-databases \
    --flush-logs \
    --master-data=2 \
    --set-gtid-purged=OFF \
    | gzip > "${BACKUP_BASE}/${BACKUP_NAME}"; then
    echo "Success: mysqldump备份成功！文件路径: ${BACKUP_BASE}/${BACKUP_NAME}" | tee -a "$LOG_FILE"
else
    echo "ERROR: mysqldump备份失败！请检查日志 ${LOG_FILE}" | tee -a "$LOG_FILE"
    exit 1
fi

#=============================清理旧备份==========================================
echo "正在清理 $KEEP_DAYS 天之前的旧备份..." | tee -a "$LOG_FILE"
find "${BACKUP_BASE}" -maxdepth 1 -type f -name "mysqldump_*.sql.gz" -mtime +${KEEP_DAYS} -exec rm -f {} \;
find "${LOG_DIR}" -maxdepth 1 -type f -name "mysqldump_*.log" -mtime +${KEEP_DAYS} -exec rm -f {} \;

#=============================统计数据简报========================================
BACKUP_SIZE=$(du -sh "${BACKUP_BASE}/${BACKUP_NAME}" | cut -f1)
echo "本次mysqldump备份大小：${BACKUP_SIZE}" | tee -a "$LOG_FILE"

echo "=================== MySQL 5.7 mysqldump备份完成于 [$(date '+%F %H:%M:%S')] ===================" | tee -a "$LOG_FILE"

⭐账号密码不写进明文，采用配置文件方式

在 /etc/mysqldump57_backup.cnf 里写好备份账号信息：

[root@localhost ~]# vim /etc/mysqldump57_backup.cnf
[client]
user=root                         #自行选择备份账号
password=asfasdfO&*#HR   
port=3306
socket=/usr/local/mysql/mysql.sock

给文件设置权限：

[root@localhost ~]# chmod 600 /etc/mysqldump57_backup.cnf

配置执行权限

#给脚本执行权限
[root@localhost ~]# chmod +x /data/mysql57/scripts/mysqldump57_backup.sh

测试运行：

#执行脚本
[root@localhost ~]# bash /data/mysql57/scripts/mysqldump57_backup.sh
========================== mysqldump备份开始于 [2026-01-05_11-06-00] ===================
Success: mysqldump备份成功！文件PATH: /data/mysql57backup/mysqldump_backup/mysqldump_2026-01-05_11-06-00.sql.gz
正在清理 30 天之前的旧备份...
本次mysqldump备份大小：500M
========================== mysqldump备份完成于 [2026-01-05 11:06:12] ===================

加入计划任务 

[root@localhost ~]# crontab -e
0 2 * * * bash /data/mysql57/scripts/mysqldump57_backup.sh >> /var/log/mysql/mysqldump57_cron.log 2>&1

 

 

四、 mysqldump 恢复备份流程

4.0 默认恢复流程

1. 准备环境

  • 确认目标数据库版本（5.6 或 5.7），避免跨版本恢复时出现字符集或 sql_mode 差异。

  • 确认目标库已初始化，并有足够磁盘空间存放恢复数据。

  • 建议在恢复前关闭业务连接，避免写入冲突。

2. 解压备份文件

如果备份文件是 .sql.gz 压缩格式，先解压：

[root@localhost ~]# gunzip /data/mysql57/backup/mysqldump_backup/mysqldump_2026-01-05_14-30-00.sql.gz

解压后得到：

mysqldump_2026-01-05_14-30-00.sql

3. 执行恢复命令

直接将 SQL 文件导入 MySQL：

[root@localhost ~]# mysql --defaults-extra-file=/etc/mysqldump57_backup.cnf < mysqldump_2026-01-05_14-30-00.sql

如果是压缩文件，可以用管道方式：

[root@localhost ~]# gunzip < mysqldump_2026-01-05_14-30-00.sql.gz | mysql --defaults-extra-file=/etc/mysqldump57_backup.cnf

4. 恢复过程中的注意事项

  • 外键检查：mysqldump 在导出时会自动关闭 FOREIGN_KEY_CHECKS，恢复时再开启，避免因表顺序导致外键错误。

  • 唯一性检查：大库恢复时可临时关闭：

    SET UNIQUE_CHECKS=0;
SET FOREIGN_KEY_CHECKS=0;

    导入完成后再开启：

    SET UNIQUE_CHECKS=1;
SET FOREIGN_KEY_CHECKS=1;

  • GTID 环境：如果备份文件包含 SET @@GLOBAL.gtid_purged，恢复时可能报错。建议备份时用 --set-gtid-purged=OFF，恢复后再根据需要手动设置。

  • 严格模式：MySQL 5.7 默认开启严格模式，对非法日期（如 0000-00-00）会报错。恢复前可调整：

    SET sql_mode='';

5. 验证恢复结果

  • 检查库和表数量：

    SHOW DATABASES;
SHOW TABLES FROM dbname;

  • 检查数据量：

    SELECT COUNT(*) FROM dbname.tablename;

  • 检查对象完整性：

    SHOW PROCEDURE STATUS;
SHOW TRIGGERS;
SHOW EVENTS;

  • 检查 binlog 位点（如果有 --master-data）：

    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000123', MASTER_LOG_POS=456789;

6. 恢复后的收尾工作

  • 检查并创建缺失的 DEFINER 用户，确保存储过程/触发器能正常运行。

  • 如果是主从环境，执行 CHANGE MASTER TO 并启动复制：

    CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000123', MASTER_LOG_POS=456789;
START SLAVE;

  • 检查日志，确认没有报错或警告。

✅ 总结

恢复流程的关键点是：

  1. 解压备份文件

  2. 导入 SQL 文件

  3. 处理外键/唯一性检查

  4. 注意 GTID 和严格模式

  5. 验证恢复结果

  6. 收尾工作（权限、复制、日志检查）

4.1 恢复场景分类（先选对）

按真实生产场景分 3 类：

场景 是否常见 复杂度
A. 新实例全量恢复 ⭐⭐⭐ 简单
B. 原实例清库恢复 ⭐⭐⭐⭐ 中
C. 全量 + binlog 时间点恢复 ⭐⭐⭐⭐⭐ 高

下面逐一写完整步骤。

4.2 场景 A：新 MySQL 实例全量恢复（最标准）

典型场景：

  • 新服务器

  • 新数据目录

  • 测试恢复

  • 灾备演练

A-1️⃣ 前置条件

  • MySQL 5.7 已安装并启动

  • 数据目录是 空的 或新初始化

  • 能正常登录 mysql

A-2️⃣ 找到备份文件

[root@localhost ~]# ls -lh /data/mysql57/backup/mysqldump_backup/
例如：
mysqldump_2026-01-05_10-30-00.sql.gz

A-3️⃣ 执行恢复（标准做法）

[root@localhost ~]# gunzip < mysqldump_2026-01-05_10-30-00.sql.gz \
    | /usr/local/mysql/bin/mysql \
    --defaults-extra-file=/etc/mysqldump57_backup.cnf

🔍 解释

  • gunzip <：边解压边导入（省空间）

  • 不需要手动建库

  • 所有库一次性恢复

A-4️⃣ 验证恢复结果

SHOW DATABASES;

SELECT COUNT(*) FROM mysql.user;

SELECT VERSION();
 
 

4.3 场景 B：原实例“清库后恢复”（非常常见）

典型场景：

  • 数据被误删

  • 表被 DROP

  • 但 MySQL 实例还在

B-1️⃣ 强烈建议：停业务

[root@localhost ~]# systemctl stop mysql

或至少：

FLUSH TABLES WITH READ LOCK;

B-2️⃣ 清空旧数据（⚠️谨慎）

[root@localhost ~]# systemctl stop mysql
[root@localhost ~]# rm -rf /usr/local/mysql8/data/*            #根据自己安装MySQL的目录
[root@localhost ~]# systemctl start mysql

📌 必须保证是你要恢复的实例

B-3️⃣ 执行恢复

同 A-3️⃣：

[root@localhost ~]# gunzip < mysqldump_2026-01-05_10-30-00.sql.gz \
    | /usr/local/mysql/bin/mysql \
    --defaults-extra-file=/etc/mysqldump57_backup.cnf
 

B-4️⃣ 恢复完成后检查

mysql> SHOW SLAVE STATUS\G    #如果是从库


mysql> SHOW DATABASES;

 

 

4.4 场景 C：全量 + binlog 时间点恢复（高级）

C-1️⃣ 找到 dump 中的 binlog 点

[root@localhost ~]# zcat mysqldump_2026-01-05_10-30-00.sql.gz | head -50

你会看到类似：

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000123', MASTER_LOG_POS=456789;

记下：

  • binlog 文件

  • position

C-2️⃣ 先恢复全量（同 A / B）

[root@localhost ~]# gunzip < mysqldump_2026-01-05_10-30-00.sql.gz | mysql ...
 

C-3️⃣ 用 binlog 恢复到“误操作前”

例如：

  • 误删发生在 2026-01-05 14:30:00

[root@localhost ~]# mysqlbinlog \
  --start-position=456789 \
  --stop-datetime="2026-01-05 14:29:59" \
  mysql-bin.000123 mysql-bin.000124 \
| mysql --defaults-extra-file=/etc/mysqldump57_backup.cnf
 

👉 这一步 = 时间点恢复（PITR）

C-4️⃣ 验证数据

SELECT * FROM 关键表 LIMIT 10;
 

4.5 恢复后的“必做检查清单”

✔ 数据完整性

SELECT COUNT(*) FROM 核心表;

✔ 字符集

SHOW VARIABLES LIKE 'character_set%';

✔ 账号是否存在

SELECT user,host FROM mysql.user;

✔ 主从状态（如有）

SHOW SLAVE STATUS\G
 

 

posted @ 2026-01-05 14:40  Noleaf  阅读(12)  评论(0)    收藏  举报