ProxySQL构建PolarDB-X标准版高可用路由服务三节点集群
ProxySQL构建PolarDB-X标准版高可用路由服务三节点集群
一、PolarDB-X标准版主备集群搭建
三台机器上传 polardbx 包，包可以从官网https://openpolardb.com/download获取，这里提供离线rpm。
1、上传 polardbx 安装包 到 /opt目录下
rpm -ivh t-polardbx-engine-8.4.19-20241112.el7.x86_64.rpm
会出现 ：
(base) [root@cdh-alpha opt]# ll polardbx_engine/
total 708
drwxr-xr-x 2 root root 4096 Feb 11 13:56 bin
drwxr-xr-x 2 root root 107 Feb 11 13:56 docs
drwxr-xr-x 3 root root 4096 Feb 11 13:56 include
drwxr-xr-x 6 root root 247 Feb 11 13:56 lib
-rwxr-xr-x 1 root root 283374 Feb 11 13:56 LICENSE
-rwxr-xr-x 1 root root 121462 Feb 11 13:56 LICENSE.router
-rwxr-xr-x 1 root root 283374 Feb 11 13:56 LICENSE-test
drwxr-xr-x 4 root root 42 Feb 11 13:56 man
-rwxr-xr-x 1 root root 1622 Feb 11 13:56 mysqlrouter-log-rotate
-rwxr-xr-x 1 root root 952 Feb 11 13:56 README
-rwxr-xr-x 1 root root 679 Feb 11 13:56 README.router
-rwxr-xr-x 1 root root 952 Feb 11 13:56 README-test
drwxr-xr-x 2 root root 10 Feb 11 13:56 run
drwxr-xr-x 28 root root 4096 Feb 11 13:56 share
drwxr-xr-x 2 root root 93 Feb 11 13:56 support-files
drwxr-xr-x 3 root root 25 Feb 11 13:56 var
(base) [root@cdh-alpha opt]#
2、创建 polarx 用户
（您也可以使用其他非 root 用户），准备一份 my.cnf（参考模板）和数据目录（如果改了 my.cnf，则下面的目录也要相应修改）
创建并切换到 polarx 用户
useradd -ms /bin/bash polarx
echo "polarx:polarx" | chpasswd
echo "polarx ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers
su - polarx
创建必要目录
mkdir polardbx-engine
cd polardbx-engine && mkdir log mysql run data tmp
准备一份 my.cnf 文件，可以参考本文末尾的模板，放置于当前目录，my.cnf 参考模板，请根据实际情况修改参数，仅验证功能和测试，更多参数可参考完整参数模板。附参考：
vi my.cnf
###### my.cnf 模板
[mysqld]
basedir = /opt/polardbx_engine
log_error_verbosity = 2
default_authentication_plugin = mysql_native_password
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
# 忽略大小写
lower_case_table_names=1
# 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
port = 4886
cluster_id = 1
cluster_info = 10.10.6.47:14886@1,10.10.6.48:14886@2,10.10.6.49:14886@3
server_id = 123
[mysqld_safe]
pid_file = /home/polarx/polardbx-engine/run/mysql.pid
###### my.cnf 模板
注意：这里讲集群模式的my.cnf 配置， 如果以三副本模式运行，my.cnf 中的 server_id 参数在三个副本节点需要配置为不同的值
3、集群初始化、启动
在 3 台机器上，按前述步骤，安装 RPM 后，准备好 my.cnf 和目录 （如果有任何步骤失败，请完全清理 log mysql run data tmp 等目录，重新创建。）。 然后在 3 个机器上，分别按如下方式启动： 如果 my.cnf 不在当前目录，请将下述命令的 my.cnf 改成绝对路径
10.10.6.47初始化：
/opt/polardbx_engine/bin/mysqld --defaults-file=my.cnf --cluster-info='10.10.6.47:14886;10.10.6.48:14886;10.10.6.49:14886@1' --initialize-insecure
启动
/opt/polardbx_engine/bin/mysqld_safe --defaults-file=my.cnf --cluster-info='10.10.6.47:14886;10.10.6.48:14886;10.10.6.49:14886@1' &
10.10.6.48初始化：
/opt/polardbx_engine/bin/mysqld --defaults-file=my.cnf --cluster-info='10.10.6.47:14886;10.10.6.48:14886;10.10.6.49:14886@2' --initialize-insecure
启动
/opt/polardbx_engine/bin/mysqld_safe --defaults-file=my.cnf --cluster-info='10.10.6.47:14886;10.10.6.48:14886;10.10.6.49:14886@2' &
10.10.6.49初始化：
/opt/polardbx_engine/bin/mysqld --defaults-file=my.cnf --cluster-info='10.10.6.47:14886;10.10.6.48:14886;10.10.6.49:14886@3' --initialize-insecure
启动
/opt/polardbx_engine/bin/mysqld_safe --defaults-file=my.cnf --cluster-info='10.10.6.47:14886;10.10.6.48:14886;10.10.6.49:14886@3' &
稍等片刻，即可登录三台数据库。如果直接使用本文的 my.cnf 模板，可以用mysql -h127.0.0.1 -P4886 -uroot 登录数据库。 （需要预先在机器上安装 mysql 客户端，centos 系统可以使用 yum install mysql 安装。）
[polarx@cdh-alpha polardbx-engine]$ mysql -h127.0.0.1 -P4886 -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 990
Server version: 8.0.32-X-Cluster-8.4.19-20241112 Source distribution
Copyright (c) 2000, 2022, 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>
4、验证集群状态
数据库启动完成后，我们登录数据库，验证一下集群的状态。（需要在 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.10.6.47:14886 | 18 | 19 | Follower | Yes | No | 5 | 0 | 18 | Yes | No |
| 2 | 10.10.6.48:14886 | 18 | 19 | Follower | Yes | No | 5 | 0 | 18 | Yes | No |
| 3 | 10.10.6.49:14886 | 18 | 0 | Leader | Yes | No | 5 | 0 | 17 | No | No |
+-----------+------------------+-------------+------------+----------+-----------+------------+-----------------+----------------+---------------+------------+--------------+
3 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM INFORMATION_SCHEMA.ALISQL_CLUSTER_LOCAL \G
*************************** 1. row ***************************
SERVER_ID: 3
CURRENT_TERM: 41
CURRENT_LEADER: 10.10.6.49:14886
COMMIT_INDEX: 18
LAST_LOG_TERM: 41
LAST_LOG_INDEX: 18
ROLE: Leader
VOTED_FOR: 3
LAST_APPLY_INDEX: 17
SERVER_READY_FOR_RW: Yes
INSTANCE_TYPE: Normal
1 row in set (0.00 sec)
mysql>
5、数据库操作
数据插入测试：
CREATE DATABASE db1;
USE db1;
CREATE TABLE tb1 (id int);
INSERT INTO tb1 VALUES (0), (1), (2);
MySQL [db1]> select * from tb1;
+------+
| id |
+------+
| 0 |
| 1 |
| 2 |
+------+
3 rows in set (0.001 sec)
至此，三台主备集群搭建完成，可自行kill主，查看切换主备集群自动切换，接下来搭建 高可用路由配置 ：
======================================================================================================
======================================================================================================
二、ProxySQL构建PolarDB-X集群高可用
1、配置PolarDB-X标准版
创建依赖视图，目的让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运行通用依赖
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.10.6.47:14886 | 18 | 19 | Follower | Yes | No | 5 | 0 | 18 | Yes | No |
| 2 | 10.10.6.48:14886 | 18 | 19 | Follower | Yes | No | 5 | 0 | 18 | Yes | No |
| 3 | 10.10.6.49:14886 | 18 | 0 | Leader | Yes | No | 5 | 0 | 17 | No | No |
+-----------+------------------+-------------+------------+----------+-----------+------------+-----------------+----------------+---------------+------------+--------------+
3 rows in set (0.00 sec)
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)
2、ProxySQL部署
1、依赖包
选择一台性能高机器，依赖包获取官网：https://github.com/sysown/proxysql/releases/tag/v2.4.8 ，本文提供离线rpm包：
wget https://github.com/sysown/proxysql/releases/download/v2.4.8/proxysql-2.4.8-1-centos7.x86_64.rpm
(base) [root@cdh-alpha opt]# ll proxysql-2.4.8-1-centos7.x86_64.rpm
-rw-rw-rw- 1 root root 16422372 Feb 15 2023 proxysql-2.4.8-1-centos7.x86_64.rpm
(base) [root@cdh-alpha opt]#
本地安装
sudo rpm -ivh proxysql-2.4.8-1-centos7.x86_64.rpm --nodeps
启动ProxySQL
(base) [root@cdh-alpha opt]# sudo systemctl start proxysql
(base) [root@cdh-alpha 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 cdh-alpha systemd[1]: Starting High Performance Advanced Proxy for MySQL...
Feb 13 18:50:39 cdh-alpha proxysql[23894]: 2025-02-13 18:50:39 [INFO] Using config file /etc/proxysql.cnf
Feb 13 18:50:39 cdh-alpha proxysql[23894]: 2025-02-13 18:50:39 [INFO] Current RLIMIT_NOFILE: 102400
Feb 13 18:50:39 cdh-alpha proxysql[23894]: 2025-02-13 18:50:39 [INFO] Using OpenSSL version: OpenSSL 3.0.8 7 Feb 2023
Feb 13 18:50:39 cdh-alpha 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 cdh-alpha systemd[1]: Started High Performance Advanced Proxy for MySQL.
You have mail in /var/spool/mail/root
2、配置
- 登陆账户
mysql -uadmin -padmin -h 127.0.0.1 -P 6032 -A
- 检查确保mysql_servers、mysql_group_replication_hostgroups、mysql_query_rules为空
#检查
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)
- 更新监控账号
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)
- 设置读写组，写组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.10.6.49',4886);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'10.10.6.47',4886);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'10.10.6.48',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.10.6.49 | 4886 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 10.10.6.47 | 4886 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 10.10.6.48 | 4886 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
- 配置读写分离规则。对于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 |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
| 9 | 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 |
| 10 | 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;
检查：
mysql> 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 | 10.10.6.49 | 4886 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 30 | 10.10.6.47 | 4886 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 30 | 10.10.6.48 | 4886 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 30 | 10.10.6.49 | 4886 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.01 sec)
配置完成。
- 高可用路由验证： 相关登陆账户
#proxysql监控登录账户
mysql -uadmin -padmin -h 127.0.0.1 -P 6032 -A
#proxysql测试登录账户
mysql -uadmin2 -p'123456' -h127.0.0.1 -P6033 -A
- 本文安装在 10.10.6.49， 测试登录账号进行验证：
create database d1;
create table d1.t1(c1 int, c2 varchar(10));
insert into d1.t1 values(1,"hello");
begin;insert into d1.t1 values(2,"world");select * from d1.t1;commit;
select * from d1.t1;
select * from d1.t1 for update;
有客户端，可使用数据库连接工具连接 10.10.6.49 6033 admin2 123456 ，创建库表，增删改查，验证
并对主节点 kill，验证其高可用路由；
=========================================================================================================