MySQL8.0之XtraBackup【转】
XtraBackup是由Percona提供的开源备份软件。它能在不关闭服务器的情况下复制普通文件。但为了避免不一致，它会使用redo日志文件。XtraBackup被许多公司广泛用做标准备份工具。与逻辑备份工具相比，其优势是备份速度非常快，恢复速度也非常快。
Percona XtraBackup的工作原理：
1.XtraBackup复制InnoDB数据文件，这会导致内部不一致的数据，但是它会对文件执行崩溃恢复，以使其再次成为一个一致的可用数据库
2.这样做的可行性是因为InnoDB维护一个REDO日志，也称为事务日志。REDO日志包含了InnoDB数据每次更改的记录。当InnoDB启动时，REDO日志会检查数据文件和事务日志，并执行两个步骤。它将已提交的事务日志条目应用于数据文件，并对任何修改了数据但未提交的事务执行undo操作
3.Percona XtraBackup会在启动时记住日志序列号（LSN）,然后复制数据文件。这需要一些时间来完成，如果文件正在改变，那么它会在不同的时间点反映数据库的状态。同时，Percona XtraBackup运行一个后台进程，用于监视事务日志文件，并从中复制更改。Percona XtraBackup需要持续这样做，因为事务日志是以循环方式写入的，并且可以在一段时间后重新使用。Percona XtraBackup开始执行后，需要复制每次数据文件更改对应的事务日志记录。
安装
--下载rpm包
[root@mysql ~]# wget https://www.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0-6/binary/redhat/6/x86_64/percona-xtrabackup-80-8.0.6-1.el6.x86_64.rpm
--yum安装rpm包，提示缺少 libev.so.4()(64bit)
[root@mysql ~]# yum localinstall percona-xtrabackup-80-8.0.6-1.el6.x86_64.rpm
Loaded plugins: fastestmirror, security
Setting up Local Package Process
Examining percona-xtrabackup-80-8.0.6-1.el6.x86_64.rpm: percona-xtrabackup-80-8.0.6-1.el6.x86_64
Marking percona-xtrabackup-80-8.0.6-1.el6.x86_64.rpm to be installed
....省略...
--> Finished Dependency Resolution
Error: Package: percona-xtrabackup-80-8.0.6-1.el6.x86_64 (/percona-xtrabackup-80-8.0.6-1.el6.x86_64)
Requires: libev.so.4()(64bit)
You could try using --skip-broken to work around the problem
** Found 3 pre-existing rpmdb problem(s), 'yum check' output follows:
2:postfix-2.6.6-2.2.el6_1.x86_64 has missing requires of libmysqlclient.so.16()(64bit)
2:postfix-2.6.6-2.2.el6_1.x86_64 has missing requires of libmysqlclient.so.16(libmysqlclient_16)(64bit)
2:postfix-2.6.6-2.2.el6_1.x86_64 has missing requires of mysql-libs
--下载安装 libev.so.4()(64bit)
去如下网站查找对应系统的包
http://rpmfind.net/linux/rpm2html/search.php?query=libev.so.4%28%29%2864bit%29&submit=Search+...&system=&arch=
--下载并rpm安装
[root@oracle ~]# wget http://rpmfind.net/linux/epel/6/x86_64/Packages/l/libev-4.03-3.el6.x86_64.rpm
[root@oracle ~]# rpm -ivh libev-4.03-3.el6.x86_64.rpm
Preparing... ########################################### [100%]
1:libev ########################################### [100%]
--重新安装 percona-xtrabackup
[root@oracle ~]# yum localinstall percona-xtrabackup-80-8.0.6-1.el6.x86_64.rpm
Loaded plugins: fastestmirror, security
Setting up Local Package Process
Examining percona-xtrabackup-80-8.0.6-1.el6.x86_64.rpm: percona-xtrabackup-80-8.0.6-1.el6.x86_64
....省略...
Complete!
权限
如果需要创建专门的用户去执行备份恢复操作，需要选项如下：
mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cr%T';
mysql> GRANT BACKUP_ADMIN, SUPER, PROCESS, CREATE TABLESPACE, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
mysql> GRANT SELECT, INSERT, CREATE ON performance_schema.* TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;
--如下是官网对每个参数含义的解释
• RELOAD and LOCK TABLES (unless the --no-lock option is specified) in order to run FLUSH TABLES WITH READ LOCK and FLUSH ENGINE LOGS prior to start copying the files, and requires this privilege when Backup Locks are used
• BACKUP_ADMIN privilege is needed to query the performance_schema.log_status table, and run LOCK INSTANCE FOR BACKUP, LOCK BINLOG FOR BACKUP, or LOCK TABLES FOR BACKUP
• REPLICATION CLIENT in order to obtain the binary log position
• CREATE TABLESPACE in order to import tables (see Restoring Individual Tables)
• PROCESS in order to run SHOW ENGINE INNODB STATUS (which is mandatory), and optionally to see all threads which are running on the server (see Handling FLUSH TABLES WITH READ LOCK)
• SUPER in order to start/stop the slave threads in a replication environment, use XtraDB Changed Page Tracking for Incremental Backups and for handling FLUSH TABLES WITH READ LOCK
• CREATE privilege in order to create the PERCONA_SCHEMA.xtrabackup_history database and table
• INSERT privilege in order to add history records to the PERCONA_SCHEMA.xtrabackup_history table
• SELECT privilege in order to use --incremental-history-name or --incremental-history-uuid in order for the feature to look up the innodb_to_lsn values in the PERCONA_SCHEMA.xtrabackup_history table
1.全量备份和恢复
全量备份
在XtraBackup8概述
移除了innobackupex命令。
由于新的MySQL重做日志和数据字典格式，8.0版本只支持mysql8.0和percona8.0。
早于mysql8.0的版本需要使用xtrabackup2.4备份和恢复
--备份命令
[root@oracle bin]# xtrabackup --user=root --backup --socket=/tmp/mysql.sock --target-dir=/xtrabackup
...省略...
190802 15:52:55 All tables unlocked
190802 15:52:55 [00] Copying ib_buffer_pool to /xtrabackup/ib_buffer_pool
190802 15:52:55 [00] ...done
190802 15:52:55 Backup created in directory '/xtrabackup/'
MySQL binlog position: filename 'binlog.000011', position '155'
190802 15:52:55 [00] Writing /xtrabackup/backup-my.cnf
190802 15:52:55 [00] ...done
190802 15:52:55 [00] Writing /xtrabackup/xtrabackup_info
190802 15:52:55 [00] ...done
xtrabackup: Transaction log of lsn (20323274) to (20323294) was copied.
190802 15:52:55 completed OK!
--查看备份
[root@oracle bin]# ll /xtrabackup/
total 56372
-rw-r----- 1 root root 476 Aug 2 15:52 backup-my.cnf
-rw-r----- 1 root root 155 Aug 2 15:52 binlog.000011
-rw-r----- 1 root root 16 Aug 2 15:52 binlog.index
drwxr-x--- 2 root root 4096 Aug 2 15:52 company
-rw-r----- 1 root root 3329 Aug 2 15:52 ib_buffer_pool
-rw-r----- 1 root root 12582912 Aug 2 15:52 ibdata1
drwxr-x--- 2 root root 4096 Aug 2 15:52 mysql
-rw-r----- 1 root root 24117248 Aug 2 15:52 mysql.ibd
drwxr-x--- 2 root root 4096 Aug 2 15:52 performance_schema
drwxr-x--- 2 root root 4096 Aug 2 15:52 sys
-rw-r----- 1 root root 10485760 Aug 2 15:52 undo_001
-rw-r----- 1 root root 10485760 Aug 2 15:52 undo_002
-rw-r----- 1 root root 18 Aug 2 15:52 xtrabackup_binlog_info
-rw-r----- 1 root root 95 Aug 2 15:52 xtrabackup_checkpoints
-rw-r----- 1 root root 479 Aug 2 15:52 xtrabackup_info
-rw-r----- 1 root root 2560 Aug 2 15:52 xtrabackup_logfile
-rw-r----- 1 root root 262 Aug 2 15:52 xtrabackup_tablespaces
备份时间长短根据数据库大小有关，在备份的期间可以随时取消，因为xtrabackup命令不会对数据库进行操作
全量恢复
--停止数据库
[root@oracle data]# service mysqld stop
Shutting down MySQL.. SUCCESS!
--删除现有的data目录
[root@oracle data]# rm -rf /usr/local/mysql/data/*
--执行恢复命令
[root@oracle data]# xtrabackup --prepare --target-dir=/xtrabackup/
[root@oracle data]# xtrabackup --copy-back --target-dir=/xtrabackup/
xtrabackup: recognized server arguments: --datadir=/usr/local/mysql/data --server-id=1
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/xtrabackup/
xtrabackup version 8.0.6 based on MySQL server 8.0.14 Linux (x86_64) (revision id: c0a2d91)
190802 16:33:39 [01] Copying undo_001 to /usr/local/mysql/data/undo_001
...省略...
190802 16:33:41 [01] ...done
190802 16:33:41 completed OK!
--对文件授权
chwon -R mysql.mysql /usr/local/mysql/data/*
Tips:
--copy-back命令表示将备份 复制 到datadir目录下，如果不想保留备份，可以使用--move-back命令，直接将备份 移动 到datadir目录下
2.增量备份
在进行增量备份之前，通过先进行一次全量备份。XtraBackup通过二进制方式在备份目录下写入xtrabackup_checkpoints文件。该文件其中一行会显示to_lsn，
该参数记录了数据库备份完成的LSN。全量备份命令：
方式一：
基于base的备份
[root@oracle xtrabackup]# xtrabackup --user=root --backup --socket=/tmp/mysql.sock --target-dir=/xtrabackup/base
xtrabackup_checkpoints的内容：
[root@oracle xtrabackup]# cat /xtrabackup/base/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 19957274
last_lsn = 19957284
flushed_lsn = 0
在进行了全量备份后 ，我们可以通过 增量备份 的命令进行备份：
基于base的incr1备份
[root@oracle xtrabackup]# xtrabackup --user=root --backup --socket=/tmp/mysql.sock --target-dir=/xtrabackup/incr1 --incremental-basedir= /xtrabackup/base
xtrabackup_checkpoints的内容：
[root@oracle xtrabackup]# cat /xtrabackup/incr1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 19957274
to_lsn = 19957304
last_lsn = 19957314
flushed_lsn = 0
基于incr1的incr2备份
[root@oracle xtrabackup]# xtrabackup --user=root --backup --socket=/tmp/mysql.sock --target-dir=/xtrabackup/incr2 --incremental-basedir= /xtrabackup/incr1/
xtrabackup_checkpoints的内容：
[root@oracle xtrabackup]# cat /xtrabackup/incr2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 19957304
to_lsn = 19957364
last_lsn = 19957374
flushed_lsn = 0
结论：
假设周一是基于base的备份，周二是基于base的incr1备份，周三是基于incr1的incr2备份，在恢复数据库的时候，需要使用base，incr1，incr2三个备份都存在时，才能进行完整的恢复，每个备份的from_lsn都是基于上一个备份的to_lsn，所以缺一不可。
方式二：
基于base的备份
[root@oracle xtrabackup]# xtrabackup --user=root --backup --socket=/tmp/mysql.sock --target-dir=/xtrabackup/base
xtrabackup_checkpoints的内容：
[root@oracle xtrabackup]# cat /xtrabackup/base/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 19957274
last_lsn = 19957284
flushed_lsn = 0
基于base的incr1备份
[root@oracle xtrabackup]# xtrabackup --user=root --backup --socket=/tmp/mysql.sock --target-dir=/xtrabackup/incr1 --incremental-basedir= /xtrabackup/base
xtrabackup_checkpoints的内容：
[root@oracle xtrabackup]# cat /xtrabackup/incr1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 19957274
to_lsn = 19957304
last_lsn = 19957314
flushed_lsn = 0
基于base的incr2备份
[root@oracle xtrabackup]# xtrabackup --user=root --backup --socket=/tmp/mysql.sock --target-dir=/xtrabackup/incr2 --incremental-basedir= /xtrabackup/base/
xtrabackup_checkpoints的内容：
[root@oracle xtrabackup]# cat /xtrabackup/incr2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 19957274
to_lsn = 19957394
last_lsn = 19957404
flushed_lsn = 0
结论：假设周一是基于base的备份，周二是基于base的incr1备份，周三是基于base的incr2备份，在恢复数据库的时候，需要使用base和incr1，incr2两个备份中的其中一个，才能进行完整的恢复，因为incr1和incr2的from_lsn都是基于base备份中的to_lsn，所以恢复数据库时，只需要base和任意一个基于base的增量备份。
3.增量备份恢复
增量备份和全量备份的--prepare执行的方式是不一样的。在全量备份中，可以通过两种操作保持数据库的一致性:已提交的事务将根据数据文件和日志文件进行重放操作，并回滚未提交的事务。在准备增量备份时，必须跳过未提交事务的回滚，因为在备份的过程中，可能存在进行中且未提交的事务，并且这些事务很可能在下一次的增量备份中才进行提交，所以必须使用--apply-log-only选项来防止回滚操作。
基于方式一的恢复：
3.1准备好备份片
[root@oracle xtrabackup]# ll
total 20
drwxr-x--- 6 root root 4096 Aug 5 08:30 base
drwxr-x--- 6 root root 4096 Aug 5 08:32 incr1
drwxr-x--- 6 root root 4096 Aug 5 08:52 incr2
3.2执行恢复命令
[root@oracle xtrabackup]# xtrabackup --prepare --apply-log-only --target-dir=/xtrabackup/base
[root@oracle xtrabackup]# xtrabackup --prepare --apply-log-only --target-dir=/xtrabackup/base --incremental-dir=/xtrabackup/incr1
[root@oracle xtrabackup]# xtrabackup --prepare --target-dir=/xtrabackup/base --incremental-dir=/xtrabackup/incr2
[root@oracle xtrabackup]# xtrabackup --copy-back --target-dir=/xtrabackup/base/
3.3对data目录授权
[root@oracle mysql] chown -R mysql.mysql data/
基于方式二的恢复：
3.1准备好备份片
[root@oracle xtrabackup]# ll
total 20
drwxr-x--- 6 root root 4096 Aug 5 08:30 base
drwxr-x--- 6 root root 4096 Aug 5 08:32 incr1
drwxr-x--- 6 root root 4096 Aug 5 08:52 incr2
3.2执行恢复命令
[root@oracle xtrabackup]# xtrabackup --prepare --apply-log-only --target-dir=/xtrabackup/base
[root@oracle xtrabackup]# xtrabackup --prepare --target-dir=/xtrabackup/base --incremental-dir=/xtrabackup/incr2
[root@oracle xtrabackup]# xtrabackup --copy-back --target-dir=/xtrabackup/base/
3.3对data目录授权
[root@oracle mysql] chown -R mysql.mysql data/
Tips:
--apply-log-only命令应该用在所有增量备份（除最后一次增量备份） ，这就是为什么恢复脚本中，最后一次的命令不包含--apply-log-only。即使--apply-log-only在最后一次增量备份时被使用，备份仍将是一致的，但在这种情况下，数据库会执行回滚的操作。
4.压缩备份
[root@oracle xtrabackup]# xtrabackup --user=root --backup --compress --socket=/tmp/mysql.sock --target-dir=/xtrabackup/compress
压缩和未压缩的对比
[root@oracle xtrabackup]# du -h --max-depth=1
57M ./base
3.3M ./compress
压缩后的文件列表
[root@oracle compress]# ll
total 2876
-rw-r----- 1 root root 453 Aug 5 09:02 backup-my.cnf.qp
-rw-r----- 1 root root 183 Aug 5 09:02 binlog.000018.qp
-rw-r----- 1 root root 93 Aug 5 09:02 binlog.index.qp
drwxr-x--- 2 root root 4096 Aug 5 09:02 company
-rw-r----- 1 root root 912 Aug 5 09:02 ib_buffer_pool.qp
-rw-r----- 1 root root 253886 Aug 5 09:02 ibdata1.qp
drwxr-x--- 2 root root 4096 Aug 5 09:02 mysql
-rw-r----- 1 root root 2191005 Aug 5 09:02 mysql.ibd.qp
drwxr-x--- 2 root root 4096 Aug 5 09:02 performance_schema
drwxr-x--- 2 root root 4096 Aug 5 09:02 sys
-rw-r----- 1 root root 224626 Aug 5 09:02 undo_001.qp
-rw-r----- 1 root root 220474 Aug 5 09:02 undo_002.qp
-rw-r----- 1 root root 105 Aug 5 09:02 xtrabackup_binlog_info.qp
-rw-r----- 1 root root 95 Aug 5 09:02 xtrabackup_checkpoints
-rw-r----- 1 root root 471 Aug 5 09:02 xtrabackup_info.qp
-rw-r----- 1 root root 333 Aug 5 09:02 xtrabackup_logfile.qp
-rw-r----- 1 root root 234 Aug 5 09:02 xtrabackup_tablespaces.qp
如果想要加速备份的速度，可以采用--compress-threads命令
[root@oracle xtrabackup]# xtrabackup --user=root --backup --compress --compress-threads=4 --socket=/tmp/mysql.sock --target-dir=/xtrabackup/compress
解压缩
[root@oracle xtrabackup]# xtrabackup --decompress --target-dir=/xtrabackup/compress
报错：
xtrabackup: recognized server arguments: --datadir=/usr/local/mysql/data --server-id=1
xtrabackup: recognized client arguments: --decompress=1 --target-dir=/xtrabackup/compress
xtrabackup version 8.0.6 based on MySQL server 8.0.14 Linux (x86_64) (revision id: c0a2d91)
190805 09:07:23 [01] decompressing ./ibdata1.qp
sh: qpress: command not found
cat: write error: Broken pipe
Error: decrypt and decompress thread 0 failed.
出现这种错误，使用如下解决方式：
[root@oracle ~]# wget http://www.quicklz.com/qpress-11-linux-x64.tar
[root@oracle ~]# tar xf qpress-11-linux-x64.tar
[root@oracle ~]# cp qpress /usr/bin
重新解压缩
[root@oracle xtrabackup]# xtrabackup --decompress --target-dir=/xtrabackup/compress
...省略...
190805 10:29:16 [01] decompressing ./mysql.ibd.qp
190805 10:29:16 [01] decompressing ./binlog.index.qp
190805 10:29:16 completed OK!
Tips:
--parallel命令可以配合--decompress一起使用
正常，查看目录文件，会发现压缩文件和解压文件共存，如果不想保留原压缩文件，可以使用 --remove-original 命令
[root@oracle compress]# ll
total 59236
-rw-r--r-- 1 root root 476 Aug 5 10:29 backup-my.cnf
-rw-r----- 1 root root 453 Aug 5 09:07 backup-my.cnf.qp
-rw-r--r-- 1 root root 155 Aug 5 10:29 binlog.000019
-rw-r----- 1 root root 183 Aug 5 09:07 binlog.000019.qp
-rw-r--r-- 1 root root 16 Aug 5 10:29 binlog.index
-rw-r----- 1 root root 93 Aug 5 09:07 binlog.index.qp
drwxr-x--- 2 root root 4096 Aug 5 10:29 company
-rw-r--r-- 1 root root 3329 Aug 5 10:29 ib_buffer_pool
-rw-r----- 1 root root 912 Aug 5 09:07 ib_buffer_pool.qp
-rw-r--r-- 1 root root 12582912 Aug 5 10:29 ibdata1
-rw-r----- 1 root root 253881 Aug 5 09:07 ibdata1.qp
drwxr-x--- 2 root root 4096 Aug 5 10:29 mysql
-rw-r--r-- 1 root root 24117248 Aug 5 10:29 mysql.ibd
-rw-r----- 1 root root 2191005 Aug 5 09:07 mysql.ibd.qp
drwxr-x--- 2 root root 12288 Aug 5 10:29 performance_schema
drwxr-x--- 2 root root 4096 Aug 5 10:29 sys
-rw-r--r-- 1 root root 10485760 Aug 5 10:29 undo_001
-rw-r----- 1 root root 224626 Aug 5 09:07 undo_001.qp
-rw-r--r-- 1 root root 10485760 Aug 5 10:29 undo_002
-rw-r----- 1 root root 220474 Aug 5 09:07 undo_002.qp
-rw-r--r-- 1 root root 18 Aug 5 10:29 xtrabackup_binlog_info
-rw-r----- 1 root root 105 Aug 5 09:07 xtrabackup_binlog_info.qp
-rw-r----- 1 root root 95 Aug 5 09:07 xtrabackup_checkpoints
-rw-r--r-- 1 root root 529 Aug 5 10:29 xtrabackup_info
-rw-r----- 1 root root 486 Aug 5 09:07 xtrabackup_info.qp
-rw-r--r-- 1 root root 2560 Aug 5 10:29 xtrabackup_logfile
-rw-r----- 1 root root 342 Aug 5 09:07 xtrabackup_logfile.qp
-rw-r--r-- 1 root root 262 Aug 5 10:29 xtrabackup_tablespaces
-rw-r----- 1 root root 234 Aug 5 09:07 xtrabackup_tablespaces.qp
不保留原压缩文件，使用 --remove-original
[root@oracle compress]# xtrabackup --decompress --remove-original --target-dir=/xtrabackup/compress
查看目录文件
[root@oracle compress]# ll
total 56380
-rw-r--r-- 1 root root 476 Aug 5 10:32 backup-my.cnf
-rw-r--r-- 1 root root 155 Aug 5 10:32 binlog.000019
-rw-r--r-- 1 root root 16 Aug 5 10:32 binlog.index
drwxr-x--- 2 root root 4096 Aug 5 10:32 company
-rw-r--r-- 1 root root 3329 Aug 5 10:32 ib_buffer_pool
-rw-r--r-- 1 root root 12582912 Aug 5 10:32 ibdata1
drwxr-x--- 2 root root 4096 Aug 5 10:32 mysql
-rw-r--r-- 1 root root 24117248 Aug 5 10:32 mysql.ibd
drwxr-x--- 2 root root 12288 Aug 5 10:32 performance_schema
drwxr-x--- 2 root root 4096 Aug 5 10:32 sys
-rw-r--r-- 1 root root 10485760 Aug 5 10:32 undo_001
-rw-r--r-- 1 root root 10485760 Aug 5 10:32 undo_002
-rw-r--r-- 1 root root 18 Aug 5 10:32 xtrabackup_binlog_info
-rw-r----- 1 root root 95 Aug 5 09:07 xtrabackup_checkpoints
-rw-r--r-- 1 root root 529 Aug 5 10:32 xtrabackup_info
-rw-r--r-- 1 root root 2560 Aug 5 10:32 xtrabackup_logfile
-rw-r--r-- 1 root root 262 Aug 5 10:32 xtrabackup_tablespaces
5.流式备份(Streaming Backups)
Percona XtraBackup支持xbstream流模式将备份发送到STDOUT，而不是将文件复制到备份目录。这允许您使用其他程序过滤备份的输出，从而为备份的存储提供更大的灵活性。例如，通过输出管道的方式可以实现压缩且备份可以自动加密。
使用xbstream作为流选项，可以并行复制和压缩备份，这可以显着加快备份过程。 如果备份既压缩又加密，则需要先解密才能解压缩。
--并行压缩且并行复制备份
[root@oracle /]# xtrabackup --backup --socket=/tmp/mysql.sock --compress --compress-threads=8 --stream=xbstream --parallel=4 --target-dir=/xtrabackup/ >backup.xbstream
--查看结果
[root@oracle xtrabackup]# ll backup.xbstream
-rw-r--r-- 1 root root 3148078 Aug 6 15:01 backup.xbstream
恢复时必须先解压xbstream格式，再解压qb
--解压xbstream
[root@oracle /]# xbstream -x < backup.xbstream -C /xtrabackup/
--查看内容
[root@oracle xtrabackup]# ll
total 2920
-rw-r----- 1 root root 453 Aug 6 15:03 backup-my.cnf.qp
-rw-r----- 1 root root 183 Aug 6 15:03 binlog.000035.qp
-rw-r----- 1 root root 93 Aug 6 15:03 binlog.index.qp
drwxr-x--- 2 root root 4096 Aug 6 15:03 company
-rw-r----- 1 root root 945 Aug 6 15:03 ib_buffer_pool.qp
-rw-r----- 1 root root 315187 Aug 6 15:03 ibdata1.qp
drwxr-x--- 2 root root 4096 Aug 6 15:03 mysql
-rw-r----- 1 root root 2187997 Aug 6 15:03 mysql.ibd.qp
drwxr-x--- 2 root root 4096 Aug 6 15:03 performance_schema
drwxr-x--- 2 root root 4096 Aug 6 15:03 sys
-rw-r----- 1 root root 212804 Aug 6 15:03 undo_001.qp
-rw-r----- 1 root root 215761 Aug 6 15:03 undo_002.qp
-rw-r----- 1 root root 105 Aug 6 15:03 xtrabackup_binlog_info.qp
-rw-r----- 1 root root 95 Aug 6 15:03 xtrabackup_checkpoints
-rw-r----- 1 root root 498 Aug 6 15:03 xtrabackup_info.qp
-rw-r----- 1 root root 333 Aug 6 15:03 xtrabackup_logfile.qp
-rw-r----- 1 root root 234 Aug 6 15:03 xtrabackup_tablespaces.qp
--解压qp
[root@oracle /]# xtrabackup --decompress --remove-original --target-dir=/xtrabackup/
--查看内容
[root@oracle xtrabackup]# ll
total 56372
-rw-r--r-- 1 root root 476 Aug 6 15:04 backup-my.cnf
-rw-r--r-- 1 root root 155 Aug 6 15:04 binlog.000035
-rw-r--r-- 1 root root 16 Aug 6 15:04 binlog.index
drwxr-x--- 2 root root 4096 Aug 6 15:04 company
-rw-r--r-- 1 root root 3458 Aug 6 15:04 ib_buffer_pool
-rw-r--r-- 1 root root 12582912 Aug 6 15:04 ibdata1
drwxr-x--- 2 root root 4096 Aug 6 15:04 mysql
-rw-r--r-- 1 root root 24117248 Aug 6 15:04 mysql.ibd
drwxr-x--- 2 root root 4096 Aug 6 15:04 performance_schema
drwxr-x--- 2 root root 4096 Aug 6 15:04 sys
-rw-r--r-- 1 root root 10485760 Aug 6 15:04 undo_001
-rw-r--r-- 1 root root 10485760 Aug 6 15:04 undo_002
-rw-r--r-- 1 root root 18 Aug 6 15:04 xtrabackup_binlog_info
-rw-r----- 1 root root 95 Aug 6 15:03 xtrabackup_checkpoints
-rw-r--r-- 1 root root 544 Aug 6 15:04 xtrabackup_info
-rw-r--r-- 1 root root 2560 Aug 6 15:04 xtrabackup_logfile
-rw-r--r-- 1 root root 262 Aug 6 15:04 xtrabackup_tablespaces
6.加密备份（Encrypting Backups）
Percona XtraBackup支持使用xbstream选项加密和解密本地和流式备份，从而增加了另一层保护。使用GnuPG的libgcrypt库实现加密
加密
--encrypt-key选项
通过ssl生成密钥
[root@oracle xtrabackup]# openssl rand -base64 24
LymjcKljN4xxtPUe3GkF/4mNzj3x4y9D
加密时指定密钥
[root@oracle xtrabackup]# xtrabackup --backup --encrypt=AES256 --encrypt-key="LymjcKljN4xxtPUe3GkF/4mNzj3x4y9D" --target-dir=/xtrabackup/ --socket=/tmp/mysql.sock
查看备份的文件
[root@oracle xtrabackup]# ll
total 56468
-rw-r----- 1 root root 568 Aug 6 16:21 backup-my.cnf.xbcrypt
-rw-r----- 1 root root 247 Aug 6 16:21 binlog.000036.xbcrypt
-rw-r----- 1 root root 108 Aug 6 16:21 binlog.index.xbcrypt
drwxr-x--- 2 root root 4096 Aug 6 16:21 company
-rw-r----- 1 root root 3550 Aug 6 16:21 ib_buffer_pool.xbcrypt
-rw-r----- 1 root root 12600576 Aug 6 16:21 ibdata1.xbcrypt
drwxr-x--- 2 root root 4096 Aug 6 16:21 mysql
-rw-r----- 1 root root 24151104 Aug 6 16:21 mysql.ibd.xbcrypt
drwxr-x--- 2 root root 4096 Aug 6 16:21 performance_schema
drwxr-x--- 2 root root 4096 Aug 6 16:21 sys
-rw-r----- 1 root root 10500480 Aug 6 16:21 undo_001.xbcrypt
-rw-r----- 1 root root 10500480 Aug 6 16:21 undo_002.xbcrypt
-rw-r----- 1 root root 110 Aug 6 16:21 xtrabackup_binlog_info.xbcrypt
-rw-r----- 1 root root 95 Aug 6 16:21 xtrabackup_checkpoints
-rw-r----- 1 root root 595 Aug 6 16:21 xtrabackup_info.xbcrypt
-rw-r----- 1 root root 2744 Aug 6 16:21 xtrabackup_logfile.xbcrypt
-rw-r----- 1 root root 354 Aug 6 16:21 xtrabackup_tablespaces.xbcrypt
Tips:
通过使用--encrypt-threads选项，可以指定多个线程并行使用加密。选项--encrypt-chunk-size可用于指定每个加密线程的工作加密缓冲区的大小（以字节为单位）（默认为64K）。
解密
--decrypt选项
执行解密，如果不想保留原压缩文件，可以使用 --remove-original 命令
[root@oracle xtrabackup]# xtrabackup --remove-original --decrypt=AES256 --encrypt-key="LymjcKljN4xxtPUe3GkF/4mNzj3x4y9D" --target-dir=/xtrabackup/
查看备份文件
[root@oracle xtrabackup]# ll
total 56380
-rw-r--r-- 1 root root 476 Aug 6 16:25 backup-my.cnf
-rw-r--r-- 1 root root 155 Aug 6 16:25 binlog.000036
-rw-r--r-- 1 root root 16 Aug 6 16:25 binlog.index
drwxr-x--- 2 root root 4096 Aug 6 16:25 company
-rw-r--r-- 1 root root 3458 Aug 6 16:25 ib_buffer_pool
-rw-r--r-- 1 root root 12582912 Aug 6 16:25 ibdata1
drwxr-x--- 2 root root 4096 Aug 6 16:25 mysql
-rw-r--r-- 1 root root 24117248 Aug 6 16:25 mysql.ibd
drwxr-x--- 2 root root 12288 Aug 6 16:25 performance_schema
drwxr-x--- 2 root root 4096 Aug 6 16:25 sys
-rw-r--r-- 1 root root 10485760 Aug 6 16:25 undo_001
-rw-r--r-- 1 root root 10485760 Aug 6 16:25 undo_002
-rw-r--r-- 1 root root 18 Aug 6 16:25 xtrabackup_binlog_info
-rw-r----- 1 root root 95 Aug 6 16:21 xtrabackup_checkpoints
-rw-r--r-- 1 root root 503 Aug 6 16:25 xtrabackup_info
-rw-r--r-- 1 root root 2560 Aug 6 16:25 xtrabackup_logfile
-rw-r--r-- 1 root root 262 Aug 6 16:25 xtrabackup_tablespaces
Tips:
--parallel可以与--decrypt选项一起使用来同时解密多个文件。
7.部分备份和恢复( 不建议 )
部分备份
xtrabackup支持部分备份，前提是innodb_file_per_table选项被启用。可以通过三种方式进行部分备份：
1.使用正则表达式匹配表名称
2.将包含表名称的列表放入一个文件中
3.数据库名称列表
重要提示
关于部分备份只有一个注意事项： 不要复制准备好的备份。应该通过导入表的方式来恢复部分备份，而不是使用--copy-back选项。 尽管在某些情况下可以通过复制文件来进行恢复，但这可能导致数据库在许多情况下出现不一致，强烈不建议通过这种方式去恢复。
--tables选项
第一种方式是通过xtrabackup --tables选项。该选项的值是一个正则表达式，用于完全匹配数据库和表名称，格式为database.table。
只备份company数据库下的所有表，可以使用如下命令
[root@oracle ~]# xtrabackup --socket=/tmp/mysql.sock --backup --target-dir=/xtrabackup/company --tables="^company[.].*"
只备份company下的t1表，可以使用如下命令
[root@oracle ~]# xtrabackup --socket=/tmp/mysql.sock --backup --target-dir=/xtrabackup/company_t1 --tables="^company[.]t1"
--tables-file选项
该命令指定某个文件，可以文件包含了多个要备份的表名，每行一个表名。只有在该文件中的表才会被备份。表名必须完全匹配，区分大小写，表名不能包含任何表达式。表名必须完全匹配database.table格式
查看文件内容
[root@oracle tmp]# cat /tmp/tables.txt
company.t1
company.t2
备份命令
[root@oracle tmp]# xtrabackup --socket=/tmp/mysql.sock --backup --tables-file=/tmp/tables.txt --target-dir=/xtrabackup/partial_tables
--databases选项
xtrabackup --databases支持以空格分隔的数据库和表列表，格式为database[.table]。除此列表以外，请确保mysql, sys, performance_schema三个数据库也包含在内，这些数据库在恢复时需要使用xtrabackup --copy-back选项来恢复。
[root@oracle tmp]# xtrabackup --databases='mysql sys performance_schema ...'
-databases-file选项
该命令指定某个文件，可以文件包含了多个要备份的表名，每行一个表名。只有在该文件中的数据库和表才会被备份。表名必须完全匹配，区分大小写，表名不能包含任何表达式。表名必须完全匹配database[.table]格式
部分恢复
在进行部分备份的恢复时，使用xtrabackup --prepare选项时，将会提示不存在该表的警告，这是因为这些表虽然存在于InnoDB的数据字典中，但是相应的.ibd文件不存在。他们还未被复制到备份目录中，当你恢复备份并启动InnoDB时，这些表将会从数据字典中删除，这时在日志文件中才不会出现任何错误或警告。
该过程类似于还原单个表：应用日志并使用--export选项：
[root@oracle xtrabackup]# xtrabackup --prepare --export --target-dir=/xtrabackup/company_t1/
[root@oracle xtrabackup]# ll /xtrabackup/company_t1/company
-rw-r--r-- 1 root root 581 Aug 6 13:27 t1.cfg
-rw-r----- 1 root root 114688 Aug 6 13:27 t1.ibd
1.将t1表offline
mysql> alter table company.t1 discard tablespace;
2.cp拷贝
[root@oracle company]# cp /xtrabackup/company_t1/company/* /usr/local/mysql/data/company/
3.授权
[root@oracle company]# chown -R mysql.mysql /usr/local/mysql/data/company/t1.*
4.将t1表online
mysql> alter table company.t1 import tablespace;
BUG问题：
笔者在成功恢复后，又再次对该t1表重新进行部分备份和恢复，在部分恢复的时候，产生了如下 BUG ，所以不建议使用该功能进行备份和恢复
[root@oracle company]# xtrabackup --prepare --export --target-dir=/xtrabackup/company_t1/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=1 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
xtrabackup: recognized client arguments: --prepare=1 --export=1 --target-dir=/xtrabackup/company_t1/
xtrabackup version 8.0.6 based on MySQL server 8.0.14 Linux (x86_64) (revision id: c0a2d91)
xtrabackup: auto-enabling --innodb-file-per-table due to the --export option
xtrabackup: cd to /xtrabackup/company_t1/
xtrabackup: This target seems to be not prepared yet.
Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(20169689)
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 = 1
xtrabackup: innodb_log_file_size = 8388608
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 = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
PUNCH HOLE support available
Mutexes and rw_locks use GCC atomic builtins
Uses event mutexes
GCC builtin __atomic_thread_fence() is used for memory barrier
Compressed tables use zlib 1.2.3
Number of pools: 1
Using CPU crc32 instructions
Directories to scan '.;.;.'
Scanning './'
Completed space ID check of 4 files.
Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
Completed initialization of buffer pool
page_cleaner coordinator priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
The log sequence number 20140501 in the system tablespace does not match the log sequence number 20169689 in the ib_logfiles!
Database was not shutdown normally!
Starting crash recovery.
Starting to parse redo log at lsn = 20169366, whereas checkpoint_lsn = 20169689
Doing recovery: scanned up to log sequence number 20169709
Log background threads are being started...
Applying a batch of 1 redo log records ...
100%
Apply batch completed!
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
Creating shared tablespace for temporary tables
Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
File './ibtmp1' size is now 12 MB.
Scanning temp tablespace dir:'./#innodb_temp/'
Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
8.0.14 started; log sequence number 20169709
Allocated tablespace ID 10 for company/t1, old maximum was 0
InnoDB: Assertion failure: dict0dict.cc:1215:table2 == NULL
InnoDB: thread 140589081102528InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to https://jira.percona.com/projects/PXB .
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: about forcing recovery.
05:34:21 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.
key_buffer_size=0
read_buffer_size=131072
max_used_connections=0
max_threads=0
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1676 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x5ed7650
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7fff80e30ba8 thread_stack 0x46000
xtrabackup(my_print_stacktrace(unsigned char*, unsigned long)+0x2e) [0x1fc2d9e]
xtrabackup(handle_fatal_signal+0x413) [0xfe8883]
/lib64/libpthread.so.0() [0x3720a0f7e0]
/lib64/libc.so.6(gsignal+0x35) [0x3720632495]
/lib64/libc.so.6(abort+0x175) [0x3720633c75]
xtrabackup(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x9b) [0x141105b]
xtrabackup(dict_table_add_to_cache(dict_table_t*, unsigned long, mem_block_info_t*)+0x11c) [0x115b2fc]
xtrabackup(dd_table_create_on_dd_obj(dd::Table const*, dd::Partition const*, std::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const*, bool)+0x16f7) [0x117e897]
xtrabackup(dd_table_load_part(unsigned long, dd::Table const&, dd::Partition const*, dict_table_t*&, THD*, std::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const*, bool)+0x40e) [0x117f7fe]
xtrabackup(dd_table_load_on_dd_obj(dd::cache::Dictionary_client*, unsigned int, dd::Table const&, dict_table_t*&, THD*, std::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const*, bool)+0x50) [0x117f870]
xtrabackup(dict_load_tables_from_space_id(unsigned int, THD*, trx_t*)+0x685) [0xba5805]
xtrabackup() [0xba5ebd]
xtrabackup() [0xbad711]
xtrabackup(main+0x704) [0xb6f134]
/lib64/libc.so.6(__libc_start_main+0xfd) [0x372061ed1d]
xtrabackup() [0xb9b9a5]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 0
Status: NOT_KILLED
Please report a bug at https://jira.percona.com/projects/PX
MySQL8.0之XtraBackup_ITPUB博客 http://blog.itpub.net/29812844/viewspace-2658452/
