MySQL备份与恢复

目录

一、MySQL 数据库备份概述

1.1、数据备份的重要性 

1.2、数据库备份类型 

1.3、常见的备份方法 

二、数据库完全备份操作 

2.1、物理冷备份与恢复

2.2、mysqldump备份与恢复 

三、MySQL增量备份与恢复 

3.1、MySQL增量备份概述

3.2、MySQL增量恢复 

3.3、MySQL企业备份案例 

四、制定企业备份策略的思路 

---

一、MySQL 数据库备份概述

备份的主要目的是灾难恢复，备份还可以测试应用、回滚数据修改、查询历史数据、审计等。之前已经学习过如何安装 MySQL，本小节将从生产运维的角度了解备份恢复的分类与方法。

1.1、数据备份的重要性 

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

• 程序错误。
• 人为操作错误。
• 运算错误。
• 磁盘故障。
• 灾难（如火灾、地震）和盗窃。

1.2、数据库备份类型 

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

数据库备份可以分为物理备份和逻辑备份。物理备份是对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。这种类型的备份适用于在出现问题时需要快速恢复的大型重要数据库。

物理备份又可以分为冷备份（脱机备份）、热备份（联机备份）和温备份。

• 冷备份：在数据库关闭状态下进行备份操作。
• 热备份：在数据库处于运行状态时进行备份操作，该备份方法依赖数据库的日志文件。
• 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作。 

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

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

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

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

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

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

1.3、常见的备份方法 

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

1）物理冷备份

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

2）专用备份工具 mysqldump 或 mysqlhotcopy

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

mysqlhotcopy 是由 Tim Bunce 最初编写和贡献的 Perl 脚本。mysqlhotcopy 仅用于备份 MyISAM 和 ARCHIVE 表。它只能运行在 UNIX 或 Linux 上。因为使用范围很小，因此本文中不做详细介绍。

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

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

4）通过第三方工具备份

Percona XtraBackup 是一个免费的 MySQL 热备份软件，支持在线热备份 Innodb 和 XtraDB，也可以支持 MySQL 表备份，不过 MyISAM 表的备份要在表锁的情况下进行。本节对于 Percona XtraBackupr 的叙述是基于 2.4 版本的。 

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 备份 Innodb 数据的同时，xtrabackup 还有另外一个线程用来监控重做日志，一但日志发生变化，就把发生变化的日志数据复制走。这样就可以利用重做日志做灾难恢复了。

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

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

第三方工具备份操作本节中不做详细介绍。

---

二、数据库完全备份操作 

上面提到根据数据库备份策略分类，备份可分为完全备份、差异备份和增量备份。在本节中我们只介绍完全备份与增量备份。 

2.1、物理冷备份与恢复

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

1）备份数据库

创建一个/backup 目录作为备份数据存储路径，使用 tar 创建备份文件。整个数据库文件夹备份属于完全备份。

[root@centos7-2 ~]# systemctl stop mysqld  
[root@centos7-2 ~]# mkdir /backup
[root@centos7-2 ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
[root@centos7-2 ~]# ls -l /backup/
总用量 1344
-rw-r--r--. 1 root root 1375996 3月   7 11:34 mysql_all-2024-03-07.tar.gz
[root@centos7-2 ~]# 

2）恢复数据库 

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

[root@centos7-2 ~]# mkdir /bak
[root@centos7-2 ~]# mv /usr/local/mysql/data/ /bak/
[root@centos7-2 ~]#

执行下面操作可以从备份文件恢复数据。 

[root@centos7-2 ~]# mkdir /restore
[root@centos7-2 ~]# tar zxf /backup/mysql_all-2024-03-07.tar.gz -C /restore/
[root@centos7-2 ~]# mv /restore/usr/local/mysql/data/ /usr/local/mysql/
[root@centos7-2 ~]# systemctl start mysqld
[root@centos7-2 ~]#

2.2、mysqldump备份与恢复 

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

1）备份数据库

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

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

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

格式 2：备份一个或多个完整的库（包括其中所有的表）。 

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

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

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

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

[root@centos7-2 ~]# mysqldump -u root -pcisco@123 mysql user > /backup/mysql_user.sql
[root@centos7-2 ~]# 
[root@centos7-2 ~]# mysqldump -u root -pcisco@123 --databases auth > /backup/auth.sql
[root@centos7-2 ~]# 
[root@centos7-2 ~]# ls /backup/
auth.sql  mysql_user.sql
[root@centos7-2 ~]#

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

[root@centos7-2 ~]# mysqldump -u root -pcisco@123 --opt --all-databases > /backup/all_data.sql
[root@centos7-2 ~]# 
[root@centos7-2 ~]# ls /backup/
all_data.sql  auth.sql  mysql_user.sql
[root@centos7-2 ~]#

2）查看备份文件 

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

[root@centos7-2 ~]# grep -v "^--" /backup/auth.sql | grep -v "^/" | grep -v "^$"
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `auth` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `auth`;
DROP TABLE IF EXISTS `info`;
CREATE TABLE `info` (
  `user_name` char(16) NOT NULL,
  `user_password` char(48) DEFAULT '',
  PRIMARY KEY (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `info` WRITE;
INSERT INTO `info` VALUES ('zhangsan','*A4B6157319038724E3560894F7F932C8886EBFCF');
UNLOCK TABLES;
[root@centos7-2 ~]# 

3）恢复数据库

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

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

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。例如，执行以下操作可以从备份文件 mysql-user.sql 中将表导入 test 库。

[root@centos7-2 ~]# mysql -u root -pcisco@123 test < /backup/mysql_user.sql 
[root@centos7-2 ~]# 
mysql> show tables from test;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)

mysql> 

若备份文件中已经包括完整的库信息，则执行导入操作时无须指定库名。例如，执行以下操作可以从备份文件 auth.sql 恢复 auth 库。 

mysql> drop database auth;  //删除auth数据库，模拟故障；
Query OK, 1 row affected (0.03 sec)

mysql> show databases;  //查看auth数据库是否存在；
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> 

[root@centos7-2 ~]# mysql -u root -pcisco@123 < /backup/auth.sql   //执行导入恢复操作；
[root@centos7-2 ~]# 

mysql> show databases;  //确认恢复后的结果；
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> 

---

三、MySQL增量备份与恢复 

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

3.1、MySQL增量备份概述

1）增量备份的特点 

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

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

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

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

[root@centos7-2 ~]# mkdir -p /usr/local/mysql/logs/mysql-bin
[root@centos7-2 ~]# chown -R mysql:mysql /usr/local/mysql/logs
[root@centos7-2 ~]# vim /etc/my.cnf
......//省略部分内容
log-bin=/usr/local/mysql/logs/mysql-bin
binlog_format=MIXED
server-id=1
[root@centos7-2 ~]# systemctl restart mysqld
[root@centos7-2 ~]# ls -l /usr/local/mysql/logs/mysql-bin.*
-rw-r-----. 1 mysql mysql 154 3月   7 19:35 /usr/local/mysql/logs/mysql-bin.000001
-rw-r-----. 1 mysql mysql  39 3月   7 19:35 /usr/local/mysql/logs/mysql-bin.index
[root@centos7-2 ~]# 

3.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 --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码 

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

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

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

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

下面通过一个企业级的备份案例讲解增量备份与恢复的具体操作。 

---

3.3、MySQL企业备份案例 

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

身份证	姓名	性别	用户ID号	资费
000006	张三	男	016	10
000007	李四	女	017	91
000008	王五	女	018	23
000009	赵六	男	019	37
000010	孙七	男	020	36

 

 1）一般恢复

• 添加数据库、表，录入信息

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

mysql> create database client;  //创建client数据库
Query OK, 1 row affected (0.01 sec)

mysql> use client;  //进入client数据库
Database changed
mysql> create table user_info(身份证 char(18) not null,姓名 char(10) not null,性别 char(1) not null,用户ID号 char(10) not null,资费 int(10));  //创建user_info表
Query OK, 0 rows affected (0.01 sec)

mysql> insert into user_info values('000006','张三','男','016','10');  //插入数据
Query OK, 1 row affected (0.04 sec)

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

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

mysql> select * from user_info;  //查看user_info表
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
+-----------+--------+--------+-------------+--------+
3 rows in set (0.00 sec)

mysql> 

• 先进行一次完全备份

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

[root@centos7-2 ~]# mkdir /bak
[root@centos7-2 ~]# mysqldump -u root -pcisco@123 client user_info > /bak/client.user_info-$(date +%F).sql
[root@centos7-2 ~]# ls /bak
client.user_info-2024-03-07.sql
[root@centos7-2 ~]# mysqladmin -u root -pcisco@123 flush-logs
[root@centos7-2 ~]# ls -l /usr/local/mysql/logs/mysql-bin.*
-rw-r-----. 1 mysql mysql 1617 3月   7 20:07 /usr/local/mysql/logs/mysql-bin.000001
-rw-r-----. 1 mysql mysql  154 3月   7 20:07 /usr/local/mysql/logs/mysql-bin.000002
-rw-r-----. 1 mysql mysql   78 3月   7 20:07 /usr/local/mysql/logs/mysql-bin.index
[root@centos7-2 ~]# 

• 继续录入新的数据并进行增量备份

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

mysql> use client;
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('000010','孙七','男','020','36');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
| 000009    | 赵六   | 男     | 019         |     37 |
| 000010    | 孙七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+
5 rows in set (0.00 sec)

mysql> 


[root@centos7-2 ~]# mysqladmin -u root -pcisco@123 flush-logs
[root@centos7-2 ~]# ls -l /usr/local/mysql/logs/mysql-bin.*
-rw-r-----. 1 mysql mysql 1617 3月   7 20:07 /usr/local/mysql/logs/mysql-bin.000001
-rw-r-----. 1 mysql mysql 1471 3月   7 20:17 /usr/local/mysql/logs/mysql-bin.000002
-rw-r-----. 1 mysql mysql  154 3月   7 20:17 /usr/local/mysql/logs/mysql-bin.000003
-rw-r-----. 1 mysql mysql  117 3月   7 20:17 /usr/local/mysql/logs/mysql-bin.index
[root@centos7-2 ~]# cp /usr/local/mysql/logs/mysql-bin.000002 /bak/
[root@centos7-2 ~]#

• 模拟误操作删除 user_info 表 

mysql> drop table user_info;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from client.user_info;
ERROR 1146 (42S02): Table 'client.user_info' doesn't exist
mysql> 

• 恢复操作 

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

[root@centos7-2 ~]# mysql -u root -pcisco@123 client < /bak/client.user_info-2024-03-07.sql     //恢复完全备份
[root@centos7-2 ~]# 

mysql> select * from client.user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
+-----------+--------+--------+-------------+--------+
3 rows in set (0.00 sec)

mysql> 


[root@centos7-2 ~]# mysqlbinlog --no-defaults /bak/mysql-bin.000002 | mysql -u root -pcisco@123  //恢复增量备份
[root@centos7-2 ~]#


mysql> select * from client.user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
| 000009    | 赵六   | 男     | 019         |     37 |
| 000010    | 孙七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+
5 rows in set (0.01 sec)

mysql> 

2）基于位置恢复

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

mysql> drop table client.user_info;  //模拟误操作删除user_info表；
Query OK, 0 rows affected (0.01 sec)

mysql> select * from client.user_info;  //查看user_info表是否存在；
ERROR 1146 (42S02): Table 'client.user_info' doesn't exist
mysql> 


[root@centos7-2 ~]# mysql -u root -pcisco@123 client < /bak/client.user_info-2024-03-07.sql   //恢复完全备份
[root@centos7-2 ~]# 


mysql> select * from client.user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
+-----------+--------+--------+-------------+--------+
3 rows in set (0.00 sec)

mysql> 

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

[root@centos7-2 ~]# mysqlbinlog /bak/mysql-bin.000002 
......//省略部分内容
BEGIN
/*!*/;
# at 930
#240307 20:13:25 server id 1  end_log_pos 1072 CRC32 0x239a1452 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1709813605/*!*/;
insert into user_info values('000009','赵六','男','019','37')
/*!*/;
# at 1072
#240307 20:13:25 server id 1  end_log_pos 1103 CRC32 0x14f28932 	Xid = 49
COMMIT/*!*/;
# at 1103
#240307 20:15:59 server id 1  end_log_pos 1168 CRC32 0x8e6cee83 	Anonymous_GTID	last_committed=3	sequence_number=4     rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1168
#240307 20:15:59 server id 1  end_log_pos 1251 CRC32 0x7606d79a 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1709813759/*!*/;
BEGIN
/*!*/;
# at 1251
#240307 20:15:59 server id 1  end_log_pos 1393 CRC32 0x279976bd 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1709813759/*!*/;
insert into user_info values('000010','孙七','男','020','36')
/*!*/;
# at 1393
#240307 20:15:59 server id 1  end_log_pos 1424 CRC32 0x6adfdceb 	Xid = 50
COMMIT/*!*/;
# at 1424
#240307 20:17:53 server id 1  end_log_pos 1471 CRC32 0x498d9403 	Rotate to mysql-bin.000003  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7-2 ~]# 

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

[root@centos7-2 ~]# mysqlbinlog --no-defaults --stop-position='1168' /bak/mysql-bin.000002 | mysql -u root -pcisco@123
[root@centos7-2 ~]#


mysql> select * from client.user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
| 000009    | 赵六   | 男     | 019         |     37 |
+-----------+--------+--------+-------------+--------+
4 rows in set (0.00 sec)

mysql> 

上述操作命令中，“--stop-position”指定的是停止的位置。如果仅恢复“孙七”的信息，跳过“赵六”的信息恢复，可以使用“--start-position”选项指定开始恢复数据的位置。这时所恢复的数据是从指定位置开始直到二进制日志文件的最后。 

mysql> drop table client.user_info;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from client.user_info;
ERROR 1146 (42S02): Table 'client.user_info' doesn't exist


[root@centos7-2 ~]# mysql -u root -pcisco@123 client < /bak/client.user_info-2024-03-07.sql 
[root@centos7-2 ~]#

mysql> select * from client.user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
+-----------+--------+--------+-------------+--------+
3 rows in set (0.00 sec)

mysql>

[root@centos7-2 ~]# mysqlbinlog --no-defaults --start-position='1168' /bak/mysql-bin.000002 | mysql -u root -pcisco@123
[root@centos7-2 ~]# 


mysql> select * from client.user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
| 000010    | 孙七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+
4 rows in set (0.00 sec)

mysql> 

3）基于时间点恢复

基于时间点的数据恢复所使用的选项是“--stop-datetime”，指定的时间同样也是查询二进制日志所得。执行以下操作可以实现仅恢复到 20:15:59 之前的数据，即不恢复“孙七” 的信息。 

mysql> drop table client.user_info;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from client.user_info;
ERROR 1146 (42S02): Table 'client.user_info' doesn't exist
mysql> 


[root@centos7-2 ~]# mysql -u root -pcisco@123 client < /bak/client.user_info-2024-03-07.sql 
[root@centos7-2 ~]# 
[root@centos7-2 ~]# mysqlbinlog --no-defaults --stop-datetime='2024-03-07 20:15:59' /bak/mysql-bin.000002 | mysql -u root -pcisco@123
[root@centos7-2 ~]#


mysql> select * from client.user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
| 000009    | 赵六   | 男     | 019         |     37 |
+-----------+--------+--------+-------------+--------+
4 rows in set (0.00 sec)

mysql> 

执行以下操作可以实现仅恢复“孙七”的信息，跳过“赵六”的信息恢复。

mysql> drop table client.user_info;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from client.user_info;
ERROR 1146 (42S02): Table 'client.user_info' doesn't exist
mysql> 

[root@centos7-2 ~]# mysql -u root -pcisco@123 client < /bak/client.user_info-2024-03-07.sql 
[root@centos7-2 ~]# mysqlbinlog --no-defaults --start-datetime='2024-03-07 20:15:59' /bak/mysql-bin.000002 | mysql -u root -pcisco@123
[root@centos7-2 ~]#

mysql> select * from client.user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
| 000010    | 孙七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+
4 rows in set (0.00 sec)

mysql> 

---

四、制定企业备份策略的思路 

在企业中备份策略并不是千篇一律的，而是根据每个企业的实际生产环境与业务需求制定合适的备份策略。无论是选择完全备份，还是选择增量备份，都需考虑它们的优缺点，是否适合当前的生产环境。同时，为了保证恢复的完整性，建议开启二进制日志功能，二进制日志文件给恢复工作也带来了很大的灵活性，可以基于时间点或位置进行恢复。考虑到数据库性能，可以将二进制日志文件保存到其他安全的硬盘中。

在进行热备份时，备份操作和应用服务在同时运行，这样就十分消耗系统资源了，导致数据库服务性能下降，这就需要选择一个合适的时间（如在应用负担很小的时候）再来进行备份操作。

需要注意的是，不是备份完就万事大吉，最好确认备份是否可用，所以备份之后的恢复测试是非常有必要的。同时备份时间也要灵活调整，如： 

• 数据更新频繁，则应该频繁地备份。
• 数据的重要性，在有适当更新时进行备份。
• 在数据库压力小的时间段进行备份，如一周一次完全备份，每天进行增量备份。
• 中小公司，完全备份一般一天一次即可。
• 大公司可每周进行一次完全备份，每天进行一次增量备份。
• 尽量为企业实现主从复制架构，以增加数据的可用性。