5.6 MySql主从自动切换脚本

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

2015.07.17 <div id="codeText" class="codeText"> <ol style="margin:0 1px 0 0;padding:5px 0pt;" start="1" class="dp-css none_number"> <li> #!/bin/bash </li> <li> cat << README </li> <li> ##################################################################################################### </li> <li> #Auther :zhanglin                                                                                   # </li> <li> #Date : 2015.07.17                                                                                  # </li> <li> #Step 1: point the slave IP          　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 # </li> <li> #Step 2: check the master and slave information whether good for change　　    　　　　　　　　　　　　# </li> <li> #Step 3: stop old slave,then get new master binlog name and postation,then execute change master to # </li> <li> #Step 4: start slave,and show whether change successed.                                             # </li> <li> ##################################################################################################### </li> <li> README </li> <li> User=root </li> <li> PW=123456 </li> <li> read -p "-- Please input the slave IP:" Slave </li> <li> Master=$(mysql -u${User} -h${Slave} -p${PW} -e "show slave status \G;"|awk '/Master_Host/{print $2}') </li> <li>  if [ -n "${Master}" ] </li> <li> then </li> <li> echo -e "--Master IP:${Master},Slave IP:${Slave}" </li> <li> M=$(mysql -u${User} -h${Master} -p${PW} -e "show master status;"|awk 'NR==2{print $2}') </li> <li> S=$(mysql -u${User} -h${Slave} -p${PW} -e "show slave status \G;"|awk '/Read_Master_Log_Pos/{print $2}') </li> <li> echo -e "-- master pos:${M};slave pos is:${S}" </li> <li>  else </li> <li> echo "-- Slave IP input wrong,please input again ! " </li> <li> exit 1 </li> <li>  fi </li> <li>        if [ "${M}" -eq "${S}" ] </li> <li> then </li> <li>  read -p "-- Master-Slave is accordance,input Yes to start changing:" var </li> <li>             case "$var" in </li> <li>                 [Yy]es) </li> <li>                     mysql -u${User} -h${Slave} -p${PW} -e "stop slave;reset slave;change master to master_host='';" </li> <li>                     Pos=$(mysql -u${User} -h${Slave} -p${PW} -e "show master status;"|awk 'NR==2{print $2}') </li> <li>                     File=$(mysql -u${User} -h${Slave} -p${PW} -e "show master status;"|awk 'NR==2{print $1}') </li> <li>                     mysql -u${User} -h${Master} -p${PW} -e "stop slave; </li> <li>                     change master to master_user='ideal',master_host='${Slave}',master_password='123456',master_log_file='${File}',master_log_pos=${Pos}; </li> <li>                    start slave;" </li> <li>                    ;; </li> <li>                 *) </li> <li>                    echo "-- error input .... exit!" </li> <li>                    ;; </li> <li>            esac </li> <li>                echo "-- changing, please wait 3s...." </li> <li>                sleep 3 </li> <li>                echo "-- change successfull,the new master is:${Slave},new slave is ${Master}" </li> <li>                 SlaveIOStatus=$(mysql -u${User} -h${Master} -p${PW} -e "show slave status \G;"|awk '/Slave_IO_Running/{print $2}') </li> <li>                 SlaveSQLStatus=$(mysql -u${User} -h${Master} -p${PW} -e "show slave status \G;"|awk '/Slave_SQL_Running/{print $2}') </li> <li>                 printf "The new master is: \n Slave_IO_Running=${SlaveIOStatus}\n" </li> <li>                 printf "Slave_SQL_Running=${SlaveSQLStatus}\n" </li> <li>           else </li> <li>           echo "node change failed please change again ! " </li> <li>           exit 1 </li> <li>          fi </li> </ol> </div>