526互联

xtrabackup8安装并部署主从

发布时间 2023-08-26 15:42:59作者: slnngk

环境:
OS:Centos 7
xtrabackup:8.0.28
mysql:8.0.28

1.下载xtrabackup-8.0.28

2.安装解压
[root@host135 soft]# tar -xvf percona-xtrabackup-8.0.28-21-Linux-x86_64.glibc2.17.tar.gz
[root@host135 soft]# mv percona-xtrabackup-8.0.28-21-Linux-x86_64.glibc2.17 /opt/xtrabackup-8.0.28

3.备份数据库
/opt/xtrabackup-8.0.28/bin/xtrabackup --defaults-file=/data/middle/mysql8/conf/my.cnf --user=root --socket=/data/middle/mysql8/mysql.sock  --password=mysql -P23306 --no-version-check --backup --target-dir=/opt/xtrabackup_file/

遇到错误1:
Failed to connect to MySQL server as DBD::mysql module is not installed at - line 1548.

加上参数,该报错不会出现,但是不影响备份:
--no-version-check

备份的目录文件:

[root@host135 xtrabackup_file]# ls
backup-my.cnf  db_test         mysql               sys       xtrabackup_binlog_info  xtrabackup_logfile
binlog.000003  ib_buffer_pool  mysql.ibd           undo_001  xtrabackup_checkpoints  xtrabackup_tablespaces
binlog.index   ibdata1         performance_schema  undo_002  xtrabackup_info

 

4.打包压缩
[root@host135 opt]# tar -cvf xtrabackup_file.tar ./xtrabackup_file

5.拷贝到从库机器
[root@host135 opt]# scp xtrabackup_file.tar root@192.168.1.134:/tmp/

###################异机器恢复部署主从#######################

1.安装相同版本的mysql
安装步骤省略

2.停掉数据库
[root@localhost opt]#/data/middle/mysql8/bin/mysqladmin -h localhost -uroot -P23306 -p -S /data/middle/mysql8/mysql.sock shutdown

3.备份之前的数据文件目录,并创建一个空目录
[root@localhost mysql8]# cd /data/middle/mysql8
[root@host02 mysql5718]# mv data bakdata
[root@host02 mysql5718]# mkdir data
这里要是binlog日志不是放在data目录的话,需要将binlog日志要删除掉,否则copy-backup过程有报如下错误:

xtrabackup: Can't create/write to file '/data/middle/mysql8/mysqllog/binlog/binlog.000004' (OS errno 17 - File exists)
2023-08-25T16:43:14.072671+08:00 0 [ERROR] [MY-011825] [Xtrabackup] cannot open the destination stream for binlog.000004
2023-08-25T16:43:14.072756+08:00 0 [ERROR] [MY-011825] [Xtrabackup] copy_file() failed.

 

解决办法:
删除binlog下的所有文件
[root@host134 data]# cd /data/middle/mysql8/mysqllog/binlog
[root@host134 binlog]# ls
binlog.000001  binlog.000002  binlog.000003  binlog.000004  binlog.index
[root@host134 binlog]# rm -rf *

 

4.xtrabackup恢复
解压文件
[root@localhost mha]# cd /tmp/
[root@localhost mha]# tar -xvf xtrabackup_file.tar
/opt/xtrabackup-8.0.28/bin/xtrabackup --defaults-file=/data/middle/mysql8/conf/my.cnf --user=root --prepare --target-dir=/tmp/xtrabackup_file/
/opt/xtrabackup-8.0.28/bin/xtrabackup --defaults-file=/data/middle/mysql8/conf/my.cnf --user=root --copy-back --target-dir=/tmp/xtrabackup_file/ ##这个步骤会把文件直接拷贝到配置文件的data目录

--copy-back期间会生产binlogr日志,需要在--copy-back之前删除掉原来的binlog日志

[root@host134 binlog]# cd /data/middle/mysql8/mysqllog/binlog
[root@host134 binlog]# ls -al
total 8
drwxrwxr-x 2 mysql mysql  47 Aug 25 16:47 .
drwxrwxr-x 5 mysql mysql  51 Aug 25 14:27 ..
-rw-r----- 1 root  root  197 Aug 25 16:47 binlog.000004
-rw-r----- 1 root  root   50 Aug 25 16:47 binlog.index
[root@host134 binlog]# more binlog.index 
/data/middle/mysql8/mysqllog/binlog/binlog.000004

 

另外一种方式:
直接解压到数据文件路径或是解压后把解压后的文件mv到数据文件目录,这样做的目的是不需要--copy-back步骤,特别是大文件的情况下拷贝文件很慢.

[root@localhost mha]# cd /tmp/
[root@localhost mha]# tar -xvf xtrabackup_file.tar
[root@host134 tmp]# cd xtrabackup_file
[root@host134 xtrabackup_file]# mv * /data/middle/mysql8/data/

/opt/xtrabackup-8.0.28/bin/xtrabackup --defaults-file=/data/middle/mysql8/conf/my.cnf --user=root --prepare --target-dir=/data/middle/mysql8/data/

 

5.修改目录属性启动数据库：
[root@localhost mysql8]# cd /data/middle
[root@localhost mysql8]# chown -R mysql:mysql ./mysql8


6.启动数据库
[root@localhost opt]#/data/middle/mysql8/bin/mysqld_safe --defaults-file=/data/middle/mysql8/conf/my.cnf --user=mysql &

7.部署从库

/data/middle/mysql8/bin/mysql -h localhost -uroot -P23306 --socket=/data/middle/mysql8/mysql.sock -p
mysql> reset slave;
mysql> reset master;
mysql> set global gtid_purged='f61fd6e1-4255-11ee-aaf5-52540051cd25:1-106'; ##xtrabackup_info中的binlog_pos = filename 'binlog.000004', position '197', GTID of the last change 'f61fd6e1-4255-11ee-aaf5-52540051cd25:1-106'

change master to master_host='192.168.1.135',
       master_user='repl',
       master_password='mysql',
       master_port=23306,
       master_auto_position=1;

mysql>start slave;

 

后续版本的正确语法

mysql> reset replica;
mysql> reset master;
mysql> set global gtid_purged='f61fd6e1-4255-11ee-aaf5-52540051cd25:1-106';
change replication source to source_host='192.168.1.135',
       source_user='repl',
       source_password='mysql',
       source_port=23306,
       source_auto_position=1;


mysql> start replica;

mysql> show replica status \G;