mysql【1】主从同步之人机交互自动化配置脚本-CSDN博客
接着上一篇,要继续实现在从节点更新数据后,主节点也能同步更新数据,
mysqlmasterbyslave文件夹含以下文件
请在【主节点】下执行
mysqlmasterbyslave文件夹的master.sh,记住,当提示按ctrcl+c退出,就只退出!只备份最新的
日期[master]my.cnf文件!请先确保主从同步也OK,tb.ini文件也亦生成!若没有请参照
mysql【1】主从同步之人机交互自动化配置脚本-CSDN博客
把文件夹所有内容复制到【从节点】
【从节点】上执行 sh master1.sh
内容如下:
变量相关内容user请填写你mysql的管理员账号!pwd填写你mysql的管理员密码
###变量相关
data="zabbix"
user="root"
pwd="Zabbix@123"
backuppath="/winshare/backup"
###变量相关
clear
config=`ls -lt|awk '/\[master\]my.cnf/{split($0,a," ");print a[length(a)]}'|head -n 1 `
#cat $config
items=(log-bin binlog_format auto-increment-offset auto-increment-increment binlog-ignore-db binlog-do-db)
cnf=`find / -name "my.cnf" `
echo backup your $cnf
#cp $cnf `date +%F`"[master1]"${cnf##*/}
for (( i = 0; i < ${#items[@]}; i++ )); do
#echo ${items[$i]}
sed -i "/${items[$i]}.*/d" $cnf
done
for (( i = 0; i < ${#items[@]}; i++ )); do
case ${items[$i]} in
"auto-increment-offset")
cat $config|awk 'BEGIN{FS="="};$0~"'${items[$i]}'"{print "'${items[$i]}'="$2+1}'|tee -a $cnf
;;
*)
cat $config|awk '$0~"'${items[$i]}'"{print $0}'|tee -a $cnf
;;
esac
done
#cat $cnf
systemctl restart mysqld
sleep 2
systemctl list-units --type=service|grep -i -E "mysqld"|awk '{if($4=="running"&&$3=="active"){print $1",服务启动成功"}else{print $1",服务启动失败,请检查"}}'
if [[ -e ./tb.ini ]]; then
echo "是否选择和从服务器的同一账户,来同步数据?"
read -p "输入y或n" xz
case $xz in
"y")
tbuser=`cat ./tb.ini|grep -E "tbuser"|cut -d= -f2`
tbpwd=`cat ./tb.ini|grep -E "tbpwd"|cut -d= -f2`
;;
*)
read -p "设置用于同步的用户" tbuser
read -p "设置用于同步的密码" tbpwd
;;
esac
else
read -p "设置用于同步的用户" tbuser
read -p "设置用于同步的密码" tbpwd
fi
echo tbuser=${tbuser}|tee tb1.ini
echo tbpwd=${tbpwd}|tee -a tb1.ini
mysql -u"${user}" -p"${pwd}" -e "GRANT REPLICATION SLAVE ON *.* TO '${tbuser}'@'%' IDENTIFIED BY '${tbpwd}';flush privileges;" -N
mysql -u"${user}" -p"${pwd}" -e "show grants for ${tbuser};" -N
mysql -u"${user}" -p"${pwd}" -e "show master status\G;" |awk -F : '/File|Position/ {print $1"="$2 }'|tr -d " "|tee -a tb1.ini
echo please confirm your serverIP,such as 1
ifconfig | grep inet | awk '{print "["NR"]"$2}'
read -p "" ip
ifconfig | grep inet | awk 'NR=="'${ip}'"{print "IP="$2}'|tee -a tb1.ini
根据提示设置好同步用的账号和密码,及用于同步的IP,将生成tb1.ini文件在目录下!
再将文件夹mysqlmasterbyslave重新拷贝回【主节点】
【主节点】上执行 sh master1.sh,master1.sh 内容如下
###变量相关
user="root"
pwd="Zabbix@123"
###变量相关
clear
find ./ -name "*.sql" -type f|awk '{i++}{print "a["i-1"]="$0 }' >tmp.txt
while read line;do
eval $line
done <tmp.txt
echo "select your Synchronize data,such as 0"
for (( i = 0; i < ${#a[@]}; i++ )); do
name[$i]=`echo ${a[i]##*/}|awk '{gsub(".sql","",$0);print $0}'`
echo "[${i}]${name[$i]} Path:${a[i]}"
done
read -p "" sb
echo ${name[$sb]}
if [[ ! -e ./tb1.ini ]]; then
echo tb1.ini not exist! please check !!!
fi
while read line;do
eval $line
done <./tb1.ini
echo 用于同步的用户 $tbuser
echo 用户密码 $tbpwd
echo 数据库同步的日志文件 $File
echo 文件位置 $Position
echo 服务器IP $IP
read -p "按任意键继续"
mysql -u${tbuser} -p${tbpwd} -h ${IP} -e "show grants for ${tbuser} ;"
if [[ $? -ne 0 ]]; then
echo [master]mysqlserver can not connected! please check!!
exit
fi
cnf=`find / -name "my.cnf" `
echo backup your $cnf
cp $cnf `date +%F`"[slave1]"${cnf##*/}
items=(relay-log log-slave-updates)
values=(relay-log on information_schema performance_schema)
for (( i = 0; i < ${#items[@]}; i++ )); do
#echo ${items[$i]}
sed -i "/${items[$i]}.*/d" $cnf
echo ${items[$i]}=${values[$i]} >>$cnf
done
sed -i "/replicate-ignore-db.*/d" $cnf
ignoredb=information_schema,performance_schema
echo $ignoredb | awk '{split($0,a,",");for(i in a) system("echo replicate-ignore-db="a[i]"|tee -a '${cnf}'")}'
#service mysqld restart
systemctl restart mysqld
sleep 2
systemctl list-units --type=service|grep -i -E "mysqld"|awk '{if($4=="running"&&$3=="active"){print $1",服务启动成功"}else{print $1",服务启动失败,请检查"}}'
#mysql -u${user} -p${pwd} < ${a[$sb]}
sql="
STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='${IP}',
MASTER_USER='${tbuser}',
MASTER_PASSWORD='${tbpwd}',
MASTER_LOG_FILE='${File}',
MASTER_LOG_POS=${Position};
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
"
mysql -u${user} -p${pwd} -e "${sql}"
systemctl restart mysqld
sleep 2
systemctl list-units --type=service|grep -i -E "mysqld"|awk '{if($4=="running"&&$3=="active"){print $1",服务启动成功"}else{print $1",服务启动失败,请检查"}}'
mysql -u"${user}" -p"${pwd}" -e "show slave status\G;SHOW VARIABLES LIKE '%server_%';"
根据提示选择要同步的数据库,确认数据没问题,按任意键继续
等待执行完毕,
现在通过执行errlog.sh文件来查看是否设置成功,errlog.sh的内容如下
###变量相关
user="root"
pwd="Zabbix@123"
###变量相关
datadir=`find / -name my.cnf -type f|xargs -i cat {}|awk -F = '$0~"datadir" {print $2}'`
echo 日志文件目录:$datadir
find ${datadir} -name "*.err"|xargs -i cat {}
echo 配置文件
find / -name my.cnf -type f|xargs -i cat {}
echo 同步状态
mysql -u"${user}" -p"${pwd}" -e "show slave status\G;SHOW VARIABLES LIKE '%server_%';"
主主同步设置成功!
文件下载
链接:https://pan.baidu.com/s/16Jz9uWm-oy_Zwkc0UoD-vQ
提取码:ud4i
--来自百度网盘超级会员V6的分享