MYSQL主从复制高可用手册.docx
《MYSQL主从复制高可用手册.docx》由会员分享，可在线阅读，更多相关《MYSQL主从复制高可用手册.docx（21页珍藏版）》请在冰豆网上搜索。
MYSQL主从复制高可用手册
MYSQL主从复制高可用实施手册
文档属性
标题
MYSQL主从复制高可用方案
作者
Fire9fire9dingh@
创建日期
2007年9月12日星期二
版本
Release1.0
修改日期
声明：
该方案不能进行主从自由切换，如果要在主节点当机在恢复使用必须时候手动来操作，首先要进行同步数据，然后在做同步复制，最后在切换！
这一切操作必须人为干预！
应用需求：
双机热备提供备份，冗余功能
安装环境：
NODE1主机名masterIP地址10.10.10.101
NODE2主机名slaveIP地址10.10.10.102
VIAIP（漂移IP）10.10.10.100
NODE1为主节点，NODE2为从节点，同步的数据库名fire9
在安装之前请确认下面的安装包不存在
rpm-emysql-devel-4.1.20-1.RHEL4.1
rpm-emysql-bench-4.1.20-1.RHEL4.1
rpm-ephp-mysql-4.3.9-3.15
rpm-elibdbi-dbd-mysql-0.6.5-10.RHEL4.1
rpm-emod_auth_mysql-2.6.1-2.2
rpm-emysql-server-4.1.20-1.RHEL4.1
rpm-eMySQL-python-1.0.0-1.RHEL4.1.i386
rpm-eMyODBC-2.50.39-21.RHEL4.1.i386
rpm-eqt-MySQL-3.3.3-9.3.i386
rpm-emysqlclient10-devel-3.23.58-4.RHEL4.1.i386
rpm-emysqlclient10-3.23.58-4.RHEL4.1
rpm-ecyrus-sasl-sql-2.1.19-5.EL4.i386
rpm-eperl-DBD-MySQL-2.9004-3.1.i386
rpm-emysql-4.1.20-1.RHEL4.1
安装准备：
我已经把相关的软件和配置文件都放在工具包里面了
redhatas4update432位
mysql-5.0.45-linux-i686-icc-glibc23.tar.gz
libnet-1.1.2.1-1.rh.el.um.1.i386.rpm
heartbeat-pils-2.0.4-1.el4.i386.rpm
heartbeat-stonith-2.0.4-1.el4.i386.rpm
heartbeat-2.0.4-1.el4.i386
perl-5.8.8.tar.gz
DBI-1.59.tar.gz
DBD-mysql-4.005.tar.gz
Time-HiRes-01.20.tar.gz
Period-1.20.tar.gz
Convert-BER-1.31.tar.gz
Mon-0.11.tar.gz
mon-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/f
从机需要注意的是关闭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主从复制的配置
在主机上操作
#mysql
Mysql>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-phipiao
登入数据库操作
Mysql>setpasswordforroot@localhost=password（‘123456’）;#给ROOT用户建立密码
Mysql>flushprivileges;
Mysql>CHANGEMASTERTO
MASTER_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>showslavestatus\G;
检查一下Master_Log_File和Master_Log_Pos是不是和主机的一样同时下面两个必须都为YES才对。
Slave_IO_Running:
Yes
Slave_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
#1crc
1sha1HI!
#3md5Hello!
#chmod600authkeys
cp/usr/share/doc/heartbeat-2.0.4/haresources/etc/ha.d
vi/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.d
ha.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.
#
#Filetowritedebugmessagesto
debugfile/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#Linux
bcasteth1#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<
升级会员 