XtraBackup数据库物理备份
1、XtraBackup介绍
XtraBackup是Percona公司的开源项目，用以实现类似Innodb官方的热备份工具InnoDB Hot Backup的功能，它支持在线热备份(备份时不影响数据读写)。到目前为止，最新的版本为Percona XtraBackup 2.4.7.
XtraBackup有很多功能和优点：例如支持全备、增量备份、部分备份；支持压缩备份；备份不影响数据读写、事务等，但是也有缺陷不足：例如不支持脱机备份、不支持直接备份到磁带设备、不支持Cloud Back，MyISAM的备份也会阻塞。不过这些小瑕疵不影响XtraBackup成为一款流行的MySQL备份工具。另外，请注意XtraBackup只支持Linux平台，不支持Windows平台。
2、XtraBackup安装
下面我测试的环境为CentOS Linux release 7.2.1511 (Core) ，安装Percona XtraBackup 2.4为例：
步骤1： Install the Percona repositor
[root@harlan software]#wget https://www.percona.com/redir/downloads/percona-release/redhat/1.0-15/percona-release-1.0-15.noarch.rpm
[root@harlan software]# yum install percona-release-1.0-15.noarch.rpm
已加载插件：fastestmirror
正在检查 percona-release-1.0-15.noarch.rpm: percona-release-1.0-15.noarch
percona-release-1.0-15.noarch.rpm 将被安装
正在解决依赖关系
--> 正在检查事务
---> 软件包 percona-release.noarch.0.1.0-15 将被 安装
--> 解决依赖关系完成
依赖关系解决
================================================================================================================================================
Package 架构 版本 源 大小
================================================================================================================================================
正在安装:
percona-release noarch 1.0-15 /percona-release-1.0-15.noarch 21 k
事务概要
================================================================================================================================================
安装 1 软件包
总计：21 k
安装大小：21 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在安装 : percona-release-1.0-15.noarch 1/1
* Enabling the Percona Original repository
<*> All done!
The percona-release package now contains a percona-release script that can enable additional repositories for our newer products.
For example, to enable the Percona Server 8.0 repository use:
percona-release setup ps80
Note: To avoid conflicts with older product versions, the percona-release setup command may disable our original repository for some products.
For more information, please visit:
https://www.percona.com/doc/percona-repo-config/percona-release.html
验证中 : percona-release-1.0-15.noarch 1/1
已安装:
percona-release.noarch 0:1.0-15
完毕
步骤2： 测试Repository,确保Percona XtraBackup相关包已经在Repository中。
[root@harlan ~]# yum list | grep percona-xtrabackup
percona-xtrabackup.x86_64 2.3.10-1.el7 percona-release-x86_64
percona-xtrabackup-22.x86_64 2.2.13-1.el7 percona-release-x86_64
percona-xtrabackup-22-debuginfo.x86_64 2.2.13-1.el7 percona-release-x86_64
percona-xtrabackup-24.x86_64 2.4.19-1.el7 percona-release-x86_64
percona-xtrabackup-24-debuginfo.x86_64 2.4.19-1.el7 percona-release-x86_64
percona-xtrabackup-80.x86_64 8.0.10-1.el7 percona-release-x86_64
percona-xtrabackup-80-debuginfo.x86_64 8.0.10-1.el7 percona-release-x86_64
percona-xtrabackup-debuginfo.x86_64 2.3.10-1.el7 percona-release-x86_64
percona-xtrabackup-test.x86_64 2.3.10-1.el7 percona-release-x86_64
percona-xtrabackup-test-22.x86_64 2.2.13-1.el7 percona-release-x86_64
percona-xtrabackup-test-24.x86_64 2.4.19-1.el7 percona-release-x86_64
percona-xtrabackup-test-80.x86_64 8.0.10-1.el7 percona-release-x86_64
步骤3：安装Percona XtraBackup包
[root@harlan ~]# yum install percona-xtrabackup-24
已加载插件：fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.cn99.com
* extras: mirrors.huaweicloud.com
* updates: mirrors.163.com
正在解决依赖关系
--> 正在检查事务
---> 软件包 percona-xtrabackup-24.x86_64.0.2.4.19-1.el7 将被 安装
--> 正在处理依赖关系 perl(Digest::MD5)，它被软件包 percona-xtrabackup-24-2.4.19-1.el7.x86_64 需要
--> 正在处理依赖关系 perl(DBD::mysql)，它被软件包 percona-xtrabackup-24-2.4.19-1.el7.x86_64 需要
--> 正在处理依赖关系 rsync，它被软件包 percona-xtrabackup-24-2.4.19-1.el7.x86_64 需要
--> 正在处理依赖关系 libev.so.4()(64bit)，它被软件包 percona-xtrabackup-24-2.4.19-1.el7.x86_64 需要
--> 正在检查事务
---> 软件包 libev.x86_64.0.4.15-7.el7 将被 安装
---> 软件包 perl-DBD-MySQL.x86_64.0.4.023-6.el7 将被 安装
--> 正在处理依赖关系 perl(DBI::Const::GetInfoType)，它被软件包 perl-DBD-MySQL-4.023-6.el7.x86_64 需要
--> 正在处理依赖关系 perl(DBI)，它被软件包 perl-DBD-MySQL-4.023-6.el7.x86_64 需要
---> 软件包 perl-Digest-MD5.x86_64.0.2.52-3.el7 将被 安装
--> 正在处理依赖关系 perl(Digest::base) >= 1.00，它被软件包 perl-Digest-MD5-2.52-3.el7.x86_64 需要
---> 软件包 rsync.x86_64.0.3.1.2-6.el7_6.1 将被 安装
--> 正在检查事务
---> 软件包 perl-DBI.x86_64.0.1.627-4.el7 将被 安装
--> 正在处理依赖关系 perl(RPC::PlServer) >= 0.2001，它被软件包 perl-DBI-1.627-4.el7.x86_64 需要
--> 正在处理依赖关系 perl(RPC::PlClient) >= 0.2000，它被软件包 perl-DBI-1.627-4.el7.x86_64 需要
--> 正在处理依赖关系 perl(Data::Dumper)，它被软件包 perl-DBI-1.627-4.el7.x86_64 需要
---> 软件包 perl-Digest.noarch.0.1.17-245.el7 将被 安装
--> 正在检查事务
---> 软件包 perl-Data-Dumper.x86_64.0.2.145-3.el7 将被 安装
---> 软件包 perl-PlRPC.noarch.0.0.2020-14.el7 将被 安装
--> 正在处理依赖关系 perl(Net::Daemon) >= 0.13，它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要
--> 正在处理依赖关系 perl(Net::Daemon::Test)，它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要
--> 正在处理依赖关系 perl(Net::Daemon::Log)，它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要
--> 正在处理依赖关系 perl(Compress::Zlib)，它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要
--> 正在检查事务
---> 软件包 perl-IO-Compress.noarch.0.2.061-2.el7 将被 安装
--> 正在处理依赖关系 perl(Compress::Raw::Zlib) >= 2.061，它被软件包 perl-IO-Compress-2.061-2.el7.noarch 需要
--> 正在处理依赖关系 perl(Compress::Raw::Bzip2) >= 2.061，它被软件包 perl-IO-Compress-2.061-2.el7.noarch 需要
---> 软件包 perl-Net-Daemon.noarch.0.0.48-5.el7 将被 安装
--> 正在检查事务
---> 软件包 perl-Compress-Raw-Bzip2.x86_64.0.2.061-3.el7 将被 安装
---> 软件包 perl-Compress-Raw-Zlib.x86_64.1.2.061-4.el7 将被 安装
--> 解决依赖关系完成
依赖关系解决
================================================================================================================================================
Package 架构 版本 源 大小
================================================================================================================================================
正在安装:
percona-xtrabackup-24 x86_64 2.4.19-1.el7 percona-release-x86_64 7.6 M
为依赖而安装:
libev x86_64 4.15-7.el7 extras 44 k
perl-Compress-Raw-Bzip2 x86_64 2.061-3.el7 base 32 k
perl-Compress-Raw-Zlib x86_64 1:2.061-4.el7 base 57 k
perl-DBD-MySQL x86_64 4.023-6.el7 base 140 k
perl-DBI x86_64 1.627-4.el7 base 802 k
perl-Data-Dumper x86_64 2.145-3.el7 base 47 k
perl-Digest noarch 1.17-245.el7 base 23 k
perl-Digest-MD5 x86_64 2.52-3.el7 base 30 k
perl-IO-Compress noarch 2.061-2.el7 base 260 k
perl-Net-Daemon noarch 0.48-5.el7 base 51 k
perl-PlRPC noarch 0.2020-14.el7 base 36 k
rsync x86_64 3.1.2-6.el7_6.1 base 404 k
事务概要
================================================================================================================================================
安装 1 软件包 (+12 依赖软件包)
总下载量：9.4 M
安装大小：12 M
Is this ok [y/d/N]: y
Downloading packages:
(1/13): perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm | 57 kB 00:00:00
(2/13): perl-DBI-1.627-4.el7.x86_64.rpm | 802 kB 00:00:00
(3/13): perl-Data-Dumper-2.145-3.el7.x86_64.rpm | 47 kB 00:00:00
(4/13): perl-Digest-1.17-245.el7.noarch.rpm | 23 kB 00:00:00
(5/13): perl-Digest-MD5-2.52-3.el7.x86_64.rpm | 30 kB 00:00:00
(6/13): perl-IO-Compress-2.061-2.el7.noarch.rpm | 260 kB 00:00:00
(7/13): perl-Net-Daemon-0.48-5.el7.noarch.rpm | 51 kB 00:00:00
(8/13): perl-PlRPC-0.2020-14.el7.noarch.rpm | 36 kB 00:00:00
(9/13): rsync-3.1.2-6.el7_6.1.x86_64.rpm | 404 kB 00:00:00
(10/13): libev-4.15-7.el7.x86_64.rpm | 44 kB 00:00:05
(11/13): perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm | 32 kB 00:00:05
(12/13): perl-DBD-MySQL-4.023-6.el7.x86_64.rpm | 140 kB 00:00:12
warning: /var/cache/yum/x86_64/7/percona-release-x86_64/packages/percona-xtrabackup-24-2.4.19-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
percona-xtrabackup-24-2.4.19-1.el7.x86_64.rpm 的公钥尚未安装
(13/13): percona-xtrabackup-24-2.4.19-1.el7.x86_64.rpm | 7.6 MB 00:07:04
------------------------------------------------------------------------------------------------------------------------------------------------
总计 23 kB/s | 9.4 MB 00:07:04
从 file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY 检索密钥
导入 GPG key 0x8507EFA5:
用户ID : "Percona MySQL Development Team (Packaging key) <mysql-dev@percona.com>"
指纹 : 4d1b b29d 63d9 8e42 2b21 13b1 9334 a25f 8507 efa5
软件包 : percona-release-1.0-15.noarch (@/percona-release-1.0-15.noarch)
来自 : /etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY
是否继续？[y/N]：y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在安装 : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 1/13
正在安装 : rsync-3.1.2-6.el7_6.1.x86_64 2/13
正在安装 : perl-Digest-1.17-245.el7.noarch 3/13
正在安装 : perl-Digest-MD5-2.52-3.el7.x86_64 4/13
正在安装 : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 5/13
正在安装 : perl-IO-Compress-2.061-2.el7.noarch 6/13
正在安装 : libev-4.15-7.el7.x86_64 7/13
正在安装 : perl-Data-Dumper-2.145-3.el7.x86_64 8/13
正在安装 : perl-Net-Daemon-0.48-5.el7.noarch 9/13
正在安装 : perl-PlRPC-0.2020-14.el7.noarch 10/13
正在安装 : perl-DBI-1.627-4.el7.x86_64 11/13
正在安装 : perl-DBD-MySQL-4.023-6.el7.x86_64 12/13
正在安装 : percona-xtrabackup-24-2.4.19-1.el7.x86_64 13/13
验证中 : perl-Net-Daemon-0.48-5.el7.noarch 1/13
验证中 : perl-Data-Dumper-2.145-3.el7.x86_64 2/13
验证中 : percona-xtrabackup-24-2.4.19-1.el7.x86_64 3/13
验证中 : perl-Digest-MD5-2.52-3.el7.x86_64 4/13
验证中 : perl-DBD-MySQL-4.023-6.el7.x86_64 5/13
验证中 : perl-IO-Compress-2.061-2.el7.noarch 6/13
验证中 : libev-4.15-7.el7.x86_64 7/13
验证中 : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 8/13
验证中 : perl-Digest-1.17-245.el7.noarch 9/13
验证中 : rsync-3.1.2-6.el7_6.1.x86_64 10/13
验证中 : perl-DBI-1.627-4.el7.x86_64 11/13
验证中 : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 12/13
验证中 : perl-PlRPC-0.2020-14.el7.noarch 13/13
已安装:
percona-xtrabackup-24.x86_64 0:2.4.19-1.el7
作为依赖被安装:
libev.x86_64 0:4.15-7.el7 perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7
perl-DBD-MySQL.x86_64 0:4.023-6.el7 perl-DBI.x86_64 0:1.627-4.el7 perl-Data-Dumper.x86_64 0:2.145-3.el7
perl-Digest.noarch 0:1.17-245.el7 perl-Digest-MD5.x86_64 0:2.52-3.el7 perl-IO-Compress.noarch 0:2.061-2.el7
perl-Net-Daemon.noarch 0:0.48-5.el7 perl-PlRPC.noarch 0:0.2020-14.el7 rsync.x86_64 0:3.1.2-6.el7_6.1
完毕！
3、XtraBackup的备份原理
XtraBackup的备份原理：
XtraBackup基于InnoDB的crash-recovery功能。它会复制innodb 的data file，由于不锁表，复制出来的数据是不一致的，在恢复的时候使用crash-recovery，使得数据恢复一致。
InnoDB维护了一个redo log，又称为 transaction log，事务日志，它包含了innodb数据的所有改动情况。当InnoDB启动的时候，它会先去检查data file和transaction log，并且会做二步操作：
1）它适用于提交事务日志条目数据文件
2）它执行撤销操作在任何事务修改但未提交的数据
XtraBackup在备份的时候， 一页一页地复制innodb的数据，而且不锁定表，与此同时，XtraBackup还有另外一个线程监视着transactions log，一旦log发生变化，就把变化过的log pages复制走。为什么要急着复制走呢？ 前几章的时候就提过这个问题，因为 transactions log文件大小有限，写满之后，就会从头再开始写，所以新数据可能会覆盖到旧的数据。 在prepare过程中，XtraBackup使用复制到的transactions log 对备份出来的innodb data file 进行crash recovery。
使用前对 innobackupex一些关键参数做介绍：
--defaults-file #指定MySQL配置文件 ，如果不指定–defaults-file，默认值为/etc/my.cnf
--user=root #备份操作用户名，一般都是root用户
/mnt/backup/ #备份路径
--socket=/tmp/mysql.sock #指定mysql.sock登录(可通过innobackupex --help查看)
--parallel=2 --throttle=200 #并行个数，根据主机配置选择合适的，默认是1个，多个可以加快备份速度。
2>/mnt/backup/bak.log #备份日志，将备份过程中的输出信息重定向到bak.log
--apply-log #在备份目录下生成一个xtrabackup_logfile事务日志文件， 此外，创建新的事务日志。 InnoDB配置从文件“backup-my.cnf”中读取。
--redo-only #全备数据后对DB有增量更改，则必须执行此操作，在准备基本完全备份和合并除最后一个之外的所有增量备份时，应使用此选项
注意：
备份单个或者多个库 --databases 用于指定要备份的数据库, 备份多个库使用方法: --databases="db_zone_9033 db_zone_9034" 用空格分开。
排除多个个库 --databases-exclude 根据名称排除数据库 ，使用方法：--databases-exclude="db_zone_battlereport_9037 db_zone_battlereport_9038" 用空格分开。
4、XtraBackup的全量备份
1）创建用于备份恢复的用户 backupuser 并赋予权限,也可以使用root用户权限
MariaDB [(none)]> create user 'backupuser'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.004 sec)
MariaDB [(none)]> grant reload,process,lock tables,replication client on *.* to 'backupuser'@'localhost';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.002 sec)
2）创建用于备份数据存放目录
[root@harlan ~]# mkdir -p /data/backups/base
3）进行数据全备
出现以下报错：
[root@harlan ~]# innobackupex --user=backupuser --password=123456 --databases="harlanch" /data/backups/base/
xtrabackup: recognized server arguments: --datadir=/application/mysql/data --open_files_limit=10240 --innodb_buffer_pool_size=1G --innodb_log_file_size=256M --innodb_flush_log_at_trx_commit=1 --innodb_flush_method=O_DIRECT
xtrabackup: recognized client arguments:
200409 05:23:15 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
200409 05:23:15 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/application/mysql/data/mysql.sock' as 'backupuser' (using password: YES).
200409 05:23:15 version_check Connected to MySQL server
200409 05:23:15 version_check Executing a version check against the server...
200409 05:23:15 version_check Done.
200409 05:23:15 Connecting to MySQL server host: localhost, user: backupuser, password: set, port: 3306, socket: /application/mysql/data/mysql.sock
Using server version 10.3.15-MariaDB
innobackupex version 2.4.19 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c2d69da)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /application/mysql/data
xtrabackup: open files limit requested 10240, set to 65535
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 268435456
xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.3.15. Please follow the instructions at http://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html
查了下官网，原来xtrabackup已经不支持mariadb-10.3版本了。
maridb官方文档也介绍了这个变量：innodb_safe_truncate
innodb_safe_truncate
Description: Use a backup-safe TRUNCATE TABLE implementation and crash-safe rename operations inside InnoDB. This is not compatible with hot backup tools other than Mariabackup. Users who need to use such tools may set this to OFF.
Commandline: --innodb-safe-truncate={0|1}
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: ON
Introduced: MariaDB 10.2.19
Removed: MariaDB 10.3.0
原文链接：https://mariadb.com/kb/en/library/innodb-system-variables/#innodb_safe_truncate
上面的描述翻译是： 在InnoDB中使用备份安全的TRUNCATE TABLE实现和崩溃安全的重命名操作。这与Mariabackup以外的热备份工具不兼容。需要使用此类工具的用户可将此设置为OFF。
解决方法
所以我们只要把这个系统变量关了就行了。然后重启程序systemctl restart mariadb
MariaDB 在10.2.19版本就更新了innodb_safe_truncate这个变量，所以，10.2.19版本~10.3版本还是可以用xtrabackup这个工具的，只不过要把innodb_safe_truncate这个变量OFF掉就可以了。
继续上述操作：
[root@harlan ~]#innobackupex --user=root --password=harlan --parallel=2 --throttle=200 /data/backups/base
xtrabackup: recognized server arguments: --datadir=/application/mysql/data --open_files_limit=10240 --innodb_buffer_pool_size=1G --innodb_log_file_size=256M --innodb_flush_log_at_trx_commit=1 --innodb_flush_method=O_DIRECT
xtrabackup: recognized client arguments:
200409 05:23:15 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
4)查看备份文件是否成功
[root@mysql01 base]# ll /data/backups/base/2020-04-10_14-36-29/
总用量 12328
-rw-r----- 1 root root 487 4月 10 14:36 backup-my.cnf
-rw-r----- 1 root root 409 4月 10 14:36 ib_buffer_pool
-rw-r----- 1 root root 12582912 4月 10 14:36 ibdata1
drwxr-x--- 2 root root 4096 4月 10 14:36 mysql
drwxr-x--- 2 root root 4096 4月 10 14:36 performance_schema
drwxr-x--- 2 root root 12288 4月 10 14:36 sys
-rw-r----- 1 root root 135 4月 10 14:36 xtrabackup_checkpoints
-rw-r----- 1 root root 458 4月 10 14:36 xtrabackup_info
-rw-r----- 1 root root 2560 4月 10 14:36 xtrabackup_logfile
配置文件注释：
(1) backup-my.cnf —— 备份命令用到的配置选项信息；
(2) ibdata1 —— 备份的表空间文件；
(3) xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件；
(4) xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置；
(5) xtrabackup_logfile —— 备份的重做日志文件。
(6) xtrabackup_checkpoints —— 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；
(7) harlanch mysql sys performance_schema——数据库
5、XtraBackup的全量恢复
1）首先关闭数据库，并且清除/application/mysql/data目录的数据，也就是清除数据库存放目录
[root@mysql01 ~]# /etc/init.d/mysqld stop
[root@mysql01 ~]# rm -rf /application/mysql/data/*
[root@mysql01 ~]# ll /application/mysql/data/
总用量 0
2）在全备目录上重放已提交的事务
[root@mysql01 ~]# innobackupex --copy-back /data/backups/base/
xtrabackup: recognized server arguments: --datadir=/application/mysql/data --innodb_buffer_pool_size=1024M --innodb_log_buffer_size=8M 使用默认8M
xtrabackup: recognized client arguments:
200410 15:19:42 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.19 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c2d69da)
200410 15:19:42 [01] Copying ib_logfile0 to /application/mysql/data/ib_logfile0
200410 15:19:43 [01] ...done
200410 15:19:43 [01] Copying ib_logfile1 to /application/mysql/data/ib_logfile1
200410 15:19:44 [01] ...done
200410 15:19:44 [01] Copying ibdata1 to /application/mysql/data/ibdata1
...................................
3）重启数据库，查看全量恢复是否成功
查看数据库存放目录是否存在数据
[root@mysql01 ~]# ll /application/mysql/data/
总用量 176164
drwxr-x--- 2 root root 4096 4月 10 15:19 harlanch
-rw-r----- 1 root root 661 4月 10 15:19 ib_buffer_pool
-rw-r----- 1 root root 79691776 4月 10 15:19 ibdata1
-rw-r----- 1 root root 50331648 4月 10 15:19 ib_logfile0
-rw-r----- 1 root root 50331648 4月 10 15:19 ib_logfile1
drwxr-x--- 2 root root 4096 4月 10 15:19 mysql
drwxr-x--- 2 root root 4096 4月 10 15:19 performance_schema
drwxr-x--- 2 root root 12288 4月 10 15:19 sys
-rw-r----- 1 root root 457 4月 10 15:19 xtrabackup_info
-rw-r----- 1 root root 1 4月 10 15:19 xtrabackup_master_key_id
将恢复的数据库目录和文件授权mysql用户和组
[root@mysql01 ~]# chown -R mysql.mysql /application/mysql/*
启动数据库
[root@mysql01 ~]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| harlanch |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
由上可见，数据库全量恢复成功。
6、XtraBackup的增量备份
1）首先在数据库中插入一张表t_flag_server
mysql> create table t_flag_server(app varchar(64) not null,server varchar(64) not null,division varchar(64) not null,node varchar(64) not null,port varchar(64) not null,status int(10) unsigned not null,PRIMARY KEY(app,server,division,node,port)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)
mysql> show tables;
mysql> desc t_flag_server;
+----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| app | varchar(64) | NO | PRI | NULL | |
| server | varchar(64) | NO | PRI | NULL | |
| division | varchar(64) | NO | PRI | NULL | |
| node | varchar(64) | NO | PRI | NULL | |
| port | varchar(64) | NO | PRI | NULL | |
| status | int(10) unsigned | NO | | NULL | |
+----------+------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
2)增量备份
[root@mysql01 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=harlan --incremental /data/backups/incremental/ --incremental-basedir=/data/backups/base/ --parallel=2
...............................
..............................
#--incremental /backup/ 指定增量备份文件备份的目录
#--incremental-basedir 指定上一次全备或增量备份的目录
3)查看是否备份成功
[root@mysql01 ~]# cd /data/backups/incremental/2020-04-10_15-51-32/
[root@mysql01 2020-04-10_15-51-32]# ls
backup-my.cnf ib_buffer_pool ibdata1.meta performance_schema xtrabackup_checkpoints xtrabackup_logfile
harlanch ibdata1.delta mysql sys xtrabackup_info
[root@mysql01 2020-04-10_15-51-32]# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 22278571
to_lsn = 22284503
last_lsn = 22284512
compact = 0
recover_binlog_info = 0
flushed_lsn = 22284512
由上述可知，此次增量备份成功。
7、XtraBackup的增量恢复
增量备份的恢复需要有3个步骤
- 恢复完全备份
- 恢复增量备份到完全备份(开始恢复的增量备份要添加--redo-only参数，到最后一次增量备份要去掉--redo-only)
- 对整体的完全备份进行恢复，回滚未提交的数据
1）准备一个全备
#####准备一个全备#######
[root@mysql01 ~]# innobackupex --apply-log --redo-only /data/backups/base/ xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=1 xtrabackup: recognized client arguments: 200410 16:16:53 innobackupex: Starting the apply-log operation IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!". innobackupex version 2.4.19 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c2d69da) xtrabackup: cd to /data/backups/base/ xtrabackup: This target seems to be already prepared with --apply-log-only. InnoDB: Number of pools: 1 xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'. xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = . xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 50331648 xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = . xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 50331648 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) InnoDB: PUNCH HOLE support available InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Uses event mutexes InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier InnoDB: Compressed tables use zlib 1.2.3 InnoDB: Number of pools: 1 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M InnoDB: Completed initialization of buffer pool InnoDB: page_cleaner coordinator priority: -20 InnoDB: Highest supported file format is Barracuda. xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 22278713 InnoDB: Number of pools: 1 200410 16:16:55 complete
2)将增量应用到全备中
########将增量1应用到完全备份中############
[root@mysql01 ~]# innobackupex --apply-log --redo-only /data/backups/base/ --incremental-dir=/data/backups/incremental/2020-04-10_15-51-32/ ............................. .............................
注意：如果有增量2，而且增量2为最后一个备份，把增量2应用到完全备份将不需要添加参数：--redo-only
3）使用全恢复
[root@mysql01 ~]# innobackupex --copy-back /data/backups/base/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=1
xtrabackup: recognized client arguments:
200410 16:22:35 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
innobackupex version 2.4.19 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c2d69da)
xtrabackup: cd to /data/backups/base/
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
...............................................
查看是否恢复成功
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| harlanch |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use harlanch;
Database changed
mysql> desc t_flag_server;
+----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| app | varchar(64) | NO | PRI | NULL | |
| server | varchar(64) | NO | PRI | NULL | |
| division | varchar(64) | NO | PRI | NULL | |
| node | varchar(64) | NO | PRI | NULL | |
| port | varchar(64) | NO | PRI | NULL | |
| status | int(10) unsigned | NO | | NULL | |
+----------+------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
如果有错误，欢迎各位网友指导。
另外喜欢的话，给个推荐呗，谢谢！
