溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

5.6 MySql主從自動切換腳本

發(fā)布時間：2020-08-06 21:43:18 來源：ITPUB博客 閱讀：140 作者：urgel_babay 欄目：MySQL數(shù)據(jù)庫
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 />
向AI問一下細節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手