mycat实现mysql读写分离

发布时间:2024年01月18日

一. mycat集群+Haproxy+Keepalived

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

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

名称ip端口
mysql-master192.168.1.2203306
mysql-slave1192.168.1.2213306
mysql-slave2192.168.1.2223306
mycat-1192.168.1.2218066
mycat-2192.168.1.2228066
haproxy-1192.168.1.22118066
haproxy-2192.168.1.22218066
keepalived-1192.168.1.221
keepalived-2192.168.1.222

image.png

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
image.png

文章来源:https://blog.csdn.net/qq_35583325/article/details/135677400
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。