搜索
MYSQL主从复制高可用实施手册文档属性声明：该方案不能进行主从自由切换，如果要在主节点当机在恢复使用必须时候手动来操作，首先要进行同步数据，然后在做同步复制，最后在切换！这一切操作必须人为干预！应用需求：双机热备提供备份，冗余功能安装环境：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.rpm标题MYSQL主从复制高可用方案作者Fire9fire9dingh@gmail.com创建日期2007年9月12日星期二版本Release1.0修改日期heartbeat-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.gzMon-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主从复制的配置在主机上操作#mysqlMysqlcreatedatabasefire9;#从机也需要建立一样的数据库Mysqlsetpasswordforroot@localhost=password(‘123456’);#给ROOT用户建立密码Mysqlflushprivileges;MysqlGRANTreplicationslaveONfire9.*TOslave@10.10.10.102IDENTIFIEDBY'password';#mysqldump-uroot-pfire9fire9.sql#scpfire9.sqlroot@10.10.10.102:/tmp在从机上操作导入主机的数据库Mysqlcreatedatabasefire9;#从机需要建立与主机一样的数据库#mysql-uroot-phipiao/tmp/fire9.sql登入数据库操作Mysqlsetpasswordforroot@localhost=password(‘123456’);#给ROOT用户建立密码Mysqlflushprivileges;MysqlCHANGEMASTERTOMASTER_HOST='10.10.10.101',MASTER_PORT=3306,MSTER_USER='slave',----------------此帐号和密码是在主服务器上建立一个复制帐号MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',-------这个在主机上通过Mysqlshowmasterstatus;命令获得MASTER_LOG_POS=98;----------------这个在主机上通过Mysqlshowmasterstatus;命令获得MysqlSlavestart;Mysqlshowslavestatus\G;检查一下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.###sha1isbelievedtobethebest,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},#andavalueforauto_failback.##ATTENTION:Astheconfigurationfileisreadlinebyline,#THEORDEROFDIRECTIVEMATTERS!##Inparticular,makesurethattheudpport,serialbaudrate#etc.aresetbeforetheheartbeatmediaaredefined!#debugandlogfiledirectivesgointoeffectwhenthey#areencountered.##Allwillbefineifyoukeepthemorderedasinthisexample.###Noteonlogging:#Ifanyofdebugfile,logfileandlogfacilityaredefinedthenthey#willbeused.Ifdebugfileand/orlogfilearenotdefinedand#logfacilityisdefinedthentherespectivelogginganddebug#messageswillbelogedtosyslog.Iflogfacilityisnotdefined#thendebugfileandlogfilewillbeusedtologmessges