尉氏xtrabackup安装以及xtrabackup备份以及恢复
准备两天机子:
lb01:10.0.0.4
lb02:10.0.0.5
第一部分:全量备份和全量恢复
1.下载xtrabackup软件包
#安装依赖
[root@lb01 ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
#下载Xtrabackup包
[root@localhost packages]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
2.安装xtrabackup软件包
[root@lb01 ~]# yum install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm -y
3.准备测试数据
[root@localhost ~]# mysql -u root -p123
mysql> create database backup;
mysql> use backup
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
4、创建全备文件夹
[root@lb01 ~]# mkdir /backup
5 全备(实质上只是ibd线程拷贝出的数据,而redo线程的数据后面弄)
[root@lb01 ~]# innobackupex --defalult-file=/etc/my.cnf --user=root --password=123 --no-timestamp /backup/full
- ll /backup/full/
[root@lb01 ~]# cat /backup/2021-03-12_06-21-15/xtrabackup_info
uuid = 18f04393-82b8-11eb-8b9f-000c299fc436
name =
tool_name = innobackupex
tool_command = --user=root --password=... /backup/
tool_version = 2.4.4
ibbackup_version = 2.4.4
server_version = 5.6.51-log
start_time = 2021-03-12 06:21:15
end_time = 2021-03-12 06:21:16
lock_time = 0
binlog_pos = filename 'mysql-bin.000006', position '545'
innodb_from_lsn = 0
innodb_to_lsn = 1846924
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
root@lb01 ~]# cat /backup/2021-03-12_06-21-15/xtrabackup_binlog_info
mysql-bin.000006 545
#检查点
root@lb01 ~]# cat /backup/2021-03-12_06-21-15/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1846924
last_lsn = 1846924
compact = 0
recover_binlog_info = 0
7.删除backup库接着停止数据库
mysql> drop database backup;
Query OK, 1 row affected (0.01 sec)
[root@lb01 ~]# systemctl stop mysqld
8.修改数据库配置文件数据目录(实质上就是保护之前文件夹下的东西)
[root@lb01 ~]#vim /etc/my.cnf
。。。。
datadir=/backup/data/
。。。。
[root@lb01 ~]# mkdir /backup/data/
9.归档数据,实质就是把ibd和redo两个线程的数据合并了
[root@lb01 2021-03-12_06-21-15]# innobackupex --user=root --password=123 --apply-log /backup/full
10.恢复数据库并重启当时失败了因为/back/data/的用户是root不是mysql改了
[root@lb01 2021-03-12_06-21-15]# innobackupex --user=root --password=123 --copy-back /backup/full
[root@lb01 ~]# chown -R mysql.mysql /backup/
[root@lb01 ~]# systemctl restart mysqld
11.重启数据库并检验删掉的库和表回来没有
systemctl start mysqld
mysql -u root -p123
mysql> show databases;
mysql> use backup
mysql> show tables;
mysql> select * from t1;
12.总结全库备份与恢复三步曲:
innobackupex全量备份,并指定备份目录路径;
在恢复前,需要使用--apply-log参数先进行合并数据文件,确保数据的一致性要求;
恢复时,直接使用--copy-back参数进行恢复,需要注意的是,在my.cnf中要指定数据文件目录的路径。
第二部分:增量备份和增量恢复
理论:
- 基于上一次全备进行增量
- 增量备份无法单独恢复,必须基于全备进行恢复
- 所有增量必须要按顺序合并到全备当中
首先先将数据库的东西进行全备(xtrabackup增备是建立在全备的基础上的 ,那我现在需要把全备做出来)
[root@lb01 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full
1、准备模拟库
[root@lb01 ~]# mysql -uroot -p123456
mysql> create database xtra;
mysql> use xtra;
mysql> create table test1(id int);
mysql> insert into test1 values(1),(2),(3);
mysql> create table test2(id int);
mysql> insert into test2 values(11),(22),(33);
mysql> commit;
mysql> show tables;
+----------------+
| Tables_in_xtra |
+----------------+
| test1 |
| test2 |
+----------------+
2 rows in set (0.00 sec)
2.第一次增量备份
[root@lb01 ~]# innobackupex --user=root --password=123 --no-timestamp --increment --increment-basedir=/backup/full /backup/xtra1
。。
。。
xtrabackup: Transaction log of lsn (1888211) to (1888211) was copied.
210315 00:40:05 completed OK!
3.检查第一次增量备份from_lsn = 1842251数值是否等于全备的lsn
[root@lb01 ~]# cat /backup/full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1842251
last_lsn = 1842251
compact = 0
recover_binlog_info = 0
[root@lb01 ~]# cat /backup/xtra1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1842251 #这个lsn必须等于基于全备的lsn数值
to_lsn = 1857063
last_lsn = 1857063
compact = 0
recover_binlog_info = 0
4.准备第二次模拟数据
mysql> create database xtra2;
mysql> use xtra2
mysql> create table test3(id int);
mysql> insert into test3 values(21),(22),(23);
mysql> create table test4(id int);
mysql> insert into test4 values(31),(32),(33);
mysql> commit;
5.开始第二次增量备份:注意第二次是针对第一次增量备份而言的,所以需要指定第一次增量备份的目录
[root@lb01 ~]# innobackupex --user=root --password=123 --incremental --incremental-basedir=/backup/xtra1/ /backup/xtra2
..
..
xtrabackup: Transaction log of lsn (1872670) to (1872670) was copied.
210314 17:36:59 completed OK!
6.第二次检查两个检查点是否相同
[root@lb01 ~]# cat /backup/xtra1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1842251
to_lsn = 1857063
last_lsn = 1857063
compact = 0
recover_binlog_info = 0
[root@lb01 ~]# cat /backup/xtra2/2021-03-14_17-36-57/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1857063
to_lsn = 1872670
last_lsn = 1872670
compact = 0
recover_binlog_info = 0
第三部分:增量恢复流程
1.恢复思路
1)full+xtra1+xtra2 #整合三个文件
2)需要将xtra1和xtra2按顺序合并到full中,分步骤进行–apply-log(不是每一步都要模拟CSR)
3)删除数据库
mysql> drop database xtra2;
1.合并全备数据,只做redo不做undo
[root@lb01 ~]# innobackupex --apply-log --redo-only /backup/full
[root@lb01 ~]# cat /backup/full/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 1842251
last_lsn = 1842251
compact = 0
recover_binlog_info = 0
2.合并到第一次增量数据到全备数据中,只做redo不做undo
[root@lb01 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/xtra1 /backup/full
3.合并第二次(也是我这次实验里最后一个)增量数据到全备数据中,redo,undo全做(–redo-only代表只做这个,不写它,代表redo,undo都做)
[root@lb01 ~]# innobackupex --apply-log --incremental-dir=/backup/xtra2/2021-03-14_17-36-57/ /backup/full (注意:因为前面我们没有去掉时间戳,所以出来个时间段的问题)
[root@lb01 ~]# cat /backup/full/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 1872670
last_lsn = 1872670
compact = 0
recover_binlog_info = 0
4.关掉数据库
[root@lb01 ~]# systemctl stop mysqld
5.去修改mysql的配置文件,数据存放目录
[root@lb01 ~]# vim /etc/my.cnf
datadir=/backup/data/
socket=/var/lib/mysql/mysql.sock
server_id=2
"/etc/my.cnf" 33L, 1095C
6.授予这个data目录属猪数组
[root@lb01 ~]# chown -R mysql.mysql /backup/data/
7.恢复数据 (就是前面写的所有合并的现在进行恢复就可以了)
[root@lb01 ~]# innobackupex --user=root --password=123 --copy-back /backup/full
8.重启数据库
[root@lb01 ~]# systemctl restart mysqld
9.查看数据库都恢复了OK啦
mysql> show tables;
+----------------+
| Tables_in_xtra |
+----------------+
| test1 |
| test2 |
+----------------+
2 rows in set (0.00 sec)
mysql> use xtra2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------+
| Tables_in_xtra2 |
+-----------------+
| test3 |
| test4 |
+-----------------+
2 rows in set (0.00 sec)