06_备份与还原

课程目标

[ ] 了解MySQL常见的备份方式和类型

[ ] 能够使用mysqldump工具(逻辑备份)进行数据库的备份。如全库备份，库级别备份，表级别备份

[ ] 能够使用mysqldump工具+binlog日志实现增量备份

[ ] 能够使用xtrabackup工具(物理备份)对数据库进行全备

---

一、MySQL备份与还原

基本概念

数据库备份是指对数据库中的数据进行复制和存储，以便在数据丢失或损坏时进行恢复。

数据库备份通常包括库、表、索引、配置、日志等关键数据和结构。

数据库备份最重要的为：数据(数据库中存储的数据)

逻辑备份 vs 物理备份

数据库备份的常用方式：逻辑备份与物理备份

方式	逻辑备份	物理备份
内容	备份的是数据库的结构、数据	备份的是数据库文件物理数据文件；日志文件binlog二进制日志；配置文件my.cnf）
工具	常用 `mysqldump`	常用 `xtrabackup`
特点	可读性强，跨平台，可用于部分恢复和迁移	备份速度快，恢复效率高
场景	适合中小型数据库	适合大规模数据库

二进制日志

binlog：二进制日志（Binary Log），可以手工配置log-bin。

这个日志主要负责把用户对数据库的增、删、改（DML语言）事务型的SQL语句记录在binlog日志中，方便未来对数据进行查找与恢复！

binlog日志它只记录对数据有影响的操作语句，mysql8默认现在就是开启的，可以通过my.cnf进对关闭。

它的文件名称默认为：binlog.000001 binlog.000002 binlog.000003 ... 这些文件它都是二进制文件，不能直接cat查看

binlog.index 记录当前binlog日志文件是谁，最后一行就是现在的，此文件可以用cat来查看

root@localhost [test3]> show variables like 'log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /opt/3306/data/binlog       |
| log_bin_index                   | /opt/3306/data/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.00 sec)

数据库备份核心

数据库：可以简单理解为一堆物理文件的集合 => 数据 + 日志 + 配置

① 数据文件 /usr/local/mysql/data

② 配置文件 => /etc/my.cnf

③ 日志文件（主要是二进制日志文件） => binlog日志（MySQL8以后默认开启） => 记录对数据库的增删改操作

工具选型

逻辑备份：

适用于MyISAM 和 InnoDB，可以通过工具如 mysqldump 导出表结构和数据为SQL语句。备份文件为文本格式，便于跨平台迁移和恢复。

InnoDB 支持事务和外键，备份时需要注意数据一致性，可以用 --single-transaction 实现无锁备份。

MyISAM引擎进行mysqldump时，它会选锁表再进行备份。

物理备份：

MyISAM：MySQL5.7及之前版本可以简单复制数据库文件（如 .frm、.MYI文件、.MYD 文件）进行物理备份，MySQL8.0引入了更多复杂的功能，导致摒弃了这种操作。

InnoDB：物理备份需要包括表数据、日志文件等，适用工具如 xtrabackup，确保数据的一致性和完整性，特别是在大规模数据环境下。

二、MySQL逻辑备份

概述

在进⾏数据库数据逻辑备份操作过程中，主要会运⽤mysqldump逻辑备份⼯具，可以实现本地或远程的数据备份；

利⽤mysqldump进⾏逻辑备份数据时，主要的备份逻辑是将建库、建表、数据插⼊语句信息导出，实现数据的备份操作；

基于mysqldump备份数据的逻辑原理，对于数据量⽐较⼩的场景（单表数据⾏百万以内），mysqldump备份⼯具做备份会更适合些；

在跨平台或跨版本进⾏数据库数据信息迁移时，mysqldump备份⼯具做备份也会⽐较适合，可以避免物理备份的兼容性问题；

# 语法
mysqldump -u数据库用户名  -p数据库密码  [选项] >/路径信息/数据库备份⽂件.sql

# 选项
--help                 查看帮助
-A                     所有的数据库
-B                     表示备份指定数据库
-F                     开始备份前刷新日志（二进制日志）binlog.000001 => binlog.000002
--single-transaction   适用InnoDB引擎，保证一致性，服务可用性，导出时不锁表   优化选项
-R                     数据库存储过程备份
-E                     数据库事件信息备份   create event
--triggers             触发器信息备份
--source-data =1|2     可以实现⾃动记录位置点信息  1位置命令不会注释   2位置命令注释

-P, --port             指定端口号，默认是 3306
-S, --socket           指定本地连接的socket文件路径
-h, --host             指定数据库主机名或IP，不指定时默认为 localhost
--no-data (-d)         只导出表结构，不导出数据

-- 准备数据
create database db_itheima default charset=utf8mb4;

use db_itheima;
create table tb_student(
  id int not null auto_increment,
  name varchar(20),
  age tinyint unsigned default 0,
  gender enum('male','female'),
  subject enum('ui','java','bigdata','yunwei'),
  primary key(id)
) engine=innodb default charset=utf8mb4;

insert into tb_student values (null,'刘备',33,'male','java');
insert into tb_student values (null,'关羽',32,'male','yunwei');
insert into tb_student values (null,'张飞',30,'male','yunwei');
insert into tb_student values (null,'貂蝉',18,'female','ui');
insert into tb_student values (null,'大乔',18,'female','ui');

-- 存储过程 ----------------------------------------------------------------------------------------
DELIMITER //
CREATE PROCEDURE SimpleAdd()
BEGIN    
SELECT 1 + 1 AS result;
END //
DELIMITER ;

-- 事件
-- 在 2026-06-01 00:00:00 时向日志表插入一条记录
CREATE EVENT one_time_event
ON SCHEDULE AT '2026-06-01 00:00:00'
DO
    INSERT INTO event_log(message, created_at) VALUES ('一次性事件触发', NOW());

-- 每隔 1 小时执行一次
CREATE EVENT hourly_event
ON SCHEDULE EVERY 1 HOUR
DO
    DELETE FROM temp_data WHERE expire_time < NOW();

-- 触发器
DELIMITER //

CREATE TRIGGER trg_users_before_update
BEFORE UPDATE ON 表名
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END //

DELIMITER ;
----------------------------------------------------------------------------------------    

-- 准备备份目录
[root@mysql-node ~]# mkdir -p /data/sqlbak

注意事项：

修改配置前先备份原文件，尤其是 SSH、Nginx、数据库和 Kubernetes 生产配置。

表级备份与还原

备份

案例：把db_itheima数据库中的tb_student数据表进行备份

# 创建导出的sql存储
[root@node3 /opt/3306/data]# mkdir /sqldata

# 导出db_itheima库下面的students表结构和数据
[root@node3 /opt/3306/data]# mysqldump -uroot -pAa123456. db_itheima students >/sqldata/students.sql 2>/dev/null

命令作用：

创建目录 /sqldata；父目录必须已存在，若同名目录已存在则命令会报错。

执行结果：

成功后目标目录会存在；可用 ls -ld 目录名 查看目录权限和属主。

关键参数：

注意事项：


再导入数据。

+----------------------+
+----------------------+
+----------------------+
+------+--------+------+--------+--------+--------+
+------+--------+------+--------+--------+--------+
|    1 | 刘备   |   18 | male   | 100.00 |      2 |
|    2 | 貂蝉   |   18 | female |  99.00 |      1 |
|    3 | 赵云   |   18 | male   |  98.00 |      3 |
|    4 | 关羽   |   18 | male   |  96.00 |      3 |
|    5 | 大乔   |   18 | female |  97.00 |      1 |
|    6 | 测试   |   20 | male   |  99.00 |      5 |
+------+--------+------+--------+--------+--------+

-------------------------------------------------------------------------------

关键参数：

注意事项：


**关键参数：**

**注意事项：**



增量备份是指在`全量备份`的基础上，只备份自上次（？）备份以来发生变化的数据（新增、修改或删除的内容）。

相比于全量备份，


增量备份常用于提高备份效率，特别是在数据量大且频繁更新的场景中。


每周会做一次全量备份，以后每天就是增量备份（只备份增加的那一部分数据）


第一步：先准备数据（前提）

第二步：开启二进制日志(binlog日志)，然后做全量备份（全库备份）

第三步：继续对数据库进行增删改操作（还未备份）

第四步：突然发生了硬件故障，数据库丢失了

第五步：备份二进制日志


---


**注意事项：**

+---------------+----------+--------------+------------------+-------------------+
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |      988 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+




确认at位置

注意事项：








**效率升级：**并行操作加速备份恢复，支持流式传输；


**可靠性增强：**优化故障恢复，保障数据完整。


官方下载地址：[www.percona.com/](http://www.percona.com/)


如果稍微展开成三个步骤，就是：



下载地址：https://www.percona.com/downloads

![](/assets/feishu-images/10bc96f796adb37283b92826.png)

选择的xtrabackup工具的版本一定要和你要备份的mysql的版本是一样或接近。

注意事项：

备份

灾难

恢复

https://docs.percona.com/percona-xtrabackup/8.0/privileges.html#privileges-needed


**如果有下面的报错，则才要去解决**

![](/assets/feishu-images/10f539ad0564aca0535a0752.png)


解决方案：

方案1：把你的套接字文件创建一个软链接，放置于/var/lib/mysql/mysql.sock文件中（不推荐）

注意事项：


**常见问题**

**1、导出失败，再次执行时报错**

![](/assets/feishu-images/71d957e5d8e548b05974b1f5.png)



预备阶段，把备份这段时间内产生的日志，整合到全量备份中


**注意事项：**

注意事项：

恢复数据时，一定要记得更改/export/server/mysql/data目录下的文件拥有者以及所属组权限，否则mysql无法启动


**注意事项：**



启动MySQL服务，进行验证。



+--------------------+
+--------------------+
+--------------------+

注意事项：

本文由飞书云文档同步生成。涉及命令、SQL、配置示例时，请以飞书源文档和实际环境执行结果为准。