ProxySQL 代理访问MGR，可以做到故障自动切换主节点，可以自定义访问规则实现读写分离


目录

  • 网络架构
  • 一、环境准备
  • 二、软件安装
  • 1. MGR集群配置
  • 2. 创建必要用户
  • 2. ProxySQL部署测试
  • 3. Keepalived部署高可用
  • 4. 测试高可用


网络架构

Docker方式部署ProxySQL和Keepalived组合实现MGR的高可用访问_docker

一、环境准备

三台物理主机： 22.04.3-Ubuntu Linux 16核 32G内存

Docker版本：24.0.9

MySQL版本：8.0.27

4个IP地址
192.168.30.191
192.168.30.192
192.168.30.193
192.168.30.190（作为虚拟IP）

二、软件安装

1. MGR集群配置

请参考博客：

假设 192.168.30.191 为主节点，其他为从节点 组成MGR集群

2. 创建必要用户

在MGR主节点上创建一个用于ProxySQL监控的用户，后面配置用到。

CREATE USER 'monitor'@'%' IDENTIFIED WITH mysql_native_password BY 'monitor';

GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
  
GRANT SELECT on sys.* to 'monitor'@'%';

GRANT SELECT on performance_schema.* to 'monitor'@'%';

FLUSH PRIVILEGES;

上面是示例用户密码(monitor/monitor)，生产环境记得修改密度。

2. ProxySQL部署测试

三台物理服务器都操作下面步骤

下载Docker镜像

docker pull proxysql/proxysql:2.6.5

创建目录

mkdir -p /apps/proxysql/conf

在此目录下，创建映射配置文件

touch proxysql.cnf

文件内容如下：（这里一口气配置好MGR监控和主机信息，暂时没有配置读写分离访问策略）

datadir="/var/lib/proxysql"  

admin_variables=  
{  
    admin_credentials="admin:admin;radmin:radmin"  
    mysql_ifaces="0.0.0.0:6032"  
}

mysql_variables=  
{
    threads=8                    # 增加线程数以处理更多请求  
    max_connections=2000         # 适配32GB内存，允许更多连接  
    default_query_delay=0  
    default_query_timeout=3000000 # 有效的查询超时设置（以毫秒为单位）  
    have_compress=true  
    poll_timeout=2000  
    interfaces="0.0.0.0:6033"  
    default_schema="information_schema"  
    stacksize=1048576  
    server_version="8.0.27"  
    connect_timeout_server=3000  
    monitor_username="monitor"  
    monitor_password="monitor"  
    monitor_history=600000  
    monitor_connect_interval=60000  
    monitor_ping_interval=10000  
    monitor_read_only_interval=1500  
    monitor_read_only_timeout=500  
    ping_interval_server_msec=120000 # 适当调整  
    ping_timeout_server=500  
    commands_stats=true  
    sessions_sort=true  
    connect_retries_on_failure=10  

    # 优化内存使用及性能监控  
    max_idle_connections=1000       # 最大空闲连接数  
    monitor_server_prune_interval=60000 # 删除过期监控服务器的间隔  
}

mysql_group_replication_hostgroups =  
(  
    {  
        writer_hostgroup=10  
        backup_writer_hostgroup=11  
        reader_hostgroup=12  
        offline_hostgroup=13  
        active=1  
        max_writers=1  
        writer_is_also_reader=1  
        max_transactions_behind=100  
        comment="MySQL MGR Group"  
    }  
)

mysql_servers =  
(  
    { address="192.168.30.191" , port=3319 , hostgroup=10, max_connections=300 },  
    { address="192.168.30.192" , port=3319 , hostgroup=10, max_connections=300 },  
    { address="192.168.30.193" , port=3320 , hostgroup=10, max_connections=300 },  
)

mysql_users:  
(  
    {  
        username = "root"  
        password = "YOUR_ROOT_PASSWORD"  
        default_hostgroup = 10  
    }  
)
  • mysql_variables 各参数说明参考官方文档，这里简单记录下：

参数名

说明

threads

可用于处理请求的线程数。增加此值可以帮助处理更多的并发请求。

max_connections

允许的最大连接数，针对32GB内存进行了调整以适应更多连接。

default_query_delay

查询之间的延迟时间（以毫秒为单位）。设置为0表示没有延迟。

default_query_timeout

查询执行的最长时间（以毫秒为单位），3000000毫秒即50分钟。

have_compress

如果设置为true，则启用通信压缩。

poll_timeout

套接字轮询操作的等待时间（以毫秒为单位）。

interfaces

指定服务器监听连接的IP地址和端口。

default_schema

默认使用的数据库模式，此处为information_schema。

stacksize

每个线程的堆栈大小（以字节为单位），1048576字节即1 MB。

server_version

使用的MySQL服务器版本。

connect_timeout_server

在连接超时之前等待服务器连接建立的时间（以毫秒为单位）。

monitor_username

用于监控的用户名。

monitor_password

与监控用户名关联的密码。

monitor_history

保留监控历史数据的时间（以毫秒为单位）。

monitor_connect_interval

尝试监控连接之间的时间间隔（以毫秒为单位）。

monitor_ping_interval

监控服务器状态的ping尝试之间的时间间隔（以毫秒为单位）。

monitor_read_only_interval

监控期间检查只读状态的时间间隔（以毫秒为单位）。

monitor_read_only_timeout

监控只读状态的超时时间（以毫秒为单位）。

ping_interval_server_msec

检查服务器状态的ping时间间隔（以毫秒为单位）；根据需要进行适当调整。

ping_timeout_server

服务器ping的超时时间（以毫秒为单位）。

commands_stats

如果启用（true），则跟踪命令的统计信息。

sessions_sort

如果为true，允许对会话进行排序，有助于管理和监控。

connect_retries_on_failure

服务器连接失败时的重试次数。

max_idle_connections

允许的最大空闲连接数，以优化内存使用。

monitor_server_prune_interval

从列表中删除过期监控服务器的时间间隔（以毫秒为单位）。

  • mysql_group_replication_hostgroups 配置MGR的信息，后面自动将各节点划分到如下配置的组信息中：
    写组10、备写组11、读组12、离线组13
  • mysql_servers 配置MGR 集群的每一个节点信息，默认组可以都写成10，ProxySQL会根据监控信息自动重新划分
  • mysql_users 规定可以使用 proxsql连接的用户，如果不在这里面是无法连接上mysql的。

ProxySQL常用的端口如下：

端口类型

默认端口

描述

Admin Port (管理端口)

6032

用于管理和配置 ProxySQL

MySQL Frontend Port (MySQL 前端端口)

6033

用于应用程序连接到 ProxySQL，就像连接到 MySQL 服务器一样

Stats Port (统计端口)

6080

用于访问 ProxySQL 的统计信息

三台主机上创建容器： 注意：必须host模式 否则监控无效

sudo docker run -d --network=host \
    --privileged \
    --restart=always \
    --name proxysql \
    -v /apps/proxysql/conf/proxysql.cnf:/etc/proxysql.cnf \
    proxysql/proxysql:2.6.5

进入容器中验证：

docker exec -it proxysql /bin/bash

在容器中执行：

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

查看当前主机信息：

select hostgroup_id, hostname,port, status  from runtime_mysql_servers;

(这里是示例图，非对应上面主机IP)

Docker方式部署ProxySQL和Keepalived组合实现MGR的高可用访问_mgr_02


查看监控详情：

select  time_start_us, hostname,  port,  viable_candidate,  read_only, transactions_behind,  error  from mysql_server_group_replication_log   order by time_start_us desc   limit 6;

(这里是示例图，非对应上面主机IP)

Docker方式部署ProxySQL和Keepalived组合实现MGR的高可用访问_proxysql_03


read_only 字段为 NO 的表示主节点，为YES的表示从节点，hostname会将名称和IP都记录上，MGR节点间通讯采用的时主机名

使用Navicat 采用 6033 端口连接ProxySQL看下是否可以成功连接上MySQL主节点。

3. Keepalived部署高可用

三台物理服务器都操作下面步骤

镜像下载

docker pull osixia/keepalived:2.0.20

映射文件

mkdir -p /apps/keepalived/conf
cd /apps/keepalived/conf
touch keepalived.conf

192.168.30.191上 keepalived.conf：

vrrp_instance VI_1 {  
    state MASTER  
    interface eth0             
    virtual_router_id 51  
    priority 101                 
    advert_int 1  
    authentication {  
        auth_type PASS  
        auth_pass yourpass  
    }  
    
    virtual_ipaddress {  
        192.168.1.100        
    }  
    
    track_script {  
        chk_proxysql  
    }  
}  

vrrp_script chk_proxysql {  
    script "nc -zv localhost 6033 > /dev/null 2>&1"  
    interval 2  
    weight -3  
}

192.168.30.192上 keepalived.conf：

vrrp_instance VI_1 {  
    state BACKUP
    interface eth0             
    virtual_router_id 51  
    priority 100                 
    advert_int 1  
    authentication {  
        auth_type PASS  
        auth_pass yourpass  
    }  
    
    virtual_ipaddress {  
        192.168.1.100        
    }  
    
    track_script {  
        chk_proxysql  
    }  
}  

vrrp_script chk_proxysql {  
    script "nc -zv localhost 6033 > /dev/null 2>&1"  
    interval 2  
    weight -3  
}

192.168.30.193上 keepalived.conf：

vrrp_instance VI_1 {  
    state BACKUP
    interface eth0             
    virtual_router_id 51  
    priority 99              
    advert_int 1  
    authentication {  
        auth_type PASS  
        auth_pass yourpass  
    }  
    
    virtual_ipaddress {  
        192.168.1.100        
    }  
    
    track_script {  
        chk_proxysql  
    }  
}  

vrrp_script chk_proxysql {  
    script "nc -zv localhost 6033 > /dev/null 2>&1"  
    interval 2  
    weight -3  
}

主要是如下几个变量要注意：

interval 2 每隔2秒执行一次

weight -5 脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级减5

fall 2 #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间） 默认值为 3

rise 1 #检测1次成功就算成功。但不修改优先级 默认值为 2

interface 当前节点服务器的网卡名称

priority 优先级，每一个节点上不一样

virtual_ipaddress 虚拟ip

virtual_router_id 共享相同虚拟IP的节点上该参数值必须一样 ，
keepalived可以代理多个虚拟IP，每一个虚拟IP对应的virtual_router_id值不可一样。

state 指定当前keepalived的初始状态，这个可用是 MASTER 或者 BACKUP ，如果是BACKUP 则作为从节点，但是这个值会根据监控脚本运行状态改变优先级从而导致状MASTER /BACKUP 切换.

三台物理服务器都操作下面步骤

docker run -d --name keepalived \
	--cap-add=NET_ADMIN \
	--cap-add=NET_BROADCAST \
	--cap-add=NET_RAW \
	--net=host \
	-e KEEPALIVED_INTERFACE=eth0 \
	-v /apps/keepalived/conf/keepalived.conf:/container/service/keepalived/assets/keepalived.conf \
	osixia/keepalived:2.0.20 --loglevel debug --copy-service

变量 KEEPALIVED_INTERFACE 是指当前节点服务器的网卡名称，如果不想开启debug模式去掉--loglevel debug

4. 测试高可用

  • 使用虚拟IP 192.168.1.100 连接ProxySQL 6033端口，读写MySQL数据库
  • 关闭MySQL主节点，查看是否可以继续读写MySQL数据库
  • 关闭某服务器上的ProxySQL服务看下是否可以继续读写MySQL数据库
  • 重新测试，直接关闭一台服务器或者拔掉一台服务器的网线继续查看