MySQL的备份和恢复

一、MySQL 数据库备份概述

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

1、 数据备份的重要性

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

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

2、数据库备份类型

2.1、 从物理与逻辑的角度分类

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

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

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

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

2.2.从数据库的备份策略角度分类

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

完全备份	每次对数据进行完整的备份，即对整个数据库、数据库结构和文件结构的备份,保存的是备份完成时刻的数据库,是差异备份与增量备份的基础。完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复，并且会占用大量的磁盘空间，备份的时间也很长
差异备份	备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时，只需恢复上次的完全备份与最近的一次差异备份
增量备份	只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次增量备份的时间为时间点,仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之间的所有增量依次恢复,如中间某次的备份数据损坏，将导致数据的丢失

3、常见的备份方法

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

3.1 物理冷备份

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

3.2、专用备份工具 mysqldump 或 mysqlhotcopy

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

        mysqlhotcopy是由TimBunce 最初编写和贡献的Perl 脚本。mysqlhotcopy 仅用于备份 MyISAM 和 ARCHIVE 表。它只能运行在 UNIX 或 Linux上。因为使用范围小，因此本文中不做详细介绍，如果同学们有兴趣可以在课下研究。

3.3、通过启用二进制日志进行增量备份

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

3.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 的复制，是按这样的一个顺序进行的:首先锁定表，然后复制，再解锁表。

二、数据库完全备份操作

1、物理冷备份与恢复

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

1.1、备份数据库

#关闭数据库
systemctl stop mysqld#创建备份目录
mkdir -p /opt/backup#切换到原文件目录
cd /usr/local/mysql/#备份
tar zcf /opt/backup/sqldata-$(date +%F).tar.gz data#查看
cd /opt/backup && ll

1.2、恢复数据库

#改名
mv data data.bak#解压到指定位置
tar -zxf sqldata-2025-04-12.tar.gz -C /usr/local/mysql/

2、mysqldump 备份与恢复

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

2.1、备份数据库

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

#格式1:备份指定库中的部分表
mysqldump [选项]库名[表名 1][表名 2]…>/备份路径/备份文件名###栗子###
mysqldump -uroot -ppwd123 db1 t1 >/opt/backup/db1_t1_tb.bak#格式2:备份一个或多个完整的库(包括其中所有的表)。
mysqldump [选项] --databases库名1[库名2]…###栗子###
mysqldump -uroot -ppwd123 --databases db1 > /opt/backup/db1_db.bak#格式3:备份MySQL服务器中所有的库
mysqldump [选项]--al1-databases>/备份路径/备份文件名###栗子###
mysqldump -uroot -ppwd123 --all-databases > /opt/backup/db_all.bak

2.2、恢复数据库

###栗子###
mysql -uroot -ppwd123 db1 </opt/backup/db1_t1_tb.bak

2.2.1、删除表

2.2.2、恢复表

###栗子###
mysql -uroot -ppwd123 < /opt/backup/db1_db.bak

2.2.3、删除库

2.2.4、恢复库

2.2.5、使用 source 命令恢复数据

#删除
mysql -uroot -ppwd123 -e "drop database db1;"#恢复
mysql -uroot -ppwd123 -e "source /opt/backup/db1_db.bak;"#查看
mysql -uroot -ppwd123 -e "show databases;"

三、MySQL 增量备份与恢复

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

1、 MySQL 增量备份概述

1.1、增量备份的特点

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

        二进制日志保存了所有更新数据库的操作。二进制日志在启动 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

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

vim /etc/my.cnf[mysqld]#启用二进制日志(Binary Log)并指定其存储路径
log-bin=/usr/local/mysql/data/mysql-bin#定义二进制日志的记录格式为混合模式
binlog_format=MIXED#为mysql 实例分配一个唯一的服务器标识符
server-id=1#重启服务
systemctl restart mysqld

1.2、MySQL 增量恢复格式

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

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

1.2.1、一般恢复:

#将所有备份的二进制日志内容全部恢复
mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u用户名 -p密码

1.2.2、基于位置的恢复:

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

#恢复数据到指定位置
mysqlbinlog --stop-position='操作 id' 二进制日志 | mysql -u用户名 -p密码#从指定的位置开始恢复数据
mysqlbinlog --start-position='操作 id' 二进制日志 | mysql-u 用户名 -p密码

1.2.3、基于时间点的恢复:

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

#从日志开头截止到某个时间点的恢复
mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 时:分:秒' 二进制日志 | mysql -u用户名 -p密码#从某个时间点到日志结尾的恢复
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 时:分:秒' 二进制日志 | mysql -u用户名 -p密码#从某个时间点到某个时间点的恢复
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 时:分:秒' --stop-datetime='年-月-日 时:分:秒' 二进制日志 | mysql -u用户名 -p密码

1.3、应用栗子：

1.3.1、一般恢复例子：

#创建库
create database DB1;#使用库
use DB1;#创建表
create table T1 (id int(3));#插入数据
insert into T1 values(1);
insert into T1 values(2);
insert into T1 values(3);#查看表的内容
select * from T1；

#创建完整备份
mysqldump -uroot -ppwd123 DB1 T1 >/opt/backup/DB1_T1_tb-$(date +%F).sqlbak#生成二进制日志
mysqladmin -uroot -ppwd123 flush-logs

mysql -uroot -ppwd123 -e "drop table DB1.T1;"

#恢复完整备份
mysql -uroot -ppwd123 DB1 < /opt/backup/DB1_T1_tb-$(date +%F).sqlbak#查看
mysql -uroot -ppwd123 -e "select * from DB1.T1;"#恢复增量备份
mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -ppwd123#查看
mysql -uroot -ppwd123 -e "select * from DB1.T1;"

1.3.2、基于位置恢复（指定开始，结束）

#查看二进制日志
mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000002

mysqlbinlog --no-defaults --start-position='316' --stop-position='447' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -ppwd123

1.3.3、基于时间点恢复（指定开始，结束）

mysqlbinlog --no-defaults --start-datetime='2025-04-12 17:47:58' --stop-datetime='2025-04-12 17:48:06' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -ppwd123

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

        在企业中备份策略并不是千篇一律的，而是根据每个企业的实际生产环境与业务需求制定合适的备份策略。无论是选择完全备份，还是选择增量备份，都需考虑它们的优缺点，是否适合当前的生产环境。同时，为了保证恢复的完整性，建议开启二进制日志功能，二进制日志文件给恢复工作也带来了很大的灵活性，可以基于时间点或位置进行恢复。考虑到数据库性能，可以将二进制日志文件保存到其他安全的硬盘中。
        在进行热备份时，备份操作和应用服务在同时运行，这样就十分消耗系统资源了，导致数据库服务性能下降，这就需要选择一个合适的时间(如在应用负担很小的时候)再来进行备份操作。
需要注意的是，不是备份完就万事大吉，最好确认备份是否可用，所以备份之后的恢复测试是非常有必要的。

灵活调整备份时间

数据更新频繁，则应该频繁地备份

数据的重要性，在有适当更新时进行备份

在数据库压力小的时间段进行备份，如一周一次完全备份，每天进行增量备份

中小公司，完全备份一般一天一次即可

大公司可每周进行一次完全备份，每天进行一次增量备份

尽量为企业实现主从复制架构，以增加数据的可用性

五、扩展:Mysql的GTID

1、Mysql的GTID

        GTID 即全局事务 ID(global transaction identifier)，其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的 ID。GTID 最初由 google实现，官方 MySQL在 5.6才加入该功能

        GTID 实际上是由 UUID+TID(即 transactionId)组成的。其中 UUID(即server uuid)产生于auto.conf 文件(cat /data/mysql/data/auto.cnf)，是个 MySQL, 实例的唯一标识。TID 代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以GTID能够保证每个MySQL实例事务的执行(不会重复执行同一个事务，并且会补全没有执行的事务)。GTID在一组复制中，全局唯一，Gtid 的备份也是基于binlog 的。

1.1、配置my.cnf 开启 gtid

vim /etc/my.cnf[mysql]
#启用二进制日志(Binary Log)并指定其存储路径
log-bin=/usr/local/mysql/data/mysql-bin
#定义二进制日志的记录格式为混合模式
binlog_format=MIXED
#为mysql 实例分配一个唯一的服务器标识符
server-id=1
#启用模块
gtid_mode = ON
#确保事务安全性
enforce_gtid_consistency = ON#重启服务
systemctl restart mysqld#查看gtid模块是否开启
mysql -uroot -ppwd123 -e "show global variables like 'gtid_mode'"

1.2、创建基本测试库、表、数据

#初始化 master，会清除所有 binlog 和 gtid 信息
reset master;

创建db2库、t2表

create database db2;
use db2;
create table t2 (id int(2));
insert into t2 values (1);
insert into t2 values (2);
insert into t2 values (3);

#f3222d6e-1770-11f0-85b1-000c294b1fbd:1-5
表示在该实例上第一个到第五个事务的所有操作
事务1：create database db2;
事务2：create table t2 (id int(2));
事务3：insert into t2 values (1);
事务4：insert into t2 values (2);
事务5：insert into t2 values (3);

1.3、完整备份

#完整备份
mysqldump -uroot -ppwd123 --databases db2 > /opt/backup/gtid_db2.sql#过滤出有关gtid的行
grep -i gtid gtid_db2.sql

1.4、插入数据，模拟误操作

f3222d6e-1770-11f0-85b1-000c294b1fbd:1-9
表示在该实例上第一个到第五个事务的所有操作
事务1：create database db2;
事务2：create table t2 (id int(2));
事务3：insert into t2 values (1);
事务4：insert into t2 values (2);
事务5：insert into t2 values (3);
事务6：insert into t2 values (4);
事务7：insert into t2 values (5);
事务8：insert into t2 values (6);
事务9：drop database db2;

1.4、导出增量备份

#导出增量备份
mysqlbinlog --include-gtids=f3222d6e-1770-11f0-85b1-000c294b1fbd:6-8 /usr/local/mysql/data/mysql-bin.000001 >gtid_db2.sql2事务6：insert into t2 values (4);
事务7：insert into t2 values (5);
事务8：insert into t2 values (6);

        这里把6-8的事务导出，也就是全量备份后，新插入的两条数据，第9个事务不能导除，因为它是 drop database 的误删除语句

1.5、恢复全量备份

#清空GTID历史,不然恢复全量备份时会产生冲突，生产环境谨慎操作须提前备份
mysql -uroot -ppwd123 -e 'reset master;'#恢复全量备份
mysql -uroot -ppwd123 < /opt/backup/gtid_db2.sql#查看
mysql -uroot -ppwd123 -e 'select * from db2.t2;'

1.6、恢复增量备份

#恢复全量备份
mysql -uroot -ppwd123 < /opt/backup/gtid_db2.sql2#查看
mysql -uroot -ppwd123 -e 'select * from db2.t2;'