目录
专用备份工具 mysqldump 或 mysqlhotcopy
数据备份的重要性
在企业中数据的价值至关重要，数据保障了企业业务的正常运行。因此，数据的安全性及数据的可靠性是运维的重中之重，任何数据的丢失都可能对企业产生严重的后果。通常情况下造成数据丢失的原因有如下几种：
- 程序错误。
- 人为操作错误。
- 运算错误。
- 磁盘故障。
- 灾难（如火灾、地震）和盗窃
数据库备份类型
从物理与逻辑的角度分类
数据库备份可以分为物理备份和逻辑备份。
物理备份是对数据库操作系统物理文件（如数据文件、日志文件等）的备份。
这种类型的备份适用于在出现问题时需要快速恢复的大型重要数据库。
物理备份又可以分为冷备份（脱机备份）、热备份（联机备份）和温备份。
- 冷备份：在数据库关闭状态下进行备份操作。
- 热备份：在数据库处于运行状态时进行备份操作，该备份方法依赖数据库的日志文件。
- 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作。
逻辑备份是对数据库逻辑组件（如表等数据库对象）的备份，表示为逻辑数据库结构（CREATE DATABASE，CREATE TABLE 语句）和内容（INSERT 语句或分隔文本文件）的信息。这种类型的备份适用于可以编辑数据值或表结构较小的数据量，或者在不同的机器体系结构上重新创建数据。
从数据库的备份策略角度分类
从数据库的备份策略角度，数据库的备份可分为完全备份、差异备份和增量备份。
完全备份：每次对数据进行完整的备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复，并且会占用大量的磁盘空间，备份的时间也很长。
差异备份：备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时，只需恢复上次的完全备份与最近的一次差异备份。
增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之间的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。
常见的备份方法
MySQL 数据库的备份可以采用很多种方式，如直接打包数据库文件（物理冷备份）、 专用备份工具（mysqldump）、二进制日志增量备份、第三方工具备份等。
物理冷备份
物理冷备份时需要在数据库处于关闭状态下，能够较好地保证数据库的完整性。物理冷备份一般用于非核心业务，这类业务一般都允许中断，物理冷备份的特点就是速度快，恢复时也是最为简单的。通常通过直接打包数据库文件夹（本章中的数据库文件夹位于 /usr/local/mysql/data）来实现备份。
专用备份工具 mysqldump 或 mysqlhotcopy
mysqldump 程序和 mysqlhotcopy 都可以做备份。mysqldump 是客户端常用逻辑备份程序，能够产生一组被执行以后再现原始数据库对象定义和表数据的 SQL 语句。它可以转储一个到多个 MySQL 数据库，对其进行备份或传输到远程 SQL 服务器。mysqldump 更为通用，因为它可以备份各种表。mysqlhotcopy 仅适用于某些存储引擎。
mysqlhotcopy 是由 Tim Bunce 最初编写和贡献的 Perl 脚本。mysqlhotcopy 仅用于备份 MyISAM 和 ARCHIVE 表。它只能运行在 UNIX 或 Linux 上。因为使用范围小，因此本文中不做详细介绍，如果同学们有兴趣可以在课下研究。
通过启用二进制日志进行增量备份
MySQL 支持增量备份，进行增量备份时必须启用二进制日志。二进制日志文件为用户 提供复制，对执行备份点后进行的数据库更改所需的信息进行恢复。如果进行增量备份（包含自上次完全备份或增量备份以来发生的数据修改），需要刷新二进制日志。
通过第三方工具备份
Percona XtraBackup 是一个免费的 MySQL 热备份软件，支持在线热备份 Innodb 和 XtraDB，也可以支持 MySQL 表备份，不过 MyISAM 表的备份要在表锁的情况下进行。本节对于 Percona XtraBack upr 的叙述是基于 2.4 版本的。
Percona XtrBackup 有三个主要的工具：xtrabackup、innobackupex、xbstream。
- xtrabackup：是一个编译了的二进制文件，只能备份 Innodb/Xtradb 数据文件。
- innobackupex ：是一个封装了 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 的复制，是按这样的一个顺序进行的：首先先锁定表，然后复制，再解锁表。第三方工具备份操作本章中不做详细介绍，如果同学们有兴趣可以在课下研究
数据库完全备份操作
上面提到根据数据库备份策略分类，备份可分为完全备份、差异备份和增量备份。在本章中我们只介绍完全备份与增量备份，感兴趣的同学可以课下自学差异备份的方法。
物理冷备份与恢复
物理冷备份一般用 tar 命令直接打包数据库文件夹，而在进行备份之前需要使用 “systemctl stop mysqld” 命令关闭 mysqld 服务。
备份数据库
创建一个 /backup 目录作为备份数据存储路径，使用 tar 创建备份文件。
整个数据库文件夹备份属于完全备份。
insert into users values('lisi', SHA2('123',456) 密
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# mkdir /backup
[root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
[root@localhost ~]# ls -l /backup/
-rw-r--r-- 1 root root 2473377 3 月 22 日 16:18 mysql_all-2025-03-22.tar.gz
恢复数据库
执行下面操作将数据库文件 /usr/local/mysql/data/ 转移至 bak 目录下，模拟故障。
[root@localhost ~]# mkdir bak
[root@localhost ~]# mv /usr/local/mysql/data/ /root/bak/
[root@localhost ~]# mkdir restore
[root@localhost ~]# tar zxf /backup/mysql_all-2025-03-22.tar.gz -C restore/
[root@localhost ~]# mv restore/usr/local/mysql/data/ /usr/local/mysql/
[root@localhost ~]# systemctl start mysqld
mysqldump 备份与恢复
通过 mysqldump 命令可以将指定的库、表或全部的库导出为 SQL 脚本，便于该命令在不同版本的 MySQL 服务器上使用。例如，当需要升级 MySQL 服务器时，可以先使用 mysqldump 命令将原有库信息导出，然后直接在升级后的 MySQL 服务器中导入即可。
备份数据库
使用 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，将整个 test 库导出为 test.sql 文件，所有操作均以 root 用户身份验证。
[root@localhost ~]# mysqldump -u root -p mysql user > mysql-user.sql
Enter password:
[root@localhost ~]# mysqldump -u root -p --databases test > test.sql
Enter password:
若需要备份整个 MySQL 服务器中的所有库，应使用格式 3。当导出的数据量较大的时候，可以添加 “--opt” 选项以优化执行速度。例如，执行以下操作将创建备份文件 all-data.sql，其中包括 MySQL 服务器中的所有库。
[root@localhost ~]# mysqldump -u root -p --opt --all-databases > all-data.sql
Enter password:查看备份文件
通过 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 `employee`;
CREATE TABLE `employee` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`sex` enum('male','female') NOT NULL DEFAULT'male',
`age` int unsigned NOT NULL DEFAULT '28',
`hire_date` date NOT NULL,
`post` varchar(50) DEFAULT NULL,
`post_comment` varchar(100) DEFAULT NULL,
`salary` double(15,2) DEFAULT NULL,
`office` int DEFAULT NULL,
`depart_id` int DEFAULT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb3;
LOCK TABLES `employee` WRITE;
INSERT INTO `employee` VALUES
(1,'zhangsan','male',18,'2017-03-01','teacher',NULL,7300.33,401,1),(2,
'lisi','male',78,'...//省略部分恢复数据库
使用 mysqldump 命令导出的 SQL 备份脚本，在需要恢复时可以通过 mysql 命令对其进行导入操作，命令格式如下所示。
mysql [选项] [库名] [表名] < /备份路径/备份文件名
当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。
例如，执行以下操作可以从备份文件 mysql-user.sql 中将表导入 test 库。其中 “-e” 选项是用于指定连接 MySQL 后执行的命令，命令执行完后自动退出。
[root@localhost ~]# mysql -u root -p test < mysql-user.sql
Enter password:
[root@localhost ~]# mysql -u root -p -e 'SHOW TABLES FROM test;'//验证导入结果
Enter password:
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
若备份文件中已经包括完整的库信息，则执行导入操作时无须指定库名。
例如，执行以下操作可以从备份文件 test.sql 恢复 test 库。
[root@localhost ~]# mysql -u root -p -e 'DROP DATABASE test;'
//删除 test 数据库,模拟故障
Enter password:
[root@localhost ~]# mysql -u root -p -e 'SHOW DATABASES;'
//查看 test 数据库是否存在
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@localhost data]# mysql -u root -p < ~/test.sql //执行导入恢复操作
Enter password:
[root@localhost ~]# mysql -u root -p -e 'SHOW DATABASES;'//确认恢复后结果
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
| mysql |
| performance_schema |
| sys |
+--------------------+除了使用 mysql 命令结合 “<” 恢复数据外，还可以使用 source 命令恢复数据，具体用法如下
bash
[root@localhost ~]# mysql -u root -p
Enter password:
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)
...
MySQL 增量备份与恢复
使用 mysqldump 进行完全备份，备份的数据中有重复数据，备份时间与恢复时间过长。而增量备份就是自上一次备份之后增加或改变的内容。
MySQL 增量备份概述
增量备份的特点
与完全备份不同，增量备份没有重复数据，备份量不大，时间短；但其恢复麻烦，需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复。MySQL 没有提供直接的增量备份办法，可以通过 MySQL 提供的二进制日志（binary logs）间接实现增量备份。
MySQL 二进制日志对备份的意义
二进制日志保存了所有更新数据库的操作。二进制日志在启动 MySQL 服务器后开始记录，并在文件达到二进制日志所设置的最大值或者接收到 flush logs 命令后重新创建新的日志文件，生成二进制文件序列，并及时把这些日志保存到安全的存储位置，即可完成一个时间段的增量备份。使 max_binlog_size 配置项可以设置二进制日志文件的最大值，如果二进制文件的大小超过了 max_binlog_size，它就会自动创建新的二进制文件。
要进行 MySQL 的增量备份，首先要开启二进制日志功能。开启 MySQL 的二进制日志功能的实现方法有很多种，最常用的是在 MySQL 配置文件的 mysqld 项下加入 “log-bin= 文件路径 / 文件名” 前缀，如 log-bin=/usr/local/mysql/mysql-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 154 Jan 2 14:22 /usr/local/mysql/data/mysql-bin.000001
-rw-r----- 1 mysql mysql 19 Jan 2 14:22 /usr/local/mysql/data/mysql-bin.index
MySQL 增量恢复
在维护数据库时，因为各种各样的原因可能会导致数据丢失，如：人为的 SQL 语句破坏数据库、在进行下一次全备份之前发生系统故障导致数据库数据丢失、在数据库主从架构中主库的数据发生故障等。当出现以上场景时可以使用增量恢复来恢复数据。
常用的增量恢复的方法有三种：一般恢复、基于位置的恢复、基于时间点的恢复。
一般恢复：将所有备份的二进制日志内容全部恢复，命令格式如下所示。
mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户基于位置的恢复：数据库管理员在操作数据库时可能在同一时间点既有错误的操作也有正确的操作，通过基于位置进行恢复可以更加精准，命令格式如下所示。
- 格式 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='年-月-- 格式 3：从某个时间点到某个时间点的恢复。
plaintext
mysqlbinlog [--no -defaults] --start -datetime='年-月-日 小时:分钟:秒' --stop -datetime='年-月-日小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码
下面通过一个企业级的备份案例讲解增量备份与恢复的具体操作。
MySQL 企业备份案例
需求描述：北京移电通信公司的用户信息数据库为 client，用户资费数据表为 user_info，该公司每周需要进行完全备份，每天需要进行增量备份。新增加的用户信息如表 4-1 所示。
新增用户信息
| 身份证 | 姓名 | 性别 | 用户 ID 号 | 资费 |
|---|---|---|---|---|
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 赵六 | 男 | 019 | 37 |
| 000010 | 孙七 | 男 | 020 | 36 |
一般恢复
添加数据库、表，录入信息
在进行备份之前，先根据给出的需求创建用户信息数据库 client、用户资费数据表 user_info，并且根据需求描述中的表格插入前三条用户的数据。
[root@localhost ~]#mysql -uroot -p
Enter password:
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) NOT NULL,name CHAR(20) NOT NULL, gender CHAR(4),UserID CHAR(10) NOT NULL,expenses INT(10));
Query OK, 0 rows affected (0.12 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('000008','王五','女','018','23');
Query OK, 1 row affected (0.01 sec)
mysql>SELECT * FROM user_info;
+-------------+-------+--------+--------+---------+
| IdentityCard| name | gender | UserID | expenses|
+-------------+-------+--------+--------+---------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
+-------------+-------+--------+--------+---------+
3 rows in set (0.00 sec)先进行一次完全备份
为方便验证二进制日志的增量恢复功能，在插入三条用户数据后先对 client 数据库的 user_info 表进行一次完全备份。然后在 Linux 系统命令行下执行 “mysqladmin -uroot -p flush-logs” 命令或在 “mysql>” 命令提示符下执行 “flush logs;” 生成新的二进制日志。
[root@localhost ~]# mkdir /mysql_bak
[root@localhost ~]# mysqldump -uroot -p clientuser_info >/mysql_bak/client_userinfo-$(date +%F).sql
Enter password:
[root@localhost ~]# ls /mysql_bak/
client_userinfo-2025-03-24.sql
[root@localhost ~]# mysqladmin -uroot -p flush-logs
Enter password:
[root@localhost ~]# ls -l /usr/local/mysql/data/mysql-bin.*
-rw-r----- 1 mysql mysql 1724 3 月 24 日 19:28 /usr/local/mysql/data/mysql-bin.000001
-rw-r----- 1 mysql mysql 157 3 月 24 日 19:28 /usr/local/mysql/data/mysql-bin.000002
-rw-r----- 1 mysql mysql 78 3 月 24 日 19:28 /usr/local/mysql/data/mysql-bin.index
继续录入新的数据并进行增量备份
继续录入两个用户的数据，并执行 “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.02 sec)
mysql>INSERT INTO user_info VALUES('000010','孙七','男','020','36');
Query OK, 1 row affected (0.02 sec)
mysql>SELECT * FROM user_info;
+-------------+-------+--------+--------+---------+
| IdentityCard| name | gender | UserID | expenses|
+-------------+-------+--------+--------+---------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 赵六 | 男 | 019 | 37 |
| 000010 | 孙七 | 男 | 020 | 36 |
+-------------+-------+--------+--------+---------+
[root@localhost ~]# mysqladmin -uroot -p flush-logs
Enter password:
[root@localhost ~]# ls -l /usr/local/mysql/data/mysql-bin.*
-rw-r----- 1 mysql mysql 1724 3 月 24 日 19:28 /usr/local/mysql/data/mysql-bin.000001
-rw-r----- 1 mysql mysql 886 3 月 24 日 19:29 /usr/local/mysql/data/mysql-bin.000002
-rw-r----- 1 mysql mysql 4547 3 月 24 日 19:33 /usr/local/mysql/data/mysql-bin.000003
-rw-r----- 1 mysql mysql 117 3 月 24 日 19:29 /usr/local/mysql/data/mysql-bin.index
[root@localhost ~]# /bin/cp /usr/local/mysql/data/mysql-bin.000002 [mysql_bak/
模拟误操作删除 user_info 表
[root@localhost ~]# mysql -uroot -p -e 'DROP TABLE client.user_info;'
Enter password:
[root@localhost ~]# mysql -uroot -p -e 'SELECT * FROM client.user_info;'
Enter password:
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
恢复操作
再执行恢复操作时，需要先恢复完全备份，然后恢复增量备份。
[root@localhost ~]# mysql -uroot -p client < /mysql_bak/client_userinfo-2025-03-24.sql
Enter password:
// 恢复完全备份
Enter password:
[root@localhost ~]# mysql -uroot -p -e 'SELECT * FROM client.user_info;'
Enter password:
+-------------+-------+--------+--------+---------+
| IdentityCard| name | gender | UserID | expenses|
+-------------+-------+--------+--------+---------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
+-------------+-------+--------+--------+---------+
基于位置恢复
由于前面已经做过备份操作，接下来直接进行模拟故障与数据恢复的操作。
想要实现基于位置或时间点恢复数据，必须先通过查看二进制日志文件确定恢复的位置或时间点。使用 “mysqlbinlog --no-defaults 二进制日志文件” 可以查看二进制日志文件的具体内容。
[root@localhost ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002
……//(省略部分内容
BEGIN
/*!*/;
# at 322
#250324 19:29:22 server id 1 end_log_pos 467 CRC32 0x836h8647
Query thread_id=12 exec_time=0 error_code=0
use `client` /*!*/;
SET TIMESTAMP=1742815762/*!*/;
INSERT INTO user_info VALUES('000009','赵六','男','019','37')
/*!*/;
# at 467
#250324 19:29:22 server id 1 end_log_pos 498 CRC32 0xdc633d08
Xid = 65
COMMIT /*!*/;
# at 498
#250324 19:29:28 server id 1 end_log_pos 577 CRC32 0x3f50b233
Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
original_commit_timestamp=1742815768667356
immediate_commit_timestamp=1742815768667356
transaction_length=341
# original_commit_timestamp=1742815768667356 (2025-03-24 19:29:28.667356 CST)
# immediate_commit_timestamp=1742815768667356 (2025-03-24 19:29:28.667356 CST)
/*!80000 SET @@session.original_commit_timestamp=1742815768667356/*!*/;
/*!80014 SET @@session.original_server_version=80036/*!*/;
/*!80014 SET @@session.immediate_server_version=80036/*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 577
#250324 19:29:28 server id 1 end_log_pos 663 CRC32 0x5a162276
Query thread_id=12 exec_time=0 error_code=0
SET TIMESTAMP=1742815768/*!*/;
BEGIN
/*!*/;
# at 663
#250324 19:29:28 server id 1 end_log_pos 808 CRC32 0xf36d9ea7
Query thread_id=12 exec_time=0 error_code=0通过查看日志文件的具体内容可以发现，在每进行一个操作之前都会有一个独特的编号，如 “# at 663” 。此编号随着操作数增多而变大，我们称之为操作 ID。在操作 ID 下面紧跟着的是时间标记。要实现基于位置或时间点恢复数据，需要分别依赖二进制日志文件中的操作 ID 或者时间标记。例如，通过二进制日志文件得知，在操作 ID 为 “663” 的时候，user_info 表中插入了 “孙七” 的用户数据。因此执行以下命令可以实现仅恢复到操作 ID 为 “663” 之前的数据，即不恢复 “孙七” 的信息。这时所恢复的数据是从二进制日志文件的开始位置直到指定位置。
[root@localhost ~]# mysqlbinlog --no -defaults --stop -position='623'
/mysql_bak/mysql -bin.000002 | mysql -uroot -p
Enter password:
[root@localhost ~]# mysql -uroot -p -e 'SELECT * FROM client.user_info;'
Enter password:
| IdentityCard | name | gender | UserID | expenses |
|---|---|---|---|---|
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 赵六 | 男 | 019 | 37 |
上述操作命令中，“--stop -position” 指定的是停止的位置。如果仅恢复 “孙七” 的信息，跳过 “赵六” 的信息恢复，可以使用 “--start -position” 选项指定开始恢复数据的位置。这时所恢复的数据是从指定位置开始直到二进制日志文件的最后。
基于时间点恢复
基于时间点的数据恢复所使用的选项是 “--stop-datetime”，指定的时间同样也是查询二进制日志所得。执行以下操作可以实现仅恢复到 16:41:24 之前的数据，即不恢复 “孙七” 的信息。
[root@localhost ~]# mysql -uroot -p -e 'DROP TABLE client.user_info;'
Enter password:
[root@localhost ~]# mysql -uroot -p client < /mysql_bak/client_userinfo-2025-03-24.sql
Enter password:
[root@localhost ~]# mysqlbinlog --no -defaults --stop-datetime='2025-03-24 19:29:28' /mysql_bak/mysql -bin.000002 | mysql -uroot -p
Enter password:
[root@localhost ~]# mysql -uroot -p -e 'SELECT * FROM client.user_info;'
Enter password:执行以下操作可以实现仅恢复 “孙七” 的信息，跳过 “赵六” 的信息恢复。
[root@localhost ~]# mysql -uroot -p -e 'DROP TABLE client.user_info;'
Enter password:
[root@localhost ~]# mysql -uroot -p client < /mysql_bak/client_userinfo-2025-03-24.sql
Enter password:
[root@localhost ~]# mysqlbinlog --no -defaults --start-datetime='2025-03-24 19:29:28' /mysql_bak/mysql -bin.000002 | mysql -uroot -p
Enter password:
[root@localhost ~]# mysql -uroot -p -e 'SELECT * FROM client.user_info;'
Enter password:制定企业备份策略的思路
在企业中备份策略并不是千篇一律的，而是根据每个企业的实际生产环境与业务需求制定合适的备份策略。无论是选择完全备份，还是选择增量备份，都需考虑它们的优缺点，是否适合当前的生产环境。同时，为了保证恢复的完整性，建议开启二进制日志功能，二进制日志文件给恢复工作也带来了很大的灵活性，可以基于时间点或位置进行恢复。考虑到数据库性能，可以将二进制日志文件保存到其他安全的硬盘中。
在进行热备份时，备份操作和应用服务在同时运行，这样就十分消耗系统资源了，导致数据库服务性能下降，这就需要选择一个合适的时间（如在应用负担很小的时候）再来进行备份操作。
需要注意的是，不是备份完就万事大吉，最好确认备份是否可用，所以备份之后的恢复测试是非常有必要的。同时备份时间也要灵活调整，如：
- 数据更新频繁，则应该频繁地备份。
- 数据的重要性，在有适当更新时进行备份。
- 在数据库压力小的时间段进行备份，如一周一次完全备份，每天进行增量备份。
- 中小公司，完全备份一般一天一次即可。
- 大公司可每周进行一次完全备份，每天进行一次增量备份。
- 尽量为企业实现主从复制架构，以增加数据的可用性。
扩展：Mysql 的 GTID 和 XtraBackup
Mysql 的 GTID
GTID 即全局事务 ID (global transaction identifier)，其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的 ID。GTID 最初由 google 实现，官方 MySQL 在 5.6 才加入该功能。
GTID 实际上是由 UUID+TID（即 transactionId）组成的。其中 UUID (即 server_uuid) 产生于 auto.cnf 文件 (cat /data/mysql/data/auto.cnf)，是一个 MySQL 实例的唯一标识。TID 代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以 GTID 能够保证每个 MySQL 实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）。GTID 在一组复制中，全局唯一。
通过下面的实验，了解基于 gtid 的增量备份和恢复（Gtid 的备份也是基于 binlog 的）
(1) 配置 my.cnf 开启 gtid
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON # 确保事务安全性
[root@localhost ~]# systemctl restart mysqld验证是否开启
[root@localhost ~]# mysql -uroot -p
mysql> SHOW GLOBAL VARIABLES LIKE 'gtid_mode';
| Variable_name | Value |
| ------------- | ----- |
| gtid_mode | ON |
1 row in set (0.00 sec)
创建基本测试库、表、数据
mysql> reset master; #初始化master，会清除所有binlog和gtid信息
mysql> show master status;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| ---------------- | -------- | ------------ | ---------------- | ----------------- |
| mysql-bin.000001 | 157 | | | |
创建测试库 test，测试表 user，并导入 3 条数据
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table user(id int);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into user values(1);
Query OK, 1 row affected (0.14 sec)
mysql> insert into user values(2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values(3);
Query OK, 1 row affected (0.00 sec)mysql> show master status;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| ---------------- | -------- | ------------ | ---------------- | ----------------- |
| mysql-bin.000001 | 1417 | | | d780a5a6-055f-11f0-b6e6-000c29078b04:1-5 |
1 row in set (0.00 sec)全量备份
[root@localhost ~]# mysqldump -u root -p --databases test >test.sql
Enter password:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all -databases --triggers --routines --events.
Warning: A dump from a server that has GTIDs enabled will by default include the GTIDs of all transactions, Even those that were re -executed during its extraction and might not be represented in the dumped data. This might result in an inconsistent data dump. In order to ensure a consistent backup of the database, pass --single-transaction or --lock-all-tables or --master-data.
关于数据一致性的提醒的警告信息，可忽略
[root@localhost ~]# grep -i gtid test.sql
-- GTID state at the beginning of the backup
SET @@GLOBAL.GTID_PURGED='/*!80000 '+'*/
d780a5a6-055f-11f0-b6e6-000c29078b04:1-5[root@localhost ~]# mysql -uroot -p
mysql> use test;
Database changed
mysql> insert into user values(4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values(5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test.user;
+----+
| id |
|----|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
5 rows in set (0.00 sec)
mysql> show master status;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| ---------------- | -------- | ------------ | ---------------- | ----------------- |
| mysql-bin.000001 | 2007 | | | d780a5a6-055f-11f0-b6e6-000c29078b04:1-7 |
1 row in set (0.00 sec)模拟数据删除
mysql> drop database test;
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| ---------------- | -------- | ------------ | ---------------- | ----------------- |
| mysql-bin.000001 | 2188 | | | d780a5a6-055f-11f0-b6e6-000c29078b04:1-8 |
导出增量数据
[root@localhost ~]# mysqlbinlog
--include-gtids='d780a5a6-055f-11f0-b6e6-000c29078b04:3-7'
/usr/local/mysql/data/mysql-bin.000001 >/mysqlbak.sql
这里把 3 - 7 的事务导出，也就是全量备份后，新插入的两条数据，第 8 个事务不能导除，因为它是 drop database 的误删除语句
恢复全量
[root@localhost ~]#mysql -u root -p -e "RESET MASTER;" # 清空GTID历史,不然恢复全量备份时会产生冲突，生产环境谨慎操作须提前备份
[root@localhost ~]# mysql -uroot -p <test.sql
[root@localhost ~]# mysql -uroot -p -e "select * from test.user;"
Enter password:
| id |
|----|
| 1 |
| 2 |
| 3 |恢复增量
[root@localhost ~]# mysql -uroot -p <mysqlbak.sql
[root@localhost ~]# mysql -uroot -p -e "select * from test.user;"
Enter password:
| id |
|----|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
XtraBackup
MySQL 冷备、mysqldump、MySQL 热拷贝都无法实现对数据库进行增量备份。在实际生产环境中增量备份是非常实用的，如果数据大于 50G 或 100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周实用完整备份，周一到周六实用增量备份。而 Percona-Xtrabackup 就是为了实现增量备份而出现的一款主流备份工具，xtrabackup 有 2 个工具，分别是 xtrabackup、innobackupex。
Percona-xtrabackup 是 Percona 公司开发的一个用于 MySQL 数据库物理热备的备份工具，支持 MySQL、Percona server 和 MariaDB，开源免费，是目前较为受欢迎的主流备份工具。xtrabackup 只能备份 innodb 和 xtraDB 两种数据引擎的表，而不能备份 MyISAM 数据表。
安装 xtrabackup
[root@localhost ~]#wgethttps://downloads.percona.com/downloads/Percona -XtraBackup-8.0/Percona-XtraBackup-8.0.35-30/binary/tarball/percona-x trabackup-8.0.35-30-Linux-x86_64.glibc2.17.tar.gz
[root@localhost ~]#tar xzf percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17.tar.gz
[root@localhost ~]#mv percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17 /usr/local/xtrabackup
[root@localhost ~]#echo 'export PATH=$PATH:/usr/local/xtrabackup/bin' >> /etc/profile
[root@localhost ~]#source /etc/profile
安装 qpress
qpress 是解压缩需要用到的工具
[root@localhost ~]#wget -d --user-agent="Mozilla/5.0 (Windows NT x.y; rv:10.0) Gecko/20100101 Firefox/10.0" https://docs-tencentdb-1256569818.cos.ap-guangzhou.myqcloud.com/qpres s-11-linux-x64.tar
[root@localhost ~]#tar -xf qpress-11-linux-x64.tar -C /usr/local/bin
[root@localhost ~]#source /etc/profile完整备份与恢复
备份
[root@localhost ~]#bakdir="/backup/fullbackups/$(date '+%F')"
[root@localhost ~]#mkdir -p $bakdir
# --backup 备份
# --compress 压缩
[root@localhost ~]#xtrabackup --defaults-file=/etc/my.cnf --user=bkpuser --password=s3cret --backup --compress --target-dir=$bakdir
恢复（恢复数据的时候要先关闭 MySQL，清理数据存储目录）
# 解压备份数据
[root@localhost ~]#xtrabackup --defaults-file=/etc/my.cnf --user=bkpuser --password=s3cret --decompress --target-dir=$bakdir
# 准备备份文件以供恢复
[root@localhost ~]#xtrabackup --prepare --target-dir=$bakdir
# 恢复数据
[root@localhost ~]#xtrabackup --defaults-file=/etc/my.cnf --user=bkpuser --password=s3cret --copy-back --target-dir=$bakdir
# 修改权限，替换成自己的数据存储目录
[root@localhost ~]#chown -R mysql:mysql /var/lib/mysql
# 然后启动MySQL即可
[root@localhost ~]#systemctl start mysqld增量备份与恢复
备份
plaintext
[root@localhost ~]#fulldir="/backup/fullbackups/$(date '+%F')"
[root@localhost ~]#incdir="/backup/incrementalbackups/$(date '+%F')"
[root@localhost ~]#mkdir -p $fulldir
[root@localhost ~]#mkdir -p $incdir
# 准备基础数据后进行全量备份
[root@localhost ~]#xtrabackup --defaults-file=/etc/my.cnf --user=bkpuser --password=s3cret --backup --compress --target-dir=$fulldir
# 插入新数据后进行增量备份
[root@localhost ~]#xtrabackup --defaults-file=/etc/my.cnf --user=bkpuser --password=s3cret --backup --compress --target-dir=$incdir --incremental-basedir=$fulldir
在备份的目录里面会有 xtrabackup_checkpoints 文件
增量恢复
恢复数据的时候要先关闭 MySQL，清理数据存储目录
# 解压备份数据
[root@localhost ~]#xtrabackup --defaults-file=/etc/my.cnf --user=root --password= --decompress --target-dir=$fulldir
[root@localhost ~]#xtrabackup --defaults-file=/etc/my.cnf --user=root --password= --decompress --target-dir=$incdir
# 准备数据
[root@localhost ~]#xtrabackup --prepare --apply-log-only --target-dir=$fulldir
# 把增量备份的数据合并到完整备份里面
[root@localhost ~]#xtrabackup --prepare --apply-log-only --target-dir=$fulldir --incremental-dir=$incdir
# 完整重放日志
[root@localhost ~]#xtrabackup --prepare --target-dir=$fulldir
# 恢复
[root@localhost ~]#xtrabackup --defaults-file=/etc/my.cnf --user=root --password= --copy-back --target-dir=$fulldir
# 修改权限，替换成自己的数据存储目录
[root@localhost ~]#chown -R mysql:mysql /usr/local/mysql/data
# 然后启动MySQL即可
[root@localhost ~]#systemctl start mysqld