前言
ProxySQL作为一款高性能的MySQL中间件代理，是构建高可用、高性能MySQL集群架构的关键组件。
关于为什么要用ProxySQL构建PolarDB-X上层服务？
- 读写分离自动化：ProxySQL能根据SQL语句类型（如
SELECT、INSERT、UPDATE）自动区分读写请求，将写操作定向至主库，读操作分发到从库； - 高可用保障：实时健康检测-ProxySQL持续监控PolarDB-X各个节点的状态；
- 负载均衡化：支持基于连接数、权重、响应时间多种负载均衡算法；路由配置通过表数据操作就可完成，可以清晰看见路由链路。
除开以上能力外，最重要的一点是可以统一开放IP，在企业内网中网络开放申请一般针对某个IP和端口。ProxySQL能提供一个IP和端口供应用层系统使用，避免了由应用端去管理节点切换。
PolarDB-X标准版三节点集群搭建
详情可参考PolarDB开源社区官方操作文档，PolarDB-X支持多种形态的快速部署能力，可以结合各自需求选择，本文直接通过开源官网下载RPM包进行安装。
环境准备与依赖项
- 硬件/操作系统要求（CentOS 7+/Ubuntu 18.04等） - 网络规划（私有化部署IP分配、防火墙策略） - 三台机器： --10.xxx.xxx.82; --10.xxx.xxx.83; --10.xxx.xxx.84；
部署PolarDB-X标准版集群
1、PolarDB-X标准版RPM安装包，可以手动编译生成或从PolarDB开源官网下载，目前最新为v2.4.2。本人已经测试该版本与本文使用的ProxySQL版本兼容性，目前生产稳定运行中。
2、将下载的RPM包分别上传到三台服务器上并进行安装：
[root@10-xxx-xxx-82 opt]# rpm -ivh t-polardbx-engine-8.4.19-20241112.el7.x86_64.rpm 完成后的目录是这样的 [root@10-xxx-xxx-82 polardbx_engine]# ll 总用量 704 drwxr-xr-x 2 root root 4096 8月 25 22:17 bin drwxr-xr-x 2 root root 87 8月 25 22:17 docs drwxr-xr-x 3 root root 282 8月 25 22:17 include drwxr-xr-x 6 root root 207 8月 25 22:17 lib -rwxr-xr-x 1 root root 283374 8月 25 22:17 LICENSE -rwxr-xr-x 1 root root 121462 8月 25 22:17 LICENSE.router -rwxr-xr-x 1 root root 283374 8月 25 22:17 LICENSE-test drwxr-xr-x 4 root root 30 8月 25 22:17 man -rwxr-xr-x 1 root root 1622 8月 25 22:17 mysqlrouter-log-rotate -rwxr-xr-x 1 root root 952 8月 25 22:17 README -rwxr-xr-x 1 root root 679 8月 25 22:17 README.router -rwxr-xr-x 1 root root 952 8月 25 22:17 README-test drwxr-xr-x 2 root root 6 8月 25 22:17 run drwxr-xr-x 28 root root 4096 8月 25 22:17 share drwxr-xr-x 2 root root 77 8月 25 22:17 support-files drwxr-xr-x 3 root root 17 8月 25 22:17 var
3、创建polarx用户并切换
useradd -ms /bin/bash polarx echo "polarx:polarx" | chpasswd echo "polarx ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers su - polarx
4、创建安装目录
mkdir polardbx-engine cd polardbx-engine && mkdir log mysql run data tmp
5、在polardbx-engine目录下准备my.cnf配置文件
vim my.cnf
贴入模版内容，根据具体情况修改相应配置，polardb-x和mysql配置基本一致
[mysqld] basedir = /opt/polardbx_engine log_error_verbosity = 2 gtid_mode = ON enforce_gtid_consistency = ON log_bin = mysql-binlog binlog_format = row binlog_row_image = FULL master_info_repository = TABLE relay_log_info_repository = TABLE # change me if needed datadir = /home/polarx/polardbx-engine/data tmpdir = /home/polarx/polardbx-engine/tmp socket = /home/polarx/polardbx-engine/tmp.mysql.sock log_error = /home/polarx/polardbx-engine/log/alert.log bind-address = 0.0.0.0 port = 4886 cluster_id = 1 cluster_info = 10.xxx.xxx.82:14886;10.xxx.xxx.83:14886;10.xxx.xxx.84:14886@1 server_id = 1 [mysqld_safe] pid_file = /home/polarx/polardbx-engine/run/mysql.pid
注意：这里讲集群模式的my.cnf 配置， 以三副本模式运行，my.cnf 中的
server_id 参数在三个副本节点需要配置为不同的值;
cluster_id是用于标识一个PolarDB-X集群的唯一ID；
cluster_info参数用于指定集群的元数据信息，包括集群中所有节点的地址和端口,指定PolarDB-X集群中所有节点的信息，用于节点间的通信和元数据管理；
其他信息可自行查阅官方文档
查看polardbx-engine目录下内容：
[polarx@10-xxx-xxx-82 ~]$ cd polardbx-engine/ [polarx@10-xxx-xxx-82 polardbx-engine]$ ll 总用量 12 drwxr-xr-x 22 polarx polarx 4096 10月 14 15:55 data drwxr-x--- 2 polarx polarx 50 8月 26 14:48 log -rw-rw-r-- 1 polarx polarx 665 8月 26 15:33 my.cnf drwxrwxr-x 2 polarx polarx 6 8月 25 22:13 mysql drwxrwxr-x 2 polarx polarx 23 8月 26 15:33 run drwxrwxr-x 2 polarx polarx 6 8月 26 15:33 tmp
7、集群初始化
按前述步骤，安装 RPM 后，准备好 my.cnf 和目录 （如果有任何步骤失败，请完全清理 log mysql run data tmp 等目录，重新创建。）。 然后在 3 个机器上，分别按如下方式启动：
# 10.xxx.xxx.82上执行 /opt/polardbx_engine/bin/mysqld --defaults-file=my.cnf \ --cluster-info='10.xxx.xxx.82:14886;10.xxx.xxx.83:14886;10.xxx.xxx.84:14886@1' \ --initialize-insecure /opt/polardbx_engine/bin/mysqld_safe --defaults-file=my.cnf \ --cluster-info='10.xxx.xxx.82:14886;10.xxx.xxx.83:14886;10.xxx.xxx.84:14886@1' \ & # 10.xxx.xxx.83上执行 /opt/polardbx_engine/bin/mysqld --defaults-file=my.cnf \ --cluster-info='10.xxx.xxx.82:14886;10.xxx.xxx.83:14886;10.xxx.xxx.84:14886@2' \ --initialize-insecure /opt/polardbx_engine/bin/mysqld_safe --defaults-file=my.cnf \ --cluster-info='10.xxx.xxx.82:14886;10.xxx.xxx.83:14886;10.xxx.xxx.84:14886@2' \ & # 10.xxx.xxx.84上执行 /opt/polardbx_engine/bin/mysqld --defaults-file=my.cnf \ --cluster-info='10.xxx.xxx.82:14886;10.xxx.xxx.83:14886;10.xxx.xxx.84:14886@3' \ --initialize-insecure /opt/polardbx_engine/bin/mysqld_safe --defaults-file=my.cnf \ --cluster-info='10.xxx.xxx.82:14886;10.xxx.xxx.83:14886;10.xxx.xxx.84:14886@3' \ &
注意到，我们在启动时修改了 cluster-info 的配置项，其中的格式为 [host1]:[port1];[host2]:[port2];[host3]:[port3]@[idx] ，不同的机器，只有 [idx] 不同，[idx] 也反映了该机器是第几个 [host][port]。请根据实际机器的 ip 修改该配置项。
另外，如果要让 PolarDB-X 的副本启动为 Logger 模式（Logger 会占用更少的资源，推荐让其中一个副本配置为 Logger），需要设置cluster-log-type-node=ON。Logger为节省成本而设计的副本类型，仅提供多数派协议服务，不提供数据服务。
# 比如我们把第三个主机，配置为logger模式 /opt/polardbx_engine/bin/mysqld_safe --defaults-file=my.cnf \ cluster-log-type-node=ON \ --cluster-info='10.xxx.xxx.82:14886;10.xxx.xxx.83:14886;10.xxx.xxx.84:14886@3' \ & # 如果配置了Logger节点，则该节点无法提供数据服务，只会存储日志，本案采用的是3个数据副本启动
启动后可进入log目录查看alert.log日志
[polarx@10-xxx-xxx-82 polardbx-engine]$ tail -f ./log/alert.log
8、验证安装
登录三台数据库。如果直接使用本文的 my.cnf 模板，可以用mysql -h127.0.0.1 -P4886 -uroot 登录数据库。 （需要预先在机器上安装 mysql 客户端，centos 系统可以使用 yum install mysql 安装。）
[polarx@10-xxx-xxx-82 polardbx-engine]$ mysql -h 127.0.0.1 -P 4886 -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 72906 Server version: 8.0.32-X-Cluster-8.4.19-20241112 Source distribution Copyright (c) 2000, 2025, 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>
分别登录每个数据库副本，执行下面的命令，可以看到每个副本的角色（Leader，Follower，Logger）。
mysql> SELECT * FROM INFORMATION_SCHEMA.ALISQL_CLUSTER_LOCAL \G *************************** 1. row *************************** SERVER_ID: 1 CURRENT_TERM: 69 CURRENT_LEADER: 10.xxx.xxx.82:14886 COMMIT_INDEX: 22100326 LAST_LOG_TERM: 69 LAST_LOG_INDEX: 22100326 ROLE: Leader VOTED_FOR: 1 LAST_APPLY_INDEX: 22100326 SERVER_READY_FOR_RW: Yes INSTANCE_TYPE: Normal 1 row in set (0.00 sec) # 在 Leader 节点上执行下面的命令，可以看到整个集群的状态。 mysql> SELECT * FROM INFORMATION_SCHEMA.ALISQL_CLUSTER_GLOBAL; +-----------+--------------------+-------------+------------+----------+-----------+------------+-----------------+----------------+---------------+------------+--------------+ | SERVER_ID | IP_PORT | MATCH_INDEX | NEXT_INDEX | ROLE | HAS_VOTED | FORCE_SYNC | ELECTION_WEIGHT | LEARNER_SOURCE | APPLIED_INDEX | PIPELINING | SEND_APPLIED | +-----------+--------------------+-------------+------------+----------+-----------+------------+-----------------+----------------+---------------+------------+--------------+ | 1 | 10.xxx.xxx.82:14886 | 22100388 | 0 | Leader | Yes | No | 5 | 0 | 22100388 | No | No | | 2 | 10.xxx.xxx.83:14886 | 22100388 | 22100389 | Follower | Yes | No | 5 | 0 | 208402 | Yes | No | | 3 | 10.xxx.xxx.84:14886 | 22100387 | 22100389 | Follower | No | No | 5 | 0 | 22100385 | Yes | No | +-----------+--------------------+-------------+------------+----------+-----------+------------+-----------------+----------------+---------------+------------+--------------+ 3 rows in set (0.00 sec) mysql>
到这里三节点的高可用集群就安装完毕了。
通过ProxySQL构建PolarDB-X高可用集群
ProxySQL执行图，6032是admin管理端口、6033是客户端应用端口（SQL操作）
ProxySQL部署
本文操作版本选择ProxySQLv2.4.8（兼容性已验证），安装方式（RPM包或源码编译）；
下载地址：https://github.com/sysown/proxysql/releases/tag/v2.4.8
1、在指定机器安装ProxySQL服务（本文档案例部署在10.xxx.xxx.82机器上）：
rpm -ivh proxysql-2.4.8-1-centos7.x86_64.rpm --nodeps
[root@10-xxx-xxx-82 opt]# sudo systemctl start proxysql [root@10-xxx-xxx-82 opt]# sudo 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 Thu 2025-02-13 18:50:39 CST; 17h ago Process: 23894 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS) Main PID: 23896 (proxysql) Tasks: 32 Memory: 31.0M CGroup: /system.slice/proxysql.service ├─23896 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf └─23897 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf Feb 13 18:50:39 10-xxx-xxx-82 systemd[1]: Starting High Performance Advanced Proxy for MySQL... Feb 13 18:50:39 10-xxx-xxx-82 proxysql[23894]: 2025-02-13 18:50:39 [INFO] Using config file /etc/proxysql.cnf Feb 13 18:50:39 10-xxx-xxx-82 proxysql[23894]: 2025-02-13 18:50:39 [INFO] Current RLIMIT_NOFILE: 102400 Feb 13 18:50:39 10-xxx-xxx-82 proxysql[23894]: 2025-02-13 18:50:39 [INFO] Using OpenSSL version: OpenSSL 3.0.8 7 Feb 2023 Feb 13 18:50:39 10-xxx-xxx-82 proxysql[23894]: 2025-02-13 18:50:39 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them. Feb 13 18:50:39 10-xxx-xxx-82 systemd[1]: Started High Performance Advanced Proxy for MySQL.
2、配置PolarDB-X集群（目的让ProxySQL识别PolarDB-X集群的leader\follower）
通过PolarDB集群的Leader节点创建依赖视图。（通过主节点创建视图后，follower节点会自动同步）
本案例中当前Leader节点为：10.xxx.xxx.82 机器
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; --注意如果有Logger节点服务，不能提供数据服务，该地方只录入了Leader和Follower节点
创建ProxySQL监控账户“proxysql_monitor”，ProxySQL运行通用依赖
create user 'proxysql_monitor'@'%' identified with mysql_native_password by '123456'; GRANT SELECT on sys.* to 'proxysql_monitor'@'%'; --注意这里的密码请修改为复核您自己要求的密码
创建一个测试用户
create user 'admin2'@'%' identified with mysql_native_password by '123456'; GRANT all privileges on *.* to 'admin2'@'%'; --注意这个用户也可以直接创建一个可使用的高权限用户，密码也需要调整
检查配置是否生效
mysql> select @@port; +--------+ | @@port | +--------+ | 4886 | +--------+ 1 row in set (0.00 sec) mysql> select * from information_schema.ALISQL_CLUSTER_GLOBAL; +-----------+------------------+-------------+------------+----------+-----------+------------+-----------------+----------------+---------------+------------+--------------+ | SERVER_ID | IP_PORT | MATCH_INDEX | NEXT_INDEX | ROLE | HAS_VOTED | FORCE_SYNC | ELECTION_WEIGHT | LEARNER_SOURCE | APPLIED_INDEX | PIPELINING | SEND_APPLIED | +-----------+------------------+-------------+------------+----------+-----------+------------+-----------------+----------------+---------------+------------+--------------+ | 1 | 10.xxx.xxx.84:14886 | 18 | 19 | Follower | Yes | No | 5 | 0 | 18 | Yes | No | | 2 | 10.xxx.xxx.83:14886 | 18 | 19 | Follower | Yes | No | 5 | 0 | 18 | Yes | No | | 3 | 10.xxx.xxx.82:14886 | 18 | 0 | Leader | Yes | No | 5 | 0 | 17 | No | No | +-----------+------------------+-------------+------------+----------+-----------+------------+-----------------+----------------+---------------+------------+--------------+ 3 rows in set (0.00 sec) --Logger节点不能提供数据服务 mysql> 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> select User,Host from mysql.user where User in ('admin2', 'proxysql_monitor'); +------------------+------+ | User | Host | +------------------+------+ | admin2 | % | | proxysql_monitor | % | +------------------+------+ 2 rows in set (0.00 sec)
3、后端节点注册与路由规则
连接ProxySQL服务管理端
mysql -uadmin -padmin -h 127.0.0.1 -P 6032 -A
检查需要配置的表单
#检查 mysql> select * from mysql_group_replication_hostgroups; Empty set (0.00 sec) mysql> select * from mysql_servers; Empty set (0.00 sec) mysql> select * from mysql_query_rules; Empty set (0.00 sec)
mysql_replication_hostgroups表中的记录包含了主机组的相关信息，包括主机组ID、主服务器和从服务器的配置、读写分离规则、复制延迟等。通过对该表的配置，可以定义并管理ProxySQL的主从复制配置，实现高可用和负载均衡的数据库访问。
mysql_servers表是ProxySQL中用于配置MySQL服务器信息的表。它存储了与每个MySQL服务器相关的详细信息，包括主机名、端口、用户名、密码、权重、状态等。
通过在mysql_servers表中添加或更新记录，你可以告诉ProxySQL如何连接到MySQL服务器。这些记录定义了ProxySQL与后端MySQL服务器之间的连接和负载均衡策略。每个记录代表一个MySQL服务器实例
mysql_query_rules表是ProxySQL中用于定义查询规则的表；
在ProxySQL中，查询规则决定了如何处理不同类型的查询语句。通过在mysql_query_rules表中定义查询规则，可以实现灵活的查询路由和行为控制；
将我们在前面创建的监控账号更新到proxySQL中
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'; #检查： mysql> select * from global_variables where variable_name in ('mysql-monitor_username', 'mysql-monitor_password'); +------------------------+------------------+ | variable_name | variable_value | +------------------------+------------------+ | mysql-monitor_password | 123456 | | mysql-monitor_username | proxysql_monitor | +------------------------+------------------+ 2 rows in set (0.00 sec)
添加测试账号
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('admin2','123456',10); #检查 mysql> 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)
mysql_users表是ProxySQL中的一个配置表，用于存储与MySQL用户相关的信息
在ProxySQL中，mysql_users表定义了在连接到MySQL服务器时使用的用户名和密码，以及该用户在ProxySQL中的权限和其他属性。通过在mysql_users表中配置用户信息，ProxySQL可以根据客户端的连接请求进行身份验证，并根据定义的权限规则控制对MySQL服务器的访问。
设置读写组，写组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> 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)
添加后端mysql_servers，leader节点定义为写组10, follower节点定义为备写库20。 注意这里port为节点的PolarDB-X标准版的监听端口port。
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'10.xxx.xxx.82',4886); INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'10.xxx.xxx.83',4886); INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'10.xxx.xxx.84',4886); mysql> 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 | 10.xxx.xxx.82 | 4886 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 10.xxx.xxx.83 | 4886 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 10.xxx.xxx.84 | 4886 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec) mysql>
配置读写分离规则。对于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> 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)
保存配置并载入内存生效
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;
利用远程连接工具验证是否正常：
因为本案例ProxySQL部署和PolarDB-X集群主节点部署在一台机器上，所以链接的IP地址为：10.xxx.xxx.82，端口为ProxySQL默认服务端口：6033
ProxySQL常见故障
可能会出现的问题
1、ProxySQL Error: connection is locked to hostgroup 2 but trying to reach hostgroup 1
解决方案：登上proxysql的管理端执行以下命令
set mysql-set_query_lock_on_hostgroup=0; #修改后，需要加载到RUNTIME，并保存到disk load mysql variables to runtime; save mysql variables to disk; # 也可以在安装后尚未启动时修改配置文件，增加上这个参数： mysql_variables= { ...... set_query_lock_on_hostgroup=0 }
2、java.sql.SQLException: Unknown system variable query_cache_size(记得修改proxysql内置mysql的版本号，登上proxysql的管理端执行以下命令)
update global_variables set variable_value="8.0.4" where variable_name='mysql-server_version'; #修改后，需要加载到RUNTIME，并保存到disk load mysql variables to runtime; save mysql variables to disk; mysql> select * from global_variables where variable_name='mysql-server_version'; +----------------------+------------------+ | variable_name | variable_value | +----------------------+------------------+ | mysql-server_version | 8.0.4 (ProxySQL) | +----------------------+------------------+ 1 row in set (0.00 sec) mysql>
