ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > MySQL主主+Keepalived+LVS高可用（三）：单点写入、读负载均衡

MySQL主主+Keepalived+LVS高可用（三）：单点写入、读负载均衡

原创 MySQL 作者：jx_yu 时间：2014-12-11 17:29:43 0 删除 编辑

方案简介

本方案采用 keepalived 中的sorry_server 来实现写入数据库为单点的需求，读负载均衡通过 lvs 实现，读能自由的实现负载均衡和故障切换。本方案实现的功能是当网络有问题、 mysql 有问题、服务器宕机、 keepalived 服务停止后,服务器能自动跳转到备用机，当主服务器服务
启动起来后会自动切换回来。

1)    KeepAlived配置

Master A

~]# cat /etc/keepalived/keepalived.conf

! Configuration File for keepalived

 

! Configuration File for keepalived

 

global_defs {

   notification_email {

     jixiang.yu@trekiz.com

   }

   notification_email_from Alexandre.Cassen@firewall.loc

   smtp_server 127.0.0.1

   smtp_connect_timeout 30

   router_id MySQL_HA

}

 

vrrp_instance MySQL-HA{

    state MASTER

    interface eth1

    virtual_router_id 51

    priority 100

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    virtual_ipaddress {

        192.168.56.111

                192.168.56.112

    }

}

virtual_server 192.168.56.111 3306{

    delay_loop 3

    lb_algo rr

    lb_kind DR

    persistence_timeout 20

    protocol TCP

    sorry_server 192.168.56.102 3306

    real_server 192.168.56.101 3306{

        weight 3

        TCP_CHECK {

            connect_timeout 10

            nb_get_retry 3

            delay_before_retry 3

            connect_port 3306

        }

    }

}

virtual_server 192.168.56.112 3306{

    delay_loop 3

    lb_algo rr

lb_kind DR

persistence_timeout 20

    protocol TCP

    real_server 192.168.56.101 3306{

        weight 3

        TCP_CHECK {

            connect_timeout 10

            nb_get_retry 3

            delay_before_retry 3

            connect_port 3306

        }

    }

 

    real_server 192.168.56.102 3306{

        weight 3

        TCP_CHECK {

            connect_timeout 10

            nb_get_retry 3

            delay_before_retry 3

            connect_port 3306

        }

    }

}

Master B

~]# cat /etc/keepalived/keepalived.conf

global_defs {

   notification_email {

     jixiang.yu@trekiz.com

   }

   notification_email_from Alexandre.Cassen@firewall.loc

   smtp_server 127.0.0.1

   smtp_connect_timeout 30

   router_id MySQL_HA

}

 

vrrp_instance MySQL-HA{

    state BACKUP

    interface eth1

    virtual_router_id 51

    priority 90

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    virtual_ipaddress {

        192.168.56.111

    }

}

 

virtual_server 192.168.56.111 3306{

    delay_loop 3

    lb_algo rr

    lb_kind DR

    persistence_timeout 20

    protocol TCP

    sorry_server 192.168.56.102 3306

    real_server 192.168.56.101 3306{

        weight 3

        TCP_CHECK {

            connect_timeout 10

            nb_get_retry 3

            delay_before_retry 3

            connect_port 3306

        }

    }

}

 

virtual_server 192.168.56.112 3306{

    delay_loop 3

    lb_algo rr

lb_kind DR

persistence_timeout 20

    protocol TCP

    real_server 192.168.56.101 3306{

        weight 3

        TCP_CHECK {

            connect_timeout 10

            nb_get_retry 3

            delay_before_retry 3

            connect_port 3306

        }

    }

 

    real_server 192.168.56.102 3306{

        weight 3

        TCP_CHECK {

            connect_timeout 10

            nb_get_retry 3

            delay_before_retry 3

            connect_port 3306

        }

    }

2)    realserver配置

#A和B一样

~]# cat /etc/init.d/realserver

#!/bin/bash

VIP=192.168.56.111

VIP2=192.168.56.112

. /etc/rc.d/init.d/functions                    

case "$1" in

start)

    /sbin/ifconfig lo:0 $VIP netmask 255.255.255.255 broadcast $VIP

    /sbin/ifconfig lo:1 $VIP2 netmask 255.255.255.255 broadcast $VIP2

    /sbin/route add -host $VIP dev lo:0

    /sbin/route add -host $VIP2 dev lo:1

    echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore   

    echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce   

    echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore   

    echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce   

    /sbin/sysctl -p >/dev/null 2>&1

    echo "LVS-DR real server starts successfully."                   

    ;;   

stop)   

    /sbin/ifconfig lo:0 down   

    /sbin/ifconfig lo:1 down   

    /sbin/route del $VIP >/dev/null 2>&1   

    /sbin/route del $VIP2 >/dev/null 2>&1   

    echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore   

    echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce   

    echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore   

    echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce   

    echo "LVS-DR real server stopped."

    ;;

status)

    isLoOn=`/sbin/ifconfig lo:0 | grep "$VIP"`

    isRoOn=`/bin/netstat -rn | grep "$VIP"`

    if [ "$isLoOn" == "" -a "$isRoOn" == "" ]; then

       echo "LVS-DR real server has to run yet."

    else

       echo "LVS-DR real server is running."

    fi

    exit 3

    ;;

*)   

    echo "Usage: $0 {start|stop|status}"

    exit 1   

esac                    

exit 0

3)    启动realserver和KeepAlived服务

#A和B都分别启动

~]# /etc/init.d/realserver start

LVS-DR real server starts successfully.

~]# /etc/init.d/keepalived start

Starting keepalived:

Master A~]# ipvsadm -ln

IP Virtual Server version 1.2.1 (size=4096)

Prot LocalAddress:Port Scheduler Flags

  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn

TCP  192.168.56.111:3306 rr persistent 20

  -> 192.168.56.101:3306          Local   3      1          0        

TCP  192.168.56.112:3306 rr persistent 20

  -> 192.168.56.101:3306          Local   3      0          0        

  -> 192.168.56.102:3306          Route   3      0          0       

Master B~]# ipvsadm -ln

IP Virtual Server version 1.2.1 (size=4096)

Prot LocalAddress:Port Scheduler Flags

  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn

TCP  192.168.56.111:3306 rr persistent 20

  -> 192.168.56.101:3306          Route   3      3          0        

TCP  192.168.56.112:3306 rr persistent 20

  -> 192.168.56.101:3306          Route   3      0          0        

  -> 192.168.56.102:3306          Local   3      0          0  

并将 keepalived 和 realserver 的启动脚本加入到 rc.local 自启动中：
echo “/etc/rc.d/init.d/realserver start” >> /etc/rc.local

echo “/etc/rc.d/init.d/keepalived start” >> /etc/rc.local

4)    读LoadBlance测试

~]# cat test.sh

#! /bin/sh 

> /tmp/q

for((i=1;i<=100;i++));do  

        mysql -utrekiz -h 192.168.56.112 -ptrekiz -e "show variables like 'server_id'" >> /tmp/q; 

        sleep 3

done 

~]# ./test.sh


由上可知实现了负载均衡，读可以通过VIP 192.168.56.112来实现LB

5)    Failover测试

a)         停止Master(A)上的MySQL,写IP切换到sorry_server(B)上，并且读IP只剩B

Master A~]# /etc/init.d/mysql stop

Shutting down MySQL....                                    [  OK  ]

~]# ipvsadm -Ln

IP Virtual Server version 1.2.1 (size=4096)

Prot LocalAddress:Port Scheduler Flags

  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn

TCP  192.168.56.111:3306 rr persistent 20

  -> 192.168.56.102:3306          Route   1      0          0        

TCP  192.168.56.112:3306 rr persistent 20

  -> 192.168.56.102:3306          Route   3      0          0       

b)         停止Master（A）上的keepalived，VIP切换到soory_server（B）主机上

Master B~]# ip a|grep eth1

3: eth1: mtu 1500 qdisc pfifo_fast state UP qlen 1000

    inet 192.168.56.102/24 brd 192.168.56.255 scope global eth1

    inet 192.168.56.111/32 scope global eth1

inet 192.168.56.112/32 scope global eth1

c)         启动Master上的mysql，看写IP又切回real_server(101)上

Master B ~]# ipvsadm -Ln

IP Virtual Server version 1.2.1 (size=4096)

Prot LocalAddress:Port Scheduler Flags

  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn

TCP  192.168.56.111:3306 rr persistent 20

  -> 192.168.56.101:3306          Route   3      0          0        

TCP  192.168.56.112:3306 rr persistent 20

  -> 192.168.56.101:3306          Route   3      0          0        

  -> 192.168.56.102:3306          Local   3      0          0  

d)         启动Master上的keepalived,读写VIP切回Master A

Master A~]# ipvsadm -Ln

IP Virtual Server version 1.2.1 (size=4096)

Prot LocalAddress:Port Scheduler Flags

  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn

TCP  192.168.56.111:3306 rr persistent 20

  -> 192.168.56.101:3306          Local   3      0          0        

TCP  192.168.56.112:3306 rr persistent 20

  -> 192.168.56.101:3306          Local   3      0          0        

  -> 192.168.56.102:3306          Route   3      0          0        

Master A~]# ip a|grep eth1

3: eth1: mtu 1500 qdisc pfifo_fast state UP qlen 1000

    inet 192.168.56.101/24 brd 192.168.56.255 scope global eth1

    inet 192.168.56.111/32 scope global eth1

inet 192.168.56.112/32 scope global eth1

e)         重启Master的系统，切换过程如下：

Dec 10 16:50:34 MySQL2 Keepalived_healthcheckers[2530]: TCP connection to [192.168.56.101]:3306 failed !!!

Dec 10 16:50:34 MySQL2 Keepalived_healthcheckers[2530]: Removing service [192.168.56.101]:3306 from VS [192.168.56.112]:3306

Dec 10 16:50:34 MySQL2 Keepalived_healthcheckers[2530]: Remote SMTP server [127.0.0.1]:25 connected.

Dec 10 16:50:34 MySQL2 Keepalived_healthcheckers[2530]: SMTP alert successfully sent.

Dec 10 16:50:35 MySQL2 Keepalived_healthcheckers[2530]: TCP connection to [192.168.56.101]:3306 failed !!!

Dec 10 16:50:35 MySQL2 Keepalived_healthcheckers[2530]: Removing service [192.168.56.101]:3306 from VS [192.168.56.111]:3306

Dec 10 16:50:35 MySQL2 Keepalived_healthcheckers[2530]: Lost quorum 1-0=1 > 0 for VS [192.168.56.111]:3306

Dec 10 16:50:35 MySQL2 Keepalived_healthcheckers[2530]: Adding sorry server [192.168.56.102]:3306 to VS [192.168.56.111]:3306

Dec 10 16:50:35 MySQL2 Keepalived_healthcheckers[2530]: Removing alive servers from the pool for VS [192.168.56.111]:3306

Dec 10 16:50:35 MySQL2 Keepalived_healthcheckers[2530]: Remote SMTP server [127.0.0.1]:25 connected.

Dec 10 16:50:35 MySQL2 Keepalived_healthcheckers[2530]: SMTP alert successfully sent.

Dec 10 16:50:41 MySQL2 Keepalived_vrrp[2531]: VRRP_Instance(MySQL-HA{) Transition to MASTER STATE

Dec 10 16:50:42 MySQL2 Keepalived_vrrp[2531]: VRRP_Instance(MySQL-HA{) Entering MASTER STATE

Dec 10 16:50:42 MySQL2 Keepalived_vrrp[2531]: VRRP_Instance(MySQL-HA{) setting protocol VIPs.

Dec 10 16:50:42 MySQL2 Keepalived_vrrp[2531]: VRRP_Instance(MySQL-HA{) Sending gratuitous ARPs on eth1 for 192.168.56.111

Dec 10 16:50:42 MySQL2 Keepalived_vrrp[2531]: VRRP_Instance(MySQL-HA{) Sending gratuitous ARPs on eth1 for 192.168.56.112

Dec 10 16:50:42 MySQL2 avahi-daemon[1509]: Registering new address record for 192.168.56.111 on eth1.IPv4.

Dec 10 16:50:42 MySQL2 avahi-daemon[1509]: Registering new address record for 192.168.56.112 on eth1.IPv4.

Dec 10 16:50:42 MySQL2 Keepalived_healthcheckers[2530]: Netlink reflector reports IP 192.168.56.111 added

Dec 10 16:50:42 MySQL2 Keepalived_healthcheckers[2530]: Netlink reflector reports IP 192.168.56.112 added

Dec 10 16:50:47 MySQL2 Keepalived_vrrp[2531]: VRRP_Instance(MySQL-HA{) Sending gratuitous ARPs on eth1 for 192.168.56.111

Dec 10 16:50:47 MySQL2 Keepalived_vrrp[2531]: VRRP_Instance(MySQL-HA{) Sending gratuitous ARPs on eth1 for 192.168.56.112

Dec 10 16:51:17 MySQL2 Keepalived_healthcheckers[2530]: TCP connection to [192.168.56.101]:3306 success.

Dec 10 16:51:17 MySQL2 Keepalived_healthcheckers[2530]: Adding service [192.168.56.101]:3306 to VS [192.168.56.111]:3306

Dec 10 16:51:17 MySQL2 Keepalived_healthcheckers[2530]: Gained quorum 1+0=1 <= 3 for VS [192.168.56.111]:3306

Dec 10 16:51:17 MySQL2 Keepalived_healthcheckers[2530]: Removing sorry server [192.168.56.102]:3306 from VS [192.168.56.111]:3306

Dec 10 16:51:17 MySQL2 Keepalived_healthcheckers[2530]: Adding alive servers to the pool for VS [192.168.56.111]:3306

Dec 10 16:51:17 MySQL2 Keepalived_healthcheckers[2530]: Remote SMTP server [127.0.0.1]:25 connected.

Dec 10 16:51:17 MySQL2 Keepalived_healthcheckers[2530]: SMTP alert successfully sent.

Dec 10 16:51:18 MySQL2 Keepalived_healthcheckers[2530]: TCP connection to [192.168.56.101]:3306 success.

Dec 10 16:51:18 MySQL2 Keepalived_healthcheckers[2530]: Adding service [192.168.56.101]:3306 to VS [192.168.56.112]:3306

Dec 10 16:51:18 MySQL2 Keepalived_healthcheckers[2530]: Remote SMTP server [127.0.0.1]:25 connected.

Dec 10 16:51:18 MySQL2 Keepalived_healthcheckers[2530]: SMTP alert successfully sent.

Dec 10 16:53:35 MySQL2 Keepalived_vrrp[2531]: VRRP_Instance(MySQL-HA{) Received higher prio advert

Dec 10 16:53:35 MySQL2 Keepalived_vrrp[2531]: VRRP_Instance(MySQL-HA{) Entering BACKUP STATE

Dec 10 16:53:35 MySQL2 Keepalived_vrrp[2531]: VRRP_Instance(MySQL-HA{) removing protocol VIPs.

Dec 10 16:53:35 MySQL2 avahi-daemon[1509]: Withdrawing address record for 192.168.56.111 on eth1.

Dec 10 16:53:35 MySQL2 avahi-daemon[1509]: Withdrawing address record for 192.168.56.112 on eth1.

Dec 10 16:53:35 MySQL2 Keepalived_healthcheckers[2530]: Netlink reflector reports IP 192.168.56.111 removed

Dec 10 16:53:35 MySQL2 Keepalived_healthcheckers[2530]: Netlink reflector reports IP 192.168.56.112 removed


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27000195/viewspace-1364792/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： MySQL主主+Keepalived+LVS高可用（二）：单点写入
下一篇： Oracle之备份和清理监听日志、告警日志脚本
请登录后发表评论 登录
全部评论
jx_yu

注册时间：2012-05-23

  • 博文量
    80
  • 访问量
    876947

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593