MySQL全量、增量备份与恢复

原创于 2025-06-03 13:42:49 发布 · 773 阅读

CC 4.0 BY-SA版权

文章标签：

前言

随着自动化办公与电子商务的不断发展，企业对于信息系统的依赖性越来越高，而数据库在信息系统中担任着非常重要的角色。尤其一些对数据可靠性要求非常高的行业，如银行、证券、电信等，如果发生意外宕机或数据丢失，其损失是非常严重的。为此数据库管理员必须针对具体的业务要求制定详细的数据库备份与灾难恢复的策略，并通过模拟故障对每种可能的情况进行严格的测试，从而保证数据的可靠性

一. MySQL数据库备份概述

备份的主要目的是灾难恢复，备份还可以测试应用、回滚数据修改、查询历史数据、审计等

1. 数据备份的重要性

在企业中数据的价值至关重要，数据保障了企业业务的正常运行。因此，数据的安全性及数据的可靠性是运维的重中之重,任何数据的丢失都可能对企业产生严重的后果。通常情况下造成数据丢失的原因有如下几种:

程序错误

人为操作错误

运算错误

磁盘故障

灾难(如火灾、地震)和盗窃

2. 数据库备份类型

（1）从物理与逻辑的角度分类

数据库备份可以分为物理备份和逻辑备份

物理备份是对数据库操作系统物理文件(如数据文件、日志文件等)的备份

这种类型的备份适用于在出现问题时需要快速恢复的大型重要数据库

物理备份又可以分为冷备份(脱机备份)、热备份(联机备份)和温备份

冷备份:在数据库关闭状态下进行备份操作

热备份:在数据库处于运行状态时进行备份操作，该备份方法依赖数据库的日志文件

温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作

逻辑备份是对数据库逻辑组件(如表等数据库对象)的备份，表示为逻辑数据库结构(CREATEDATABASE，CREATE TABLE 语句)和内容(INSERT 语句或分隔文本文件)的信息。这种类型的备份适用于可以编辑数据值或表结构较小的数据量，或者在不同的机器体系结构上重新创建数据

（2）从数据库的备份策略角度分类

从数据库的备份策略角度，数据库的备份可分为完全备份、差异备份和增量备份

完全备份:每次对数据进行完整的备份，即对整个数据库、数据库结构和文件结构的备份,保存的是备份完成时刻的数据库,是差异备份与增量备份的基础。完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复，并且会占用大量的磁盘空间，备份的时间也很长

差异备份:备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时，只需恢复上次的完全备份与最近的一次差异备份

增量备份:只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次增量备份的时间为时间点,仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之间的所有增量依次恢复,如中间某次的备份数据损坏，将导致数据的丢失

3. 常见的备份方法

MySQL 数据库的备份可以采用很多种方式，如直接打包数据库文件(物理冷备份)、专用备份工具(mysqldump)、二进制日志增量备份、第三方工具备份备份。

（1）物理冷备份

物理冷备份时需要在数据库处于关闭状态下，能够较好地保证数据库的完整性。物理冷备份一般用于非核心业务，这类业务一般都允许中断，物理冷备份的特点就是速度快，恢复时也是最为简单的。通常通过直接打包数据库文件夹(本章中的数据库文件夹位于/usr/local/mysql/data)来实现备份

（2）专用备份工具 mysqldump 或 mysqlhotcopy

mysqldump 程序和 mysqlhotcopy 都可以做备份。mysqldump 是客户端常用逻辑备份程序，能够产生一组被执行以后再现原始数据库对象定义和表数据的SQL 语句。它可以转储一个到多个 MySQL 数据库，对其进行备份或传输到远程SQL 服务器。mysqldump 更为通用，因为它可以备份各种表。mysqlhotcopy 仅适用于某些存储引擎

mysqlhotcopy是由TimBunce最初编写和贡献的Perl 脚本。mysqlhotcopy 仅用于备份 MyISAM 和 ARCHIVE 表。它只能运行在 UNIX 或 Linux上。因为使用范围小.

（3）通过启用二进制日志进行增量备份

MySQL 支持增量备份，进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制，对执行备份点后进行的数据库更改所需的信息进行恢复。如果进行增量备份(包含自上次完全备份或增量备份以来发生的数据修改)，需要刷新二进制日志.

（4）通过第三方工具备份

Percona XtraBackup 是一个免费的 MVSQL热备份软件，支持在线热备份Innodb 和 XtraDB,也可以支持 MVSQL 表备份,不过 MVISAM 表的备份要在表锁的情况下进行.

Percona XtrBackup 有三个主要的工具:xtrabackup、innobackupex、xbstream

xtrabackup:是一个编译了的二进制文件，只能备份Innodb/Xtradb 数据文件

innodbackupex :是一个封装了 xtrabackup 的Perl脚本，除了可以备份Innodb/Xtradb 之外，还可以备份 MySIAM

xbstream:是一个新组件，能够允许将文件格式转成xbstream 格式或从xbstream 格式转到文件格式

xtrabackup 工具可以单独使用，但推荐使用 innobackupex 来进行备份，这是因为 innobackupex本身就已经包含了 xtrabackup 的所有功能

xtrabackup 是基于 Innodb 的灾难恢复功能进行设计的，备份工具复制Innodb 的数据文件。但是，由于不锁表，这样复制出来的数据将不一致。Innodb维护了一个重做日志，包含 Innodb 数据的所有改动情况。在 xtrabackup 备份[nnodb 数据的同时，xtrabackup 还有另外一个线程用来监控重做日志，一但日志发生变化，就把发生变化的日志数据复制走。这样就可以利用重做日志做灾难恢复了

以上是备份过程，如果需要恢复数据，则在准备阶段，xtrabackup 就需要使用之前复制的重做日志对备份出来的 Innodb 数据文件进行灾难恢复,此阶段完成之后，数据库就可以进行重建还原了

Percona XtraBackup 对 MySIAM 的复制，是按这样的一个顺序进行的:首先锁定表，然后复制，再解锁表.

二. 数据库完全备份操作

1.物理冷备份与恢复

物理冷备份一般用 tar 命令直接打包数据库文件夹，而在进行备份之前需要使用“systemctl stop mysqld”命令关闭 mysqld 服务

（1）备份数据库

先创建要备份的数据库

mysql> create database db1;						##创建db1库
Query OK, 1 row affected (0.01 sec)

mysql> use db1;									##切换到db1库
Database changed
mysql> create table user1(user_name char(20),user_pass char(65));	##为db1库创建表user1
Query OK, 0 rows affected (0.02 sec)

mysql> insert into user1 values('zhangsan','pwd123');			    ##插入数据1
Query OK, 1 row affected (0.04 sec)

mysql> insert into user1 values('lisi',SHA2('pwd123',256));			##插入数据2
Query OK, 1 row affected (0.00 sec)

mysql> select * from user1;				##查看表中信息
+-----------+------------------------------------------------------------------+
| user_name | user_pass                                                        |
+-----------+------------------------------------------------------------------+
| zhangsan  | pwd123                                                           |
| lisi      | 3838bd5806d32cd91144865aa822b9551417dd2796c163d390baa7074d3067a7 |
+-----------+------------------------------------------------------------------+
2 rows in set (0.00 sec)

创建一个/backup 目录作为备份数据存储路径，使用tar 创建备份文件

整个数据库文件夹备份属于完全备份

[root@localhost ~]# mkdir /backup                     	   ##创建/backup目录
[root@localhost ~]# systemctl stop mysqld		               ##备份前关闭防火墙
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src
[root@localhost local]# tar zcf /bak/mysql_$(date +%F).tar.gz mysql			
[root@localhost local]# cd mysql/
[root@localhost mysql]# ls
bin  data  docs  include  lib  LICENSE  man  README  share  support-files
[root@localhost mysql]# cd data/
[root@localhost data]# ls
 auto.cnf          db1                 '#innodb_temp'        server-key.pem
 binlog.000001     error.log            mysql                sys
 binlog.index     '#ib_16384_0.dblwr'   mysql.ibd            undo_001
 ca-key.pem       '#ib_16384_1.dblwr'   performance_schema   undo_002
 ca.pem            ib_buffer_pool       private_key.pem
 client-cert.pem   ibdata1              public_key.pem
 client-key.pem   '#innodb_redo'        server-cert.pem

（2）恢复数据库

执行下面操作将数据库文件/usr/local/mysql/data/转移至 /bak 目录下，模拟故障。

[root@localhost data]# systemctl start mysqld
[root@localhost data]# mysql -uroot -ppwd123 -e 'drop table db1.user1'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost local]# rm -rf mysql/*
[root@localhost mysql]# tar zxf /bak/mysql_2025-04-07.tar.gz -C /usr/local/
[root@localhost mysql]# ls
bin  data  docs  include  lib  LICENSE  man  README  share  support-files
[root@localhost mysql]# systemctl restart mysqld
[root@localhost mysql]# mysql -uroot -ppwd123 -e 'select * from db1.user1'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+------------------------------------------------------------------+
| user_name | user_pass                                                        |
+-----------+------------------------------------------------------------------+
| zhangsan  | pwd123                                                           |
| lisi      | 3838bd5806d32cd91144865aa822b9551417dd2796c163d390baa7074d3067a7 |
+-----------+------------------------------------------------------------------+

2. mysqldump备份与恢复

通过 mysqldump 命令可以将指定的库、表或全部的库导出为 SQL 脚本，便于该命令在不同版本的 MySQL 服务器上使用。例如，当需要升级 MySQL 服务器时，可以先使用 mysqldump 命令将原有库信息导出,然后直接在升级后的 MySQL服务器中导入即可

（1）备份数据库

使用 mysqldump 命令导出数据时，默认会直接在终端显示，若要保存到文件，还需要结合 Shel1 的“>”重定向输出操作，命令格式如下所示

格式一：备份指定库中的部分表

mysqldump [选项] 库名 [表名1] [表名2] …>/备份路径/备份文件名

格式二：备份一个或多个完整的库(包括其中所有的表)

mysqldump [选项] --databases库名1[库名 2]…

格式三：备份MySQL服务器中所有的库

mysqldump [选项] --all-databases >/备份路径/备份文件名

其中，常用的选项包括“-u”，分别用于指定数据库用户名、密码。p”例如，以下操作分别使用格式 1、格式 2，将 mysql 库中的 user 表导出为mysql-user.sql,将整个 test 库导出为 test.sql 文件,所有操作均以 root用户身份验证

需要先创建auth、test数据库，并创建表

mysql> create database auth;						##创建auth库	
Query OK, 1 row affected (0.01 sec)

mysql> create database test;						##创建test库
Query OK, 1 row affected (0.01 sec)

mysql> use auth;
Database changed
mysql> create table aaa(name char(1));					##创建表
Query OK, 0 rows affected (0.02 sec)

mysql> use test;
Database changed				                        ##创建表
mysql> create table aaa(name char(1));
Query OK, 0 rows affected (0.02 sec)

备份auth-aaa.sql 和 test.sql

[root@localhost ~]# mysqldump -uroot -ppwd123 auth aaa > auth-aaa.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysqldump -uroot -ppwd123 --databases test > test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

若需要备份整个 MSQL 服务器中的所有库，应使用格式 3。当导出的数据量较大的时候，可以添加“-opt"选项以优化执行速度。例如，执行以下操作将创建备份文件 all-data.sql，其中包括 MySQL 服务器中的所有库。

[root@localhost ~]# mysqldump -uroot -ppwd123 --opt --all-databases > all-data.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

（2）查看备份文件

通过 mysqldump 工具导出的 SQL 脚本是文本文件，其中"”"…"部分或以“一"开头的行表示注释信息。使用 grep、less、cat等文本工具可以查看脚本内容。例如,执行以下操作可以过滤出 test.sql 脚本中的数据库操作语句。

[root@localhost ~]# grep -v "^--" test.sql | grep -v "^/" | grep -v "^$"
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `test`;
DROP TABLE IF EXISTS `aaa`;
CREATE TABLE `aaa` (
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
LOCK TABLES `aaa` WRITE;
INSERT INTO `aaa` VALUES ('1');
UNLOCK TABLES;

（3）恢复数据库

使用 mysqldump 命令导出的 SQL备份脚本，在需要恢复时可以通过 mysql命令对其进行导入操作，命令格式如下所示

mysql [选项] [库名] [表名] < /备份路径/备份文件名

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在

例如，执行以下操作可以从备份文件 auth-aaa.sgl中将表导入test 库。其中“-e"选项是用于指定连接 MYSQL后执行的命令，命令执行完后自动退出。

[root@localhost ~]# mysql -uroot -ppwd123 test < auth-aaa.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -ppwd123 -e 'show tables from test;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+
| Tables_in_test |
+----------------+
| aaa            |
+----------------+

若备份文件中已经包括完整的库信息，则执行导入操作时无须指定库名

例如，执行以下操作可以从备份文件 test.sql 恢复 test 库

[root@localhost ~]# mysql -uroot -ppwd123 -e 'drop database test;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -ppwd123 -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@localhost ~]# mysql -uroot -ppwd123 < ~/test.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -ppwd123 -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

除了使用 mysql 命令结合“<”恢复数据外，还可以使用 source 命令恢复数据，具体用法如下

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database test;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> source /root/test.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)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

3. MySQL 增量备份与恢复

使用 mysqldump 进行完全备份，备份的数据中有重复数据，备份时间与恢复时间过长。而增量备份就是自上一次备份之后增加或改变的内仿

（1）MySQL增量备份概述

特点

与完全备份不同，增量备份没有重复数据，备份量不大，时间短;但其恢复麻烦，需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复。MSQL 没有提供直接的增量备份办法，可以通过MySQL 提供的二进制日志(binarylogs)间接实现增量备份。

MySQL二进制日志对备份的意义

二进制日志保存了所有更新数据库的操作。二进制日志在启动 MySQL 服务器后开始记录，并在文件达到二进制日志所设置的最大值或者接收到flush1ogs 命令后重新创建新的日志文件，生成二进制文件序列，并及时把这些日志保存到安全的存储位置，即可完成一个时间段的增量备份。使max binlog size配置项可以设置二进制日志文件的最大值，如果二进制文件的大小超过了max binlog size，它就会自动创建新的二进制文件。

要进行 MySQL 的增量备份，首先要开启二进制日志功能。开启 MySQL 的二进制日志功能的实现方法有很多种，最常用的是在 MySQL 配置文件的 mysqld项下加入“log-bin=/ 文件路径/文件名”前缀，如log-bin=/usr/local/mysql/mysq1-bin,然后重启 MySQL 服务就可以在指定路径下查看二进制日志文件了。默认情况下，二进制日志文件的扩展名是一个六位的数字，如 mysql-bin.000001。

Mysql8.0 默认已经开启 binlog，无需显示配置 binlog(默认 binlog 文件为:binlog.000001)，如需自定义binlog配置，请添加如下配置项

[root@localhost ~]# vim /etc/my.cnf 
[mysqld]
log-bin=/usr/local/mysql/data/mysql-bin  #启用二进制日志(Binary Log)并指定其存储路径
binlog-format=MIXED                      #定义二进制日志的记录格式为混合模式
server-id=1                              #为mysql实列分配一个唯一的服务器标识符
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# ls -l /usr/local/mysql/data/mysql-bin.*
-rw-r----- 1 mysql mysql 157  6月 3日 10:24 /usr/local/mysql/data/mysql-bin.000001
-rw-r----- 1 mysql mysql  39  6月 3日 10:24 /usr/local/mysql/data/mysql-bin.index

（2）MySQL增量恢复

在维护数据库时,因为各种各样的原因可能会导致数据丢失，如:人为的 SQL语句破坏数据库、在进行下一次全备份之前发生系统故障导致数据库数据丢失、在数据库主从架构中主库的数据发生故障等。当出现以上场景时可以使用增量恢复来恢复数据。

常用的增量恢复的方法有三种:一般恢复、基于位置的恢复、基于时间点的恢复

一般恢复:将所有备份的二进制日志内容全部恢复，命令格式如下

mysqlbinlog  [--no-defaults]  增量备份文件|mysql -u 用户名 -p 密码

基于位置的恢复:数据库管理员在操作数据库时可能在同一时间点既有错误的操作也有正确的操作，通过基于位置进行恢复可以更加精准，命令格式如下

格式1:恢复数据到指定位置:

mysqlbinlog  --stop-position='操作  id’  二进制日志  |   mysql -u 用户名  -p 密码

格式 2:从指定的位置开始恢复数据

mysqlbinlog --start-position='操作 id’  二进制日志  |   mysql  -u 用户名  -p  密码

基于时间点的恢复:跳过某个发生错误的时间点实现数据恢复，而基于时间点的恢复可以分成三种情况

格式 1:从日志开头截止到某个时间点的恢复

mysqlbinlog  [--no-defaults]   --stop-datetime='年-月-日 小时:分钟:秒’  二进制日志   |   mysql -u 用户名 -p 密码

格式 2:从某个时间点到日志结尾的恢复

mysqlbinlog  [--no-defaults]   --start-datetime='年-月-日 小时:分钟:秒’   二进制日志    |    mysql -u 用户名 -p 密码

格式 3:从某个时间点到某个时间点的恢复。

mysqlbinlog  [--no-defaults]--start-datetime='年-月-日 小时:分钟:秒’--stop-datetime=’年-月-日小时:分钟:秒”   二进制日志   | mysql -u 用户名-p 密码

三. MySQL企业备份实列

需求描述:北京移电通信公司的用户信息数据库为client，用户资费数据表为 user info，该公司每周需要进行完全备份，每天需要进行增量备份。新增加的用户信息如表所示

在这里插入图片描述

1. 一般恢复

（1）添加数据库、表，录入信息

在进行备份之前，先根据给出的需求创建用户信息数据库client、用户资费数据表 userinfo，并且根据需求描述中的表格插入前三条用户的数据

mysql> create database client;
Query OK, 1 row affected (0.00 sec)

mysql> use client;
Database changed

mysql> create table user_info(identitycard char(20),name char(20),gender char(4),userid char (10),expenses int(10));
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> insert into user_info values ('000006','张三','男','016','10');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user_info values ('000007','李四','女','017','91');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user_info values ('000007','王五','女','018','23');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user_info;
+--------------+--------+--------+--------+----------+
| identitycard | name   | gender | userid | expenses |
+--------------+--------+--------+--------+----------+
| 000006       | 张三   | 男     | 016    |       10 |
| 000007       | 李四   | 女     | 017    |       91 |
| 000007       | 王五   | 女     | 018    |       23 |
+--------------+--------+--------+--------+----------+
3 rows in set (0.00 sec)

（2）先进行一次完全备份

为方便验证二进制日志的增量恢复功能，在插入三条用户数据后先对client 数据库的 user info 表进行一次完全备份。然后在 Linux 系统命令行下执行“mysqladmin -uroot -p flush-logs”命令或在“mysql>”命令提示符下执行“flush logs;”生成新的二进制日志。

[root@localhost ~]# mkdir mysql_bak
[root@localhost ~]# mysqldump -uroot -ppwd123 client user_info > mysql_bak/client-userinfo.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls mysql_bak/
client-userinfo.sql
[root@localhost ~]# mysqladmin -uroot -ppwd123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls -l /usr/local/mysql/data/mysql-bin.*
-rw-r----- 1 mysql mysql 2059  6月 3日 10:55 /usr/local/mysql/data/mysql-bin.000001
-rw-r----- 1 mysql mysql  157  6月 3日 10:55 /usr/local/mysql/data/mysql-bin.000002
-rw-r----- 1 mysql mysql   78  6月 3日 10:55 /usr/local/mysql/data/mysql-bin.index

（3）继续录入新的数据并进行增量备份

继续录入两个用户的数据，并执行“mysqladmin-uroot-p flush-logs“命令刷新二进制日志，进行增量备份。如此,进制日志文件 mysq1-bin.000002中仅保留插入两个用户数据的操作

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| client             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use client;
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> insert into user_info values ('000009','赵六','男','019','37');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user_info values ('000009','孙七','男','020','36');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user_info;
+--------------+--------+--------+--------+----------+
| identitycard | name   | gender | userid | expenses |
+--------------+--------+--------+--------+----------+
| 000006       | 张三   | 男     | 016    |       10 |
| 000007       | 李四   | 女     | 017    |       91 |
| 000007       | 王五   | 女     | 018    |       23 |
| 000009       | 赵六   | 男     | 019    |       37 |
| 000009       | 孙七   | 男     | 020    |       36 |
+--------------+--------+--------+--------+----------+
5 rows in set (0.00 sec

[root@localhost ~]# mysqladmin -uroot -ppwd123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls -l /usr/local/mysql/data/mysql-bin.*
-rw-r----- 1 mysql mysql 2059  6月 3日 10:55 /usr/local/mysql/data/mysql-bin.000001
-rw-r----- 1 mysql mysql  888  6月 3日 11:00 /usr/local/mysql/data/mysql-bin.000002
-rw-r----- 1 mysql mysql  157  6月 3日 11:02 /usr/local/mysql/data/mysql-bin.000003
-rw-r----- 1 mysql mysql  195  6月 3日 11:02 /usr/local/mysql/data/mysql-bin.index
[root@localhost ~]# /bin/cp /usr/local/mysql/data/mysql-bin.000002 mysql_bak/

（4）模拟操作删除user_info表

[root@localhost ~]# mysql -uroot -ppwd123 -e 'drop table client.user_info'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -ppwd123 -e 'select * from client.user_info;'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

（5）恢复操作

再执行恢复操作时，需要先恢复完全备份，然后恢复增量备份

恢复完全备份

[root@localhost ~]# mysql -uroot -ppwd123 client < mysql_bak/client-userinfo.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -ppwd123 -e 'select * from client.user_info;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+--------+--------+--------+----------+
| identitycard | name   | gender | userid | expenses |
+--------------+--------+--------+--------+----------+
| 000006       | 张三   | 男     | 016    |       10 |
| 000007       | 李四   | 女     | 017    |       91 |
| 000007       | 王五   | 女     | 018    |       23 |
+--------------+--------+--------+--------+----------+

恢复增量备份

[root@localhost ~]# mysqlbinlog --no-defaults mysql_bak/mysql-bin.000002 | mysql -uroot -ppwd123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -ppwd123 -e 'select * from client.user_info;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+--------+--------+--------+----------+
| identitycard | name   | gender | userid | expenses |
+--------------+--------+--------+--------+----------+
| 000006       | 张三   | 男     | 016    |       10 |
| 000007       | 李四   | 女     | 017    |       91 |
| 000007       | 王五   | 女     | 018    |       23 |
| 000009       | 赵六   | 男     | 019    |       37 |
| 000009       | 孙七   | 男     | 020    |       36 |
+--------------+--------+--------+--------+----------+

2. 基于位置恢复

由于前面已经做过备份操作，接下来直接进行模拟故障与数据恢复的操作

[root@localhost ~]# mysql -uroot -ppwd123 -e 'drop table client.user_info'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -ppwd123 -e 'select * from client.user_info'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
[root@localhost ~]# mysql -uroot -ppwd123 client < mysql_bak/client-userinfo.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

想要实现基于位置或时间点恢复数据,必须先通过査看二进制日志文件确定恢复的位置或时间点。使用“mysalbinlog.-no-defaults 二进制日志文件”可以査看二进制日志文件的具体内容

[root@localhost ~]# mysqlbinlog --no-defaults mysql_bak/mysql-bin.000002
# at 499
#250603 10:58:04 server id 1  end_log_pos 578 CRC32 0xfb3f3666 	Anonymous_GTID	last_committed=1	br_only=no	original_committed_timestamp=1748919484030821	immediate_commit_timestamp=174891948ngth=342
# original_commit_timestamp=1748919484030821 (2025-06-03 10:58:04.030821 CST)
# immediate_commit_timestamp=1748919484030821 (2025-06-03 10:58:04.030821 CST)
/*!80001 SET @@session.original_commit_timestamp=1748919484030821*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 578
#250603 10:58:04 server id 1  end_log_pos 664 CRC32 0x72f2977f 	Query	thread_id=12	exec_time=0	
SET TIMESTAMP=1748919484/*!*/;
BEGIN
/*!*/;
# at 664
#250603 10:58:04 server id 1  end_log_pos 810 CRC32 0xe1483562 	Query	thread_id=12	exec_time=0	
SET TIMESTAMP=1748919484/*!*/;
insert into user_info values ('000009','孙七','男','020','36')
/*!*/;

通过查看日志文件的具体内容可以发现，在每进行一个操作之前都会有一个独特的编号，如“#at 663”。此编号随着操作数增多而变大，我们称之为操作ID。在操作 ID下面紧跟着的是时间标记。要实现基于位置或时间点恢复数据，需要分别依赖二进制日志文件中的操作 !D或者时间标记。例如，通过二进制日志文件得知，在操作 ID 为“663”的时候，user info 表中插入了“孙七”的用户数据。因此执行以下命令可以实现仅恢复到操作ID为“663"之前的数据即不恢复“孙七”的信息。这时所恢复的数据是从二进制日志文件的开始位置直到指定位置。

[root@localhost ~]# mysqlbinlog --no-defaults --stop-position='663' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -ppwd123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -ppwd123 -e 'select * from client.user_info'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+--------+--------+--------+----------+
| identitycard | name   | gender | userid | expenses |
+--------------+--------+--------+--------+----------+
| 000006       | 张三   | 男     | 016    |       10 |
| 000007       | 李四   | 女     | 017    |       91 |
| 000008       | 王五   | 女     | 018    |       23 |
| 000009       | 赵六   | 男     | 019    |       37 |

3.基于时间点恢复

[root@localhost ~]# mysql -uroot -ppwd123 -e 'drop table client.user_info;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -ppwd123 client < mysql_bak/client-userinfo.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysqlbinlog --no-defaults --start-datetime='25-06-03 10:58:04' /usr/local/mysql/data/mysql-bin.000002  | mysql -uroot -ppwd123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -ppwd123 -e 'select * from client.user_info'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+--------+--------+--------+----------+
| identitycard | name   | gender | userid | expenses |
+--------------+--------+--------+--------+----------+
| 000006       | 张三   | 男     | 016    |       10 |
| 000007       | 李四   | 女     | 017    |       91 |
| 000007       | 王五   | 女     | 018    |       23 |
| 000009       | 孙七   | 男     | 020    |       36 |
+--------------+--------+--------+--------+----------+