一般情况下，java项目试用Sharding-jdbc做分库分表，但是开发过程中需要查看数据，分表后很不方便，所以建议采用部署一个proxy来用于开发过程中的数据查询使用，说实话没用过proxy作为项目主要的代理去访问数据，主要考虑点是 1. 增加了运维 2. 性能可能存在损耗
以下是部署proxy的主要过程
版本： proxy 5.2
部署方式：采用容器部署的方式
步骤
准备工作
mkdir -p /home/shardingsphereproxy/{conf,ext-lib}
#拷贝mysql驱动jar文件 mysql5.7.35版本的，使用了 mysql-connector-java-5.1.49.jar 驱动，pg的话不需要拷贝驱动
#驱动拷贝到ext-lib下
server.yaml配置文件：放到 /home/shardingsphereproxy/conf 下
#mode:
# type: Cluster
# repository:
# type: ZooKeeper
# props:
# namespace: governance_ds
# server-lists: localhost:2181
rules:
- !AUTHORITY
users:
- root@%:root
provider:
type: ALL_PERMITTED
props:
max-connections-size-per-query: 1
kernel-executor-size: 16 # Infinite by default.
proxy-frontend-flush-threshold: 128 # The default value is 128.
sql-show: true
具体数据源和分表配置文件：config-sharding-demo.yaml, 放到 /home/shardingsphereproxy/conf 下
databaseName: sharding_db
dataSources:
ds_0:
url: jdbc:mysql://172.18.4.60:3380/demo_ds_0?serverTimezone=UTC&useSSL=false
username: root
password: 55665566ah
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:mysql://172.18.4.60:3380/demo_ds_1?serverTimezone=UTC&useSSL=false
username: root
password: 55665566ah
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_inline
keyGenerateStrategy:
column: order_id
keyGeneratorName: snowflake
auditStrategy:
auditorNames:
- sharding_key_required_auditor
allowHintDisable: true
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_item
defaultDatabaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
defaultTableStrategy:
none:
defaultAuditStrategy:
auditorNames:
- sharding_key_required_auditor
allowHintDisable: true
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
t_order_inline:
type: INLINE
props:
algorithm-expression: t_order_${order_id % 2}
t_order_item_inline:
type: INLINE
props:
algorithm-expression: t_order_item_${order_id % 2}
keyGenerators:
snowflake:
type: SNOWFLAKE
auditors:
sharding_key_required_auditor:
type: DML_SHARDING_CONDITIONS
# scalingName: default_scaling
# scaling:
# default_scaling:
# input:
# workerThread: 40
# batchSize: 1000
# rateLimiter:
# type: QPS
# props:
# qps: 50
# output:
# workerThread: 40
# batchSize: 1000
# rateLimiter:
# type: TPS
# props:
# tps: 2000
# streamChannel:
# type: MEMORY
# props:
# block-queue-size: 10000
# completionDetector:
# type: IDLE
# props:
# incremental-task-idle-seconds-threshold: 1800
# dataConsistencyChecker:
# type: DATA_MATCH
# props:
# chunk-size: 1000
拉取镜像并启动容器：
docker pull apache/shardingsphere-proxy:5.2.0
docker run -d -v /home/shardingsphereproxy/conf:/opt/shardingsphere-proxy/conf -v /home/shardingsphereproxy/ext-lib:/opt/shardingsphere-proxy/ext-lib -p3338:3307 apache/shardingsphere-proxy:5.2.0
如何访问proxy：
1. 命令行访问：
mysql -h127.0.0.1 -P 3338 -uroot -proot
进入之后就像普通的mysql命令行一样使用了
2.Navicat 网上很多说只能11版本使用，那纯属扯淡。
image.png
image.png
