在任何数据库环境中，总会有 不确定的意外 情况发生，比如意外的停电、计算机系统中的各种软硬件故障、人为破坏、管理员误操作等是不可避免的，这些情况可能会导致 数据的丢失、服务器瘫痪 等严重的后果。存在多个服务器时，会出现主从服务器之间的 数据同步问题。

为了有效防止数据丢失，并将损失降到最低，应 定期 对 MySQL 数据库服务器做 备份。如果数据库中的数据丢失或者出现错误，可以使用备份的数据 进行恢复。主从服务器之间的数据同步问题可以通过复制功能实现。

16.1、备份类型

根据备份的方法（是否需要数据库离线）可以将备份分为：

• 热备（Hot Backup）：可以在数据库运行中直接备份，对正在运行的数据库操作没有任何的影响，数据库的读写操作可以正常执行。这种方式在 MySQL 官方手册中称为 Online Backup（在线备份）。
• 冷备（Cold Backup）：必须在数据库停止的情况下进行备份，数据库的读写操作不能执行。这种备份最为简单，一般只需要复制相关的数据库物理文件即可。这种方式在 MySQL 官方手册中称为 Offline Backup（离线备份）。
• 温备（Warm Backup）：同样是在数据库运行中进行的，但是会对当前数据库的操作有所影响，备份时仅支持读操作，不支持写操作。

按照备份后文件的内容，热备份又可以分为：

• 逻辑备份：是指备份出的文件内容是可读的，一般是文本内容。内容一般是由一条条 SQL 语句，或者是表内实际数据组成。如 mysqldump 和 SELECT * INTO OUTFILE 的方法。这类方法的好处是可以观察导出文件的内容，一般适用于数据库的升级、迁移等工作。但其缺点是恢复的时间较长。
• 物理备份：是指复制数据库的物理文件，既可以在数据库运行中进行复制（如 ibbackup、xtrabackup 这类工具），也可以在数据库停止运行时直接复制数据文件。这类备份的恢复时间往往比逻辑备份短很多。

按照备份数据库的内容来分，备份又可以分为：

• 完全备份：是指对数据库进行一个完整的备份，即备份整个数据库，如果数据较多会占用较大的时间和空间。
• 差异备份：指的是自上一次完全备份以来变化的数据。和增量备份相比，浪费空间，但恢复数据比增量备份简单。
• 增量备份：需要使用专业的备份工具。指的是在上次完全备份的基础上，对更改的数据进行备份。也就是说每次备份只会备份自上次备份之后到备份时间之内产生的数据。因此每次备份都比差异备份节约空间，但是恢复数据麻烦。

MySQL 中进行不同方式的备份还要考虑存储引擎是否支持，如 MyISAM 不支持热备，支持温备和冷备。而 InnoDB 支持热备、温备和冷备。

16.2、mysqldump 实现逻辑备份

mysqldump 是 MySQL 提供的一个非常有用的数据库备份工具。

16.2.1、备份一个数据库

mysqldump 命令执行时，可以将数据库备份成一个 文本文件，该文件中实际上包含多个 CREATE 和 INSERT 语句，使用这些语句可以重新创建表和插入数据。

• 查出需要备份的表的结构，在文本文件中生成一个 CREATE 语句。
• 将表中的所有记录转换为一条 INSERT 语句。

基本语法：

mysqldump –u 用户名称 –h 主机名称 –p密码 待备份的数据库名称[tbname, [tbname...]] > 备份文件名称.sql

说明：备份的文件并非一定要求后缀名为 .sql，例如后缀名为 .txt 的文件也是可以的。

举例：使用 root 用户备份 atguigu 数据库

mysqldump -uroot -p atguigu > atguigu.sql # 备份文件存储在当前目录下

mysqldump -uroot -p atguigudb1 > /var/lib/mysql/atguigu.sql

16.2.2、备份全部数据库

若想用 mysqldump 备份整个实例，可以使用 --all-databases 或 -A 参数：

mysqldump -uroot -pxxxxxx --all-databases > all_database.sql
mysqldump -uroot -pxxxxxx -A > all_database.sql

生产经验：

如果是 mysql 5.7 及以前的版本，使用全备份导出，再全备份导入，会很顺利。在 mysql 8.0 及以后的版本，使用全备份导入会有如下问题：

[root@base01 mysql8.0.36]# mysql_client/bin/mysql -uroot -h 172.23.0.2 -p -f < /backups/20250226_all_databases.sql
Enter password: 
ERROR 3554 (HY000) at line 352: Access to system table 'mysql.innodb_index_stats' is rejected.
ERROR 3554 (HY000) at line 355: Access to system table 'mysql.innodb_index_stats' is rejected.
ERROR 3554 (HY000) at line 372: Access to system table 'mysql.innodb_index_stats' is rejected.
ERROR 1062 (23000) at line 374: Duplicate entry 'mysql-component-PRIMARY-n_diff_pfx01' for key 'innodb_index_stats.PRIMARY'
ERROR 3554 (HY000) at line 382: Access to system table 'mysql.innodb_table_stats' is rejected.
ERROR 3554 (HY000) at line 385: Access to system table 'mysql.innodb_table_stats' is rejected.
ERROR 3554 (HY000) at line 400: Access to system table 'mysql.innodb_table_stats' is rejected.
ERROR 1062 (23000) at line 402: Duplicate entry 'mysql-component' for key 'innodb_table_stats.PRIMARY'

这个是强制导入全备份文件，出现 mysql.innodb_index_stats 和 mysql.innodb_table_stats 这两个表拒绝，对应全备份文件中的 352、355、372、374 ... 这些行，我们看一下是什么语句被拒绝。

 352 DROP TABLE IF EXISTS innodb_index_stats;
 355 CREATE TABLE innodb_index_stats (...);
 372 LOCK TABLES innodb_index_stats WRITE;
 374 INSERT INTO innodb_index_stats VALUES (...)
 382 DROP TABLE IF EXISTS innodb_table_stats;
 385 CREATE TABLE innodb_table_stats (...)
 400 LOCK TABLES innodb_table_stats WRITE;
 402 INSERT INTO innodb_table_stats VALUES (...)

这说明 mysql 8.0 拒绝删除和创建这两个表，这两个表存储了 InnoDB 存储引擎的统计信息，对于优化查询和维护数据库性能非常重要。

mysql.innodb_table_stats: 这个表保存了每个 InnoDB 表的统计数据，例如行数、索引大小等。

mysql.innodb_index_stats: 这个表保存了每个 InnoDB 索引的统计数据，类似于 innodb_table_stats，它记录了索引的基数、修改次数等信息。

这两个表由 innodb 存储引擎自动维护，就算在导入导出过程中丢失了这两个表，也不会影响数据库的主要功能，只会影响查询性能。

解决方案：

全备份时，可以忽略这两个表，通过实践，在全备份导入的时候，innodb 存储引擎会自动重建 mysql.innodb_index_stats 和 mysql.innodb_table_stats 这两个表，跟原数据库里面一模一样，实践版本 mysql 8.0.41，mysql 8.4.4，所以全备份导出如下：

mysqldump -uroot -p -h172.23.0.2 --all-databases --ignore-table=mysql.innodb_table_stats --ignore-table=mysql.innodb_index_stats > /backups/20250227_all_databases.sql

mysql -uroot -p < 20250227_all_databases.sql

如果全备份时没有忽略 mysql.innodb_index_stats 和 mysql.innodb_table_stats 这两个表，导入时，用 -f 强制导入也没有什么问题，生产环境也做过这种实践。

16.2.3、备份部分数据库

使用 --databases 或 -B 参数了，该参数后面跟数据库名称，多个数据库间用空格隔开。如果指定 databases 参数，备份文件中会存在创建数据库的语句，如果不指定参数，则不存在。语法如下：

mysqldump –u user –h host –p --databases [数据库的名称1 [数据库的名称2...]] > 备份文件名称.sql

举例：

mysqldump -uroot -p --databases atguigu1 atguigu2 > two_database.sql

或：

mysqldump -uroot -p -B atguigu1 atguigu2 > two_database.sql

16.2.4、备份部分表

比如，在表变更前做个备份。语法如下：

mysqldump –u user –h host –p 数据库的名称 [表名1 [表名2...]] > 备份文件名称.sql

举例：备份 atguigu 数据库下的 book 表：

mysqldump -uroot -p atguigu book > book.sql

备份多张表使用下面的命令，比如备份 book 和 account 表：

# 备份多张表
mysqldump -uroot -p atguigu book account > 2_tables_bak.sql

16.2.5、备份单表的部分数据

有些时候一张表的数据量很大，我们只需要部分数据。这时就可以使用 --where 选项了。where 后面附带需要满足的条件。

举例：备份 student 表中 id 小于 10 的数据

mysqldump -uroot -p atguigu student --where="id < 10 " > student_part_id10_low_bak.sql

内容如下所示，insert 语句只有 id 小于 10 的部分：

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,100002,'JugxTY',157,280),(2,100003,'QyUcCJ',251,277),
(3,100004,'lATUPp',80,404),(4,100005,'BmFsXI',240,171),(5,100006,'mkpSwJ',388,476),
(6,100007,'ujMgwN',259,124),(7,100008,'HBJTqX',429,168),(8,100009,'dvQSQA',61,504),
(9,100010,'HljpVJ',234,185);

16.2.6、排除某些表的备份

如果我们想备份某个库，但是某些表数据量很大或者与业务关联不大，这个时候可以考虑排除掉这些表，同样的，选项 --ignore-table 可以完成这个功能。

mysqldump -uroot -p atguigu --ignore-table=atguigu.student > no_stu_bak.sql

通过如下指定判定文件中没有 student 表结构：

grep "student" no_stu_bak.sql

16.2.7、只备份结构或只备份数据

只备份结构的话可以使用 --no-data 简写为 -d 选项；只备份数据可以使用 --no-create-info 简写为 -t 选项。

只备份结构：

mysqldump -uroot -p atguigu --no-data > atguigu_no_data_bak.sql
# 使用grep命令，没有找到insert相关语句，表示没有数据备份。
[root@node1 ~]# grep "INSERT" atguigu_no_data_bak.sql
[root@node1 ~]#

只备份数据：

mysqldump -uroot -p atguigu --no-create-info > atguigu_no_create_info_bak.sql
# 使用grep命令，没有找到create相关语句，表示没有数据结构。
[root@node1 ~]# grep "CREATE" atguigu_no_create_info_bak.sql
[root@node1 ~]#

16.2.8、备份中包含存储过程、函数、事件

mysqldump 备份默认是不包含存储过程，自定义函数及事件的。可以使用 --routines 或 -R 选项来备份存储过程及函数，使用 --events 或 -E 参数来备份事件。

举例：备份整个 atguigu 库，包含存储过程及事件。

• 使用下面的 SQL 可以查看当前库有哪些存储过程或者函数。

  mysql> SELECT SPECIFIC_NAME,ROUTINE_TYPE ,ROUTINE_SCHEMA FROM
  information_schema.Routines WHERE ROUTINE_SCHEMA="atguigu";
  +---------------+--------------+----------------+
  | SPECIFIC_NAME | ROUTINE_TYPE | ROUTINE_SCHEMA |
  +---------------+--------------+----------------+
  | rand_num      | FUNCTION     | atguigu        |
  | rand_string   | FUNCTION     | atguigu        |
  | BatchInsert   | PROCEDURE    | atguigu        |
  | insert_class  | PROCEDURE    | atguigu        |
  | insert_order  | PROCEDURE    | atguigu        |
  | insert_stu    | PROCEDURE    | atguigu        |
  | insert_user   | PROCEDURE    | atguigu        |
  | ts_insert     | PROCEDURE    | atguigu        |
  +---------------+--------------+----------------+
  9 rows in set (0.02 sec)

下面备份 atguigu 库的数据，函数以及存储过程。

mysqldump -uroot -p -R -E --databases atguigu > fun_atguigu_bak.sql

查询备份文件中是否存在函数，如下所示，可以看到确实包含了函数。

grep -C 5 "rand_num" fun_atguigu_bak.sql
--
--
-- Dumping routines for database 'atguigu'
--
/*!50003 DROP FUNCTION IF EXISTS `rand_num` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb3 */ ;
/*!50003 SET character_set_results = utf8mb3 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode =
'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISIO
N_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `rand_num`(from_num BIGINT ,to_num BIGINT) RETURNS
bigint
BEGIN
DECLARE i BIGINT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END ;;
--
BEGIN
DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO class ( classname,address,monitor ) VALUES
    (rand_string(8),rand_string(10),rand_num());
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END ;;
DELIMITER ;
--
BEGIN
DECLARE i INT DEFAULT 0;
    SET autocommit = 0; #设置手动提交事务
    REPEAT #循环
    SET i = i + 1; #赋值
    INSERT INTO order_test (order_id, trans_id ) VALUES
    (rand_num(1,7000000),rand_num(100000000000000000,700000000000000000));
    UNTIL i = max_num
    END REPEAT;
    COMMIT; #提交事务
END ;;
DELIMITER ;
--
BEGIN
DECLARE i INT DEFAULT 0;
    SET autocommit = 0; #设置手动提交事务
    REPEAT #循环
    SET i = i + 1; #赋值
    INSERT INTO student (stuno, name ,age ,classId ) VALUES
    ((START+i),rand_string(6),rand_num(),rand_num());
    UNTIL i = max_num
    END REPEAT;
    COMMIT; #提交事务
END ;;
DELIMITER ;
--
BEGIN
DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO `user` ( name,age,sex ) VALUES ("atguigu",rand_num(1,20),"male");
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END ;;
DELIMITER ;

16.3、mysql 命令恢复数据

使用 mysqldump 命令将数据库中的数据备份成一个文本文件。需要恢复时，可以使用 mysql命令 来恢复备份的数据。

mysql 命令可以执行备份文件中的 CREATE语句 和 INSERT语句。通过 CREATE 语句来创建数据库和表。通过 INSERT 语句来插入备份的数据。

基本语法：

mysql –u root –p [dbname] < backup.sql

其中，dbname 参数表示数据库名称。该参数是可选参数，可以指定数据库名，也可以不指定。

• 指定数据库名时，表示还原该数据库下的表。此时需要确保 MySQL 服务器中已经创建了该名的数据库。
• 不指定数据库名，表示还原文件中所有的数据库。此时 sql 文件中包含有 CREATE DATABASE 语句，不需要 MySQL 服务器中已存在的这些数据库。

16.3.1、单库备份中恢复单库

使用 root 用户，将之前练习中备份的 atguigu.sql 文件中的备份导入数据库中，命令如下。

如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称，如下所示：

mysql -uroot -p < atguigu.sql

否则需要指定数据库名称，如下所示：

mysql -uroot -p atguigu4 < atguigu.sql

16.3.2、全量备份恢复

如果我们现在有昨天的全量备份，现在想整个恢复，则可以这样操作：

mysql –u root –p < all.sql

执行完后，MySQL 数据库中就已经恢复了 all.sql 文件中的所有数据库。

可能有这样的需求，比如说我们只想恢复某一个库，但是我们有的是整个实例的备份，这个时候我们可以从全量备份中分离出单个库的备份。

举例：

sed -n '/^-- Current Database: `atguigu`/,/^-- Current Database: `/p' all_database.sql > atguigu.sql
# 分离完成后我们再导入atguigu.sql即可恢复单个库

16.3.3、从单库备份中恢复单表

这个需求还是比较常见的。比如说我们知道哪个表误操作了，那么就可以用单表恢复的方式来恢复。

举例：我们有 atguigu 整库的备份，但是由于 class 表误操作，需要单独恢复出这张表。

cat atguigu.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' > class_structure.sql
cat atguigu.sql | grep --ignore-case 'insert into `class`' > class_data.sql
# 用shell语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复

use atguigu;
mysql> source class_structure.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> source class_data.sql;
Query OK, 1 row affected (0.01 sec)

16.4、物理备份

直接将 MySQL 中的数据库文件复制出来。这种方法最简单，速度也最快。MySQL 的数据库目录位置不一定相同：

• 在 Windows 平台下，MySQL 8.0 存放数据库的目录通常默认为 C:\ProgramData\MySQL\MySQL Server 8.0\Data 或者其他用户自定义目录；
• 在 Linux 平台下，数据库目录位置通常为 /var/lib/mysql/；
• 在 MAC OSX 平台下，数据库目录位置通常为 /usr/local/mysql/data。

但为了保证备份的一致性。需要保证：

• 方式 1：备份前，将服务器停止。
• 方式 2：备份前，对相关表执行 FLUSH TABLES WITH READ LOCK 操作。这样当复制数据库目录中的文件时，允许其他客户继续查询表。同时，FLUSH TABLES 语句来确保开始备份前将所有激活的索引页写入硬盘。

这种方式方便、快速，但不是最好的备份方法，因为实际情况可能 不允许停止MySQL服务器 或者 锁住表，而且这种方法对 InnoDB 存储引擎的表不适用。

对于 MyISAM 存储引擎的表，这样备份和还原很方便，但是还原时最好是相同版本的 MySQL 数据库，否则可能会存在文件类型不同的情况。

注意，物理备份完毕后，执行 UNLOCK TABLES 来结算其他客户对表的修改行为。

说明：在 MySQL 版本号中，第一个数字表示主版本号，主版本号相同的 MySQL 数据库文件格式相同。

此外，还可以考虑使用相关工具实现备份。比如， MySQLhotcopy 工具。MySQLhotcopy 是一个 Perl 脚本，它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库。

它是备份数据库或单个表最快的途径，但它只能运行在数据库目录所在的机器上，并且只能备份 MyISAM 类型的表。多用于 mysql5.5 之前。

16.5、物理恢复

直接复制到数据库目录。

步骤：

1. 演示删除备份的数据库中指定表的数据。

2. 将备份的数据库数据拷贝到数据目录下，并重启 MySQL 服务器。

3. 查询相关表的数据是否恢复。需要使用下面的 chown 操作。

要求：

• 必须确保备份数据的数据库和待恢复的数据库服务器的主版本号相同。

  • 因为只有 MySQL 数据库主版本号相同时，才能保证这两个 MySQL 数据库文件类型是相同的。

• 这种方式对 MyISAM类型的表比较有效，对于 InnoDB 类型的表则不可用。

  • 因为 InnoDB 表的表空间不能直接复制。

• 在 Linux 操作系统下，复制到数据库目录后，一定要将数据库的用户和组变成 mysql，命令如下：

  chown -R mysql.mysql /var/lib/mysql/dbname

提示 Linux 操作系统下的权限设置非常严格。通常情况下，MySQL 数据库只有 root 用户和 mysql 用户组下的 mysql 用户才可以访问，因此将数据库目录复制到指定文件夹后，一定要使用 chown 命令将文件夹的用户组变为 mysql，将用户变为 mysql。

16.6、表的导出与导入

16.6.1、表的导出

16.6.1.1、使用 SELECT…INTO OUTFILE 导出文本文件

在 MySQL 中，可以使用 SELECT…INTO OUTFILE 语句将表的内容导出成一个文本文件。

举例：使用 SELECT…INTO OUTFILE 将 atguigu 数据库中 account 表中的记录导出到文本文件。

1. 选择数据库atguigu，并查询account表，执行结果如下所示：

   use atguigu;
   select * from account;
   mysql> select * from account;
   +----+--------+---------+
   | id | name | balance   |
   +----+--------+---------+
   | 1  | 张三    | 90      |
   | 2  | 李四    | 100     |
   | 3  | 王五    | 0       |
   +----+--------+---------+
   3 rows in set (0.01 sec)

2. mysql 默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录进行操作。

   查询 secure_file_priv 值：

   mysql> SHOW GLOBAL VARIABLES LIKE '%secure%';
   +--------------------------+-----------------------+
   | Variable_name            | Value                 |
   +--------------------------+-----------------------+
   | require_secure_transport | OFF                   |
   | secure_file_priv         | /var/lib/mysql-files/ |
   +--------------------------+-----------------------+
   2 rows in set (0.02 sec)

   参数 secure_file_priv 的可选值和作用分别是：

   • 如果设置为 empty，表示不限制文件生成的位置，这是不安全的设置；
   • 如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录；
   • 如果设置为 NULL，就表示禁止在这个 MySQL 实例上执行 select ...into outfile 操作。

3. 上面结果中显示，secure_file_priv 变量的值为 /var/lib/mysql-files/，导出目录设置为该目录，SQL 语句如下。

   SELECT * FROM account INTO OUTFILE "/var/lib/mysql-files/account.txt";

4. 查看 /var/lib/mysql-files/account.txt 文件。

   1 张三 90
   2 李四 100
   3 王五 0

16.6.1.2、使用 mysqldump 命令导出文本文件

举例 1：使用 mysqldump 命令将 atguigu 数据库中 account 表中的记录导出到文本文件：

mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account

mysqldump 命令执行完毕后，在指定的目录 /var/lib/mysql-files/ 下生成了 account.sql 和 account.txt 文件。

打开 account.sql 文件，其内容包含创建 account 表的 CREATE 语句。

[root@node1 mysql-files]# cat account.sql
-- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64)
--
-- Host: localhost Database: atguigu
-- ------------------------------------------------------
-- Server version 8.0.26
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `account`
--

DROP TABLE IF EXISTS `account`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `account` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`balance` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-01-07 23:19:27

打开 account.txt 文件，其内容只包含 account 表中的数据。

[root@node1 mysql-files]# cat account.txt
1 张三 90
2 李四 100
3 王五 0

举例 2：使用 mysqldump 将 atguigu 数据库中的 account 表导出到文本文件，使用 FIELDS 选项，要求字段之 间使用逗号 ， 间隔，所有字符类型字段值用双引号括起来：

mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account --fields-terminated-by=',' --fields-optionally-enclosed-by='\"'

语句 mysqldump 语句执行成功之后，指定目录下会出现两个文件 account.sql 和 account.txt。

打开 account.sql 文件，其内容包含创建 account 表的 CREATE 语句。

[root@node1 mysql-files]# cat account.sql
-- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64)
--
-- Host: localhost Database: atguigu
-- ------------------------------------------------------
-- Server version 8.0.26
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `account`
--
DROP TABLE IF EXISTS `account`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `account` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`balance` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2022-01-07 23:36:39

打开 account.txt 文件，其内容包含创建 account 表的数据。从文件中可以看出，字段之间用逗号隔开，字符类型的值被双引号括起来。

[root@node1 mysql-files]# cat account.txt
1,"张三",90
2,"李四",100
3,"王五",0

16.6.1.3、使用 mysql 命令导出文本文件

举例 1：使用 mysql 语句导出 atguigu 数据中 account 表中的记录到文本文件。

mysql -uroot -p --execute="SELECT * FROM account;" atguigu> "/var/lib/mysql-files/account.txt"

打开 account.txt 文件，其内容包含创建 account 表的数据。

[root@node1 mysql-files]# cat account.txt
id name balance
1 张三 90
2 李四 100
3 王五 0

举例 2：将 atguigu 数据库 account 表中的记录导出到文本文件，使用 --veritcal 参数将该条件记录分为多行显示：

mysql -uroot -p --vertical --execute="SELECT * FROM account;" atguigu > "/var/lib/mysql-files/account_1.txt"

打开 account_1.txt 文件，其内容包含创建 account 表的数据。

[root@node1 mysql-files]# cat account_1.txt
*************************** 1. row ***************************
id: 1
name: 张三
balance: 90
*************************** 2. row ***************************
id: 2
name: 李四
balance: 100
*************************** 3. row ***************************
id: 3
name: 王五
balance: 0

举例 3：将 atguigu 数据库 account 表中的记录导出到 xml 文件，使用 --xml 参数，具体语句如下。

mysql -uroot -p --xml --execute="SELECT * FROM account;" atguigu>"/var/lib/mysql-files/account_3.xml"

[root@node1 mysql-files]# cat account_3.xml
<?xml version="1.0"?>
<resultset statement="SELECT * FROM account"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="id">1</field>
<field name="name">张三</field>
<field name="balance">90</field>
</row>
<row>
<field name="id">2</field>
<field name="name">李四</field>
<field name="balance">100</field>
</row>
<row>
<field name="id">3</field>
<field name="name">王五</field>
<field name="balance">0</field>
</row>
</resultset>

说明：如果要将表数据导出到 html 文件中，可以使用 --html 选项。然后可以使用浏览器打开。

16.6.2、表的导入

16.6.2.1、使用 LOAD DATA INFILE 方式导入文本文件

举例 1：

使用 SELECT...INTO OUTFILE 将 atguigu 数据库中 account 表的记录导出到文本文件。

SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account_0.txt';

删除 account 表中的数据：

DELETE FROM atguigu.account;

从文本文件 account.txt 中恢复数据：

LOAD DATA INFILE '/var/lib/mysql-files/account_0.txt' INTO TABLE atguigu.account;

查询 account 表中的数据：

mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
| 1 | 张三     | 90      |
| 2 | 李四     | 100     |
| 3 | 王五     | 0       |
+----+--------+---------+
3 rows in set (0.00 sec)

举例 2： 选择数据库 atguigu，使用 SELECT…INTO OUTFILE 将 atguigu 数据库 account 表中的记录导出到文本文件，使用 FIELDS 选项和 LINES 选项，要求字段之间使用逗号 ， 间隔，所有字段值用双引号括起来：

SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account_1.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

删除 account 表中的数据：

DELETE FROM atguigu.account;

从 /var/lib/mysql-files/account_1.txt 中导入数据到 account 表中：

LOAD DATA INFILE '/var/lib/mysql-files/account_1.txt' INTO TABLE atguigu.account FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

查询 account 表中的数据，具体 SQL 如下：

select * from account;
mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
| 1 | 张三     | 90      |
| 2 | 李四     | 100     |
| 3 | 王五     | 0       |
+----+--------+---------+
3 rows in set (0.00 sec)

16.6.2.2、使用 mysqlimport 方式导入文本文件

举例：导出文件 account.txt，字段之间使用逗号 ， 间隔，字段值用双引号括起来

SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

删除 account 表中的数据：

DELETE FROM atguigu.account;

使用 mysqlimport 命令将 account.txt 文件内容导入到数据库 atguigu 的 account 表中：

mysqlimport -uroot -p atguigu '/var/lib/mysql-files/account.txt' --fields-terminated-by=',' --fields-optionally-enclosed-by='\"'

查询 account 表中的数据：

select * from account;
mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
| 1 | 张三     | 90      |
| 2 | 李四     | 100     |
| 3 | 王五     | 0       |
+----+--------+---------+
3 rows in set (0.00 sec)

16.7、数据库迁移

16.7.1、概述

数据迁移（data migration）是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。此外， 验证迁移数据的完整性 和 退役原来旧的数据存储，也被认为是整个数据迁移过程的一部分。

数据库迁移的原因是多样的，包括服务器或存储设备更换、维护或升级，应用程序迁移，网站集成，灾难恢复和数据中心迁移。

根据不同的需求可能要采取不同的迁移方案，但总体来讲，MySQL 数据迁移方案大致可以分为 物理迁移 和 逻辑迁移 两类。通常以尽可能 自动化 的方式执行，从而将人力资源从繁琐的任务中解放出来。

16.7.2、迁移方案

• 物理迁移

  物理迁移适用于大数据量下的整体迁移。使用物理迁移方案的优点是比较快速，但需要停机迁移并且要求 MySQL 版本及配置必须和原服务器相同，也可能引起未知问题。

  物理迁移包括拷贝数据文件和使用 XtraBackup 备份工具两种。

  不同服务器之间可以采用物理迁移，我们可以在新的服务器上安装好同版本的数据库软件，创建好相同目录，建议配置文件也要和原数据库相同，然后从原数据库方拷贝数据文件及日志文件过来，配置好文件组权限，之后在新服务器这边使用 mysqld 命令启动数据库。

• 逻辑迁移

  逻辑迁移适用范围更广，无论是 部分迁移 还是 全量迁移，都可以使用逻辑迁移。逻辑迁移中使用最多的就是 mysqldump 等备份工具。

16.7.3、迁移注意点

1. 相同版本的数据库之间迁移注意点

   指的是在主版本号相同的 MySQL 数据库之间进行数据库移动。

   方式1： 因为迁移前后 MySQL 数据库的 主版本号相同，所以可以通过复制数据库目录来实现数据库迁移，但是物理迁移方式只适用于 MyISAM 引擎的表。对于 InnoDB 表，不能用直接复制文件的方式备份数据库。

   方式2：最常见和最安全的方式是使用 mysqldump命令 导出数据，然后在目标数据库服务器中使用 MySQL 命令导入。

   举例：

   # host1的机器中备份所有数据库,并将数据库迁移到名为host2的机器上
   mysqldump –h host1 –uroot –p –-all-databases | mysql –h host2 –uroot –p

   在上述语句中，| 符号表示管道，其作用是将 mysqldump 备份的文件给 mysql 命令；--all-databases 表示要迁移所有的数据库。通过这种方式可以直接实现迁移。

2. 不同版本的数据库之间迁移注意点

   例如，原来很多服务器使用 5.7 版本的 MySQL 数据库，在 8.0 版本推出来以后，改进了 5.7 版本的很多缺陷，因此需要把数据库升级到 8.0 版本。

   旧版本与新版本的 MySQL 可能使用不同的默认字符集，例如有的旧版本中使用 latin1 作为默认字符集，而最新版本的 MySQL 默认字符集为 utf8mb4。如果数据库中有中文数据，那么迁移过程中需要对 默认字符集 进行修改 ，不然可能无法正常显示数据。

   高版本的 MySQL 数据库通常都会 兼容低版本，因此可以从低版本的 MySQL 数据库迁移到高版本的 MySQL 数据库。

3. 不同数据库之间迁移注意点

   不同数据库之间迁移是指从其他类型的数据库迁移到 MySQL 数据库，或者从 MySQL 数据库迁移到其他类型的数据库。这种迁移没有普适的解决方法。

   迁移之前，需要了解不同数据库的架构，比较它们之间的差异。不同数据库中定义相同类型的数据的 关键字可能会不同。

   • 例如，MySQL 中日期字段分为 DATE 和 TIME 两种，而 ORACLE 日期字段只有 DATE；SQL Server数据库中有 ntext、Image 等数据类型，MySQL数据库没有这些数据类型；MySQL 支持的 ENUM 和 SET 类型，这些 SQL Server 数据库不支持。

   另外，数据库厂商并没有完全按照 SQL 标准来设计数据库系统，导致不同的数据库系统的 SQL语句 有差别。例如，微软的 SQL Server 软件使用的是 T-SQL 语句，T-SQL 中包含了非标准的 SQL 语句，不能和 MySQL 的 SQL 语句兼容。

   不同类型数据库之间的差异造成了互相 迁移的困难，这些差异其实是商业公司故意造成的技术壁垒。但是不同类型的数据库之间的迁移并 不是完全不可能。

   • 例如，可以使用 MyODBC 实现 MySQL 和 SQL Server 之间的迁移。MySQL 官方提供的工具 MySQL Migration Toolkit 也可以在不同数据之间进行数据迁移。MySQL 迁移到 Oracle 时，需要使用 mysqldump 命令导出 sql 文件，然后，手动更改 sql 文件中的 CREATE 语句。

16.7.4、迁移小结

16.8、误删数据库

传统的高可用架构是不能预防误删数据的，因为主库的一个 drop table 命令，会通过 binlog 传给所有从库和级联从库，进而导致整个集群的实例都会执行这个命令。

为了找到解决误删数据的更高效的方法，我们需要先对 MySQL 相关的误删数据做下分类：

1. 使用 delete 语句误删数据行；
2. 使用 drop table 或者 truncate table 语句误删数据表；
3. 使用 drop database 语句误删数据库；
4. 使用 rm 命令误删整个 MySQL 实例。

16.8.1、DELETE 误删行

• 处理措施 1：数据恢复

  使用前提：binlog_format=row 和 binlog_row_image=FULL。

  工具：使用 Flashback 工具恢复数据。

  原理：修改 binlog 内容，拿回原库重放。如果误删数据涉及到了多个事务的话，需要将事务的顺序调过来再执行。

• 处理措施 2：预防

  代码上线前，必须 SQL 审查、审计。

  建议可以打开 安全模式，把 sql_safe_updates 参数设置为 on。强制要求加 where 条件且 where 后需要是索引字段，否则必须使用 limit。否则就会报错。

16.8.2、TRUNCATE/DROP 误删库/表

背景：
delete 全表是很慢的，需要生成回滚日志、写 redo、写 binlog。所以，从性能角度考虑，优先考虑使用 truncate table 或者 drop table 命令。

使用 delete 命令删除的数据，你还可以用 Flashback 来恢复。而使用 truncate /drop table 和 drop database 命令删除的数据，就没办法通过 Flashback 来恢复了。

因为，即使我们配置了 binlog_format=row，执行这三个命令时，记录的 binlog 还是 statement 格式。binlog 里面就只有一个 truncate/drop 语句，这些信息是恢复不出数据的。

方案：

这种情况下恢复数据，需要使用 全量备份 与 增量日志 结合的方式。

方案的前提：有定期的全量备份，并且实时备份 binlog。

举例：有人误删了一个库，时间为下午 3 点。

步骤如下：

1. 取最近一次 全量备份。假设设置数据库是一天一备，最近备份数据是当天 凌晨 2 点；
2. 用备份恢复出一个 临时库；(注意：这里选择临时库，而不是直接操作主库)
3. 取出凌晨 2 点之后的 binlog 日志；
4. 剔除误删除数据的语句外，其它语句全部应用到临时库；(前面讲过 binlog 的恢复)
5. 最后恢复到主库。

16.8.3、预防误删库/表

上面我们说了使用 truncate /drop 语句误删库/表的恢复方案，在生产环境中可以通过下面建议的方案来尽量的避免类似的误操作。

1. 权限分离

   限制帐户权限，核心的数据库，一般都 不能随便分配写权限，想要获取写权限需要 审批。比如只给业务开发人员 DML 权限，不给 truncate/drop 权限。即使是 DBA 团队成员，日常也都规定只使用只读账号，必要的时候才使用有更新权限的账号。

   不同的账号，不同的数据之间要进行 权限分离，避免一个账号可以删除所有库。

2. 制定操作规范

   比如在删除数据表之前，必须先对表做改名操作(比如标记 _to_be_deleted)。然后，观察一段时间，确保对业务无影响以后再删除这张表。

3. 设置延迟复制备库

   简单的说延迟复制就是设置一个固定的延迟时间，比如 1 个小时，让从库落后主库一个小时。出现误删除操作 1 小时内，到这个备库上执行 stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。

   这里通过 CHANGE MASTER TO MASTERDELAY = N 命令，可以指定这个备库持续保持跟主库有 N 秒的延迟。比如把 N 设置为 3600，即代表 1 个小时。

此外，延迟复制还可以用来解决以下问题：

1. 用来做 延迟测试，比如做好的数据库读写分离，把从库作为读库，那么想知道当数据产生延迟的时候到底会发生什么，就可以使用这个特性模拟延迟。
2. 用于 老数据的查询等需求，比如你经常需要查看某天前一个表或者字段的数值，你可能需要把备份恢复后进行查看，如果有延迟从库，比如延迟一周，那么就可以解决这样类似的需求。

16.8.4、rm -rf 误删 MySQL 实例

对于一个有高可用机制的 MySQL 集群来说，不用担心 rm 删除数据了。只是删掉了其中某一个节点的数据的话，HA 系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。我们要做的就是在这个节点上把数据恢复回来，再接入整个集群。

但如果是恶意地把整个集群删除，那就需要考虑跨机房备份，跨城市备份。

16.9、mysqldump 案例

16.9.1、背景环境

正在运行的网站系统，mysql 数据库，数据量 25G，日业务增量 10-15M。每天 23:00 点，利用计划任务调用 mysqldump 执行全备脚本。

问题：上午 10 点，误删除了一个表，考虑使用 mysqldump 进行恢复。

16.9.2、思路

1. 断开业务，防止对数据库二次伤害，挂出维护页面。

2. 搭建备用库，恢复全备。

3. 截取昨天晚上 23:00 之后到上午 10 点误删除操作之前的二进制日志。

4. 恢复到备用库，验证数据可用性和完整性。

5. 两种方案恢复前端应用。

   • 备用库导出误删除的表，导入到生产库，开启业务。
   • 直接将应用切割到备用库，替代生产库，开启业务。

16.9.3、模拟故障并恢复

1. 创建原始数据如下：

   mysql> CREATE DATABASE IF NOT EXISTS bingo;
   Query OK, 1 row affected (0.02 sec)
   
   mysql> use bingo;
   Database changed
   mysql>
   mysql> CREATE TABLE t1(id int, name varchar(20));
   Query OK, 0 rows affected (0.10 sec)
   
   mysql> INSERT INTO t1 VALUES (1, '小A');
   Query OK, 1 row affected (0.01 sec)
   
   mysql> INSERT INTO t1 VALUES (2, '小B');
   Query OK, 1 row affected (0.00 sec)
   
   mysql> INSERT INTO t1 VALUES (3, '小C');
   Query OK, 1 row affected (0.01 sec)
   
   mysql> commit;
   Query OK, 0 rows affected (0.00 sec)

2. 模拟前一天晚上 23:00 全备

   [root@master1 ~]# mysqldump -uroot -p -A -R -E --source-data=2 --single-transaction | gzip > all_$(date +%F).sql.gz

3. 模拟白天（23:00-10:00）业务对数据的修改

   mysql> INSERT INTO t1 VALUES (4, '小D'), (5, '小E'), (6, '小F');
   Query OK, 3 rows affected (0.00 sec)
   Records: 3  Duplicates: 0  Warnings: 0
   
   mysql> commit;
   Query OK, 0 rows affected (0.00 sec)

4. 模拟故障

   mysql> DROP TABLE t1;
   Query OK, 0 rows affected (0.02 sec)

5. 恢复过程

   1. 准备全备，并获取到备份文件中的 binlog 的截取起点

      # 把当前业务节点的全备日志传到slave1节点上，准备导入全备数据
      [root@master1 ~]# scp all_2022-12-14.sql.gz root@192.168.50.201:/root
      root@192.168.50.201's password:
      all_2022-12-14.sql.gz                                                          100%  271KB 271.5KB/s   00:00
      
      # 从二进制日志来看，事务Xid=79是23:00插入"小C"这条记录的最后一次提交，以position 3363结束，也就是说position 3363是23:00后面发生事件的开始。
      [root@master1 ~]# mysqlbinlog -v /var/lib/mysql/mysql-bin.000003 | grep -E -i -A 10  "小C"
      ###   @2='小C'
      # at 3332
      #221214 12:15:36 server id 1  end_log_pos 3363 CRC32 0x30451c78         Xid = 79
      COMMIT/*!*/;
      # at 3363
      #221214 13:32:38 server id 1  end_log_pos 3442 CRC32 0x86f71f87         Anonymous_GTID  last_committed=12       sequence_number=13       rbr_only=yes    original_committed_timestamp=1670995958803970   immediate_commit_timestamp=1670995958803970      transaction_length=313
      /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
      # original_commit_timestamp=1670995958803970 (2022-12-14 13:32:38.803970 CST)
      # immediate_commit_timestamp=1670995958803970 (2022-12-14 13:32:38.803970 CST)
      /*!80001 SET @@session.original_commit_timestamp=1670995958803970*//*!*/;
      /*!80014 SET @@session.original_server_version=80031*//*!*/;

   2. 截取 23:00-10:00 之间删除表 t1 之前的二进制日志

      # 通过查看binlog事件，可以定位这段日志start position是3363，end position是3676
      mysql> show binlog events in 'mysql-bin.000003' from 3363;
      +------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
      | Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                  |
      +------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
      | mysql-bin.000003 | 3363 | Anonymous_Gtid |         1 |        3442 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
      | mysql-bin.000003 | 3442 | Query          |         1 |        3523 | BEGIN                                                                 |
      | mysql-bin.000003 | 3523 | Table_map      |         1 |        3580 | table_id: 229 (bingo.t1)                                              |
      | mysql-bin.000003 | 3580 | Write_rows     |         1 |        3645 | table_id: 229 flags: STMT_END_F                                       |
      | mysql-bin.000003 | 3645 | Xid            |         1 |        3676 | COMMIT /* xid=1496 */                                                 |
      | mysql-bin.000003 | 3676 | Anonymous_Gtid |         1 |        3753 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
      | mysql-bin.000003 | 3753 | Query          |         1 |        3887 | use bingo; DROP TABLE t1 /* generated by server */ /* xid=1508 */ |
      +------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
      7 rows in set (0.00 sec)
      
      # 截取这段日志
      [root@master1 ~]# mysqlbinlog --start-position=3363 --stop-position=3676 /var/lib/mysql/mysql-bin.000003 |gzip > 2300-1000_sql.gz
      
      # 将这段日志传到slave1上去
      [root@master1 ~]# scp 2300-1000_sql.gz root@192.168.50.201:/root
      root@192.168.50.201's password:
      2300-1000_sql.gz

   3. 在 slave1 上恢复全备数据和 23:00-10:00 之间删除表 t1 之前的二进制日志

      [root@slave1 ~]# gunzip all_2022-12-14.sql.gz
      [root@slave1 ~]# gunzip 2300-1000_sql.gz
      [root@slave1 ~]# mysql -uroot -p < all_2022-12-14.sql
      Enter password:
      [root@slave1 ~]# mysql -uroot -p < 2300-1000_sql
      Enter password:

   4. 检查 slave1 上数据是否全部恢复

      # 已经全部恢复
      mysql> use bingo;
      Database changed
      mysql>
      mysql> select * from t1;
      +------+------+
      | id   | name |
      +------+------+
      |    1 | 小A  |
      |    2 | 小B  |
      |    3 | 小C  |
      |    4 | 小D  |
      |    5 | 小E  |
      |    6 | 小F  |
      +------+------+
      6 rows in set (0.01 sec)

16.10、Xtrabackup 用例

Xtrabackup 是 percona 公司的备份工具，性能比较高。物理备份工具，在同级数据量基础上，都要比逻辑备份性能要好的多。特别是在数据量比较大的时候，体现的更加明显。

原理请参见 https://docs.percona.com/percona-xtrabackup 查看 How Percona XtraBackup works

16.10.1、安装 Xtrabackup

下载地址：https://www.percona.com/downloads/Percona-XtraBackup-LATEST/

[root@node01 ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.30-23/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.30-23.1.el7.x86_64.rpm
[root@node01 ~]# yum localinstall percona-xtrabackup-80-8.0.30-23.1.el7.x86_64.rpm

16.10.2、备份与恢复

xtrabackup 用法参见 https://docs.percona.com/percona-xtrabackup 查看 How to

下面我们来模拟 xtrabackup 的备份与恢复过程，大致思路是周日全备，周一到周六做增量。

1. 模拟生产数据

   mysql> CREATE DATABASE IF NOT EXISTS soga;
   Query OK, 1 row affected (0.00 sec)
   
   mysql> USE soga;
   Database changed
   mysql>
   mysql> CREATE TABLE lovers(id INT,
      -> age INT,
      -> name VARCHAR(20));
   Query OK, 0 rows affected (0.01 sec)
   mysql> INSERT INTO lovers VALUES (1, 20, 'Catherine'),
      -> (2, 22, 'Tabaccy'),
      -> (3, 20, 'Youga'),
      -> (4, 22, 'Lucy');
   Query OK, 4 rows affected (0.01 sec)
   Records: 4  Duplicates: 0  Warnings: 0

2. 周日全备

   [root@node01 ~]# mkdir /backups/mysql/full -pv
   mkdir: created directory ‘/backups’
   mkdir: created directory ‘/backups/mysql’
   mkdir: created directory ‘/backups/mysql/full’
   
   [root@node01 ~]# xtrabackup --user=root --password=WelC0me168! --backup --target-dir=/backups/mysql/full/ --no-server-version-check

   注意：percona-xtrabackup 的版本要跟 mysql 的版本匹配，我这里 mysql v8.0.31，percona-xtrabackup v8.0.30-23，应用起来不匹配，类似如下报错：

   xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
   221216 22:38:35  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
   221216 22:38:35  version_check Connected to MySQL server
   221216 22:38:35  version_check Executing a version check against the server...
   221216 22:38:35  version_check Done.
   2022-12-16T22:38:35.442900+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
   2022-12-16T22:38:35.455859+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Unsupported server version 8.0.31
   2022-12-16T22:38:35.455898+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Please upgrade PXB, if a new version is available. To continue with risk, use the option --no-server-version-check.

   生产环境一定要版本匹配使用。我这里先跳过版本检查。

3. 周一生产新数据，晚上增量备份

   mysql> INSERT INTO lovers VALUES (5, 18, 'Lily');
   Query OK, 1 row affected (0.01 sec)
   
   mysql> INSERT INTO lovers VALUES (6, 18, 'Snowy');
   Query OK, 1 row affected (0.01 sec)
   
   [root@node01 ~]# xtrabackup --user=root --password=WelC0me168! --backup --target-dir=/backups/mysql/inc_date +'%Y%m%d_%H%M%S' --incremental_basedir=/backups/mysql/full/ --no-server-version-check

4. 周二生产新数据，晚上增量备份

   mysql> INSERT INTO lovers VALUES (7, 18, 'Elena');
   Query OK, 1 row affected (0.01 sec)
   
   mysql> INSERT INTO lovers VALUES (8, 18, 'Elena');
   Query OK, 1 row affected (0.00 sec)
   
   [root@node01 ~]# xtrabackup --user=root --password=WelC0me168! --backup --target-dir=/backups/mysql/inc_date +'%Y%m%d_%H%M%S' --incremental_basedir=/backups/mysql/inc_20220828_051639/ --no-server-version-check

5. 周三上午做了很多操作，10:00 时刻，误删 lovers 表

   # 模拟多了下面这些数据
   mysql> INSERT INTO lovers VALUES (9, 18, 'Bonnie');
   Query OK, 1 row affected (0.01 sec)
   
   mysql> INSERT INTO lovers VALUES (10, 18, 'Caroline');
   Query OK, 1 row affected (0.00 sec)
   
   mysql> SELECT * FROM lovers;
   +------+------+-----------+
   | id   | age  | name      |
   +------+------+-----------+
   |    1 |   20 | Catherine |
   |    2 |   22 | Tabaccy   |
   |    3 |   20 | Youga     |
   |    4 |   22 | Lucy      |
   |    5 |   18 | Lily      |
   |    6 |   18 | Snowy     |
   |    7 |   18 | Elena     |
   |    8 |   18 | Elena     |
   |    9 |   18 | Bonnie    |
   |   10 |   18 | Caroline  |
   +------+------+-----------+
   10 rows in set (0.00 sec)
   
   # 误删 lovers 表
   mysql> DROP TABLE lovers;
   Query OK, 0 rows affected (0.01 sec)

6. 预恢复备份数据

   [root@node01 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backups/mysql/full
   [root@node01 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backups/mysql/full --incremental-dir=/backups/mysql/inc_20220828_051639/
   [root@node01 ~]# xtrabackup --prepare --target-dir=/backups/mysql/full --incremental-dir=/backups/mysql/inc_20220828_051735/

   使用 xtrabackup 进行数据库数据恢复之前，需要先进行 预恢复备份，假设数据库备份方案是 全量备份 + 增量备份，那么这个预恢复就分为两步来完成：

   第一步：先进行 预恢复全量备份

   xtrabackup --prepare --apply-log-only --target-dir=/backups/mysql/full

   第二步：再进行 预恢复增量备份，合并当前预恢复备份到前一个备份（可能是全量或增量备份）目录，如果有多个增量备份，依次完成所有增量备份的预恢复操作

   xtrabackup --prepare --target-dir=/backups/mysql/full --incremental-dir=/backups/mysql/incr

   全量 + 增量预恢复备份，最核心的部分就是 --apply-log-only 参数，可以看下增量备份官方文档说明：

   --prepare 增量备份的步骤与普通备份不同。在正常备份中，执行两种类型的操作来使数据库保持一致：

   • 针对数据文件从日志文件重放已提交的事务，以及回滚未提交的事务。
   • 在准备备份时，您必须跳过未提交事务的回滚，因为在您备份时未提交的事务可能正在进行中，并且它们很可能会在下一次增量备份中提交。您应该使用该 --apply-log-only 选项来防止回滚阶段。

7. 恢复备份数据

   [root@node01 ~]# systemctl stop mysqld
   [root@node01 ~]# cp -ar /var/lib/mysql /tmp/
   [root@node01 ~]#
   [root@node01 ~]# rm -rf /var/lib/mysql/*
   [root@node01 ~]#
   [root@node01 ~]# xtrabackup --copy-back --target-dir=/backups/mysql/full/

   • 先要停止 mysqld 服务，然后清空数据库目录下的数据，否则恢复时报错：

     Original data directory /var/lib/mysql is not empty!

8. 数据文件授权后启动服务

   # xtrabackup拷贝过来的数据文件属主属组是root，改成mysql才能启动服务
   [root@node01 ~]# chown -R mysql:mysql /var/lib/mysql
   [root@node01 ~]# systemctl start mysqld
   
   # 查看一下数据完整性
   mysql> SELECT * FROM lovers;
   +------+------+-----------+
   | id   | age  | name      |
   +------+------+-----------+
   |    1 |   20 | Catherine |
   |    2 |   22 | Tabaccy   |
   |    3 |   20 | Youga     |
   |    4 |   22 | Lucy      |
   |    5 |   18 | Lily      |
   |    6 |   18 | Snowy     |
   |    7 |   18 | Elena     |
   |    8 |   18 | Elena     |
   +------+------+-----------+
   8 rows in set (0.00 sec)

   通过查看，很明显最后一次增量备份时间点到误删 lovers 表时间点之间的数据变化没有了，可以通过二进制日志补充缺失的数据变化。

9. 截取最后一次备份结束的 position 号和误删除之前的 position 号

   # 最后一次备份截取的position
   [root@node01 ~]# cat /backups/mysql/inc_20220828_051735/xtrabackup_binlog_info
   binlog.000004   157
   
   # 前面实验把mysql数据文件移到/tmp目录下了，查出误删lovers表之前的position
   [root@node01 ~]# mysqlbinlog -v /tmp/mysql/binlog.000004| grep -Ei -A 10 -B 10 'DROP TABLE lovers'
   #220828  5:20:24 server id 1  end_log_pos 989 CRC32 0x2c6b6ed7  Anonymous_GTID  last_committed=3        sequence_number=4        rbr_only=no     original_committed_timestamp=1661635224681343   immediate_commit_timestamp=1661635224681343      transaction_length=208
   # original_commit_timestamp=1661635224681343 (2022-08-28 05:20:24.681343 CST)
   # immediate_commit_timestamp=1661635224681343 (2022-08-28 05:20:24.681343 CST)
   /*!80001 SET @@session.original_commit_timestamp=1661635224681343*//*!*/;
   /*!80014 SET @@session.original_server_version=80031*//*!*/;
   /*!80014 SET @@session.immediate_server_version=80031*//*!*/;
   SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
   # at 989
   #220828  5:20:24 server id 1  end_log_pos 1120 CRC32 0x102f4c79         Query   thread_id=13    exec_time=0     error_code=0     Xid = 121
   SET TIMESTAMP=1661635224/*!*/;
   DROP TABLE lovers /* generated by server */
   /*!*/;
   # at 1120
   #220828  5:20:38 server id 1  end_log_pos 1197 CRC32 0x55a6ede6         Anonymous_GTID  last_committed=4        sequence_number=5        rbr_only=no     original_committed_timestamp=1661635238095403   immediate_commit_timestamp=1661635238095403      transaction_length=159
   # original_commit_timestamp=1661635238095403 (2022-08-28 05:20:38.095403 CST)
   # immediate_commit_timestamp=1661635238095403 (2022-08-28 05:20:38.095403 CST)
   /*!80001 SET @@session.original_commit_timestamp=1661635238095403*//*!*/;
   /*!80014 SET @@session.original_server_version=80031*//*!*/;
   /*!80014 SET @@session.immediate_server_version=80031*//*!*/;
   SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
   # at 1197

   很容易查到误删 lovers 表之前的 position 是 989，所以使用 binlog 补充缺失的数据变化

   [root@node01 ~]# mysqlbinlog --start-position=157 --stop-position=989 /tmp/mysql/binlog.000004 > makeup.sql
   [root@node01 ~]# mysql -uroot -p < makeup.sql
   Enter password:
   [root@node01 ~]# mysql -uroot -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 11
   Server version: 8.0.31 MySQL Community Server - GPL
   
   Copyright (c) 2000, 2022, 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> use soga;
   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>
   mysql> select * from lovers;
   +------+------+-----------+
   | id   | age  | name      |
   +------+------+-----------+
   |    1 |   20 | Catherine |
   |    2 |   22 | Tabaccy   |
   |    3 |   20 | Youga     |
   |    4 |   22 | Lucy      |
   |    5 |   18 | Lily      |
   |    6 |   18 | Snowy     |
   |    7 |   18 | Elena     |
   |    8 |   18 | Elena     |
   |    9 |   18 | Bonnie    |
   |   10 |   18 | Caroline  |
   +------+------+-----------+
   10 rows in set (0.00 sec)

   至此，数据已全部恢复。

16.11、生产案例 1

官方文档：https://docs.percona.com/percona-xtrabackup/8.0/index.html

银行级生产环境，全量 + 增量 + 二进制日志（binlog） + 高可用 + 灾备

项目	内容
频率	全量备份：每天一次（如凌晨 2 点） 增量备份：每 1 小时一次或每 6 小时一次（视变更量和 RPO 要求） binlog 实时备份（或至少每小时归档）
备份工具	MySQL Enterprise Backup(企业版)：使用其增量 + 全量模块； Percona XtraBackup(社区版)：全量 + 增量 + binlog
存储方案	本地磁盘 + 异地备份（备份服务器或对象存储，比如 S3/OSS）使用压缩和加密以节省空间并提高安全性
高可用性	主从复制 + 半同步模式，以确保提交的事务至少被一个从节点确认如有条件，部署多活 / 集群
灾备	异地库 + 异地备份 + 完整恢复演练定期测试恢复流程，验证备份的完整性

16.11.1、备份

16.11.1.1、环境准备

我们的基础环境是 Rockylinux 9.2 + MySQL 8.0.43

1、安装 Percona XtraBackup 8 兼容 MySQL 8

# 添加 Percona 仓库
sudo dnf install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release setup ps80
sudo dnf install -y percona-xtrabackup-80

2、创建备份用户（官方推荐，避免 root 直接操作）

mysql -u root -p
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'StrongBackupPwd!';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
GRANT SELECT ON performance_schema.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

• 如果你的 mysqld 使用了 keyring 插件/组件（比如加密表空间），还可能需要额外的 BACKUP_ADMIN 权限（MySQL 企业版有，社区版没有）。

3、准备备份目录

sudo mkdir -p /backup/mysql/{full,incr,binlog,log}
sudo chown -R mysql:mysql /backup/mysql

16.11.1.2、准备脚本

/usr/local/bin/mysql_full_backup.sh

#!/bin/bash
set -euo pipefail
DATE=$(date +%Y%m%d%H%M%S)
FULLDIR=/backup/mysql/full/${DATE}
LOG=/backup/mysql/log/full_${DATE}.log

mkdir -p "${FULLDIR}"
exec >>"${LOG}" 2>&1

echo "START FULL BACKUP: ${DATE}"

xtrabackup --backup --target-dir="${FULLDIR}" --user=backup --password='StrongBackupPwd!'
xtrabackup --prepare --target-dir="${FULLDIR}"

# 生成 latest 链接便于增量使用
ln -sfn "${FULLDIR}" /backup/mysql/full/latest_prepared

echo "FULL BACKUP COMPLETE: ${FULLDIR}"

默认情况下，xtrabackup 会通过 MySQL 配置文件 /etc/my.cnf 自动探测 datadir。如果没找到，默认把 /var/lib/mysql 当数据目录。也可以手动指定数据目录。查看官方文档。

For all modes, the default options are read from the xtrabackup and mysqld configuration groups from the following files in the given order:
/etc/my.cnf
/etc/mysql/my.cnf
/usr/etc/my.cnf
~/.my.cnf.

xtrabackup --backup \
           --target-dir="${FULLDIR}" \
           --datadir=/data/mysql/data \ # 指定 MySQL 数据目录（InnoDB 文件和表空间所在目录）
           --user=${BACKUP_USER} \
           --password="${BACKUP_PASS}"

/usr/local/bin/mysql_incr_backup.sh

#!/bin/bash
set -euo pipefail
DATE=$(date +%Y%m%d%H%M%S)
INCDIR=/backup/mysql/incr/${DATE}
LOG=/backup/mysql/log/incr_${DATE}.log
FULL_BASE_DIR=/backup/mysql/full/latest_prepared

mkdir -p "${INCDIR}"
exec >>"${LOG}" 2>&1

echo "START INCREMENTAL BACKUP: ${DATE}"

xtrabackup --backup --target-dir="${INCDIR}" --incremental-basedir="${FULL_BASE_DIR}" \
    --user=backup --password='StrongBackupPwd!'

echo "INCREMENTAL BACKUP COMPLETE: ${INCDIR}"

/usr/local/bin/mysql_binlog_archive.sh

#!/bin/bash
set -euo pipefail
DSTDIR=/backup/mysql/binlog/$(date +%Y%m%d)
mkdir -p "${DSTDIR}"
cp -ar /var/lib/mysql/mysql_bin.* "${DSTDIR}/" 2>/dev/null || true
echo "$(date +%F_%T) COPIED binlogs to ${DSTDIR}"

16.11.1.3、配置 systemd 服务和 Timer

全量

/etc/systemd/system/mysql-backup-full.service

[Unit]
Description=MySQL Full Backup (xtrabackup)
After=network.target mysqld.service
Wants=mysqld.service

[Service]
Type=oneshot
ExecStart=/usr/local/bin/mysql_full_backup.sh
User=root
Group=root
TimeoutStartSec=0
StandardOutput=journal
StandardError=journal

/etc/systemd/system/mysql-backup-full.timer

[Unit]
Description=Timer for MySQL Full Backup daily at 02:00

[Timer]
OnCalendar=*-*-* 02:00:00
Persistent=true
RandomizedDelaySec=5m
AccuracySec=1s

[Install]
WantedBy=timers.target

增量

/etc/systemd/system/mysql-backup-incr.service

[Unit]
Description=MySQL Incremental Backup (xtrabackup)
After=network.target mysqld.service
Wants=mysqld.service

[Service]
Type=oneshot
ExecStart=/usr/local/bin/mysql_incr_backup.sh
User=root
Group=root
TimeoutStartSec=0
StandardOutput=journal
StandardError=journal

/etc/systemd/system/mysql-backup-incr.timer

[Unit]
Description=Timer for MySQL Incremental Backup every 6 hours

[Timer]
OnCalendar=*-*-* 02/6:00:00
Persistent=true
RandomizedDelaySec=30m
AccuracySec=1s

[Install]
WantedBy=timers.target

Binlog

/etc/systemd/system/mysql-backup-binlog.service

[Unit]
Description=MySQL Binlog Archive
After=network.target mysqld.service
Wants=mysqld.service

[Service]
Type=oneshot
ExecStart=/usr/local/bin/mysql_binlog_archive.sh
User=root
Group=root
TimeoutStartSec=0
StandardOutput=journal
StandardError=journal

/etc/systemd/system/mysql-backup-binlog.timer

[Unit]
Description=Timer to archive MySQL binlogs hourly

[Timer]
OnCalendar=*-*-* *:05:00
Persistent=true
RandomizedDelaySec=5m
AccuracySec=30s

[Install]
WantedBy=timers.target

16.11.1.4、部署步骤

# 保存脚本并授权
chmod +x /usr/local/bin/mysql_*.sh

# systemd 重新加载
systemctl daemon-reload

# 启用 Timer
systemctl enable --now mysql-backup-full.timer
systemctl enable --now mysql-backup-incr.timer
systemctl enable --now mysql-backup-binlog.timer

# 查看 Timer 状态
systemctl list-timers | grep mysql-backup

# 手动测试
systemctl start mysql-backup-full.service
journalctl -u mysql-backup-full.service -f

官方建议：全量备份每天至少 1 次，增量备份结合全量和 binlog，实现 Point-In-Time Recovery (PITR)。

16.11.2、PITR 恢复

16.11.2.1、环境准备

确认备份文件存在

ls -lh /backup/mysql/full/latest_prepared
ls -lh /backup/mysql/incr/
ls -lh /backup/mysql/binlog/

确保你有：

• 最新全量备份目录 /backup/mysql/full/latest_prepared
• 所有增量备份目录
• 所有 binlog 文件（从上次全量备份开始的）

16.11.2.3、增量合并

假设你有多次增量备份（按时间顺序排列）：

# 示例：有两个增量备份
xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full/latest_prepared \
    --incremental-dir=/backup/mysql/incr/20250917080000

xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full/latest_prepared \
    --incremental-dir=/backup/mysql/incr/20250917140000

xtrabackup --prepare --target-dir=/backup/mysql/full/latest_prepared

注意：每个增量备份必须按时间顺序 apply，最后一次增量 apply 完后，再进行最后一次 --prepare 不加 --apply-log-only。

对基线（full）和每个中间阶段的合并都用 --apply-log-only（只做 redo，不做回滚），按时间顺序依次把增量应用到基线；

在最后一次增量应用完之后，再运行一次完整的 xtrabackup --prepare（不带 --apply-log-only），以执行回滚（undo）并得到一致性的备份结果。

时间 → ------------------------------------------------------------------------------------

  Full backup (BASE)
    created at T0
    xtrabackup writes: xtrabackup_checkpoints { from_lsn=0, to_lsn = LSN_B }

    Command to create:
      xtrabackup --backup --target-dir=/data/backups/base

  INC1 (changes after T0)
    created at T1
    inc1.xtrabackup_checkpoints { from_lsn = LSN_B, to_lsn = LSN_I1 }

    Command to create:
      xtrabackup --backup --incremental-basedir=/data/backups/base \
                 --target-dir=/data/backups/inc1

  INC2 (changes after T1)
    created at T2
    inc2.xtrabackup_checkpoints { from_lsn = LSN_I1, to_lsn = LSN_I2 }

    Command to create:
      xtrabackup --backup --incremental-basedir=/data/backups/inc1 \
                 --target-dir=/data/backups/inc2

恢复 (apply) 流程：
  STEP A: prepare base (redo only)
    xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base
    => base now at LSN_B (crash-like state, undo NOT applied)

  STEP B: apply inc1 (redo only)
    xtrabackup --prepare --apply-log-only \
      --target-dir=/data/backups/base --incremental-dir=/data/backups/inc1
    => base now at LSN_I1

  STEP C: apply inc2 (redo only)
    xtrabackup --prepare --apply-log-only \
      --target-dir=/data/backups/base --incremental-dir=/data/backups/inc2
    => base now at LSN_I2

  STEP D: final prepare (redo + undo) # 最后一次 prepare 仍然会扫描 redo 日志，先做 redo（roll forward），再执行 undo，得到一致性快照。
    xtrabackup --prepare --target-dir=/data/backups/base
    => performs undo (rollback uncommitted tx), final consistent snapshot at LSN_I2
    then:
    xtrabackup --copy-back --target-dir=/data/backups/base
    chown -R mysql:mysql /var/lib/mysql
    systemctl start mysqld

16.11.2.2、合并后恢复

1、停止 MySQL 服务（恢复前必须停服）

systemctl stop mysqld

2、清空当前数据目录（注意备份现有数据）

mv /var/lib/mysql /var/lib/mysql_old_$(date +%F_%H%M%S)
mkdir -p /var/lib/mysql
chown mysql:mysql /var/lib/mysql

3、合并后全量恢复

xtrabackup --copy-back --target-dir=/backup/mysql/full/latest_prepared
chown -R mysql:mysql /var/lib/mysql

• --copy-back：将备份恢复到 MySQL 数据目录

• 权限必须正确（MySQL 启动用户）

16.11.2.4、回放 binlog 用于 PITR

方案1：全量备份 + binlog

时间轴 →
T0(全量备份完成点) ────────────────► Tn(目标时间)

恢复步骤：
恢复全量备份 (T0 状态)
binlog 回放 从 T0 开始 ──► Tn

• 你的恢复点停在 全量备份完成时（比如 2025-09-17 02:00）。

• 所以 binlog 必须从 02:00 之后 开始回放，即从全量时间点开始回放。

方案2：全量 + 增量 + binlog

时间轴 →
T0(全量) ──► T1(增量1) ──► T2(增量2,最后一次) ─────────────► Tn(目标时间)

恢复步骤：
恢复全量备份 (T0 状态)
应用增量链 (T1, T2) → 最终数据点 = T2
binlog 回放 从 T2 开始 ──► Tn

• 你做了全量（T0=02:00），又做了两次增量（T1=08:00，T2=14:00）。
• 当你 --prepare 把增量合并进 base 后，你的快照其实已经到了 T2=14:00 的状态。
• 如果这时你再从 02:00 全量时间点开始回放 binlog：
  • 02:00–14:00 的事务已经包含在增量备份里（物理页级别），
  • 再回放一次 binlog = 重复事务（逻辑层面），就会出错/冲突。
  • 必须从 14:00（最后一个增量结束点） 对应的 binlog 位置/时间点开始回放。

1、找到起始 binlog 文件

假设全量备份或最后一个增量结束时间点为 2025-09-17 02:00:00，需要回放之后所有 binlog 文件：

ls /backup/mysql/binlog/
# 示例: mysql_bin.000001 mysql_bin.000002 ...

2、回放 binlog

BINLOG_DIR=/backup/mysql/binlog/
START_TIME="2025-09-17 14:00:00"   # 最后一次增量备份完成时间
TARGET_TIME="2025-09-17 18:30:00"  # 想恢复到的时间点

for f in $(ls $BINLOG_DIR/mysql_bin.* | sort); do
    mysqlbinlog --start-datetime="$START_TIME" \
                --stop-datetime="$TARGET_TIME" "$f" | mysql -u root -p
done

• --start-datetime：从哪里开始回放

• --stop-datetime：回放到指定时间

• 如果想全部回放到最新，可省略 --stop-datetime

• MySQL binlog 是顺序写的，后面的文件可能包含前面文件未完成的事务边界（大事务跨文件）。

• 必须按文件名顺序(sort)回放，确保事务顺序一致。

• 事件早于 --start-datetime → 跳过，事件晚于 --stop-datetime → 停止。

16.11.2.5、启动 MySQL 服务

systemctl start mysqld
systemctl status mysqld

16.11.2.6、检查恢复结果

mysql -u root -p
SHOW DATABASES;

确认关键数据库和表恢复正常。

16.11.3、流程总结

时间线 →
T0           T1           T2           T3           T4
|------------|------------|------------|------------|
全量备份      INC1         INC2         PITR目标时间

备份文件：
[full/T0]    [inc1/T1]    [inc2/T2]

MySQL binlog 文件（按序号）：
mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  ...
|----------------|-----------------|-----------------| 
 T0                T1                T2                T4
 (包含事件)       (包含事件)       (包含事件)       (目标时间之前)

恢复流程：
1. 停服 & 清理数据目录
   systemctl stop mysqld
   mv /var/lib/mysql /var/lib/mysql_old_$(date +%F_%H%M%S)
   mkdir -p /var/lib/mysql && chown mysql:mysql /var/lib/mysql

2. 准备全量备份（redo only）
   xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full/T0

3. 应用增量备份（按时间顺序）
   xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full/T0 \
              --incremental-dir=/backup/mysql/inc1/T1
   xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full/T0 \
              --incremental-dir=/backup/mysql/inc2/T2

4. 最终 prepare（redo+undo）
   xtrabackup --prepare --target-dir=/backup/mysql/full/T0

5. 回放 binlog（PITR）
   START_TIME = T2   # 从最后一次增量备份时间开始
   TARGET_TIME = T4  # 目标恢复时间点
   for f in $(ls /backup/mysql/binlog/mysql-bin.* | sort); do
       mysqlbinlog --start-datetime="$START_TIME" \
                   --stop-datetime="$TARGET_TIME" "$f" | mysql -u root -p
   done
   # 事件时间 < START_TIME 的会被自动跳过，不会重复
   # 事件时间 > TARGET_TIME 的会被自动截断

6. 启动 MySQL
   systemctl start mysqld
   mysql -u root -p
   SHOW DATABASES;

说明：
- 全量备份 T0 -> 起点
- 每个增量备份必须按时间顺序 apply
- PITR 回放 binlog 的 START_TIME = 最后增量备份时间，避免重复
- 遍历所有 binlog 文件（按序号排序），mysqlbinlog 根据时间筛选事件