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

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;