注意： 操作前需要修改对应的账号、密码、Ip地址信息,
请一定要根据自己实际情况，谨慎执行操作。
ChangeIpOneByOne.sh
### 修改本地从库为主库的操作####
# 1、登录到本地的数据库（从库）上,根据不同端口需要执行多遍
`which mysql` -uroot -p"123456" -P3306 -S /tmp/mysql3306.sock
# 2、停止当前slave同步
stop slave;
# 3、重置当前的slave同步
reset slave;
# 4、修改当前库为读写状态
set global read_only=0;
# 5、查看主库状态
show master status;
记录下 File: mysqld-bin.000011 Position: 231
# 6、配置主库的IP
ifconfig bond1:1 192.168.1.159 up
# 7、另一个从库上执行操作
`which mysql` -uroot -p"123456" -P3306 -S /tmp/mysql3306.sock
# 8、停止slave同步
stop slave;
# 9、重置slave同步
reset slave;
# 10、修改新的主库为新升级的库
change master to master_host='192.168.1.159',master_port=3306,master_user='repl',master_password='Repl123456',MASTER_LOG_FILE='mysqld-bin.000011',MASTER_LOG_POS=231"
# 11、查看同步状态
show slave statusG;
# 12、修改新主库上的配置文件
sed -i "s/IPADDR=192.168.1.159/IPADDR=192.168.1.156/g" /etc/sysconfig/network-scripts/ifcfg-bond1
sed -i "s/read_only = 1/read_only = 0/g" /etc/my3306.cnf
### 修改本地IP地址为从库的操作#####
# 1、登录到本地的数据库（从库）上
# 2、增加从库的Ip地址
ifconfig bond1:1 192.168.1.159 up
sed -i "s/IPADDR=192.168.1.159/IPADDR=192.168.1.156/g" /etc/sysconfig/network-scripts/ifcfg-bond1
changeIP.sh
#!/bin/bash
set -o nounset
#数据库的端口
PORTS=( 3306 3308 )
PSW="123456"
REPL_USER="repl"
REPL_USER_PSW="repl123456"
ANSIBLE_HOST_NAME="168" #同 SLAVE_HOST_IP ，需要在/etc/ansible/hosts里配置
#[root@t159 ~]# more /etc/ansible/hosts
#[168]
#192.168.1.168
#网卡配置文件
NETWORKCONFIGFILE="/etc/sysconfig/network-scripts/ifcfg-eth1"
#临时网卡
TEMPNETNIC="eth1:1"
##############################################
# #
# 配置修改开始 #
# #
##############################################
LOCAL_HOST_IP="192.168.1.156" ##升级为主库的现有IP地址
MASTER_HOST_IP='192.168.1.169' ## 主库的IP地址
SLAVE_HOST_IP="192.168.1.168" ##原另一个从库的IP
MYSQL_BIN=`which mysql`
MYSQL_MASTER_BIN="${MYSQL_BIN} -uroot -p${PSW}"
##############################################
# #
# 配置修改结束 #
# #
##############################################
# define restricted path
PATH="/bin:/usr/bin:/sbin:/usr/sbin"
# adirname - return absolute dirname of given file
adirname() { odir=`pwd`; cd `dirname $1`; pwd; cd "${odir}"; }
MYNAM=`basename "$0"`
MYDIR=`adirname "$0"`
MYLOG_PATH="${MYDIR}/logs"
MYLOG="${MYLOG_PATH}/${MYNAM}_`date +%F`.log"
for D in ${MYLOG_PATH}
do
if [ ! -d ${D} ] ; then
mkdir -p ${D}
echo -e "Mkdir ${D}" >> ${MYLOG}
fi
done
# ---------
# functions
# ---------
#日志函数
function L(){
message="$(date -d today +"%Y-%m-%d %H:%M:%S") - $1"
echo -e " 33[34m $message 33[0m" && echo $message >> ${MYLOG}
}
#主库挂，从库升级为主库
#1、将主库IP地址增加到从库上
#2、停止slave同步
#3、重看当前从库的pos和bin-logs记录
#4、修改从库为读写状态
#5、修改另一个从库来连接新的主库并查看同步状态
#从库挂。另一个从库作为新的从库
#1、将挂掉的从库IP地址增加到本地服务器上即可。
#1、将主库IP地址增加到从库上
function AddMasterIP(){
#临时增加原masterIP地址到临时网卡上
`which ifconfig` ${TEMPNETNIC} ${MASTER_HOST_IP} up
L "ifconfig ${TEMPNETNIC} ${MASTER_HOST_IP} up"
#修改网卡配置文件，但不重启（防止该服务器重启后临时IP地址丢失）
sed -i "s/IPADDR=${LOCAL_HOST_IP}/IPADDR=${MASTER_HOST_IP}/g" ${NETWORKCONFIGFILE}
L "sed -i "s/IPADDR=${LOCAL_HOST_IP}/IPADDR=${MASTER_HOST_IP}/g" ${NETWORKCONFIGFILE}"
NIP=`\`which ifconfig\` ${TEMPNETNIC}|grep "inet addr"|cut -f 2 -d ":"|cut -f 1 -d " "`
FIP=`cat ${NETWORKCONFIGFILE}|grep IPADDR|awk -F "=" '{print $2}'`
if [ "${NIP}" != "${MASTER_HOST_IP}" ]&&[ "${NIP}" != "${FIP}" ];then
L "${MASTER_HOST_IP} is error,exit1"
exit 1
fi
L "该服务器上设置${MASTER_HOST_IP}成功.
临时增加IP为${NIP},网卡配置文件为${FIP}"
return 0
}
function StopSlave(){
#2、停止当前slave同步
${MYSQL_MASTER_BIN} -e "stop slave"
${MYSQL_MASTER_BIN} -e "reset slave"
L "${MYSQL_MASTER_BIN} -e "stop slave""
SLAVESTATUS=`${MYSQL_MASTER_BIN} -e "show slave statusG"|grep "Slave_SQL_Running"|grep -v "State"|awk '{print $NF}'`
L "${SLAVESTATUS} ${MYSQL_MASTER_BIN} -e "show slave statusG"|grep "Slave_SQL_Running"|grep -v "State"|awk '{print $NF}'"
if [ "${SLAVESTATUS}" != "No" ];then
L "当前db${IPORT}停止slave失败。"
exit 1
fi
L "停止当前DB${IPORT}的slave同步成功"
return 0
}
#function GetNewMasterLogFile(){
# #3、重看当前升级为主库的bin-logs记录
# #bin-logs记录
# Log_File=`${MYSQL_MASTER_BIN} -e "show master statusG"|egrep "File"|awk -F " " '{print $2}'`
# L "新的maser库${IPORT}的Log_File是${Log_File}"
# LogFile=${Log_File}
#}
function GetNewMasterPosNum(){
#3、重看当前升级为主库的posnum记录
#posnum
Pos=`${MYSQL_MASTER_BIN} -e "show master statusG"|grep "Position"|awk -F " " '{print $2}'`
L "新的master库${IPORT}的pos是${Pos}"
return ${Pos}
}
function AlterReadOnlyStatus(){
#4、修改当前库为读写状态
${MYSQL_MASTER_BIN} -e "set global read_only=0"
#修改网卡配置文件，但不重启（防止该服务器重启后临时IP地址丢失）
sed -i "s/read_only = 1/read_only = 0/g" /etc/my${IPORT}.cnf
L "sed -i "s/read_only = 1/read_only = 0/g" /etc/my${IPORT}.cnf"
ReadOnlyStatus=`${MYSQL_MASTER_BIN} -e "show variables like "read_only""|awk '{print $NF}'|sed -n '$p'`
ReadOnlyConfig=`grep "read_only" /etc/my${IPORT}.cnf |awk '{print $NF}'`
#echo "${ReadOnlyStatus}"
if [ "${ReadOnlyStatus}" != "OFF" ] && [ "${ReadOnlyConfig}" != "0" ];then
L "修改当前数据库${IPORT}的读写状态失败。"
exit 1
fi
L "修改当前数据库${IPORT}的读写状态成功。"
return 0
}
function AnsibleSlaveConnect(){
#5、修改另一个从库来连接新的主库并查看同步状态
ansible ${ANSIBLE_HOST_NAME} -m shell -a "${MYSQL_MASTER_BIN} -e "stop slave""
L "另一个从库${IPORT}停止原主从同步成功"
ansible ${ANSIBLE_HOST_NAME} -m shell -a "${MYSQL_MASTER_BIN} -e "reset slave""
L "另一个从库${IPORT}重置原主从同步成功"
ansible ${ANSIBLE_HOST_NAME} -m shell -a "${MYSQL_MASTER_BIN} -e "set global read_only=1""
L "获取新主库${IPORT}的bin-logs记录"
LogFile=`${MYSQL_MASTER_BIN} -e "show master statusG"|egrep "File"|awk -F " " '{print $2}'`
L "获取新主库${IPORT}的Pos记录"
GetNewMasterPosNum
ansible ${ANSIBLE_HOST_NAME} -m shell -a "${MYSQL_MASTER_BIN} -e "change master to master_host='${MASTER_HOST_IP}',master_port=${IPORT},master_user='${REPL_USER}',master_password='${REPL_USER_PSW}',MASTER_LOG_FILE='${LogFile}',MASTER_LOG_POS=${Pos}""
L "另一个从库${IPORT}指定新的主库"
L "ansible ${ANSIBLE_HOST_NAME} -m shell -a "${MYSQL_MASTER_BIN} -e "change master to master_host='${MASTER_HOST_IP}',master_port=${IPORT},master_user='${REPL_USER}',master_password='${REPL_USER_PSW}',MASTER_LOG_FILE=${LogFile},MASTER_LOG_POS=${Pos}"""
ansible ${ANSIBLE_HOST_NAME} -m shell -a "${MYSQL_MASTER_BIN} -e "start slave""
L "另一个从库${IPORT}开启同步"
SlaveRsyncIp=`ansible ${ANSIBLE_HOST_NAME} -m shell -a "${MYSQL_MASTER_BIN} -e "show slave statusG""|grep "Master_Host"|awk '{print $NF}'`
L "ansible ${ANSIBLE_HOST_NAME} -m shell -a "${MYSQL_MASTER_BIN} -e "show slave statusG""|grep "Master_Host"|awk '{print $NF}'"
L "SlaveRsyncIp ${SlaveRsyncIp} ,${MASTER_HOST_IP}"
if [ "${SlaveRsyncIp}" != "${MASTER_HOST_IP}" ];then
L "同步主库${IPORT}的IP错误"
exit 1
fi
L "另一个从库${IPORT}配置完成，并开始同步"
return 0
}
##############################################
# #
# 始设置本机为slave db #
# #
##############################################
function AddSlaveIP(){
`which ifconfig` ${TEMPNETNIC} ${SLAVE_HOST_IP} up
L "ifconfig eth1:1 ${SlaveIP} up"
L "本地增加slave ip地址成功"
}
##############################################
# #
# 始设置本机为master db #
# #
##############################################
function MasterDB(){
for IPORT in ${PORTS[@]}
do
MYSQL_MASTER_BIN="${MYSQL_BIN} -uroot -p${PSW} -P${IPORT} -S /tmp/mysql${IPORT}.sock"
StopSlave
if [ $? != 0 ];then
L "停止${IPORT}的slave同步失败."
exit 1
fi
AlterReadOnlyStatus
if [ $? != 0 ];then
L "设置master${IPORT}库为读写库失败."
exit 1
fi
AddMasterIP
if [ $? != 0 ];then
L "增加临时IP或修改IP配置文件失败."
exit 1
fi
AnsibleSlaveConnect
if [ $? != 0 ];then
L "修改另一从库${IPORT}的master库为新的master库失败."
exit 1
fi
L "${IPORT}设置为新的主库完成！"
done
}
##############################################
# #
# 开始执行脚本 #
# #
##############################################
function exec_continue(){
read -p "Continue, Input your choice(m/s/b):" choice_user
echo "Your choice: '${choice_user}'"
echo " "
if [ "m" == "${choice_user}" ];then
MasterDB
elif [ "s" == "${choice_user}" ];then
AddSlaveIP
else
exit 1
fi
}
exec_continue