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

 

标题	MYSQL主从复制高可用方案
作者	Fire9 [e-mail]
创建日期	2007年9月12日星期二
版本	Release 1.0
修改日期

声明：该方案不能进行主从自由切换，如果要在主节点当机在恢复使用必须时候手动来操作，首先要进行同步数据，然后在做同步复制，最后在切换！这一切操作必须人为干预！

 

应用需求：

双机热备提供备份，冗余功能

安装环境：

NODE1 主机名 master      IP地址 10.10.10.101

NODE2 主机名 slave       IP地址 10.10.10.102

VIA  IP(漂移IP) 10.10.10.100

NODE1为主节点，NODE2为从节点，同步的数据库名fire9

 

在安装之前请确认下面的安装包不存在

rpm -e mysql-devel-4.1.20-1.RHEL4.1

rpm -e mysql-bench-4.1.20-1.RHEL4.1

rpm -e php-mysql-4.3.9-3.15

rpm -e libdbi-dbd-mysql-0.6.5-10.RHEL4.1

rpm -e mod_auth_mysql-2.6.1-2.2

rpm -e mysql-server-4.1.20-1.RHEL4.1

rpm -e MySQL-python-1.0.0-1.RHEL4.1.i386

rpm -e MyODBC-2.50.39-21.RHEL4.1.i386

rpm -e  qt-MySQL-3.3.3-9.3.i386

rpm -e mysqlclient10-devel-3.23.58-4.RHEL4.1.i386

rpm -e mysqlclient10-3.23.58-4.RHEL4.1

rpm -e cyrus-sasl-sql-2.1.19-5.EL4.i386

rpm -e  perl-DBD-MySQL-2.9004-3.1.i386

rpm -e mysql-4.1.20-1.RHEL4.1

 

安装准备：我已经把相关的软件和配置文件都放在工具包里面了

redhat as 4 update4 32位

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 主从都要做

# 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/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主从复制的配置

在主机上操作

# mysql

Mysql> create database fire9;  #从机也需要建立一样的数据库

Mysql>set password for root@localhost = password (‘123456’); #给ROOT用户建立密码

Mysql>flush privileges;

Mysql>GRANT replication slave ON fire9.* TO slave@10.10.10.102 IDENTIFIED BY 'password';

# mysqldump -uroot -p fire9 > fire9.sql

# scp fire9.sql root@10.10.10.102:/tmp

 

在从机上操作

导入主机的数据库

Mysql> create database fire9;  #从机需要建立与主机一样的数据库

# mysql -uroot -p hipiao < /tmp/fire9.sql

登入数据库操作

Mysql>set password for root@localhost = password (‘123456’); #给ROOT用户建立密码

Mysql>flush privileges;

Mysql> CHANGE MASTER TO

MASTER_HOST = '10.10.10.101',

MASTER_PORT = 3306,

MSTER_USER = 'slave',---------------- 此帐号和密码是在主服务器上建立一个复制帐号

MASTER_PASSWORD = 'password',

MASTER_LOG_FILE = 'mysql-bin.000001',------- 这个在主机上通过

Mysql>show master status;命令获得

MASTER_LOG_POS = 98;---------------- 这个在主机上通过

Mysql>show master status;命令获得

Mysql> Slave start;

Mysql>show slave status\G;

检查一下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.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.

#       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 doesn't 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 crc

1 sha1 HI!

#3 md5 Hello!

# chmod 600 authkeys

 

cp /usr/share/doc/heartbeat-2.0.4/haresources /etc/ha.d

vi /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.d

ha.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 to

debugfile /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 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 you've 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 rate 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          # Linux

bcast         eth1    # Linux

#bcast      le0             # Solaris

#bcast      le1 le2                # Solaris

#

#       Set up a multicast heartbeat 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 heartbeats.  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 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, 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, you're 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 10.0.0.3 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 doesn't 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

#       node         nodename ...   -- must match uname -n

#node       ken3

#node       kathy

#

#       Less common options...

#

#       Treats 10.10.10.254 as a psuedo-cluster-member

#       Used together with ipfail below...

#

#ping 10.10.10.254

#

#       Treats 10.10.10.254 and 10.10.10.253 as a psuedo-cluster-member

#       called group1. If either 10.10.10.254 or 10.10.10.253 are up

#       then group1 is up

#       Used together with ipfail below...

#

#ping_group group1 10.10.10.254 10.10.10.253

#

#       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.

#

#apiauth  ipfail 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  netstring

node master

node slave

上面的两个node 后面跟的名字可以自己定，只要在vi /etc/hosts 在其中添加上面的两行就行。