mysql主从复制一键安装

最新推荐文章于 2023-03-15 14:40:02 发布

菜狗沉淀之路

最新推荐文章于 2023-03-15 14:40:02 发布

阅读量843

点赞数 2

本文链接：https://blog.csdn.net/weixin_43601042/article/details/120188088

版权

mysql 数据库数据库架构

#########position方式安装并可自定义从库延时同步时间######################

#!/bin/bash
###本脚本主从机器所使用的的管理者root的密码一致
###应该在从机上提取授权好远程可登录用户
####管理者
admin="root"
####密码
passwd="111"
####从ip
farip="192.168.100.15"
####主ip
masterip="192.168.100.10"
#####授权
read -p "请输入从库需要延迟多长时间同步：
" delytime
if [[ "${delytime}" =~ ^[0-9]+$ ]]
then
mysql -u${admin} -p${passwd} -e "grant replication slave on *.* to repl@'%' identified by '111';" 2> /dev/null
masterfile=`mysql -u${admin} -p${passwd} -e "show master status;" 2> /dev/null | grep -Eo "mysql-bin.[0-9]{6}"`
begin=`mysql -u${admin} -p${passwd} -e "show master status;" 2> /dev/null | grep -Eo "[^.0][0-9]+" | awk '{print $1}'`
cat > /tmp/myzc.sql <<EOF
CHANGE MASTER TO
MASTER_HOST="${masterip}",
MASTER_USER="repl",
MASTER_PASSWORD="111",
MASTER_PORT=3306,
MASTER_LOG_FILE="${masterfile}",
MASTER_LOG_POS=${begin},
MASTER_CONNECT_RETRY=10;
EOF
mysql -u${admin} -p${passwd} -h ${farip} -e "source /tmp/myzc.sql" 2> /dev/null
mysql -u${admin} -p${passwd} -h ${farip} -e "CHANGE MASTER TO MASTER_DELAY = ${delytime};" 2> /dev/null
mysql -u${admin} -p${passwd} -h ${farip} -e "start slave;" 2> /dev/null
mysql -u${admin} -p${passwd} -h ${farip} -e "show slave status\G" 2> /dev/null | grep -Eo "Slave_[a-Z]+_Running:[ ][a-Z]+"
else
echo "输入错误"
fi

######gtid方式安装##################################

#!/bin/bash
###本脚本主从机器所使用的的管理者root的密码一致
###应该在从机上提取授权好远程可登录用户
####管理者
admin="root"
####密码
passwd="111"
####从ip
farip="192.168.100.15"
####主ip
masterip="192.168.100.10"
#####授权
read -p "请输入从库需要延迟多长时间同步：
" delytime
if [[ "${delytime}" =~ ^[0-9]+$ ]]
then
mysql -u${admin} -p${passwd} -e "grant replication slave on *.* to repl@'%' identified by '123';" 2> /dev/null
cat > /tmp/myzc.sql <<EOF
CHANGE MASTER TO
MASTER_HOST="${masterip}",
MASTER_USER="repl",
MASTER_PASSWORD="123",
MASTER_AUTO_POSITION=1;
EOF
mysql -u${admin} -p${passwd} -h ${farip} -e "source /tmp/myzc.sql" 2> /dev/null
mysql -u${admin} -p${passwd} -h ${farip} -e "CHANGE MASTER TO MASTER_DELAY = ${delytime};" 2> /dev/null
mysql -u${admin} -p${passwd} -h ${farip} -e "start slave;" 2> /dev/null
mysql -u${admin} -p${passwd} -h ${farip} -e "show slave status\G" 2> /dev/null | grep -Eo "Slave_[a-Z]+_Running:[ ][a-Z]+"
else
echo "输入错误"
fi