MySQL数据库备份与还原的方案及具体命令行操作

2026-07-03 131295 代码

众所周知备份与还原是数据库管理中不可或缺的部分,这篇文章主要介绍了MySQL数据库备份与还原的方案及具体命令行操作的相关资料,文中通过代码介绍的非常详细,需要的朋友可以参考下

目录
  • MySQL 数据库备份与还原详细解读
  • 一、备份类型与核心工具
    • 核心备份工具
  • 二、推荐备份方案
    • 方案 1：小型数据库（<100GB）—— 逻辑备份（mysqldump）
    • 方案 2：中型数据库（100GB~1TB）—— 多线程逻辑备份（mydumper）
    • 方案 3：大型数据库（>1TB）—— 物理热备（Percona XtraBackup）
    • 方案 4：关键业务（高可用需求）—— 全量+增量+Binlog 组合
  • 三、具体命令行操作
    • 场景 1：逻辑备份（mysqldump）
      • 1. 备份单个数据库（含结构和数据）
      • 2. 备份多个数据库
      • 3. 备份所有数据库（含系统库）
      • 4. 备份单表
      • 5. 带条件的备份（如按时间过滤数据）
      • 6. 压缩备份（节省空间）
      • 7. 关键选项说明
    • 场景 2：逻辑备份（mydumper，多线程）
      • 1. 安装 mydumper
      • 2. 备份单个数据库
      • 3. 备份所有数据库
    • 场景 3：物理备份（Percona XtraBackup）
      • 1. 安装 Percona XtraBackup
      • 2. 全量备份
      • 3. 增量备份（基于全量备份）
      • 4. 准备备份（恢复前必须步骤）
    • 场景 4：还原数据库
      • 1. 逻辑备份还原（mysqldump 生成的 SQL 文件）
      • 2. mydumper 备份还原
      • 3. Percona XtraBackup 物理还原
  • 四、备份策略与最佳实践
    • 1. 备份频率
      • 2. 备份文件管理
        • 3. 验证备份有效性
          • 4. 加密与压缩
          • 五、注意事项
            • 六、总结

              MySQL 数据库备份与还原详细解读

              MySQL 数据库备份与还原是保障数据安全的核心操作，需根据数据库规模、性能要求、恢复时效选择合适方案。本文从备份类型、推荐方案、具体命令行、备份策略到注意事项全面解析，覆盖逻辑备份、物理备份及点-in-time恢复（PITR）。

              一、备份类型与核心工具

              MySQL 备份分为 逻辑备份 和 物理备份，核心区别如下：

              类型 原理 优点 缺点 适用场景
              逻辑备份 将数据导出为 SQL 语句或文本格式 跨版本兼容、可读性强、支持部分备份 备份/恢复速度慢（尤其大数据量）、锁表风险 小型数据库（<100GB）、跨环境迁移
              物理备份 直接复制数据文件（如 InnoDB 表空间） 备份/恢复速度快、支持增量备份、低锁表 依赖存储引擎、跨版本兼容性差、不可读 大型数据库（>100GB）、高性能恢复需求

              核心备份工具

              • 逻辑备份：mysqldump（官方）、mysqlpump（官方多线程）、mydumper（第三方多线程）。
              • 物理备份：Percona XtraBackup（开源，支持热备）、MySQL Enterprise Backup（官方收费）。
              • 二进制日志（Binlog）：记录所有数据变更，用于点-in-time恢复（PITR）。

              二、推荐备份方案

              根据数据库规模和需求选择方案：

              方案 1：小型数据库（<100GB）—— 逻辑备份（mysqldump）

              优势：简单易用、跨版本兼容、支持单库/单表备份，适合开发环境或小型生产库。

              工具：mysqldump（MySQL 自带，无需额外安装）。

              方案 2：中型数据库（100GB~1TB）—— 多线程逻辑备份（mydumper）

              优势：多线程并行备份，速度远超 mysqldump，支持分块导出、压缩，适合中等规模数据库。

              工具：mydumper（需手动安装，基于 glib 开发）。

              方案 3：大型数据库（>1TB）—— 物理热备（Percona XtraBackup）

              优势：支持 InnoDB 热备（不锁表）、增量备份、压缩，恢复速度快，适合生产环境核心库。

              工具：Percona XtraBackup（开源，Percona 公司开发）。

              方案 4：关键业务（高可用需求）—— 全量+增量+Binlog 组合

              优势：通过“全量备份+增量备份+Binlog”实现任意时间点恢复（PITR），满足 RTO（恢复时间目标）和 RPO（恢复点目标）要求。

              三、具体命令行操作

              场景 1：逻辑备份（mysqldump）

              mysqldump 是 MySQL 官方逻辑备份工具，语法：

              mysqldump [选项] 数据库名 [表名...] > 备份文件.sql

              1. 备份单个数据库（含结构和数据）

              # 备份 db_name 数据库到 backup_db.sql
mysqldump -u 用户名 -p 密码 db_name > backup_db.sql

# 示例（输入密码时隐藏明文）
mysqldump -u root -p db_test > /backup/db_test_$(date +%Y%m%d).sql
# 执行后输入密码：Enter password: ******

              2. 备份多个数据库

              # 备份 db1、db2 到 multi_db.sql
mysqldump -u root -p --databases db1 db2 > multi_db.sql

              3. 备份所有数据库（含系统库）

              # 备份所有库（包括 mysql、information_schema 等）
mysqldump -u root -p --all-databases > all_databases.sql

              4. 备份单表

              # 备份 db_name 库的 table_name 表
mysqldump -u root -p db_name table_name > table_name.sql

              5. 带条件的备份（如按时间过滤数据）

              # 备份 orders 表中 2023 年的数据（需表有 create_time 字段）
mysqldump -u root -p db_name orders --where="create_time >= '2023-01-01'" > orders_2023.sql

              6. 压缩备份（节省空间）

              结合 gzip 压缩备份文件（推荐）：

              mysqldump -u root -p db_name | gzip > db_name.sql.gz

              7. 关键选项说明

              选项 作用
              --single-transaction 对 InnoDB 表使用事务快照备份（不锁表，适合生产环境）
              --routines 备份存储过程和函数
              --triggers 备份触发器
              --events 备份事件调度器
              --hex-blob 二进制数据（如 BLOB）以十六进制格式导出（避免乱码）
              --skip-lock-tables 不锁表（MyISAM 表可能不一致，慎用）

              场景 2：逻辑备份（mydumper，多线程）

              mydumper 是第三方多线程逻辑备份工具，速度比 mysqldump 快 5~10 倍，支持分块导出和压缩。

              1. 安装 mydumper

              • Ubuntu/Debian： 
                sudo apt install mydumper
              • CentOS/RHEL： 
                sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release enable-only tools release
sudo yum install mydumper

              2. 备份单个数据库

              # 多线程备份 db_name（-t 线程数，-c 压缩，-o 输出目录）
mydumper -u root -p 密码 -h localhost -P 3306 \
  -t 4 -c -o /backup/mydumper/db_name \
  -B db_name  # -B 指定数据库名

              3. 备份所有数据库

              mydumper -u root -p 密码 -t 4 -c -o /backup/mydumper/all_databases -a  # -a 备份所有库

              场景 3：物理备份（Percona XtraBackup）

              Percona XtraBackup 支持 InnoDB 热备（不锁表）、增量备份，适合大型数据库。

              1. 安装 Percona XtraBackup

              • Ubuntu/Debian： 
                wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.*.deb
sudo apt update
sudo apt install percona-xtrabackup-24  # MySQL 5.7/8.0 对应版本

              2. 全量备份

              # 备份所有 InnoDB 表到 /backup/xtrabackup/full
xtrabackup --backup \
  --user=root --password=密码 \
  --target-dir=/backup/xtrabackup/full/$(date +%Y%m%d)

              3. 增量备份（基于全量备份）

              # 基于全量备份做增量备份（需指定上次备份目录）
xtrabackup --backup \
  --user=root --password=密码 \
  --target-dir=/backup/xtrabackup/incr/$(date +%Y%m%d) \
  --incremental-basedir=/backup/xtrabackup/full/20231001

              4. 准备备份（恢复前必须步骤）

              备份文件需通过 --prepare 命令整理后才能恢复：

              # 全量备份准备（--apply-log）
xtrabackup --prepare --target-dir=/backup/xtrabackup/full/20231001

# 增量备份准备（合并到全量备份）
xtrabackup --prepare \
  --target-dir=/backup/xtrabackup/full/20231001 \
  --incremental-dir=/backup/xtrabackup/incr/20231002

              场景 4：还原数据库

              1. 逻辑备份还原（mysqldump 生成的 SQL 文件）

              使用 mysql 命令导入 SQL 文件：

              # 还原单个数据库（需先创建空库）
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS db_name;"
mysql -u root -p db_name < backup_db.sql

# 还原所有数据库（直接导入 all_databases.sql）
mysql -u root -p < all_databases.sql

              2. mydumper 备份还原

              使用 myloader（mydumper 配套工具）还原：

              myloader -u root -p 密码 -h localhost -P 3306 \
  -t 4 -d /backup/mydumper/db_name  # -d 指定备份目录

              3. Percona XtraBackup 物理还原

              1. 停止 MySQL 服务： 
                sudo systemctl stop mysql
              2. 清空数据目录（默认 /var/lib/mysql）： 
                sudo rm -rf /var/lib/mysql/*
              3. 复制备份文件到数据目录： 
                sudo cp -r /backup/xtrabackup/full/20231001/* /var/lib/mysql/
              4. 修改权限（MySQL 运行用户，如 mysql:mysql）： 
                sudo chown -R mysql:mysql /var/lib/mysql
              5. 启动 MySQL： 
                sudo systemctl start mysql

              四、备份策略与最佳实践

              1. 备份频率

              • 全量备份：每日/每周（根据数据变更频率，核心库每日）。
              • 增量备份：每小时（基于全量备份，减少备份窗口）。
              • Binlog 备份：实时同步 Binlog 文件到远程存储（如 S3、NAS）。

              2. 备份文件管理

              • 命名规范：包含日期和类型（如 db_test_20231001_full.sql.gz）。
              • 存储位置：本地+异地（如本地 SSD + 远程对象存储），避免单点故障。
              • 保留周期：全量备份保留 7~30 天，增量备份保留 3~7 天，Binlog 保留 7 天。

              3. 验证备份有效性

              • 定期恢复测试：每月随机抽取备份文件恢复到测试环境，验证数据完整性。
              • 检查备份文件大小：异常小的文件可能备份失败（如权限问题）。

              4. 加密与压缩

              • 压缩：用 gzip 或 mydumper 内置压缩（节省存储空间）。
              • 加密：用 openssl 加密备份文件（如 mysqldump ... | openssl enc -aes-256-cbc -salt -pass pass:密钥 > backup.sql.enc）。

              五、注意事项

              1. 锁表风险：

                • mysqldump 默认对 MyISAM 表锁表，InnoDB 表建议加 --single-transaction 选项（事务快照备份，不锁表）。
                • 大表备份时，避免在业务高峰期执行。

              2. 字符集问题：
                备份/还原时指定字符集（如 --default-character-set=utf8mb4），避免乱码：

                mysqldump -u root -p --default-character-set=utf8mb4 db_name > backup.sql
mysql -u root -p --default-character-set=utf8mb4 db_name < backup.sql

              3. 权限问题：
                备份用户需有 SELECT、SHOW VIEW、TRIGGER、LOCK TABLES（非事务表）权限。

              4. Binlog 开启：
                点-in-time恢复需开启 Binlog，在 my.cnf 中配置：

                [mysqld]
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW  # 推荐 ROW 格式（记录行变更）
expire_logs_days = 7  # Binlog 保留 7 天

              六、总结

              • 小型库：用 mysqldump 逻辑备份（简单、兼容），命令示例： 
                mysqldump -u root -p --single-transaction --routines --triggers db_name | gzip > db_name_$(date +%Y%m%d).sql.gz
              • 大型库：用 Percona XtraBackup 物理热备（高速、低影响），结合 Binlog 实现 PITR。
              • 关键业务：全量备份（每日）+ 增量备份（每小时）+ Binlog（实时），确保任意时间点可恢复。

              通过合理选择工具和策略，可最大化保障 MySQL 数据安全与业务连续性。

              到此这篇关于MySQL数据库备份与还原的方案及具体命令行操作的文章就介绍到这了,更多相关MySQL备份与还原内容请搜索就爱读以前的文章或继续浏览下面的相关文章希望大家以后多多支持就爱读！

              您可能感兴趣的文章:

              • mysql 数据库备份和还原方法集锦 推荐
              • MySQL数据库备份和还原的常用命令小结
              • MySQL数据库如何导入导出（备份还原）
              • MySQL使用命令备份和还原数据库
              • mysql数据库太大了如何备份与还原
              • MySQL 数据备份与还原的示例代码
              • php简单备份与还原MySql的方法
              • MySQL 备份还原数据库批处理
              • 简单的MySQL备份与还原方法分享
              mysql数据库双机热备份实现

              mysql数据库双机热备份实现

              这篇文章主要介绍了mysql数据库双机热备份实现方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教 目录mysql数据库双机热备份1.环境准备2.主从复制两台机器同时操作主服务器master配置从服务器slave配置测试同步3.主主复制服务器A配置（192.168.111.136）服务器B配置（192.168.111.137）配置双向复制验证复制状态总结mysql...

              代码 2026-07-07 297686
              Windows共享认证失效导致SQL Server异地备份失败的处理方法

              Windows共享认证失效导致SQL Server异地备份失败的处理方法

              SQL Server异地备份失败,别让SMB认证问题中断你的数据保护,本文手把手教你排查共享连接、删除旧凭据、重新认证,并修复传输脚本,学习如何确保备份文件完整传输到异地服务器,避免数据丢失风险,需要的朋友可以参考下 目录一、示例环境二、问题现象三、问题定位四、处理步骤1.查看当前共享连接2.删除原有共享连接3.重新认证异地共享4.验证异地目录是否可以访问5.验证异地目录写权限6.手工复制备份文件...

              代码 2026-07-07 145618
              Nginx 的Rsync备份服务实战步骤

              Nginx 的Rsync备份服务实战步骤

              本文介绍了如何在Nginx服务器中使用Rsync进行数据备份,包括安装Rsync、配置rsyncd服务、执行实时和定时同步,以及结合Inotify实现文件变化触发备份的实战步骤,感兴趣的朋友一起看看吧 目录1.安装Rsync2.配置Rsync服务3.执行Rsync备份4.结合Inotify实现实时同步5.定时备份在Nginx实战中，Rsync常常用于数据备份和服务间数据同步，尤其是在搭建高可用集群...

              代码 2026-06-23 17037
              macOS 27 终止支持 AFP 协议：苹果 Time Capsule 失去官方备份支持

              macOS 27 终止支持 AFP 协议：苹果 Time Capsule 失去官方备份支持

              就爱读6月11日消息，科技媒体Appleinsider昨日（6月10日）发布博文，报道称在macOS27系统中，苹果公司终止支持AFP协议，导致已停产的TimeCapsule无法继续用于TimeMachine备份。就爱读注：AFP全称为AppleFilingProtocol，是苹果公司开发的专有网络文件共享协议，用于Mac之间及Mac与苹果网络存储设备之间的文件传输。TimeCapsule网络存储...

              前沿 2026-06-11 42020
              太还原了！《剑星 血雨》新女主COS：真有人长这样？

              太还原了！《剑星 血雨》新女主COS：真有人长这样？

              　　《剑星》系列新作《剑星：血雨》正式公开，新女主角Evie迅速成为玩家关注的焦点。近日，博主@JyunaUndomiel分享了一组高还原度的Evie仿妆COS照，还原度极高。 　　官方将Evie设定为娇小身材、短发、娃娃脸，却搭配凌厉的近身格斗风格与贴身战斗服，形成“幼态外表×硬核战力”的反差美学，既酷又富有视觉冲击。 　　相比初代Eve的形象，Evie的中性飒爽造型打破预期，吸引追求新鲜感、厌...

              游戏 2026-06-11 296771
              《皇牌空战8》开发商被战机厂家反向科普?只为1:1还原

              《皇牌空战8》开发商被战机厂家反向科普?只为1:1还原

              　　《皇牌空战8：希孚之翼》已在索尼发布会上正式定档，将于2026年10月2日全球同步登陆Steam、PS5和XboxSeriesX|S平台，Steam国区标准版298元、豪华版398元，预购豪华版可提前三天于9月29日抢先体验。预购玩家还能获得F-14A可操作机体和情怀满满的《皇牌空战Zero：贝尔卡战争》移植版。 　　为了打造系列最真实的空战体验，ProjectAces开发团队可谓下足了血本。...

              游戏 2026-06-11 233549
              Linux命令行参数&环境变量:程序和系统沟通的底层逻辑分析

              Linux命令行参数&环境变量:程序和系统沟通的底层逻辑分析

              了解Linux中main函数参数意义及环境变量作用,探索命令如何通过PATH环境变量找到并运行,解析environ全局变量与getenv函数访问环境变量机制 目录1.什么是环境变量1.最直观的例子：PATH环境变量1.格式：变量名=值2.环境变量长什么样？3.环境变量从哪里来？4.常见的环境变量5.Linux下查看/设置环境变量的命令1.查看所有环境变量2.查看某个环境变量echo$PATH  里...

              代码 2026-06-09 2670
              一文分享MySQL数据库备份恢复脚本

              一文分享MySQL数据库备份恢复脚本

              在日常业务场景,通常情况下,我们不需要进行全库备份,而是只备份需要的数据库,本文分享一个MySQL数据库备份恢复脚本,用于备份和恢复指定的数据库,希望对大家有所帮助 目录备份脚本恢复脚本restore-onedb.sh脚本代码分析本人亲测可用 在日常业务场景，通常情况下，我们不需要进行全库备份，而是只备份需要的数据库。本文分享一个MySQL数据库备份恢复脚本，用于备份和恢复指定的数据库。 备份脚本...

              代码 2026-06-03 30885