使用xtrabackup进行mysql数据的备份与恢复
使用xtrabackup工具进行mysql数据的备份与恢复。
1. 整库备份与恢复
1.1 备份
备份命令如下:
mkdir -pv /backup/mysql3306/full/
innobackupex --defaults-file=/etc/my.cnf-3306 -S /home/my3306/mysql.sock --user=root --password=Root@1234 --slave-info /backup/mysql3306/full/
1.2 恢复(直接恢复到原目录下)
–copy-back参数,直接从备份文件恢复到原目录下,
需要先停mysql服务,移动原目录,再创建数据目录
如果是单机多实例的配置文件,配置都在/etc/my.cnf中,那么还需要把3306的那一段配置复制出来,稍作修改,变成单实例的配置,否则恢复会报错“Error: datadir must be specified.”
innobackupex --copy-back /backup/mysql3306/full/2022-07-14_15-28-17/
xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments:
220714 15:40:48 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
innobackupex version 2.4.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
Error: datadir must be specified.
因为innobackupex恢复的时候会读取配置文件中datadir的配置,而多实例设置,所有实例的配置都在一个文件中,所以要单独拆开。
正确的恢复方式
cp /etc/my.cnf /etc/my.cnf-3306
vim /etc/my.cnf-3306
innobackupex --defaults-file=/etc/my.cnf-3306 --copy-back /backup/mysql3306/full/2022-07-14_15-28-17/
chown -R mysql.mysql /home/my3306
systemctl start mysqld@3306
systemctl status mysqld@3306
2. 单库备份与恢复
参考:https://www.yisu.com/zixun/261113.html
看了一下相关的文档,介绍,发现由于备份恢复机制的问题,innobackup并不能进行单库的备份与恢复,只能进行整库的备份与恢复。如果想要进行单库的备份与恢复,只能依赖mysqldump了。
3. 增量备份与全量备份
参考:https://blog.csdn.net/qq_36183569/article/details/83024114
3.1 全量备份
参考1.1的命令
3.2 全量备份基础上进行增量备份
innobackupex --defaults-file=/etc/my.cnf-3306 -S /home/my3306/mysql.sock --user=root --password=Root@1234 --slave-info --incremental --incremental-basedir=/backup/mysql3306/full/2022-07-15_09-18-39/ /backup/mysql3306/increse/
3.3 数据恢复
-
- 全量备份恢复
innobackupex --apply-log --redo-only /backup/mysql3306/full/2022-07-15_09-18-39/
-
- 增量备份整合
innobackupex --apply-log --redo-only /backup/mysql3306/full/2022-07-15_09-18-39/ --incremental-dir=/backup/mysql3306/increse/2022-07-15_09-25-44/
-
- 恢复
systemctl stop mysqld@3306
mv /home/my3306{,-bak}
innobackupex --defaults-file=/etc/my.cnf-3306 --copy-back /backup/mysql3306/full/2022-07-15_09-18-39/
chown -R mysql.mysql /home/my3306
systemctl start mysqld@3306
systemctl status mysqld@3306
如果有多次增量备份,需要进行多次整合。
容量大小情况
4. 使用备份和binlog日志进行恢复
4.1 完全恢复
参考:https://blog.csdn.net/aa5181/article/details/115513420
4.1.1 备份
### 全备
innobackupex --defaults-file=/etc/my.cnf-3306 -S /home/my3306/mysql.sock --user=root --password=Root@1234 --slave-info /backup/mysql3306/full/
### 插入测试数据
create database userauth;
use userauth;
create table user(id int(3), name char(20), address char(32));
insert into user values(001,'zhangsan', 'beijing');
insert into user values(002,'lisi', 'zhejiang');
### 第一次增量
innobackupex --defaults-file=/etc/my.cnf-3306 -S /home/my3306/mysql.sock --user=root --password=Root@1234 --slave-info --incremental --incremental-basedir=/backup/mysql3306/full/2022-07-15_11-11-50 /backup/mysql3306/increse/
### 插入测试数据
create database userinfo;
use userinfo;
create table user(id int(3), name char(20), address char(32));
insert into user values(001,'zhangsan', 'beijing');
insert into user values(002,'lisi', 'zhejiang');
insert into user values(003,'wanger', 'shanghai');
insert into user values(004,'zhangxiaoming', 'henan');
### 第二次增量
innobackupex --defaults-file=/etc/my.cnf-3306 -S /home/my3306/mysql.sock --user=root --password=Root@1234 --slave-info --incremental-basedir=/backup/mysql3306/increse/2022-07-15_11-15-40/ --incremental /backup/mysql3306/increse/
### 插入测试数据
create database userinfo2;
use userinfo2;
create table user(id int(3), name char(20), address char(32));
insert into user values(005,'zhangsan', 'beijing');
insert into user values(006,'lisi', 'zhejiang');
insert into user values(007,'wanger', 'shanghai');
insert into user values(008,'zhangxiaoming', 'henan');
4.1.2 恢复
### 全量数据恢复
innobackupex --apply-log --redo-only /backup/mysql3306/full/2022-07-15_11-11-50/
### 第一次增量数据恢复
innobackupex --apply-log --redo-only /backup/mysql3306/full/2022-07-15_11-11-50/ --incremental-dir=/backup/mysql3306/increse/2022-07-15_11-15-40/
### 第二次增量数据恢复
innobackupex --apply-log --redo-only /backup/mysql3306/full/2022-07-15_11-11-50/ --incremental-dir=/backup/mysql3306/increse/2022-07-15_11-23-39/
### 停服恢复
systemctl stop mysqld@3306
mv /home/my3306{,-bak}
innobackupex --defaults-file=/etc/my.cnf-3306 --copy-back /backup/mysql3306/full/2022-07-15_11-11-50/
chown -R mysql.mysql /home/my3306
systemctl start mysqld@3306
systemctl status mysqld@3306
启动后看一下恢复情况
userinfo这个库是存在的,数据没有问题
再利用之前备份的binlog日志来进行恢复。
要恢复的时间点从innobackup的全备文件夹中获取
cat /backup/mysql3306/full/2022-07-15_11-11-50/xtrabackup_binlog_info
开始恢复之前可以先看一下binlog输出的情况
cd /home/my3306-bak
# 测试
mysqlbinlog mysql-201-3306-binlog.000001 --start-position=95456454 > /tmp/temp.sql
vim /tmp/temp.sql
binlog恢复
cd /home/my3306-bak
mysqlbinlog mysql-201-3306-binlog.000001 --start-position=95456454 | mysql -S /home/my3306/mysql.sock --user=root --password=Root@1234
当然,真实的故障情况并不会直接去恢复binlog日志,或者是不会直接恢复所有的binlog日志,如果有误操作,比如删库、删表、表更新等情况,如果用binlog直接全部恢复,那跟不恢复没什么区别了。所以基于时间点,或基于position的恢复才更符合实际情况。
4.2 基于时间点或binlog position的恢复
4.2.1 基于binlog position的恢复
备份
# 全备
innobackupex --defaults-file=/etc/my.cnf-3306 -S /home/my3306/mysql.sock --user=root --password=Root@1234 --slave-info /backup/mysql3306/full/
# 插入数据
create database userinfo;
use userinfo;
create table user(id int(3), name char(20), address char(32));
insert into user values(001,'zhangsan', 'beijing');
insert into user values(002,'lisi', 'zhejiang');
insert into user values(003,'wanger', 'shanghai');
insert into user values(004,'zhangxiaoming', 'henan');
# 插入数据
create database userinfo2;
use userinfo2;
create table user(id int(3), name char(20), address char(32));
insert into user values(005,'zhangsan', 'beijing');
insert into user values(006,'lisi', 'zhejiang');
insert into user values(007,'wanger', 'shanghai');
insert into user values(008,'zhangxiaoming', 'henan');
模拟误操作
# 查询数据
show databases;
use userinfo2
select * from user;
# 删除库
drop database userinfo;
use userinfo2;
insert into user values(009,'libai', 'luoyang');
innobackup恢复
innobackupex --apply-log --redo-only /backup/mysql3306/full/2022-07-15_16-26-59
systemctl stop mysqld@3306
mv /home/my3306{,-bak}
innobackupex --defaults-file=/etc/my.cnf-3306 --copy-back /backup/mysql3306/full/2022-07-15_16-26-59
chown -R mysql.mysql /home/my3306
systemctl start mysqld@3306
systemctl status mysqld@3306
确认情况
重连之后发现userinfo,userinfo2这两个全备之后的库都没了
利用之前的mysqlbinlog确认情况
cd /home/my3306-bak
mysqlbinlog mysql-201-3306-binlog.000001 --start-position=95456647 > /tmp/1.sql
vim /tmp/1.sql
找到删除语句的执行时间点
执行恢复
mysqlbinlog mysql-201-3306-binlog.000001 --start-position=95456647 --stop-position=95459769 | mysql -u root -p'Root@1234' -S /home/my3306/mysql.sock
查看执行的结果
但是后面新增的一条数据还是没有加进来,尝试恢复试试
这两个点
尝试恢复
# mysqlbinlog mysql-201-3306-binlog.000001 --start-position=95460015 --stop-position=95460127 | mysql -u root -p'Root@1234' -S /home/my3306/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1782 (HY000) at line 17: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
由于gitd设置的原因,恢复失败,需要临时设置一下gitd参数
参考:https://blog.csdn.net/weixin_40524659/article/details/105902881
mysql> set global gtid_mode='on_permissive';
mysql> show global variables like 'gtid_mode';
尝试恢复
# mysqlbinlog mysql-201-3306-binlog.000001 --start-position=95460015 --stop-position=95460127 | mysql -u root -p'Root@1234' -S /home/my3306/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
这一次不报错了,确认插入成功。
4.2.2 基于时间点的恢复
mysqlbinlog mysql-201-3306-binlog.000001 --start-datetime='2022-07-15 16:32:55' --stop-datetime='2022-07-15 16:34:46' | mysql -u root -p'Root@1234' -S /home/my3306/mysql.sock
4.2.3 混合类型恢复
mysqlbinlog mysql-201-3306-binlog.000001 --start-position=95456647 --stop-datetime='2022-07-15 16:34:46' | mysql -u root -p'Root@1234' -S /home/my3306/mysql.sock
搞定!