写在最前

前置配置

配置数据库

创建依赖视图，⽬的让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="10.133.179.71" , port=4886 , hostgroup=10, max_connections=1000 },
       { address="10.133.179.72" , port=4886 , hostgroup=20, max_connections=1000 },
       { address="10.133.179.73" , 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=5
          active=1
          match_pattern="^SELECT"
          destination_hostgroup=30
          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=(
        "10.133.179.71:4886"   # 节点1：IP:端口
        "10.133.179.72:4886"   # 节点2：IP:端口
        "10.133.179.73: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

当前环境仅用于测试，可通过 NodePort 访问服务；生产环境请严格禁止使用 NodePort，以确保安全性和流量管控。

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
      nodePort: 31222
    - name: http-6033
      protocol: TCP
      port: 6033
      targetPort: 6033
      nodePort: 32498
  selector:
    app: proxysql
  type: NodePort
  sessionAffinity: None
  externalTrafficPolicy: Cluster
  ipFamilies:
    - IPv4
  ipFamilyPolicy: SingleStack
  internalTrafficPolicy: Cluster

3. rpm 部署

前置详细解释一下：

• ProxySQL 有两个主要端口：

  1. 管理端口（默认 6032）：只提供管理功能，用于 ProxySQL 的配置、用户同步、查看状态等。普通数据库客户端不能用这个端口登录查询数据库。

  2. 前端端口（默认 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 -A

3.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"
  }
)

5. 优秀帖子

https://proxysql.com/documentation/query-rewrite/

https://proxysql.com/documentation/scheduler/?highlight=schedu

写在最后

在配置读写组（写组、读组、备写组、离线组）时，目前的流程相对抽象，需要手动指定主节点、备写节点以及读节点的 hostgroup。