利用ShardingSphere-proxy实现PostgreSQL读写分离

最新推荐文章于 2024-08-28 19:59:39 发布

duqk1982

最新推荐文章于 2024-08-28 19:59:39 发布

阅读量559

点赞数

分类专栏： PostgreSQL 文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/dqk1982/article/details/133833168

版权

PostgreSQL 专栏收录该内容

20 篇文章 1 订阅

订阅专栏

在业务对数据库是读多写少，且单台服务器或者数据库的性能已经不能满足当前业务对数据库大量读取请求的时候，我们可能需要对数据库做读写分离。本文使用ShardingSphere-proxy实现了PostgreSQL的读写分离场景.

1.节点信息

192.168.10.11  PostgreSQL14.7     主 
192.168.10.12  PostgreSQL14.7     从
192.168.10.13     ShardingSphere-proxy 5.4.0

2.准备环境

防火墙设置

systemctl status firewalld.service
systemctl stop firewalld.service
systemctl disable firewalld

SELINUX设置

setenforce 0 
vi /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three two values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected. 
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

设置三节点时间同步

1、时间服务器配置（以192.168.10.13为例）
1）安装ntpd服务
yum install -y ntp
2）配置ntpd.conf
vi /etc/ntp.conf #以下定义是让NTP Server和其自身保持同步，如果在/etc/ntp.conf中定义的server都不可用时，将使用local时间作为ntp服务提供给ntp客户端。
server 127.127.1.0
fudge 127.127.1.0 stratum 10
3）启动ntpd服务
systemctl start ntpd
systemctl enable ntpd
systemctl status ntpd

2、数据库节点配置
yum install -y ntpdate
其余节点安装用ntpdate,并从192.168.10.13进行时间同步，
为避免时间出现误差，对数据库主备节点添加crontab任务保持定时同步时间，每分钟同步一次：

crontab -e
*/10 * * * *  /sbin/ntpdate -u 192.168.10.13 >> /var/log/ntp.log

检查时间同步情况
cat /var/log/ntp.log

2.数据库安装

搭建好主从PostgreSQL流复制(过程省略)，并配置好pg_hba.conf

[postgres@du101 ~]$ tail -5 /data/pgdata/pg_hba.conf    
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
host    all     all     0.0.0.0/0 md5
host    replication     all     192.168.10.11/32        md5
host    replication     all     192.168.10.12/32        md5
[postgres@du101 ~]$

postgres=# select * from pg_replication_slots\gx
-[ RECORD 1 ]-------+----------
slot_name           | rep
plugin              | 
slot_type           | physical
datoid              | 
database            | 
temporary           | f
active              | t
active_pid          | 1468
xmin                | 738
catalog_xmin        | 
restart_lsn         | 0/5001B58
confirmed_flush_lsn | 
wal_status          | reserved
safe_wal_size       | 
two_phase           | f

postgres=# select * from pg_stat_replication\gx 
-[ RECORD 1 ]----+------------------------------
pid              | 1468
usesysid         | 16384
usename          | replica
application_name | walreceiver
client_addr      | 192.168.10.12
client_hostname  | 
client_port      | 18262
backend_start    | 2023-10-14 20:33:41.35771+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/5001B58
write_lsn        | 0/5001B58
flush_lsn        | 0/5001B58
replay_lsn       | 0/5001B58
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2023-10-14 20:34:20.417643+08

创建测试数据库和用户

postgres=# create database testdb;
CREATE DATABASE
postgres=# create user myuser password 'Myuser@123' superuser;
CREATE ROLE
postgres=#

3.安装Proxy

ShardingSphere-proxy的运行需要java环境

[root@du103 ~]# yum -y install java
[root@du103 ~]# java -version      
openjdk version "1.8.0_332"
OpenJDK Runtime Environment (build 1.8.0_332-b09)
OpenJDK 64-Bit Server VM (build 25.332-b09, mixed mode)

[root@du103 ~]# tar zxvf apache-shardingsphere-5.4.0-shardingsphere-proxy-bin.tar.gz  -C /opt/
[root@du103 ~]# mv /opt/apache-shardingsphere-5.4.0-shardingsphere-proxy-bin /opt/apache-shardingsphere-5.4.0

[root@du103 ~]# ll /opt/apache-shardingsphere-5.4.0/conf
total 36
-rw-r--r-- 1 root root 3529 Dec 12  2022 config-encrypt.yaml
-rw-r--r-- 1 root root 3772 Dec 12  2022 config-mask.yaml
-rw-r--r-- 1 root root 3762 Dec 12  2022 config-readwrite-splitting.yaml   --读写分离配置文件
-rw-r--r-- 1 root root 5210 Dec 12  2022 config-shadow.yaml
-rw-r--r-- 1 root root 5701 Dec 12  2022 config-sharding.yaml
-rw-r--r-- 1 root root 1715 Dec 12  2022 hbase-db.yaml
-rw-r--r-- 1 root root 3096 Dec 12  2022 server.yaml
[root@du103 ~]# 
--以 config- 前缀命名的 YAML 配置文件，即为一个逻辑数据源。
--server.yaml 配置注册中心、认证信息以及公用属性

保留 config-readwrite-splitting.yaml server.yaml 文件其他可以创建bak目录放进去

[root@du103 ~]# mkdir /opt/apache-shardingsphere-5.4.0/conf/bak
[root@du103 ~]# mv /opt/apache-shardingsphere-5.4.0/conf/*.yaml /opt/apache-shardingsphere-5.4.0/conf/bak
[root@du103 ~]# cp /opt/apache-shardingsphere-5.4.0/conf/bak/server.yaml /opt/apache-shardingsphere-5.4.0/conf/
[root@du103 ~]# cp /opt/apache-shardingsphere-5.4.0/conf/bak/config-readwrite-splitting.yaml /opt/apache-shardingsphere-5.4.0/conf/
[root@du103 ~]#

配制server.yaml文件

server.yaml文件的模板。该配置文件的主要作用是配置前端的认证数据库、用户名和密码, 以及连接相关的属性：包括分布式事务类型、sql日志等。
配置虚拟帐号密码，使用默认的root用户及密码root，且允许外网连接，同时权限指定为“所有权限无限制”，sql-show是用于在日志中记录下每条sql及执行的节点。

[root@du103 ~]# vi /opt/apache-shardingsphere-5.4.0/conf/server.yaml
authority:
  users:
    - user: root@%
      password: root
  privilege:
    type: ALL_PERMITTED      
props:
  sql-show: true
[root@du103 ~]#

说明：

authority:
  users:
    - user: # 用于登录计算节点的用户名和授权主机的组合，格式：<username>@<hostname>，hostname 为 % 或空字符串表示不限制授权主机
      password: # 用户密码
      authenticationMethodName: # 可选项，用于为用户指定密码认证方式
  authenticators: # 可选项，默认不需要配置，Proxy 根据前端协议类型自动选择
    authenticatorName:
      type: # 密码认证类型
  defaultAuthenticator: # 可选项，指定一个 authenticatorName 作为默认的密码认证方式
  privilege:
    type: # 权限提供者类型，缺省值为 ALL_PERMITTED

得益于 ShardingSphere 的可插拔架构，Proxy 提供了两种级别的权限提供者，分别是：
• ALL_PERMITTED：每个用户都拥有所有权限，无需专门授权；
• DATABASE_PERMITTED：为用户授予指定逻辑库的权限，通过 user-database-mappings 进行定义。

其他属性：
proxy-default-port: 3307 # ShardingSphere-Proxy的默认端口3307

修改读写分离配置文件

databaseName是映射的数据库名字，primary_ds和replica_ds_0分别为主库/写库的相关配置和从库/读库的相关被配置。

[root@du103 ~]# vi /opt/apache-shardingsphere-5.4.0/conf/config-readwrite-splitting.yaml   
databaseName: readwrite_splitting_db
dataSources:
  primary_ds:
    url: jdbc:postgresql://192.168.10.11:1921/testdb
    username: myuser
    password: Myuser@123
  replica_ds_0:
    url: jdbc:postgresql://192.168.10.12:1921/testdb
    username: myuser
    password: Myuser@123
rules:
- !READWRITE_SPLITTING
  dataSources:
    readwrite_ds:
      writeDataSourceName: primary_ds
      readDataSourceNames:
        - replica_ds_0   

[root@du103 ~]#

说明：

dataSources: # 数据源配置，可配置多个 <data-source-name>
  <data_source_name>: # 数据源名称
    dataSourceClassName: # 数据源连接池完整类名
    url: # 数据库 URL 连接
    username: # 数据库用户名
    password: # 数据库密码
    # ... 数据库连接池的其它属性

启动shardingsphere

[root@du103 ~]# cd /opt/apache-shardingsphere-5.4.0/bin/
[root@du103 bin]# ls
start.bat  start.sh  stop.sh
[root@du103 bin]#
[root@du103 bin]#** sh start.sh **
/usr/bin/java
we find java version: java8, full_version=1.8.0_332, full_path=/usr/bin/java
The classpath is /opt/apache-shardingsphere-5.4.0/conf:/opt/apache-shardingsphere-5.4.0/conf:.:/opt/apache-shardingsphere-5.4.0/lib/*:/opt/apache-shardingsphere-5.4.0/ext-lib/*
main class org.apache.shardingsphere.proxy.Bootstrap -1 /opt/apache-shardingsphere-5.4.0/conf 0.0.0.0 false
Starting the ShardingSphere-Proxy ...
 PID: 1751
Please check the STDOUT file: /opt/apache-shardingsphere-5.4.0/logs/stdout.log
[root@du103 bin]# 
[root@du103 bin]# tail -2 /opt/apache-shardingsphere-5.4.0/logs/stdout.log
[INFO ] 2023-10-14 20:57:43.031 [main] o.a.s.p.frontend.ssl.ProxySSLContext - Proxy frontend SSL/TLS is not enabled.
[INFO ] 2023-10-14 20:57:43.191 [main] o.a.s.p.frontend.ShardingSphereProxy - ShardingSphere-Proxy Standalone mode started successfully
[root@du103 bin]#

端口已经启动：

[root@du103 bin]# ss -tnl
State       Recv-Q Send-Q                                Local Address:Port                                               Peer Address:Port              
LISTEN      0      128                                               *:22                                                            *:*                  
LISTEN      0      100                                       127.0.0.1:25                                                            *:*                  
LISTEN      0      128                                            [::]:22                                                         [::]:*                  
LISTEN      0      100                                           [::1]:25                                                         [::]:*                  
LISTEN      0      1024                                           [::]:3307                                                       [::]:*           
[root@du103 bin]#

4.测试

[postgres@du102 ~]$ **psql -h 192.168.10.13 -p 3307 -U root readwrite_splitting_db**
Password for user root: 
psql (14.7)
Type "help" for help.

readwrite_splitting_db=> create table t(id int);
CREATE TABLE
readwrite_splitting_db=> insert into t values(1);
INSERT 0 1
readwrite_splitting_db=> insert into t values(2);
INSERT 0 1
readwrite_splitting_db=> select * from t;
 id 
----
  1
  2
(2 rows)

readwrite_splitting_db=> \q
[postgres@du102 ~]$

从日志看书，读分发到了备库，写分发到了主库：

[root@du103 bin]# tail -f /opt/apache-shardingsphere-5.4.0/logs/stdout.log                   
[INFO ] 2023-10-14 21:21:58.999 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: create table t(id int);
[INFO ] 2023-10-14 21:21:58.999 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: primary_ds ::: create table t(id int);
[INFO ] 2023-10-14 21:22:06.818 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: insert into t values(1);
[INFO ] 2023-10-14 21:22:06.818 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: primary_ds ::: insert into t values(1);
[INFO ] 2023-10-14 21:22:09.162 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: insert into t values(2);
[INFO ] 2023-10-14 21:22:09.162 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: primary_ds ::: insert into t values(2);
[INFO ] 2023-10-14 21:22:14.044 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: select * from t;
[INFO ] 2023-10-14 21:22:14.045 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: replica_ds_0 ::: select * from t;

5.数据异常处理

按照现有的配置，读从备库读取，写是写到主库的，如果在同一个事务中同时有读写，会出现什么情况？测试如下：

[postgres@du102 ~]$ psql -h 192.168.10.13 -p 3307 -U root readwrite_splitting_db
Password for user root: 
psql (14.7)
Type "help" for help.

readwrite_splitting_db=> begin;
BEGIN
readwrite_splitting_db=*> select * from t where id =1;
 id 
----
  1
(1 row)

readwrite_splitting_db=*> insert into t values(3);
INSERT 0 1
readwrite_splitting_db=*> select * from t;
 id 
----
  1
  2
(2 rows)

readwrite_splitting_db=*> select * from t where id=3;
 id 
----
(0 rows)

readwrite_splitting_db=*> select * from t where id=2;
 id 
----
  2
(1 row)

readwrite_splitting_db=*> commit;
COMMIT
readwrite_splitting_db=> select * from t;
 id 
----
  1
  2
  3
(3 rows)

readwrite_splitting_db=> \q
[postgres@du102 ~]$

[INFO ] 2023-10-14 21:45:59.907 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: select * from t where id =1;
[INFO ] 2023-10-14 21:45:59.907 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: replica_ds_0 ::: select * from t where id =1;
[INFO ] 2023-10-14 21:46:06.077 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: insert into t values(3);
[INFO ] 2023-10-14 21:46:06.077 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: primary_ds ::: insert into t values(3);
[INFO ] 2023-10-14 21:46:13.131 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: select * from t;
[INFO ] 2023-10-14 21:46:13.132 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: replica_ds_0 ::: select * from t;
[INFO ] 2023-10-14 21:46:20.515 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: select * from t where id=3;
[INFO ] 2023-10-14 21:46:20.516 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: replica_ds_0 ::: select * from t where id=3;
[INFO ] 2023-10-14 21:46:30.674 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: select * from t where id=2;
[INFO ] 2023-10-14 21:46:30.675 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: replica_ds_0 ::: select * from t where id=2;
[INFO ] 2023-10-14 21:46:42.084 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: select * from t;
[INFO ] 2023-10-14 21:46:42.084 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: replica_ds_0 ::: select * from t;

从ShardingSphere日志得出：
当在同一个事务中的时候，由于读写分离查询的是备库，在同一个事务中不能做到数据的可见，只有当数据提交之后，才能可见，例如语句：select * from t where id=3。

修改config-readwrite-splitting.yaml配置：

[root@du103 bin]# cat /opt/apache-shardingsphere-5.4.0/conf/config-readwrite-splitting.yaml |grep -v '^#'|grep -v '^$'
databaseName: readwrite_splitting_db
dataSources:
  primary_ds:
    url: jdbc:postgresql://192.168.10.11:1921/testdb
    username: myuser
    password: Myuser@123
  replica_ds_0:
    url: jdbc:postgresql://192.168.10.12:1921/testdb
    username: myuser
    password: Myuser@123
rules:
- !READWRITE_SPLITTING
  dataSources:
    readwrite_ds:
      writeDataSourceName: primary_ds
      readDataSourceNames:
        - replica_ds_0
      transactionalReadQueryStrategy: PRIMARY
[root@du103 bin]# 

[root@du103 bin]# sh stop.sh && sh start.sh

[postgres@du102 ~]$ psql -h 192.168.10.13 -p 3307 -U root readwrite_splitting_db
Password for user root: 
psql (14.7)
Type "help" for help.

readwrite_splitting_db=> select * from t;
 id 
----
  1
  2
  3
(3 rows)

readwrite_splitting_db=> begin;
BEGIN
readwrite_splitting_db=*> select * from t;
 id 
----
  1
  2
  3
(3 rows)

readwrite_splitting_db=*> insert into t values(4);
INSERT 0 1
readwrite_splitting_db=*> select * from t;        
 id 
----
  1
  2
  3
  4
(4 rows)

readwrite_splitting_db=*> select * from t where id=4;
 id 
----
  4
(1 row)

readwrite_splitting_db=*> commit;
COMMIT
readwrite_splitting_db=> \q
[postgres@du102 ~]$ 

[INFO ] 2023-10-14 21:54:15.358 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: select * from t;
[INFO ] 2023-10-14 21:54:15.359 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: replica_ds_0 ::: select * from t;
[INFO ] 2023-10-14 21:54:24.709 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: select * from t;
[INFO ] 2023-10-14 21:54:24.709 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: primary_ds ::: select * from t;
[INFO ] 2023-10-14 21:54:37.564 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: insert into t values(4);
[INFO ] 2023-10-14 21:54:37.564 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: primary_ds ::: insert into t values(4);
[INFO ] 2023-10-14 21:54:39.067 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: select * from t;
[INFO ] 2023-10-14 21:54:39.067 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: primary_ds ::: select * from t;
[INFO ] 2023-10-14 21:54:44.063 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: select * from t where id=4;
[INFO ] 2023-10-14 21:54:44.063 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: primary_ds ::: select * from t where id=4;

从ShardingSphere日志得出：在同一个事务中的读写都是发给主库执行的，这样就保证了在同一个事务中的数据一致性。
参数说明：
transactionalReadQueryStrategy : # 事务内读请求的路由策略，可选值：PRIMARY（路由至主库）、FIXED（同一事务内路由至固定数据源）、DYNAMIC（同一事务内路由至非固定数据源）。默认值：DYNAMIC