5.6 MySql主从自动切换脚本

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