MYSQL主从复制高可用实施手册

'MYSQL主从复制高可用实施手册文档属性标题MYSQL主从复制高可用方案作者Fire9fire9dingh@gmail.com创建日期2007年9月12日星期二版本Release1.0修改日期 声明：该方案不能进行主从自由切换，如果要在主节点当机在恢复使用必须时候手动来操作，首先要进行同步数据，然后在做同步复制，最后在切换！这一切操作必须人为干预！应用需求：双机热备提供备份，冗余功能安装环境：NODE1主机名masterIP地址10.10.10.101NODE2主机名slaveIP地址10.10.10.102VIAIP(漂移IP)10.10.10.100NODE1为主节点，NODE2为从节点，同步的数据库名fire9在安装之前请确认下面的安装包不存在rpm-emysql-devel-4.1.20-1.RHEL4.1rpm-emysql-bench-4.1.20-1.RHEL4.1rpm-ephp-mysql-4.3.9-3.15rpm-elibdbi-dbd-mysql-0.6.5-10.RHEL4.1rpm-emod_auth_mysql-2.6.1-2.2rpm-emysql-server-4.1.20-1.RHEL4.1rpm-eMySQL-python-1.0.0-1.RHEL4.1.i386rpm-eMyODBC-2.50.39-21.RHEL4.1.i386rpm-eqt-MySQL-3.3.3-9.3.i386rpm-emysqlclient10-devel-3.23.58-4.RHEL4.1.i386rpm-emysqlclient10-3.23.58-4.RHEL4.1rpm-ecyrus-sasl-sql-2.1.19-5.EL4.i386rpm-eperl-DBD-MySQL-2.9004-3.1.i386rpm-emysql-4.1.20-1.RHEL4.1安装准备：我已经把相关的软件和配置文件都放在工具包里面了redhatas4update432位mysql-5.0.45-linux-i686-icc-glibc23.tar.gzlibnet-1.1.2.1-1.rh.el.um.1.i386.rpmheartbeat-pils-2.0.4-1.el4.i386.rpmheartbeat-stonith-2.0.4-1.el4.i386.rpmheartbeat-2.0.4-1.el4.i386perl-5.8.8.tar.gzDBI-1.59.tar.gzDBD-mysql-4.005.tar.gzTime-HiRes-01.20.tar.gzPeriod-1.20.tar.gzConvert-BER-1.31.tar.gz Mon-0.11.tar.gzmon-0.99.3-47.tar.gz一、安装MYSQL主从都要做#tarzxvfmysql-5.0.45-linux-i686-icc-glibc23.tar.gz-C/usr/local/#cd/usr/local/#mvmysql-5.0.45-linux-i686-icc-glibc23mysql#cdmysql#groupaddmysql#useradd-gmysqlmysql#passwdmysql#./scripts/mysql_install_db--user=mysql#cpsupport-files/mysql.server/etc/rc.d/init.d/mysqld#chmod+x/etc/rc.d/init.d/mysqld#chkconfig--addmysqld#/etc/rc.d/init.d/mysqldstart把提供的MY.CNF文件拷贝主机的/etc/目录下，根据下面的提示修改所需要的参数把提供的MY.CNF文件拷贝从机的/etc/目录下，根据下面的提示修改所需要的参数主机和从机一样进行操作：vi/etc/my.cnf从机需要注意的是关闭server-id=1打开server-id=2；关闭log-bin=mysql-bin和binlog-do-db=fire9这两个参数;主机打开server-id=1关闭server-id=2；打开log-bin=mysql-bin和打开replicate-do-db=fire9。（fire9指的是需要主从备份的数据库）mysql主从复制的配置在主机上操作#mysqlMysql>createdatabasefire9;#从机也需要建立一样的数据库Mysql>setpasswordforroot@localhost=password(‘123456’);#给ROOT用户建立密码Mysql>flushprivileges;Mysql>GRANTreplicationslaveONfire9.*TOslave@10.10.10.102IDENTIFIEDBY"password";#mysqldump-uroot-pfire9>fire9.sql#scpfire9.sqlroot@10.10.10.102:/tmp在从机上操作导入主机的数据库Mysql>createdatabasefire9;#从机需要建立与主机一样的数据库#mysql-uroot-phipiaosetpasswordforroot@localhost=password(‘123456’);#给ROOT用户建立密码Mysql>flushprivileges;Mysql>CHANGEMASTERTOMASTER_HOST="10.10.10.101",MASTER_PORT=3306,MSTER_USER="slave",----------------此帐号和密码是在主服务器上建立一个复制帐号 MASTER_PASSWORD="password",MASTER_LOG_FILE="mysql-bin.000001",-------这个在主机上通过Mysql>showmasterstatus;命令获得MASTER_LOG_POS=98;----------------这个在主机上通过Mysql>showmasterstatus;命令获得Mysql>Slavestart;Mysql>showslavestatusG;检查一下Master_Log_File和Master_Log_Pos是不是和主机的一样同时下面两个必须都为YES才对。Slave_IO_Running:YesSlave_SQL_Running:Yes二、HEARTBEAT软件包安装---------主从都需要作#rpm-ivhlibnet-1.1.2.1-1.rh.el.um.1.i386.rpm#rpm-ivhheartbeat-pils-2.0.4-1.el4.i386.rpm#rpm-ivhheartbeat-stonith-2.0.4-1.el4.i386.rpm#rpm-ivhheartbeat-2.0.4-1.el4.i386.rpm配置PERL环境#rpm-eperl-DBI-1.40-8#rm-rf/usr/lib/perl#rm-rf/usr/lib/perl5#rm-rf/usr/bin/perl*#rm-rf/usr/share/man/man1/perl*#rm-rf/usr/local/bin/perl*#tarzxvfperl-5.8.8.tar.gz-C/usr/lib/#cd/usr/lib#mvperl-5.8.8perl#cdperl#./Configure-de#make#maketest#makeinstall#tarzxvfDBI-1.59.tar.gz#cdDBI-1.59#perlMakefile.PL#make#maketest#makeinstall#tarzxvfDBD-mysql-4.005.tar.gz-C/usr/lib/#cd/usr/lib/#mvDBD-mysql-4.005dbd#cddbd #perlMakefile.PL#make#makeinstall配置HEARTBEAT相关文件cp/usr/share/doc/heartbeat-2.0.4/authkeys/etc/ha.d#cd/etc/ha.d/Authkeys配置viauthkeys##Authenticationfile.Mustbemode600###Musthaveexactlyoneauthdirectiveatthefront.#authsendauthenticationusingthismethod-id##Then,listthemethodandkeythatgowiththatmethod-id##Availablemethods:crcsha1,md5.Crcdoesn"tneed/wantakey.##Younormallyonlyhaveoneauthenticationmethod-idlistedinthisfile##Putmorethanonetomakeasmoothtransitionwhenchangingauth#methodsand/orkeys.###sha1isbelievedtobethe"best",md5nextbest.##crcaddsnosecurity,exceptfrompacketcorruption.#Useonlyonphysicallysecurenetworks.#auth1#1crc1sha1HI!#3md5Hello!#chmod600authkeyscp/usr/share/doc/heartbeat-2.0.4/haresources/etc/ha.dvi/etc/ha.d/haresources添加如下一行master10.10.10.100-----------------master是指主服务器的主机名-----------------10.10.10.100是指对外提供的虚拟ip-----------------mysqld是指mysqld服务 cp/usr/share/doc/heartbeat-2.0.4/ha.cf/etc/ha.dha.cf配置vi/etc/ha.d/ha.cf##Therearelotsofoptionsinthisfile.Allyouhavetohaveisaset#ofnodeslisted{"node...}oneof{serial,bcast,mcast,orucast},#andavaluefor"auto_failback".##ATTENTION:Astheconfigurationfileisreadlinebyline,#THEORDEROFDIRECTIVEMATTERS!##Inparticular,makesurethattheudpport,serialbaudrate#etc.aresetbeforetheheartbeatmediaaredefined!#debugandlogfiledirectivesgointoeffectwhenthey#areencountered.##Allwillbefineifyoukeepthemorderedasinthisexample.###Noteonlogging:#Ifanyofdebugfile,logfileandlogfacilityaredefinedthenthey#willbeused.Ifdebugfileand/orlogfilearenotdefinedand#logfacilityisdefinedthentherespectivelogginganddebug#messageswillbelogedtosyslog.Iflogfacilityisnotdefined#thendebugfileandlogfilewillbeusedtologmessges.If#logfacilityisnotdefinedanddebugfileand/orlogfilearenot#definedthendefaultswillbeusedfordebugfileandlogfileas#requiredandmessageswillbesentthere.##Filetowritedebugmessagestodebugfile/var/log/ha-debug###Filetowriteothermessagesto#logfile/var/log/ha-log###Facilitytouseforsyslog()/logger##logfacilitylocal0###Anoteonspecifying"howlong"timesbelow... ##Thedefaulttimeunitisseconds#10meanstenseconds##Youcanalsospecifytheminmilliseconds#1500msmeans1.5seconds###keepalive:howlongbetweenheartbeats?#keepalive2##deadtime:howlong-to-declare-host-dead?##Ifyousetthistoolowyouwillgettheproblematic#split-brain(orclusterpartition)problem.#SeetheFAQforhowtousewarntimetotunedeadtime.#deadtime30##warntime:howlongbeforeissuing"lateheartbeat"warning?#SeetheFAQforhowtousewarntimetotunedeadtime.#warntime10###Veryfirstdeadtime(initdead)##Onsomemachines/OSes,etc.thenetworktakesawhiletocomeup#andstartworkingrightafteryou"vebeenrebooted.Asaresult#wehaveaseparatedeadtimeforwhenthingsfirstcomeup.#Itshouldbeatleasttwicethenormaldeadtime.#initdead120###WhatUDPporttouseforbcast/ucastcommunication?#udpport694##Baudrateforserialports...##baud19200# #serialserialportname...#serial/dev/ttyS0#Linux#serial/dev/cuaa0#FreeBSD#serial/dev/cua/a#Solaris###Whatinterfacestobroadcastheartbeatsover?##bcasteth0#Linuxbcasteth1#Linux#bcastle0#Solaris#bcastle1le2#Solaris##Setupamulticastheartbeatmedium#mcast[dev][mcastgroup][port][ttl][loop]##[dev]devicetosend/rcvheartbeatson#[mcastgroup]multicastgrouptojoin(classDmulticastaddress#224.0.0.0-239.255.255.255)#[port]udpporttosendto/rcvfrom(setthisvaluetothe#samevalueas"udpport"above)#[ttl]thettlvalueforoutboundheartbeats.thiseffects#howfarthemulticastpacketwillpropagate.(0-255)#Mustbegreaterthanzero.#[loop]togglesloopbackforoutboundmulticastheartbeats.#ifenabled,anoutboundpacketwillbeloopedbackand#receivedbytheinterfaceitwassenton.(0or1)#Setthisvaluetozero.###mcasteth0225.0.0.169410##Setupaunicast/udpheartbeatmedium#ucast[dev][peer-ip-addr]##[dev]devicetosend/rcvheartbeatson#[peer-ip-addr]IPaddressofpeertosendpacketsto##ucasteth0192.168.1.2###Aboutbooleanvalues...##Anyofthefollowingcase-insensitivevalueswillworkfortrue: #true,on,yes,y,1#Anyofthefollowingcase-insensitivevalueswillworkforfalse:#false,off,no,n,0####auto_failback:determineswhetheraresourcewill#automaticallyfailbacktoits"primary"node,orremain#onwhatevernodeisservingituntilthatnodefails,or#anadministratorintervenes.##Thepossiblevaluesforauto_failbackare:#on-enableautomaticfailbacks#off-disableautomaticfailbacks#legacy-enableautomaticfailbacksinsystems#whereallnodesdonotyetsupport#theauto_failbackoption.##auto_failback"on"and"off"arebackwardscompatiblewiththeold#"nice_failbackon"setting.##SeetheFAQforinformationonhowtoconvert#from"legacy"to"on"withoutaflashcut.#(i.e.,usinga"rollingupgrade"process)##Thedefaultvalueforauto_failbackis"legacy",which#willissueawarningatstartup.So,makesureyouput#anauto_failbackdirectiveinyourha.cffile.#(note:auto_failbackcanbeanybooleanor"legacy")#auto_failbackon###BasicSTONITHsupport#Usingthisdirectiveassumesthatthereisonestonith#deviceinthecluster.Parameterstothisdeviceare#readfromaconfigurationfile.Theformatofthislineis:##stonith##NOTE:itisuptoyoutomaintainthisfileoneachnodeinthe#cluster!##stonithbaytech/etc/ha.d/conf/stonith.baytech ##STONITHsupport#Youcanconfiguremultiplestonithdevicesusingthisdirective.#Theformatofthelineis:#stonith_host#isthemachinethestonithdeviceisattached#toor*tomeanitisaccessiblefromanyhost.#isthetypeofstonithdevice(alistof#supporteddrivesisin/usr/lib/stonith.)#aredriverspecificparameters.Toseethe#formatforaparticulardevice,run:#stonith-l-t###Notethatifyouputyourstonithdeviceaccessinformationin#here,andyoumakethisfilepublicallyreadable,you"reasking#foradenialofserviceattack;-)##Togetalistofsupportedstonithdevices,run#stonith-L#Fordetailedinformationonwhichstonithdevicesaresupported#andtheirdetailedconfigurationoptions,runthiscommand:#stonith-h##stonith_host*baytech10.0.0.3myloginmysecretpassword#stonith_hostken3rps10/dev/ttyS1kathy0#stonith_hostkathyrps10/dev/ttyS1ken30##Watchdogisthewatchdogtimer.Ifourownheartdoesn"tbeatfor#aminute,thenourmachinewillreboot.#NOTE:Ifyouareusingthesoftwarewatchdog,youverylikely#wishtoloadthemodulewiththeparameter"nowayout=0"or#compileitwithoutCONFIG_WATCHDOG_NOWAYOUTset.Otherwiseeven#anorderlyshutdownofheartbeatwilltriggerareboot,whichis#verylikelyNOTwhatyouwant.##watchdog/dev/watchdog##Tellwhatmachinesareinthecluster#nodenodename...--mustmatchuname-n#nodeken3#nodekathy##Lesscommonoptions... ##Treats10.10.10.254asapsuedo-cluster-member#Usedtogetherwithipfailbelow...##ping10.10.10.254##Treats10.10.10.254and10.10.10.253asapsuedo-cluster-member#calledgroup1.Ifeither10.10.10.254or10.10.10.253areup#thengroup1isup#Usedtogetherwithipfailbelow...##ping_groupgroup110.10.10.25410.10.10.253##Processesstartedandstoppedwithheartbeat.Restartedunless#theyexitwithrc=100##respawnuserid/path/name/to/run#respawnhacluster/usr/lib/heartbeat/ipfail##Accesscontrolforclientapi#defaultisnoaccess##apiauthclient-namegid=gidlistuid=uidlist#apiauthipfailgid=haclientuid=hacluster#############################Unusualoptions.##############################hopfudgemaximumhopcountminusnumberofnodesinconfig#hopfudge1##deadping-deadtimeforpingnodes#deadping30##hbgenmethod-Heartbeatgenerationnumbercreationmethod#Normallythesearestoredondiskandincrementedasneeded.#hbgenmethodtime##realtime-enable/disablerealtimeexecution(highpriority,etc.)#defaultstoon#realtimeoff ##debug-setdebuglevel#defaultstozero#debug1##APIAuthentication-replacesthefifo-permissions-basedsystemofthepast###Youcanputauidlistand/oragidlist.#Ifyouputboth,thenaprocessisauthorizedifitqualifiesundereither#theuidlist,orunderthegidlist.##Thegroupname"default"hasspecialmeaning.Ifitisspecified,then#thiswillbeusedforauthorizinggrouplessclients,andanyclientgroups#nototherwisespecified.##apiauthipfailuid=hacluster#apiauthccmuid=hacluster#apiauthpinggid=haclientuid=alanr,root#apiauthdefaultgid=haclient#messageformatinthewire,itcanbeclassicornetstring,defaultisclassic#msgfmtnetstringnodemasternodeslave上面的两个node后面跟的名字可以自己定，只要在vi/etc/hosts在其中添加上面的两行就行。三、安装配置MON相关文件仅仅在主机上安装，从机无需安装#tarzxvfTime-HiRes-01.20.tar.gz#cdTime-HiRes-01.20#perlMakefile.PL#make#makeinstall#tarzxvfPeriod-1.20.tar.gz#cdPeriod-1.20#perlMakefile.PL#make#makeinstall#tarzxvfConvert-BER-1.3101.tar.gz#cdConvert-BER-1.3101#perlMakefile.PL#make#makeinstall #tarzxvfMon-0.11.tar.gz#cdMon-0.11#perlMakefile.PL#make#makeinstall#tarzxvfmon-0.99.3-47.tar.gz-C/usr/lib/#cd/usr/lib/#mvmon-0.99.3-47mon#cdmon#ln-s/usr/lib/mon/etc//etc/monmon.cf配置vi/etc/mon/mon.cf添加如下：##Simplifiedcluster"mon.cf"configurationfile#alertdir=/usr/lib/mon/alert.dmondir=/usr/lib/mon/mon.dstatedir=/usr/lib/mon/state.dlogdir=/var/log/mon/logshistlength=500dtlogging=yesdtlogfile=/var/log/mon/logs/dtloghostgroupmaster10.10.10.100#主机名和虚拟IPwatchmaster#监控的主机servicemysqld#监控MYSQL服务interval5smonitormysql.monitor#负责监控MYSQL服务的文件periodwd{Mon-Sun}alertbring-ha-down.alert#负责停止HEARTBEAT的文件alertmail.alertfire9dingh@gmail.com#发送电邮的参数upalertmail.alertfire9dingh@gmail.comalertevery600salertafter3#cd/usr/lib/mon#mvmon.d/msql-mysql.monitormon.d/mysql.monitorvi/usr/lib/mon/mon.d/mysql.monitor显示如下：#!/usr/bin/perl##$Id:msql-mysql.monitor1.5Thu,21Aug200310:57:47-0400trockij$##arguments: ##[--mode[msql|mysql]]--username=username--password=password#--database=database--port=##hostname##amonitortodetermineifamSQLorMySQLdatabaseserverisoperational##Ratherthanusetcp.monitortoensurethatyourSQLserverisresponding#ontheproperport,thisattemptstoconnecttoandlistthedatabases#onagivendatabaseserver.##Thesingleargument,--mode[msql|mysql]isinferredfromthescriptname#ifitisnamedmysql.monitorormsql.monitor.Thus,thefollowingtwoare#equivalent:##lnmsql-mysql.monitormsql.monitor#lnmsql-mysql.monitormysql.monitor#msql.monitorhostname#mysql.monitorhostname##and##msql-mysql.monitor--modemsqlhostname#msql-mysql.monitor--modemysqlhostname##usethesyntaxthatyoufeelmorecomfortablewith.##Thismonitorrequirestheperl5DBI,DBD::mSQLandDBD::mysqlmodules,#availablefromCPAN(http://www.cpan.org)##Copyright(C)1998,ACCTelEnterprises#WrittenbyJamesFitzGibbon##Thisprogramisfreesoftware;youcanredistributeitand/ormodify#itunderthetermsoftheGNUGeneralPublicLicenseaspublishedby#theFreeSoftwareFoundation;eitherversion2oftheLicense,or#(atyouroption)anylaterversion.##Thisprogramisdistributedinthehopethatitwillbeuseful,#butWITHOUTANYWARRANTY;withouteventheimpliedwarrantyof#MERCHANTABILITYorFITNESSFORAPARTICULARPURPOSE.Seethe#GNUGeneralPublicLicenseformoredetails.##YoushouldhavereceivedacopyoftheGNUGeneralPublicLicense #alongwiththisprogram;ifnot,writetotheFreeSoftware#Foundation,Inc.,59TemplePlace,Suite330,Boston,MA02111-1307USA#useDBI;useGetopt::Long;my@details=();my@failures=();GetOptions(%options,"mode=s","port=i","username=s","password=s","database=s");#uncommentthesetwolinesandprovidesuitableinformationifyoudon"t#wanttopasssensitiveinformationonthecommandline#$options{username}||="username";#$options{password}||="password";if($0=~m//msql.monitor$/||$options{mode}=~m/msql/i){$mode="mSQL";$options{port}=1114if!$options{port};}elsif($0=~m//mysql.monitor/||$options{mode}=~m/mysql/i){$mode="mysql";$options{port}=3306if!$options{port};}else{print"invalidmode$mode!n";exit1;}for$host(@ARGV){my($dbh)=DBI->connect("DBI:mysql:database=fire9;host=localhost","root","123456",{"PrintError"=>1});#仅仅需要修改这一行，改成相应的数据库名、存在的本地用户和密码，host等于的就是localhost.主从的这个连接信息必须一致，我这里测试用了ROOT帐号if(!$dbh){push(@failures,$host);push(@details,"$host:Couldnotconnectto$modeserveron$options{port}:".$DBI::errstr."n");next;}@tables=$dbh->tables();if($#tables<0){push(@failures,$host);push(@details,"$host:Notablesfoundfordatabase$options{database}n");} $dbh->disconnect();}if(@failures){printjoin("",sort@failures),"n";printsort@detailsif(scalar@details>0);exit1;}else{exit0;}#chmod755mon.d/mysql.monitor#vi/usr/lib/mon/alert.d/bring-ha-down.alert添加如下一行/etc/rc.d/init.d/heartbeatstop#chmod755/usr/lib/mon/alert.d/bring-ha-down.alert#vi/etc/rc.d/rc.local#添加MON自启动/usr/lib/mon/mon-f-c/usr/lib/mon/etc/mon.cf需要检查文件权限bring-ha-down.alert#chmod755mysql.monitor#chmod755authkeys#chmod600都配置完成就重新启动一下服务器吧。所有的配置都会自动执行的。启动服务器顺序，先启动主服务器再启动从服务器。通过tail/var/log/messages和tail/var/log/ha-log来查看是否运行正常，ps–ef也可以看到进程的状态，如果主节点MYSQL服务停止就会发送EMAIL到你的邮箱里面。'