Mysql 全量增量备份教程及脚本（一）

Mysql 全量增量备份脚本记录

使用XtraBackup程序进行备份

centos8.0 与 rocky 9.0 安装XtraBackup

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

yum install -y percona-xtrabackup-80.x86_64

安装完成之后，如果数据库做了读写分离，以下操作就在从库操作，如果是单机则无所谓。

登录 mysql 数据库

创建备份用户
create user 'bkupuser'@'localhost' identified by '123';
grant backup_admin,process,reload,lock tables,replication client on *.* to 'bkupuser'@'localhost';
grant select on *.* to 'bkupuser'@'localhost';
flush privileges;
show grants for 'bkupuser'@'localhost';

准备工作:

备份目录创建：
### 全量目录
mkdir -p /data/backup/full  
### 第一次增量目录
mkdir -p /data/backup/inc1 
### 第二次增量目录
mkdir -p /data/backup/inc2
以此类推：也可以使用脚本创建以日期时间为目录等都可以，无规范。

接下来开始备份

一、先进行完整备份 （完整目录什么也没有，每次增量备份需要添加一些数据，以呈现效果）

xtrabackup --user=bkupuser --password=123 --backup --target-dir=/data/backup/full  

参数详解：
–user ： 数据库用户
–password: 数据库密码
–backup: 创建备份
–target-dir ：指定备份文件存放目录

执行结果为：

只要执行备份，或者恢复命令，日志最终结果都是OK，如有报错，会提示报错信息

添加测试数据

mysql -u root -p123
create database test;

添加了测试数据之后，在进行增量备份。

二、增量备份1，在全备的基础上

xtrabackup --user=bkupuser --password=123 --backup  \ 
 --incremental-basedir=/data/backup/full \ 
 --target-dir=/data/backup/inc1 

参数详解：
–incremental-basedir：
该选项表示接受了一个字符串参数指定含有full backup的目录为增量备份的base目录
说白了就是 指定上次完整备份或者增量备份文件的位置

添加测试数据

建表：
create table test.tb_name (
`id` int(11) NOT NULL,
`name` varchar(12)  COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
);
插入数据：
INSERT INTO test.tb_name (id,name) values (1,'yuhl');
INSERT INTO test.tb_name (id,name) values (2,'wang');
INSERT INTO test.tb_name (id,name) values (3,'er');
INSERT INTO test.tb_name (id,name) values (4,'ma');
INSERT INTO test.tb_name (id,name) values (5,'zi');
INSERT INTO test.tb_name (id,name) values (6,'zhangsan');

三、增量备份2，在增量备份1的基础上

xtrabackup --user=bkupuser --password=123 --backup \  
--incremental-basedir=/data/backup/inc1 \   
--target-dir=/data/backup/inc2

备份完成之后，测试效果。

场景：
删除数据库表内容，不小心误操作把test数据库删除了，里面数据什么也没了，需要找回。

开始恢复操作如下：

步骤：将增量备份1、增量备份2…合并到完整备份，加到一起出来一个新的完整备份，将新的完整备份恢复到数据库空目录

预备完整备份

xtrabackup --user=bkupuser -p'2023'  \ 
 --prepare --apply-log-only \  
 --target-dir=/data/backup/full 

参数注释：
–prepare ：
一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据 文件仍处理不一致状态，在准备增量备份时，必须跳过未提交事务的回滚，因为备份时未提交的事务可能正在进行中，并且很可能会在下一次增量备份中提交
–apply-log-only:
在备份还在进行准备工作时，跳过消除（undo）阶段，只执行重做（redo）阶段，从而进行增量备份，通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。

######### 以上参数注释是网上查出来的 作者也不太理解,作者自己理解的是：如果要恢复数据，这两个参数要一起使用，直到最后一条增量无需增加 –apply-log-only，如果有更好的理解，欢迎留言

恢复增量备份1到全量数据

xtrabackup --user=bkupuser -p'2023' \   
--prepare --apply-log-only  \  
--target-dir=/data/backup/full  \ 
--incremental-dir=/data/backup/inc1

参数注释：
–incremental-dir 指向增量备份目录

恢复增量备份2到全量数据,

-######

#####重点：注意我这里没有加–apply-log-only 参数

xtrabackup --user=bkupuser -p'2023' \ 
 --prepare  \  
 --target-dir=/data/backup/full  \ 
 --incremental-dir=/data/backup/inc2

因为我只有一个全量，两个增量备份，上面提了，最后一个增量备份加到全量中时，不要加 --apply-log-only 。原因是：使最后一次备份数据中未完成的事务进行回滚，让数据统一。

如果最后一次合并添加了 --apply-log-only 参数

解决方法如下：

# 直接回滚操作，回滚未提交的事务： 回滚到第一次就可以了 
# 不要加 --apply-log-only 参数
xtrabackup --user=bkupuser -p'2023' --prepare  --target-dir=/data/backup/full 

回滚到第一次事务，不影响恢复数据，接着如下操作即可

准备恢复数据

1.停止数据库
./support-files/mysql.server stop
2.将原目录下的数据迁移干净
mkdir /data/mysqlbak
rm -rf /data/mysqlbak/*
mv /data/mysql/*  /data/mysqlbak
3.恢复备份
xtrabackup --user=bkupuser -p'2023' --copy-back  --target-dir=/data/backup/full/
4.赋权
chown -R mysql.mysql  /data/mysql/
5.启动数据库
./support-files/mysql.server start
6.查看数据是否恢复
mysql -uroot -p

数据已经恢复，如上及恢复完成。

如果以上备份数据已经做过恢复操作后，下次备份需重新全量备份。

如果不想重新备份，也可以接着备份2 去备份，操作如下

#增量备份3，在增量备份2的基础上
xtrabackup --user=bkupuser --password=2023 \ 
 --backup --incremental-basedir=/data/backup/inc2 \ 
 --target-dir=/data/backup/inc3
 
#增量备份4，在增量备份3的基础上
xtrabackup --user=bkupuser --password=2023  \  
 --backup --incremental-basedir=/data/backup/inc3 \ 
 --target-dir=/data/backup/inc4

恢复 同上一样

步骤：将增量备份3、增量备份4…合并到完整备份，加到一起出来一个新的完整备份，将新的完整备份恢复到数据库空目录

# 先预备完整备份
xtrabackup --user=bkupuser -p'2023' \ 
 --prepare --apply-log-only \ 
 --target-dir=/data/backup/full
# 恢复增量备份3到全量数据（可以略过1、2备份，因为全量中已经存放了）
xtrabackup --user=bkupuser -p'2023' \  
--prepare --apply-log-only \ 
--target-dir=/data/backup/full  \ 
--incremental-dir=/data/backup/inc3
# 恢复增量备份4到全量数据
xtrabackup --user=bkupuser -p'2023' \ 
 --prepare  \ 
 --target-dir=/data/backup/full  \  
 --incremental-dir=/data/backup/inc4

接着准备恢复数据 同上1 - 6 步一样即可

1.停止数据库
./support-files/mysql.server stop
2.将原目录下的数据迁移干净
mkdir /data/mysqlbak
rm -rf /data/mysqlbak/*
mv /data/mysql/*  /data/mysqlbak
3.恢复备份
xtrabackup --user=bkupuser -p'2023' --copy-back  --target-dir=/data/backup/full/
4.赋权
chown -R mysql.mysql  /data/mysql/
5.启动数据库
./support-files/mysql.server start
6.查看数据是否恢复
mysql -uroot -p

脚本内容在下一章，感谢关注。