ps网站建设教程,国内最大的软件开发商,扫码支付 wordpress,网页是什么样子的ProxySQL构建PolarDB-X标准版高可用路由服务三节点集群
一、PolarDB-X标准版主备集群搭建
三台机器上传 polardbx 包#xff0c;包可以从官网https://openpolardb.com/download获取#xff0c;这里提供离线rpm。
1、上传 polardbx 安装包 到 /opt目录下
rpm -ivh t-pol…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) [rootcdh-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) [rootcdh-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_names1# 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:148861,10.10.6.48:148862,10.10.6.49:148863
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-filemy.cnf --cluster-info10.10.6.47:14886;10.10.6.48:14886;10.10.6.49:148861 --initialize-insecure
启动
/opt/polardbx_engine/bin/mysqld_safe --defaults-filemy.cnf --cluster-info10.10.6.47:14886;10.10.6.48:14886;10.10.6.49:148861 10.10.6.48初始化
/opt/polardbx_engine/bin/mysqld --defaults-filemy.cnf --cluster-info10.10.6.47:14886;10.10.6.48:14886;10.10.6.49:148862 --initialize-insecure
启动
/opt/polardbx_engine/bin/mysqld_safe --defaults-filemy.cnf --cluster-info10.10.6.47:14886;10.10.6.48:14886;10.10.6.49:148862 10.10.6.49初始化
/opt/polardbx_engine/bin/mysqld --defaults-filemy.cnf --cluster-info10.10.6.47:14886;10.10.6.48:14886;10.10.6.49:148863 --initialize-insecure
启动
/opt/polardbx_engine/bin/mysqld_safe --defaults-filemy.cnf --cluster-info10.10.6.47:14886;10.10.6.48:14886;10.10.6.49:148863 稍等片刻即可登录三台数据库。如果直接使用本文的 my.cnf 模板可以用mysql -h127.0.0.1 -P4886 -uroot 登录数据库。 需要预先在机器上安装 mysql 客户端centos 系统可以使用 yum install mysql 安装。
[polarxcdh-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 distributionCopyright (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: 3CURRENT_TERM: 41CURRENT_LEADER: 10.10.6.49:14886COMMIT_INDEX: 18LAST_LOG_TERM: 41LAST_LOG_INDEX: 18ROLE: LeaderVOTED_FOR: 3LAST_APPLY_INDEX: 17
SERVER_READY_FOR_RW: YesINSTANCE_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(ROLELeader OR ROLEFollower, 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) [rootcdh-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) [rootcdh-alpha opt]#本地安装
sudo rpm -ivh proxysql-2.4.8-1-centos7.x86_64.rpm --nodeps
启动ProxySQL
(base) [rootcdh-alpha opt]# sudo systemctl start proxysql(base) [rootcdh-alpha opt]# sudo systemctl status proxysql
● proxysql.service - High Performance Advanced Proxy for MySQLLoaded: loaded (/etc/systemd/system/proxysql.service; enabled; vendor preset: disabled)Active: active (running) since Thu 2025-02-13 18:50:39 CST; 17h agoProcess: 23894 ExecStart/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (codeexited, status0/SUCCESS)Main PID: 23896 (proxysql)Tasks: 32Memory: 31.0MCGroup: /system.slice/proxysql.service├─23896 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf└─23897 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnfFeb 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/root2、配置
登陆账户
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_valueproxysql_monitor WHERE variable_namemysql-monitor_username;
UPDATE global_variables SET variable_value123456 WHERE variable_namemysql-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_serversleader节点定义为写组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 -p123456 -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验证其高可用路由
