随笔- 729 文章- 0 评论- 51 阅读- 123万

mysql数据库主从切换的两个脚本

注意：操作前需要修改对应的账号、密码、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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

#!/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