老男孩mysql高級dba培訓高可用方案之mha

1、rootdg55#yum-yinstallperl-Config-Tinyperl-Params-Validateperl-Log-Dispatchperl-Parallel-rootdg55 # wgetrootdg55#yum-yinstallperl-Config-Tinyperl-Params-Validateperl-Log-Dispatchperl-Parallel-rootdg55 # wget-manager-0.52.tar.gz-P/usr/l 二：管理節(jié)1：按照步驟一mha 2:安裝mharootdg53# rpm -warning:/var/tmp/rpm-xfer.y

2、qwfYT:HeaderV3DSAsignature:NOKEY,keyID217521f6 # 100%1:epel-rootdg53#lsbase.repo epel.repo epel-testing.repo rhel-rootdg53#yum-yinstallperl-rootdg53 # wget-node-0.52.tar.gz-P/usr/loc rootdg53#cdrootdg53tarbag#tar-zxvpf-node-0.52.tar.gz-Crootdg53tarbag#cd-node-rootdg53 -node-0.52#perlrootdg53 -node-0

3、.52#make&make點；管理節(jié)點的角色類似于 oracle 數(shù)據(jù)庫中的 fast start failover 中的observer，但 mha 上層可以通過 keepalive 部署 程序連接數(shù)據(jù)庫使用VIP，從而實現(xiàn)MHA 節(jié)點包含三個，依賴 perl 模塊； apply_diff_relay_logs:識別差異的 relay log 事件，并應用于其他 salve 服務器purge_relay_logs:清除relaylog件需要在所服務器上安裝MHA 節(jié)點，MHA管理服務器也需要安裝。MHA管理節(jié)點模依賴MHA 節(jié)點模塊MHA 管理節(jié)點通過 ssh 連接管理服務器和執(zhí)行 MHA

4、節(jié)點。MHA 節(jié)點依賴 perl 的 DBD:本文環(huán)境介紹，操作系統(tǒng)均為rhel5.4管理節(jié)點在開始之前，請先配置好服務器間的時間同步和名稱一:在數(shù)據(jù)庫節(jié)點安裝mha nodeMHA 分管理節(jié)點和數(shù)據(jù)庫節(jié)點，數(shù)據(jù)庫節(jié)點，當主庫掛掉后，管理節(jié)點會自動將從節(jié)為主 rootdg55#ssh-keygen-trootdg55#ssh-copy-idrootdg55#ssh-keygen-trootdg55#ssh-copy-id-i/root/.ssh/id_rsa.pub HYPERLINK mailto:root3 rootdg55#ssh-copy-id-i/root/.ssh/id_rsa.p

5、ub HYPERLINK mailto:root4 root4 rootdg55#ssh3ifconfig|grepinetaddr|head-inetaddr:3 Bcast:55 rootdg55#ssh4ifconfig|grepinetaddr|head-inetaddr:4 Bcast:55 4:配置manager 節(jié)點和 node 節(jié)點以及 node 節(jié)點間的ssh rootdg55 -manager-0.52# mkdir rootdg55 -manager-0.52#mkdir-prootdg55 -manager-0.52#cples/conf/*rootdg55 -mana

6、ger-0.52#catserver19. 23. 3:rootdg55#cdrootdg55tarbag#tar-zxvpf-manager-0.52.tar.gz-Crootdg55tarbag# cd -manager-rootdg55 -manager-0.52#perlrootdg55 -manager-0.52#make&makeselectuser,host,password-selectuser,host,password-| user| host| password-|10. 11. |r_test|4|*6BB4837EB74329105EE4568DDA7DC67ED2C

7、A2AD912. -13. 7rowsinset(0.08sec) updateusersethost=192.168.123.%whereQueryOK,1rowaffected(0.05Rowsmatched:1 Changed:1 Warnings:0 QueryOK,0rowsaffected(0.01sec) 6：測試主情況，默認使用 root 用戶連 Wed Jun 6 11:11:25 2012 - warning Global configuration file /etc/mafnotfound.Ski WedJun 611:11:252012-infoReadingappl

8、icationdefaultconfigurationsfrom/etc WedJun 611:11:252012-infoReadingserverconfigurationsfromWedJun 611:11:252012-infoStartingSSHconnectionWedJun 611:11:252012-WedJun 611:11:252012 -debug ConnectingviaSSHfromroot3(3) toroot WedJun 611:11:252012-WedJun 611:11:262012-10. WedJun 611:11:252012-debug Con

9、nectingviaSSHfromroot4(4)toroot 11. WedJun 611:11:262012 -12. WedJun 611:11:262012-infoAll SSHconnectiontestspassed5：測試ssh updateusersethost=192.168.123.%wherehost=localhostanduser=rootandQueryOK,0rowsaffected(0.00Rowsmatched:1 Changed:1 Warnings:updateusersetpassword=PASSWORD(123456)whereuser=roota

10、ndQueryOK,0rowsaffected(0.00Rowsmatched:1 Changed:0 Warnings:flushQueryOK,0rowsaffected(0.02rootdg53#binlog:updateusersethost=192.168.123.%wherehost=localhostanduser=rootandQueryOK,0rowsaffected(0.00Rowsmatched:1 Changed:1 Warnings:updateusersetpassword=PASSWORD(123456)whereuser=rootandQueryOK,0rows

11、affected(0.00Rowsmatched:1 Changed:0 Warnings:flushQueryOK,0rowsaffected(0.02rootdg53#binlog:rootdg53#mvysql*rootdg54#mvysql*rootdg53#ln-s5.5.25/bin/*rootdg54#ln-s5.5.25/bin/* fWed612:39:03-warningGlobalconfigurationfilefnotfound.p.Wed612:39:03-infoReadingapplicationdefaultconfigurationsfromWed612:3

12、9:03-infoReadingserverconfigurationsfromWed612:39:03-infoMHA:MasterMonitorWed612:39:03-infoDeadWed612:39:03-infoAliveWed612:39:03-Wed612:39:03-Wed612:39:03-infoAliveWed612:39:03-4(4:3306) =5.5.25-log(oldestornslaves)log-Wed612:39:03-ReplicatingfromWed612:39:03-PrimarycandidateforthenewMaster(candida

13、te_masterisWed612:39:03-infoCurrentAliveMaster:Wed612:39:03-infoCheckingslaveWed612:39:03-warning read_only=1isnotsetonslaveWed612:39:03-warning relay_log_purge=0isnotsetonslaveWed612:39:03-infoCheckingreplicationfilteringWed612:39:03-info ,test,Wed612:39:03-info ReplicationfilteringcheckWed612:39:0

14、3-infoStartingSSHconnection2&1rootdg55#nohup2&1rootdg55#nohupmasterha_manager-f/tmp/mha_manager.log showslave-|Server_id|Host|Port|Master_id| -2|33061-1rowinset(0.00sec) rootdg53#dShutting. OK 1：當前主庫為 3，關閉三：測試failover1rootdg55 # masterha_check_sus -conf=/etcapp1 (:25516) is running(0:_OK), master:3

15、fandfagain,andtryingtoconnecttoallserverstocheckservers Wed 614:51:352012-warningGlobalconfigurationfilefnotfound.Wed 614:51:352012-infoReadingapplicationdefaultconfigurations fandfagain,andtryingtoconnecttoallserverstocheckservers Wed 614:51:352012-warningGlobalconfigurationfilefnotfound.Wed 614:51

16、:352012-infoReadingapplicationdefaultconfigurationsfromWed 614:51:352012-infoReadingserverconfigurationsfromWed 614:51:352012-infoDeadWed 614:51:352012-Wed 614:51:352012-infoAliveWed 614:51:352012-Wed 614:51:352012-infoAliveWed614:51:352012-4(4:3306) =5.5.25-log(oldestmajornslaves) log-Wed 614:51:35

17、2012-ReplicatingfromWed 614:51:352012-PrimarycandidateforthenewMaster(candidate_masterisWed 614:51:352012-infoCheckingslaveWed 614:51:352012-warning read_only=1isnotsetonslaveWed 614:51:352012-warning relay_log_purge=0isnotsetonslaveWed 614:51:352012-infoCheckingreplicationfilteringWed 614:51:352012

18、-ReplicationfilteringcheckWed 614:51:352012-infoMasterisWed 614:51:352012-infoTerminatingmonitoringWed 614:51:352012-infoGotexitcode20(MasterWed 614:51:352012-infoMHA:MasterFailoverWed 614:51:352012-infoStartingmasterWed 614:51:352012-Wed 614:51:352012-info*Phase1:ConfigurationCheckWed 614:51:352012

19、-Wed 614:51:352012-infoDeadWed 614:51:352012-Wed 614:51:352012-infoCheckingmasterreachability(doubleWed 614:51:352012-Wed 614:51:352012-infoAliveWed 614:51:352012-Wed 614:51:352012-infoAliveWed614:51:352012-4(4:3306) =5.5.25-log(oldestmajornslaves) log-Wed 614:51:352012-ReplicatingfromWed 614:51:352

20、012-PrimarycandidateforthenewMaster(candidate_masterisWed 614:51:352012-info*Phase1:ConfigurationCheckPhaseWed 614:51:352012-Wed 614:51:352012-info*Phase2:DeadMasterShutdownWed 614:51:352012-Wed 614:51:352012-infoForcingshutdowntapplicationsneverconnecttothecurrentWed 614:51:352012-warningmaster_ip_

21、failover_scriptisnotset.invalidatingdeadmasteripWed 614:51:352012-warningshutdown_scriptisnotset.explicitshuttingdownofthedeadWed 614:51:352012-info*Phase2:DeadMasterShutdownPhaseWed 614:51:352012-Wed 614:51:352012-info*Phase3:MasterRecoveryWed 614:51:352012-WedWed 614:51:352012-warningshutdown_scri

22、ptisnotset.explicitshuttingdownofthedeadWed 614:51:352012-info*Phase2:DeadMasterShutdownPhaseWed 614:51:352012-Wed 614:51:352012-info*Phase3:MasterRecoveryWed 614:51:352012-Wed 614:51:352012-info*Phase3.1:GettingLatestSlavesWed 614:51:352012-Wed 614:51:352012-infoThelatestbinarylogfile/ itiononallsl

23、avesWed 614:51:352012-infoLatestslavestreceivedrelaylogfilestotheWed614:51:352012-4(4:3306) =5.5.25-log(oldestmajornslaves) log-Wed 614:51:352012-ReplicatingfromWed 614:51:352012-PrimarycandidateforthenewMaster(candidate_masterisWed 614:51:352012-infoTheoldestbinarylogfile/ itiononallslavesWed 614:5

24、1:352012-infoOldestWed614:51:352012-4(4:3306) =5.5.25-log(oldestmajornslaves) log-Wed 614:51:352012-ReplicatingfromWed 614:51:352012-PrimarycandidateforthenewMaster(candidate_masterisWed 614:51:352012-Wed 614:51:352012-info*Phase3.2:SavingDeadMastersBinlogWed 614:51:352012-Wed 614:51:352012-infoFetc

25、hingdeadmastersbinaryWed614:51:352012-infod on the dead master 3(3:3306): mand=save -bin.000021 -start_ =107- Creating/var/tmpifnotConcatbinary/relaylogs107-bin.000021obinlogformatdescriptionevent,ition0to107.effectivebinlogdatafrom-ition107totail(126).ConcatWedJun 614:51:362012 -infoWed 614:51:3620

26、12-infoHealthCheck:SSHto4isWed 614:51:372012-Wed 614:51:372012-info*Phase3.3:DeterminingNewWed 614:51:372012-Wed 614:51:372012-infoFindingthelatestsallrelaylogsforrecoveringotherWed 614:51:372012-infoAllslavesreceivedrelaylogstotheition.NoneedtoresynceachWed 614:51:372012-infoSearchingnewmasterfromW

27、ed 614:51:372012-CandidatemastersfromtheconfigurationWedJun 614:51:372012-4(4:3306) =5.5.25-log(oldestmajorbet slaves) log-Wed 614:51:372012-ReplicatingfromWed 614:51:372012-PrimarycandidateforthenewMaster(candidate_masterisWed 614:51:372012-Non-candidate WedWedJun 614:51:372012-4(4:3306) =5.5.25-lo

28、g(oldestmajorbet slaves) log-Wed 614:51:372012-ReplicatingfromWed 614:51:372012-PrimarycandidateforthenewMaster(candidate_masterisWed 614:51:372012-Non-candidate Wed614:51:372012-Searchingfromcandidate_masterslaveswhichhavereceivedthelatestrelaylogWed 614:51:372012-infoNewmasterisWed 614:51:372012-i

29、nfoStartingmasterWed 614:51:372012-3 (current +- Wed 614:51:372012-Wed 614:51:372012-info*Phase3.3:NewMasterDiffLogGenerationWed 614:51:372012-Wed614:51:372012-info Thisserverhasallrelaylogs.NoneedtogenerateilesfromthelatestWed 614:51:372012-infoSendingWed 614:51:372012-infoscpWed 614:51:372012-Wed

30、614:51:372012-info*Phase3.4:MasterLogApplyWed 614:51:372012-Wed 614:51:372012-info*NOTICE:Ifanyerrorhappensfromthisphase,manualrecoveryisWed 614:51:372012-infoStartingrecoveryonWed 614:51:372012-GeneratingWed 614:51:372012-infoWaitinguntilallrelaylogsareapp Wed 614:51:372012-Wed 614:51:372012-infoGe

31、ttingslaves Wed 614:51:372012-infoThisslave(4)sequalsRead_Master_Log_ -bin.000021:107).NoneedtorecoverfromExec_Master_Log_ WedJun 614:51:372012-infoConnectingtoslavehost4,runningrecoverWedJun 614:51:372012-infod:mand=apply-slave_host=4-slave_ip=4 - -WedJun 614:51:372012 -Applying differential binary

32、/relay log files /var/tmp/saved_master_binlog_from_3_3306_20120606145135.binlog 4:3306.ThismaytakelongApplyinglogWedJun 614:51:372012 -Allrelaylogsweresuc sfullyapp showmaster- showmaster- | Fileition|Binlog_Do_DB|Binlog_Ignore_DB| - -bin.000023107- 1rowinset(0.003：在原從庫4 上查看結(jié)Wed Jun 6 14:51:37 2012

33、- info Getting new masters binlog name andWed Jun 6 14:51:37 2012 - infoWedJun 614:51:372012-info Allotherslavesshouldstartreplicationfromhere.S ementshouldbe:CHANGEMASTER TO MASTER_HOST=4, MASTER_PORT=3306, MASTER_LOG_FILE=-bin.000023, MASTER_LOG_ =107, MASTER_USER=r_test, MASTER_PASSWORD= ;Wed Jun

34、 6 14:51:37 2012 - warning master_ip_failover_script is not set. Skipernewmasteripaddress. Wed Jun 6 14:51:37 2012 - info * Finished master recovery suc sfully.WedJun 614:51:372012-info*Phase3:MasterRecoveryPhasecompleted. Wed Jun 6 14:51:37 2012 - infoWedJun 614:51:372012-info*Phase4:SlavesRecovery

35、WedJun 614:51:372012 -WedJun 614:51:372012-info*Phase4.1:StartingParallelSlaveDiffLogGenerationPhase. Wed Jun 6 14:51:37 2012 - infoWed Jun 6 14:51:37 2012 - info Generating relay diilesfromthelatestslavesucceeded. Wed Jun 6 14:51:37 2012 - infoWedJun 614:51:372012-info*Phase4.2:StartingParallelSlaveLogApplyPhase. Wed Jun 6 14:51:37 2012 - infoWedJun 614:51:372012-infoAllnewslaveserversrecoveredsuc sfully. Wed Jun 6 14:51:37 2012 - infoWedJun 614:51:372012-info*Phase5:Newmaster