xtrabackup的全量备份及增量备份
[root@localhost ~]# innobackupex --user=root --password=123456 xtrabackup/full/
[root@localhost full]# ls /root/xtrabackup/full/2021-05-23_21-40-02/
backup-my.cnf ibdata1 sys xtrabackup_logfile
db1 mysql xtrabackup_checkpoints
ib_buffer_pool performance_schema xtrabackup_info
## --incremental 选项是指定你增量备份文件存放的位置 ## --incremental-basedir 选项是指定你索要增量备份的基础备份 ##增量备份需要你之前有一次全量备份，之后在这个全量备份的基础上增量备份，当然也可以使用是上次的增量备份做基准 ##先做一次全量备份 [root@localhost ~]# innobackupex --user=root --password=123456 xtrabackup/full/ ##做一次增量备份 [root@localhost ~]# innobackupex --user=root --password=123456 --incremental xtrabackup/incremental --incremental-basedir=/root/xtrabackup/full/2021-08-22_22-49-39/ ##在上次的增量备份基础上再做增量备份 [root@localhost ~]# innobackupex --user=root --password=123456 --incremental xtrabackup/incremental --incremental-basedir=/root/xtrabackup/incremental/2021-08-22_22-52-03/ ##查看存放的备份文件 [root@localhost ~]# ls xtrabackup/incremental/2021-08-22_22-5* xtrabackup/incremental/2021-08-22_22-52-03: backup-my.cnf ibdata1.delta performance_schema xtrabackup_binlog_info xtrabackup_logfile db1 ibdata1.meta sys xtrabackup_checkpoints ib_buffer_pool mysql xtrabackup xtrabackup_info xtrabackup/incremental/2021-08-22_22-53-46: backup-my.cnf ibdata1.delta performance_schema xtrabackup_binlog_info xtrabackup_logfile db1 ibdata1.meta sys xtrabackup_checkpoints ib_buffer_pool mysql xtrabackup xtrabackup_info
xtrabackup的全量恢复及增量恢复
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# rm -fr /var/lib/mysql/*
[root@localhost ~]# rm -fr /var/log/mysql.log
[root@localhost ~]# rm -fr /var/log/mysql-slow/slow.log
[root@localhost ~]# innobackupex --apply-log xtrabackup/full/2021-05-23_21-40-02/
[root@localhost ~]# innobackupex --copy-back xtrabackup/full/2021-05-23_21-40-02/
[root@localhost ~]# chown mysql:mysql /var/lib/mysql -R
[root@localhost ~]# systemctl start mysqld
查看binlog二进制日志的内容：
[root@localhost ~]# mysqlbinlog -vv mysql-bin.000001 | less
数据恢复时，要求的时间格式：
xxxx（年）-xx（月）-xx（日） 空格 时:分:秒
2020-05-25 15:16:00 比开始写数据，小1-2秒
2020-05-25 15:16:18 比结束写数据，大1-2秒
使用二进制日志进行数据恢复：
[root@localhost ~]# mysqlbinlog --start-datetime="2021-05-23 21:47:11" --stop-datetime="2021-05-23 21:48:35" mysql-bin.000001 | mysql -u root -p
增量恢复的基础是全量备份，所以在使用增量前是需要先使用全量；
恢复的顺序是先添加全量，之后再依次合并增量，然后再执行恢复；
##同样还是先破坏数据库，删除文件 [root@localhost ~]# rm -fr /var/lib/mysql/* ##先添加全量备份 [root@localhost ~]# innobackupex --apply-log --redo-only xtrabackup/full/2021-08-22_22-49-39/ ##添加合并第一次增量 [root@localhost ~]# innobackupex --apply-log --redo-only --incremental xtrabackup/full/2021-08-22_22-49-39/ --incremental-dir=xtrabackup/incremental/2021-08-22_22-52-03/ ##添加合并第二次增量 [root@localhost ~]# innobackupex --apply-log --redo-only --incremental xtrabackup/full/2021-08-22_22-49-39/ --incremental-dir=xtrabackup/incremental/2021-08-22_22-53-46/ ##执行恢复 [root@localhost ~]# innobackupex --copy-back xtrabackup/full/2021-08-22_22-49-39/ ##修改文件权限,/var/lib/mysql下的文件均需要改为mysql权限，否则不能启动，完成后重启mysqld [root@localhost ~]# chown mysql:mysql /var/lib/mysql/* -R [root@localhost ~]# systemctl stop mysqld [root@localhost ~]# systemctl start mysqld
xtrabackup的2.4及8.0版本安装
官网地址：
MySQL8.0+版本：Download Percona XtraBackup 8.0
MySQL5.x+版本：Download Percona XtraBackup 2.4
[root@MySQL-120 opt]# ls -lh Percona-XtraBackup-*
-rw-r--r-- 1 root root 62M May 23 14:22 Percona-XtraBackup-2.4.22-rc99a781-el7-x86_64-bundle.tar
-rw-r--r-- 1 root root 428M May 23 14:22 Percona-XtraBackup-8.0.23-16-r934bc8f-el7-x86_64-bundle.tar
打开tar包
[root@MySQL-120 opt]# tar -xvf Percona-XtraBackup-8.0.23-16-r934bc8f-el7-x86_64-bundle.tar
[root@MySQL-120 opt]# ls -lh *.rpm
-rw-r--r-- 1 root root 38K May 23 14:22 libev-4.04-2.el6.x86_64.rpm
-rw-rw-r-- 1 root root 14M Mar 16 23:19 percona-xtrabackup-80-8.0.23-16.1.el7.x86_64.rpm
-rw-rw-r-- 1 root root 194M Mar 16 23:19 percona-xtrabackup-80-debuginfo-8.0.23-16.1.el7.x86_64.rpm
-rw-rw-r-- 1 root root 222M Mar 16 23:19 percona-xtrabackup-test-80-8.0.23-16.1.el7.x86_64.rpm
安装xtrabackup
##这是我在安装8.0版本时的报错 [root@MySQL-120 opt]# rpm -ivh percona-xtrabackup-80-8.0.23-16.1.el7.x86_64.rpm warning: percona-xtrabackup-80-8.0.23-16.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY error: Failed dependencies: perl(DBD::mysql) is needed by percona-xtrabackup-80-8.0.23-16.1.el7.x86_64 perl(Digest::MD5) is needed by percona-xtrabackup-80-8.0.23-16.1.el7.x86_64 rsync is needed by percona-xtrabackup-80-8.0.23-16.1.el7.x86_64 ##这是我在安装2.4版本时的报错 [root@localhost ~]# rpm -ivh percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm warning: percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY error: Failed dependencies: libev.so.4()(64bit) is needed by percona-xtrabackup-24-2.4.22-1.el7.x86_64 perl(DBD::mysql) is needed by percona-xtrabackup-24-2.4.22-1.el7.x86_64 perl(Digest::MD5) is needed by percona-xtrabackup-24-2.4.22-1.el7.x86_64
1、安装时报错：Requires: libmysqlclient.so.18()(64bit)
##这里时需要你安装 mysql-community-libs-compat ，这个包在官网可以下到，也可以看看之前安装MySQL的rpm包解压的文件， ..-bundle.tar中就有； [root@localhost opt]# rpm -ivh mysql-community-libs-compat-5.7.32-1.el7.x86_64.rpm warning: mysql-community-libs-compat-5.7.32-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-libs-compat-5.7.3################################# [100%]
2、安装时报错：libev.so.4()(64bit) is needed by percona-xtrabackup-24-2.4.22-1.el7.x86_64
依赖包下载地址：http://rpmfind.net/linux/atrpms/el6-x86_64/atrpms/stable/libev-4.04-2.el6.x86_64.rpm
[root@MySQL-120 opt]# wget http://rpmfind.net/linux/atrpms/el6-x86_64/atrpms/stable/libev-4.04-2.el6.x86_64.rpm
[root@MySQL-120 opt]# rpm -ivh libev-4.04-2.el6.x86_64.rpm
warning: libev-4.04-2.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 66534c2b: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:libev-4.04-2.el6 ################################# [100%]
3、安装时报错：perl(DBD::mysql) is needed by percona-xtrabackup- ... ...
##直接 yum 执行安装也会帮你下载需要的 mysql-community-libs-compat 安装包
##我在安装2.4版本的时候安装提示我：Requires: libmysqlclient.so.18()(64bit)，也是就解决依赖环境中的第一个问题，需要的话，我们也可能要手动安装 mysql-community-libs-compat；
[root@MySQL-120 opt]# yum install -y perl-DBD-MySQL
4、安装时报错：perl(Digest::MD5) is needed by percona-xtrabackup- ... ...
[root@MySQL-120 opt]# yum install -y perl perl-Digest-MD5
5、安装时报错：rsync is needed by percona-xtrabackup-80-8.0.23-16.1.el7.x86_64
[root@MySQL-120 opt]# yum install -y rsync
解决依赖环境后安装xtrabackup
##成功安装8.0版本 [root@MySQL-120 opt]# rpm -ivh percona-xtrabackup-80-8.0.23-16.1.el7.x86_64.rpm warning: percona-xtrabackup-80-8.0.23-16.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:percona-xtrabackup-80-8.0.23-16.1################################# [100%] ##成功安装2.4版本 [root@localhost ~]# rpm -ivh percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm warning: percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:percona-xtrabackup-24-2.4.22-1.el################################# [100%]
