Xtrabackup备份与恢复

Xtrabackup介绍

　　MySQL冷备、mysqldump、MySQL热拷贝都无法实现对数据库进行增量备份。在实际生产环境中增量备份是非常实用的，如果数据大于50G或100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周实用完整备份，周一到周六实用增量备份。而Percona-Xtrabackup就是为了实现增量备份而出现的一款主流备份工具，xtrabakackup有2个工具，分别是xtrabakup、innobakupe。

　　Percona-xtrabackup是 Percona公司开发的一个用于MySQL数据库物理热备的备份工具，支持MySQL、Percona server和MariaDB，开源免费，是目前较为受欢迎的主流备份工具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表，而不能备份MyISAM数据表。

Xtrabackup优点

（1）备份速度快，物理备份可靠

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

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

（4）自动备份校验

（5）还原速度快

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

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

xtrabackup安装

准备工作，先安装mysql（如何安装，请看"mysql源码安装"）


//下载xtrabackup并安装
[root@localhost ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/8/x86_64/Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar
[root@localhost ~]# ls
anaconda-ks.cfg  Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar
[root@localhost ~]# tar xf Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar 
[root@localhost ~]# ls
anaconda-ks.cfg
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@localhost ~]# yum -y install percona*

xtrabackup全量备份与恢复

总结全库备份与恢复三步曲：

a.　innobackupex全量备份，并指定备份目录路径；

b.　在恢复前，需要使用--apply-log参数先进行合并数据文件，确保数据的一致性要求；

c.　恢复时，直接使用--copy-back参数进行恢复，需要注意的是，在my.cnf中要指定数据文件目录的路径。

备份——语法：
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/20XX-XX-XX_XX-XX-XX/

全量备份

//授权root用户在所有位置上远程登录访问root数据库
[root@localhost ~]# mysql -uroot -p123456
mysql> grant all on *.* to 'root'@'%' identified by '123456';
mysql> flush privileges;
mysql> quit
Bye

[root@localhost ~]# innobackupex --user=root --password=123456 --host=127.0.0.1 /backups/
201229 19:23:54 completed OK!
[root@localhost ~]# ll /backups/
total 0
drwxr-x---. 6 root root 207 Dec 29 19:23 2020-12-29_19-23-50
[root@localhost ~]# ll /backups/2020-12-29_19-23-50/
total 12336
-rw-r-----. 1 root root      487 Dec 29 19:23 backup-my.cnf
-rw-r-----. 1 root root      388 Dec 29 19:23 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Dec 29 19:23 ibdata1
drwxr-x---. 2 root root     4096 Dec 29 19:23 mysql
drwxr-x---. 2 root root     8192 Dec 29 19:23 performance_schema
drwxr-x---. 2 root root       58 Dec 29 19:23 school
drwxr-x---. 2 root root     8192 Dec 29 19:23 sys
-rw-r-----. 1 root root      135 Dec 29 19:23 xtrabackup_checkpoints
-rw-r-----. 1 root root      437 Dec 29 19:23 xtrabackup_info
-rw-r-----. 1 root root     2560 Dec 29 19:23 xtrabackup_logfile

恢复

[root@localhost ~]# service mysqld stop
Shutting down MySQL.. SUCCESS! 
//合并数据，使数据文件处于一致性的状态
[root@localhost ~]# innobackupex --apply-log /backups/2020-12-29_19-23-50/
201229 19:29:05 completed OK!

//删除原有的数据
[root@localhost ~]# rm -rf /opt/data/
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
datadir=/opt/data

//数据恢复
[root@localhost ~]# innobackupex --copy-back /backups/2020-12-29_19-23-50/
201229 19:34:26 completed OK!

//修改属主属组
[root@localhost ~]# ll /opt/data/
total 122920
-rw-r-----. 1 root root      388 Dec 29 19:34 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Dec 29 19:34 ibdata1
-rw-r-----. 1 root root 50331648 Dec 29 19:34 ib_logfile0
-rw-r-----. 1 root root 50331648 Dec 29 19:34 ib_logfile1
-rw-r-----. 1 root root 12582912 Dec 29 19:34 ibtmp1
drwxr-x---. 2 root root     4096 Dec 29 19:34 mysql
drwxr-x---. 2 root root     8192 Dec 29 19:34 performance_schema
drwxr-x---. 2 root root       58 Dec 29 19:34 school
drwxr-x---. 2 root root     8192 Dec 29 19:34 sys
-rw-r-----. 1 root root      437 Dec 29 19:34 xtrabackup_info
-rw-r-----. 1 root root        1 Dec 29 19:34 xtrabackup_master_key_id
[root@localhost ~]# chown -R mysql.mysql /opt/data/

//启动mysql
[root@localhost ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
 SUCCESS! 

//查看数据，是否恢复
[root@localhost ~]# mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

xtrabackup增量备份与恢复

增量备份

//全备数据
[root@localhost ~]# innobackupex --user=root --password=123456 --host=127.0.0.1 /backups/
201229 19:50:12 completed OK!
mysql> create database student;
Query OK, 1 row affected (0.00 sec)

//创建student库并创建testtb表插入若干数据
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 15
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> use student;
Database changed
mysql> create table testtb(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into testtb values(1),(10),(99);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from testtb;
+------+
| id   |
+------+
|    1 |
|   10 |
|   99 |
+------+
3 rows in set (0.00 sec)
mysql> quit;
Bye

//使用innobackupex进行增量备份
[root@localhost ~]# innobackupex --user=root --password=123456 --host=127.0.0.1 --incremental /backups/ --incremental-basedir=/backups/2020-12-29_19-50-10/
xtrabackup: Transaction log of lsn (4247071) to (4247080) was copied.
201229 19:57:26 completed OK!
[root@localhost ~]# ll /backups/
total 0
drwxr-x---. 6 root root 207 Dec 29 19:50 2020-12-29_19-50-10  //全量备份数据目录
drwxr-x---. 7 root root 248 Dec 29 19:57 2020-12-29_19-57-24  //增量备份数据目录

//查看全量备份的xtrabackup_checkpoints
[root@localhost ~]# cd /backups/2020-12-29_19-50-10/
[root@localhost 2020-12-29_19-50-10]# cat xtrabackup_checkpoints 
backup_type = full-backuped		//备份类型为全量备份
from_lsn = 0
to_lsn = 4240955
last_lsn = 4240964
compact = 0
recover_binlog_info = 0
flushed_lsn = 4240964

//查看增量备份的xtrabackup_checkpoints
[root@localhost 2020-12-29_19-50-10]# cd /backups/2020-12-29_19-57-24/
[root@localhost 2020-12-29_19-57-24]# cat xtrabackup_checkpoints 
backup_type = incremental	//备份类型为增量备份
from_lsn = 4240955
to_lsn = 4247071
last_lsn = 4247080
compact = 0
recover_binlog_info = 0
flushed_lsn = 4247080

增量备份后数据恢复

//模拟mysql故障，删除数据目录所有数据
[root@localhost ~]# service mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@localhost ~]# rm -rf /opt/data/*

//合并全备数据目录，确保数据的一致性
[root@localhost ~]# innobackupex --apply-log --redo-only /backups/2020-12-29_19-50-10/
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 4240973
InnoDB: Number of pools: 1
201229 21:15:41 completed OK!

//将增量备份数据合并到全备数据目录当中
[root@localhost ~]# innobackupex --apply-log --redo-only /backups/2020-12-29_19-50-10/ --incremental-dir=/backups/2020-12-29_19-57-24
201229 21:21:24 [00]        ...done
201229 21:21:24 completed OK!

//查看类型
[root@localhost ~]# cat /backups/2020-12-29_19-50-10/xtrabackup_checkpoints 
backup_type = log-applied	//查看到数据备份类型是增加
from_lsn = 0
to_lsn = 4247071
last_lsn = 4247080
compact = 0
recover_binlog_info = 0
flushed_lsn = 4247080

//恢复数据
[root@localhost ~]# innobackupex --copy-back /backups/2020-12-29_19-50-10/
201229 21:23:13 [01]        ...done
201229 21:23:13 completed OK!

//更改数据的属主属组
[root@localhost ~]# ll /opt/data/
total 110632
-rw-r-----. 1 root root      388 Dec 29 21:23 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Dec 29 21:23 ibdata1
-rw-r-----. 1 root root 50331648 Dec 29 21:23 ib_logfile0
-rw-r-----. 1 root root 50331648 Dec 29 21:23 ib_logfile1
drwxr-x---. 2 root root     4096 Dec 29 21:23 mysql
drwxr-x---. 2 root root     8192 Dec 29 21:23 performance_schema
drwxr-x---. 2 root root       58 Dec 29 21:23 school
drwxr-x---. 2 root root       56 Dec 29 21:23 student
drwxr-x---. 2 root root     8192 Dec 29 21:23 sys
-rw-r-----. 1 root root      509 Dec 29 21:23 xtrabackup_info
-rw-r-----. 1 root root        1 Dec 29 21:23 xtrabackup_master_key_id
[root@localhost ~]# chown -R mysql.mysql /opt/data/

//启动mysql
[root@localhost ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
. SUCCESS! 

//查看数据是否恢复
[root@localhost ~]# mysql -uroot -p123456 -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| student            |
| sys                |
+--------------------+

总结：

（1）增量备份需要使用参数--incremental指定需要备份到哪个目录，使用incremental-dir指定全备目录；

（2）进行数据备份时，需要使用参数--apply-log redo-only先合并全备数据目录数据，确保全备数据目录数据的一致性；