当前位置： 首页 > web >正文

MySQL 备份与恢复

web 2025/5/13 11:52:03

目录

一、MySQL 数据库备份概述

1. 数据备份的重要性

2. 数据库备份类型

（1）从物理与逻辑的角度分类

（2）从数据库的备份策略角度分类

3. 常见的备份方法

（1）物理冷备份

（2）专用备份工具 mysqldump 或 mysqlhotcopy

1. mysqldump

（3）通过启用二进制日志进行增量备份

（4）通过第三方工具备份

二、数据库完全备份操作

1. 物理冷备份与恢复

2. mysqldump 备份与恢复

3. MySQL 增量备份与恢复

（1）增量备份概述

（2）二进制日志对备份的意义与配置

（3）MySQL 增量配置与恢复

4. 指定备份策略的思路

三、 扩展： MySQL的 GTID 和 XtraBackup

1. GTOD

（1）概述

（2）配置

2. XtraBackup

（1）概述

（2）配置

一、MySQL 数据库备份概述

1. 数据备份的重要性

  • 防止数据丢失：应对硬件故障、人为误操作、恶意攻击等意外场景。

  • 灾难恢复：在数据库崩溃或服务器宕机时快速恢复业务。

  • 数据迁移：备份可用于跨服务器、跨环境的数据迁移。

  • 合规要求：满足数据安全法规（如 GDPR）的存档和审计需求。

2. 数据库备份类型

（1）从物理与逻辑的角度分类

▪ 物理备份

  • 冷备份：停止数据库服务后直接复制数据文件（操作简单但影响业务）

  • 热备份：数据库运行时直接复制文件（需InnoDB等支持事务的引擎，依赖数据库日志文件）

  • 温备份：数据库运行时锁定表后备份（允许读不允许写）

  • 特点
    - 速度快，适合大数据量
    - 依赖存储引擎（如 InnoDB/MyISAM）

▪ 逻辑备份：通过导出SQL语句或CSV文件备份（如mysqldump），是对数据库逻辑组件（如表、库等）的备份。

  • 特点
    -- 跨版本/跨平台兼容性好
    - 恢复速度较慢，需逐条执行 SQL
（2）从数据库的备份策略角度分类
类型说明特点
完全备份备份整个数据库的所有数据。- 恢复简单
- 占用存储空间大
差异备份备份自上次完全备份后发生变化的数据，每次都要基于完全备份做备份。- 存储空间中等
- 恢复需完全备份+差异备份
增量备份备份自上次任意类型备份（完全/增量）后发生变化的数据。- 存储空间小
- 恢复需完全备份+所有增量备份

3. 常见的备份方法

（1）物理冷备份

  • 操作步骤：

    1. 停止MySQL服务

    2. 用 tar 打包数据文件夹（data）。

    3. 重启服务

  • 适用场景：小型数据库、允许停机维护

（2）专用备份工具 mysqldump 或 mysqlhotcopy
1. mysqldump

概述
mysqldump 是 MySQL 官方提供的逻辑备份工具，通过生成 SQL 格式的文本文件实现备份，支持所有存储引擎（如 InnoDB、MyISAM 等）。备份文件包含重建数据库所需的 SQL 语句（如 CREATE TABLE 和 INSERT），适用于跨版本、跨平台迁移或小型数据库的备份。

核心特性

  • 逻辑备份：生成可读的 SQL 文件，便于手动修改或审计。

  • 灵活性：支持备份单个表、数据库或整个服务器。

  • 事务安全：对 InnoDB 表可使用 --single-transaction 选项，避免锁表。

  • 兼容性：备份文件可在不同 MySQL 版本或架构上恢复。

2. mysqlhotcopy 

概述
mysqlhotcopy 是一个 Perl 脚本，专为 MyISAM 存储引擎设计的物理备份工具。它通过直接复制数据库文件（.frm、.MYD、.MYI）实现快速备份，需在服务器本地运行，且依赖文件系统权限。

核心特性

  • 物理备份：直接复制文件，速度极快。

  • 最小化锁：短暂锁定表或使用 FLUSH TABLES 确保一致性。

  • 简单恢复：只需将文件复制回数据库目录即可

（3）通过启用二进制日志进行增量备份

  • 核心原理：通过binlog记录所有写操作（DDL/DML）

  • 操作流程：

    1. 启用二进制日志（配置my.cnf中log-bin=mysql-bin）

    2. 定期完全备份（如每周）

    3. 每天用mysqlbinlog工具导出增量日志

► 关键命令：PURGE BINARY LOGS BEFORE '2023-10-01 00:00:00'（日志清理）

（4）通过第三方工具备份
工具特点
Percona XtraBackup开源热备工具，支持InnoDB增量备份
mydumper/myloader并行备份与恢复，速度优于mysqldump
云服务（如AWS RDS）自动备份+时间点恢复（PITR），无需手动管理

二、数据库完全备份操作

1. 物理冷备份与恢复

1. 备份数据库
--创建备份存储位置路径，使用 tar 创建备份文件
systemctl stop mysqld  #关闭MySQL
mkdir /backup
tar zcf /backup/mysql-$(date +%F).tar.gz /usr/local/mysql/data   #打包数据文件
ls -l /backup2.恢复数据库
--把数据库文件转移到bak目录下，模拟故障，然后在验证恢复
mkdir bak
mv /usr/local/mysql/data/  /root/bak/
mkdir restore
tar zxf /backup/mysql-时间.tar.gz -C restore/    #解压打包数据到restore目录
mv restore/usr/local/mysql/data /usr/local/mysql/   #恢复
systemctl start mysqld

2. mysqldump 备份与恢复

备份命令格式：

        格式1：备份指定库中的部分表

                mysqldump [选项]  库名 [表名1] [表名2]  ... >/备份路径/备份文件名

        格式2：备份一个或多个完整的库（包括库中的表）

                mysqldump [选项]  --databases 库名1 [库名2] ...

        格式3：备份MySQL 服务器中的所有库

                mysqldump [选项]  --all-databases >/备份路径/备份文件名

示例：
mysqldump -u root -p mysql user > mysql-user.sql    #备份mysql库中的user表
mysqldump -u root -p --databases test > test.sql    #备份test库
mysqldump -u root -p --opt --all-databases > all-data.sql  #备份整个服务器
  •  查看备份文件：导出的SQL脚本是文本文件，其中“/*...*” 部分或“--” 开头的行表示注释信息；使用grep、less、cat 等文本工具可以查看脚本内容。 
    cat  脚本文件
grep -v "^--" test.sql | grep -v "^/" | grep -v "^$"   #过滤注释信息查看脚本文件信息

恢复命令格式：

        mysql  [选项] [库名] [表名] </备份路径/备份文件名

#注意:当备份文件中只包含表的备份，而不包含创建库的语句时，执行导入操作时必须指定库名，且目标库必须存在。
示例：把备份文件mysql-user.sql中的表导入test库，“-e”选项是用于指定连接MySQL后执行的命令
mysql -u root -p test < mysql-user.sql   #导入mysql -u root -p -e 'show tables from test;'  //验证导入结果#若备份文件中已经包含完整库的信息，则执行导入操作时无序指定库名
示例：备份文件test。sql 恢复 test 库
mysql -u root -p -e "show databases;"  //检测数据库状态
mysql -u root -p < ~/test.sql   //执行导入恢复操作注：除了mysql 命令结合“<”恢复数据外，还可以使用 source 命令恢复数据
--如下
mysql> source /root/test.sql

3. MySQL 增量备份与恢复

（1）增量备份概述

  • 增量备份是指仅备份自上次全量备份或增量备份以来发生变化的数据部分。相比全量备份，它节省存储空间和备份时间，但恢复时需要依赖完整的全量备份和所有增量备份链。

  • 需要上次完全备份及完全备份之后所有的增量备份才能恢复。通过二进制日志（binary logs）间接实现增量备份。

（2）二进制日志对备份的意义与配置

   二进制日志（Binary Log）的作用

  • 关键用途：

    • 支持时间点恢复（Point-in-Time Recovery, PITR）：通过重放二进制日志，将数据库恢复到任意时刻的状态。

    • 主从复制的核心组件：从库通过读取主库的二进制日志实现数据同步。

 配置：

  1. 启用二进制日志   在 MySQL 配置文件（my.cnf 或 my.ini）中设置： 
    [mysqld] 
server-id = 1     #为MySQL实例分配一个唯一的服务器标识符
log_bin = /usr/local/mysql/data/mysql-bin  # 二进制日志存储路径
binlog_format = MIXED                # 定义二进制日志记录格式为混合模式--可选
expire_logs_days = 7                # 自动清理 7 天前的日志
max_binlog_size = 100M              # 单个日志文件大小限制
sync_binlog = 1                     # 每次事务提交同步日志到磁盘（确保一致性）
  2. 重启 MySQL 服务 
    systemctl restart mysql
  3. 验证配置 
    SHOW VARIABLES LIKE 'log_bin%';     -- 确认二进制日志已启用
SHOW BINARY LOGS;                   -- 查看当前所有二进制日志文件
（3）MySQL 增量配置与恢复

  • 增量恢复的方式：

    • 一般恢复： 
      命令格式：
mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p 密码

    • 基于位置的恢复：

      命令格式1：恢复数据到指定位置
mysqlbinlog --stop-position='操作 id' 二进制日志 |mysql -u 用户名 -p 密码
命令格式2：从指定的位置开始恢复数据
mysqlbinlog --start-position='操作 id' 二进制日志 |mysql -u 用户名 -p 密码

    • 基于时间点的恢复：

      格式1： 从日志开头在截止到某个时间点的恢复
mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码格式2：从某个时间点到日志结尾的恢复
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码格式3：从某个时间点到某个时间点的恢复
mysqlbinlog [--no-defaults]  --start-datetime='年-月-日 小时:分钟:秒'
--stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码
  • 示例 
    #前提准备完整的数据库、表、录入信息---进行一次完全备份
# 使用 mysqldump 全量备份
mysqldump -u root -p --all-databases 库名 表名 > /路径/文件名-$(date +%F).sql# 生成新的二进制日志
mysqladmin -uroot -p flush-logs--增量备份（继续录入新的数据信息之后做增量）
# 刷新的二进制日志
mysqladmin -uroot -p flush-logs
#检查备份信息
ls -l /usr/local/mysql/data/mysql-bin.*--恢复(先进行完全备份恢复，再恢复增量)
mysql -uroot -p 表名 < /路径/文件名.sql
#
#一般恢复
mysqlbinlog --no-defaults /目录路径/目标二进制日志文件 | mysql -u root -p#基于位置
mysqlbinlog --no-defaults /目录路径/目标二进制日志文件  //查看二进制日志文件确定恢复的位置
mysqlbinlog --no-defaults --stop-position='停止位置编号' /目录路径/目标二进制日志文件 | mysql -u root -p    //用停止位置（位置都看“ at ”）恢复
mysqlbinlog --no-defaults --start-position='开始位置编号' /目录路径/目标二进制日志文件 | mysql -u root -p    //用停止位置（位置都看“ at ”）恢复#基于时间点
mysqlbinlog --no-defaults /目录路径/目标二进制日志文件        //确定恢复终点时间mysqlbinlog --start-datetime="2024-01-01 00:00:00" 或 --stop-datetime="2024-01-01 12:00:00" /目录路径/目标二进制日志文件 | mysql  -uroot -p

4. 指定备份策略的思路

核心原则：结合全量备份、增量备份与二进制日志，构建多级保护。

  1. 全量备份：每周日凌晨执行一次全量备份（使用 XtraBackup）。

  2. 增量备份：每日凌晨执行增量备份（基于前一天的备份）。

  3. 二进制日志：

    • 启用二进制日志并保留 7 天。

    • 每小时刷新日志（FLUSH BINARY LOGS）或按日志大小滚动。

  4. 备份存储：

    • 全量备份异地存储，增量备份本地+异地。

    • 定期清理过期备份（如保留 1 个月内的全量备份）。

  5. 验证与监控：

    • 定期模拟恢复流程，验证备份有效性。

    • 监控备份任务状态及存储空间。

三、 扩展： MySQL的 GTID 和 XtraBackup

1. GTOD

（1）概述

GTID（Global Transaction Identifier） 是 MySQL 引入的全局事务唯一标识机制，用于简化主从复制的配置和管理。

  • 核心作用：

    • 每个事务在提交时被分配一个全局唯一的 GTID（格式：server_uuid:transaction_id）。

    • 从库通过 GTID 自动定位需要同步的事务，无需手动指定 MASTER_LOG_FILE 和 MASTER_LOG_POS。

  • 优势：

    • 简化主从切换和故障恢复。

    • 确保主从数据一致性，避免日志偏移量错误。

（2）配置
--开启GTID
vim /etc/my.cnf
[mysqld]
添加：gtid_mode=ONenforce_gtid_consistency=ON
--重启MySQL
systemctl restart mysqld
--验证配置
mysql -uroot -ppwd123
mysql> show global variables like 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+
1 row in set (0.01 sec)--创建基本测试库
mysql> reset master;    #初始化master，会清楚所有binlog和gtid的信息
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |      157 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)--添加数据
mysql> create database test;
mysql> use test;
mysql> create table user(user_name char(10),user_pass char(70));
mysql> insert into user values ('zhangsan','111');
mysql> insert into user values ('lisi','222');
mysql> insert into user values ('wangwu','222');
mysql> select * from user;
+-----------+-----------+
| user_name | user_pass |
+-----------+-----------+
| zhangsan  | 111       |
| lisi      | 222       |
| wangwu    | 222       |
+-----------+-----------+
3 rows in set (0.00 sec)mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 |     1424 |              |                  | 483ef70f-02fb-11f0-af66-000c29a96e86:1-5 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
--全量备份
mysqldump -u root -p --databases test > test.sql  #备份test数据库到test.sql
grep -i gtid test.sql   #在 test.sql 文件中搜索包含 “gtid”（不区分大小写)
--插入新数据
mysql -uroot -p
mysql> use test;
mysql> insert into user values ('zhaoliu','3332');
mysql> insert into user values ('xiaoqi','333');
mysql> select * from test.user;
+-----------+-----------+
| user_name | user_pass |
+-----------+-----------+
| zhangsan  | 111       |
| lisi      | 222       |
| wangwu    | 222       |
| zhaoliu   | 3332      |
| xiaoqi    | 333       |
+-----------+-----------+
5 rows in set (0.00 sec)mysql> show master status;  //验证查看效果
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 |     2000 |              |                  | 483ef70f-02fb-11f0-af66-000c29a96e86:1-7 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)--模拟数据误删除
mysql> drop database test;
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 |     2181 |              |                  | 483ef70f-02fb-11f0-af66-000c29a96e86:1-8 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)--导出增量数据
mysqlbinlog --include-gtids='483ef70f-02fb-11f0-af66-000c29a96e86:3-7' /usr/local/mysql/data/binlog.000001 > /mysqlbak.sql--恢复全量
mysql -u root -p -e "reset master;"
mysql -uroot -p < test.sql
mysql -uroot -p -e 'select * from test.user;'
---恢复增量
mysql -uroot -p < mysqlbak.sql
mysql -uroot -p -e 'select * from test.user;'

2. XtraBackup

（1）概述

Percona XtraBackup 是 MySQL 开源物理备份工具，专为 InnoDB/XtraDB 设计，支持热备份（备份期间不影响数据库读写）。

  • 核心特性：

    • 热备份：无需锁表，备份期间数据库正常提供服务。

    • 增量备份：仅备份自上次全量以来的变化数据，节省存储和带宽。

    • 压缩与加密：支持备份文件压缩和加密。

    • 快速恢复：直接复制文件恢复，速度远快于逻辑备份工具。

  • 适用场景：

    • 大型 InnoDB 数据库的备份与恢复。

    • 高可用架构（如主从复制、Galera Cluster）的备份支持

（2）配置

xtrabackup官方网站：http://downloads.percona.com/dowmloads/....

安装xtrabackup
--准备源码包
tar xzf percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17.tar.gz 
mv percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17 /usr/local/xtrabackup
vim /etc/profileexport PATH=$PATH:/usr/local/xtrabackup/bin
source /etc/profile安装qpress
tar xf qpress-11-linux-x64.tar注意：恢复数据要先关闭MySQL，清理数据存储目录！！！！！！！完整备份与恢复
--备份
bakdir="/backup/fullbackups/$(date '+%F')"
mkdir -p $ bakdir  //生成文件夹  ##--backup  备份  --compress 压缩xtrabackup --defaults-file=/etc/my.cnf --user=root --password=pwd123 --backup --compress --target-dir=$bakdir--解压备份数据
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=pwd123 --decompress --target-dir=$bakdir--准备备份文件以供恢复
xtrabackup --prepare --target-dir=$bakdir
--恢复数据
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=pwd123 --copy-back --target-dir=$bakdir#修改权限，替换成MySQL的
chown -R mysql:mysql /usr/local/mysql/data/mysql
#启动MySQL
systemctl start mysqld增量备份与恢复
--备份
fulldir="/backup/fullbackups/$(date '+%F')"
incdir="/backup/incrementalbackups/$(date '+%F')"
mkdir -p $fulldir
mkdir -p $incdir --准备基础数据进行全量备份
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=pwd123 --backup --compress --target-dir=$fulldir--插入新数据后进行增量备份
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=pwd123 --backup --compress --target-dir=$incdir --incremental-basedir=$fulldir--恢复
--解压备份数据
xtrabackup --defaults-file=/etc/my.cnf --user=root --password= --decompress --target-dir=$fulldir
xtrabackup --defaults-file=/etc/my.cnf --user=root --password= --decompress --target-dir=$incdir --准备数据
xtrabackup --prepare --apply-log-only --target-dir=$fulldir 
--把增量备份的数据合并到完整备份里面
xtrabackup --prepare --apply-log-only --target-dir=$fulldir --incremental-dir=$incdir--完整重放日志
xtrabackup --prepare --target-dir=$fulldir --恢复数据
xtrabackup --defaults-file=/etc/my.cnf --user=root --password= --copy-back --target-dir=$fulldir--修改权限，启动MySQL
chown -R mysql:mysql /usr/local/mysql/data
systemctl start mysqld

http://www.xdnf.cn/news/5790.html

相关文章：

  • 45、跳跃游戏Ⅱ
  • JavaScript双问号操作符（??）详解，解决使用 || 时因类型转换带来的问题
  • 消息队列RocketMQ-docker部署保姆级教程（从0到1）（2）
  • 16.three官方示例+编辑器+AI快速学习webgl_buffergeometry_lines_indexed
  • apt 软件源与 Docker 镜像源
  • Westlake-Omni 情感端音频生成式输出模型
  • 软考高分备考秘籍：综合知识、案例分析、论文全攻略
  • 如何使用VBA宏高效操作Word文档中的表格(对齐与样式)
  • 六、STM32 HAL库回调机制详解：从设计原理到实战应用
  • nginx-整合modsecurity做waf
  • Ubuntu 22初始配置（root、ssh）
  • 航电系统之电传飞行控制系统篇
  • IDR方程迭代求解算法介绍与比较
  • Ollama+OpenWebUI+docker完整版部署，附带软件下载链接，配置+中文汉化+docker源，适合内网部署，可以局域网使用
  • Java 线程的堆栈跟踪信息
  • 《Python星球日记》 第62天：图像方向综合项目（猫狗分类）
  • Java自动化测试
  • 2025年5月13日 奇门遁甲与股市
  • 学习笔记：黑马程序员JavaWeb开发教程(2025.4.3)
  • 麒麟 v10 卸载podman
  • 【大模型MCP协议】MCP官方文档（Model Context Protocol）一、开始——1. 介绍
  • pythonocc 拉伸特征
  • C语言 第六章 结构体（3）
  • 0前言（文章体系）
  • 数字滤波器应用介绍
  • 流体力学绪论（期末复习）
  • 【android bluetooth 框架分析 02】【Module详解 13】【CounterMetrics 模块介绍】
  • 继承关系下创建对象的具体流程
  • 生活破破烂烂，AI 缝缝补补（附提示词）
  • 进程间的通信