4.MySQL全量、增量备份与恢复

1.数据备份的重要性

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

a.程序错误

b.人为操作失误

c.运算错误

d.磁盘故障

e.灾难（如火灾、地震）和盗窃

2.数据库备份类型

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

物理备份：是对数据库操作系统物理文件（如数据文件、日志文件）的备份，这种类型的备份适用于在出现问题时需要快速恢复的大型重要数据库

     物理备份分为三种：

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

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

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

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

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备份数据库

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

命令：

systemctl  stop  mysqld

mkdir  /backup

tar  zcf  /backup/mysql_all-$(data  +%F).tar.gz  /usr/local/mysql/data/

ls  -l  /backup/

1.2恢复数据库

先将数据库文件移至bak目录下，模拟故障

命令：

mkdir  bak

mv  /usr/local/mysql/data/  /root/bak/

mkdir  restore

tar  zxf  /backup/mysql_all-2025-03-22.tar.gz  -C  restore/

mv  restore/usr/local/mysql/data/  /usr/local/mysql/

systemctl  start  mysqld

2.mysqldump备份与恢复

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

2.1备份数据库

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

格式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用户身份验证。

命令：

mysqldump  -uroot  -ppwd123  mysql  user  >  myql-user.sql

mysqldump  -uroot  -ppwd123 --databases  test  >  test.sql

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

命令：mysqldump  -uroot  -ppwd123  --opt  --all-databases  >  all-data.sql

2.2查看备份文件

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

命令：grep  -v  "^--"  test.sql  |  grep  -v  "^/"  |  grep  -v  "^$"

2.3恢复数据库

使用mysqldump命令导出的SQL备份脚本，在需要恢复时可以通过mysql命令对其进行导入操作，命令格式：mysql  [选项]  [库名]  [表名]  <  /备份路径/备份文件名  

当备份文件中只包含表的备份，而不包含创建的语句时，执行导入test库，其中“-e”选项是用于指定连接MySQL后执行的命令，命令执行完成后自动退出

mysql  -uroot  -ppwd123  test  < mysql-user.sql

mysql  -uroot  -ppwd123  -e  "show  tables  from  test;"      (验证导入结果)

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

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

命令：mysql  -u  root  -ppwd123  -e  "drop  database  test;"    (删除test库，模拟故障)

           mysql  -uroot  -ppwd123  -e  "show  databases;"       (查看库是否存在)

           mysql  -u  root  -p  <  test.sql

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

mysql  -u  root -ppwd123  -e "source  /root/test.sql"

3.MySQL增量备份与恢复

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

3.1MySQL增量备份概述

（1）增量备份的特点

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

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

二进制日志保存了所有更新数据库的操作。二进制日志在启动 MySQL 服务器后开始记录，并在文件达到二进制日志所设置的最大值或者接收到flush logs 命令后重新创建新的日志文件，生成二进制文件序列，并及时把这些日志保存到安全的存储位置，即可完成一个时间段的增量备份。使max_binlog_size配置项可以设置二进制日志文件的最大值，如果二进制文件的大小超过了max_ binlog_ size，它就会自动创建新的二进制文件。
要进行 MySQL 的增量备份，首先要开启二进制日志功能。开启 MySQL的二进制日志功能的实现方法有很多种，最常用的是在 MySQL 配置文件的 mysqld项下加入“log-bin=/ 文件路径/文件名”前缀，如1og-bin=/usr/local/mysql/mysq1-bin,然后重启 MySQL 服务就可 以在指定路径下查看二进制日志文件了。默认情况下，二进制日志文件的扩展名是一个六位的数字，如 mysql-bin.000001。

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

命令：vim  /etc/my.cnf

[mysqld]

log-bin=/usr/local/mysql/data/mysql-bin            (启用二进制日志，并指定其存储路径)

binlog_format=MINED                                    (定义二进制日志的记录格式为混合模式)

server-id=1                                                       （为mysql实例分配一个唯一的服务器标识符）

           systemctl  restart  mysqld

           ls  -l  /usr/local/mysql/data/mysql-bin.*

3.2MySQL增量恢复

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

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

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-datatime='年-月-日  小时：分钟：秒'  二进制文件  | mysql -u 用户名 -p 密码

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

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

四.扩展：MySQL的GTID和XtraBackup

1.Mysql的GTID

GTID即全局事务ID，其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID

GTID实际上是由UUID+TID组成。其中UUID产生于auto.conf文（cat /data/mysql/data/auto.conf）

是一个MySQL示例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以GTID能够保证每个mysql实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）。GTID在一组复制中，全局唯一

（1）配置my.cnf开启gtid

vim  /etc/my.cnf

[mysqld]

gtid_mode=ON

enforce_gtid_consistency=ON  （确保事务安全性）

mysql  -uroot  -ppwd123  -e  "show  variables  'gtid_mode';"

(2)创建基本测试库、表、数据

mysql>reset  master;               （初始化master，会清除所有binlog和gtid信息）

mysql>show  master  status;

创建测试库test，测试表user，并导入三条数据

mysql>create  database  test;

msyq>use test

mysql>create  table  user(id  int);

mysql>insert  into  user  values(1);

mysql>insert  into  user  values(2);

mysql>insert  into  user  values(3);

mysql>show  master  status;

(3)全量备份

mysqldump  -uroot -ppwd123  --databases  test  > test.sql

grep  -i  gtid  test.sql

(4)插入新数据

mysql  -uroot  -ppwd123

use  test

insert  into  user   values(4)；

insert  into  user  values(5);

select  *  from  test.user;

show  master  status;

(5)模拟数据误删除

mysql>drop  database  test;

mysql>show  master  status;

(6)导入增量数据

mysqlbinlog  --include-gtids='d7................1-7'  /usr/local/mysql/data/mysql-bin.000001  > /mysqlbak.sql

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

（7）恢复全量

mysql  -uroot  -ppwd123  -e  'reset  master;'     (清空GTID历史，不然恢复全量备份时会产生冲突)

mysql  -uroot  -p  < test.sql

mysql  -uroot  -p  -e  "select  *  from  test.user;"

(8)恢复增量

mysql  -uroot   -ppwd123  <  mysqlbak.sql

mysql  -uroot  -ppwd123  -e  "select  *  from  test.user;"

2.XtraBackup

MySQL 冷备、mysqldump、MySQL,热拷贝都无法实现对数据库进行增量备份。在实际生产环境中增量备份是非常实用的，如果数据大于50G或100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周实用完整备份，周一到周六实用增量备份。而Percona-Xtrabackup 就是为了实现增量备份而出现的一款主流备份工具，xtrabakackup有2个工具，分别是xtrabakup、innobakupe。
Percona-xtrabackup 是 Percona 公司开发的一个用于 MySQL 数据库物理热备的备份工具，支持MySQL、Percona server 和 MariaDB，开源免费，是目前较为受欢迎的主流备份工具。xtrabackup 只能备份 innoDB 和 xtraDB 两种数据弓警的表，而不能备份 MyISAM 数据表。

(1)安装xrabackup

wgethttps://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.35-30/binary/tarball/percona-xtrabackup-8.0.35-30-Linux-x86 64.glibc2.17.tar.gz

tar  zxf  percona-xtrabackup-8.0.35-30-Linux-x86 64.glibc2.17.tar.gz
mv  percona-xtrabackup-8.0.35-30-Linux-x86 64.glibc2.17  /usr/local/xtrabackup

echo  'export  PATH=$PATH://usr/local/xtrabackup/bin'   >>  /etc/profile  source 

/etc/profile

(2)安装qpress

(3)完整备份与恢复

备份

bakdir="/backup/fullbackups/$(date ’+%F’)”

mkdir  -p  $backdir

(--backup 备份              --compress  压缩)

xtrabackup   --defaults-file=/etc/my.cnf  --user=bkpuser  --password=s3cret  --backup  --compress  --target-dir=$bakdir

恢复（恢复数据的时候要先关闭 MySQL，清理数据存储目录）

 解压备份数据：

xtrabackup  --defaults-file=/etc/my.cnf  --user=bkpuser --password=s3cret --decompress --target-dir=$bakdir

准备备份文件以供恢复：

xtrabackup  --prepare --target-dir=$bakdir

恢复数据：

xtrabackup  --defaults-file=/etc/my.cnf  --user=bkpuser  --password=s3cret --copy-back --target-dir=$bakdir

修改权限，替换成自己的数据存储目录：

chown-R mysql:mysql /var/lib/mysql

（5）增量备份与恢复

备份

fulldir="/backup/fullbackups/$ (date ’+%F’)"

incdir="/backup/incrementalbackups/$(date ’+%F’)”

mkdir -p $fulldir

mkdir -p $incdir

准备基础数据后进行全量备份：

xtrabackup  --defaults-file=/etc/my.cnf  --user=bkpuser  --password=s3cret  --target-dir=$fulldir --backup  --compress

插入新数据后进行增量备份：

xtrabackup
--defaults-file=/etc/my.cnf
--password=s3cret
--backup
--user=bkpuser
--compress
-target-dir=$incdir --incremental-basedir=$fulldir

在备份的目录里面会有xtrabackup_checkpoints文件

增量恢复

恢复数据的时候要先关闭 MySQL，清理数据存储目录

解压备份数据：

xtrabackup --defaults-file=/etc/my.cnf --user=root  --password=  --decompress  --target-dir=$fulldir

xtrabackup --defaults-file=/etc/my.cnf --user=root  --password=  --decompress --target-dir=$incdir

准备数据：

xtrabackup  --prepare  --apply-log-only  --target-dir=$fulldir

把增量备份的数据合并到完整备份里面:

xtrabackup  --prepare  --apply-log-only  --target-dir=$fulldir  --incremental-dir=$incdir

完整重放日志:

xtrabackup --prepare --target-dir=$fulldir

恢复数据:

xtrabackup  --defaults-file=/etc/my.cnf  --user=root  --password=  --copy-back  --target-dir=$fulldir

修改权限，替换成自己的数据存储目录:

chown -R mysql:mysql /usr/local/mysql/data

然后启动 MySQL 即可:

start  mysqld