xtrabackup8安装并部署主从
环境:
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;
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布:重大改进与新特性概览!
· AI与.NET技术实操系列(二):开始使用ML.NET
· 单线程的Redis速度为什么快?