MySQL5.6使用mysqldump数据备份的实现

来源：这里教程网时间：2026-03-03 10:17:22 作者：

一、 mysqldump 在 MySQL 5.6 的工作原理1.逻辑备份工具2.一致性快照3.对象导出4.复制位点5.性能优化6.恢复过程⚠️ 局限性⭐MySQL 5.6 和 MySQL5.7 mysqldump 逻辑备份深度对比表二、执行备份使用mysqldump命令来执行备份???? 参数含义逐条解释三、备份脚本编写基于MySQL5.6的mysqldump备份脚本四、恢复备份流程MySQL 5.6 mysqldump 恢复流程1. 准备环境2. 解压备份文件3. 执行恢复命令4. 恢复过程中的注意事项5. 验证恢复结果6. 恢复后的收尾工作✅ 总结

一、 mysqldump 在 MySQL 5.6 的工作原理

1.逻辑备份工具

mysqldump 是一个 客户端工具，它不会直接复制数据文件，而是通过 SQL 协议连接到数据库，把表结构和数据导出为一系列 SQL 语句（CREATE TABLE、INSERT 等）。恢复时只需执行这些 SQL，就能在另一台服务器上重建数据库。

2.一致性快照

对 InnoDB 表：使用 --single-transaction，在导出开始时开启一个事务，保证整个导出过程看到的是同一个一致性快照，不会锁表。对 MyISAM 表：因为不支持事务，必须用 --lock-tables 来加读锁，否则可能出现数据不一致。

3.对象导出

默认只导出表结构和数据。如果加上 --routines --triggers --events，还能导出存储过程、触发器和事件。--hex-blob 确保二进制字段（BLOB/TEXT）在导出时不会乱码。

4.复制位点

--master-data=2 会在导出文件里写入当前 binlog 文件和位置（作为注释），方便主从复制或点时间恢复。在 MySQL 5.6 开启 GTID 时，mysqldump 会提示是否要导出 GTID 集合。通常建议加 --set-gtid-purged=OFF，避免恢复时 GTID 冲突。

5.性能优化

--quick：逐行读取数据并写入文件，避免客户端内存爆炸。--extended-insert：使用多行 INSERT，减少 SQL 语句数量，加快恢复速度。压缩：mysqldump 本身不压缩，通常配合 gzip 使用，例如：

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

6.恢复过程

恢复时直接执行：

[root@localhost ~]# gunzip < backup.sql.gz | mysql -u root -p

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

⚠️ 局限性

大库性能差：TB 级数据库用 mysqldump 会非常慢，恢复更慢。
非事务表不一致：MyISAM 表无法保证一致性，必须锁表。
跨版本风险：5.6 导出的 SQL 在 8.0 恢复时可能遇到字符集、排序规则不兼容问题。

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

维度MySQL 5.6MySQL 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.6 的 mysqldump 是一个 逻辑备份工具，通过 SQL 导出数据库结构和数据，适合小型或中型数据库、跨版本迁移，但不适合超大规模数据库。

二、执行备份

使用mysqldump命令来执行备份

[root@localhost ~]# /usr/local/mysql/bin/mysqldump \ --defaults-extra-file=/etc/mysqldump56_backup.cnf \ --single-transaction \ --routines \ --hex-blob \ --triggers \ --events \ --all-databases \ --flush-logs \ --master-data=2 \ --set-gtid-purged=OFF \ | gzip > /data/mysql56/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/ └── mysql56 ├── 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 mysqldump56_backup.sh #!/bin/bash # # ============================================================================== # MySQL 逻辑备份脚本（基于 mysqldump） # 适用于 MySQL 5.6，支持定时任务 # 作者：Noleaf # 日期：2025-12-31 # ============================================================================== set -euo pipefail # 时间戳 Timestamp=$(date '+%F_%H-%M-%S') # 备份根目录 BACKUP_BASE=/data/mysql56/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/mysqldump56_backup.cnf #=============================创建目录========================================== mkdir -p "$BACKUP_BASE" "$LOG_DIR" echo "========================== MySQL 5.6 mysqldump备份开始于 [$Timestamp] ===================" | tee -a "$LOG_FILE" #=============================执行备份========================================== 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.6 mysqldump备份完成于 [$(date '+%F %H:%M:%S')] ===================" | tee -a "$LOG_FILE"

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

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

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

执行脚本

#给脚本执行权限 [root@localhost ~]# chmod +x mysqldump56_backup.sh #执行脚本 [root@localhost ~]# bash mysqldump56_backup.sh ========================== mysqldump备份开始于 [2026-01-05_11-06-00] =================== Success: mysqldump备份成功！文件PATH: /data/mysql56/backup/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/mysql56/scripts/mysqldump56_backup.sh >> /var/log/mysql/mysqldump56_cron.log 2>&1

四、恢复备份流程

MySQL 5.6 mysqldump 恢复流程

1. 准备环境

确认目标库版本为 MySQL 5.6。确认目标库已初始化，并有足够磁盘空间。建议在恢复前关闭业务连接，避免写入冲突。如果是主从环境，确认 binlog_format 和复制参数一致。

2. 解压备份文件

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

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

得到：

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

3. 执行恢复命令

直接导入 SQL 文件：

[root@localhost ~]# mysql --defaults-extra-file=/etc/mysqldump56_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/mysqldump56_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 支持： MySQL 5.6 是 GTID 的引入阶段，恢复时如果备份文件包含 SET @@GLOBAL.gtid_purged，可能报错。建议在备份时使用 --set-gtid-purged=OFF，恢复后再根据需要手动设置。

字符集/排序规则： 5.6 的默认字符集可能与目标库不同，恢复时需确认 character_set_server 和 collation_server。