你的位置:首页 > 操作系统

[操作系统]利用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 mysqluseradd -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/mysqldchkconfig --add mysqldchkconfig mysqld on

  2.配置主主复制:

    19.74:

vim /etc/my.cnf ----------------------------------------------->[mysqld]server-id = 1datadir = /mydata/datalog-bin = /mydata/data/mysql1-binbinlog_format = ROWrelay_log = /mydata/data/relay-logauto-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/datalog-bin = /mydata/data/mysql2-binbinlog_format = ROWrelay_log = /mydata/data/relay-logauto-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.14make 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 0exec="/usr/sbin/haproxy"prog=$(basename $exec)[ -e /etc/sysconfig/$prog ] && . /etc/sysconfig/$progcfgfile=/etc/haproxy/haproxy.cfgpidfile=/var/run/haproxy.pidlockfile=/var/lock/subsys/haproxycheck() {  $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 2esac
<---------------------------------------------------
chkconfig --add haproxy
chkconfig haproxy on
chmod +x /etc/init.d/haproxy

  3.提供配置文件:

mkdir /etc/haproxy
mkdir /var/lib/haproxy
useradd -r haproxyvim /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/statsdefaults 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 514local2.*       /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 keepalivedchkconfig 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没有理由去抢夺资源,造成没必要的资源切换。实验演示就到这里,谢谢大家!