13-Linux主主一键配置脚本

#!/bin/bash
#author by xiaokai 2019-07-29
#-*- coding:utf-8 -*-

#重启数据库
#echo 'restart mysql1 node...'
#docker restart mysql1
#echo 'restart mysql2 node...'
#docker restart mysql2

echo -e "\e[1;34m connect mysql node1... \n"
echo 'connect mysql node1...'
#数据库ip
HOSTNAME1="172.26.2.114"
#数据库端口
PORT1="3306"
#数据库用户名
USERNAME1="xiaokai"
#数据库密码
PASSWORD1="123456"
#数据库名
DBNAME1="mysql"
#查询语句
select_sql="show master status"
#*#执行查询*
result1=`mysql -h${HOSTNAME1}  -P${PORT1}  -u${USERNAME1} -p${PASSWORD1} ${DBNAME1} -Bse "${select_sql}"`
node1_file_position=`echo ${result1} | awk '{print $1}'` #获取到的第一个值
node1_Postion=`echo ${result1} | awk '{print $2}'` #获取到的第二个值
node1_comtend=`echo ${result1} | awk '{print $n}'` #获取到的第N个值

echo 'node1_file_position:'$node1_file_position
echo 'node1_Postion:'$node1_Postion
echo 'node1_comtend:'$node1_comtend

echo '***************************'
echo 'connect mysql node2...'
#数据库ip
HOSTNAME2="172.26.2.115"
#数据库端口
PORT2="3306"
#数据库用户名
USERNAME2="xiaokai"
#数据库密码
PASSWORD2="123456"
#数据库名
DBNAME2="mysql"
#查询语句
select_sql="show master status"
#*#执行查询*
result2=`mysql -h${HOSTNAME2}  -P${PORT2}  -u${USERNAME2} -p${PASSWORD2} ${DBNAME2} -Bse "${select_sql}"`

node2_file_position=`echo ${result2} | awk '{print $1}'` #获取到的第一个值
node2_Postion=`echo ${result2} | awk '{print $2}'` #获取到的第二个值
node2_comtend=`echo ${result2} | awk '{print $n}'` #获取到的第N个值

echo 'node2_file_position:'$node2_file_position
echo 'node2_Postion:'$node2_Postion
echo 'node2_comtend:'$node2_comtend

#创建同步帐号并授权
create_acount_sql="create user 'xiaokai'@'%' identified by '123456"
`mysql -h${HOSTNAME1}  -P${PORT1}  -u${USERNAME1} -p${PASSWORD1} ${DBNAME1} -Bse "${create_acount_sql}"`
grant_acount_sql="grant all on *.* to 'xiaokai'@'%' identified by '123456'"
`mysql -h${HOSTNAME1}  -P${PORT1}  -u${USERNAME1} -p${PASSWORD1} ${DBNAME1} -Bse "${grant_acount_sql}"`

`mysql -h${HOSTNAME2}  -P${PORT2}  -u${USERNAME2} -p${PASSWORD2} ${DBNAME2} -Bse "${create_acount_sql}"`
`mysql -h${HOSTNAME2}  -P${PORT2}  -u${USERNAME2} -p${PASSWORD2} ${DBNAME2} -Bse "${grant_acount_sql}"`

echo '同步帐号授权完毕'

#配置从节点写入
stop_slave_sql="stop slave"
`mysql -h${HOSTNAME1}  -P${PORT1}  -u${USERNAME1} -p${PASSWORD1} ${DBNAME1} -Bse "${stop_slave_sql}"`
`mysql -h${HOSTNAME2}  -P${PORT2}  -u${USERNAME2} -p${PASSWORD2} ${DBNAME2} -Bse "${stop_slave_sql}"`
#配置node1
change_master_node1="CHANGE MASTER TO MASTER_HOST='${HOSTNAME1}',MASTER_PORT=${PORT1},MASTER_USER='${USERNAME1}',MASTER_PASSWORD='${PASSWORD1}',MASTER_LOG_FILE='${node1_file_position}',MASTER_LOG_POS=${node1_Postion}"
change_master_node2="CHANGE MASTER TO MASTER_HOST='${HOSTNAME2}',MASTER_PORT=${PORT2},MASTER_USER='${USERNAME2}',MASTER_PASSWORD='${PASSWORD2}',MASTER_LOG_FILE='${node2_file_position}',MASTER_LOG_POS=${node2_Postion}"

echo ${change_master_node1}
echo ${change_master_node2}

echo '==============================================='
start_slave_sql="start slave"

`mysql -h${HOSTNAME2}  -P${PORT2}  -u${USERNAME2} -p${PASSWORD2} ${DBNAME2} -Bse "${change_master_node1}"`
sleep 1s
`mysql -h${HOSTNAME2}  -P${PORT2}  -u${USERNAME2} -p${PASSWORD2} ${DBNAME2} -Bse "${start_slave_sql}"`
sleep 1s
`mysql -h${HOSTNAME1}  -P${PORT1}  -u${USERNAME1} -p${PASSWORD1} ${DBNAME1} -Bse "${change_master_node2}"`
sleep 1s
`mysql -h${HOSTNAME1}  -P${PORT1}  -u${USERNAME1} -p${PASSWORD1} ${DBNAME1} -Bse "${start_slave_sql}"`

#回调slave状态
show_slave_sql="show slave status"
slave1_status=`mysql -h${HOSTNAME1}  -P${PORT1}  -u${USERNAME1} -p${PASSWORD1} ${DBNAME1} -Bse "${show_slave_sql}"`
slave_node1_status=`echo ${slave1_status} | awk '{print $n}'`
echo 'node1:'${slave_node1_status}
echo '执行完毕'

echo -e '\e[0m'
#查询语句
#select_sql2='select now()'
#dateResult=`mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} ${DBNAME} -Bse "${select_sql2}"` 
#time2=`echo ${dateResult}|awk '{print $n}'`
#echo 'time:'$time2