xtrabackup备份异地恢复+binlog日志应用

----------------------------------------异机恢复----------------------------
1.获取到xtrbackup备份文件和binlog日志
我这里将原数据库的备份文件和binlog下载到了恢复的机器上面
[root@localhost hnbak]# ls -al
total 423684
drwxr-xr-x  2 root root       166 Apr 30 09:04 .
drwxr-xr-x. 6  631  503      4096 Apr 30 09:02 ..
-rw-r--r--  1 root root       421 Apr 21 21:28 binlog.000001
-rw-r--r--  1 root root      2195 Apr 22 14:35 binlog.000002
-rw-r--r--  1 root root 104857829 Apr 22 18:26 binlog.000003
-rw-r--r--  1 root root 104857895 Apr 22 18:32 binlog.000004
-rw-r--r--  1 root root 104857667 Apr 22 18:37 binlog.000005
-rw-r--r--  1 root root  60155334 Apr 25 17:32 binlog.000006
-rw-r--r--  1 root root  59096336 Apr 25 01:00 xtrabackup_20210425.tar.gz

2.安装mysql
在恢复的机器上安装与原库相同版本的mysql
安装步骤可以参考如下连接
https://www.cnblogs.com/hxlasky/p/14633613.html

3.安装xtrabackup软件
恢复机器上安装的xtrabackup软件建议保持与备份的机器的软件版本一致
[root@localhost soft]#tar -xvf percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz
[root@localhost soft]#mv percona-xtrabackup-2.4.7-Linux-x86_64 /opt/xtrabackup247

4.停掉恢复机器上的数据库
若步骤2安装的mysql在运行的话,先停掉
[root@localhost mysql5729]# /opt/mysql5729/bin/mysqladmin -h localhost -uroot -pmysql -S /opt/mysql5729/mysql.sock shutdown

5.清空数据文件目录
[root@localhost mysql5729]# cd /opt/mysql5729
[root@localhost mysql5729]# mv data bakdata
[root@localhost mysql5729]# mkdir data

6.解压从原库下载的备份文件
[root@localhost soft]# cd /soft/hnbak
[root@localhost hnbak]# mkdir xtraback_file
[root@localhost hnbak]# mv xtrabackup_20210425.tar.gz ./xtraback_file/
[root@localhost hnbak]# cd xtraback_file/
[root@localhost xtraback_file]# tar -xvf xtrabackup_20210425.tar.gz
[root@localhost xtraback_file]# rm xtrabackup_20210425.tar.gz ##解压后即可删除

7.恢复
root用户操作
cd /opt/xtrabackup247/bin
./innobackupex --defaults-file=/opt/mysql5729/conf/my.cnf --user=root --apply-log /soft/hnbak/xtraback_file
./innobackupex --defaults-file=/opt/mysql5729/conf/my.cnf --user=root --copy-back --rsync /soft/hnbak/xtraback_file

上面的两个步骤成功后分别都会有成功提示:
210430 10:00:33 completed OK!



8.修改权限
[root@localhost bin]# cd /opt
[root@localhost opt]# chown -R mysql:mysql ./mysql5729

9.启动数据库
[root@localhost opt]# su - mysql
[mysql@localhost bin]$cd /opt/mysql5729/bin
[mysql@localhost bin]$ ./mysqld_safe --defaults-file=/opt/mysql5729/conf/my.cnf --user=mysql &

10.登陆数据库
操作系统root下面登陆
/opt/mysql5729/bin/mysql -h localhost -uroot -psourepasswd -S /opt/mysql5729/mysql.sock
注意这里的数据库账号密码是原库的密码,若是密码忘记了,只能采用跳过密码检查项的配置启动数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_vaccination     |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

-----------------------binlog日志应用----------------------------
1.查看上面步骤恢复的日志信息
[root@localhost xtraback_file]# cd /soft/hnbak/xtraback_file
[root@localhost xtraback_file]# more xtrabackup_binlog_info
binlog.000006   60142378        8f4f3e71-a2a4-11eb-9bfa-fa163e803f9a:1-825251

我们要应用该位置之后的日志

2.日志应用
应用binlog.000006
[root@localhost xtraback_file]#/opt/mysql5729/bin/mysqlbinlog /soft/hnbak/binlog.000006 --start-position="60142378"|/opt/mysql5729/bin/mysql -h localhost -uroot -psourepasswd -S /opt/mysql5729/mysql.sock

若是有更多的日志,可以继续应用,直到最新的日志文件,达到完全恢复的目的
应用binlog.000007
[root@localhost xtraback_file]#/opt/mysql5729/bin/mysqlbinlog /soft/hnbak/binlog.000007|/opt/mysql5729/bin/mysql -h localhost -uroot -psourepasswd -S /opt/mysql5729/mysql.sock

3.检查业务数据

mysql> select count(1) from region_ext_info;
+----------+
| count(1) |
+----------+
|   706941 |
+----------+
1 row in set (0.67 sec)

mysql> select count(1) from hospital_info;
+----------+
| count(1) |
+----------+
|   112393 |
+----------+
1 row in set (0.09 sec)

mysql> select count(1) from document;
+----------+
| count(1) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)