《童虎学习笔记》15分钟ShardingSphere搭建PostgreSQL分库分表
来我的dou音 aa10246666, 看配套视频
一、实战环境
角色 | 版本 | 机器名 | IP地址 |
shard1 | pg 14 | node1 | 10.211.55.9 |
shard2 | pg 14 | node2 | 10.211.55.4 |
ShardingSphere-Proxy | 5.1.0 | node3 | 10.211.55.6 |
用于测试连接ShardingSphere-Proxy | pg 14 | node4 | 10.211.55.7 |
二、 shard1和shard2上准备环境
1、创建ShardingSphere连接pg所使用的帐号
create user appuser with password '123';
grant appuser to postgres ;
vim /etc/postgresql/14/main/pg_hba.conf
host all appuser 0.0.0.0/0 md5
重载访问控制文件 select pg_reload_conf();
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
2、创建ShardingSphere连接pg所使用的数据库
shard1上创建数据库 shard1
create database shard1;
shard2上创建数据库 shard2
create database shard2;
三、ShardingSphere-Proxy上准备环境
1、安装ShardingSphere-Proxy
安装及配置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
安装及配置ShardingSphere-Proxy
tar xzf apache-shardingsphere-5.1.0-shardingsphere-proxy-bin.tar.gz
mv apache-shardingsphere-5.1.0-shardingsphere-proxy-bin /usr/local/
2、修改配置文件 server.yaml
rules:
- !AUTHORITY
users:
- root@%:123
provider:
type: ALL_PRIVILEGES_PERMITTEDprops:
sql-show: true
3、修改配置文件 config-sharding.yaml
schemaName: testdb
dataSources:
ds_0:
url: jdbc:postgresql://10.211.55.9:5432/shard1
username: appuser
password: 123
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:postgresql://10.211.55.4:5432/shard2
username: appuser
password: 123
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1rules:
- !SHARDING
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..15}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_inline
keyGenerateStrategy:
column: order_id
keyGeneratorName: snowflake
# t_order_item:
# actualDataNodes: ds_${0..1}.t_order_item_${0..1}
# tableStrategy:
# standard:
# shardingColumn: order_id
# shardingAlgorithmName: t_order_item_inline
# keyGenerateStrategy:
# column: order_item_id
# keyGeneratorName: snowflake
bindingTables:
- t_order
# - t_order,t_order_item
defaultDatabaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
defaultTableStrategy:
none:
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
t_order_inline:
type: INLINE
props:
algorithm-expression: t_order_${order_id % 16}
# t_order_item_inline:
# type: INLINE
# props:
# algorithm-expression: t_order_item_${order_id % 2}keyGenerators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
4、启动ShardingSphere-Proxy
root@node3:/usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/conf# ../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
四、测试
1、在node4上连接ShardingSphere-Proxy
postgres@node4:~$ psql -h 10.211.55.6 -U root -p 3307 testdb
Password for user root:
psql (14.2 (Debian 14.2-1.pgdg110 1))
Type "help" for help.testdb=>
2、在node4上连接ShardingSphere-Proxy,并创建分片表 t_order
create table t_order(user_id int, order_id int primary key);
3、在ShardingSphere-Proxy上查看日志 stdout.log,可以看到分别在node1和node2上创建了16张表
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Logic SQL: create table t_order(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLCreateTableStatement(containsNotExistClause=false)
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_0(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_1(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_2(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_3(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_4(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_5(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_6(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_7(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_8(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_9(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_10(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_11(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_12(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_13(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_14(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_15(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_0(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_1(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_2(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_3(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_4(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_5(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_6(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_7(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_8(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_9(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_10(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_11(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_12(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_13(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_14(user_id int, order_id int primary key);
[INFO ] 2022-04-03 22:55:29.138 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_15(user_id int, order_id int primary key);
4、在node1上查看新建表的情况
postgres@node1:~$ psql
psql (14.2 (Debian 14.2-1.pgdg110 1))
Type "help" for help.
postgres=# \c shard1
You are now connected to database "shard1" as user "postgres".
shard1=# \d
List of relations
Schema | Name | Type | Owner
-------- ------------ ------- ---------
public | t_order_0 | table | appuser
public | t_order_1 | table | appuser
public | t_order_10 | table | appuser
public | t_order_11 | table | appuser
public | t_order_12 | table | appuser
public | t_order_13 | table | appuser
public | t_order_14 | table | appuser
public | t_order_15 | table | appuser
public | t_order_2 | table | appuser
public | t_order_3 | table | appuser
public | t_order_4 | table | appuser
public | t_order_5 | table | appuser
public | t_order_6 | table | appuser
public | t_order_7 | table | appuser
public | t_order_8 | table | appuser
public | t_order_9 | table | appuser
(16 rows)
5、在node2上查看新建表的情况
postgres@node2:~$ psql shard2
psql (14.2 (Debian 14.2-1.pgdg110 1))
Type "help" for help.shard2=# \d
List of relations
Schema | Name | Type | Owner
-------- ------------ ------- ---------
public | t_order_0 | table | appuser
public | t_order_1 | table | appuser
public | t_order_10 | table | appuser
public | t_order_11 | table | appuser
public | t_order_12 | table | appuser
public | t_order_13 | table | appuser
public | t_order_14 | table | appuser
public | t_order_15 | table | appuser
public | t_order_2 | table | appuser
public | t_order_3 | table | appuser
public | t_order_4 | table | appuser
public | t_order_5 | table | appuser
public | t_order_6 | table | appuser
public | t_order_7 | table | appuser
public | t_order_8 | table | appuser
public | t_order_9 | table | appuser
(16 rows)
6、在node4上连接ShardingSphere-Proxy,插入几条数据
postgres@node4:~$ psql -h 10.211.55.6 -U root -p 3307 testdb
Password for user root:
psql (14.2 (Debian 14.2-1.pgdg110 1))
Type "help" for help.testdb=>
insert into t_order values(1, 1);
insert into t_order values(2, 1);
insert into t_order values(1, 2);
insert into t_order values(2, 2);
insert into t_order values(1, 3);
insert into t_order values(2, 3);
testdb=>
在ShardingSphere-Proxy上,查看日志stdout.log,观察数据分布式插入的情况
[INFO ] 2022-04-03 23:04:57.311 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Logic SQL: insert into t_order values(1, 1);
[INFO ] 2022-04-03 23:04:57.311 [Connection-4-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLInsertStatement(withSegment=Optional.empty)
[INFO ] 2022-04-03 23:04:57.311 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: insert into t_order_1 values(1, 1);
[INFO ] 2022-04-03 23:05:14.515 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Logic SQL: insert into t_order values(2, 1);
[INFO ] 2022-04-03 23:05:14.515 [Connection-4-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLInsertStatement(withSegment=Optional.empty)
[INFO ] 2022-04-03 23:05:14.515 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: insert into t_order_1 values(2, 1);
[INFO ] 2022-04-03 23:05:20.018 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Logic SQL: insert into t_order values(1, 2);
[INFO ] 2022-04-03 23:05:20.018 [Connection-4-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLInsertStatement(withSegment=Optional.empty)
[INFO ] 2022-04-03 23:05:20.018 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: insert into t_order_2 values(1, 2);
[INFO ] 2022-04-03 23:05:23.546 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Logic SQL: insert into t_order values(2, 2);
[INFO ] 2022-04-03 23:05:23.547 [Connection-4-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLInsertStatement(withSegment=Optional.empty)
[INFO ] 2022-04-03 23:05:23.547 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: insert into t_order_2 values(2, 2);
[INFO ] 2022-04-03 23:06:47.975 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Logic SQL: insert into t_order values(1, 3);
[INFO ] 2022-04-03 23:06:47.976 [Connection-4-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLInsertStatement(withSegment=Optional.empty)
[INFO ] 2022-04-03 23:06:47.976 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: insert into t_order_3 values(1, 3);
[INFO ] 2022-04-03 23:06:50.604 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Logic SQL: insert into t_order values(2, 3);
[INFO ] 2022-04-03 23:06:50.605 [Connection-4-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLInsertStatement(withSegment=Optional.empty)
[INFO ] 2022-04-03 23:06:50.605 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: insert into t_order_3 values(2, 3);
在node1上,查看表的情况,以便确认数据插入情况,红色部分大小为8192的,即为有数据插入的
postgres@node1:~$ psql shard1
psql (14.2 (Debian 14.2-1.pgdg110 1))
Type "help" for help.shard1=# \d
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
-------- ------------ ------- --------- ------------- --------------- ------------ -------------
public | t_order_0 | table | appuser | permanent | heap | 0 bytes |
public | t_order_1 | table | appuser | permanent | heap | 8192 bytes |
public | t_order_10 | table | appuser | permanent | heap | 0 bytes |
public | t_order_11 | table | appuser | permanent | heap | 0 bytes |
public | t_order_12 | table | appuser | permanent | heap | 0 bytes |
public | t_order_13 | table | appuser | permanent | heap | 0 bytes |
public | t_order_14 | table | appuser | permanent | heap | 0 bytes |
public | t_order_15 | table | appuser | permanent | heap | 0 bytes |
public | t_order_2 | table | appuser | permanent | heap | 8192 bytes |
public | t_order_3 | table | appuser | permanent | heap | 8192 bytes |
public | t_order_4 | table | appuser | permanent | heap | 0 bytes |
public | t_order_5 | table | appuser | permanent | heap | 0 bytes |
public | t_order_6 | table | appuser | permanent | heap | 0 bytes |
public | t_order_7 | table | appuser | permanent | heap | 0 bytes |
public | t_order_8 | table | appuser | permanent | heap | 0 bytes |
public | t_order_9 | table | appuser | permanent | heap | 0 bytes |
(16 rows)shard1=#
在node2上,查看表的情况,以便确认数据插入情况,红色部分大小为8192的,即为有数据插入的
postgres@node2:~$ psql shard2
psql (14.2 (Debian 14.2-1.pgdg110 1))
Type "help" for help.shard2=# \d
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
-------- ------------ ------- --------- ------------- --------------- ------------ -------------
public | t_order_0 | table | appuser | permanent | heap | 0 bytes |
public | t_order_1 | table | appuser | permanent | heap | 8192 bytes |
public | t_order_10 | table | appuser | permanent | heap | 0 bytes |
public | t_order_11 | table | appuser | permanent | heap | 0 bytes |
public | t_order_12 | table | appuser | permanent | heap | 0 bytes |
public | t_order_13 | table | appuser | permanent | heap | 0 bytes |
public | t_order_14 | table | appuser | permanent | heap | 0 bytes |
public | t_order_15 | table | appuser | permanent | heap | 0 bytes |
public | t_order_2 | table | appuser | permanent | heap | 8192 bytes |
public | t_order_3 | table | appuser | permanent | heap | 8192 bytes |
public | t_order_4 | table | appuser | permanent | heap | 0 bytes |
public | t_order_5 | table | appuser | permanent | heap | 0 bytes |
public | t_order_6 | table | appuser | permanent | heap | 0 bytes |
public | t_order_7 | table | appuser | permanent | heap | 0 bytes |
public | t_order_8 | table | appuser | permanent | heap | 0 bytes |
public | t_order_9 | table | appuser | permanent | heap | 0 bytes |
(16 rows)
7、在node4上连接ShardingSphere-Proxy,发起2条查询语句
testdb=> select * from t_order where order_id=1;
user_id | order_id
--------- ----------
2 | 1
1 | 1
(2 rows)testdb=> select * from t_order where order_id=2;
user_id | order_id
--------- ----------
2 | 2
1 | 2
(2 rows)
在ShardingSphere-Proxy上,查看日志stdout.log,观察数据分布式查询的情况
[INFO ] 2022-04-03 23:20:38.118 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Logic SQL: select * from t_order where order_id=1;
[INFO ] 2022-04-03 23:20:38.118 [Connection-4-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-04-03 23:20:38.118 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from t_order_1 where order_id=1;
[INFO ] 2022-04-03 23:20:38.118 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from t_order_1 where order_id=1;
[INFO ] 2022-04-03 23:20:46.669 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Logic SQL: select * from t_order where order_id=2;
[INFO ] 2022-04-03 23:20:46.669 [Connection-4-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-04-03 23:20:46.669 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from t_order_2 where order_id=2;
[INFO ] 2022-04-03 23:20:46.669 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from t_order_2 where order_id=2;
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /boutique/detail/tanhibcfkh
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
TikTok加速器哪个好免费的TK加速器推荐
TK小达人 10-01 -
Android 11 保存文件到外部存储,并分享文件
Luke 10-12 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
excel下划线不显示怎么办
PHP中文网 06-23 -
微信运动停用后别人还能看到步数吗
PHP中文网 07-22 -
excel打印预览压线压字怎么办
PHP中文网 06-22