注意: 操作前需要修改对应的账号、密码、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 status\G; # 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 "\033[34m $message \033[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 status\G"|grep "Slave_SQL_Running"|grep -v "State"|awk '{print $NF}'` L "${SLAVESTATUS} ${MYSQL_MASTER_BIN} -e \"show slave status\G\"|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 status\G"|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 status\G"|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 status\G"|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 status\G\""|grep "Master_Host"|awk '{print $NF}'` L "ansible ${ANSIBLE_HOST_NAME} -m shell -a "${MYSQL_MASTER_BIN} -e \"show slave status\G\""|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