mha+keepalived实现mysql master高可用

1.mha概述：

MHA是由日本Mysql专家用Perl写的一套Mysql故障切换方案以保障数据库的高可用性，它的功能是能在0-30s之内实现主Mysql故障转移（failover），

MHA故障转移可以很好的帮我们解决从库数据的一致性问题，同时最大化挽回故障发生后的数据。MHA里有两个角色一个是node节点 一个是manager节点，要实现这个MHA，必须最少要三台数据库服务器，一主多备，即一台充当master，一台充当master的备份机，另外一台是从属机，需要说明的是一旦主服务器宕机，备份机即开始充当master提供服务，如果主服务器上线也不会再成为master了，因为如果这样数据库的一致性就被改变了。

2.实验环境概述：

由于资源有限，我这用两台服务器做此实验。

系统版本：

Red Hat Enterprise Linux Server release 5.8 (Tikanga) x86_642.6.18-308.el5

主从复制配置在此略过，详见可参考：

http://chocolee.blog.51cto.com/8158455/1415173

拓扑图：

图1

故障后转移：

图2

3.部署MHA：

3.1两台主机安装epel源

rpm-ivh http://mirrors.hustunique.com/epel/5/x86_64/epel-release-5-4.noarch.rpm

3.2 两台主机间配置ssh免密钥登陆

mha-node操作：

ssh-keygen-t dsa

cd/root/.ssh

ssh-copy-id-i id_dsa.pub 192.168.199.115

mha-master操作：

ssh-keygen-t dsa

cd/root/.ssh

ssh-copy-id-i id_dsa.pub 192.168.199.113

ssh-copy-id-i id_dsa.pub 192.168.199.115

3.3安装mha

sed-i ‘s#gpgcheck=1#gpgcheck=0#g‘ /etc/yum.conf

两台主机都安装node:

http://mysql-master-ha.googlecode.com/files/mha4mysql-node-0.52-0.noarch.rpm

yumlocalinstall mha4mysql-node-0.52-0.noarch.rpm

master主机安装master:

http://mysql-master-ha.googlecode.com/files/mha4mysql-manager-0.52-0.noarch.rpm

yumlocalinstall mha4mysql-manager-0.52-0.noarch.rpm

3.4 配置mha

mysql添加root远程登陆用户

mha-node操作：

grantall on *.* to ‘root‘@‘192.168.199.%‘ identified by ‘123456‘;

flushprivileges;

此用户将同步到mha-master节点上。

mha-master操作：

将mysql-slave设置read-only

setglobal read_only=1;

app1.conf配置文件

mkdir/etc/masterha/app1 -p

cat>>/etc/masterha/app1/app1.conf <<EOF

[serverdefault] 

manager_workdir=/etc/masterha/app1 

manager_log=/etc/masterha/app1/manager.log 

user=root

password=123456

ssh_user=root

repl_user=rep

repl_password=123456

ping_interval=1

shutdown_script=""

#master_ip_failover_script="/usr/local/bin/master_ip_failover"

master_ip_online_change_script=""

report_script=""

[server1] 

hostname=192.168.199.113 

master_binlog_dir="/usr/local/mysql/data"

candidate_master=1

[server2] 

hostname=192.168.199.115 

master_binlog_dir="/usr/local/mysql/data"

candidate_master=1

EOF

3.5 测试

测试ssh连接

masterha_check_ssh--conf=/etc/masterha/app1/app1.conf

测试主从复制

masterha_check_repl--conf=/etc/masterha/app1/app1.conf

3.6 启动

masterha_manager--conf=/etc/masterha/app1/app1.conf &

关闭主库，观察failover过程

tail-f /etc/masterha/app1/manager.log

-----Failover Report -----

app1:MySQL Master failover 192.168.199.113 to 192.168.199.115 succeeded

Master192.168.199.113 is down!

CheckMHA Manager logs at mha-master:/etc/masterha/app1/manager.log for details.

Startedautomated(non-interactive) failover.

Thelatest slave 192.168.199.115(192.168.199.115:3306) has all relay logs for recovery.

Selected192.168.199.115 as a new master.

192.168.199.115:OK: Applying all logs succeeded.

Generatingrelay diff files from the latest slave succeeded.

192.168.199.115:Resetting slave info succeeded.

Masterfailover to 192.168.199.115(192.168.199.115:3306) completed successfully.

从日志中可观察到，已经切换到了192.168.199.115上，目前环境为图2.

将环境还原成图1，配置keepalived.

4.部署keepalived

4.1安装keepalived(两台都要装)

yuminstall openssl* popt* -y

ln-s /usr/src/kernels/2.6.18-308.el5-x86_64/ /usr/src/linux

ll/usr/src/

mkdir-p /application/tools

cd/application/tools

wget http://www.keepalived.org/software/keepalived-1.1.19.tar.gz 

tarzxf keepalived-1.1.19.tar.gz

cdkeepalived-1.1.19

./configure--sysconf=/etc

make

makeinstall

cp/usr/local/sbin/keepalived /usr/sbin/keepalived

/etc/init.d/keepalivedstart

ps-ef|grep keep

/etc/init.d/keepalivedstop

4.2 配置keepalived

mha-nodekeepalived配置文件：

[root@mha-nodemysql]# cat /etc/keepalived/keepalived.conf

!Configuration File for keepalived

global_defs{

   router_id MHA_01

}

vrrp_scriptcheck_mysql {

  script "sh/etc/keepalived/ck_mysql.sh"

  interval 2

  weight -20

}

vrrp_instanceVI_1 {

    state BACKUP          

    interface eth0

    virtual_router_id 60

    priority 100              

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    virtual_ipaddress {

      192.168.199.118/24

    }

    nopreempt

    track_script {

      check_mysql

    }

}

mha-masterkeepalived配置文件：

[root@mha-masterapp1]# cat /etc/keepalived/keepalived.conf

!Configuration File for keepalived

global_defs{

   router_id MHA_02

}

vrrp_scriptcheck_mysql {

  script "sh/etc/keepalived/ck_mysql.sh"

  interval 2

  weight -20

}                                                    

vrrp_instanceVI_1 {

    state BACKUP       

    interface eth0

    virtual_router_id 60

    priority 90       

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    virtual_ipaddress {

      192.168.199.118/24

    }

    track_script {

      check_mysql

    }

}

停止mha-node主机keepalived服务，测试VIP漂移是否正常（略）

ck_mysql.sh脚本：

#!/bin/sh

/usr/local/mysql/bin/mysql-utest -p123456 -e "select version()" >/dev/null 2>&1

if[ $? -eq 0 ];then

  exit 0

else

  exit 1

fi

5.测试

关闭主库的mysql,测试从库是否被提升为主库，VIP是否漂到到从库上去。

-----Failover Report -----

app1:MySQL Master failover 192.168.199.113 to 192.168.199.115 succeeded

Master192.168.199.113 is down!

CheckMHA Manager logs at mha-master:/etc/masterha/app1/manager.log for details.

Startedautomated(non-interactive) failover.

Thelatest slave 192.168.199.115(192.168.199.115:3306) has all relay logs forrecovery.

Selected192.168.199.115 as a new master.

192.168.199.115:OK: Applying all logs succeeded.

Generatingrelay diff files from the latest slave succeeded.

192.168.199.115:Resetting slave info succeeded.

Master failover to192.168.199.115(192.168.199.115:3306) completed successfully.

[root@mha-masterapp1]# ip addr |grep 192.168.199.

    inet 192.168.199.115/24 brd 192.168.199.255scope global eth0

    inet 192.168.199.118/24 scope global secondary eth0

本文出自 “Chocolee” 博客，请务必保留此出处http://chocolee.blog.51cto.com/8158455/1580221