4台mysql如何主备_LVS+Keepalived+Mysql+主备数据库架构[4台]

这是一个坑...磨了不少时间。见证自己功力有待提升。。。

架构图

数据库

1.安装数据库

这块不难， 直接引用：mysql安装

2.数据库主备

这块不难， 直接引用： mysql主备

虚拟VIP

重点来了！！

上脚本， 把这个脚本命名后仍进/etc/init.d， 然后授予权限755或者+x

SNS_VIP=192.168.158.110

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

case "$1" in

start)

ifconfig eth0:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP

/sbin/route add -host $SNS_VIP dev eth0: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

echo "RealServer Start OK"

;;

stop)

ifconfig eth0:0 down

route del $SNS_VIP >/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 "RealServer Stoped"

;;

*)

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

exit 1

esac

exit 0

启动这个脚本后会出现虚拟ip， 启动方法service realserver start

查看有好几种方式

第一种: ifconfig

第二种: ip addr

第三种: ifconfig eth0:0

好了, 虚拟ip也有了,记得两台mysql是一样的操作。

LVS

重点！

1.依赖：

yum install -y libnl* popt*

rpm -ivh popt-static-1.13-7.el6.x86_64.rpm

第二个是后面发现缺少, 恰恰yum中也没有的安装而独自下载rpm

包

2.查看是否已加载lvs模块

modprobe -l |grep ipvs

3.正常的情况下是这样的:

[root@localhost init.d]# modprobe -l |grep ipvs

kernel/net/netfilter/ipvs/ip_vs.ko

kernel/net/netfilter/ipvs/ip_vs_rr.ko

kernel/net/netfilter/ipvs/ip_vs_wrr.ko

kernel/net/netfilter/ipvs/ip_vs_lc.ko

kernel/net/netfilter/ipvs/ip_vs_wlc.ko

kernel/net/netfilter/ipvs/ip_vs_lblc.ko

kernel/net/netfilter/ipvs/ip_vs_lblcr.ko

kernel/net/netfilter/ipvs/ip_vs_dh.ko

kernel/net/netfilter/ipvs/ip_vs_sh.ko

kernel/net/netfilter/ipvs/ip_vs_sed.ko

kernel/net/netfilter/ipvs/ip_vs_nq.ko

kernel/net/netfilter/ipvs/ip_vs_ftp.ko

[root@localhost init.d]#

4.安装正包

tar -zxvf ipvsadm-1.26.tar.gz

make && make install

5.编译安装完成后测试:

ipvsadm -L -n

[root@localhost init.d]# ipvsadm -ln

IP Virtual Server version 1.2.1 (size=4096)

Prot LocalAddress:Port Scheduler Flags

-> RemoteAddress:Port Forward Weight ActiveConn InActConn

lvs大功告成!

Keepalived

重点!!

1.安装依赖

yum install -y kernel-devel openssl openssl-devel

2.编译安装

新建安装目录

mkdir -p /usr/local/keepalived

编译

./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.32-358.el6.x86_64/

Keepalived configuration

------------------------

Keepalived version : 1.2.13

Compiler : gcc

Compiler flags : -g -O2

Extra Lib : -lssl -lcrypto -lcrypt

Use IPVS Framework : Yes

IPVS sync daemon support : Yes

IPVS use libnl : No

fwmark socket support : Yes

Use VRRP Framework : Yes

Use VRRP VMAC : Yes

SNMP support : No

SHA1 support : No

Use Debug flags : No

注意: 这里的2.6.32-358.el6.x86_64是您的内核, 不要照抄哦, 可能咋们内核不一致

安装

make && make install

3.其他操作

cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/

cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

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

chkconfig keepalived on

LVS+Keepalived配置

~~负载均衡主机~~

1.新建keepalived配置文件:

vim /etc/keepalived/keepalived.conf

2.keepalived.conf详细:

! Configuration File for keepalived

global_defs { #全局标识模块

notification_email { #定义邮件通知

test@163.com #目标邮箱

}

notification_email_from test@163.com #发送邮箱

smtp_server 127.0.0.1 #发送邮箱的smtp服务器

smtp_connect_timeout 30 #smtp服务器连接超时时间

router_id LVS_DEVEL

}

vrrp_instance VI_1 {

state MASTER #本实例启动状态：MASTER/BACKUP

interface eth0 #监控的网络接口

virtual_router_id 51 #vrrp实例(同一个组主备服务器设置一样)

priority 100 #优先级高的为master，不能超过255。(BACKUP可设置为50)

advert_int 1 #均衡器检测间隔1秒(服务器设置都一样)

authentication { #验证类型及密码(服务器设置都一样)

auth_type PASS #认证方式，PASS或AH

auth_pass 123456 #认证密码

}

virtual_ipaddress { #虚拟ip地址virtual_ipaddress,可以定义多个

192.168.158.110

}

}

virtual_server 192.168.158.110 3306 { #定义虚拟服务器，与上面的virtual_server一样

delay_loop 3 #健康检查时间间隔，6秒

lb_algo wrr #负载均衡调度算法：rr|wrr|lc|wlc|sh|dh|lblc

lb_kind DR #负载均衡转发规则：NAT|DR|TUN

nat_mask 255.255.255.0

#persistence_timeout 50 #回话保持时间50秒，动态服务建议开启

protocol TCP #转发协议protocol，一般有tcp和udp两种

#后端真实服务器，有几台就设置几个

real_server 192.168.158.134 3306 {

weight 1 #权重越大负载分越大，0表示失效

SSL_GET { #健康检查方式：HTTP_GET|SSL_GET|TCP_CHECK|SMTP_CHECK|MISC_CHECK

connect_timeout 3

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

real_server 192.168.158.139 3306 {

weight 1

SSL_GET {

connect_timeout 3

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

}

3.附上启动脚本

#!/bin/sh

#

# Startup script for the Keepalived daemon

#

# processname: keepalived

# pidfile: /var/run/keepalived.pid

# config: /etc/keepalived/keepalived.conf

# chkconfig: - 21 79

# description: Start and stop Keepalived

# Source function library

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

# Source configuration file (we set KEEPALIVED_OPTIONS there)

. /etc/sysconfig/keepalived

RETVAL=0

prog="keepalived"

start() {

echo -n $"Starting $prog: "

daemon keepalived ${KEEPALIVED_OPTIONS}

RETVAL=$?

echo

[ $RETVAL -eq 0 ] && touch /var/lock/subsys/$prog

}

stop() {

echo -n $"Stopping $prog: "

killproc keepalived

RETVAL=$?

echo

[ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/$prog

}

reload() {

echo -n $"Reloading $prog: "

killproc keepalived -1

RETVAL=$?

echo

}

# See how we were called.

case "$1" in

start)

start

;;

stop)

stop

;;

reload)

reload

;;

restart)

stop

start

;;

condrestart)

if [ -f /var/lock/subsys/$prog ]; then

stop

start

fi

;;

status)

status keepalived

RETVAL=$?

;;

*)

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

RETVAL=1

esac

exit $RETVAL

4.检测keepalived

service keepalived start

service keepalived stop

service keepalived status

5.检测lvs

ipvsadm

lvs数据参考:

[root@localhost init.d]# ipvsadm

IP Virtual Server version 1.2.1 (size=4096)

Prot LocalAddress:Port Scheduler Flags

-> RemoteAddress:Port Forward Weight ActiveConn InActConn

TCP 192.168.158.110:mysql wrr

-> 192.168.158.134:mysql Route 1 0 0

-> 192.168.158.139:mysql Route 1 0 0

~~负载均衡备机~~

备机负载服务器与主负载服务器大致相同，只是在keepalived的配置文件中需要改以下两处：

(1)将state由MASTER改为BACKUP

(2)将priority由100改为99

lvs+keepalived大功告成!!

验证性测试

1.启动mysql(两台)

service mysqld start

2.启动vip

service realserver start

3.启动keepalived(两台)

service keepalived start

4.使用本地ping vip

5.使用本地连接mysql(连接的是vip)

mysql.exe -u root -p"lizhenghua" -P 3306 -h 192.168.158.110

6.在从库139上建立数据库cheng(先断开主从)

192.168.158.139

192.168.158.134

笔记

! Configuration File for keepalived

global_defs {

notification_email {

test@163.com

}

notification_email_from test@163.com

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id LVS_1

}

vrrp_instance VI_1 {

state MASTER

interface eth0

virtual_router_id 51

priority 100

advert_int 1

authentication {

auth_type PASS

auth_pass 123456

}

virtual_ipaddress {

192.168.158.110

}

}

virtual_server 192.168.158.110 3306 {

delay_loop 3

lb_algo wrr

lb_kind DR

nat_mask 255.255.255.0

#persistence_timeout 50

protocol TCP

real_server 192.168.158.134 3306 {

weight 1

SSL_GET {

connect_timeout 3

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

real_server 192.168.158.139 3306 {

weight 1

SSL_GET {

connect_timeout 3

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

}

监测脑裂

#!/bin/bash

# 检查脑裂的脚本，在备节点上进行部署

LB01_VIP=192.168.158.110

LB01_IP=192.168.158.140

LB02_IP=192.168.158.141

while true

do

ping -c 2 -W 3 $LB01_VIP &>/dev/null

if [ $? -eq 0 -a `ip add|grep "$LB01_VIP"|wc -l` -eq 1 ];then

echo "ha is brain."

else

echo "ha is ok"

fi

sleep 5

done

完成!!