【原创】MySQL双机热备

把最近做的一个项目的实施文档放上来了。

·          软件环境

RHEL 5.5

MySQL 5.1.46企业版

Heartbeat 2.1.4

·          硬件及网络环境

1. 心跳IP网段:192.168.1.0/24 服务IP网段:10.11.196.0/24, VIP(MySQL对外提供服务的IP):

主机名	心跳IP	服务IP
Db-01	192.168.1.11	8
Db-02	192.168.1.12	10.11.196.49

 

2. DELL服务器两台及共享存储

·          配置步骤

注意:以下配置步骤如没有特殊说明, 均是要在两台服务器上做相同操作

1.安装Heartbeat 通过yum方式

 

# yum install heartbeat

 

有下列依赖：

PyXML                  0.8.4-4  

Heartbeat-pils          2.1.411.el5        

heartbeat-stonith       2.1.4-11.el5        

libnet                  1.1.4-3.el5       

libtool-ltdl            1.5.22-6.1     

openhpi-libs            2.14.0-5.el5   

 

2.安装MySQL

使用rpm包安装

# rpm –ivh MySQL-server-enterprise-gpl-5.1.46sp1-1.rhel5.i386.rpm

# rpm –ivh MySQL-client-enterprise-gpl-5.1.46sp1-1.rhel5.i386.rpm

# rpm –ivh MySQL-shared-compat-enterprise-gpl-5.1.46sp1-1.rhel5.i386.rpm

 

注意：安装好后会自动启动mysql，停止mysql然后修改/etc/my.cnf文件，内容参考文档末尾附件A

 

# chkconfig –level 2345 mysql off

 

禁止mysql自动启动，由heartbeat控制其启动。

 

3. 配置Heartbeat的前期准备

 

编辑系统hosts文件: 两台机器hosts配置一样

# vi /etc/hosts   

内容如下:

127.0.0.1 localhost.localdomain localhost

::1       localhost6.localdomain6 localhost6

8  db-01

10.11.196.49  db-02

192.168.1.11 db-01

192.168.1.12  db-02

 

# vi /etc/sysconfig/network

HOSTNAME = db-01   #另外一台是db-02

 

配置网卡服务IP

# vi /etc/sysconfig/network-scripts/ifcfg-eth0

DEVICE=eth0

BOOTPROTO=static

IPADDR=10.11.196.48       #另外一台改成10.11.196.49

 

配置心跳IP

# vi /etc/sysconfig/network-scripts/ifcfg-eth1

DEVICE=eth1

BOOTPROTO=static

BROADCAST=192.168.1.255

IPADDR=192.168.1.11       #另外一台改成192.168.1.12

NETMASK=255.255.255.0

NETWORK=192.168.0.0

ONBOOT=yes

 

配置时间同步服务(ntpd) 修改ntpd配置文件:

# vi /etc/ntp.conf  #在db-01配置ntpserver

restrict default nomodify notrap noquery

server 127.127.1.0

fudge 127.127.0.1 stratum 10

restrict 127.0.0.0 mask 255.0.0.0 notrust nomodify notrap

restrict 192.168.1.0 mask 255.255.255.0 nomodify

driftfile /var/lib/ntp/ntpd.drift

 

启动ntpd服务:

# service ntpd start

 

让ntpd服务开机自启动:

# chkconfig --level 2345 ntpd on

 

在db-02上设置计划任务更新时间

# crontab -e

0-59/30 * * * * /usr/sbin/ntpdate 192.168.1.11

 

# service crond restart

# chkconfig crond on 2345

 

配置防火墙（若开启防火墙）：

 

# vi /etc/sysconfig/iptables

加入以下内容

-A INPUT -p udp --dport 694 -s 10.0.0.2 -j ACCEPT     # 填写对方ip地址

应用服务器ip> -j ACCEPT    

-A INPUT -p tcp --dport 3306 -s <应用服务器ip> -j ACCEPT    

 

4. 配置Heartbeat

 

配置文件有四个分别是: ha.cf,authkeys,haresources,cib.xml

添加以下内容

debugfile /var/log/ha-debug

logfile /var/log/ha-log

logfacility local0

keepalive 1

deadtime 10

warntime 5

30

udpport 694

bcast eth1              # 此网口必须是心跳口，也可写成 ucast eth1 对方ip

auto_failback off      #说明：主节点重启成功后，资源是自动拿到主节点还是等到副节点down调后拿回资源

node db-01 db-02       #说明：节点名称与uname –n保持一致。

ping 10.11.196.254 //网关

respawn hacluster /usr/lib/heartbeat/ipfail

apiauth ipfail uid=hacluster

 

# /etc/ha.d/authkeys

auth 1

1 crc

# chmod 600 /etc/ha.d/authkeys

 

# vi /etc/ha.d/haresources

db-01 10.11.196.50 Filesystem::/dev/sdb1::/mysqld::ext3 mysql

 

5. 启动服务

 

# service heartbeat start

 

设置此机器为standby状态

# /usr/lib/heartbeat/hb_standby

 

检查是否启动成功

# mysql –uroot –pxxxx    #xxxx为密码

 

附件A：

 

# Example MySQL config file for large systems.

#

# This is for a large system with memory = 512M where the system runs mainly

# MySQL.

#

# You can copy this file to

# /etc/my.cnf to set global options,

# mysql-data-dir/my.cnf to set server-specific options (in this

# installation this directory is /var/lib/mysql) or

# ~/.my.cnf to set user-specific options.

#

# In this file, you can use all long options that a program supports.

# If you want to know which options a program supports, run the program

# with the "--help" option.

 

# The following options will be passed to all MySQL clients

[client]

#password = your_password

port     = 3306

socket        = /mysqld/data/mysql.sock

 

# Here follows entries for some specific programs

[mysqld]

port     = 3306

socket        = /mysqld/data/mysql.sock

datadir       = /mysqld/data

log-error = /mysqld/data/mysql.err

pid-file = /mysqld/data/mysql.pid

default-storage-engine = InnoDB

character-set-server = utf8

skip-locking

key_buffer_size = 256M

max_allowed_packet = 100M

table_open_cache = 256

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 256M

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8

 

# Don't listen on a TCP/IP port at all. This can be a security enhancement,

# if all processes that need to connect to mysqld run on the same host.

# All interaction with mysqld must be made via Unix sockets or named pipes.

# Note that using this option without enabling named pipes on Windows

# (via the "enable-named-pipe" option) will render mysqld useless!

#

#skip-networking

 

# Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin

 

# binary logging format - mixed recommended

binlog_format=mixed

 

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id = 1

 

 

# Uncomment the following if you are using InnoDB tables

innodb_data_home_dir = /mysqld/data

innodb_data_file_path = ibdata1:2G;ibdata2:2G;ibdata3:2G;ibdata4:2G:autoextend

innodb_log_group_home_dir = /mysqld/data/

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 2048M

innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 64M

innodb_log_buffer_size = 16M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

 

[mysqldump]

quick

max_allowed_packet = 160M

 

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

 

[myisamchk]

key_buffer_size = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M

 

[mysqlhotcopy]

interactive-timeout