mysql【2】主主同步自动化配置脚本

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的分享