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

1、MYSQL主从复制高可用手册MYSQL主从复制高可用实施手册文档属性标题MYSQL主从复制高可用方案作者Fire9 fire9dingh创建日期2007年9月12日星期二版本Release 1.0修改日期声明：该方案不能进行主从自由切换，如果要在主节点当机在恢复使用必须时候手动来操作，首先要进行同步数据，然后在做同步复制，最后在切换！这一切操作必须人为干预！应用需求：双机热备提供备份，冗余功能安装环境：NODE1 主机名 master IP地址 10.10.10.101NODE2 主机名 slave IP地址 10.10.10.102VIA IP(漂移IP) 10.10.10.100NODE1

2、为主节点，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 MyODB

3、C-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位 m

4、ysql-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.gzMon-0.

5、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/mysq

6、l.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

7、这两个参数;主机打开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 fire

8、9.* TO slave10.10.10.102 IDENTIFIED BY password;# mysqldump -uroot -p fire9 fire9.sql# scp fire9.sql root10.10.10.102:/tmp在从机上操作导入主机的数据库Mysql create database fire9; #从机需要建立与主机一样的数据库# mysql -uroot -p hipiao set password for rootlocalhost = password (123456); #给ROOT用户建立密码Mysqlflush privileges; Mysql C

9、HANGE MASTER TOMASTER_HOST = 10.10.10.101,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 statu

10、sG;检查一下Master_Log_File 和Master_Log_Pos是不是和主机的一样同时下面两个必须都为YES才对。 Slave_IO_Running: Yes Slave_SQL_Running: Yes二、HEARTBEAT软件包安装-主从都需要作# rpm -ivh libnet-1.1.2.1-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

11、.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 i

12、nstall# 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/

13、ha.d/Authkeys配置vi authkeys# Authentication file. Must be mode 600# Must have exactly one auth directive at the front.# auth send 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

14、 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

15、 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 10.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# There are

16、 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

17、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

18、. 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 no

19、t# 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 #logfacility local0# A note on speci

20、fying 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 problemati

21、c# 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

22、. 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?#udpport 694# Baud r

23、ate for serial ports.#baud 19200# # serial serialportname .#serial /dev/ttyS0 # Linux#serial /dev/cuaa0 # FreeBSD#serial /dev/cua/a # Solaris# What interfaces to broadcast heartbeats over?#bcast eth0 # Linuxbcast eth1 # Linux#bcast le0 # Solaris#bcast le1 le2 # Solaris# Set up a multicast heartbeat

24、medium# mcast dev mcast group port ttl loop# dev device to send/rcv heartbeats on# mcast group multicast group to join (class D multicast address# 224.0.0.0 - 239.255.255.255)# port udp port to sendto/rcvfrom (set this value to the# same value as udpport above)# ttl the ttl value for outbound heartb

25、eats. this effects# how far the multicast packet will propagate. (0-255)# Must be greater than zero.# loop toggles 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

26、eth0 225.0.0.1 694 1 0# Set up a unicast / udp heartbeat medium# ucast dev peer-ip-addr# dev device to send/rcv heartbeats on# peer-ip-addr IP address of peer to send packets to#ucast eth0 192.168.1.2# About boolean values.# Any of the following case-insensitive values will work for true:# true, on,

27、 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

28、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

29、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