一般情況下尽爆，java項(xiàng)目試用Sharding-jdbc做分庫分表巴元，但是開發(fā)過程中需要查看數(shù)據(jù)舵盈，分表后很不方便陋率，所以建議采用部署一個(gè)proxy來用于開發(fā)過程中的數(shù)據(jù)查詢使用球化，說實(shí)話沒用過proxy作為項(xiàng)目主要的代理去訪問數(shù)據(jù)，主要考慮點(diǎn)是 1. 增加了運(yùn)維 2. 性能可能存在損耗
以下是部署proxy的主要過程
版本： proxy 5.2
部署方式：采用容器部署的方式
步驟
準(zhǔn)備工作
mkdir -p /home/shardingsphereproxy/{conf,ext-lib}
#拷貝mysql驅(qū)動(dòng)jar文件 mysql5.7.35版本的瓦糟，使用了 mysql-connector-java-5.1.49.jar 驅(qū)動(dòng)筒愚，pg的話不需要拷貝驅(qū)動(dòng)
#驅(qū)動(dòng)拷貝到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
具體數(shù)據(jù)源和分表配置文件：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
拉取鏡像并啟動(dòng)容器：
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
進(jìn)入之后就像普通的mysql命令行一樣使用了
2.Navicat 網(wǎng)上很多說只能11版本使用，那純屬扯淡菩浙。
image.png
image.png
