使用手册：https://shardingsphere.apache.org/document/legacy/3.x/document/cn/manual/sharding-proxy/usage/
（下载解压、改配置、启动）
Sharding-Proxy分片使用过程
1、下载Sharding-Proxy的最新发行版；
https://shardingsphere.apache.org/document/current/cn/downloads/
2、解压缩后修改conf/server.yaml和以config-前缀开头的文件
进行分片规则、读写分离规则配置
解压后的目录（Linux和Windows都是一样的目录信息）
进入到conf里面，打开之后都是一些模板，根据其格式进行修改参数即可。
3、编辑\conf\server.yaml
配置了这个文件，但是目前和数据库还没有任何关系
####################################################################################################### If you want to configure orchestration, authorization and proxy properties, please refer to this file.######################################################################################################## 连接zookeeper配置中心的（目前不用）#orchestration:# orchestration_ds:# orchestrationType: registry_center,config_center,distributed_lock_manager# instanceType: zookeeper# serverLists: localhost:2181# namespace: orchestration# props:# overwrite: false# retryIntervalMilliseconds: 500# timeToLiveSeconds: 60# maxRetries: 3# operationTimeoutMilliseconds: 500## 认证信息。用来对账号的访问（默认的名字是root，密码现在也设置为root）authentication:users:root:password: rootsharding:#password: shardingauthorizedSchemas: lagou_db #目前是一个逻辑库名，没有与任何地方有对应关系##配置属性props:max.connections.size.per.query: 1 ##一个查询一个连接acceptor.size: 16 # The default value is available processors count * 2. 接收数量executor.size: 16 # Infinite by default. 执行的数量proxy.frontend.flush.threshold: 128 # The default value is 128. 代理# LOCAL: Proxy will run with LOCAL transaction.# XA: Proxy will run with XA transaction.# BASE: Proxy will run with B.A.S.E transaction.proxy.transaction.type: LOCAL ##事物支持proxy.opentracing.enabled: false #打开跟踪proxy.hint.enabled: falsequery.with.cipher.column: true #脱敏字段（true是使用）sql.show: true #显示SQLallow.range.query.with.inline.sharding: false #是否使用范围的
4、编辑conf\config-sharding.yaml
具体的分片操作就要进入到这个config-sharding.yaml里面来进行配置
分库配置如下：
######################################################################################################
#
# Here you can configure the rules for the proxy.
# This example is configuration of sharding rule.
#
# If you want to use sharding, please refer to this file;
# if you want to use master-slave, please refer to the config-master_slave.yaml.
#
######################################################################################################
#
#这个名字和server中配置的要一致（逻辑数据源名称）
schemaName: lagou_db
dataSources: ##配置数据源(可配置多个)
ds_0: #与Sharding-JDBC配置不同，无需配置数据库连接池
url: jdbc:mysql://127.0.0.1:3306/lagou1?serverTimezone=UTC&useSSL=false
username: root #指定好连接的用户密码(和刚刚server的配置一致)
password: root
connectionTimeoutMilliseconds: 30000 #连接超时毫秒数
idleTimeoutMilliseconds: 60000 #空闲连接回收超时毫秒数
maxLifetimeMilliseconds: 1800000 #连接最大存活时间毫秒数
maxPoolSize: 50 #最大连接数
ds_1:
url: jdbc:mysql://127.0.0.1:3306/lagou2?serverTimezone=UTC&useSSL=false
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
##分片规则
shardingRule:
tables:
#对position表操作
position:
#目前是分库，还没分表
actualDataNodes: ds_${0..1}.position
#目前是分库，分表
#actualDataNodes: ds_${0..1}.t_order_${0..1}
#分库策略
databaseStrategy:
#分表策略
#tableStrategy:
inline:
shardingColumn: id
#分库操作
algorithmExpression: ds_${id % 2}
#分表操作
#algorithmExpression: t_order_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: id
##以下其他的配置也是按照这个逻辑配置
# t_order_item:
# actualDataNodes: ds_${0..1}.t_order_item_${0..1}
# tableStrategy:
# inline:
# shardingColumn: order_id
# algorithmExpression: t_order_item_${order_id % 2}
# keyGenerator:
# type: SNOWFLAKE
# column: order_item_id
# bindingTables:
# - t_order,t_order_item
# defaultDatabaseStrategy:
# inline:
# shardingColumn: user_id
# algorithmExpression: ds_${user_id % 2}
# defaultTableStrategy:
# none:
######################################################################################################
#
# If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.
#
######################################################################################################
#schemaName: sharding_db
#
#dataSources:
# ds_0:
# url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
# username: root
# password:
# connectionTimeoutMilliseconds: 30000
# idleTimeoutMilliseconds: 60000
# maxLifetimeMilliseconds: 1800000
# maxPoolSize: 50
# ds_1:
# url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
# username: root
# password:
# connectionTimeoutMilliseconds: 30000
# idleTimeoutMilliseconds: 60000
# maxLifetimeMilliseconds: 1800000
# maxPoolSize: 50
#
#shardingRule:
# tables:
# t_order:
# actualDataNodes: ds_${0..1}.t_order_${0..1}
# tableStrategy:
# inline:
# shardingColumn: order_id
# algorithmExpression: t_order_${order_id % 2}
# keyGenerator:
# type: SNOWFLAKE
# column: order_id
# t_order_item:
# actualDataNodes: ds_${0..1}.t_order_item_${0..1}
# tableStrategy:
# inline:
# shardingColumn: order_id
# algorithmExpression: t_order_item_${order_id % 2}
# keyGenerator:
# type: SNOWFLAKE
# column: order_item_id
# bindingTables:
# - t_order,t_order_item
# defaultDatabaseStrategy:
# inline:
# shardingColumn: user_id
# algorithmExpression: ds_${user_id % 2}
# defaultTableStrategy:
# none:
5、引入依赖jar
a、如果后端连接MySQL数据库，需要下载MySQL驱动， 解压缩后将mysql-connector-java-
5.1.48.jar拷贝到${sharding-proxy}\lib目录。lib中引入驱动包。
b、如果后端连接PostgreSQL数据库，不需要引入额外依赖。
6、启动Sharding-Proxy。
Linux操作系统请运行bin/start.sh，Windows操作系统请运行bin/start.bat启动Sharding-Proxy。
使用默认配置启动：${sharding-proxy}\bin\start.sh
配置端口启动：${sharding-proxy}\bin\start.sh ${port}
其间没有报错信息，就说明启动成功
7、使用客户端工具连接。
使用客户端工具连接。如: mysql -h 127.0.0.1 -P 3307 -u root -p root
若想使用Sharding-Proxy的数据库治理功能，则需要使用注册中心实现实例熔断和从库禁用功能。
Sharding-Proxy默认提供了Zookeeper的注册中心解决方案。只需按照配置规则进行注册中心的配置，即可使用。
查询数据库里面的表的记录
如果插入，其内部则会插入到真实表中
insert into position (name,salary,city) values (‘haha’,’2000000’,’nanning’);
注意：要先查询，后插入，不然报错。我的就是（下面是查询后，插入成功）
使用sharding-proxy，其很多定义全部集中在配置文件中，按其规则进行配置即可。
Sharding-Proxy读写分离使用过程
1、添加读写分离配置信息
修改：config-master_slave.yaml
######################################################################################################
#
# Here you can configure the rules for the proxy.
# This example is configuration of master-slave rule.
#
# If you want to use master-slave, please refer to this file;
# if you want to use sharding, please refer to the config-sharding.yaml.
#
######################################################################################################
##逻辑数据源名称
schemaName: lagou_db
#与数据分片一致
dataSources:
master_ds:
url: jdbc:mysql://127.0.0.1:3306/lagou1?serverTimezone=UTC&useSSL=false
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
slave_ds_0:
url: jdbc:mysql://127.0.0.1:3306/lagou2?serverTimezone=UTC&useSSL=false
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
# slave_ds_1:
# url: jdbc:postgresql://127.0.0.1:5432/demo_ds_slave_1?serverTimezone=UTC&useSSL=false
# username: postgres
# password: postgres
# connectionTimeoutMilliseconds: 30000
# idleTimeoutMilliseconds: 60000
# maxLifetimeMilliseconds: 1800000
# maxPoolSize: 50
#
#省略读写分离配置，与Sharding-JDBC配置一致
masterSlaveRule:
name: ms_ds #默认即可
masterDataSourceName: master_ds #主库
slaveDataSourceNames:
- slave_ds_0 #从库
#- slave_ds_1
######################################################################################################
#
# If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.
#
######################################################################################################
#schemaName: master_slave_db
#
#dataSources:
# master_ds:
# url: jdbc:mysql://127.0.0.1:3306/demo_ds_master?serverTimezone=UTC&useSSL=false
# username: root
# password:
# connectionTimeoutMilliseconds: 30000
# idleTimeoutMilliseconds: 60000
# maxLifetimeMilliseconds: 1800000
# maxPoolSize: 50
# slave_ds_0:
# url: jdbc:mysql://127.0.0.1:3306/demo_ds_slave_0?serverTimezone=UTC&useSSL=false
# username: root
# password:
# connectionTimeoutMilliseconds: 30000
# idleTimeoutMilliseconds: 60000
# maxLifetimeMilliseconds: 1800000
# maxPoolSize: 50
# slave_ds_1:
# url: jdbc:mysql://127.0.0.1:3306/demo_ds_slave_1?serverTimezone=UTC&useSSL=false
# username: root
# password:
# connectionTimeoutMilliseconds: 30000
# idleTimeoutMilliseconds: 60000
# maxLifetimeMilliseconds: 1800000
# maxPoolSize: 50
#
#masterSlaveRule:
# name: ms_ds
# masterDataSourceName: master_ds
# slaveDataSourceNames:
# - slave_ds_0
# - slave_ds_1
2、启动Sharding-Proxy
注意：config-*配置文件中的数据源名称不能出现相同名字，必须是唯一的，否则报错
启动没有报错信息
3、登录客户端进行测试验证
mysql -h 127.0.0.1 -P 3307 -u root -p
然后要使用数据库、再查询表、再执行插入语句（我开始没查表就报错，查了之后就OK）
insert into position (name,salary,city) values (‘tom’,’2000000’,’nanning’);
成功插入到主库中的结果展示
查询时是往从库出来的
查询的结果显示是从库的数据信息
Sharding-Proxy数据脱敏使用过程
1、添加脱敏配置信息
修改config-encrypt.yaml（目前是对主库操作而已）
如果数据库已经加密了，秘钥一定要一致，否则报错
######################################################################################################
#
# Here you can configure the rules for the proxy.
# This example is configuration of encrypt rule.
#
# If you want to use encrypt, please refer to this file;
# if you want to use sharding, please refer to the config-sharding.yaml.
# if you want to use master-slave, please refer to the config-master_slave.yaml.
#
######################################################################################################
#
schemaName: Blagou_db
dataSource:
url: jdbc:mysql://127.0.0.1:3306/lagou1?serverTimezone=UTC&useSSL=false
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
#脱敏规则
encryptRule:
encryptors:
encryptor_aes:
type: aes
props:
aes.key.value: 1234 #如果数据库存有了加密数据则秘钥一定要一致，否则各种报错
# encryptor_md5:
# type: md5
tables:
#对c_user表进行操作
c_user:
columns:
pwd:
plainColumn: pwd_plain #明文列
cipherColumn: pwd_cipher #密文列
encryptor: encryptor_aes
# order_id:
# cipherColumn: order_encrypt
# encryptor: encryptor_md5
######################################################################################################
#
# If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.
#
######################################################################################################
#
#schemaName: encrypt_db
#
#dataSource:
# url: jdbc:mysql://127.0.0.1:3306/demo_ds?serverTimezone=UTC&useSSL=false
# username: root
# password:
# connectionTimeoutMilliseconds: 30000
# idleTimeoutMilliseconds: 60000
# maxLifetimeMilliseconds: 1800000
# maxPoolSize: 50
#
#encryptRule:
# encryptors:
# encryptor_aes:
# type: aes
# props:
# aes.key.value: 123456abc
# encryptor_md5:
# type: md5
# tables:
# t_encrypt:
# columns:
# user_id:
# plainColumn: user_plain
# cipherColumn: user_cipher
# encryptor: encryptor_aes
# order_id:
# cipherColumn: order_cipher
# encryptor: encryptor_md5
2、启动Sharding-Proxy
注意：config-*配置文件中的数据源名称不能出现相同名字，必须是唯一的，否则报错
启动没有报错信息
3、登录客户端进行测试验证
mysql -h 127.0.0.1 -P 3307 -u root -p
insert into c_user (id,name,pwd) values (2,’halo’,’143’);
查询结果展示
若有收获，就点个赞吧
0 人点赞
