应用层 → 接入层 → 代理层 → 数据层
(VIP) (HAProxy) (ProxySQL) (MySQL)
核心思想：每一层只做一件事，层与层之间解耦，任何一层出问题都不影响整体可用性。
OS:
[root@flink-node1 ~]# cat /etc/redhat-release
Rocky Linux release 8.10 (Green Obsidian)
下载 proxysql-3.0.9-1-almalinux8.x86_64.rpm
21、22：Haproxy、keepalived
196/197/33: ProxySQL

应用程序
│
│ 连接 VIP: 81:6033
▼
┌─────────────────────────────────────────────────────────┐
│ Keepalived (VIP 漂移) │
│ - Master: 21 (正常工作时持有 VIP) │
│ - Backup: 22 (Master 故障时接管 VIP) │
└─────────────────────────────────────────────────────────┘
│
│ VIP 指向 Master HAProxy 81
▼
┌─────────────────────────────────────────────────────────┐
│ HAProxy (负载均衡) │
│ - 轮询分发请求到三台 ProxySQL │
└─────────────────────────────────────────────────────────┘
│ │ │
▼ ▼ ▼
┌─────────────┐ ┌─────────────┐ ┌─────────────┐
│ ProxySQL1 │ │ ProxySQL2 │ │ ProxySQL3 │
│ 196:6033 │ │ 197:6033 │ │ 33:6033 │
└─────────────┘ └─────────────┘ └─────────────┘
│ │ │
└────────────────────┼────────────────────┘
▼
┌─────────────────┐
│ MySQL 单机 │
│ 177 │

****三台：配置 ProxySQL（196/197/33三台都要执行）
#sudo dnf search openssl3

#dnf install openssl3 openssl3-devel -y

#rpm -ivh proxysql-3.0.9-1-almalinux8.x86_64.rpm

#systemctl enable proxysql

#systemctl start proxysql

#systemctl status proxysql

#ss -tunlp | grep -E “6032|6033”

#mysql -u admin -padmin -h 127.0.0.1 -P 6032

– ============================================
– 1. 清理旧配置
– ============================================
DELETE FROM mysql_servers;
DELETE FROM mysql_users;
DELETE FROM mysql_query_rules;

– ============================================
– 2. 添加单机 MySQL (hostgroup=0)
– ============================================
– 请替换 IP
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, comment)
VALUES (0, ‘10.206.49.100’, 3306, 1, ‘single_mysql’);

– ============================================
– 3. 添加主从 MySQL
– 主库: hostgroup=10 (写)
– 从库: hostgroup=20 (读)
– ============================================
– 请替换 IP
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, comment)
VALUES (10, ‘10.206.49.200’, 3306, 1, ‘master’);

INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, comment)
VALUES (20, ‘10.206.49.201’, 3306, 1, ‘slave1’);

INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, comment)
VALUES (20, ‘10.206.49.202’, 3306, 1, ‘slave2’);

– ============================================
– 4. 配置监控（所有 MySQL 用同一监控用户）
– ============================================
UPDATE global_variables SET variable_value=‘proxysql_monitor’
WHERE variable_name=‘mysql-monitor_username’;
UPDATE global_variables SET variable_value=‘monitor_password’
WHERE variable_name=‘mysql-monitor_password’;

– ============================================
– 5. 创建应用用户（两套 MySQL 用不同用户）
– ============================================

– 用户1：访问单机 MySQL (默认走 group 0)
INSERT INTO mysql_users(username, password, default_hostgroup, transaction_persistent, active)
VALUES (‘app_single’, ‘Single@2026’, 0, 1, 1);

– 用户2：访问主从 MySQL (默认走 group 10 主库)
INSERT INTO mysql_users(username, password, default_hostgroup, transaction_persistent, active)
VALUES (‘app_repl’, ‘Repl@2026’, 10, 1, 1);

– ============================================
– 6. 配置查询规则
– ============================================

– 规则组 A：针对 app_single 用户（单机，所有查询走组0）
INSERT INTO mysql_query_rules(rule_id, active, username, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, ‘app_single’, ‘^.*$’, 0, 1);

– 规则组 B：针对 app_repl 用户（主从，读写分离）
– 规则2：SELECT … FOR UPDATE 走主库
INSERT INTO mysql_query_rules(rule_id, active, username, match_digest, destination_hostgroup, apply)
VALUES (10, 1, ‘app_repl’, ‘^SELECT.*FOR UPDATE$’, 10, 1);

– 规则3：普通 SELECT 走从库
INSERT INTO mysql_query_rules(rule_id, active, username, match_digest, destination_hostgroup, apply)
VALUES (20, 1, ‘app_repl’, ‘^SELECT’, 20, 1);

– 规则4：写入操作走主库
INSERT INTO mysql_query_rules(rule_id, active, username, match_pattern, destination_hostgroup, apply)
VALUES (30, 1, ‘app_repl’, ‘^(INSERT|UPDATE|DELETE|REPLACE)’, 10, 1);

– ============================================
– 7. 加载并保存配置
– ============================================
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
LOAD MYSQL VARIABLES TO RUNTIME;

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

– ============================================
– 8. 验证配置
– ============================================
SELECT hostgroup_id, hostname, port, status, comment FROM runtime_mysql_servers;
SELECT username, default_hostgroup, active FROM runtime_mysql_users;
SELECT rule_id, active, username, match_digest, destination_hostgroup FROM runtime_mysql_query_rules;

MySQL数据库配置：

CREATE USER ‘proxysql_monitor’@‘%’ IDENTIFIED BY ‘monitor_password’;
GRANT SELECT ON mysql.* TO ‘proxysql_monitor’@‘%’;
FLUSH PRIVILEGES;

CREATE USER ‘proxysql_monitor’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘monitor_password’;
GRANT SELECT ON mysql.* TO ‘proxysql_monitor’@‘%’;
GRANT REPLICATION CLIENT ON . TO ‘proxysql_monitor’@‘%’;

– 1. 先创建用户
CREATE USER ‘app_single’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘Single@2026’;

– 2. 授予权限
GRANT ALL PRIVILEGES ON . TO ‘app_single’@‘%’;

– 3. 刷新权限
FLUSH PRIVILEGES;

– 4. 验证用户是否创建成功
SELECT host, user, plugin FROM mysql.user WHERE user = ‘app_single’;

ALTER USER ‘proxysql_monitor’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘monitor_password’;
ALTER USER ‘app_single’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘Single@2026’;

SELECT host, user, plugin FROM mysql.user WHERE user = ‘proxysql_monitor’;

FLUSH PRIVILEGES;

– 创建用户
CREATE USER ‘app_iad’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘app_iad*963.’;

– 授予权限：只针对具体业务数据库
GRANT SELECT, INSERT, UPDATE, DELETE ON ava.* TO ‘app_iad’@‘%’;

– 如果需要执行存储过程/函数
GRANT EXECUTE ON ava.* TO ‘app_iad’@‘%’;

– 如果不需要删除表结构（只删除数据即可）
– 不要给 DROP, CREATE, ALTER 权限

– 1. 单机 MySQL 的应用用户（连接 VIP 使用）
CREATE USER ‘app_single’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘Single@2026’;
GRANT SELECT, INSERT, UPDATE, DELETE ON ava.* TO ‘app_single’@‘%’;

– 2. 主从 MySQL 的应用用户（如果需要）
CREATE USER ‘app_repl’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘Repl@2026’;
GRANT SELECT, INSERT, UPDATE, DELETE ON ava.* TO ‘app_repl’@‘%’;

– 3. ProxySQL 监控用户
CREATE USER ‘proxysql_monitor’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘monitor_password’;
GRANT SELECT ON mysql.* TO ‘proxysql_monitor’@‘%’;
GRANT REPLICATION CLIENT ON . TO ‘proxysql_monitor’@‘%’;

FLUSH PRIVILEGES;

SELECT CURRENT_USER();
show grants
GRANT SYSTEM_USER ON . TO ‘root’@‘%’;

安装及配置Haproxy、keepalived (两台21/22都需要)
#yum install -y haproxy
#haproxy -v
#sudo tee /etc/haproxy/haproxy.cfg > /dev/null << ‘EOF’
global
log 127.0.0.1 local2
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
daemon
stats socket /var/lib/haproxy/stats

defaults
mode tcp
log global
option dontlognull
option redispatch
retries 3
timeout connect 5000ms
timeout client 50000ms
timeout server 50000ms
maxconn 3000

============================================

前端1：单机 MySQL (端口 6033)

============================================

frontend mysql_single_frontend
bind 0.0.0.0:6033
mode tcp
default_backend mysql_single_backend

backend mysql_single_backend
mode tcp
balance roundrobin
option tcp-check
server proxysql1 xx.xx.xx.196:6033 check inter 5000 rise 2 fall 3
server proxysql2 xx.xx.xx.197:6033 check inter 5000 rise 2 fall 3
server proxysql3 xx.xx.xx.33:6033 check inter 5000 rise 2 fall 3

============================================

前端2：主从 MySQL (端口 6034)

============================================

frontend mysql_repl_frontend
bind :6034
mode tcp
default_backend mysql_repl_backend

backend mysql_repl_backend
mode tcp
balance roundrobin
option tcp-check
server proxysql1 xx.xx.xx.196:6033 check inter 5000 rise 2 fall 3
server proxysql2 xx.xx.xx.197:6033 check inter 5000 rise 2 fall 3
server proxysql3 xx.xx.xx.33:6033 check inter 5000 rise 2 fall 3

============================================

统计页面

============================================

listen stats
bind 0.0.0.0:8082
mode http
stats enable
stats uri /haproxy?stats
stats refresh 30s
stats auth admin:admin123
EOF

#sudo systemctl restart haproxy

#21/22安装keepalived

#yum install -y keepalived
#sudo tee /etc/keepalived/check_haproxy.sh > /dev/null << ‘EOF’
#!/bin/bash
if pgrep -x haproxy > /dev/null; then
exit 0
else
systemctl start haproxy
sleep 2
if pgrep -x haproxy > /dev/null; then
exit 0
else
systemctl stop keepalived
exit 1
fi
fi
EOF
#sudo chmod +x /etc/keepalived/check_haproxy.sh
[root@te-db1 oracle]# cat /etc/keepalived/check_haproxy.sh
#!/bin/bash
if pgrep -x haproxy > /dev/null; then
exit 0
else
systemctl start haproxy
sleep 2
if pgrep -x haproxy > /dev/null; then
exit 0
else
systemctl stop keepalived
exit 1
fi
fi

21 keepalived配置文件：
[root@te-db1 oracle]# cat /etc/keepalived/keepalived.conf
global_defs {
router_id LVS_MASTER
script_user root
enable_script_security
}

vrrp_script chk_haproxy {
script “/etc/keepalived/check_haproxy.sh”
interval 2
weight 2
}

vrrp_instance VI_1 {
state MASTER # 主节点状态为 MASTER
interface eno2 # 确认网卡名称，可能是 ens192 或 eth0
virtual_router_id 51 # 虚拟路由ID，主备必须一致，范围 0-255
priority 101 # 优先级，主节点要高于备节点
advert_int 1 # 发送 VRRP 通告的间隔（秒）
# 认证信息，主备必须一致
authentication {
auth_type PASS
auth_pass Mysql@Proxy2026
}
# 虚拟 IP 地址（VIP），这就是客户端要连接的地址
virtual_ipaddress {
10.206.49.81/24 dev eno2 label eno2:1
}
# 关联健康检查脚本
track_script {
chk_haproxy
}
}

22 keepalived 配置文件：
[root@te-db2 oracle]# cat /etc/keepalived/keepalived.conf
global_defs {
router_id LVS_BACKUP
script_user root
enable_script_security
}

vrrp_script chk_haproxy {
script “/etc/keepalived/check_haproxy.sh”
interval 2
weight 2
}

vrrp_instance VI_1 {
state BACKUP
interface eno2 # 确认网卡名称
virtual_router_id 51
priority 100
advert_int 1

authentication {
    auth_type PASS
    auth_pass Mysql@Proxy2026
}

virtual_ipaddress {
    10.206.49.81/24 dev eno2 label eno2:1
}

track_script {
    chk_haproxy
}

}

*#为了让 HAProxy 能够绑定在非本机 IP（VIP）上，需要开启一个内核参数。在两台服务器上分别执行：
*
#启用非本地 IP 绑定
#echo “net.ipv4.ip_nonlocal_bind = 1” | sudo tee -a /etc/sysctl.conf

#使参数生效
#sudo sysctl -p

#systemctl enable keepalived
#systemctl start keepalived
#systemctl status keepalived

#systemctk enable haproxy
#systemctk start haproxy
#systemctk status haproxy

#测试
[root@flink-node3 ~]# mysql -u app_single -pSingle@2026 -h 10.206.49.81 -P 6033 -e “SELECT CONNECTION_ID(), USER(), @@hostname;”
mysql: [Warning] Using a password on the command line interface can be insecure.
±----------------±-------------------------±-----------+
| CONNECTION_ID() | USER() | @@hostname |
±----------------±-------------------------±-----------+
| 6485223 | app_single@ xx.xx.xx.196 | BE5 |
±----------------±-------------------------±-----------+
[root@flink-node3 ~]# mysql -u app_single -pSingle@2026 -h 10.206.49.81 -P 6033 -e “SELECT CONNECTION_ID(), USER(), @@hostname;”
mysql: [Warning] Using a password on the command line interface can be insecure.
±----------------±-------------------------±-----------+
| CONNECTION_ID() | USER() | @@hostname |
±----------------±-------------------------±-----------+
| 6485352 | app_single@ xx.xx.xx.197 | BE5 |
±----------------±-------------------------±-----------+
[root@flink-node3 ~]# mysql -u app_single -pSingle@2026 -h 10.206.49.81 -P 6033 -e “SELECT CONNECTION_ID(), USER(), @@hostname;”
mysql: [Warning] Using a password on the command line interface can be insecure.
±----------------±------------------------±-----------+
| CONNECTION_ID() | USER() | @@hostname |
±----------------±------------------------±-----------+
| 6485355 | app_single@ xx.xx.xx.33 | BE5 |
±----------------±------------------------±-----------+

测试OK。。。