5.7.29重新部署主从

1.停掉重库
/opt/mysql57_slave/bin/mysqladmin -h localhost -uroot -P13306 -pyeemiao3040 -S /opt/mysql57_slave/mysql.sock shutdown

2.主库上创建复制账号(已经创建的话不需要):
grant replication slave, replication client on *.*  to repl@'192.168.1.%' identified by 'mysql';

3.备份主库
root账号下操作
cd /opt/xtrabackup-2.4.7/bin
./innobackupex --defaults-file=/opt/mysql57/conf/my.cnf --user=root --socket=/tmp/mysql.sock  --password=mm3040 -P3306 /opt/xtrabackup_file/

 

4.把主库备份的备份文件拷贝到从库

[root@host134 xtrabackup_file]# pwd
/opt/xtrabackup_file

[root@host134 xtrabackup_file]# scp -r 2024-04-15_16-21-50 root@192.168.1.135:/opt/xtrabackup_file/

----------------------------备库恢复------------------------
1.进入到从库的data目录,备份data目录
[root@cdc-henan-cdhworker01 bin]# cd /opt/mysql57_slave
[root@cdc-henan-cdhworker01 mysql57_slave]# mv data bakdata
[root@cdc-henan-cdhworker01 mysql57_slave]# mkdir data

2.恢复
root用户操作
cd /opt/xtrabackup-2.4.7/bin
./innobackupex --defaults-file=/opt/mysql57_slave/conf/my.cnf --user=root --apply-log /opt/xtrabackup_file/2021-04-28_10-53-04
./innobackupex --defaults-file=/opt/mysql57_slave/conf/my.cnf --user=root --copy-back --rsync /opt/xtrabackup_file/2021-04-28_10-53-04

innobackupex: Can't create/write to file '/opt/mysql57_slave/redolog/ib_logfile0' (Errcode: 17 - File exists)
[01] error: cannot open the destination stream for ib_logfile0

解决办法:删除掉相应目录的redolog文件

 

为了提升恢复的速度，可以把主库的备份文件之间拷贝到从库的data目录，然后直接应用日志即可，例子如下:

[root@host135 binlog]#cd /opt/xtrabackup_file/2024-04-15_16-21-50
[root@host135 binlog]#cp -r * /opt/mysql56/data/
[root@host135 binlog]#/opt/xtrabackup247/bin/innobackupex --defaults-file=/opt/mysql56/conf/my.cnf --user=root --apply-log /opt/mysql56/data

 

 

3.修改权限
[root@cdc-henan-cdhworker01 bin]# cd /opt
[root@cdc-henan-cdhworker01 opt]# chown -R mysql:mysql ./mysql57_slave

4.启动从库
/opt/mysql57_slave/bin/mysqld_safe --defaults-file=/opt/mysql57_slave/conf/my.cnf --user=mysql &

5.重新启动复制进程
/opt/mysql57_slave/bin/mysql -h localhost -uroot -P13306 -pmm3040 -S /opt/mysql57_slave/mysql.sock
change master to master_host='192.168.1.197',
       master_port=3306,
       master_user='repl',
       master_password='mysql',
       master_log_file='binlog.000536',
       master_log_pos=26685347;


mysql> start slave;


找到主从同步的参数值
master_log_file和master_log_pos
这两个参数可以在xtrabackup备份目录下的文件中查找到
[root@cdc-henan-cdhworker01 2021-04-28_10-53-04]# cd /opt/xtrabackup_file/2021-04-28_10-53-04
[root@cdc-henan-cdhworker01 2021-04-28_10-53-04]# more xtrabackup_binlog_info
binlog.000536   26685347        52cf753d-78ad-11ea-8c41-d0946652eba0:1-81347322