mycat实现mysql读写分离

一. mycat集群+Haproxy+Keepalived

mycat集群+Haproxy+Keepalived+mysql1主2从

环境规划 centos7.9
1主2从，读写分离

名称	ip	端口
mysql-master	192.168.1.220	3306
mysql-slave1	192.168.1.221	3306
mysql-slave2	192.168.1.222	3306
mycat-1	192.168.1.221	8066
mycat-2	192.168.1.222	8066
haproxy-1	192.168.1.221	18066
haproxy-2	192.168.1.222	18066
keepalived-1	192.168.1.221
keepalived-2	192.168.1.222

1.1 在另一台机器上面部署mycat,

1.2 Keepalived安装

#查看yum可用的keepalived版本
yum list|grep keepalived

#在线安装使用yum安装keepalived
yum -y install  keepalived.x86_64


#在/etc/keepalived下新建check_haproxy.sh脚本，脚本内容如下
cat > /etc/keepalived/check_haproxy.sh <<-'EOF'
#!/bin/bash
A=`ps -C haproxy --no-header |wc -l`
if [ $A -eq 0 ];then
systemctl start haproxy.service
fi
EOF

cat /etc/keepalived/check_haproxy.sh

chmod + /etc/keepalived/check_haproxy.sh

1.3 修改Keepalived配置文件

cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf-bak



#192.168.1.221 节点1配置
vim /etc/keepalived/keepalived.conf
global_defs {
    router_id NodeA            					 #当前节点名称
}
vrrp_instance VI_1 {
    state MASTER                         #设置主服务器
    interface ens160                     #系统的网卡名字（centos6为eth0,centos7位eth33）
    virtual_router_id 10    haproxy.cfg  #多个保持心跳的keepalived节点要设置同一个router_id
    priority 100                         #权重，权重数字越大，权重越大：即表示VIP访问权重最大的
    advert_int 1

    authentication {
                auth_type PASS           #通过PASS认证（密码认证）
                auth_pass 123456         #多个保持心跳的keepalived节点要设置同一个auth_pass
        }
        virtual_ipaddress {     				 #虚拟IP地址，可设置多个
                  192.168.1.67/24       #对外暴露的IP地址，24是网络位数（可省略）
        }
        track_script{                    #自定义追踪脚本
                chk_haproxy              #追踪haproxy
        }
}
vrrp_script chk_haproxy {       				 #定义的方法名
        script "/etc/keepalived/check_haproxy.sh"
        interval 4              				#追踪频率
        weight 3                        #追踪权重
}




#192.168.1.222 节点2配置
vim /etc/keepalived/keepalived.conf
global_defs {
    router_id NodeB            					 #当前节点名称
}
vrrp_instance VI_1 {
    state MASTER                         #设置主服务器
    interface ens160                     #系统的网卡名字（centos6为eth0,centos7位eth33）
    virtual_router_id 10    haproxy.cfg  #多个保持心跳的keepalived节点要设置同一个router_id
    priority 90                         #权重，权重数字越大，权重越大：即表示VIP访问权重最大的
    advert_int 1

    authentication {
                auth_type PASS           #通过PASS认证（密码认证）
                auth_pass 123456         #多个保持心跳的keepalived节点要设置同一个auth_pass
        }
        virtual_ipaddress {     				 #虚拟IP地址，可设置多个
                  192.168.1.67/24       #对外暴露的IP地址，24是网络位数（可省略）
        }
        track_script{                    #自定义追踪脚本
                chk_haproxy              #追踪haproxy
        }
}
vrrp_script chk_haproxy {       				 #定义的方法名
        script "/etc/keepalived/check_haproxy.sh"
        interval 4              				#追踪频率
        weight 3                        #追踪权重
}



#重启
systemctl enable keepalived
systemctl restart keepalived
systemctl status keepalived
ip a |grep 192.168.1.67

1.4 Haproxy安装的安装与配置

#查看yum可用的haproxy版本
yum list|grep haproxy
#在线安装使用yum安装haproxy
yum -y install haproxy.x86_64

cp /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg-bak

cd /etc/haproxy/

节点1和节点2 配置修改

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

    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats

defaults
    mode        tcp
    log         global
    option      tcplog
    option      dontlognull
    option              http-server-close
    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                                     3000

#配置haproxy可连接的地址，与绑定固定的域名
frontend        mysql
        bind    0.0.0.0:18066
        mode    tcp
        log             global
        default_backend mysql_server

#负载均衡的真实数据库地址
backend         mysql_server
    balance roundrobin
    server  mycat1 192.168.1.221:8066 check inter 5s rise 2 fall 3
    server  mycat2 192.168.1.222:8066 check inter 5s rise 2 fall 3
    #rise 2是2次正确认为服务器可用，fall 3是3次失败认为服务器不可用


#客户端配置
listen  admin_status
        mode  http
        bind 0.0.0.0:18000
        option httplog
        log global
        stats enable
        stats refresh 10s
        stats hide-version
        stats realm Haproxy\ Statistics
        stats uri  /admin-status
        stats auth  admin:123456
        stats admin if TRUE

 


systemctl enable haproxy.service
systemctl restart haproxy.service
systemctl status haproxy.service

启动mycat

cd /mysql/app/mycat/bin

#测试启动控制台
./mycat console


#启动
./startup_nowrap.sh


#查看进程
jps

1.5 测试mysql

# VIP测试
mysql -uroot -p123456 -P18066 -h 192.168.1.67

http://192.168.1.67:18000/admin-status