haproxy配置mysql_利用keepalived和haproxy配置mysql的高可用负载均衡

实验系统：CentOS 6.6_x86_64(2.6.32-504.30.3.el6.x86_64)

实验前提：防火墙和selinux都关闭

实验说明：本实验共有4台主机，IP分配如拓扑

实验软件：keepalived-1.2.19　　haproxy-1.5.14　　mariadb-10.0.20

下载地址：http://pan.baidu.com/s/1bnnYiMr

实验拓扑：

一、安装mariadb

1.在两台数据库服务器安装：

tar xf mariadb-10.0.20-linux-x86_64.tar.gz -C /usr/local/cd/usr/local/

ln -sv mariadb-10.0.20-linux-x86_64 mysql

useradd-r mysqlmkdir -pv /mydata/datachown -R mysql.mysql /mydata/data/cd mysql/

chown -R root.mysql .

scripts/mysql_install_db --user=mysql --datadir=/mydata/data/

cp support-files/my-large.cnf /etc/my.cnfcp support-files/mysql.server /etc/init.d/mysqld

chkconfig--add mysqld

chkconfig mysqld on

2.配置主主复制：

19.74：

vim /etc/my.cnf----------------------------------------------->[mysqld]

server-id= 1datadir= /mydata/data

log-bin = /mydata/data/mysql1-bin

binlog_format= ROW

relay_log= /mydata/data/relay-log

auto-increment-increment = 2auto-increment-offset = 1sync_binlog= 1sync_master_info= 1sync_relay_log= 1sync_relay_log_info= 1

19.76：

vim /etc/my.cnf----------------------------------------------->[mysqld]

server-id= 2datadir= /mydata/data

log-bin = /mydata/data/mysql2-bin

binlog_format= ROW

relay_log= /mydata/data/relay-log

auto-increment-increment = 2auto-increment-offset = 2sync_binlog= 1sync_master_info= 1sync_relay_log= 1sync_relay_log_info= 1

3.创建具有复制权限的用户：

19.74：

service mysqld start/usr/local/mysql/bin/mysql------------------------------------------>GRANT REPLICATION SLAVE,REPLICATION CLIENT ON*.* TO 'master'@'192.168.19.76' IDENTIFIED BY '123456';

FLUSH PRIVILEGES;

19.76：

service mysqld start/usr/local/mysql/bin/mysql------------------------------------------>GRANT REPLICATION SLAVE,REPLICATION CLIENT ON*.* TO 'master'@'192.168.19.74' IDENTIFIED BY '123456';

FLUSH PRIVILEGES;

4.查看二进制位置：

19.74：

SHOW MASTER LOGS;

19.76上使用相同命令：

5.配置双主：

19.74：

CHANGE MASTER TO MASTER_HOST='192.168.19.76',MASTER_USER='master',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql2-bin.000001',MASTER_LOG_POS=1112;

START SLAVE;

19.76：

CHANGE MASTER TO MASTER_HOST='192.168.19.74',MASTER_USER='master',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql1-bin.000001',MASTER_LOG_POS=1112;

START SLAVE;

二、编译安装haproxy

1.在19.66和19.79上编译安装haproxy：

tar xf haproxy-1.5.14.tar.gz

cd haproxy-1.5.14

make TARGET=linux2628 ARCH=x86_64 //根据自己主机设定

make install SBINDIR=/usr/sbin/ MANDIR=/usr/share/man/ DOCDIR=/usr/share/doc/

2.提供启动脚本：

vim /etc/init.d/haproxy--------------------------------------------------->#!/bin/sh#

# haproxy

#

# chkconfig:- 85 15# description: HAProxy is afree, very fast and reliable solution \

# offering high availability, load balancing, and \

# proxyingfor TCP and HTTP-based applications

# processname: haproxy

# config:/etc/haproxy/haproxy.cfg

# pidfile:/var/run/haproxy.pid

# Sourcefunctionlibrary.

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

# Source networking configuration.

./etc/sysconfig/network

# Check that networking is up.

["$NETWORKING" = "no" ] && exit 0exec="/usr/sbin/haproxy"prog=$(basename$exec)

[-e /etc/sysconfig/$prog ] && . /etc/sysconfig/$prog

cfgfile=/etc/haproxy/haproxy.cfg

pidfile=/var/run/haproxy.pidlockfile=/var/lock/subsys/haproxy

check() {

$exec-c -V -f $cfgfile $OPTIONS

}

start() {

$exec-c -q -f $cfgfile $OPTIONSif [ $? -ne 0 ]; then

echo "Errors in configuration file, check with $prog check."return1

fi

echo -n $"Starting $prog:"# start it up here, usually something like"daemon $exec"daemon $exec-D -f $cfgfile -p $pidfile $OPTIONS

retval=$?

echo[ $retval-eq 0 ] && touch $lockfilereturn $retval

}

stop() {echo -n $"Stopping $prog:"# stop it here, often"killproc $prog"killproc $prog

retval=$?

echo[ $retval-eq 0 ] && rm -f $lockfilereturn $retval

}

restart() {

$exec-c -q -f $cfgfile $OPTIONSif [ $? -ne 0 ]; then

echo "Errors in configuration file, check with $prog check."return1

fistop

start

}

reload() {

$exec-c -q -f $cfgfile $OPTIONSif [ $? -ne 0 ]; then

echo "Errors in configuration file, check with $prog check."return1

fi

echo -n $"Reloading $prog:"$exec-D -f $cfgfile -p $pidfile $OPTIONS -sf $(cat$pidfile)

retval=$?

echoreturn $retval

}

force_reload() {

restart

}

fdr_status() {

status $prog

}case "$1" instart|stop|restart|reload)

$1;;

force-reload)

force_reload

;;

check)

check

;;

status)

fdr_status

;;

condrestart|try-restart)

[! -f $lockfile ] ||restart

;;*)echo $"Usage: $0 {start|stop|status|restart|try-restart|reload|force-reload}"exit2

esac

chkconfig --add haproxy

chkconfig haproxy on

chmod +x /etc/init.d/haproxy

3.提供配置文件：

mkdir /etc/haproxy

mkdir /var/lib/haproxy

useradd -r haproxy

vim/etc/haproxy/haproxy.cfg----------------------------------------------------------------------->

globallog127.0.0.1local2chroot /var/lib/haproxy

pidfile/var/run/haproxy.pid

maxconn4000user haproxy

group haproxy

daemon

stats socket/var/lib/haproxy/statsdefaults

mode tcp //haproxy运行模式

log global

option dontlognulloption redispatch

retries3timeout http-request 10s

timeout queue 1m

timeout connect 10s

timeout client 1m

timeout server 1m

timeout http-keep-alive 10s

timeout check 10s

maxconn600 //最大连接数

listen stats //配置haproxy状态页

mode http

bind :6677 //找一个比较特殊的端口

stats enable

stats hide-version //隐藏haproxy版本号

stats uri     /haproxyadmin?stats //一会用于打开状态页的uri

stats realm   Haproxy\ Statistics //输入账户密码时的提示文字

stats auth    admin:admin //用户名:密码

stats admin if TRUE //开启状态页的管理功能frontend main*:3306 //这里为了实验方便，使用3306端口default_backend mysql //后端服务器组名backend mysqlbalance     leastconn //使用最少连接方式调度

server m1 192.168.19.74:3306 check port 3306 maxconn 300

server m2 192.168.19.76:3306 check port 3306 maxconn 300

4.启动日志：

vim /etc/rsyslog.conf-----------------------------------------------------># Provides UDP syslog reception//去掉下面两行注释，开启UDP监听

$ModLoad imudp

$UDPServerRun514local2.* /var/log/haproxy.log //添加此行

service rsyslog restart

5.启动测试haproxy：

service haproxy start

netstat -tnlp

6.在19.74上创建远程登录账号：

GRANT ALL ON *.* TO 'jason'@'192.168.19.%' IDENTIFIED BY '123456';

FLUSH PRIVILEGES;

7.分别在19.66和19.79上登录mysql，若都能连接成功则继续往下：

yum -y install mysql //如果没有mysql客户端则运行此命令mysql -ujason -p123456 -h192.168.19.66 //在19.66上登录

mysql -ujason -p123456 -h192.168.19.79 //在19.79上登录

三、安装keepalived

1.在19.66和19.79上编译安装keepalived：

tar xf keepalived-1.2.19.tar.gz

cd keepalived-1.2.19./configure --prefix=/usr/local/keepalived --sbindir=/usr/sbin/ --sysconfdir=/etc/ --mandir=/usr/local/share/man/ --with-kernel-dir=/usr/src/kernels/2.6.32-504.30.3.el6.x86_64/ //内核版本换成自己主机的

make && make installchkconfig--add keepalived

chkconfig keepalived on

2.在19.66上配置：

vim /etc/keepalived/keepalived.conf----------------------------------------------------->

! Configuration File for keepalived

global_defs { //此段暂时略过，下同

notification_email {

acassen@firewall.loc

failover@firewall.loc

sysadmin@firewall.loc

}

notification_email_from Alexandre.Cassen@firewall.loc

smtp_server 192.168.200.1

smtp_connect_timeout 30

router_id LVS_DEVEL

}

vrrp_script chk_haproxy {

script"/etc/keepalived/chk.sh" //检查haproxy的脚本interval2 //每两秒检查一次}

vrrp_instance VI_1 {

state BACKUP //定义为BACKUP节点

nopreempt //开启不抢占

interface eth0

virtual_router_id51priority100 //开启了不抢占，所以此处优先级必须高于另一台advert_int1authentication {

auth_type PASS

auth_pass abcd

}

virtual_ipaddress {192.168.19.150 //配置VIP}

track_script {

chk_haproxy //调用检查脚本

}

notify_backup"/etc/init.d/haproxy restart"notify_fault"/etc/init.d/haproxy stop"}

3.在19.79上配置：

vim /etc/keepalived/keepalived.conf----------------------------------------------------->

! Configuration File for keepalived

global_defs {notification_email {

acassen@firewall.loc

failover@firewall.loc

sysadmin@firewall.loc

}

notification_email_from Alexandre.Cassen@firewall.loc

smtp_server 192.168.200.1

smtp_connect_timeout 30

router_id LVS_DEVEL

}vrrp_script chk_haproxy {

script"/etc/keepalived/chk.sh"interval2}

vrrp_instance VI_1 {

state BACKUP

interface eth0

virtual_router_id51priority99advert_int1authentication {

auth_type PASS

auth_pass abcd

}

virtual_ipaddress {192.168.19.150}

track_script {

chk_haproxy

}

notify_backup"/etc/init.d/haproxy restart"notify_fault"/etc/init.d/haproxy stop"}

4.在两台机器上创建chk.sh文件：

vim /etc/keepalived/chk.sh

------------------------------------------------>

#!/bin/bash

#

if [ $(ps -C haproxy --no-header | wc -l) -eq 0 ]; then

/etc/init.d/keepalived stop

fi

chmod +x /etc/keepalived/chk.sh

5.在19.66和19.79上进行测试：

service keepalived start

此处两台主机均配置为BACKUP，因此哪台先运行keepalived，VIP就在哪台上。我这里刚开始VIP运行在19.66上，然后进行连接测试：

mysql -ujason -p123456 -h192.168.19.150

------------------------------------------->

CREATE DATABASE bokeyuan；

后端数据库服务器抓包：

停掉19.66的keepalived服务，让VIP转移到19.79上，再进行测试：

service keepalived stop //停掉19.66的keepalived服务

mysql -ujason -p123456 -h192.168.19.150

------------------------------------------->

SHOW DATABASES;

后端数据库服务器抓包：

6.在浏览器打开http://192.168.19.150:6677/haproxyadmin?stats，打开haproxy状态页：

在19.74上关闭mysql服务，可以看到haproxy对于后端服务器的检测是很迅速的：

service mysqld stop

7.额外说明：

继续之前的实验，将19.66上的keepalived服务再次启动，可以发现，VIP仍然在19.79上，这就是之前为什么要配置不抢占的原因。如果按照正常的配置，将19.66配置为MASTER，当它重启keepalived服务后，则一定会将VIP抢回。但实际上我们并不希望这样，因为19.79仍在正常工作，19.66没有理由去抢夺资源，造成没必要的资源切换。实验演示就到这里，谢谢大家！