MySQL主主+Keepalived+LVS高可用（三）：單點寫入、讀負載均衡

jx_yu發表於2014-12-11
MySql負載

方案簡介

本方案採用 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部落格 ” ，連結：http://blog.itpub.net/27000195/viewspace-1364792/，如需轉載，請註明出處，否則將追究法律責任。

相關文章