mysql Replication双机同步脚本

mysql Replication双机，有时有问题导致双机不一致，需要手动做一次同步，太烦麻了，写了个脚本，可实现一键同步，但是前提是两台机器有做互信，不说废话，直接上代码：

#!/bin/bash
v_time=date '+%Y%m%d%H%M'
#主备机IP
master_ip='192.168.0.1'
slave_ip='192.168.0.2'
#主备机mysql的base目录
master_mysql_base='/home/mysql/mysql-8.0.26'
slave_mysql_base='/home/mysql/mysql-8.0.26'
#主备机mysql的data目录
master_mysql_data='/home/mysql/mysql_data'
slave_mysql_data='/home/mysql/mysql_data'
#主备机mysql的安装目录
master_mysql='/home/mysql'
slave_mysql='/home/mysql'
#主备机的登录用户和密码
master_root='root'
slave_root='root'
master_root_pwd='root'
slave_root_pwd='root'
#mysql同步用户和密码
sync_account='repl'
sync_account_pwd='******'
echo  '是否要执同步操作:yes 是 other 否'
echo -n '请输入: '
read na
if [ ${na} = 'yes' ];then
echo '开始同步'
#主机操作
echo '停止主机mysql'
service mysql stop
cd ${master_mysql}
echo '压缩数据文件'
if [ -f mysql_data.tar.gz ];
then
rm -rf mysql_data.tar.gz
tar -zcf mysql_data.tar.gz mysql_data
else
tar -zcf mysql_data.tar.gz mysql_data
fi
echo '启动主机mysql'
service mysql start
echo '获取主机同步配置信息，改成只读'
mysql -u ${master_root} -p${master_root_pwd} << EOF >${master_mysql}/master_info.txt
flush tables with read lock;
show master status;
exit
EOF
v_log_file=cat ${master_mysql}/master_info.txt | awk '{print $1}' |sed -n '2p'
v_log_pos=cat ${master_mysql}/master_info.txt | awk '{print $2}' |sed -n '2p'
echo '复制主机数据文件到备机'
scp mysql_data.tar.gz root@${slave_ip}:${slave_mysql}
echo '切换到备机操作'
#切换到备机操作
ssh ${slave_ip} <<END
cd ${slave_mysql}
echo '停止备机mysql'
service mysql stop
echo '备份备机的数据文件'
mv ${slave_mysql_data} ${slave_mysql_data}${v_time}
echo '解压数据文件'
tar -zxf ${slave_mysql}/mysql_data.tar.gz
echo '备份备机auto.cnf文件'
cd ${slave_mysql_data}
mv auto.cnf auto.cnf.bak
echo '启动备机mysql'
service mysql start
echo '用同步用户连主机测试连接'
mysql -u ${sync_account} -p${sync_account_pwd} -h ${master_ip} << EOF
exit
EOF
echo '登录备机mysql配置同步参数'
mysql -u ${slave_root} -p${slave_root_pwd} << EOF
stop slave;
CHANGE MASTER TO
MASTER_HOST='${master_ip}',
MASTER_USER='${sync_account}',
MASTER_PASSWORD='${sync_account_pwd}',
MASTER_LOG_FILE='${v_log_file}',
MASTER_LOG_POS=${v_log_pos};
start slave;
exit
EOF
sleep 5
echo '记录同步状态'
mysql -u ${slave_root} -p${slave_root_pwd} << EOF > ${slave_mysql}/slave_info.txt
show slave status\G
EOF
END

#切回主机
echo '切回主机，修改主机为可读可写'
mysql -u ${master_root} -p${master_root_pwd} << EOF
unlock tables;
exit
EOF
echo '同步完成，请查看备机上的slave_info.txt文件,确认是否同步成功'
else
exit
fi