ProxySQL 完整生产配置教程

原创于 2026-07-09 22:24:08 发布 · 229 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

VMware虚拟机下载安装Ubuntu

一、ProxySQL 完整生产配置（可直接复制部署）

1. 环境说明

主库：192.168.10.10 hostgroup=10（写组）
从库1：192.168.10.11 hostgroup=20（读组）
从库2：192.168.10.12 hostgroup=20（读组）
ProxySQL管理端口：6032，业务端口：6033
监控账号：proxy_monitor / Monitor@123
业务账号：app_user / App@123

2. 一键初始化SQL（登录 6032 执行）

-- 1. 清空旧配置
DELETE FROM mysql_servers;
DELETE FROM mysql_users;
DELETE FROM mysql_query_rules;

-- 2. 添加后端MySQL节点
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag)
VALUES
-- 写组：主库
(10,'192.168.10.10',3306,1000,2000,3),
-- 读组：两台从库，权重一致，延迟超过3秒自动下线
(20,'192.168.10.11',3306,100,2000,3),
(20,'192.168.10.12',3306,100,2000,3);

-- 3. 监控账号配置
SET mysql_monitor_username='proxy_monitor';
SET mysql_monitor_password='Monitor@123';
SET mysql_monitor_connect_timeout=1000;
SET mysql_monitor_ping_timeout=500;
SET mysql_monitor_replication_lag_threshold=3;

-- 4. 读写分离路由规则
-- rule1：SELECT ... FOR UPDATE 走主库
INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply,comment)
VALUES (1,1,'^SELECT .*FOR UPDATE$',10,0,'行锁查询强制主库');

-- rule2：普通SELECT走读库
INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply,comment)
VALUES (2,1,'^SELECT',20,1,'普通查询负载均衡从库');

-- rule3：其余DML/DDL全部走主库
INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply,comment)
VALUES (3,1,'.',10,1,'增删改建表走主库');

-- 5. 业务访问账号
INSERT INTO mysql_users(username,password,default_hostgroup,transaction_persistent,max_connections)
VALUES ('app_user','App@123',10,1,1500);

-- 6. 加载生效+持久化
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL VARIABLES TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;

SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL VARIABLES TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
SAVE MYSQL USERS TO DISK;

-- 查看校验
SELECT hostgroup_id,hostname,status,weight,max_replication_lag FROM mysql_servers;
SELECT rule_id,match_pattern,destination_hostgroup FROM mysql_query_rules;
SELECT username,default_hostgroup FROM mysql_users;

3. /etc/proxysql.cnf 主配置文件

datadir="/var/lib/proxysql"
admin_variables=
{
    admin_credentials="admin:admin"
    mysql_ifaces="0.0.0.0:6032"
    web_admin_enabled=true
    web_admin_port=6080
}
mysql_variables=
{
    threads=8
    max_connections=10000
    default_query_delay=0
    default_query_timeout=36000000
    poll_timeout=2000
    interfaces="0.0.0.0:6033"
    stacksize=1048576
    server_version="8.0.32"
    connect_retries=3
    monitor_interval=1000
}
mysql_servers =
(
)
mysql_users =
(
)
mysql_query_rules =
(
)
scheduler=
(
)

4. 启停命令

systemctl start proxysql
systemctl enable proxysql
systemctl status proxysql
# 登录管理后台
mysql -uadmin -padmin -h127.0.0.1 -P6032

二、HAProxy + Keepalived 高可用完整配置

拓扑

ProxySQL1：192.168.10.20
ProxySQL2：192.168.10.21
VIP虚拟IP：192.168.10.99（应用统一连接此IP）

1. /etc/haproxy/haproxy.cfg 两台机器通用

global
    log /dev/log local0 info
    log /dev/log local0 notice
    maxconn 50000
    user haproxy
    group haproxy
    daemon

defaults
    mode tcp
    retries 3
    timeout connect 5s
    timeout client 30s
    timeout server 30s
    timeout queue 10s

# 业务前端：统一VIP 6033 转发至两台ProxySQL
frontend mysql_proxy
    bind 192.168.10.99:6033
    default_backend proxysql_pool

backend proxysql_pool
    balance leastconn
    server px1 192.168.10.20:6033 check inter 2000 rise 2 fall 3 weight 100
    server px2 192.168.10.21:6033 check inter 2000 rise 2 fall 3 weight 100

# 监控面板
listen stats
    bind 0.0.0.0:8080
    mode http
    stats enable
    stats uri /stats
    stats auth admin:Ha@123456
    stats refresh 3s

2. Keepalived 主节点配置（192.168.10.20 /etc/keepalived/keepalived.conf）

global_defs {
   router_id LVS_MYSQL_PROXY_MASTER
}
vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456
    }
    virtual_ipaddress {
        192.168.10.99/24 dev eth0 label eth0:vip
    }
}

3. Keepalived 备节点配置（192.168.10.21）

global_defs {
   router_id LVS_MYSQL_PROXY_BACKUP
}
vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456
    }
    virtual_ipaddress {
        192.168.10.99/24 dev eth0 label eth0:vip
    }
}

4. 启动自启

systemctl start haproxy keepalived
systemctl enable haproxy keepalived

5. 验证方式

查看VIP：ip addr，主节点会绑定192.168.10.99
访问监控：http://192.168.10.20:8080 / http://192.168.10.21:8080
应用连接VIP：mysql -uapp_user -pApp@123 -h192.168.10.99 -P6033

三、配套MySQL主从创建账号SQL（所有MySQL节点执行）

-- 复制账号
CREATE USER 'repl'@'%' IDENTIFIED BY 'Repl@123';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- ProxySQL监控账号
CREATE USER 'proxy_monitor'@'%' IDENTIFIED BY 'Monitor@123';
GRANT SELECT, REPLICATION CLIENT ON *.* TO proxy_monitor@'%';

-- 业务访问账号
CREATE USER 'app_user'@'%' IDENTIFIED BY 'App@123';
GRANT ALL ON business.* TO app_user@'%';

FLUSH PRIVILEGES;