MYSQL主从复制高可用手册.doc

MYSQL主从复制高可用实施手册文档属性标题MYSQL主从复制高可用方案作者Fire9 创建日期2007年9月12日星期二版本Release 1.0修改日期声明：该方案不能进行主从自由切换，如果要在主节点当机在恢复使用必须时候手动来操作，首先要进行同步数据，然后在做同步复制，最后在切换！这一切操作必须人为干预！应用需求：双机热备提供备份，冗余功能安装环境：NODE1 主机名 master IP地址 01NODE2 主机名 slave IP地址 02VIA IP(漂移IP) 00NODE1为主节点，NODE2为从节点，同步的数据库名fire9在安装之前请确认下面的安装包不存在rpm -e mysql-devel-4.1.20-1.RHEL4.1rpm -e mysql-bench-4.1.20-1.RHEL4.1rpm -e php-mysql-4.3.9-3.15rpm -e libdbi-dbd-mysql-0.6.5-10.RHEL4.1rpm -e mod_auth_mysql-2.6.1-2.2rpm -e mysql-server-4.1.20-1.RHEL4.1rpm -e MySQL-python-1.0.0-1.RHEL4.1.i386rpm -e MyODBC-2.50.39-21.RHEL4.1.i386rpm -e qt-MySQL-3.3.3-9.3.i386rpm -e mysqlclient10-devel-3.23.58-4.RHEL4.1.i386rpm -e mysqlclient10-3.23.58-4.RHEL4.1rpm -e cyrus-sasl-sql-2.1.19-5.EL4.i386rpm -e perl-DBD-MySQL-2.9004-3.1.i386rpm -e mysql-4.1.20-1.RHEL4.1安装准备：我已经把相关的软件和配置文件都放在工具包里面了redhat as 4 update4 32位 mysql-5.0.45-linux-i686-icc-glibc23.tar.gzlibnet--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.gzMon-0.11.tar.gzmon-0.99.3-47.tar.gz一、安装MYSQL 主从都要做# tar zxvf mysql-5.0.45-linux-i686-icc-glibc23.tar.gz -C /usr/local/# cd /usr/local/# mv mysql-5.0.45-linux-i686-icc-glibc23 mysql# cd mysql# groupadd mysql# useradd -g mysql mysql#passwd mysql# ./scripts/mysql_install_db -user=mysql# cp support-files/mysql.server /etc/rc.d/init.d/mysqld# chmod +x /etc/rc.d/init.d/mysqld# chkconfig -add mysqld# /etc/rc.d/init.d/mysqld start把提供的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主从复制的配置在主机上操作# mysqlMysql create database fire9; #从机也需要建立一样的数据库Mysqlset password for rootlocalhost = password (123456); #给ROOT用户建立密码Mysqlflush privileges; MysqlGRANT replication slave ON fire9.* TO slave02 IDENTIFIED BY password;# mysqldump -uroot -p fire9 fire9.sql# scp fire9.sql root02:/tmp在从机上操作导入主机的数据库Mysql create database fire9; #从机需要建立与主机一样的数据库# mysql -uroot -p hipiao set password for rootlocalhost = password (123456); #给ROOT用户建立密码Mysqlflush privileges; Mysql CHANGE MASTER TOMASTER_HOST = 01,MASTER_PORT = 3306,MSTER_USER = slave,- 此帐号和密码是在主服务器上建立一个复制帐号MASTER_PASSWORD = password,MASTER_LOG_FILE = mysql-bin.000001,- 这个在主机上通过Mysqlshow master status;命令获得MASTER_LOG_POS = 98;- 这个在主机上通过Mysqlshow master status;命令获得Mysql Slave start;Mysqlshow slave statusG;检查一下Master_Log_File 和Master_Log_Pos是不是和主机的一样同时下面两个必须都为YES才对。 Slave_IO_Running: Yes Slave_SQL_Running: Yes二、HEARTBEAT软件包安装-主从都需要作# rpm -ivh libnet--1.rh.el.um.1.i386.rpm# rpm -ivh heartbeat-pils-2.0.4-1.el4.i386.rpm#rpm -ivh heartbeat-stonith-2.0.4-1.el4.i386.rpm# rpm -ivh heartbeat-2.0.4-1.el4.i386.rpm配置PERL环境# rpm -e perl-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*# tar zxvf perl-5.8.8.tar.gz -C /usr/lib/#cd /usr/lib# mv perl-5.8.8 perl# cd perl# ./Configure -de# make# make test# make install# tar zxvf DBI-1.59.tar.gz#cd DBI-1.59# perl Makefile.PL# make# make test# make install# tar zxvf DBD-mysql-4.005.tar.gz -C /usr/lib/# cd /usr/lib/# mv DBD-mysql-4.005 dbd# cd dbd# perl Makefile.PL# make# make install配置HEARTBEAT相关文件cp /usr/share/doc/heartbeat-2.0.4/authkeys /etc/ha.d# cd /etc/ha.d/Authkeys配置vi authkeys#Authentication file. Must be mode 600#Must have exactly one auth directive at the front.#authsend authentication using this method-id#Then, list the method and key that go with that method-id#Available methods: crc sha1, md5. Crc doesnt need/want a key.#You normally only have one authentication method-id listed in this file#Put more than one to make a smooth transition when changing auth#methods and/or keys.#sha1 is believed to be the best, md5 next best.#crc adds no security, except from packet corruption.#Use only on physically secure networks.#auth 1#1 crc1 sha1 HI!#3 md5 Hello!# chmod 600 authkeyscp /usr/share/doc/heartbeat-2.0.4/haresources /etc/ha.dvi /etc/ha.d/ haresources 添加如下一行 master 00- master是指主服务器的主机名- 00是指对外提供的虚拟ip- mysqld是指mysqld服务cp /usr/share/doc/heartbeat-2.0.4/ha.cf /etc/ha.dha.cf配置vi /etc/ha.d/ha.cf#There are lots of options in this file. All you have to have is a set#of nodes listed node . one of serial, bcast, mcast, or ucast,#and a value for auto_failback.#ATTENTION: As the configuration file is read line by line,# THE ORDER OF DIRECTIVE MATTERS!#In particular, make sure that the udpport, serial baud rate#etc. are set before the heartbeat media are defined!#debug and log file directives go into effect when they#are encountered.#All will be fine if you keep them ordered as in this example.# Note on logging:# If any of debugfile, logfile and logfacility are defined then they# will be used. If debugfile and/or logfile are not defined and# logfacility is defined then the respective logging and debug# messages will be loged to syslog. If logfacility is not defined# then debugfile and logfile will be used to log messges. If# logfacility is not defined and debugfile and/or logfile are not# defined then defaults will be used for debugfile and logfile as# required and messages will be sent there.#File to write debug messages todebugfile /var/log/ha-debug# File to write other messages to#logfile/var/log/ha-log#Facility to use for syslog()/logger #logfacilitylocal0#A note on specifying how long times below.#The default time unit is seconds#10 means ten seconds#You can also specify them in milliseconds#1500ms means 1.5 seconds#keepalive: how long between heartbeats?#keepalive 2#deadtime: how long-to-declare-host-dead?#If you set this too low you will get the problematic#split-brain (or cluster partition) problem.#See the FAQ for how to use warntime to tune deadtime.#deadtime 30#warntime: how long before issuing late heartbeat warning?#See the FAQ for how to use warntime to tune deadtime.#warntime 10#Very first dead time (initdead)#On some machines/OSes, etc. the network takes a while to come up#and start working right after youve been rebooted. As a result#we have a separate dead time for when things first come up.#It should be at least twice the normal dead time.#initdead 120#What UDP port to use for bcast/ucast communication?#udpport694#Baud rate for serial ports.#baud19200#serialserialportname .#serial/dev/ttyS0# Linux#serial/dev/cuaa0# FreeBSD#serial/dev/cua/a# Solaris#What interfaces to broadcast heartbeats over?#bcasteth0# Linuxbcasteth1 # Linux#bcastle0# Solaris#bcastle1 le2# Solaris#Set up a multicast heartbeat medium#mcast dev mcast group port ttl loop#devdevice to send/rcv heartbeats on#mcast groupmulticast group to join (class D multicast address# - 55)#portudp port to sendto/rcvfrom (set this value to the#same value as udpport above)#ttlthe ttl value for outbound heartbeats. this effects#how far the multicast packet will propagate. (0-255)#Must be greater than zero.#looptoggles loopback for outbound multicast heartbeats.#if enabled, an outbound packet will be looped back and#received by the interface it was sent on. (0 or 1)#Set this value to zero.#mcast eth0 694 1 0#Set up a unicast / udp heartbeat medium#ucast dev peer-ip-addr#devdevice to send/rcv heartbeats on#peer-ip-addrIP address of peer to send packets to#ucast eth0 #About boolean values.#Any of the following case-insensitive values will work for true:#true, on, yes, y, 1#Any of the following case-insensitive values will work for false:#false, off, no, n, 0#auto_failback: determines whether a resource will#automatically fail back to its primary node, or remain#on whatever node is serving it until that node fails, or#an administrator intervenes.#The possible values for auto_failback are:#on- enable automatic failbacks#off- disable automatic failbacks#legacy- enable automatic failbacks in systems#where all nodes do not yet support#the auto_failback option.#auto_failback on and off are backwards compatible with the old#nice_failback on setting.#See the FAQ for information on how to convert#from legacy to on without a flash cut.#(i.e., using a rolling upgrade process)#The default value for auto_failback is legacy, which#will issue a warning at startup. So, make sure you put#an auto_failback directive in your ha.cf file.#(note: auto_failback can be any boolean or legacy)#auto_failback on# Basic STONITH support# Using this directive assumes that there is one stonith # device in the cluster. Parameters to this device are # read from a configuration file. The format of this line is:# stonith # NOTE: it is up to you to maintain this file on each node in the# cluster!#stonith baytech /etc/ha.d/conf/stonith.baytech# STONITH support# You can configure multiple stonith devices using this directive.# The format of the line is:# stonith_host # is the machine the stonith device is attached# to or * to mean it is accessible from any host. # is the type of stonith device (a list of# supported drives is in /usr/lib/stonith.)# are driver specific parameters. To see the# format for a particular device, run:# stonith -l -t #Note that if you put your stonith device access information in#here, and you make this file publically readable, youre asking#for a denial of service attack ;-)#To get a list of supported stonith devices, run#stonith -L#For detailed information on which stonith devices are supported#and their detailed configuration options, run this command:#stonith -h#stonith_host * baytech mylogin mysecretpassword#stonith_host ken3 rps10 /dev/ttyS1 kathy 0 #stonith_host kathy rps10 /dev/ttyS1 ken3 0 #Watchdog is the watchdog timer. If our own heart doesnt beat for#a minute, then our machine will reboot.#NOTE: If you are using the software watchdog, you very likely#wish to load the module with the parameter nowayout=0 or#compile it without CONFIG_WATCHDOG_NOWAYOUT set. Otherwise even#an orderly shutdown of heartbeat will trigger a reboot, which is#very likely NOT what you want.#watchdog /dev/watchdog# #Tell what machines are in the cluster#nodenodename .- must match uname -n#nodeken3#nodekathy#Less common options.#Treats 54 as a psuedo-cluster-member#Used together with ipfail below.#ping 54#Treats 54 and 53 as a psuedo-cluster-member# called group1. If either 54 or 53 are up# then group1 is up#Used together with ipfail below.#ping_group group1 54 53#Processes started and stopped with heartbeat. Restarted unless#they exit with rc=100#respawn userid /path/name/to/run#respawn hacluster /usr/lib/heartbeat/ipfail#Access control for client api# default is no access#apiauth client-name gid=gidlist uid=uidlist#apiauth ipfail gid=haclient uid=hacluster#Unusual options.#hopfudge maximum hop count minus number of nodes in config#hopfudge 1#deadping - dead time for ping nodes#deadping 30#hbgenmethod - Heartbeat generation number creation method#Normally these are stored on disk and incremented as needed.#hbgenmethod time#realtime - enable/disable realtime execution (high priority, etc.)#defaults to on#realtime off#debug - set debug level#defaults to zero#debug 1#API Authentication - replaces the fifo-permissions-based system of the past#You can put a uid list and/or a gid list.#If you put both, then a process is authorized if it qualifies under either#the uid list, or under the gid list.#The groupname default has special meaning. If it is specified, then#this will be used for authorizing groupless clients, and any client groups#not otherwise specified.#apiauthipfail uid=hacluster#apiauth ccm uid=hacluster#apiauth ping gid=haclient uid=alanr,root#apiauth default gid=haclient# message format in the wire, it can be classic or netstring, default is classic#msgfmt netstringnode masternode slave上面的两个node 后面跟的名字可以自己定，只要在vi /etc/hosts 在其中添加上面的两行就行。三、安装配置MON相关文件 仅仅在主机上安装，从机无需安装# tar zxvf Time-HiRes-01.20.tar.gz# cd Time-HiRes-01.20# perl Makefile.PL# make# make install# tar zxvf Period-1.20.tar.gz# cd Period-1.20# perl Makefile.PL# make# make install# tar zxvf Convert-BER-1.3101.tar.gz# cd Convert-BER-1.3101# perl Makefile.PL# make# make install# tar zxvf Mon-0.11.tar.gz# cd Mon-0.11# perl Makefile.PL# make# make install# tar zxvf mon-0.99.3-47.tar.gz -C /usr/lib/# cd /usr/lib/# mv mon-0.99.3-47 mon# cd mon # ln -s /usr/lib/mon/etc/ /etc/monmon.cf配置vi /etc/mon/mon.cf 添加如下：# Simplified cluster mon.cf configuration file#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/dtloghostgroup master 00 #主机名和虚拟IPwatch master #监控的主机 service mysqld #监控MYSQL服务 interval 5s monitor mysql.monitor #负责监控MYSQL服务的文件 period wd Mon-Sun alert bring-ha-down.alert #负责停止HEARTBEAT的文件 alert mail.alert #发送电邮的参数 upalert mail.alert alertevery 600s alertafter 3# cd /usr/lib/mon# mv mon.d/msql-mysql.monitor mon.d/mysql.monitorvi /usr/lib/mon/mon.d/mysql.monitor 显示如下：#!/usr/bin/perl# $Id: msql-mysql.monitor 1.5 Thu, 21 Aug 2003 10:57:47 -0400 trockij $# arguments:# -mode msql|mysql -username=username -password=password# -database=database -port=#hostname# a monitor to determine if a mSQL or MySQL database server is operational# Rather than use tcp.monitor to ensure that your SQL server is responding# on the proper port, this attempts to connect to and list the databases# on a given database server.# The single argument, -mode msql|mysql is inferred from the script name# if it is named mysql.monitor or msql.monitor. Thus, the following two are# equivalent:# ln msql-mysql.monitor msql.monitor# ln msql-mysql.monitor mysql.monitor# msql.monitor hostname# mysql.monitor hostname# and# msql-mysql.monitor -mode msql hostname# msql-mysql.monitor -mode mysql hostname# use the syntax that you feel more comfortable with.# This monitor requires the perl5 DBI, DBD:mSQL and DBD:mysql modules,# available from CPAN ()# Copyright (C) 1998, ACC TelEnterprises# Written by James FitzGibbon # This program is free software; you can redistribute it and/or modify# it under the terms of the GNU General Public License as published by# the Free Software Foundation; either version 2 of the License, or# (at your option) any later version.# This program is distributed in the hope that it will be useful,# but WITHOUT ANY WARRANTY; without even the implied warranty of# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the# GNU General Public License for more details.# You should have received a copy of the GNU General Public License# along with this p