作者：严华
前言
PolarDB-X 作为PolarDB分布式版，是阿里巴巴自主设计研发的高性能云原生分布式数据库产品，采用 Shared-nothing 与存储分离计算架构，支持集中式和分布式一体化形态，具备金融级数据高可用、分布式水平扩展、混合负载、低成本存储和极致弹性等能力，坚定以兼容MySQL开源生态构建分布式能力，为用户提供高吞吐、大存储、低延时、易扩展和超高可用的云时代数据库服务。
2023年10月份，PolarDB-X 开源正式发布V2.3.0版本，重点推出PolarDB-X标准版（集中式形态），将PolarDB-X分布式中的DN节点提供单独服务。支持Paxos协议的多副本模式、lizard分布式事务引擎，采用一主一备一日志的三节点架构，通过Paxos协议多副本同步复制，确保数据的强一致性（RPO=0），可以100%兼容MySQL。
面对PolarDB-X标准版缺乏对应开源Proxy组件的现状，开发者们一直在寻找简便且高效的解决方案。好消息是，ProxySQL作为一款成熟的MySQL中间件，能够无缝对接MySQL协议支持PolarDB-X，并且支持故障切换，动态路由等高可用保障，为我们提供了一个既可用又好用的代理选项。
本文将指导如何快速搭建和配置PolarDB-X标准版与ProxySQL，并提供验证高可用路由服务验证测试。
手动部署
PolarDB-X标准版部署
安装PolarDB-X标准版
详情请参考文档《PolarDB-X 开源 | 基于Paxos的MySQL三副本》 PolarDB-X支持多种形态的快速部署能力，可以结合各自需求尽心选择
| 部署方式 | 说明 | 安装工具的快速安装 | 依赖项 |
| RPM包 | 零组件依赖，手工快速部署 | RPM下载、RPM安装 | rpm |
| PXD | 自研快速部署工具，通过yaml文件配置快速部署 | PXD安装 | python3、docker |
| K8S | 基于k8s operator的快速部署工具 | K8S安装 | k8s、docker |
配置PolarDB-X标准版
1.创建依赖视图，目的让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'@'%';
2.创建测试账户，下面高可用测试依赖
create user 'admin2'@'%' identified with mysql_native_password by '123456'; GRANT all privileges on *.* to 'admin2'@'%';
3.检查配置是否生效
# 本文PolarDB-X标准版的三节点均部署本地，监听端口分别为3301/3302/3303 mysql> select @@port; +--------+ | @@port | +--------+ | 3301 | +--------+ 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 | 127.0.0.1:16800 | 1 | 0 | Leader | Yes | No | 5 | 0 | 0 | No | No | | 2 | 127.0.0.1:16801 | 1 | 2 | Follower | Yes | No | 5 | 0 | 1 | Yes | No | | 3 | 127.0.0.1:16802 | 1 | 2 | Follower | Yes | No | 5 | 0 | 1 | Yes | 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.01 sec)
ProxySQL部署
安装ProxySQL
ProxySQL文档中有详细的安装教程可以浏览 《ProxygSQL：Introduction》 RPM方式和yum方式都可以安装的，但是当前仅测试验证ProxySQL版本v2.4.8可用（v2.5.0及最新版目前不支持，需要修改ProxySQL代码才能支持） 本文采用RPM方式安装，rpm可以从官网 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 # 本地安装 sudo rpm -ivh proxysql-2.4.8-1-centos7.x86_64.rpm --nodeps # 启动ProxySQL sudo systemctl start proxysql # 检查ProxySQL是否启动成功 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 Mon 2024-04-29 00:30:46 CST; 6s ago Process: 26825 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS) Main PID: 26828 (proxysql) Tasks: 25 Memory: 14.8M CGroup: /system.slice/proxysql.service ├─26828 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf └─26829 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf # 检查ProxySQL端口是否开放 # 6032 是ProxySQL管理端口号 # 6033 是ProxySQL对外服务端口号 # ProxySQL默认用户名和密码都是admin # ProxySQL默认日志为/var/lib/proxysql/proxysql.log # ProxySQL默认数据目录为/var/lib/proxysql/ sudo netstat -anlp | grep proxysql tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 26829/proxysql tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 26829/proxysql
配置ProxySQL
PolarDB-X标准版+ProxySQL时的ProxySQL的配置教程， 与MySQL MGR+ProxySQL时的ProxySQL的配置教程基本相同。
1.登陆账户
mysql -uadmin -padmin -h 127.0.0.1 -P 6032 -A
2.检查确保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)
3.更新监控账号
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)
4.添加测试账号
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)
5.设置读写组，写组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)
6.添加后端mysql_servers，leader节点定义为写组10, follower节点定义为备写库20。 注意这里port为节点的PolarDB-X标准版的监听端口port。
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'127.0.0.1',3301); INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'127.0.0.1',3302); INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'127.0.0.1',3303); #检查 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 | 127.0.0.1 | 3301 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 127.0.0.1 | 3303 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec)
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> 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)
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> 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 | 127.0.0.1 | 3301 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3301 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3303 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec)
高可用路由验证
相关登陆账户
#proxysql监控登录账户 mysql -uadmin -padmin -h 127.0.0.1 -P 6032 -A #proxysql测试登录账户 mysql -uadmin2 -p'123456' -h127.0.0.1 -P6033 -A
读写分离
#使用监控账户登录 #检查当前server状态，127.0.0.1:3301提供写库服务，127.0.0.1:3301、127.0.0.1:3301、127.0.0.1:3302、127.0.0.1:3303提供读库服务 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 | 127.0.0.1 | 3301 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3301 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3303 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec) #proxysql测试登录账户,执行下面sql 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; #使用监控账户登录 #检查实际路由规则 mysql> select hostgroup,digest_text from stats_mysql_query_digest; +-----------+------------------------------------------+ | hostgroup | digest_text | +-----------+------------------------------------------+ | 10 | select * from d1.t1 for update | | 30 | select * from d1.t1 | | 10 | insert into d1.t1 values(?,?) | | 10 | commit | | 10 | create table d1.t1(c1 int,c2 varchar(?)) | | 10 | create database d1 | | 10 | begin | | 10 | select @@version_comment limit ? | +-----------+------------------------------------------+ 8 rows in set (0.01 sec) #实际手动测试查看路由目的地,只读查询在读库的三个节点中随机选择，非只读查询只在写库中进行 $mysql -uadmin2 -p'123456' -h127.0.0.1 -P6033 -e 'select @@port, c1, c2 from d1.t1;' Warning: Using a password on the command line interface can be insecure. +--------+------+-------+ | @@port | c1 | c2 | +--------+------+-------+ | 3301 | 1 | hello | | 3301 | 2 | world | +--------+------+-------+ $mysql -uadmin2 -p'123456' -h127.0.0.1 -P6033 -e \ 'select @@port, c1, c2 from d1.t1;' Warning: Using a password on the command line interface can be insecure. +--------+------+-------+ | @@port | c1 | c2 | +--------+------+-------+ | 3303 | 1 | hello | | 3303 | 2 | world | +--------+------+-------+ $mysql -uadmin2 -p'123456' -h127.0.0.1 -P6033 -e \ 'select @@port, c1, c2 from d1.t1;' Warning: Using a password on the command line interface can be insecure. +--------+------+-------+ | @@port | c1 | c2 | +--------+------+-------+ | 3302 | 1 | hello | | 3302 | 2 | world | +--------+------+-------+ $mysql -uadmin2 -p'123456' -h127.0.0.1 -P6033 -e \ 'select @@port, c1, c2 from d1.t1 for update;' Warning: Using a password on the command line interface can be insecure. +--------+------+-------+ | @@port | c1 | c2 | +--------+------+-------+ | 3301 | 1 | hello | | 3301 | 2 | world | +--------+------+-------+
主节点故障
手动将当前leader节点kill掉，观察集群可用性变化
#使用监控账户登录 #leader最开始是127.0.0.1:3301节点，kill leader前， 127.0.0.1:3301是写组10 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 | 127.0.0.1 | 3301 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3301 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3303 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec) #执行检查sql, 请求会转发到leader3301上了 mysql -uadmin2 -p'123456' -h127.0.0.1 -P6033 -e 'begin;select @@port,User from mysql.user for update;commit;' Warning: Using a password on the command line interface can be insecure. +--------+------------------+ | @@port | User | +--------+------------------+ | 3303 | admin2 | | 3303 | proxysql_monitor | | 3303 | mysql.infoschema | | 3303 | mysql.session | | 3303 | mysql.sys | | 3303 | root | +--------+------------------+ #kill leader，127.0.0.1:3301节点立刻退化为离线组40，并随后状态更新为SHUNNED，而 127.0.0.1:3303节点进化为写组10 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 | +--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 30 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3303 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 40 | 127.0.0.1 | 3301 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec) 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 | 127.0.0.1 | 3303 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3303 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 40 | 127.0.0.1 | 3301 | 0 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec) #执行检查sql, 请求转发到新leader3303上 mysql -uadmin2 -p'123456' -h127.0.0.1 -P6033 -e 'begin;select @@port,User from mysql.user for update;commit;' Warning: Using a password on the command line interface can be insecure. +--------+------------------+ | @@port | User | +--------+------------------+ | 3303 | admin2 | | 3303 | proxysql_monitor | | 3303 | mysql.infoschema | | 3303 | mysql.session | | 3303 | mysql.sys | | 3303 | root | +--------+------------------+
前主节点恢复
手动将已经挂掉的前主恢复起来，观察集群状态变化
#使用监控账户登录 #127.0.0.1:3301是已经挂掉的前主， 127.0.0.1:3303是当选的新主 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 | 127.0.0.1 | 3303 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3303 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 40 | 127.0.0.1 | 3301 | 0 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec) #执行检查sql, 请求转发到新leader3303上 mysql -uadmin2 -p'123456' -h127.0.0.1 -P6033 -e 'begin;select @@port,User from mysql.user for update;commit;' Warning: Using a password on the command line interface can be insecure. +--------+------------------+ | @@port | User | +--------+------------------+ | 3303 | admin2 | | 3303 | proxysql_monitor | | 3303 | mysql.infoschema | | 3303 | mysql.session | | 3303 | mysql.sys | | 3303 | root | +--------+------------------+ # 重启恢复前主节点127.0.0.1:3301，观察节点进化到只读组30，并且状态更新为ONLINE 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 | 127.0.0.1 | 3303 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3301 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3303 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec) #执行写事务可以正确路由到leader mysql -uadmin2 -p'123456' -h127.0.0.1 -P6033 -e 'begin;select @@port,User from mysql.user for update;commit;' Warning: Using a password on the command line interface can be insecure. +--------+------------------+ | @@port | User | +--------+------------------+ | 3303 | admin2 | | 3303 | proxysql_monitor | | 3303 | mysql.infoschema | | 3303 | mysql.session | | 3303 | mysql.sys | | 3303 | root | +--------+------------------+
在线切主
手动进行在线切主，观察集群状态变化
#使用测试账户登录，执行sql进行手动切主 #切主前，127.0.0.1:3303节点是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 | 127.0.0.1:16800 | 24 | 25 | Follower | No | No | 5 | 0 | 24 | Yes | No | | 2 | 127.0.0.1:16801 | 24 | 25 | Follower | Yes | No | 5 | 0 | 24 | Yes | No | | 3 | 127.0.0.1:16802 | 24 | 0 | Leader | Yes | No | 5 | 0 | 23 | No | No | +-----------+-----------------+-------------+------------+----------+-----------+------------+-----------------+----------------+---------------+------------+--------------+ 3 rows in set (0.00 sec) mysql> call dbms_consensus.change_leader("127.0.0.1:16801"); Query OK, 0 rows affected (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 | +-----------+---------+-------------+------------+------+-----------+------------+-----------------+----------------+---------------+------------+--------------+ | 0 | | 0 | 0 | | | | 0 | 0 | 0 | | | +-----------+---------+-------------+------------+------+-----------+------------+-----------------+----------------+---------------+------------+--------------+ 1 row in set (0.00 sec) #手动将leader切到127.0.0.1:3302节点后 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 | 127.0.0.1:16800 | 25 | 26 | Follower | Yes | No | 5 | 0 | 25 | Yes | No | | 2 | 127.0.0.1:16801 | 25 | 0 | Leader | Yes | No | 5 | 0 | 24 | No | No | | 3 | 127.0.0.1:16802 | 25 | 26 | Follower | Yes | No | 5 | 0 | 25 | Yes | No | +-----------+-----------------+-------------+------------+----------+-----------+------------+-----------------+----------------+---------------+------------+--------------+ 3 rows in set (0.01 sec) #使用监控账户登录 #切主前，127.0.0.1:3303节点是leader 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 | 127.0.0.1 | 3303 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3301 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3303 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec) #手动将leader切到127.0.0.1:3302节点后， proxysql及时响应，127.0.0.1:3302变成了写组 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 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3301 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 40 | 127.0.0.1 | 3303 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec) #127.0.0.1:3303经历过短暂离线组40状态后变成读组30 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 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3301 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3303 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec) #执行写事务可以正确路由到新主 mysql -uadmin2 -p'123456' -h127.0.0.1 -P6033 -e 'begin;select @@port,User from mysql.user for update;commit;' Warning: Using a password on the command line interface can be insecure. +--------+------------------+ | @@port | User | +--------+------------------+ | 3302 | admin2 | | 3302 | proxysql_monitor | | 3302 | mysql.infoschema | | 3302 | mysql.session | | 3302 | mysql.sys | | 3302 | root | +--------+------------------+
备节点故障恢复
#使用监控账户登录 #备节点故障前状态,此时127.0.0.1:3301为备库节点 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 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3301 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3303 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec) #将127.0.0.1:3301节点杀掉， proxysql很快更新其到离线组40 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 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3303 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 40 | 127.0.0.1 | 3301 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec) #将127.0.0.1:3301节点恢复， proxysql很快更新其到读组30 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 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3301 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3302 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 30 | 127.0.0.1 | 3303 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.01 sec)
总结
通过上述步骤，我们成功地将ProxySQL配置为PolarDB-X标准版的代理，不仅提升了数据库系统的可维护性和灵活性，也为开发者提供了一个更为便捷的接入方式。现在，无论是应对高并发访问，还是实现复杂的数据路由策略，PolarDB-X标准版 + ProxySQL的组合都能轻松应对，助力开发者高效构建云原生应用。
数据库PolarDB-X新人入门一站式页面，快速体验集中分布式一体化新特性！
