阶段六：MySQL8备份与还原

学习目标

引言

俗话说"手里有粮，心里不慌"，这句话应用在数据库运维领域同样有效。

对于重要的数据做好备份，是我们每个系统运维以及数据库运维的重要职责。

备份只是一种手段，我们最终目的是当数据出现问题时能够及时的通过备份进行恢复（应急演练）。

课程目标

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

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

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

[ ] 能够使用xtrabackup工具对数据库进行全备

MySQL备份与还原

基本概念

数据库备份是指对数据库中的数据进行复制和存储，以便在数据丢失或损坏时进行恢复。数据库备份通常包括表、记录、索引、配置等关键数据和结构。

逻辑备份 vs 物理备份

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

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

二进制日志

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

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

思考：binlog文件，没有涉及对查询的记录，为什么？

库表概念

☆ 视图

比较特殊的情况：数据库中除了数据库、数据表以外，还包含视图、存储过程。

添加视图（虚拟表），底层就是一个SQL语句（select查询语句）。作用：简化SQL查询，保护数据

employee

id name age dept salary薪资

create view vw_employee as select id,name,age,dept from employee;

☆ 存储过程

存储过程（开发需要掌握，运维作为了解）：

存储过程类似Shell脚本中的函数，相当于把某些功能封装起来。

以后需要使用的时候直接通过call 存储过程名称()

stored procedure ：存储过程

-- 存储过程
DELIMITER //
CREATE PROCEDURE sp_insert_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    START TRANSACTION;
    WHILE i <= 2000000 DO
        INSERT INTO simple_table (name, age)
        VALUES (CONCAT('User', i), FLOOR(18 + (RAND() * 42)));
        SET i = i + 1;
    END WHILE;
    COMMIT;
END //
DELIMITER ;

-- 查看当前存储过程
SHOW PROCEDURE STATUS;

数据库备份核心

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

① 数据文件 /export/server/mysql/data

② 配置文件 => my.cnf (mysql --help 可以查看加载顺序)

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

4、工具选型

逻辑备份：

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

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

物理备份：

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

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

若数据库同时包含 InnoDB 和 MyISAM 表，可以：

1、用 XtraBackup 备份 InnoDB 数据。

2、配合 mysqldump --single-transaction 导出 MyISAM 表（需加锁）。

3、合并两者备份文件以完整恢复。

MySQL逻辑备份

mysqldump基本语法

强调：mysqldump不是SQL语句，而是一个MySQL二进制命令。所以在终端执行！！！

[root@mysql-node ~]# mysqldump --help

查看一个命令，是存放在了哪里，有两个命令，whereis 和 which 都可以：

[root@mysql-node \~]# whereis mysqldump

mysqldump: /export/server/mysql/bin/mysqldump

[root@mysql-node \~]# which mysqldump

/export/server/mysql/bin/mysqldump

mysqldump逻辑备份图解：

# 表级别备份
mysqldump [OPTIONS] DB1 [Table1]

# 库级别备份
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

# 全库级别备份
mysqldump [OPTIONS] --all-databases [OPTIONS]

0、准备数据

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');
exit;

准备备份目录

# 创建目录
[root@mysql-node ~]# mkdir -p /tmp/sqlbak

命令作用：

创建目录 /tmp/sqlbak；-p 会同时创建缺失的父目录，并且目录已存在时不报错。

执行结果：

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

关键参数：

注意事项：

binlog.000001 ...


**注意事项：**



| **常用选项** | **描述说明** |
|---|---|
| --flush-privileges | 备份包含mysql数据库时刷新授权表 => 刷新用户和授权信息 |
| --single-transaction | 适用InnoDB引擎，保证一致性，服务可用性 |

案例：全库备份实现




如果需要备份存储过程，需要添加--routines

案例：全库还原实现


**注意事项：**



需要的权限：

![](/assets/feishu-images/1d01e34464cfc72ad36ad780.png)

![](/assets/feishu-images/225d26f456b20b909ddc8c9a.png)

参考地址：https://docs.percona.com/percona-xtrabackup/8.0/privileges.html


进入到MySQL终端（先登录）：

...(此处省略)

注意事项：


**常见问题**

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

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



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


**注意事项：**



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

注意事项：


**命令拆解：**

| 命令部分 | 重要程度 | 详细解释 |
|---|---|---|
| `-p` | 【核心】 | `-p` 的含义取决于当前命令，不能直接套用 Docker 端口映射解释。 |

**注意事项：**



**问题1：产生Error，往前找几行，一般就可以了**

![](/assets/feishu-images/1010d4e465f7a402695fa5dc.png)

解决方案：遇到问题时，往前或者往后预读1-2行，往往都能找到问题！！！

**问题2：喜欢按照自己想法去修改文件，如/etc/my.cnf文件**

[mysqld]
port=3306
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
character_set_server=utf8
collation-server=utf8_unicode_ci

slow_query_log=1
slow_query_log_file=/export/server/mysql/logs/mysql-slow.log
long_query_time=1
log_queries_not_using_indexes=0

server-id=10
log_error=/export/server/mysql/logs/error.log
log-bin=/export/server/mysql/data/binlog
binlog_format=statement
default_authentication_plugin=mysql_native_password

注意事项：

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