MySQL8 备份与还原
引言
俗话说"手里有粮，心里不慌"，这句话应用在数据库运维领域同样有效。
对于重要的数据做好备份，是我们每个系统运维工程师以及数据库运维工程师的重要职责。
备份只是一种手段，我们最终目的是当数据出现问题时能够及时的通过备份进行恢复（应急演练）。
学习目标
- 了解MySQL常见的备份方式和类型
- 能够使用mysqldump工具进行数据库的备份（全库备份，库级别备份，表级别备份）
- 能够使用mysqldump工具+binlog日志实现增量备份
- 能够使用Xtrabackup工具对数据库进行全备
一、MySQL8 备份与还原（理解）
(一) 基本概念
数据库备份是指对数据库中的数据进行复制和存储，以便在数据丢失或损坏时进行恢复。数据库备份通常包括库、表、记录、索引、配置等关键数据和结构。
(二) 逻辑备份 vs 物理备份
数据库备份的常用方式：逻辑备份 与 物理备份
| 方式 | 逻辑备份 | 物理备份 |
|---|---|---|
| 内容 | 备份的是数据库的结构、数据 | 备份的是数据库文件 物理数据文件； 日志文件binlog二进制日志； 配置文件my.cnf |
| 工具 | 常用 mysqldump | 常用 xtrabackup |
| 特点 | 可读性强，跨平台，可用于部分恢复和迁移 | 备份速度快，恢复效率高 |
| 场景 | 适合中小型数据库 | 适合大规模数据库 |
(三) 二进制日志
binlog：二进制日志（Binary Log），可以手工配置log-bin。
这个日志主要负责把用户对数据库的增、删、改（DML语言）事务型的SQL语句记录在binlog日志中，方便未来对数据进行查找与恢复！
思考：binlog文件，没有涉及对查询的记录，为什么？
(四) 库表概念
1. ☆ 视图
比较特殊的情况：数据库中除了数据库、数据表以外，还包含视图、存储过程。
添加视图（虚拟表），底层就是一个SQL语句（select查询语句）。
作用：简化SQL查询，保护数据
employee
id name age dept salary薪资
create view vw_employee as select id,name,age,dept from employee; # 了解，不需要执行
2. ☆ 存储过程
存储过程（开发需要掌握，运维作为了解）：
存储过程类似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
② 配置文件 => /etc/my.cnf (mysql --help 可以查看加载顺序)
③ 日志文件（主要是二进制日志文件） => binlog日志（MySQL8及以后默认开启） => 记录对数据库的增删改操作
(六) 工具选型
逻辑备份：
适用于MyISAM 和 InnoDB，可以通过工具如 mysqldump导出表结构和数据为SQL语句。
备份文件为文本格式，便于跨平台迁移和恢复。
InnoDB 支持事务和外键，备份时需要注意数据一致性，可以用 --single-transaction 实现无锁备份。
物理备份：
MyISAM：MySQL5.7及之前版本可以简单复制数据库文件（如 .frm、.MYI文件、.MYD 文件）进行物理备份，MySQL8.0引入了更多复杂的功能，导致摒弃了这种操作。
InnoDB：物理备份需要包括表数据、日志文件等，适用工具如 xtrabackup，确保数据的一致性和完整性，特别是在大规模数据环境下。
若数据库同时包含 InnoDB 和 MyISAM 表，可以：
1、用 XtraBackup 备份 InnoDB 数据（无锁）。
2、配合 mysqldump 导出 MyISAM 表（加锁）。
3、合并两者备份文件以完整恢复。
二、MySQL8 逻辑备份（重点）
(一) mysqldump 基本语法
强调：mysqldump 不是 SQL 语句，而是一个 MySQL 二进制命令，所以在 Linux 终端执行！
[root@mysql-server ~]# mysqldump --help
查看一个命令，是存放在了哪里，有两种方法，whereis 和 which 都可以：
[root@mysql-server ~]# which mysql
/export/server/mysql/bin/mysql
[root@mysql-server ~]# which mysqldump
/export/server/mysql/bin/mysqldump
[root@mysql-server ~]#
[root@mysql-server ~]# whereis mysqldump
mysqldump: /export/server/mysql/bin/mysqldump
[root@mysql-server ~]#
mysqldump逻辑备份图解：
mysqldump 备份语法格式
# 表级别备份
mysqldump [OPTIONS] DB1 [Table1]
# 库级别备份
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
# 全库级别备份
mysqldump [OPTIONS] --all-databases [OPTIONS]
(二) 准备测试数据
修改MySQL root密码（可选）
ALTER USER 'root'@'localhost' IDENTIFIED BY 'ItHeiMa@666';
或者
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MySQL@666';
FLUSH PRIVILEGES;
mysql -uroot -p'MySQL@666'
drop database if exists db_itheima;
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;
show tables;
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');
select * from tb_student;
exit;
准备备份目录
[root@mysql-server ~]# mkdir -p /data/sqlbak
(三) 表级备份与还原
备份
案例：把db_itheima数据库中的tb_student数据表进行备份
[root@mysql-server ~]# mysqldump -uroot -p'MySQL@666' db_itheima tb_student > /data/sqlbak/tb_student.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-server ~]# ls /data/sqlbak/tb_student.sql
/data/sqlbak/tb_student.sql
# 可以cat查看tb_student.sql文件内容
[root@mysql-server ~]# cat /data/sqlbak/tb_student.sql
数据表还原
首先，把表删除掉
课堂为了演示真正还原数据，所以才先删除；工作中，谨慎操作！
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use db_itheima;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| tb_student |
+----------------------+
1 row in set (0.00 sec)
mysql> drop table tb_student;
Query OK, 0 rows affected (0.01 sec)
mysql>
再导入数据
# 方式一：mysql -uroot -p'密码' 数据库名称 < sql文件位置
[root@mysql-server ~]# mysql -uroot -p'MySQL@666' db_itheima < /data/sqlbak/tb_student.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show tables;
Empty set (0.00 sec)
mysql> show tables;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| tb_student |
+----------------------+
1 row in set (0.00 sec)
mysql>
# 方式二：登录到MySQL终端，执行 source sql文件位置
mysql> drop table tb_student;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> source /data/sqlbak/tb_student.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| tb_student |
+----------------------+
1 row in set (0.00 sec)
mysql> select * from tb_student;
+----+--------+------+--------+---------+
| id | name | age | gender | subject |
+----+--------+------+--------+---------+
| 1 | 刘备 | 35 | male | bigdata |
| 2 | 关羽 | 32 | male | yunwei |
| 4 | 貂蝉 | 18 | female | ui |
| 5 | 大乔 | 18 | female | ui |
| 6 | 小乔 | 16 | female | ui |
+----+--------+------+--------+---------+
5 rows in set (0.00 sec)
mysql>
确认是否还原成功
[root@mysql-server ~]# mysql -uroot -p'MySQL@666' -e "use db_itheima;show tables;select * from tb_student;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| tb_student |
+----------------------+
+----+--------+------+--------+---------+
| id | name | age | gender | subject |
+----+--------+------+--------+---------+
| 1 | 刘备 | 35 | male | bigdata |
| 2 | 关羽 | 32 | male | yunwei |
| 4 | 貂蝉 | 18 | female | ui |
| 5 | 大乔 | 18 | female | ui |
| 6 | 小乔 | 16 | female | ui |
+----+--------+------+--------+---------+
[root@mysql-server ~]#
补充
这里扩展了一个新命令， mysql -uroot -p'密码' -e 'sql语句'
代表在命令行执行SQL语句（好处：可以不需要进入mysql终端，就可以执行SQL语句）
(四) 库级备份与还原
检查测试数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use db_itheima;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| tb_student |
+----------------------+
1 row in set (0.00 sec)
mysql> select * from tb_student;
+----+--------+------+--------+---------+
| id | name | age | gender | subject |
+----+--------+------+--------+---------+
| 1 | 刘备 | 35 | male | bigdata |
| 2 | 关羽 | 32 | male | yunwei |
| 4 | 貂蝉 | 18 | female | ui |
| 5 | 大乔 | 18 | female | ui |
| 6 | 小乔 | 16 | female | ui |
+----+--------+------+--------+---------+
5 rows in set (0.00 sec)
mysql>
备份数据库，包含数据表
[root@mysql-server ~]# mysqldump -uroot -p'MySQL@666' --databases db_itheima > /data/sqlbak/db_itheima.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-server ~]# ll /data/sqlbak/
总用量 8
drwxr-xr-x 2 root root 50 4月 23 20:21 ./
drwxr-xr-x 3 root root 20 4月 23 10:24 ../
-rw-r--r-- 1 root root 2409 4月 23 20:21 db_itheima.sql
-rw-r--r-- 1 root root 2216 4月 23 20:10 tb_student.sql
库级还原
mysql -uroot -p'密码' < 后缀为.sql文件位置
或者
mysql> source 后缀为.sql文件的位置
案例：还原db_itheima.sql文件到MySQL数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database db_itheima;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bookdb666 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> source /data/sqlbak/db_itheima.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected, 1 warning (0.01 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use db_itheima;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| tb_student |
+----------------------+
1 row in set (0.00 sec)
mysql> select * from tb_student;
+----+--------+------+--------+---------+
| id | name | age | gender | subject |
+----+--------+------+--------+---------+
| 1 | 刘备 | 35 | male | bigdata |
| 2 | 关羽 | 32 | male | yunwei |
| 4 | 貂蝉 | 18 | female | ui |
| 5 | 大乔 | 18 | female | ui |
| 6 | 小乔 | 16 | female | ui |
+----+--------+------+--------+---------+
5 rows in set (0.00 sec)
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database db_itheima;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bookdb666 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
[root@mysql-server ~]# mysql -uroot -p'MySQL@666' < /data/sqlbak/db_itheima.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use db_itheima;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| tb_student |
+----------------------+
1 row in set (0.01 sec)
mysql> select * from tb_student;
+----+--------+------+--------+---------+
| id | name | age | gender | subject |
+----+--------+------+--------+---------+
| 1 | 刘备 | 35 | male | bigdata |
| 2 | 关羽 | 32 | male | yunwei |
| 4 | 貂蝉 | 18 | female | ui |
| 5 | 大乔 | 18 | female | ui |
| 6 | 小乔 | 16 | female | ui |
+----+--------+------+--------+---------+
5 rows in set (0.00 sec)
mysql>
问题：有了备份为什么还要有视图和存储过程？
简单来说：
备份是为了“救命”。
视图和存储过程是为了“好用”和“高效”。
根本目的不同：
备份 (Backup)
目的：数据安全与灾难恢复。
它关心的是数据本身的副本。当发生硬件故障、人为误删（比如DROP DATABASE）、病毒攻击等灾难性事件时，备份是你最后的防线，用于将数据恢复到某个过去的健康状态。它就像你为房子购买的火灾保险。
视图 (View) 和存储过程 (Stored Procedure)
目的：简化操作、提升效率、加强安全。
它们关心的是如何日常访问和使用数据。它们是数据库应用程序的一部分，旨在让数据访问更便捷、性能更高、管理更规范。就像你房子里设计好的电路系统和智能家居系统，让你日常生活更方便。
(五) 全库备份与还原（扩展）
在MySQL中，如果要使用mysqldump进行全库级备份，必须开启二进制日志！
1. ☆ 开启二进制日志
mysql> show variables like 'log_bin_basename';
+------------------+----------------------------------+
| Variable_name | Value |
+------------------+----------------------------------+
| log_bin_basename | /export/server/mysql/data/binlog |
+------------------+----------------------------------+
1 row in set (0.00 sec)
mysql>
vim /etc/my.cnf
添加如下内容：
[mysqld]
# 在文件最末端追加以下内容（注意：不能把这一行也复制进去）
server-id=101
log_error=/export/server/mysql/logs/error.log
log-bin=/export/server/mysql/data/binlog
# 注意：binlog 是文件名前缀，不是目录名
MySQL 会自动在这个前缀后面添加序号和扩展名
实际生成的二进制日志文件是：
/export/server/mysql/data/binlog.000001
/export/server/mysql/data/binlog.000002
/export/server/mysql/data/binlog.000003
...等等
MySQL8.0及以后版本，二进制日志默认处于开启状态！
如果mysql目录下不存在logs文件夹，需要提前创建，而且文件拥有者以及所属组必须为mysql
mkdir -p /export/server/mysql/logs
chown -R mysql.mysql /export/server/mysql
补充：
命令 chown mysql.mysql 和 chown mysql:mysql 有什么区别？
在现代 Linux 系统中，. 和 : 的功能完全相同，都用于分隔用户名和组名。chown user.group是为了兼容历史版本，推荐使用 chown user:group
重启MySQL数据库
systemctl restart mysqld
ll /export/server/mysql/data/
binlog.000001
binlog.000002
binlog.000003
...
2. ☆ mysqldump 高级选项说明
| 选项 | 正确描述 |
|---|---|
| --flush-logs, -F | 备份前刷新日志，主要是刷新二进制日志，创建一个新的二进制日志文件 |
| --flush-privileges | 用于还原 mysql 系统库（账号、权限）后，强制刷新权限，避免授权不生效。 |
| --lock-all-tables, -x | 对所有数据库的所有表加全局读锁，保证一致性但影响服务可用性 |
| --lock-tables, -l | 对当前备份的每个数据库的所有表分别加锁（不如--single-transaction常用） |
| --single-transaction | 对InnoDB表使用事务隔离，通过START TRANSACTION WITH CONSISTENT SNAPSHOT获取一致性视图 |
案例：全库备份实现
mysqldump -uroot -p'MySQL@666' --all-databases --master-data --single-transaction > /data/sqlbak/all.sql
--single-transaction 事务隔离，和 master-data 配合使用，保证数据完整性
--master-data：在导出文件中标记二进制文件位置
以后将会使用 --source-data 这个来代替 --master-data
主服务器 => 定时同步数据 => 从服务器
如果需要备份存储过程，需要添加--routines
不加 --routines：mysqldump 默认只备份表结构和数据，不会备份存储过程、函数、触发器
加了 --routines：才会同时备份存储过程和自定义函数
mysqldump -uroot -p'MySQL@666' --routines --all-databases --master-data --single-transaction > /data/sqlbak/all-bak.sql
注：在mysqldump工具中，--single-transaction选项是一个很有用的功能，它主要用于支持事务的存储引擎（如InnoDB）。当你使用此选项时，mysqldump会启动一个单独的事务来转储数据，这样可以在不锁定整个表的情况下获取表的一致性快照。
在MySQL中，大部分使用InnoDB引擎，InnoDB引擎在执行增删改的时候，都会开启事务，执行结束，提交事务，如果失败了，则回滚事务。
[root@mysql-server ~]# mysqldump -uroot -p'MySQL@666' --all-databases --master-data --single-transaction > /data/sqlbak/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
[root@mysql-server ~]# ll -h /data/sqlbak/all.sql
-rw-r--r-- 1 root root 1.3M 4月 23 20:33 /data/sqlbak/all.sql
[root@mysql-server ~]# wc -l /data/sqlbak/all.sql
1145 /data/sqlbak/all.sql
关于 **--single-transaction** 选项
通俗理解： 使用 --single-transaction 就像给数据库"拍个快照"来进行备份。
具体说明：
- 工作原理：备份开始时，它会说"就以现在这个时间点的数据状态为准"，然后开始备份。
- 优点：在备份过程中，其他用户仍然可以正常增删改数据，不会感觉到被"卡住"。
- 适用场景：主要用于 InnoDB 这种支持事务的存储引擎。
于 InnoDB 引擎的事务机制
通俗理解： InnoDB 引擎处理数据时，就像"打包操作"。
具体说明：
- 日常操作：当你执行 INSERT、UPDATE、DELETE 时，InnoDB 会自动把这个操作打包成一个"事务包"。
- 成功情况：操作顺利完成 → 提交这个"包"（就像快递发货成功）
- 失败情况：操作出现问题 → 整个"包"退回（就像取消订单，数据恢复原样）
- 好处：保证数据不会出现"做一半"的混乱状态。
两者结合的理解
比喻说明：
- 不用
--single-transaction：就像为了清点仓库货物，把整个仓库锁上，清点期间谁都不能进出。 - 使用
--single-transaction：就像给仓库拍张照片，然后对着照片清点货物，真实仓库照常营业，不影响其他人工作。
总结： --single-transaction 让你能在不打扰业务正常运行的情况下，安全地完成数据备份。
案例：全库还原实现
mysql -uroot -p'MySQL@666' -e "show databases;"
mysql -uroot -p'MySQL@666' -e "drop database db_itheima;"
mysql -uroot -p'MySQL@666' -e "drop database bookdb666;"
mysql -uroot -p'MySQL@666' -e "show databases;"
mysql -uroot -p'MySQL@666' < /data/sqlbak/all.sql
mysql -uroot -p'MySQL@666' -e "show databases;"
[root@mysql-server ~]# mysql -uroot -p'MySQL@666' -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@mysql-server ~]# mysql -uroot -p'MySQL@666' -e "drop database db_itheima;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-server ~]# mysql -uroot -p'MySQL@666' -e "drop database bookdb666;" mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-server ~]# mysql -uroot -p'MySQL@666' -e "show databases;" mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@mysql-server ~]# mysql -uroot -p'MySQL@666' < /data/sqlbak/all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-server ~]# mysql -uroot -p'MySQL@666' -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
注意：mysqldump 虽然会备份 **mysql** 系统库，但是系统库不能用来恢复！ 请勿删除或覆盖本机的 **mysql** 系统库，否则可能导致 MySQL 无法启动或无法登录！ 全库还原仅适用于业务数据，不适用于系统数据库！
(六) 总结
- mysqldump工具备份的是SQL语句，最终结果是一个SQL文件，所以备份不需要停服务（热备）
- 使用备份文件恢复时，要保证数据库处于运行状态
- 只能实现全库，指定库，表级别的某一时刻的备份，本身不能增量备份
- 适用于中小型数据库
- “扩” = 扩大已有 → 扩展（量变）
- “拓” = 开拓未知 → 拓展（质变）
三、mysqldump + binlog（拓展）
(一) 全量备份 + 增量备份
使用 mysqldump 进行全量备份，它能帮助我们将整个数据库的当前状态保存下来。
但如果数据库不断更新，进行全量备份可能会占用大量的时间和资源。
所以，在这时候我们引入了增量备份，它只备份自上次全量备份以来的变化部分。
通过结合全量备份和二进制日志（binlog），我们可以高效地还原出完整的数据状态。
这就是 mysqldump + binlog 的增量备份还原方案。
binlog：二进制日志（Binary Log），可以手工配置log-bin。
这个日志主要负责把用户对数据库的增、删、改（DML语言）事务型的SQL语句记录在binlog日志中，方便未来对数据进行查找与恢复！
什么是增量备份？
增量备份是指在全量备份的基础上，只备份自上次（？）备份以来发生变化的数据（新增、修改或删除的内容）。
相比于全量备份，
- 增量备份的优点在于速度更快、占用空间更小。
- 恢复时则需要先恢复全量备份，再依次恢复增量备份文件。
增量备份常用于提高备份效率，特别是在数据量大且频繁更新的场景中。
现有数据情况说明：
数据库名称：db_itheima
学生表名称：tb_student
(二) 备份策略
备份策略
每周会做一次全量备份，以后每天就是增量备份（只备份增加的那一部分数据）
比如，周天：全量备份，周一到周六：增量备份
(三) 备份与恢复的过程
第一步：先准备数据（前提）
第二步：开启二进制日志，然后做全量备份（全库备份）
第三步：继续对数据库进行增删改操作（还未备份）=> 写入到binlog，然后写入磁盘
第四步：突然发生了硬件故障，数据库丢失了
第五步：备份二进制日志
第六步：恢复全量备份导出的数据（不完整，可能只有90%）+ 根据二进制日志信息导入剩余10%的数据
1. 准备数据集
drop database if exists db_itheima;
create database db_itheima default charset=utf8;
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', 'yunwei', 'bigdata'),
primary key(id)
) engine=innodb default charset=utf8;
insert into tb_student values (null,'刘备',33,'male','bigdata');
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');
show tables;
select * from tb_student;
CREATE DATABASE `bookdb666` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
show databases;
use bookdb666;
CREATE TABLE book666 (ID int,Name char(16),Price int,Publishing char(16));
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('1','《Linux从入门到精通》','66','电子工业出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('2','《云计算趋势》','68','人民邮电出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('3','《操作系统设计与实现》','90','机械工业出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('4','《高性能MySQL1》','71','清华大学出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('5','《高性能MySQL2》','72','清华大学出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('6','《高性能MySQL3》','73','清华大学出版社');
show tables;
select * from book666;
2. 全量备份
一、☆ 第一步：开启二进制日志
开启二进制，以及格式化binlog日志输出格式，然后做全量备份
不过在MySQL 8当中，默认开启了
vim /etc/my.cnf
[mysqld]
尾部追加内容：
# MySQL 8.0.22+ 会自动生成临时 server-id，但重启后可能变化，因此建议手动设置
server-id=101
log-bin=/export/server/mysql/data/binlog
# 设置binlog日志存储格式，默认对sql语句进行编码，无法直观查看对应SQL语句
binlog_format=statement
# 设置密码验证插件，从MySQL5.7密码验证发生了改变，可能会导致很多客户端无法连接MySQL服务器端（了解）
# default_authentication_plugin=mysql_native_password # 快速有效，能立即解决大部分旧客户端的连接问题。
systemctl restart mysqld
二、☆ 第二步：全量备份
# 开始全量备份
mkdir -p /data/sqlbak
mysqldump -uroot -p'MySQL@666' --routines --single-transaction --flush-logs --source-data --all-databases > /data/sqlbak/all.sql
[root@mysql-server ~]# rm -f /data/sqlbak/all.sql
[root@mysql-server ~]# mysqldump -uroot -p'MySQL@666' --routines --single-transaction --flush-logs --source-data --all-databases > /data/sqlbak/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-server ~]#
注意：
--flush-logs 会让系统重新生成一个新的二进制文件，以后增量数据都会写入到新二进制文件。
从这个位置开始，后期增删改数据就相当于增量数据，最好单独保存在一个独立的binlog日志文件中，方便后期管理与数据恢复
备份完成后，一定要确认你最新的二进制文件是哪一个 => binlog.000024
[root@mysql-server ~]# ll /export/server/mysql/data/
总用量 126696
drwxr-x--- 9 mysql mysql 4096 4月 23 20:41 ./
drwxr-xr-x 11 mysql mysql 153 4月 23 18:28 ../
-rw-r----- 1 mysql mysql 56 4月 23 18:31 auto.cnf
-rw-r----- 1 mysql mysql 157 4月 23 18:31 binlog.000022
-rw-r----- 1 mysql mysql 1317467 4月 23 20:41 binlog.000023
-rw-r----- 1 mysql mysql 157 4月 23 20:41 binlog.000024
-rw-r----- 1 mysql mysql 94 4月 23 20:41 binlog.index
三、☆ 第三步：增删改操作
mysql> select * from tb_student;
+----+--------+------+--------+---------+
| id | name | age | gender | subject |
+----+--------+------+--------+---------+
| 1 | 刘备 | 35 | male | bigdata |
| 2 | 关羽 | 32 | male | yunwei |
| 4 | 貂蝉 | 18 | female | ui |
| 5 | 大乔 | 18 | female | ui |
| 6 | 小乔 | 16 | female | ui |
+----+--------+------+--------+---------+
5 rows in set (0.00 sec)
mysql> insert into tb_student values (3,'张飞',30,'male','yunwei');
Query OK, 1 row affected (0.01 sec)
mysql> delete from tb_student where id = 4;
Query OK, 1 row affected (0.00 sec)
mysql> update tb_student set age = 33 where name = '关羽';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tb_student;
+----+--------+------+--------+---------+
| id | name | age | gender | subject |
+----+--------+------+--------+---------+
| 1 | 刘备 | 35 | male | bigdata |
| 2 | 关羽 | 33 | male | yunwei |
| 3 | 张飞 | 30 | male | yunwei |
| 5 | 大乔 | 18 | female | ui |
| 6 | 小乔 | 16 | female | ui |
+----+--------+------+--------+---------+
5 rows in set (0.00 sec)
mysql>
3. 灾难
突然发生了硬件故障，数据库丢失了
情况一：服务器故障，导致数据库异常或丢失
情况二：误删或故删（删库跑路）
mysql -uroot -p'MySQ@L666' -e "drop database db_itheima;"
4. 备份 binlog
马上把最新的二进制文件进行备份
ll /export/server/mysql/data/
根据时间，找到最新的二进制日志binlog.000025
cp /export/server/mysql/data/binlog.000025 /data/sqlbak/
5. 恢复
一、☆ 第一步：全量恢复
mysql -uroot -p'MySQL@666' < /data/sqlbak/all.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use db_itheima;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| tb_student |
+----------------------+
1 row in set (0.00 sec)
mysql> select * from tb_student;
+----+--------+------+--------+---------+
| id | name | age | gender | subject |
+----+--------+------+--------+---------+
| 1 | 刘备 | 35 | male | bigdata |
| 2 | 关羽 | 32 | male | yunwei |
| 4 | 貂蝉 | 18 | female | ui |
| 5 | 大乔 | 18 | female | ui |
| 6 | 小乔 | 16 | female | ui |
+----+--------+------+--------+---------+
5 rows in set (0.00 sec)
mysql>
二、☆ 第二步：增量恢复
通过binlog增量备份还原数据到100% => at 330 ~ at 1202 （以实际为准） => 增量数据
# 注意：要先备份最新二进制日志 binlog.000025（以实际为准） 到 /data/sqlbak 目录下
mysqlbinlog /data/sqlbak/binlog.000025 => 重点找事故的临界点
或者
mysqlbinlog /data/sqlbak/binlog.000025 | more # 按 q 退出
mysqlbinlog /data/sqlbak/binlog.000025 | less # 按 q 退出
确认at位置（以实际为准）
mysqlbinlog -v /data/sqlbak/binlog.000025 | grep -A 10 -B 5 -i "insert\|delete\|update"
grep 参数说明
-i：忽略大小写
"insert\|delete\|update"：匹配 insert、delete、update 三种 SQL（用 \| 表示 OR）
-A 10：匹配到关键字后，向后多显示 10 行
-B 5：匹配到关键字后，向前多显示 5 行
mysqlbinlog --start-position=330 --stop-position=1202 /data/sqlbak/binlog.000025 | mysql -uroot -p'MySQL@666' # 330是开始的位置，1202是结束的位置，以实际为准
或者
注：除了按照位置进行恢复，还可以按照时间点进行恢复
mysqlbinlog --start-datetime="2026-04-23 21:14:18" --stop-datetime="2026-04-23 21:16:05" /data/sqlbak/binlog.000025 | mysql -uroot -p'MySQL@666'
# 查看binlog日志定位at位置和时间节点
[root@mysql-server ~]# mysqlbinlog -v /data/sqlbak/binlog.000025 | grep -A 10 -B 5 -i "insert\|delete\|update"
/*!*/;
# at 330
#260423 21:14:18 server id 101 end_log_pos 480 CRC32 0x7685044d Query thread_id=30 exec_time=0 error_code=0
use `db_itheima`/*!*/;
SET TIMESTAMP=1776950058/*!*/;
insert into tb_student values (3,'张飞',30,'male','yunwei')
/*!*/;
# at 480
#260423 21:14:18 server id 101 end_log_pos 511 CRC32 0xed9b9d07 Xid = 3194
COMMIT/*!*/;
# at 511
#260423 21:14:28 server id 101 end_log_pos 590 CRC32 0x164e9369 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=1776950068281632 immediate_commit_timestamp=1776950068281632 transaction_length=328
# original_commit_timestamp=1776950068281632 (2026-04-23 21:14:28.281632 CST)
# immediate_commit_timestamp=1776950068281632 (2026-04-23 21:14:28.281632 CST)
/*!80001 SET @@session.original_commit_timestamp=1776950068281632*//*!*/;
/*!80014 SET @@session.original_server_version=80045*//*!*/;
--
BEGIN
/*!*/;
# at 684
#260423 21:14:28 server id 101 end_log_pos 808 CRC32 0xbc5a2f81 Query thread_id=30 exec_time=0 error_code=0
SET TIMESTAMP=1776950068/*!*/;
delete from tb_student where id = 4
/*!*/;
# at 808
#260423 21:14:28 server id 101 end_log_pos 839 CRC32 0x7a3ebdaa Xid = 3195
COMMIT/*!*/;
# at 839
#260423 21:14:50 server id 101 end_log_pos 918 CRC32 0x9b4a6d4e Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no original_committed_timestamp=1776950090269000 immediate_commit_timestamp=1776950090269000 transaction_length=363
# original_commit_timestamp=1776950090269000 (2026-04-23 21:14:50.269000 CST)
# immediate_commit_timestamp=1776950090269000 (2026-04-23 21:14:50.269000 CST)
/*!80001 SET @@session.original_commit_timestamp=1776950090269000*//*!*/;
/*!80014 SET @@session.original_server_version=80045*//*!*/;
--
BEGIN
/*!*/;
# at 1021
#260423 21:14:50 server id 101 end_log_pos 1171 CRC32 0x256b3bff Query thread_id=30 exec_time=0 error_code=0
SET TIMESTAMP=1776950090/*!*/;
update tb_student set age = 33 where name = '关羽'
/*!*/;
# at 1171
#260423 21:14:50 server id 101 end_log_pos 1202 CRC32 0xded25d86 Xid = 3196
COMMIT/*!*/;
# at 1202
#260423 21:16:05 server id 101 end_log_pos 1279 CRC32 0x23a27495 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no original_committed_timestamp=1776950165881391 immediate_commit_timestamp=1776950165881391 transaction_length=199
# original_commit_timestamp=1776950165881391 (2026-04-23 21:16:05.881391 CST)
# immediate_commit_timestamp=1776950165881391 (2026-04-23 21:16:05.881391 CST)
/*!80001 SET @@session.original_commit_timestamp=1776950165881391*//*!*/;
/*!80014 SET @@session.original_server_version=80045*//*!*/;
分别还原命令
1. 只还原 INSERT 操作（张飞）
mysqlbinlog --start-position=330 --stop-position=480 /data/sqlbak/binlog.000025 | mysql -uroot -p'MySQL@666'
2. 只还原 DELETE 操作（貂蝉）
mysqlbinlog --start-position=684 --stop-position=808 /data/sqlbak/binlog.000025 | mysql -uroot -p'MySQL@666'
3. 只还原 UPDATE 操作（刘备）
mysqlbinlog --start-position=1021 --stop-position=1202 /data/sqlbak/binlog.000025 | mysql -uroot -p'MySQL@666'
4. 还原所有三个操作（完整增量还原）
mysql -uroot -p'MySQL@666' < /data/sqlbak/all.sql
mysqlbinlog --start-position=330 --stop-position=1202 /data/sqlbak/binlog.000025 | mysql -uroot -p'MySQL@666'
mysql> select * from tb_student;
+----+--------+------+--------+---------+
| id | name | age | gender | subject |
+----+--------+------+--------+---------+
| 1 | 刘备 | 35 | male | bigdata |
| 2 | 关羽 | 32 | male | yunwei |
| 4 | 貂蝉 | 18 | female | ui |
| 5 | 大乔 | 18 | female | ui |
| 6 | 小乔 | 16 | female | ui |
+----+--------+------+--------+---------+
5 rows in set (0.00 sec)
mysql> select * from tb_student;
+----+--------+------+--------+---------+
| id | name | age | gender | subject |
+----+--------+------+--------+---------+
| 1 | 刘备 | 35 | male | bigdata |
| 2 | 关羽 | 33 | male | yunwei |
| 3 | 张飞 | 30 | male | yunwei |
| 5 | 大乔 | 18 | female | ui |
| 6 | 小乔 | 16 | female | ui |
+----+--------+------+--------+---------+
5 rows in set (0.00 sec)
mysql>
6. 小结
mysqldump + binlog增量备份具体作用？
答：① 可以实现增量备份，周天（全量），周一~周六（增量），减少空间占用，备份恢复速度快
② 防止误删，因为有全量、还有增量，可以对误删数据进行恢复
四、Xtrabackup 物理备份（重点）
背景
物理备份比较适合超大型数据库备份操作，直接针对物理文件。日志、配置全都会进行备份。
逻辑备份就是把数据导出成一个xxx.sql文件。
(一) Xtrabackup 概述
Xtrabackup 是 Percona 开发的开源 MySQL 备份工具，主打高性能、低影响，能在不锁表的情况下，在线备份 InnoDB、XtraDB 等协议的数据库。
1. ☆ Xtrabackup 8 的核心优势
**版本适配：**支持 MySQL 8.x 备份恢复；
**效率升级：**并行操作加速备份恢复，支持流式传输；
**特性兼容：**适配 MySQL 8.x 新功能，如数据字典；
**可靠性增强：**优化故障恢复，保障数据完整。
对 MySQL 数据库管理员来说，Xtrabackup 8 是高效、经济的备份恢复方案。
官方下载地址：www.percona.com/
2. ☆ Xtrabackup 发音
Xtrabackup 是 Extrabackup /ˈek.strə ˈbæk.ʌp/ 单词的变种，在这里面，Extra Backup表示超越备份。强调该工具具备超出常规的功能或特性。
在英文体系当中，字母 "X" 在产品或公司命名中经常见到。
X的含义有：
- **象征未知、创新与科技感。**借用数学中 "X" 作为未知数的符号，传递探索未知、突破传统的理念。像特斯拉的 Model X（强调未来感与颠覆性设计）、SpaceX（探索太空的未知领域）。
- 替代 "Ex-" 前缀。表达超越、额外的意思。比如，BMW X6（超越性能的车型）。
(二) 2、下载安装
下载
官网地址
https://www.percona.com/downloads
安装
把percona-xtrabackup-80-8.0.35-34.1.el9.x86_64.rpm软件包拷贝到Linux，然后进行安装。
此次使用percona-xtrabackup-80-8.0.35-31.1.el9.x86_64.rpm
[root@mysql-server ~]# dnf localinstall -y percona-xtrabackup-80-8.0.35-31.1.el9.x86_64.rpm
(三) 创建备份用户并授权
需要的权限：
参考地址：https://docs.percona.com/percona-xtrabackup/8.0/privileges.html
- **flush tables with read lock ：**锁表
- backup_admin：备份权限
- **REPLICATION CLIENT：**备份时，需要读取二进制文件位置
进入到MySQL终端（先登录）：
mysql> create user 'bkuser'@'localhost' identified with mysql_native_password by 'Xtrabackup@666';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkuser'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT SELECT ON performance_schema.log_status TO 'bkuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT ON performance_schema.replication_group_members TO 'bkuser'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT SELECT ON performance_schema.keyring_component_status TO 'bkuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql>
performance_schema.log_status：该表存储有关 MySQL 服务器日志（如错误日志、查询日志、慢查询日志等）的状态信息。授权访问该表，可以使用户查询当前日志的状态信息。
performance_schema.replication_group_members：这个表包含有关 MySQL 复制组成员的信息，尤其是在 MySQL 8.0 及以上版本的组复制（Group Replication）设置中。如果你正在使用组复制，授权访问该表能让用户查看与复制成员相关的状态。
performance_schema.keyring_component_status：该表存储有关 MySQL 加密密钥管理的信息。如果启用了 MySQL 密钥环（Keyring）插件并且配置了加密功能，授权访问此表可以让用户查看密钥环组件的状态。
说明：
在数据库中需要以下权限：
- **RELOAD和LOCK TABLES权限：**为了执行FLUSH TABLES WITH READ LOCK（针对MyISAM引擎）
- **REPLICATION CLIENT权限：**为了获取binary log位置
- **PROCESS权限：**显示有关在服务器中执行的线程的信息（即有关会话执行的语句的信息），允许使用SHOW ENGINES
(四) 4、全量备份工作原理
**自学推荐的论坛：**https://opensource.actionsky.com/blog/
作用1：熟悉数据库底层（成为数据库专家）
作用2：分享了很多故障案例，这些案例都可以作为面试中印象比较深刻问题！
1. ☆ redo log vs binlog
在 MySQL 中，**redo log（重做日志）**和 **binlog（二进制日志）**是两种核心日志系统，分别服务于不同的场景。
binlog的设计目的，是为了支持复制（Replication）、数据恢复（Recovery）和审计（Audit）；
- 复制：主库将 binlog 发送到从库，从库重放以同步数据；
- 恢复：基于时间点或位置恢复数据（如误删表后，通过回放 binlog 恢复部分操作）；
- 审计：记录所有数据变更，可以用于做合规性检查；
redo log是为了保障事务的持久性（Durability）和原子性（Atomicity）。
- 通过顺序写日志替代随机写数据页，减少磁盘I/O；
- 确保事务提交后，即使数据库崩溃，未写入磁盘的数据也能通过redo log重做恢复；
所属维度
| redo log | binlog | |
|---|---|---|
| 所属层级 | InnoDB 存储引擎层（其他引擎无） | MySQL 服务器层（与引擎无关） |
| 存储引擎依赖 | 仅 InnoDB 使用（核心机制） | 所有存储引擎的变更均记录（如 MyISAM） |
| 日志类型 | 物理日志（Physical Log） | 逻辑日志（Logical Log） |
| 格式 | InnoDB内部二进制格式，不可读 | mysqlbinlog工具解析 |
| 设计目的 | 事务保障，崩溃恢复 | 主从复制，数据恢复和审计 |
| 常用关联工具 | Xtrbackup | mysqldump |
凌晨02:00开始备份，整个备份过程需要30分钟！
备份开始实际上只备份截止到02:00这段时间内的所有数据
**问题：**02:00 ~ 02:30这段时间，也可能会有增删改操作 => redo log重做日志中
**解决：**Xtrabackup不仅会备份截止到02:00这段时间内的所有数据，备份结束，其会把2:00-2:30这段时间的重写日志也执行一遍，写入到备份文件中。这样咱们得到的备份数据就是截止到02:30的所有内容。
五、Xtrabackup 全量备份与恢复（重点）
(一) 整体实施步骤
备份
☆ 第一步：全量备份，使用Xtrabackup创建数据库的全量备份。
☆ 第二步：预备阶段，整合备份期间生成的redo log日志。
灾难
☆ 模拟数据库故障，删除数据文件并停止 MySQL 服务。
恢复
☆ 第一步：恢复数据库，使用 --copy-back 命令恢复备份，并确保指定数据目录。
☆ 第二步：更改权限，更改数据目录下文件的所有者和组权限为 mysql:mysql。验证测试，启动 MySQL 后，确保还原后的MySQL可以正常工作。
(二) 备份
1. ☆ 第一步：全量备份
准备测试数据（可选）
CREATE DATABASE `bookdb666` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
show databases;
use bookdb666;
CREATE TABLE book666 (ID int,Name char(16),Price int,Publishing char(16));
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('1','《Linux从入门到精通》','66','电子工业出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('2','《云计算趋势》','68','人民邮电出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('3','《操作系统设计与实现》','90','机械工业出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('4','《高性能MySQL1》','71','清华大学出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('5','《高性能MySQL2》','72','清华大学出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('6','《高性能MySQL3》','73','清华大学出版社');
show tables;
select * from book666;
xtrabackup -S /tmp/mysql.sock --user=bkuser --password=Xtrabackup@666 --backup --target-dir=/full_xtrabackup
或者
xtrabackup -S /var/lib/mysql/mysql.sock --user=bkuser --password=Xtrabackup@666 --backup --target-dir=/full_xtrabackup
# xtrabackup --user=bkuser --password=Xtrabackup@666 --backup --target-dir=/full_xtrabackup # 了解 不加-S 默认就是/var/lib/mysql/mysql.sock
注意：第一次执行，需要加上套接字文件mysql.sock，默认在 /tmp/ 目录下面
原因：xtrabackup 拥有自己的默认配置，默认读取了/var/lib/mysql/mysql.sock文件
解决方案：
方案1：把你的套接字文件创建一个软链接，放置于/var/lib/mysql/mysql.sock文件中（不推荐）
mkdir /var/lib/mysql
ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock
方案2：在xtrabackup中添加一个-S选项，执行套接字（推荐方案2）
xtrabackup -S /tmp/mysql.sock --user=bkuser --password=Xtrabackup@666 --backup --target-dir=/full_xtrabackup
方案3：改 /etc/my.cnf 配置文件
mkdir -p /var/lib/mysql/
vim /etc/my.cnf
[mysqld]
basedir=/export/server/mysql
datadir=/export/server/mysql/data
port=3306
socket=/var/lib/mysql/mysql.sock
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci
server-id=101
log_error=/export/server/mysql/logs/error.log
log-bin=/export/server/mysql/data/binlog
systemctl restart mysqld
2. ☆ 第二步：预备阶段（必须）
预备阶段，把备份这段时间内产生的日志，整合到全量备份中。
xtrabackup -S /tmp/mysql.sock --user=bkuser --password=Xtrabackup@666 --backup --target-dir=/full_xtrabackup
"没操作数据库" ≠ "数据库没变化"
MySQL 的后台机制让数据文件在持续演进，--prepare 就是把这些静默的变更整合统一，确保恢复时 InnoDB 看到一个完整、一致、可启动的数据目录。
数据库是持续运行的服务，后台有很多自动机制在不停写入：
| 机制 | 说明 | 产生什么 |
|---|---|---|
| Checkpoint（检查点） | InnoDB 定期把脏页刷盘 | 数据页和日志的 LSN 推进 |
| Purge（清理） | 清理已删除记录的旧版本（MVCC） | undo log 变化 |
| Change Buffer Merge | 合并二级索引的缓冲变更 | 索引页更新 |
| Doublewrite Buffer | 双写缓冲区活动 | 系统表空间写入 |
| Auto-increment 持久化 | 自增值定期刷盘 | ibdata1 更新 |
| 元数据/统计信息更新 | information_schema 统计 | 内部表变化 |
这些不需要你执行任何 SQL，MySQL 后台线程自动在做。
(三) 灾难
# 1 停止 mysqld
systemctl stop mysqld
或者
pkill mysqld
ps aux|grep mysqld
# reboot
# 2 删除数据
ls /export/server/mysql/data
rm -rf /export/server/mysql/data
ls /export/server/mysql/
# 3 再次启动 mysqd 会报错
systemctl status mysqld
systemctl start mysqld
mysql -uroot -pMySQL@666 # 发现登录不进去了
[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# ps aux|grep mysqld
root 5428 0.0 0.0 6636 2176 pts/1 S+ 22:09 0:00 grep --color=auto mysqld
[root@mysql-server ~]# pkill mysqld
[root@mysql-server ~]# ps aux|grep mysqld
root 5433 0.0 0.0 6636 2176 pts/1 S+ 22:09 0:00 grep --color=auto mysqld
[root@mysql-server ~]# ls /export/server/mysql/data
auto.cnf client-key.pem mysql-server-relay-bin.index
binlog.000022 db_itheima performance_schema
binlog.000023 '#ib_16384_0.dblwr' private_key.pem
binlog.000024 '#ib_16384_1.dblwr' public_key.pem
binlog.000025 ib_buffer_pool server-cert.pem
binlog.000026 ibdata1 server-key.pem
binlog.index '#innodb_redo' sys
bookdb666 '#innodb_temp' undo_001
ca-key.pem mysql undo_002
ca.pem mysql.ibd xtrabackup_info
client-cert.pem mysql-server-relay-bin.000001
[root@mysql-server ~]# rm -rf /export/server/mysql/data
[root@mysql-server ~]# ls /export/server/mysql/
bin docs include lib LICENSE logs man README share support-files
[root@mysql-server ~]# systemctl status mysqld
× mysqld.service - MySQL Server
Loaded: loaded (/etc/systemd/system/mysqld.service; enabled; preset: disab>
Active: failed (Result: exit-code) since Thu 2026-04-23 22:09:32 CST; 51s >
Duration: 3h 37min 37.955s
Process: 4977 ExecStart=/export/server/mysql/bin/mysqld --daemonize --pid-f>
Process: 5426 ExecStop=/export/server/mysql/bin/mysqladmin --defaults-file=>
Main PID: 4979 (code=exited, status=0/SUCCESS)
CPU: 53.208s
4月 23 18:31:50 mysql-server systemd[1]: Starting MySQL Server...
4月 23 18:31:53 mysql-server systemd[1]: Started MySQL Server.
4月 23 22:09:31 mysql-server systemd[1]: Stopping MySQL Server...
4月 23 22:09:31 mysql-server mysqladmin[5426]: mysqladmin: [ERROR] Failed to op>
4月 23 22:09:31 mysql-server mysqladmin[5426]: mysqladmin: [ERROR] Fatal error >
4月 23 22:09:31 mysql-server systemd[1]: mysqld.service: Control process exited>
4月 23 22:09:32 mysql-server systemd[1]: mysqld.service: Failed with result 'ex>
4月 23 22:09:32 mysql-server systemd[1]: Stopped MySQL Server.
4月 23 22:09:32 mysql-server systemd[1]: mysqld.service: Consumed 53.208s CPU t>
...skipping...
× mysqld.service - MySQL Server
Loaded: loaded (/etc/systemd/system/mysqld.service; enabled; preset: disab>
Active: failed (Result: exit-code) since Thu 2026-04-23 22:09:32 CST; 51s >
Duration: 3h 37min 37.955s
Process: 4977 ExecStart=/export/server/mysql/bin/mysqld --daemonize --pid-f>
Process: 5426 ExecStop=/export/server/mysql/bin/mysqladmin --defaults-file=>
Main PID: 4979 (code=exited, status=0/SUCCESS)
CPU: 53.208s
4月 23 18:31:50 mysql-server systemd[1]: Starting MySQL Server...
4月 23 18:31:53 mysql-server systemd[1]: Started MySQL Server.
4月 23 22:09:31 mysql-server systemd[1]: Stopping MySQL Server...
4月 23 22:09:31 mysql-server mysqladmin[5426]: mysqladmin: [ERROR] Failed to op>
4月 23 22:09:31 mysql-server mysqladmin[5426]: mysqladmin: [ERROR] Fatal error >
4月 23 22:09:31 mysql-server systemd[1]: mysqld.service: Control process exited>
4月 23 22:09:32 mysql-server systemd[1]: mysqld.service: Failed with result 'ex>
4月 23 22:09:32 mysql-server systemd[1]: Stopped MySQL Server.
4月 23 22:09:32 mysql-server systemd[1]: mysqld.service: Consumed 53.208s CPU t>
~
~
[root@mysql-server ~]# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xeu mysqld.service" for details.
[root@mysql-server ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
(四) 恢复
1. ☆ 第一步：快速恢复
# 停止服务
systemctl stop mysqld
xtrabackup --copy-back --target-dir=/full_xtrabackup
第一次恢复报错
[root@mysql-server ~]# xtrabackup --copy-back --target-dir=/full_xtrabackup
2026-04-23T22:14:22.070232+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/export/server/mysql/data --innodb_log_file_size=50M --innodb_log_files_in_group=2 --innodb_log_group_home_dir=/export/server/mysql/data --server-id=101 --log_bin=/export/server/mysql/data/binlog
2026-04-23T22:14:22.070351+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --copy-back=1 --target-dir=/full_xtrabackup
xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7)
2026-04-23T22:14:22.070384+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /full_xtrabackup/
2026-04-23T22:14:22.070430+08:00 0 [ERROR] [MY-011825] [Xtrabackup] The target is not fully prepared. Please prepare it without option --apply-log-only
2. ☆ 错误原因
首要原因是因为没有做预备阶段，把备份这段时间内产生的日志，整合到全量备份中。没有做--prepare操作，--prepare 就是把这些静默的变更整合统一，确保恢复时 InnoDB 看到一个完整、一致、可启动的数据目录。
XtraBackup 恢复分两阶段：
- Prepare（准备/应用日志） — 让数据文件达到一致状态
- Copy-back（拷贝回原目录） — 把一致的数据文件复制到 MySQL 数据目录
报错说明 /full_xtrabackup/ 里的备份：
- 完全没有执行过 prepare；或者
- 只执行了
--apply-log-only模式的 prepare（这会把 redo log 应用到数据文件，但不会回滚未提交事务，也就不是一个“可启动”的一致状态）。
3. ☆ 解决方法
先对备份执行完整 prepare（不带 --apply-log-only），再执行 --copy-back。
# 完整准备备份
xtrabackup --prepare --target-dir=/full_xtrabackup
# 复制回数据目录（MySQL 必须已停止）
xtrabackup --copy-back --target-dir=/full_xtrabackup
如果xtrabackup --copy-back返回结果为`Completed OK!'，代表数据真正恢复成功！
[root@mysql-server ~]# ll /export/server/mysql/data/
总用量 116772
drwxr-x--- 8 root root 4096 4月 23 22:18 ./
drwxr-xr-x 11 mysql mysql 153 4月 23 22:18 ../
-rw-r----- 1 root root 157 4月 23 22:18 binlog.000026
-rw-r----- 1 root root 14 4月 23 22:18 binlog.index
drwxr-x--- 2 root root 25 4月 23 22:18 bookdb666/
drwxr-x--- 2 root root 28 4月 23 22:18 db_itheima/
-rw-r----- 1 root root 4553 4月 23 22:18 ib_buffer_pool
-rw-r----- 1 root root 12582912 4月 23 22:18 ibdata1
-rw-r----- 1 root root 12582912 4月 23 22:18 ibtmp1
drwxr-x--- 2 root root 6 4月 23 22:18 '#innodb_redo'/
drwxr-x--- 2 root root 143 4月 23 22:18 mysql/
-rw-r----- 1 root root 27262976 4月 23 22:18 mysql.ibd
drwxr-x--- 2 root root 8192 4月 23 22:18 performance_schema/
drwxr-x--- 2 root root 28 4月 23 22:18 sys/
-rw-r----- 1 root root 16777216 4月 23 22:18 undo_001
-rw-r----- 1 root root 50331648 4月 23 22:18 undo_002
-rw-r----- 1 root root 504 4月 23 22:18 xtrabackup_info
[root@mysql-server ~]#
如果出现Error: datadir must be specified. 出现以上问题的主要原因在于，xtrabackup 工具无法找到 MySQL 中的数据目录
解决方案：把my.cnf配置文件传递给xtrabackup ，让其自动识别这个文件中的datadir
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/full_xtrabackup
4. ☆ 第二步：修改权限，验证测试
恢复数据时，一定要记得更改/export/server/mysql/data目录下的文件拥有者以及所属组权限，否则mysql无法启动
[root@mysql-server ~]# ll /export/server/mysql/data
总用量 116772
drwxr-x--- 8 root root 4096 4月 23 22:18 ./
drwxr-xr-x 11 mysql mysql 153 4月 23 22:18 ../
-rw-r----- 1 root root 157 4月 23 22:18 binlog.000026
-rw-r----- 1 root root 14 4月 23 22:18 binlog.index
drwxr-x--- 2 root root 25 4月 23 22:18 bookdb666/
drwxr-x--- 2 root root 28 4月 23 22:18 db_itheima/
-rw-r----- 1 root root 4553 4月 23 22:18 ib_buffer_pool
-rw-r----- 1 root root 12582912 4月 23 22:18 ibdata1
-rw-r----- 1 root root 12582912 4月 23 22:18 ibtmp1
drwxr-x--- 2 root root 6 4月 23 22:18 '#innodb_redo'/
drwxr-x--- 2 root root 143 4月 23 22:18 mysql/
-rw-r----- 1 root root 27262976 4月 23 22:18 mysql.ibd
drwxr-x--- 2 root root 8192 4月 23 22:18 performance_schema/
drwxr-x--- 2 root root 28 4月 23 22:18 sys/
-rw-r----- 1 root root 16777216 4月 23 22:18 undo_001
-rw-r----- 1 root root 50331648 4月 23 22:18 undo_002
-rw-r----- 1 root root 504 4月 23 22:18 xtrabackup_info
[root@mysql-server ~]# chown -Rf mysql:mysql /export/server/mysql/
[root@mysql-server ~]# ll /export/server/mysql/data
总用量 116772
drwxr-x--- 8 mysql mysql 4096 4月 23 22:18 ./
drwxr-xr-x 11 mysql mysql 153 4月 23 22:18 ../
-rw-r----- 1 mysql mysql 157 4月 23 22:18 binlog.000026
-rw-r----- 1 mysql mysql 14 4月 23 22:18 binlog.index
drwxr-x--- 2 mysql mysql 25 4月 23 22:18 bookdb666/
drwxr-x--- 2 mysql mysql 28 4月 23 22:18 db_itheima/
-rw-r----- 1 mysql mysql 4553 4月 23 22:18 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 4月 23 22:18 ibdata1
-rw-r----- 1 mysql mysql 12582912 4月 23 22:18 ibtmp1
drwxr-x--- 2 mysql mysql 6 4月 23 22:18 '#innodb_redo'/
drwxr-x--- 2 mysql mysql 143 4月 23 22:18 mysql/
-rw-r----- 1 mysql mysql 27262976 4月 23 22:18 mysql.ibd
drwxr-x--- 2 mysql mysql 8192 4月 23 22:18 performance_schema/
drwxr-x--- 2 mysql mysql 28 4月 23 22:18 sys/
-rw-r----- 1 mysql mysql 16777216 4月 23 22:18 undo_001
-rw-r----- 1 mysql mysql 50331648 4月 23 22:18 undo_002
-rw-r----- 1 mysql mysql 504 4月 23 22:18 xtrabackup_info
[root@mysql-server ~]#
启动MySQL服务，进行验证。
[root@mysql-server ~]# systemctl start mysqld
[root@mysql-server ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/etc/systemd/system/mysqld.service; enabled; preset: disab>
Active: active (running) since Thu 2026-04-23 22:21:00 CST; 2s ago
Process: 5624 ExecStart=/export/server/mysql/bin/mysqld --daemonize --pid-f>
Main PID: 5626 (mysqld)
Tasks: 38 (limit: 22926)
Memory: 380.9M
CPU: 1.712s
CGroup: /system.slice/mysqld.service
└─5626 /export/server/mysql/bin/mysqld --daemonize --pid-file=/exp>
4月 23 22:20:58 mysql-server systemd[1]: Starting MySQL Server...
4月 23 22:21:00 mysql-server systemd[1]: Started MySQL Server.
[root@mysql-server ~]# mysql -uroot -p'MySQL@666'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.45 MySQL Community Server - GPL
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use db_itheima;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| tb_student |
+----------------------+
1 row in set (0.01 sec)
mysql> select * from tb_student;
+----+--------+------+--------+---------+
| id | name | age | gender | subject |
+----+--------+------+--------+---------+
| 1 | 刘备 | 35 | male | bigdata |
| 2 | 关羽 | 33 | male | yunwei |
| 3 | 张飞 | 30 | male | yunwei |
| 5 | 大乔 | 18 | female | ui |
| 6 | 小乔 | 16 | female | ui |
+----+--------+------+--------+---------+
5 rows in set (0.00 sec)
mysql>
备份：备份完成后，确认备份目录下有没有生成备份文件，终端有没有提示Complete Ok!
还原：Xtrabackup软件会到/etc/my.cnf中找datadir目录，所以必须要有这一行
还原后：我们的data文件夹中的所有数据都是root:root，必须更改为mysql:mysql，否则mysqld无法启动！