mysql集群搭建keepalived、haproxy、mycat、mysql

一、服务器配置说明

主机名	ip地址	端口
haproxy_1	192.168.236.131	48066、49066
haproxy_2	192.168.236.132	48066、49066
keepalived_1(主)	192.168.236.131
keepalived_2（备）	192.168.236.132
mycat_1	192.168.236.131	8066、9066
mycat_2	192.168.236.132	8066、9066
mysql_1	192.168.236.131	3306
mysql_2	192.168.236.132	3306
mysql_2	192.168.236.133	3306

软件版本：
haproxy-1.8.25.tar.gz
keepalived-2.0.19.tar.gz
Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz
mysql57-community-release-el7-10.noarch.rpm
keepalived下载地址： https://www.keepalived.org/download.html
haproxy下载地址：https://src.fedoraproject.org/repo/pkgs/haproxy/
mycat下载地址：http://dl.mycat.org.cn/1.6.7.1

mysql安装参考 https://blog.csdn.net/zybiao2/article/details/106379691
haproxy安装参考 https://blog.csdn.net/zybiao2/article/details/106471387

二、haproxy集成mycat文件配置

     配置文件如下：
     global
        log 127.0.0.1   local0
        #log 127.0.0.1   local1 notice
        #log loghost    local0 info
        maxconn 4096
        chroot /usr/local/haproxy
        pidfile /usr/data/haproxy/haproxy.pid
        uid 99
        gid 99
        daemon
        #debug
        #quiet

	defaults
	        log     global
	        mode    tcp
	        option  abortonclose
	        option redispatch
	        retries 3
	        maxconn 2000
	        timeout connect 5000
	        timeout client  50000
	        timeout server  50000
	
	listen proxy_status 
			#绑定访问mycat端口
	        bind :48066
	        mode tcp
	        balance roundrobin
	        #mycat服务地址配置
	        server mycat_1 192.168.236.131:8066 check inter 10s 
	        server mycat_2 192.168.236.132:8066 check inter 10s
	
	frontend admin_stats 
			#可视化页面管理端口
	        bind :49066
	        mode http
	        stats enable
	        option httplog
	        maxconn 10
	        stats refresh 30s
	       #配置可视化访问路径
	        stats uri /   
	       #配置可视化访问用户名和密码
	        stats auth haproxy:123456
	        stats hide-version
	        stats admin if TRUE

配置完成后启动haproxy服务：http://192.168.236.131:49066，
输入配置配置文件中设置的用户名和密码： haproxy/123456
进入可视化页面如下图：

测试：[root@slave2 ~]# mysql -h 192.168.236.131 -P 48066 -urepl -p123456
-h 192.168.236.131 haproxy服务器地址
-P 48066 配置文件中访问mycat的端口号
-urepl -p123456 访问数据库的用户名和密码

三、keepalived安装

1、安装依赖

yum install -y openssl-devel openssl libnl libnl-devel libnfnetlink-devel

2、keepalived安装

wget http://www.keepalived.org/software/keepalived-2.0.19.tar.gz
tar -zxvf keepalived-2.0.19.tar.gz
cd keepalived-2.0.19
./configure --prefix=/usr/local/keepalived
make
make install

cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp ./keepalived/etc/init.d/keepalived /etc/init.d/

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

mkdir -p  /usr/local/keepalived/var/log

3、设置开机启动

# ln -s /usr/local/keepalived/sbin/keepalived /usr/sbin/
# ln -s /usr/local/keepalived/sbin/keepalived /sbin/
开机启动：chkconfig keepalived on

4、修改配置文件

(1) MASTER 节点配置文件（192.168.236.131）

! Configuration Fileforkeepalived
#VRRP脚本
vrrp_script chk_http_port {
#执行的脚本
script"/etc/keepalived/scripts/check_haproxy.sh"
 #脚本执行间隔
interval 2
#脚本结果导致的优先级变更：2表示优先级+10；-2则表示优先级-2
weight 2
}
#vrrp实例
vrrp_instance VI_1 {
state MASTER
#本机网卡
interface enp0s3
##VRRP组名，两个节点的设置必须一样，以指明各个节点属于同一VRRP组
virtual_router_id 51
#优先级，数值愈大，优先级越高
priority 120
#检查间隔，默认1秒
advert_int 1
#设置认证
authentication {
#认证方式
auth_type PASS
#认证密码
auth_pass 123456
}
track_script {
#调用脚本check_haproxy.sh检查haproxy是否存活
chk_http_port
}
#虚拟IP地址池，可以有多个IP，每个IP占一行
virtual_ipaddress {
192.168.236.100 dev enp0s3 scope global
}
#以下可选择使用
#指定当切换到master时，执行的脚本
#notify_master /etc/keepalived/scripts/haproxy_master.sh
#指定当切换到backup时，执行的脚本
#notify_backup /etc/keepalived/scripts/haproxy_backup.sh
#故障时执行的脚本
#notify_fault /etc/keepalived/scripts/haproxy_fault.sh
#notify_stop /etc/keepalived/scripts/haproxy_stop.sh

(2) MASTER 节点配置文件（192.168.236.132）

! Configuration Fileforkeepalived
#VRRP脚本
vrrp_script chk_http_port {
#执行的脚本
script"/etc/keepalived/scripts/check_haproxy.sh"
 #脚本执行间隔
interval 2
#脚本结果导致的优先级变更：2表示优先级+10；-2则表示优先级-2
weight 2
}
#vrrp实例
vrrp_instance VI_1 {
state MASTER
#本机网卡
interface enp0s3
##VRRP组名，两个节点的设置必须一样，以指明各个节点属于同一VRRP组
virtual_router_id 100
#优先级，数值愈大，优先级越高
priority 110
#检查间隔，默认1秒
advert_int 1
#设置认证
authentication {
#认证方式
auth_type PASS
#认证密码
auth_pass 123456
}
track_script {
#调用脚本check_haproxy.sh检查haproxy是否存活
chk_http_port
}
#虚拟IP地址池，可以有多个IP，每个IP占一行
virtual_ipaddress {
192.168.236.100 dev enp0s3 scope global
}
#以下可选择使用
#指定当切换到master时，执行的脚本
#notify_master /etc/keepalived/scripts/haproxy_master.sh
#指定当切换到backup时，执行的脚本
#notify_backup /etc/keepalived/scripts/haproxy_backup.sh
#故障时执行的脚本
#notify_fault /etc/keepalived/scripts/haproxy_fault.sh
#notify_stop /etc/keepalived/scripts/haproxy_stop.sh

创建检查haproxy是否存活的脚本

vim /etc/keepalived/scripts/check_haproxy.sh

#!/bin/bash
STARTHAPROXY="/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/conf/haproxy.conf"
STOPKEEPALIVED="/etc/init.d/keepalived stop"
LOGFILE="/usr/local/keepalived/var/log/keepalived-haproxy-state.log"
echo "[check_haproxy status]" >> $LOGFILE
A=`ps -C haproxy --no-header |wc -l`
echo "[check_haproxy status]" >> $LOGFILE
date >> $LOGFILE
#如果没有haproxy进程存在，就启动haproxy，停止keepalived，并写入日志
if [ $A -eq 0 ];then
	echo $STARTHAPROXY >> $LOGFILE
	$STARTHAPROXY >> $LOGFILE 2>&1
	sleep 5
fi
if [ `ps -C haproxy --no-header |wc -l` -eq 0 ];then
	exit 0
else
	exit 1
fi

创建切换到MASTER与BACKUP脚本
vim /etc/keepalived/scripts/haproxy_master.sh
vim /etc/keepalived/scripts/haproxy_backup.sh
在两个文件中写入以下内容

#!/bin/bash
STARTHAPROXY=`/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/conf/haproxy.conf`
STOPHAPROXY=`ps -ef | grep sbin/haproxy | grep -v grep | awk '{print $2}'| xargs kill -s 9`
LOGFILE="/usr/local/keepalived/var/log/keepalived-haproxy-state.log"
echo "[master]" >> $LOGFILE
date >> $LOGFILE
echo "Being master...." >> $LOGFILE 2>&1
echo "stop haproxy...." >> $LOGFILE 2>&1
$STOPHAPROXY >> $LOGFILE 2>&1
echo "start haproxy...." >> $LOGFILE 2>&1
$STARTHAPROXY >> $LOGFILE 2>&1
echo "haproxy stared ..." >> $LOGFILE

创建故障时执行的脚本
vim /etc/keepalived/scripts/haproxy_fault.sh

#!/bin/bash
LOGFILE=/usr/local/keepalived/var/log/keepalived-haproxy-state.log
echo "[fault]" >> $LOGFILE
date >> $LOGFILE

创建停止vrrp脚本
vim /etc/keepalived/scripts/haproxy_stop.sh

#!/bin/bash
LOGFILE=/usr/local/keepalived/var/log/keepalived-haproxy-state.log
echo "[stop]" >> $LOGFILE
date >> $LOGFILE

赋予脚本可执行权限
chmod 777 /etc/keepalived/scripts/*

启动keepalived
systemctl start keepalived.service

加入开机启动
systemctl enable keepalived.service

测试
在浏览器输入http://192.168.236.100:49066
显示如下界面

keepalived常用命令
启动：service keepalived start
停止：service keepalived stop

完成