通过xtrabackup配置MySQL主从

软件	版本
Redhat	7.9
xtrabackup	2.4.20
MySQL	5.7.21

xtrabackup安装部署

主从服务器安装xtrabackup

上传安装文件

libev-4.15-7.el7.x86_64.rpm #本地yum源缺少该依赖通过http://rpm.pbone.net/下载
xtrabackup-24-2.4.20-1.el7.x86_64.rpm

挂载本地镜像

# mount -o loop /dev/sr0 /mnt

配置yum源

# more local.repo
[local]
name=Red Hat Enterprise Linux 7.9
baseurl=file:///mnt
enabled=1
gpgcheck=0
gpgkey=file:///mnt/RPM-GPG-KEY-redhat-release

安装xtrabackup

# yum install libev-4.15-7.el7.x86_64.rpm
# yum install percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm

开启主库binlog

主库修改配置文件

vim /etc/my.cnf
log_bin = /data/mysql/binlog/mysql-bin
binlog_format = row
expire_logs_days = 5
###从库需要开启
relay_log = /data/mysql/relay/mysql-relay
relay_log_recovery = 1

关闭MySQL数据库

set global innodb_fast_shutdown=0;
shutdown;

启动MySQL数据库

mysqld_safe --defaults-file=/etc/my.cnf &

备份主库

因磁盘性能不高，为减少对业务影响，不开启并行，2TB数据备份耗时6H，如磁盘性能好，且不在业务高峰，可以考虑添加参数–parallel开启并行，如报DBD-Mysql不存在，可以添加参数–no-server-version-check忽略错误

vim backup20230725.sh
xtrabackup --username=root --password=root --backup --slave-info --target-dir=/backup/20230725

nohup ./backup20230725.sh > backup20230725.log &

拷贝备份文件至从库

scp -r 20237725 192.168.62.82:`pwd`

主库创建复制用户，用于后期主从复制

create user repl@'192.168.62.%' identified by 'Repl_2023';
grant replication slave,replication client on *.* to repl@'192.168.62.%';

还原从库

复制主库/etc/my.cnf配置文件，修改从库/etc/my.cnf文件中server-id，确保与主库不同

方案1：

vim prepare20230725.sh
xtrabackup --prepare --target-dir=/backup/20230725

nohup ./prepare20230725.sh > prepare20230725.log &

vim copyback20230725.sh
xtrabackup --copy-back --target-dir=/backup/20230725

nohup ./copyback20230725.sh > copyback20230725.log &

方案2：

将备份文件复制到MySQL的数据目录，确保处于同一个逻辑卷
cp /backup/20230725 /data/backup/

vim prepare20230725.sh
xtrabackup --prepare --target-dir=/data/backup/20230725

nohup ./prepare20230725.sh > prepare20230725.log &

vim moveback20230725.sh
xtrabackup --move-back --target-dir=/data/backup/20230725

nohup ./moveback20230725.sh > moveback20230725.log &

启动从库

修改数据文件属主

chown -R mysql:mysql /data

通过MySQL用户启动数据库

mysqld_safe --defaults-file=/etc/my.cnf &

开启复制

根据xtrabackup_info文件中binlog_pos信息确认主从复制MASTER_LOG_FILE与MASTER_LOG_POS信息

# more xtrabackup_info
......
......
binlog_pos = filename 'mysql-bin.000019', position '320'
......

初始化时关闭MySQL从库log-slave-updates参数，否则从库会同步生成binlog，后期可以根据需求再启用

change master to master_host='192.168.62.81',
master_port=3306,
master_user='repl',
master_password='Repl_2023',
master_log_file='mysql-bin.000019',
master_log_pos=320;

start slave;
show slave status\G