the+mysql+server+instance+is+stoped_MySQL5.7+keepalived+LVS从库负载均衡搭建

数据库架构：一主三从

master：192.168.8.57

slave1：192.168.8.58

slave2：192.168.8.59

slave3：192.168.8.61

manager：192.168.8.60

工具包：

mha4mysql-manager-0.58.tar.gz

mha4mysql-node-0.58.tar.gz

keepalived-1.4.5.tar.gz

此项目数据库架构中，master和slave1利用MHA和keepalived实现故障自动切换，slave2和slave3利用

keepalived和LVS实现负载均衡，本文着重讲解负载均衡的搭建过程。

一、安装ipvsadm

在slave2和slave3安装

mount /dev/cdrom /media

yum install -y ipvsadm

二、安装keepalived

tar zxvf keepalived-1.4.5.tar.gz

cd keepalived-1.4.5

./configure --prefix=/usr/local/keepalived/

make && make install

设置开机自动启动

systemctl enable keepalived

三、更改keepalived日志位置

keepalived日志默认位置为/var/log/messages

把日志单独存放

修改 vi /usr/local/keepalived/etc/sysconfig/keepalived

把 KEEPALIVED_OPTIONS="-D" 修改为：KEEPALIVED_OPTIONS="-D -d -S 0"

vi /usr/local/keepalived/etc/sysconfig/keepalived

# Options for keepalived. See `keepalived --help' output and keepalived(8) and

# keepalived.conf(5) man pages for a list of all options. Here are the most

# common ones :

#

# --vrrp               -P    Only run with VRRP subsystem.

# --check              -C    Only run with Health-checker subsystem.

# --dont-release-vrrp  -V    Dont remove VRRP VIPs & VROUTEs on daemon stop.

# --dont-release-ipvs  -I    Dont remove IPVS topology on daemon stop.

# --dump-conf          -d    Dump the configuration data.

# --log-detail         -D    Detailed log messages.

# --log-facility       -S    0-7 Set local syslog facility (default=LOG_DAEMON)

#

KEEPALIVED_OPTIONS="-D -d -S 0"

重启服务

systemctl restart rsyslog

四、修改keepalived配置文件

192.168.8.59

vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

router_id 859

notification_email {

zdd5503@163.com

}

notification_email_from zdd5503@163.com

smtp_server stmp.163.com

smtp_connect_timeout 30

}

vrrp_instance v_mysql_slave_wgpt1 {

state MASTER

interface enp0s3

virtual_router_id 98

priority 100

advert_int 1

nopreempt

authentication {

auth_type PASS

auth_pass steven

}

virtual_ipaddress {

192.168.8.98/24

}

}

virtual_server 192.168.8.98 3306 {

delay_loop 2

lb_algo rr

lb_kind DR

persistence_timeout 0

protocol TCP

real_server 192.168.8.59 3306 {

weight 1

TCP_CHECK {

connect_timeout 3

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

real_server 192.168.8.61 3306 {

weight 1

TCP_CHECK {

connect_timeout 3

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

}

192.168.8.61

vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

router_id 861

notification_email {

zdd5503@163.com

}

notification_email_from zdd5503@163.com

smtp_server stmp.163.com

smtp_connect_timeout 30

}

vrrp_instance v_mysql_slave_wgpt1 {

state BACKUP

interface enp0s3

virtual_router_id 98

priority 80

advert_int 1

nopreempt

authentication {

auth_type PASS

auth_pass steven

}

virtual_ipaddress {

192.168.8.98/24

}

}

virtual_server 192.168.8.98 3306 {

delay_loop 2

lb_algo rr

lb_kind DR

persistence_timeout 0

protocol TCP

real_server 192.168.8.59 3306 {

weight 1

TCP_CHECK {

connect_timeout 3

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

real_server 192.168.8.61 3306 {

weight 1

TCP_CHECK {

connect_timeout 3

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

}

systemctl daemon-reload

systemctl start keepalived

systemctl enable keepalived

ps -ef |grep keepalived

ip a

五、lo:0绑定VIP地址、抑制ARP广播

192.168.8.59

vi /etc/rc.d/init.d/lvsmysql.sh

#!/bin/bash

#

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

VIP1=$1

case "$2" in

start)

echo " Start LVS of MySQL Slave REALServer"

/sbin/ifconfig lo:0 $VIP1 broadcast $VIP1 netmask 255.255.255.255 up

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

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

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

;;

stop)

/sbin/ifconfig lo:0 down

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

echo "Stoped LVS of MySQL Slave Directorserver"

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

;;

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 run yet."

else

echo "LVS-DR real server is running."

fi

exit 3

;;

*)

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

exit 1

esac

exit 0

192.168.8.61

vi /etc/rc.d/init.d/lvsmysql.sh

#!/bin/bash

#

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

VIP1=$1

case "$2" in

start)

echo " Start LVS of MySQL Slave REALServer"

/sbin/ifconfig lo:0 $VIP1 broadcast $VIP1 netmask 255.255.255.255 up

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

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

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

;;

stop)

/sbin/ifconfig lo:0 down

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

echo "Stoped LVS of MySQL Slave Directorserver"

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

;;

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 run yet."

else

echo "LVS-DR real server is running."

fi

exit 3

;;

*)

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

exit 1

esac

exit 0

六、测试负载均衡

/etc/rc.d/init.d/lvsmysql.sh 192.168.8.59 start

/etc/rc.d/init.d/lvsmysql.sh 192.168.8.59 stop

/etc/rc.d/init.d/lvsmysql.sh 192.168.8.61 start

/etc/rc.d/init.d/lvsmysql.sh 192.168.8.61 stop

echo "/etc/rc.d/init.d/lvsmysql.sh 192.168.1.65 start" >>

/etc/rc.d/rc.local

echo "ipvsadm --set 15 5 15" >> /etc/rc.d/rc.local

ifconfig

ipvsadm -L

mysql -uroot -pmysql -h192.168.8.59 -e "select @@hostname;"

mysql -uroot -pmysql -h192.168.8.61 -e "select @@hostname;