企业实用——MySQL的备份详解
序言： 本次基于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引擎的区别
- ① MyISAM引擎：擅长数据的查询，支持全文索引
- ② 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）
定义：在数据库运行状态下进行，但会对读写操作有限制（如只读模式）。