Xtrabackup备份与恢复

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优点

• 备份速度快，物理备份可靠
• 备份过程不会打断正在执行的事物（无需锁表）
• 能够给予压缩等功能节约磁盘空间和流量
• 自动备份校验
• 还原速度快
• 可以流传将备份传输到另外一台机器上
• 在不增加服务器负载的情况下备份数据

Xtrabackup工具安装

在这之前需要安装配置mysql，详情如下：

https://www.cnblogs.com/leixixi/p/14204529.html

下载地址：

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

使用wget下载

[root@localhost ~]# yum -y install wget
[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 ~]# 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
 
//安装percona*的所有包
yum -y install percona*

//可以看到命令
[root@localhost ~]# which innobackupex
/usr/bin/innobackupex

全库备份与恢复三步曲：

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

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

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

xtrabackup全量备份与恢复

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

恢复：
innobackupex --apply-log /backups/2018-07-30_11-04-55/
innobackupex --copy-back --defaults-file=/etc/my.cnf  /backups/2018-07-30_11-04-55/

在这之前需要添加一个root访问所有数据的权限

mysql> GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

做一个全量备份

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

xtrabackup: Transaction log of lsn (2719904) to (2719913) was copied.
210104 12:26:03 completed OK!  //这一行出现说明全量备份成功
[root@localhost ~]# ll /backups/
total 0
drwxr-x---. 5 root root 193 Jan  4 12:26 2021-01-04_12-25-58     //全量备份文件
[root@localhost ~]# ll /backups/2021-01-04_12-25-58/    
total 12336
-rw-r-----. 1 root root      487 Jan  4 12:26 backup-my.cnf
-rw-r-----. 1 root root      431 Jan  4 12:26 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Jan  4 12:26 ibdata1
drwxr-x---. 2 root root     4096 Jan  4 12:26 mysql
drwxr-x---. 2 root root     8192 Jan  4 12:26 performance_schema
drwxr-x---. 2 root root     8192 Jan  4 12:26 sys
-rw-r-----. 1 root root      135 Jan  4 12:26 xtrabackup_checkpoints
-rw-r-----. 1 root root      437 Jan  4 12:26 xtrabackup_info
-rw-r-----. 1 root root     2560 Jan  4 12:26 xtrabackup_logfile
[root@localhost ~]#

恢复数据

//先把mysql停掉
[root@localhost ~]# service mysqld stop
Shutting down MySQL.. SUCCESS! 

//合并数据，保持文件一致的状态，避免报错
[root@localhost ~]# innobackupex --apply-log /backups/2021-01-04_12-25-58/

InnoDB: Shutdown completed; log sequence number 2720296
210104 12:33:08 completed OK!

//删除原有数据
[root@localhost ~]# rm -rf /opt/data/


//编辑my.cnf文件
[root@localhost ~]# vim /etc/my.cnf

[mysqld]    
basedir = /usr/local/mysql
datadir = /opt/data     //加上这一行，要在mysqld下
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve


//数据恢复
[root@localhost ~]# innobackupex --copy-back /backups/2021-01-04_12-25-58/

210104 12:35:51 [01] Copying ./ibtmp1 to /opt/data/ibtmp1
210104 12:35:51 [01]        ...done
210104 12:35:51 completed OK!    //出现这个说明成功

//修改属组属主
[root@localhost ~]# ll /opt/data/
total 122920
-rw-r-----. 1 root root      431 Jan  4 12:35 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Jan  4 12:35 ibdata1
-rw-r-----. 1 root root 50331648 Jan  4 12:35 ib_logfile0
-rw-r-----. 1 root root 50331648 Jan  4 12:35 ib_logfile1
-rw-r-----. 1 root root 12582912 Jan  4 12:35 ibtmp1
drwxr-x---. 2 root root     4096 Jan  4 12:35 mysql
drwxr-x---. 2 root root     8192 Jan  4 12:35 performance_schema
drwxr-x---. 2 root root     8192 Jan  4 12:35 sys
-rw-r-----. 1 root root      437 Jan  4 12:35 xtrabackup_info
-rw-r-----. 1 root root        1 Jan  4 12:35 xtrabackup_master_key_id
[root@localhost ~]# chown -R mysql.mysql /opt/data/
[root@localhost ~]# ll /opt/data/
total 122920
-rw-r-----. 1 mysql mysql      431 Jan  4 12:35 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jan  4 12:35 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jan  4 12:35 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jan  4 12:35 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jan  4 12:35 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Jan  4 12:35 mysql
drwxr-x---. 2 mysql mysql     8192 Jan  4 12:35 performance_schema
drwxr-x---. 2 mysql mysql     8192 Jan  4 12:35 sys
-rw-r-----. 1 mysql mysql      437 Jan  4 12:35 xtrabackup_info
-rw-r-----. 1 mysql mysql        1 Jan  4 12:35 xtrabackup_master_key_id


//启动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 |
| sys                |
+--------------------+

验证完成，数据已经恢复

xtrabackup增量备份与恢复

增量备份

//全备数据
[root@localhost ~]# innobackupex --user=root --password=123456 --host=127.0.0.1 /backups/

210104 12:41:06 [00]        ...done
xtrabackup: Transaction log of lsn (2720315) to (2720324) was copied.
210104 12:41:06 completed OK!   //显示这个说明成功了

//创建school库加入student表插入数据若干
[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 10
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> create database school;
Query OK, 1 row affected (0.00 sec)

mysql> use school;
Database changed
mysql> create table student(name varchar(25),age tinyint);
Query OK, 0 rows affected (0.01 sec)

mysql> insert student(name,age)values('tom',15),('jerry',20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+-------+------+
| name  | age  |
+-------+------+
| tom   |   15 |
| jerry |   20 |
+-------+------+
2 rows in set (0.00 sec)

mysql> quit
Bye


//开始增量备份
[root@localhost ~]# innobackupex --user=root --password=123456 --host=127.0.0.1 --incremental /backups/ --incremental-basedir=/backups/2021-01-04_12-41-04/

210104 12:46:01 [00] Writing /backups/2021-01-04_12-45-59/xtrabackup_info
210104 12:46:01 [00]        ...done
xtrabackup: Transaction log of lsn (2727991) to (2728000) was copied.
210104 12:46:01 completed OK!

[root@localhost ~]# ll /backups/
total 0
drwxr-x---. 5 root root 277 Jan  4 12:33 2021-01-04_12-25-58	//第一次全量备份的数据目录
drwxr-x---. 5 root root 193 Jan  4 12:41 2021-01-04_12-41-04    //第二次插入数据后的全量备份数据目录
drwxr-x---. 6 root root 233 Jan  4 12:46 2021-01-04_12-45-59	//第一次增量备份的数据目录

//查看全备的xtrabackup_checkpoints文件
[root@localhost ~]# cat /backups/2021-01-04_12-41-04/xtrabackup_checkpoints 
backup_type = full-backuped   //全量备份类型
from_lsn = 0
to_lsn = 2720315
last_lsn = 2720324
compact = 0
recover_binlog_info = 0
flushed_lsn = 2720324
//查看增备的xtrabackup_checkpoints
[root@localhost ~]# cat /backups/2021-01-04_12-45-59/xtrabackup_checkpoints 
backup_type = incremental     //增量备份的类型
from_lsn = 2720315
to_lsn = 2727991
last_lsn = 2728000
compact = 0
recover_binlog_info = 0
flushed_lsn = 2728000

增量备份后数据恢复

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


//合并全备数据目录，确保数据的一致性
[root@localhost ~]# innobackupex --apply-log --redo-only /backups/2021-01-04_12-41-04/

InnoDB: Shutdown completed; log sequence number 2720333
InnoDB: Number of pools: 1
210104 13:02:35 completed OK!

//将增量备份数据合并到全备数据目录当中
[root@localhost ~]# innobackupex --apply-log --redo-only /backups/2021-01-04_12-41-04/ --incremental-dir=/backups/2021-01-04_12-45-59/

210104 13:05:06 [00] Copying /backups/2021-01-04_12-45-59//xtrabackup_info to ./xtrabackup_info
210104 13:05:06 [00]        ...done
210104 13:05:06 completed OK!

//查看类型
[root@localhost ~]# cat /backups/2021-01-04_12-45-59/xtrabackup_checkpoints 
backup_type = incremental   //增量备份类型
from_lsn = 2720315
to_lsn = 2727991
last_lsn = 2728000
compact = 0
recover_binlog_info = 0
flushed_lsn = 2728000


//恢复全备数据
[root@localhost ~]# innobackupex --copy-back /backups/2021-01-04_12-41-04/
210104 13:07:32 [01] Copying ./xtrabackup_info to /opt/data/xtrabackup_info
210104 13:07:32 [01]        ...done
210104 13:07:32 completed OK!

//更改数据的属主属组
[root@localhost ~]# ll /opt/data/
total 12328
-rw-r-----. 1 root root      431 Jan  4 13:07 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Jan  4 13:07 ibdata1
drwxr-x---. 2 root root     4096 Jan  4 13:07 mysql
drwxr-x---. 2 root root     8192 Jan  4 13:07 performance_schema
drwxr-x---. 2 root root       58 Jan  4 13:07 school
drwxr-x---. 2 root root     8192 Jan  4 13:07 sys
-rw-r-----. 1 root root      509 Jan  4 13:07 xtrabackup_info
-rw-r-----. 1 root root        1 Jan  4 13:07 xtrabackup_master_key_id
[root@localhost ~]# chown -R mysql.mysql /opt/data/
[root@localhost ~]# ll /opt/data/
total 12328
-rw-r-----. 1 mysql mysql      431 Jan  4 13:07 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jan  4 13:07 ibdata1
drwxr-x---. 2 mysql mysql     4096 Jan  4 13:07 mysql
drwxr-x---. 2 mysql mysql     8192 Jan  4 13:07 performance_schema
drwxr-x---. 2 mysql mysql       58 Jan  4 13:07 school
drwxr-x---. 2 mysql mysql     8192 Jan  4 13:07 sys
-rw-r-----. 1 mysql mysql      509 Jan  4 13:07 xtrabackup_info
-rw-r-----. 1 mysql mysql        1 Jan  4 13:07 xtrabackup_master_key_id


//启动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                |
+--------------------+
[root@localhost ~]#

验证完成，增量备份已经恢复

总结：

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

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

（3）再将增量备份数据使用参数--incremental-dir合并到全备数据当中；

（4）最后通过最后的全备数据进行恢复数据，注意，如果有多个增量备份，需要逐一合并到全备数据当中，再进行恢复。