Percona XtraBackup 8.0安装
Percona XtraBackup 8.0 针对MySQL8.0, XtraBackup 2.4继续支持MySQL 5.6 和 5.7。
移除了innobackupex命令；由于新的MySQL重做日志和数据字典格式，8.0版本只支持mysql8.0和percona8.0；早于mysql8.0的版本需要使用xtrabackup2.4备份和恢复.
我们应该特别小心，这个xtrabackup version 运行的基础是MySQL server 8.0.13。如果，MySQL的版本是之前的版本，在恢复阶段会报错：
xtrabackup: Error: cannot open file './xtrabackup_tablespaces'xtrabackup: Error: failed to load tablespaces list.
It is possible that the backup was created by Percona XtraBackup 2.4 or earlier version. Please use the same XtraBackup version to restore.
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-8.0.4/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.4-1.el7.x86_64.rpm
yum localinstall percona-xtrabackup-80-8.0.4-1.el7.x86_64.rpm
rpm -qa | grep -i xtrabackup
xtrabackup --version
--------------------------------------------------------------------------------------------------------------------------
#percona xtrabackup8.0.4备份恢复mysql8.0.12
mkdir -p /backup/mysql/data
mkdir -p /backup/mysql/datainc
一、全备
#target-dir指定备份目录,mysql8.0不支持percona-xtrabackup-24
xtrabackup --defaults-file=/data/mysqldata/3306/my.cnf --user=root --password=oracle --backup --target-dir=/backup/mysql/data
#或者使用参数--datadir替换掉参数--defaults-file.
rm -rf /backup/mysql/*
#xtrabackup --host=localhost --user=root --password=oracle --port=3306 --datadir=/data/mysqldata/3306/data/ --backup --target-dir=/backup/mysql/data
mysql -uroot -poracle
show databases;
flush logs;
create database test1 character set utf8;
create table test1.t1(id int,name varchar(200));
insert into test1.t1 select 1,'n1';
select * from test1.t1;
#从事件中重构伪sql语句，-vv显示字段的元数据注释信息，-v不显示注释信息
mysqlbinlog mysqlmaster-bin.000034 -vv
-----------------------------------
#通过binlog恢复到某个时间点
#准备备份集：
innobackupex --apply-log /apps/backup/xtrabackup_20160517010001
innobackupex --defaults-file=/etc/my3306.cnf --copy-back /apps/backup/xtrabackup_20160517010001
#启动数据库实例:
/usr/local/src/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf --user=mysql &
#进入最后一次备份的全备或增量备份目录，根据binlog_pos 的filename和position，拷贝之后的binlog文件到目标机器
cat xtrabackup_info
cat xtrabackup_binlog_info
#time显示执行耗时
#--base64-output=decode-rows表示不显示binglog部分
#若备份完成后进行了flush logs，则--start-position=154可能需要指定为下一个日志
time mysqlbinlog --no-defaults --start-position=154 -vv mysqlmaster-bin.000033 | mysql -uroot -pmysql
#指定结束时间点
time mysqlbinlog --no-defaults --stop-datetime='2020-12-05 02:39:59' -vv mysqlmaster-bin.000034 | mysql -uroot -pmysql
-------------------------
#以上操作失败，实际操作如下
#环境：mysql8.0.12，xtrabackup8.0.4
xtrabackup全量备份+binlog基于时间点恢复
1.通过xtrabackup的备份恢复数据库。
2.找到start-position和binlog名称
cat xtrabackup_info
3.导出mysqlbinlog为sql文件，并确定恢复的时间点
mysqlbinlog --no-defaults --start-position=124 --stop-datetime='2020-12-04 16:49:40' -vv mysql-bin.000082 > backup2.sql
4.导入sql
source /backup/mysql/binlog/backup2.sql
------------------------------
#如下为报错参考
#报错：ERROR 1837 (HY000): When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value
解决：删除backup2.sql中含有GTID_NEXT的行
#需要先开启再禁用
set global gtid_mode='on_permissive';
set global gtid_mode='off_permissive';
#如下未报错，但数据未恢复
#--base64-output=decode-rows表示不显示binglog部分
#--start-position=154指定为全量备份日志时的下一个日志仍然未恢复成功
time mysqlbinlog --start-position=355 -vv mysql-bin.000087 | mysql -uroot -poracle
time mysqlbinlog --stop-datetime='2020-12-05 00:06:59' -vv mysql-bin.000088 | mysql -uroot -poracle
#传入mysql恢复报错，未解决：ERROR 1049 (42000) at line 45: Unknown database 'test'
mysqlbinlog --no-defaults --start-position=513 --stop-datetime='2020-12-04 16:45:37' -vv mysql-bin.000082 | mysql -uroot -poracle
#恢复过程中遇到的问题分享
innobackupex恢复操作解压备份文件报错一例:http://blog.csdn.net/zengxuewen2045/article/details/51446789
innobackupex恢复操作报错两例:http://blog.csdn.net/zengxuewen2045/article/details/51446770
记一次xtrabackup工具恢复后mysqld_safe启动问题:http://blog.csdn.net/zengxuewen2045/article/details/51446754
利用xtrabackup和binlog增量恢复时提示表记录不存在案例:http://blog.csdn.net/zengxuewen2045/article/details/51454290
二、从全备恢复
#1、停止掉运行的数据库实例：
ps -ef | grep mysql
kill -9
#systemctl stop mysqld
rm -rf /data/mysqldata/3306/data/*
#2、准备：
xtrabackup --host=localhost --user=root --password=oracle --port=3306 --prepare --target-dir=/backup/mysql/data/
#3、拷回数据：
xtrabackup --host=localhost --user=root --password=oracle --port=3306 --datadir=/data/mysqldata/3306/data --copy-back --target-dir=/backup/mysql/data/
#4、修改目录属性启动数据库：
chown -R mysql:mysql /data/mysqldata/3306/data
chmod -R 755 /data/mysqldata/3306/data
#5、启动数据库实例:
/usr/local/src/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf --user=mysql &
mysql -uroot -poracle
show databases;
#systemctl start mysqld
------------------------------------------------------------
三、增量备份（前提是之前已经按步骤一进行了全备）
rm -rf /backup/mysql/datainc/*
xtrabackup --defaults-file=/data/mysqldata/3306/my.cnf --host=localhost --user=root --password=oracle --port=3306 --backup --parallel=3 --target-dir=/backup/mysql/datainc --incremental-basedir=/backup/mysql/data
create table test3.t2(id int,name varchar(200));
insert into test3.t2 select 1,'n1';
#第2次增量备份
create database test character set utf8;
use test3;
create table test.t1(id int,name varchar(200));
insert into test.t1 select 1,'n1';
xtrabackup --defaults-file=/data/mysqldata/3306/my.cnf --host=localhost --user=root --password=oracle --port=3306 --backup --parallel=3 --target-dir=/backup/mysql/datainc1 --incremental-basedir=/backup/mysql/datainc
[root@node1 ~]# ll /backup/mysql/data
total 2142240
-rw-r----- 1 root root 507 Nov 25 11:42 backup-my.cnf
-rw-r----- 1 root root 2147483648 Nov 25 11:42 ibdata1
drwxr-x--- 2 root root 143 Nov 25 11:42 mysql
-rw-r----- 1 root root 25165824 Nov 25 11:42 mysql.ibd
drwxr-x--- 2 root root 4096 Nov 25 11:42 performance_schema
drwxr-x--- 2 root root 28 Nov 25 11:42 sys
drwxr-x--- 2 root root 22 Nov 25 11:42 test3
-rw-r----- 1 root root 10485760 Nov 25 11:42 undo_001
-rw-r----- 1 root root 10485760 Nov 25 11:42 undo_002
-rw-r----- 1 root root 184 Nov 25 11:42 xtrabackup_binlog_info
-rw-r----- 1 root root 79 Nov 25 11:42 xtrabackup_checkpoints
-rw-r----- 1 root root 735 Nov 25 11:42 xtrabackup_info
-rw-r----- 1 root root 2560 Nov 25 11:42 xtrabackup_logfile
-rw-r----- 1 root root 39 Nov 25 11:42 xtrabackup_tablespaces
[root@node1 ~]# ll /backup/mysql/datainc/
total 1744
-rw-r----- 1 root root 513 Nov 25 11:44 backup-my.cnf
-rw-r----- 1 root root 917504 Nov 25 11:44 ibdata1.delta
-rw-r----- 1 root root 44 Nov 25 11:44 ibdata1.meta
drwxr-x--- 2 root root 143 Nov 25 11:44 mysql
-rw-r----- 1 root root 638976 Nov 25 11:44 mysql.ibd.delta
-rw-r----- 1 root root 53 Nov 25 11:44 mysql.ibd.meta
drwxr-x--- 2 root root 4096 Nov 25 11:44 performance_schema
drwxr-x--- 2 root root 61 Nov 25 11:44 sys
drwxr-x--- 2 root root 88 Nov 25 11:44 test3
-rw-r----- 1 root root 98304 Nov 25 11:44 undo_001.delta
-rw-r----- 1 root root 53 Nov 25 11:44 undo_001.meta
-rw-r----- 1 root root 81920 Nov 25 11:44 undo_002.delta
-rw-r----- 1 root root 53 Nov 25 11:44 undo_002.meta
-rw-r----- 1 root root 186 Nov 25 11:44 xtrabackup_binlog_info
-rw-r----- 1 root root 84 Nov 25 11:44 xtrabackup_checkpoints
-rw-r----- 1 root root 808 Nov 25 11:44 xtrabackup_info
-rw-r----- 1 root root 2560 Nov 25 11:44 xtrabackup_logfile
-rw-r----- 1 root root 39 Nov 25 11:44 xtrabackup_tablespaces
四、从增量备份恢复
#1、停止掉运行的数据库实例：
ps -ef | grep mysql
kill -9
#systemctl stop mysqld
rm -rf /data/mysqldata/3306/data/*
#2、 准备全备份的日志：
xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/data
#3、准备增量备份的日志：
xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/data --incremental-dir=/backup/mysql/datainc
xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/data --incremental-dir=/backup/mysql/datainc1
#4、全备份准备：
xtrabackup --prepare --target-dir=/backup/mysql/data
Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
xtrabackup: Last MySQL binlog file position 883, file name mysql-bin.000062
#可恢复到最后一次增量备份；也可恢复到第一次全备份；若恢复到最后一次增量备份后不能再恢复到第一次全量备份
#若全量恢复后再进行增量恢复，则报错，忽略：
Tablespace 'mysql' Page [page id: space=4294967294, page number=5] log sequence number 20236845 is in the future! Current system log sequence number 20214284.
Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
xtrabackup: Last MySQL binlog file position 883, file name mysql-bin.000062
xtrabackup: error: The transaction log file is corrupted.
xtrabackup: error: The log was not applied to the intended LSN!
xtrabackup: Log applied to lsn 20214284
xtrabackup: The intended lsn is 20236845
#5、拷回数据：
xtrabackup --host=localhost --user=root --password=oracle --port=3306 --datadir=/data/mysqldata/3306/data --copy-back --target-dir=/backup/mysql/data/
#6、修改数据目录的权限和属性：
chown -R mysql:mysql /data/mysqldata/3306/data
chmod -R 755 /data/mysqldata/3306/data
ll /data/mysqldata/3306/data
#7、 启动实例：
/usr/local/src/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf --user=mysql &
#systemctl start mysqld