写在最前
在实践使用 ProxySQL 的过程中，目前仍存在读写不一致的问题。在非事务场景下写操作完成后若立即进行读取，存在一定概率读取到旧数据。
目前为了保证可用性，仅采用了保守的策略以实现故障转移能力，在proxysql.cnf中读写我都写成了10写组。若要实现真正可靠的、具备强一致性的读写分离，还需要进一步探索和优化，欢迎给我留言讨论此现象。
在最新的实践过程中，我们可以使用官方的mysql-binlog-reader来实现持续监听每个节点的gtid事务，从而实现可靠的读写分离防止出现脏读，具体详解在最下文高级用法。
前置配置
配置数据库
创建依赖视图，⽬的让ProxySQL识别PolarDB-X标准版的元数据（Leader、Follower）
CREATE VIEW sys.gr_member_routing_candidate_status AS
SELECT IF(ROLE='Leader' OR ROLE='Follower', 'YES', 'NO' ) as viable_candidate,
IF(ROLE <>'Leader', 'YES', 'NO' ) as read_only,
IF (ROLE = 'Leader', 0, LAST_LOG_INDEX - LAST_APPLY_INDEX) as transactions_behind,
0 as 'transactions_to_cert'
FROM information_schema.ALISQL_CLUSTER_LOCAL;
创建proxysql的监控账户，ProxySQL运⾏通⽤依赖
⽐如创建proxysql_monitor，密码为123456（密码请按需修改）
create user 'proxysql_monitor'@'%' identified with mysql_native_password by '123456';
GRANT SELECT on sys.* to 'proxysql_monitor'@'%';创建测试账户（⾼可⽤测试会依赖该账号）
⽐如创建admin2，密码为123456（密码请按需修改）
create user 'admin2'@'%' identified with mysql_native_password by '123456';
GRANT all privileges on *.* to 'admin2'@'%';检查配置是否⽣效
# 检查创建的视图
MySQL [(none)]> select * from sys.gr_member_routing_candidate_status ;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES | NO | 0 | 0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.00 sec)
# 检查创建的账号
MySQL [(none)]> select User,Host from mysql.user where User in ('admin2','proxysql_monitor');
+------------------+------+
| User | Host |
+------------------+------+
| admin2 | % |
| proxysql_monitor | % |
+------------------+------+
2 rows in set (0.00 sec)
1. docker 部署
2. kubernetes 部署
在 Kubernetes 中部署 ProxySQL 时，我尝试了使用原始的 proxysql.cnf 配置文件，RPM方式是通过手动插入 SQL 的方式来配置。两种方式其实都可以使用，具体选择哪一种可以根据个人习惯或运维需求决定。默认配置文件路径为 /etc/proxysql.cnf。
2.1 deployment
kind: Deployment
apiVersion: apps/v1
metadata:
name: proxysql
namespace: bx
annotations:
deployment.kubernetes.io/revision: '43'
kubesphere.io/creator: admin
spec:
replicas: 1
selector:
matchLabels:
app: proxysql
template:
metadata:
creationTimestamp: null
labels:
app: proxysql
annotations:
kubesphere.io/creator: admin
kubesphere.io/imagepullsecrets: '{}'
kubesphere.io/restartedAt: '2025-09-10T08:00:04.771Z'
logging.kubesphere.io/logsidecar-config: '{}'
spec:
volumes:
- name: volume-5oezwk
configMap:
name: proxysql-conf
items:
- key: sync_user.sh
path: sync_user.sh
defaultMode: 493
- name: volume-bk62iq
configMap:
name: proxysql-conf
items:
- key: proxysql.cnf
path: proxysql.cnf
defaultMode: 493
- name: volume-se1c8v
configMap:
name: proxysql-conf
items:
- key: login.sh
path: login.sh
defaultMode: 493
containers:
- name: proxysql
image: 'proxysql/proxysql:2.4.8-centos'
ports:
- name: http-0
containerPort: 6033
protocol: TCP
- name: http-1
containerPort: 6032
protocol: TCP
resources: {}
volumeMounts:
- name: volume-5oezwk
readOnly: true
mountPath: /opt/proxysql_scripts/sync_user.sh
subPath: sync_user.sh
- name: volume-bk62iq
readOnly: true
mountPath: /etc/proxysql.cnf
subPath: proxysql.cnf
- name: volume-se1c8v
readOnly: true
mountPath: /login.sh
subPath: login.sh
terminationMessagePath: /dev/termination-log
terminationMessagePolicy: File
imagePullPolicy: IfNotPresent
securityContext: {}
restartPolicy: Always
terminationGracePeriodSeconds: 30
dnsPolicy: ClusterFirst
securityContext: {}
schedulerName: default-scheduler
strategy:
type: RollingUpdate
rollingUpdate:
maxUnavailable: 1
maxSurge: 25%
revisionHistoryLimit: 10
progressDeadlineSeconds: 600
2.2 configmap
CREATE USER 'proxysql_sync'@'%' IDENTIFIED BY 'xxxxxxxxxxxxxxxxxxxxxxxxxx';
GRANT SELECT ON mysql.user TO 'proxysql_sync'@'%';
FLUSH PRIVILEGES;
kind: ConfigMap
apiVersion: v1
metadata:
name: proxysql-conf
namespace: bx
annotations:
kubesphere.io/creator: admin
data:
login.sh: mysql -uadmin -padmin -h 127.0.0.1 -P 6032 -A
proxysql.cnf: >
#file proxysql.cfg
########################################################################################
# This config file is parsed using libconfig , and its grammar is described
in:
#
http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar
# Grammar is also copied at the end of this file
########################################################################################
########################################################################################
# IMPORTANT INFORMATION REGARDING THIS CONFIGURATION FILE:
########################################################################################
# On startup, ProxySQL reads its config file (if present) to determine its
datadir.
# What happens next depends on if the database file (disk) is present in the
defined
# datadir (i.e. "/var/lib/proxysql/proxysql.db").
#
# If the database file is found, ProxySQL initializes its in-memory
configuration from
# the persisted on-disk database. So, disk configuration gets loaded into
memory and
# then propagated towards the runtime configuration.
#
# If the database file is not found and a config file exists, the config
file is parsed
# and its content is loaded into the in-memory database, to then be both
saved on-disk
# database and loaded at runtime.
#
# IMPORTANT: If a database file is found, the config file is NOT parsed. In
this case
# ProxySQL initializes its in-memory configuration from the
persisted on-disk
# database ONLY. In other words, the configuration found in the
proxysql.cnf
# file is only used to initial the on-disk database read on the
first startup.
#
# In order to FORCE a re-initialise of the on-disk database from the
configuration file
# the ProxySQL service should be started with "systemctl start
proxysql-initial".
#
########################################################################################
datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"
admin_variables=
{
admin_credentials="admin:admin"
# mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
mysql_ifaces="0.0.0.0:6032"
# refresh_interval=2000
# debug=true
}
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
# interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
#server_version="5.5.30"
server_version="8.0.25"
connect_timeout_server=3000
# make sure to configure monitor username and password
#
https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
monitor_username="proxysql_monitor"
monitor_password="xxxxxxxxxxxxxxxxxxxxxxxxxx"
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
}
# defines all the MySQL servers
mysql_servers =
(
# {
# address = "127.0.0.1" # no default, required . If port is 0
, address is interpred as a Unix Socket Domain
# port = 3306 # no default, required . If port is 0
, address is interpred as a Unix Socket Domain
# hostgroup = 0 # no default, required
# status = "ONLINE" # default: ONLINE
# weight = 1 # default: 1
# compression = 0 # default: 0
# max_replication_lag = 10 # default 0 . If greater than 0 and
replication lag passes such threshold, the server is shunned
# },
# {
# address = "/var/lib/mysql/mysql.sock"
# port = 0
# hostgroup = 0
# },
# {
# address="127.0.0.1"
# port=21891
# hostgroup=0
# max_connections=200
# },
# { address="127.0.0.2" , port=3306 , hostgroup=0, max_connections=5
},
# { address="127.0.0.1" , port=21892 , hostgroup=1 },
# { address="127.0.0.1" , port=21893 , hostgroup=1 }
# { address="127.0.0.2" , port=3306 , hostgroup=1 },
# { address="127.0.0.3" , port=3306 , hostgroup=1 },
# { address="127.0.0.4" , port=3306 , hostgroup=1 },
# { address="/var/lib/mysql/mysql.sock" , port=0 , hostgroup=1 }
{ address="172.31.0.31" , port=4886 , hostgroup=10, max_connections=1000 },
{ address="172.31.0.32" , port=4886 , hostgroup=20, max_connections=1000 },
{ address="172.31.0.33" , port=4886 , hostgroup=20, max_connections=1000 },
#{ address="mysql5.bx.svc.cluster.local", port=3306, hostgroup_id=99,max_connections=1000}
)
# defines all the MySQL users
mysql_users:
(
# {
# username = "username" # no default , required
# password = "password" # default: ''
# default_hostgroup = 0 # default: 0
# active = 1 # default: 1
# },
# {
# username = "root"
# password = ""
# default_hostgroup = 0
# max_connections=1000
# default_schema="test"
# active = 1
# },
# { username = "user1" , password = "password" , default_hostgroup = 0
, active = 0 }
)
#defines MySQL Query Rules
mysql_query_rules:
(
{
rule_id=1
active=1
match_pattern="^SELECT .* FOR UPDATE$"
destination_hostgroup=10
apply=1
},
{
rule_id=2
active=1
match_pattern="^SELECT"
destination_hostgroup=10
apply=1
}
)
scheduler=
(
{
id=1
active=1
interval_ms=10000
filename="/opt/proxysql_scripts/sync_user.sh"
arg1="0"
arg2="0"
arg3="0"
arg4="0"
arg5="0"
}
)
mysql_group_replication_hostgroups=
(
# {
# writer_hostgroup=30
# reader_hostgroup=40
# comment="test repl 1"
# },
# {
# writer_hostgroup=50
# reader_hostgroup=60
# comment="test repl 2"
# }
{
writer_hostgroup=10
backup_writer_hostgroup=20
reader_hostgroup=30
offline_hostgroup=40
active=1
writer_is_also_reader=1
}
)
#
http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar
#
# Below is the BNF grammar for configuration files. Comments and include
directives are not part of the grammar, so they are not included here.
#
# configuration = setting-list | empty
#
# setting-list = setting | setting-list setting
#
# setting = name (":" | "=") value (";" | "," | empty)
#
# value = scalar-value | array | list | group
#
# value-list = value | value-list "," value
#
# scalar-value = boolean | integer | integer64 | hex | hex64 | float
# | string
#
# scalar-value-list = scalar-value | scalar-value-list "," scalar-value
#
# array = "[" (scalar-value-list | empty) "]"
#
# list = "(" (value-list | empty) ")"
#
# group = "{" (setting-list | empty) "}"
#
# empty =
sync_user.sh: >
#!/usr/bin/env bash
# 脚本路径：/opt/proxysql_scripts/sync_user.sh
# 功能：兼容所有ProxySQL版本，从远程MySQL 8集群同步用户，支持节点故障转移
##############################################################################
# 1. 配置参数（根据真实环境修改）
##############################################################################
# 远程MySQL集群配置（MySQL 8）- 按优先级排序
REMOTE_MYSQL_CLUSTER=(
"172.31.0.31:4886" # 节点1：IP:端口
"172.31.0.32:4886" # 节点2：IP:端口
"172.31.0.33:4886" # 节点3：IP:端口（可根据实际集群规模增减）
)
REMOTE_MYSQL_USER="proxysql_sync" # 远程查询账号（必填）
REMOTE_MYSQL_PASS="xxxxxxxxxxxxxxxxxxxxxxxxxx" # 远程查询密码（必填）
REMOTE_EXCLUDE_USERS=("root" "admin" "mysql.sys" "mysql.session"
"mysql.infoschema", "proxysql_sync") # 排除特定用户
# ProxySQL配置
PROXYSQL_ADMIN_IP="127.0.0.1"
PROXYSQL_ADMIN_PORT="6032"
PROXYSQL_ADMIN_USER="admin"
PROXYSQL_ADMIN_PASS="admin"
PROXYSQL_DEFAULT_HG="10" # 目标主机组（必填）
# 日志和锁文件
LOG_FILE="/var/log/proxysql_sync_remote_users.log"
LOCK_FILE="/tmp/proxysql_sync_remote.lock"
CONNECT_TIMEOUT=10 # 连接超时时间(秒)
MAX_RETRY_PER_NODE=2 # 每个节点的最大重试次数
##############################################################################
# 2. 进程锁和依赖检查
##############################################################################
if [ -f "${LOCK_FILE}" ]; then
echo "[$(date '+%Y-%m-%d %H:%M:%S')] 已有同步进程运行，退出" >> "${LOG_FILE}"
exit 0
fi
touch "${LOCK_FILE}"
trap 'rm -f "${LOCK_FILE}"' EXIT
if ! command -v mysql &> /dev/null; then
echo "[$(date '+%Y-%m-%d %H:%M:%S')] 未找到mysql客户端，退出" >> "${LOG_FILE}"
exit 1
fi
##############################################################################
# 3. 尝试从集群节点读取用户
##############################################################################
echo "[$(date '+%Y-%m-%d %H:%M:%S')] 开始从MySQL集群同步用户" >> "${LOG_FILE}"
# 正确拼接排除用户列表（MySQL 8语法要求）
EXCLUDE_USERS_SQL=$(printf "'%s'," "${REMOTE_EXCLUDE_USERS[@]}" | sed
"s/,$//")
REMOTE_USERS=""
SUCCESS_NODE=""
# 循环尝试集群中的每个节点
for node in "${REMOTE_MYSQL_CLUSTER[@]}"; do
# 拆分IP和端口
REMOTE_MYSQL_IP=$(echo "${node}" | cut -d: -f1)
REMOTE_MYSQL_PORT=$(echo "${node}" | cut -d: -f2)
echo "[$(date '+%Y-%m-%d %H:%M:%S')] 尝试连接节点: ${REMOTE_MYSQL_IP}:${REMOTE_MYSQL_PORT}" >> "${LOG_FILE}"
# 每个节点最多重试指定次数
for ((retry=1; retry<=MAX_RETRY_PER_NODE; retry++)); do
# 尝试连接并获取用户列表
NODE_USERS=$(mysql -u"${REMOTE_MYSQL_USER}" -p"${REMOTE_MYSQL_PASS}" -h"${REMOTE_MYSQL_IP}" -P"${REMOTE_MYSQL_PORT}" \
--connect-timeout="${CONNECT_TIMEOUT}" \
-Nse "SELECT user, authentication_string
FROM mysql.user
WHERE user NOT IN (${EXCLUDE_USERS_SQL})
AND authentication_string IS NOT NULL;")
# 检查是否成功获取用户
if [ $? -eq 0 ]; then
REMOTE_USERS="${NODE_USERS}"
SUCCESS_NODE="${REMOTE_MYSQL_IP}:${REMOTE_MYSQL_PORT}"
echo "[$(date '+%Y-%m-%d %H:%M:%S')] 成功从节点 ${SUCCESS_NODE} 获取用户列表" >> "${LOG_FILE}"
break 2 # 跳出所有循环
else
echo "[$(date '+%Y-%m-%d %H:%M:%S')] 从节点 ${REMOTE_MYSQL_IP}:${REMOTE_MYSQL_PORT} 获取用户失败，重试次数: ${retry}/${MAX_RETRY_PER_NODE}" >> "${LOG_FILE}"
sleep 2 # 重试前等待2秒
fi
done
done
# 检查是否成功获取用户
if [ -z "${REMOTE_USERS}" ]; then
echo "[$(date '+%Y-%m-%d %H:%M:%S')] 所有集群节点均无法获取用户列表，同步失败" >> "${LOG_FILE}"
exit 1
fi
echo "[$(date '+%Y-%m-%d %H:%M:%S')] 从节点 ${SUCCESS_NODE} 查询到 $(echo
"${REMOTE_USERS}" | wc -l) 个用户" >> "${LOG_FILE}"
##############################################################################
# 4. 同步到ProxySQL
##############################################################################
SYNC_SUCCESS=0
SYNC_FAILED=0
while IFS=$'\t' read -r USERNAME ENCRYPTED_PASS; do
if [ -z "${USERNAME}" ] || [ -z "${ENCRYPTED_PASS}" ]; then
continue
fi
echo "[$(date '+%Y-%m-%d %H:%M:%S')] 同步用户：${USERNAME}" >> "${LOG_FILE}"
# 同步用户到ProxySQL
mysql -u"${PROXYSQL_ADMIN_USER}" -p"${PROXYSQL_ADMIN_PASS}" -h"${PROXYSQL_ADMIN_IP}" -P"${PROXYSQL_ADMIN_PORT}" \
-e "REPLACE INTO mysql_users (
username, password, default_hostgroup, active, max_connections
) VALUES (
'${USERNAME}', '${ENCRYPTED_PASS}', ${PROXYSQL_DEFAULT_HG}, 1, 1000
);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;"
if [ $? -eq 0 ]; then
echo "[$(date '+%Y-%m-%d %H:%M:%S')] 成功同步：${USERNAME}" >> "${LOG_FILE}"
SYNC_SUCCESS=$((SYNC_SUCCESS + 1))
else
echo "[$(date '+%Y-%m-%d %H:%M:%S')] 同步失败：${USERNAME}" >> "${LOG_FILE}"
SYNC_FAILED=$((SYNC_FAILED + 1))
fi
done <<< "${REMOTE_USERS}"
##############################################################################
# 5. 同步结果汇总
##############################################################################
echo "[$(date '+%Y-%m-%d %H:%M:%S')] 同步结束 | 成功：${SYNC_SUCCESS} |
失败：${SYNC_FAILED} | 来源节点：${SUCCESS_NODE}" >> "${LOG_FILE}"
echo "[$(date '+%Y-%m-%d %H:%M:%S')]
--------------------------------------------------------" >> "${LOG_FILE}"
2.3 service
Java 客户端通过一个 ClusterIP Service 访问 3 个 ProxySQL Pod，Service 默认是随机负载均衡（round-robin 或 random），导致同一个 Java Connection 的多次请求可能被转发到不同的 ProxySQL Pod。这会直接导致 session_tracked_gtid（会话级最近写 GTID）丢失，从而部分丧失因果一致性读的能力。
为什么会丢失？
session_tracked_gtid 是 ProxySQL 内存中每个 session 的私有状态。
每个 ProxySQL Pod 的内存独立。
Java 的同一个 JDBC Connection（TCP 连接）第一次请求去了 Pod A → Pod A 记录了“这个 session 最近写 GTID=1000”。
下一次请求被 Service 负载均衡到 Pod B → Pod B 完全不知道这个 session 之前写过什么 → 认为没有写记录 → 读直接发从库 → 可能读不到刚写的最新数据。
k8s Service 加 sessionAffinity（会话粘性）让 Service 把同一个 TCP 连接的所有请求固定发到同一个 ProxySQL Pod。改完后，Java 客户端通过 Service 访问，写后读一致性就稳了，不会因为负载均衡丢失 session 信息。
kind: Service
apiVersion: v1
metadata:
name: proxysql
namespace: bx
labels:
app: proxysql
annotations:
kubesphere.io/creator: admin
spec:
ports:
- name: http-6032
protocol: TCP
port: 6032
targetPort: 6032
- name: http-6033
protocol: TCP
port: 6033
targetPort: 6033
selector:
app: proxysql
type: ClusterIP
sessionAffinity: ClientIP
sessionAffinityConfig:
clientIP:
timeoutSeconds: 10800
ipFamilies:
- IPv4
ipFamilyPolicy: SingleStack
internalTrafficPolicy: Cluster
3. rpm 部署
前置详细解释一下：
ProxySQL 有两个主要端口：
管理端口（默认 6032）：只提供管理功能，用于 ProxySQL 的配置、用户同步、查看状态等。普通数据库客户端不能用这个端口登录查询数据库。
前端端口（默认 6033）：提供 MySQL 协议服务，真正接受客户端连接（Navicat、应用、程序），会把请求转发到后端数据库节点。
后续如果你用了 6032 来尝试连接那是 Admin 端口，所以 Navicat 无法执行 SQL 查询。改成 6033，就接入了 ProxySQL 的前端服务，代理到 PolarDB-X 后端节点，登录就成功了。
3.1 下载安装
# rpm下载本地
wget https://github.com/sysown/proxysql/releases/download/v2.4.8/proxysql-2.4.8-1-centos7.x86_64.rpm
# 本地安装, rpm 或者 yum安装
rpm -ivh proxysql-2.4.8-1-centos7.x86_64.rpm --nodeps
yum install -y proxysql-2.4.8-1-centos7.x86_64.rpm
# 启动ProxySQL
systemctl enable --now proxysql
# 检查ProxySQL是否启动成功
[root@localhost ~]# systemctl status proxysql
● proxysql.service - High Performance Advanced Proxy for MySQL
Loaded: loaded (/etc/systemd/system/proxysql.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2025-09-05 11:13:55 CST; 7s ago
Process: 16537 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS)
Main PID: 16539 (proxysql)
CGroup: /system.slice/proxysql.service
├─16539 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
└─16540 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
Sep 05 11:13:55 localhost.localdomain systemd[1]: Starting High Performance Advanced Proxy for MySQL...
Sep 05 11:13:55 localhost.localdomain proxysql[16537]: 2025-09-05 11:13:55 [INFO] Using config file /etc/proxysql.cnf
Sep 05 11:13:55 localhost.localdomain proxysql[16537]: 2025-09-05 11:13:55 [INFO] Current RLIMIT_NOFILE: 102400
Sep 05 11:13:55 localhost.localdomain proxysql[16537]: 2025-09-05 11:13:55 [INFO] Using OpenSSL version: OpenSSL 3.0.8 7 Feb 2023
Sep 05 11:13:55 localhost.localdomain proxysql[16537]: 2025-09-05 11:13:55 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating n...ificates.
Sep 05 11:13:55 localhost.localdomain systemd[1]: Started High Performance Advanced Proxy for MySQL.
Hint: Some lines were ellipsized, use -l to show in full.
3.2 登录ProxySQL
登录ProxySQL 检查确保mysql_servers、mysql_group_replication_hostgroups、mysql_query_rules为空，没有mysql命令可以直接执行yum install -y mysql下载客户端即可使用
[root@localhost ~]# mysql -uadmin -padmin -h 127.0.0.1 -P 6032 -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
MySQL [(none)]> select * from mysql_group_replication_hostgroups;
Empty set (0.00 sec)
MySQL [(none)]> select * from mysql_servers;
Empty set (0.00 sec)
MySQL [(none)]> select * from mysql_query_rules;
Empty set (0.01 sec)3.3 更新监控账号
注意：这⾥指定了账号为proxysql_monitor，密码为123456（对⻬配置数据库时的账号和密码）此处的proxysql_monitor与123456正是前置配置中的监控账号与密码，需要在polardb中预先执行创建出来。
UPDATE global_variables SET variable_value='proxysql_monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='123456' WHERE variable_name='mysql-monitor_password';3.4 添加测试账号
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('admin2','123456',10);
#检查
MySQL [(none)]> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| admin2 | 123456 | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | | |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
1 row in set (0.00 sec)3.5 设置读写组
设置读写组，写组10，备写组20，读组30，离线组40，主节点可作为读节点
# 设置读写组，写组10，备写组20，读组30，离线组40，主节点可作为读节点
INSERT INTO mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,writer_is_also_reader) VALUES(10,20,30,40,1,1);
# 检查
MySQL [(none)]> select * from mysql_group_replication_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 10 | 20 | 30 | 40 | 1 | 1 | 1 | 0 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)3.6 添加后端
注意：mysql_servers的信息，需要根据数据库部署的ip进⾏修改。添加后端mysql_servers，leader节点定义为写组10, follower节点定义为备写库20。 注意这⾥port为节点的PolarDB-X标准版的监听端⼝port
# 假设PolarDB-X部署的机器为172.31.0.41:4886、172.31.0.42:4886、172.31.0.43:4886
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'172.31.0.41',4886);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'172.31.0.42',4886);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'172.31.0.43',4886);
# 检查配置是否成功
MySQL [(none)]> select * from mysql_servers;
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.31.0.41 | 4886 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 172.31.0.42 | 4886 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 172.31.0.43 | 4886 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)3.7 配置读写分离规则
配置读写分离规则。对于SELECT FOR UPDATE配置到写库，纯SELECT配置到读库。
# 定义路由规则
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^select.*for update$',10,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^select',30,1);
# 检查配置是否成功
MySQL [(none)]> select * from mysql_query_rules;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | attributes | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
| 1 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | ^select.*for update$ | 0 | CASELESS | NULL | NULL | 10 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | | NULL |
| 2 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | ^select | 0 | CASELESS | NULL | NULL | 30 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | | NULL |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
2 rows in set (0.00 sec)3.8 保存配置并载⼊内存⽣效
save mysql users to disk;
save mysql servers to disk;
save mysql query rules to disk;
save mysql variables to disk;
save admin variables to disk;
load mysql users to runtime;
load mysql servers to runtime;
load mysql query rules to runtime;
load mysql variables to runtime;
load admin variables to runtime;
#检查
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.31.0.41 | 4886 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 30 | 172.31.0.41 | 4886 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 30 | 172.31.0.42 | 4886 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 30 | 172.31.0.43 | 4886 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)
3.9 登录相关账号
#proxysql监控登录账户
mysql -uadmin -padmin -h 127.0.0.1 -P 6032 -A
#proxysql测试登录账户
mysql -uadmin2 -p'123456' -h127.0.0.1 -P6033 -A3.10 常用命令
# 查看连接状态
SELECT * FROM stats_mysql_connection_pool;4. 高级用法
它非常重要，因为用于将 PolarDB 用户定时同步至 ProxySQL，否则在 PolarDB 中创建的新用户在 ProxySQL 中无法连接。通过定时任务，每 10 秒执行一次同步，将 PolarDB 用户更新到 ProxySQL。同步脚本 sync_user.sh 已放置在 ConfigMap 中，需要根据实际环境调整其中的连接信息、账号和密码。
4.1 定时任务
scheduler=
(
{
id=1
active=1
interval_ms=10000
filename="/opt/proxysql_scripts/sync_user.sh"
arg1="0"
arg2="0"
arg3="0"
arg4="0"
arg5="0"
}
)4.2 mysql-binlog-reader
https://proxysql.com/documentation/mysql-binlog-reader/
需要创建一个新的账号来读取gtid信息
CREATE USER 'proxysql_binlog_reader'@'%' IDENTIFIED BY '???';
GRANT REPLICATION CLIENT ON *.* TO 'proxysql_binlog_reader'@'%';
GRANT REPLICATION SLAVE ON *.* TO 'proxysql_binlog_reader'@'%';
FLUSH PRIVILEGES;4.2.1 deployment
注意因为我的数据库是部署在worker节点中的，所以我给这些节点打上了polardbx-engine: 'true' 标签，让mysql-binlog-reader仅运行在数据库节点，并且直接使用hostNetwork注解网络这样容器连接数据库就可以直接使用127.0.0.1了，还需要给这个部署配置上容器反亲和让它在每个节点仅运行一个即可。
参数args部分需要自行变更为自己环境的，实际直接使用root账号还是太大了，可以配置一个小权限的账号比较合适。
对于开头红字的描述，因为官方本身不支持arm64并且它的源码也非常难以构建，我们可以采用binfmt来提供能力，让它能够在arm64中运行x86的镜像，具体流程参考如下
kind: Deployment
apiVersion: apps/v1
metadata:
name: proxysql-binlog-reader
namespace: bx
annotations:
deployment.kubernetes.io/revision: '2'
kubesphere.io/creator: admin
kubesphere.io/description: 使用binfmt转译技术运行
spec:
replicas: 3
selector:
matchLabels:
app: proxysql-binlog-reader
template:
metadata:
creationTimestamp: null
labels:
app: proxysql-binlog-reader
annotations:
kubesphere.io/creator: admin
spec:
volumes:
- name: host-time
hostPath:
path: /etc/localtime
type: ''
containers:
- name: proxysql-binlog-reader
image: 'proxysql/proxysql-mysqlbinlog:latest'
command:
- proxysql_binlog_reader
args:
- '-h'
- 127.0.0.1
- '-u'
- proxysql_binlog_reader
- '-p'
- 123456
- '-P'
- '4886'
- '-l'
- '4040'
- '-f'
ports:
- name: http-0
containerPort: 4040
protocol: TCP
resources: {}
volumeMounts:
- name: host-time
readOnly: true
mountPath: /etc/localtime
terminationMessagePath: /dev/termination-log
terminationMessagePolicy: File
imagePullPolicy: IfNotPresent
restartPolicy: Always
terminationGracePeriodSeconds: 30
dnsPolicy: ClusterFirst
nodeSelector:
polardbx-engine: 'true'
hostNetwork: true
securityContext: {}
affinity:
podAntiAffinity:
preferredDuringSchedulingIgnoredDuringExecution:
- weight: 100
podAffinityTerm:
labelSelector:
matchExpressions:
- key: app
operator: In
values:
- proxysql-binlog-reader
topologyKey: kubernetes.io/hostname
schedulerName: default-scheduler
strategy:
type: Recreate
revisionHistoryLimit: 10
progressDeadlineSeconds: 600
4.2.2 proxysql.cnf
我们回到 proxysql.cnf 配置文件，在 mysql_servers 配置段中，为每个节点补充配置 gtid_port，此处我统一设置为 4040。
其中 4040 端口需与 mysql-binlog-reader 组件中所配置的监听端口保持一致，该端口可自定义但必须两端保持一致。
mysql_servers =
(
{ address="172.31.0.31" , port=4886 , hostgroup=10, max_connections=2000, gtid_port=4040 },
{ address="172.31.0.32" , port=4886 , hostgroup=20, max_connections=2000, gtid_port=4040 },
{ address="172.31.0.33" , port=4886 , hostgroup=20, max_connections=2000, gtid_port=4040 },
)3.2.3 最终效果
回到proxysql中执行命令查询 SELECT * FROM stats_mysql_gtid_executed;
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3920
Server version: 8.0.25 (ProxySQL Admin Module)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT * FROM stats_mysql_gtid_executed;
+---------------+------+------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| hostname | port | gtid_executed | events |
+---------------+------+------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| 172.31.0.33 | 4886 | 08d7a22f-8c91-11f0-adbd-fa163ef27350:1-112389253,814619a4-8c92-11f0-9aaf-fa163e1f619b:1-213507,56413a31-8c90-11f0-a047-fa163e46c759:1-83501934 | 725 |
| 172.31.0.31 | 4886 | 08d7a22f-8c91-11f0-adbd-fa163ef27350:1-112389253,814619a4-8c92-11f0-9aaf-fa163e1f619b:1-213507,56413a31-8c90-11f0-a047-fa163e46c759:1-83501896 | 725 |
| 172.31.0.32 | 4886 | 08d7a22f-8c91-11f0-adbd-fa163ef27350:1-112389253,814619a4-8c92-11f0-9aaf-fa163e1f619b:1-213507,56413a31-8c90-11f0-a047-fa163e46c759:1-83501911 | 725 |
+---------------+------+------------------------------------------------------------------------------------------------------------------------------------------------+--------+
3 rows in set (0.01 sec)
mysql>
5. 优秀帖子
https://proxysql.com/documentation/query-rewrite/
https://proxysql.com/documentation/scheduler/?highlight=schedu
https://www.percona.com/blog/configuring-proxysql-binlog-reader/
写在最后
在配置读写组（写组、读组、备写组、离线组）时，目前的流程相对抽象，需要手动指定主节点、备写节点以及读节点的 hostgroup。
使用 K8S 或 Docker 快速部署 ProxySQL
https://tanqidi.com/archives/18da8fed-bacd-44ee-91df-68014573c28b-889d1fab
评论