序言：  本次基于mysql8.0.40来给大家做数据库的备份的实用技巧和思路！对于mysql基础的部分后续我会节选部分给大家讲解，本篇文章适合有一定数据库基础的小伙伴看。

         

目录

一、MySQL备份概述

1、关于数据保存你要知道

2、到底要备份什么

备份什么

MySQL体系结构（MySQL => DBMS软件到底是由哪些层构成的）

 存储引擎层（MyISAM与InnoDB引擎）

 存储层（数据文件是如何进行存储的）

 日志文件（MySQL中我们需要了解哪些日志）

3、备份过程须考虑的因素

4、备份的类型

重点掌握以下几种备份方式：

逻辑备份

物理备份

在线热备（数据冗余、AB复制、主从复制）

5、备份工具

㈠ 社区版安装包中的备份工具

① mysqldump(逻辑备份，只能全量备份）

② mysqlhotcopy(物理备份工具）

㈡ 企业版安装包中的备份工具

㈢ 第三方备份工具

① XtraBackup和innobackupex(物理备份）

② mydumper(逻辑备份，备份SQL语句）

6、备份方法

三、MySQL逻辑备份

1、mysqldump基本备份

2、mysqldump基本语法

2.1、mysqldump表级备份与还原

 备份

还原( 利用< 或者 source )

2.2、mysqldump库级备份与还原

 备份

还原

2.3、mysqldump全库级备份

备份

还原

总结

四、mysqldump + binlog实现增量备份

1、核心思路

2、增量备份步骤

3、增量备份的具体实践

小结

补充：逻辑导入导出（了解）

五、MySQL的物理备份

1、Xtrabackup概述

2、Xtrabackup优点

3、Xtrabackup软件安装

4、创建备份用户并授权

5、Xtrabackup全量备份与恢复

具体实施方案：

第一步：创建全量备份

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

第三步：模拟数据库故障

第四步：快速的恢复数据库中的数据

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

第六步：启动MySQL，测试其是否正常

6、Xtrabackup增量备份与恢复

常见问题说明

7、xtrabackup知识点补充

Tips：

一、MySQL备份概述

1、关于数据保存你要知道

思考：备份和冗余有什么区别？

备份： 能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。

冗余： 数据有多份冗余，但不等同备份，只能防止机械故障带来的数据丢失，例如主备模式、数据库集群。

2、到底要备份什么

备份什么

数据库：一堆物理文件的集合；日志文件(二进制日志)+数据文件+配置文件

所以需要备份：① 数据文件；② 配置文件 => my.cnf；③ 日志文件（主要是二进制日志文件）

MySQL体系结构（MySQL => DBMS软件到底是由哪些层构成的）

 存储引擎层（MyISAM与InnoDB引擎）

存储引擎层：简单来说，就是数据的存储方式。在MySQL中，我们可以使用show engines查看当前数据库版本支持哪些引擎，常见的数据存储引擎：InnoDB、MyISAM、NDB…

常见面试题：请简述MySQL的MyISAM引擎与InnoDB引擎的区别

1. ① MyISAM引擎：擅长数据的查询，支持全文索引
2. ② InnoDB引擎：Supports transactions, row-level locking, and foreign keys，支持事务处理、行级锁、支持外键。

 存储层（数据文件是如何进行存储的）

问题：存储引擎到底是如何保存数据文件的？

mysql> create database db1 default charset=utf8;

我能不能不进入MySQL终端，然后在命令行中直接创建一个数据库呢？-e选项即可。

当数据库创建完毕后，查看对应的 mysql 的文件夹：

db.opt，存放内容为数据库的编码格式。

• MyISAM引擎(非聚集索引)

mysql> use db1;
mysql> create table tb1(id int, name char(1)) engine=myisam default charset=utf8;

创建完成后，查看db1目录信息，发现产生了3个文件

*.frm ：框架文件，定义数据表结构

*.MYI ：INDEX索引，主要用于存放索引文件

*.MYD：数据文件

• InnoDB引擎(聚集索引)

mysql> use db1;
mysql> create table tb1(id int, name char(1)) engine=innodb default charset=utf8;

创建完成后，查看db1目录信息，发现产生了2个文件

 *.frm ：框架文件，定义数据表结构

*.ibd：索引文件 + 数据文件

        其实InnoDB引擎不仅仅会产生以上两个文件，其在也会产生一个文件（确切来说不能叫做产生文件，而应该叫做共享文件）。

 日志文件（MySQL中我们需要了解哪些日志）

日志类型	写入日志的信息
事务日志：transaction log	redo log：实现 WAL（Write Ahead Log) ,数据更新前先记录redo log；undo log：保存与执行的操作相反的操作,用于实现rollback 事务型存储引擎自行管理和使用，建议和数据文件分开存放；事务日志文件： ib_logfile0， ib_logfile1
慢查询日志 slow query log	记录执行查询时长超出指定时长的操作
中继日志 reley log	在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件
通用日志：general log	记录对数据库的通用操作，包括:错误的SQL语句 通用日志可以保存在：file（默认值）或 table（mysql.general_log表）
error log 错误日志	存放数据库的启动、停止或运行时的错误信息（找ERROR）。mysqld启动和关闭过程中输出的事件信息；mysqld运行中产生的错误信息；event scheduler运行一个event时产生的日志信息；在主从复制架构中的从服务器上启动从服务器线程时产生的信息
binary log二进制日志	数据库的所有更改操作（DDL/DML/DCL），不包含select或者show这类语句。 用于主从复制中，master主服务器将二进制日志中的更改操作发送给slave从服务器，从服务器执行这些更改操作是的和主服务器的更改相同；用于数据的恢复操作

更改日志的存放位置：

# vim my.cnf
[mysqld]
日志类型=路径+命名

注：当我们更改了my.cnf配置文件，一定要记得重启MySQL服务器。

3、备份过程须考虑的因素

• 必须制定详细的备份计划(策略)（备份频率、时间点、周期）。
• 备份数据应该放在非数据库本地，并建议有多份副本。
• 必须做好数据恢复的演练（每隔一段时间，对备份的数据在测试环境中进行模拟恢复，保证当出现数据灾难的时候能够及时恢复数据。）
• 根据数据应用的场合、特点选择正确的备份工具。
• 数据的一致性、服务的可用性

4、备份的类型

按备份方式划分
1. 物理备份（Physical Backup）
定义：直接复制数据库的物理文件（如数据文件、日志文件、配置文件等），不依赖数据库逻辑结构。
工具：cp、tar、rsync 等系统命令，或数据库专属工具（如 MySQL 的 xtrabackup、PostgreSQL 的 pg_basebackup）。
示例：xtrabackup --user=root --password=xxx --backup --target-dir=/backup/。
特点：速度快（直接拷贝文件，无需解析数据）；恢复快（直接覆盖文件，适合大规模数据）；与数据库版本、存储引擎强相关（如 InnoDB 和 MyISAM 的物理文件结构不同）。
适用场景：全量备份、大规模数据库、需要快速恢复的场景。
2. 逻辑备份（Logical Backup）
定义：通过数据库接口（如 SQL 命令）导出数据的逻辑结构（表结构、索引）和内容（行记录），生成可读的文本文件（如 SQL 脚本、CSV 等）。
工具：MySQL 的 mysqldump、PostgreSQL 的 pg_dump、SQL Server 的 bcp 等。
示例：mysqldump -u root -p --databases test > test_backup.sql。
特点：

• 跨版本、跨存储引擎兼容（SQL 脚本通用）。
• 可读性强（可直接编辑 SQL 文件）。
• 速度慢（需解析数据并生成 SQL，适合中小规模数据）。
• 恢复慢（需执行 SQL 语句重建数据）。

适用场景：中小数据库、需要跨环境迁移（如从 MySQL 5.7 迁移到 8.0）、需要筛选部分数据备份的场景。

按数据范围划分
1. 全量备份（Full Backup）
定义：备份数据库的全部数据和对象（所有表、索引、存储过程等）。
特点：

• 备份完整，恢复时无需依赖其他备份。
• 占用空间大，备份时间长。

示例：xtrabackup 全量备份整个实例、mysqldump --all-databases 导出所有库。
2. 增量备份（Incremental Backup）
定义：仅备份上次全量备份或增量备份后新增或修改的数据。
实现方式：依赖日志文件（如 MySQL 的 binlog、PostgreSQL 的 WAL 日志）或变更跟踪机制。
示例：xtrabackup --incremental 基于上次备份的增量备份、通过 mysqlbinlog 导出指定时间段的 binlog。
特点：

• 备份体积小，速度快。
• 恢复复杂（需先恢复全量备份，再依次应用增量备份）。

适用场景：全量备份后的数据补充（如每日全量 + 每小时增量），减少备份存储和时间开销。
3. 差异备份（Differential Backup）
定义：仅备份上次全量备份后新增或修改的数据（与增量备份的区别：增量基于上一次备份，差异基于上一次全量）。
特点：

• 备份体积比增量大，但比全量小。
• 恢复比增量简单（只需全量 + 最后一次差异）。

示例：周一全量备份，周二差异备份（周一后变更的数据），周三差异备份（周一后变更的数据，包含周二的内容）。

按数据库状态划分
1. 冷备份（Cold Backup）
定义：在数据库关闭状态下进行的备份（无读写操作干扰）。
实现方式：直接拷贝数据文件（如关闭 MySQL 后 cp -r /var/lib/mysql /backup/或者结合rsync进行异地备份）。
特点：

• 备份一致性高（无未提交事务）。
• 影响业务（需停机）。

适用场景：非核心业务、可接受停机的维护窗口。
2. 热备份（Hot Backup）
定义：在数据库运行状态下进行的备份，不影响正常读写（如生产环境）。
实现方式：依赖数据库的事务日志和快照机制（如 InnoDB 的 MVCC、PostgreSQL 的在线备份）。
工具：xtrabackup（支持 InnoDB 热备份）、pg_basebackup -X stream（PostgreSQL 在线备份）。
特点：

• 不影响业务（无需停机）。
• 需处理未提交事务（通过日志保证一致性）。

适用场景：核心业务、7x24 小时运行的数据库。
3. 温备份（Warm Backup）
定义：在数据库运行状态下进行，但会对读写操作有限制（如只读模式）。