Xtrabackup备份与恢复

Xtrabackup介绍

Percona-Xtrabackup是Percona公司为了实现增量备份而开发的一款主流备份工具，支持MySQL、Percona server和MariaDB，它包含了xtrabakup和innobakupe2个工具。

Xtrabackup优点

1. 备份速度快，物理备份可靠

2. 备份过程不会打断正在执行的事务（无需锁表）

3. 能够基于压缩等功能节约磁盘空间和流量

4. 自动备份校验

5. 还原速度快

6. 可以流传将备份传输到另外一台机器上

7. 在不增加服务器负载的情况备份数据

Xtrabackup安装

//从网上下载Xtrabackup后解压安装，这里是直接下载的rpm包
[root@cst opt]# ls
Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar
[root@cst opt]# tar xf Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar 
[root@cst opt]# ls
Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar
percona-xtrabackup-24-2.4.21-1.el8.x86_64.rpm
percona-xtrabackup-24-debuginfo-2.4.21-1.el8.x86_64.rpm
percona-xtrabackup-24-debugsource-2.4.21-1.el8.x86_64.rpm
percona-xtrabackup-test-24-2.4.21-1.el8.x86_64.rpm
percona-xtrabackup-test-24-debuginfo-2.4.21-1.el8.x86_64.rpm
[root@cst opt]# yum -y install percona*

xtrabackup增量备份

备份——语法：
innobackupex --user=DBUSER --password=DBUSERPASS --defaults-file=/etc/my.cnf /path/to/BACKUP-DIR/

恢复——语法：
innobackupex --apply-log /backups/20XX-XX-XX_XX-XX-XX/
innobackupex --copy-back --defaults-file=/etc/my.cnf  /backups/

增量备份

[root@cst backups]# innobackupex --user=root --password=123456 --host=127.0.0.1 /backups/   

[root@master ~]# mysql

mariadb> create database school;
Query OK, 1 row affected (0.02 sec)

mariadb> use school;
Database changed

mariadb> create table student(id int not null primary key auto_incerment,name varchar(100) not null,age tinyint);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into student(name,age) values('zhangsan',15),('lisi','16'),('wanger',15);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0 

MariaDB [school]> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   15 |
|  2 | lisi     |   16 |
|  3 | wanger   |   15 |
+----+----------+------+
3 rows in set (0.000 sec)

mariadb> quit;
Bye

//使用innobackupex进行增量备份 
[root@cst backups]# innobackupex --user=root --host=127.0.0.1 --incremental /backups/ --incremental-basedir=/backups/2021-01-03_02-48-21/ 

[root@master backups]# ll　
total 0 
drwxr-xr-x. 2 root root 6 Jan  3 03:11 2021-01-03_02-48-21
drwxr-xr-x. 2 root root 6 Jan  3 03:11 2021-01-03_03-06-45

[root@cst 2021-01-03_02-48-21]# cat xtrabackup_checkpoints #查看全量备份的xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 3127097
last_lsn = 3127097 compact = 0
recover_binlog_info = 0

[root@cst 2021-01-03_03-06-45]# cat xtrabackup_checkpoints 　　#查看增量备份的xtrabackup_checkpoints
backup_type = incremental　　
from_lsn = 3127097　　
to_lsn = 3158741　　  
last_lsn = 3158741　　 
compact = 0 
recover_binlog_info = 0

数据恢复

//模拟mysql故障，停止mysql
[root@cst ~]# systemctl stop mariadb.service
//删除数据目录中的所有数据
[root@cst ~]# rm -rf /usr/local/mysql/data/*

//合并全备数据目录，确保数据的一致性
[root@cst ~]# innobackupex --apply-log --redo-only /backups/2021-01-03_02-48-21/

//将增量备份数据合并到全备数据目录当中
[root@cst ~]# innobackupex --apply-log --redo-only /backups/2021-01-03_02-48-21/ --incremental-dir=/backups/2021-01-03_03-06-45/

[root@master ~]# cat /backups/2021-01-03_02-48-21/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0　　
to_lsn = 3158741　　
last_lsn = 3158741
compact = 0
recover_binlog_info = 0

//数据恢复
[root@cst ~]# innobackupex --copy-back /backups/2021-01-03_02-48-21/

[root@master ~]# ll /usr/local/mysql/data/
total 73245
-rw-r----- 1 root root 79691776 Jan 03 03:35 ibdata1
drwxr-x--- 2 root root       20 Jan 03 03:35 kim
drwxr-x--- 2 root root     4096 Jan 03 03:35 mysql
drwxr-x--- 2 root root     4096 Jan 03 03:35 performance_schema
drwxr-x--- 2 root root       20 Jan 03 03:35 repppp
drwxr-x--- 2 root root       56 Jan 03 03:35 student
drwxr-x--- 2 root root     4096 Jan 03 03:35 wordpress
-rw-r----- 1 root root        9 Jan 03 03:35 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root      554 Jan 03 03:35 xtrabackup_info

//更改数据的属主属组
[root@cst ~]# chown -R mysql.mysql /usr/local/mysql/data
[root@cst ~]# systemctl start mariadb.service

//查看数据是否恢复
[root@master ~]# mysql -e "show databases;"
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   15 |
|  2 | lisi     |   16 |
|  3 | wanger   |   15 |
+----+----------+------+
3 rows in set (0.000 sec)