xtrabackup8.0备份和异地恢复搭建从库

环境:

OS:Centos 7

 

1.备份
/opt/xtrabackup-8.0.7/bin/xtrabackup --defaults-file=/opt/mha/mysql8/conf/my.cnf --host=localhost --user=root --password=mysql --port=13306 --socket=/opt/mha/mysql8/mysql.sock --backup --target-dir=/opt/mha/xbackup/

2.将备份文件目录打包传到目的机器
[root@localhost mha]# cd /opt/mha
[root@localhost mha]# tar -cvf xbackup.tar ./xbackup/
[root@localhost mha]# scp xbackup.tar root@192.168.1.135:/opt/mha/

3.恢复前期准备
3.1 解压文件
[root@localhost mha]# tar -xvf xbackup.tar

3.2 停掉目的机器上的mysql
/opt/mha/mysql8/bin/mysqladmin -h 127.0.0.1 -uroot -pmysql -P13306 -S /opt/mha/mysql8/mysql.sock shutdown

3.2 备份之前的数据文件目录,并创建一个空目录
[root@localhost mysql8]# cd /opt/mha/mysql8
[root@host02 mysql5718]# mv data bakdata
[root@host02 mysql5718]# mkdir data
这里要是binlog日志不是放在data目录的话,需要将binlog日志要删除掉

4.xtrabackup恢复
/opt/xtrabackup-8.0.7/bin/xtrabackup --defaults-file=/opt/mha/mysql8/conf/my.cnf --user=root --prepare --target-dir=/opt/mha/xbackup/
/opt/xtrabackup-8.0.7/bin/xtrabackup --defaults-file=/opt/mha/mysql8/conf/my.cnf --user=root --copy-back --target-dir=/opt/mha/xbackup/

5.修改目录属性启动数据库：
[root@localhost mysql8]# cd /opt/mha/mysql8
[root@localhost mysql8]# chown -R mysql:mysql ./data ##数据文件
[root@localhost mysql8]# chown -R mysql:mysql ./mysqllog ##binlog存储路径
[root@localhost mysql8]# su - mysql
[mysql@localhost ~]$ /opt/mha/mysql8/bin/mysqld_safe --defaults-file=/opt/mha/mysql8/conf/my.cnf --user=mysql &

6.配置从库
在从库上执行
/opt/mha/mysql8/bin/mysql -h localhost -uroot -pmysql -P13306 -S /opt/mha/mysql8/mysql.sock
change master to master_host='192.168.1.134',
       master_port=13306,
       master_user='repl',
       master_password='mysql',
       master_log_file='binlog.000006',
       master_log_pos=195;

这里的master_log_file和master_log_post可以从xtrabackup_info的信息找到
[mysql@localhost data]$ more xtrabackup_info
uuid = 7b3c84a0-e021-11e9-8c80-525400c8dc1f
name =
tool_name = xtrabackup
tool_command = --defaults-file=/opt/mha/mysql8/conf/my.cnf --host=localhost --user=root --password=... --port=13306 --socket=/opt/mha/mysql8/mysql.sock --backup --target-dir=/opt/mha/xbackup/
tool_version = 8.0.7
ibbackup_version = 8.0.7
server_version = 8.0.17
start_time = 2019-09-26 13:49:56
end_time = 2019-09-26 13:50:01
lock_time = 0
binlog_pos = filename 'binlog.000006', position '195', GTID of the last change 'bd3f495f-cfb6-11e9-9e6f-525400c8dc1f:1-22'
innodb_from_lsn = 0
innodb_to_lsn = 19847436
partial = N
incremental = N
format = file
compressed = N
encrypted = N

7.启动从库
mysql> start slave;

遇到的问题:
报错信息:
error connecting to master 'repl@192.168.1.134:13306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

解决办法:

alter user 'repl'@'%' identified with mysql_native_password by 'mysql';
flush privileges;