利用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 mysql mkdir -pv /mydata/data chown -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.cnf cp support-files/mysql.server /etc/init.d/mysqld chkconfig --add mysqld chkconfig mysqld on

2.配置主主复制：

19.74：

vim /etc/my.cnf  -----------------------------------------------> [mysqld] server-id = 1 datadir = /mydata/data log-bin = /mydata/data/mysql1-bin binlog_format = ROW relay_log = /mydata/data/relay-log auto-increment-increment = 2 auto-increment-offset = 1 sync_binlog = 1 sync_master_info = 1 sync_relay_log = 1 sync_relay_log_info = 1

19.76：

vim /etc/my.cnf  -----------------------------------------------> [mysqld] server-id = 2 datadir = /mydata/data log-bin = /mydata/data/mysql2-bin binlog_format = ROW relay_log = /mydata/data/relay-log auto-increment-increment = 2 auto-increment-offset = 2 sync_binlog = 1 sync_master_info = 1 sync_relay_log = 1 sync_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 a free, very fast and reliable solution / #      offering high availability, load balancing, and / #      proxying for TCP and  HTTP-based applications # processname: haproxy # config:   /etc/haproxy/haproxy.cfg # pidfile:  /var/run/haproxy.pid # Source function library. . /etc/rc.d/init.d/functions # Source networking configuration. . /etc/sysconfig/network # Check that networking is up. [ "$NETWORKING" = "no" ] && exit 0 exec="/usr/sbin/haproxy" prog=$(basename $exec) [ -e /etc/sysconfig/$prog ] && . /etc/sysconfig/$prog cfgfile=/etc/haproxy/haproxy.cfg pidfile=/var/run/haproxy.pid lockfile=/var/lock/subsys/haproxy check() {  $exec -c -V -f $cfgfile $OPTIONS } start() {  $exec -c -q -f $cfgfile $OPTIONS  if [ $? -ne 0 ]; then   echo "Errors in configuration file, check with $prog check."   return 1  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 $lockfile  return $retval } stop() {  echo -n $"Stopping $prog: "  # stop it here, often "killproc $prog"  killproc $prog  retval=$?  echo  [ $retval -eq 0 ] && rm -f $lockfile  return $retval } restart() {  $exec -c -q -f $cfgfile $OPTIONS  if [ $? -ne 0 ]; then   echo "Errors in configuration file, check with $prog check."   return 1  fi  stop  start } reload() {  $exec -c -q -f $cfgfile $OPTIONS  if [ $? -ne 0 ]; then   echo "Errors in configuration file, check with $prog check."   return 1  fi  echo -n $"Reloading $prog: "  $exec -D -f $cfgfile -p $pidfile $OPTIONS -sf $(cat $pidfile)  retval=$?  echo  return $retval } force_reload() {  restart } fdr_status() {  status $prog } case "$1" in  start|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}"   exit 2 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 ----------------------------------------------------------------------->
global   log   127.0.0.1 local2  chroot   /var/lib/haproxy  pidfile  /var/run/haproxy.pid  maxconn  4000  user  haproxy  group    haproxy  daemon  stats socket /var/lib/haproxy/stats  defaults  mode     tcp   //haproxy运行模式  log      global  option      dontlognull  option      redispatch  retries     3  timeout http-request 10s  timeout queue     1m  timeout connect   10s  timeout client    1m  timeout server    1m  timeout http-keep-alive 10s  timeout check     10s  maxconn     600     //最大连接数

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 mysql  balance  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 $UDPServerRun 514  local2.*             /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 install chkconfig --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的脚本  interval 2             //每两秒检查一次 } vrrp_instance VI_1 {  state BACKUP          //定义为BACKUP节点  nopreempt             //开启不抢占  interface eth0  virtual_router_id 51  priority 100          //开启了不抢占，所以此处优先级必须高于另一台  advert_int 1  authentication {   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"  interval 2 } vrrp_instance VI_1 {  state BACKUP  interface eth0  virtual_router_id 51  priority 99  advert_int 1  authentication {   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没有理由去抢夺资源，造成没必要的资源切换。实验演示就到这里，谢谢大家！