使用开源ProxySQL构建PolarDB-X标准版高可用路由服务

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 本文将指导如何快速搭建和配置PolarDB-X标准版与ProxySQL,并提供验证高可用路由服务验证测试。

作者:严华

前言

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,并提供验证高可用路由服务验证测试。

3.1.jpg

手动部署

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新人入门一站式页面,快速体验集中分布式一体化新特性!

https://www.aliyun.com/database/polardb-x-new

相关实践学习
跟我学:如何一键安装部署 PolarDB-X
《PolarDB-X 动手实践》系列第一期,体验如何一键安装部署 PolarDB-X。
相关文章
|
30天前
|
关系型数据库 分布式数据库 PolarDB
开源PolarDB-X 部署安装全过程
本文介绍了开源PolarDB-X的部署安装步骤:首先,下载并解压PXD工具,配置至系统路径;然后,使用PXD工具进行部署,选择模式,输入参数,并等待部署完成;最后,验证集群状态。在遇到问题时,参考官方文档、社区支持或进行排查。建议包括优化文档、增强错误处理和建立用户反馈机制。
|
1月前
|
关系型数据库 分布式数据库 数据库
【PolarDB开源】PolarDB-X源码解读:分布式事务处理机制揭秘
【5月更文挑战第20天】PolarDB-X,PolarDB家族的一员,专注于大规模分布式事务处理,采用2PC协议保证ACID特性。源码解析揭示其通过预提交、一致性快照隔离和乐观锁优化事务性能,以及利用事务日志进行故障恢复。深入理解其事务处理机制对开发者掌握分布式数据库核心技术至关重要。随着开源社区的发展,更多优化方案将涌现,助力构建更强大的分布式数据库系统。
106 6
|
1月前
|
SQL 容灾 关系型数据库
[版本更新] PolarDB-X V2.4 列存引擎开源正式发布
[版本更新] PolarDB-X V2.4 列存引擎开源正式发布!
[版本更新] PolarDB-X V2.4 列存引擎开源正式发布
|
13天前
|
关系型数据库 数据管理 数据库
数据管理DMS产品使用合集之如何极速恢复RDS(关系型数据库服务)中的数据表
阿里云数据管理DMS提供了全面的数据管理、数据库运维、数据安全、数据迁移与同步等功能,助力企业高效、安全地进行数据库管理和运维工作。以下是DMS产品使用合集的详细介绍。
|
19天前
|
Ubuntu 关系型数据库 分布式数据库
开源PolarDB -X 部署安装
本文记录了在Ubuntu 20.04上部署阿里云分布式数据库PolarDB-X的步骤,包括环境准备、安装依赖、下载源码、编译安装、配置启动,并分享了遇到的配置错误、依赖冲突和日志不清等问题。作者建议官方改进文档细节、优化代码质量和建立开发者社区。安装历史记录显示了相关命令行操作。尽管过程有挑战,但作者期待产品体验的提升。
186 6
|
1月前
|
SQL 运维 关系型数据库
PolarDB产品使用合集之PolarDB 2.3.0 版本的 CDC 功能支持 Polardb-X 到 Polardb-X 的数据同步吗
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
9月前
|
SQL Oracle 关系型数据库
Polar DB-O (兼容 Oracle 语法版本)和Polar DB PostgreSQL 版本概述(二)
Polar DB-O (兼容 Oracle 语法版本)和Polar DB PostgreSQL 版本概述(二)
1093 0
|
存储 SQL 安全
PolarDB-X内核新版本:将MySQL进行到底
在PolarDB-X最新的内核版本5.4.15中,提供诸多新功能:存储过程,读写分离优化,表级分区管理,密码、审计优化等。
813 0
PolarDB-X内核新版本:将MySQL进行到底
|
存储 SQL Kubernetes
PolarDB-X 发布 2.1.0 版本,Paxos 重磅开源
2022年4月1号,PolarDB-X 正式开源X-Paxos,基于原生MySQL存储节点,提供Paxos三副本共识协议,可以做到金融级数据库的高可用和容灾能力,做到RPO=0的生产级别可用性,可以满足同城三机房、两地三中心等容灾架构。
PolarDB-X 发布 2.1.0 版本,Paxos 重磅开源
|
存储 SQL Cloud Native
PolarDB-X内核新版本:更精细的数据管理
非常高兴为大家带来PolarDB-X内核5.4.14版本。在最新版本中提供了冷热数据存储分离、数据Locality、数据热点诊断、并行DML优化、Flashback Query和AUTO_INCREMENT兼容性,大幅提升了数据的可管理性、SQL处理能力和兼容性,并针对数据热点问题为用户提供了更多运维手段。

相关产品

  • 云原生分布式数据库 PolarDB-X
  • 云原生数据库 PolarDB