《童虎学习笔记》10分钟ShardingSphere搭建PG读写分离

童虎学习笔记的博客_CSDN博客-领域博主https://blog.csdn.net/tonghu_note

        来我的西瓜视频，看免费配套视频https://www.ixigua.com/home/2058760810138187

        来我的dou音 aa10246666， 看配套视频

在postgresql.conf中添加下面的配置，用于放开监听

listen_addresses = '*'

在pg_hba.conf中添加下面的配置，用于ShardingSphere-Proxy连接数据库

host    all             appuser         10.211.55.6/32          md5

重启服务使配置生效        

systemctl restart postgresql

创建用户appuser，用于ShardingSphere-Proxy连接数据库

create user appuser password '111' superuser；

创建数据库d1，做为我们读写分离主要使用的数据库

create database d1;

1、安装及配置jdk(jdk的版本要依据你cpu来选择，我用的是mac虚出来的虚机)

tar xzf jdk-8u202-linux-arm64-vfp-hflt.tar.gz

mv jdk1.8.0_202 /usr/local/

/etc/profile最后添加如下配置

PATH=/usr/local/jdk1.8.0_202/bin:$PATH

source /etc/profile

tar xzf apache-shardingsphere-5.1.0-shardingsphere-proxy-bin.tar.gz

mv apache-shardingsphere-5.1.0-shardingsphere-proxy-bin /usr/local/

编辑服务文件 /usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/conf/server.yaml红色部分， 用于配置虚拟帐号密码，将配置root用户及密码123且允许外网连接，同时权限指定为“所有权限无限制”，sql-show是用于在日志中记录下每条sql及执行的节点，其它默认

#      maxRetries: 3
#      operationTimeoutMilliseconds: 500
#  overwrite: false
#
rules:
  - !AUTHORITY
    users:
      - root@%:123
#      - sharding@:sharding
    provider:
      type: ALL_PRIVILEGES_PERMITTED
#  - !TRANSACTION
#    defaultType: XA
#    providerType: Atomikos

props:
#  max-connections-size-per-query: 1
#  kernel-executor-size: 16  # Infinite by default.
#  proxy-frontend-flush-threshold: 128  # The default value is 128.
#  proxy-opentracing-enabled: false
#  proxy-hint-enabled: false
#  sql-show: false
  sql-show: true

编辑读写分离文件 /usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/conf/config-readwrite-splitting.yaml，其中：

schemaName: newdb，为虚拟映射的数据库名称

primary_ds，为主库也就是写库的相关配置

replica_ds_0，为从库也就是读库的相关配置

schemaName: newdb

dataSources:
  primary_ds:
    url: jdbc:postgresql://10.211.55.9:5432/d1
    username: appuser
    password: 111
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  replica_ds_0:
    url: jdbc:postgresql://10.211.55.4:5432/d1
    username: appuser
    password: 111
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
#  replica_ds_1:
#    url: jdbc:postgresql://127.0.0.1:5432/demo_replica_ds_1
#    username: postgres
#    password: postgres
#    connectionTimeoutMilliseconds: 30000
#    idleTimeoutMilliseconds: 60000
#    maxLifetimeMilliseconds: 1800000
#    maxPoolSize: 50
#    minPoolSize: 1
#
rules:
- !READWRITE_SPLITTING
  dataSources:
    readwrite_ds:
      type: Static
      props:
        write-data-source-name: primary_ds
        read-data-source-names: replica_ds_0

日志文件的路径是 /usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/logs/stdout.log

root@node3:/usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/bin# ./start.sh 
we find java version: java8, full_version=1.8.0_202
Starting the ShardingSphere-Proxy ...
The classpath is /usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/conf:.:/usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/lib/*:/usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/ext-lib/*
Please check the STDOUT file: /usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/logs/stdout.log

服务默认监听端口是 3307，可以验证一下端口是否启用 ss -tnl

root@node3:/usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/bin# ss -tnl
State          Recv-Q         Send-Q                 Local Address:Port                 Peer Address:Port         Process         
LISTEN         0              128                          0.0.0.0:22                        0.0.0.0:*                            
LISTEN         0              224                        127.0.0.1:5432                      0.0.0.0:*                            
LISTEN         0              4096                               *:3307                            *:*                            
LISTEN         0              128                             [::]:22                           [::]:*    

在node4上测试连接ShardingSphere-Proxy， psql -h 10.211.55.6 -p 3307 -U root newdb ， 密码是123

root@node4:~# psql -h 10.211.55.6 -p 3307 -U root newdb
Password for user root: 
psql (13.5 (Debian 13.5-0+deb11u1), server 14.2 (Debian 14.2-1.pgdg110+1)-ShardingSphere-Proxy 5.1.0)
WARNING: psql major version 13, server major version 14.
         Some psql features might not work.
Type "help" for help.

newdb=> 

创建一个表t1

newdb=> create table t1(id int);
CREATE TABLE
newdb=> 

查看日志，确认请求走的是主节点，也就是写库

[INFO ] 2022-03-19 23:22:50.199 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Logic SQL: create table t1(id int);
[INFO ] 2022-03-19 23:22:50.199 [Connection-4-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLCreateTableStatement(containsNotExistClause=false)
[INFO ] 2022-03-19 23:22:50.199 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: primary_ds ::: create table t1(id int);
 

发起一个查询

newdb=> select * from t1;
 id 
----
(0 rows)
 

查看日志，，确认请求走的是从节点，也就是读节点

[INFO ] 2022-03-19 23:24:41.250 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Logic SQL: select * from t1;
[INFO ] 2022-03-19 23:24:41.250 [Connection-4-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-03-19 23:24:41.250 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: replica_ds_0 ::: select * from t1;
 

1、搭建1主多从的架构，并应用ShardingSphere-Proxy

2、搭建双节点ShardingSphere-Proxy，同时结果其它高可用方案，实现ShardingSphere-Proxy的高可用