ShardingProxy
获取资源包
- 下载地址 ShardingProxy
- 当前使用版本
4.1.1
安装
apache-shardingsphere-4.1.1-sharding-proxy-bin.tar.gz
- 解压即可
- 目录说明
- bin : 启动脚本
- start.bat : window 启动脚本
- start.sh : linux 启动脚本
- stop.sh : linux 停止脚本
- conf：配置文件
- server.xml : 用于配置基本的服务器信息。
- config-sharding.yaml：用于配置分库分表的策略。
- config-encrypt.yaml：用于数据加密配置。
- config-master_slave.yaml： 用于主从读写分离配置。
- config-shadow.yaml：影子库配置（多用于压测）。
- logback.xml：日志规则配置。
- lib: 运行所需的 Java 类库。
- bin : 启动脚本
基础配置
- server.xml
#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements. See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
######################################################################################################
#
# If you want to configure orchestration, authorization and proxy properties, please refer to this file.
#
######################################################################################################
#
#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
#
#authentication:
# users:
# root:
# password: root
# sharding:
# password: sharding
# authorizedSchemas: sharding_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: false
# query.with.cipher.column: true
# sql.show: false
# allow.range.query.with.inline.sharding: false
authentication:
users:
root:
password: root
sharding:
password: sharding
authorizedSchemas: proxy_demo
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: false
query.with.cipher.column: true
sql.show: false
allow.range.query.with.inline.sharding: false
- MySQL 连接 Java 类库
复制 MySQL连接jar包 到 lib 目录下
分表
分表规则配置
config-sharding.yaml
#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements. See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
######################################################################################################
#
# 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.
#
######################################################################################################
#
#schemaName: sharding_db
#
#dataSources:
# ds_0:
# url: jdbc:postgresql://127.0.0.1:5432/demo_ds_0?serverTimezone=UTC&useSSL=false
# username: postgres
# password: postgres
# connectionTimeoutMilliseconds: 30000
# idleTimeoutMilliseconds: 60000
# maxLifetimeMilliseconds: 1800000
# maxPoolSize: 50
# ds_1:
# url: jdbc:postgresql://127.0.0.1:5432/demo_ds_1?serverTimezone=UTC&useSSL=false
# username: postgres
# password: postgres
# 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:
######################################################################################################
#
# If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.
#
######################################################################################################
schemaName: proxy_demo
dataSources:
ds_1:
url: jdbc:mysql://192.168.40.128:3306/db_demo?serverTimezone=UTC&useSSL=false
username: root
password: Root@123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
shardingRule:
tables:
user:
actualDataNodes: ds_${1}.user_${1..2}
tableStrategy:
inline:
shardingColumn: id
# 分表策略 id奇数存到user_2 偶数存储到user_1
algorithmExpression: user_${id % 2 + 1}
keyGenerator:
type: SNOWFLAKE
column: id
bindingTables:
- user
defaultDatabaseStrategy:
inline:
shardingColumn: client_id
algorithmExpression: ds_${1}
defaultTableStrategy:
none:
依赖表
- 原始状态
启动 proxy
双击 start.bat
连接proxy
查看数据库
show databases;
只有一个库 proxy_demo
使用数据库
use proxy_demo;
创建表
- 建表语句
CREATE TABLE `user` (
`id` bigint(20) NOT NULL,
`client_id` bigint(20) NOT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;
- 执行情况
查看表
show tables;
只有一张表 user
真实库查看
和配置 actualDataNodes: ds_${1}.user_${1…2} 完全吻合。
插入数据
ID奇数
- 插入数据
INSERT INTO `user` (`id`, `client_id`, `name`, `age`) VALUES (1, 10000, 'user_1', 18);
- 效果查看
- 奇偶规则：奇数应插入
user_2
- 奇偶规则：奇数应插入
ID偶数
- 插入数据
INSERT INTO `user` (`id`, `client_id`, `name`, `age`) VALUES (2, 10000, 'user_2', 20);
- 效果查看
- 奇偶规则：偶数应插入
user_1
- 奇偶规则：偶数应插入
分库
分库规则配置
config-sharding.yaml
#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements. See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
######################################################################################################
#
# 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.
#
######################################################################################################
#
#schemaName: sharding_db
#
#dataSources:
# ds_0:
# url: jdbc:postgresql://127.0.0.1:5432/demo_ds_0?serverTimezone=UTC&useSSL=false
# username: postgres
# password: postgres
# connectionTimeoutMilliseconds: 30000
# idleTimeoutMilliseconds: 60000
# maxLifetimeMilliseconds: 1800000
# maxPoolSize: 50
# ds_1:
# url: jdbc:postgresql://127.0.0.1:5432/demo_ds_1?serverTimezone=UTC&useSSL=false
# username: postgres
# password: postgres
# 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:
######################################################################################################
#
# If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.
#
######################################################################################################
schemaName: proxy_demo
dataSources:
ds_1:
url: jdbc:mysql://192.168.40.128:3306/db_demo_1?serverTimezone=UTC&useSSL=false
username: root
password: Root@123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
ds_2:
url: jdbc:mysql://192.168.40.128:3306/db_demo_2?serverTimezone=UTC&useSSL=false
username: root
password: Root@123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
shardingRule:
tables:
user:
actualDataNodes: ds_${1..2}.user_${1..2}
tableStrategy:
inline:
shardingColumn: id
# 分表策略 id奇数存到user_2 偶数存储到user_1
algorithmExpression: user_${id % 2 + 1}
keyGenerator:
type: SNOWFLAKE
column: id
bindingTables:
- user
defaultDatabaseStrategy:
inline:
shardingColumn: client_id
# 分库策略 client_id奇数存到db_demo_2 偶数存储到db_demo_1
algorithmExpression: ds_${client_id % 2 + 1}
defaultTableStrategy:
none:
依赖库
- 创建
db_demo_1、db_demo_2
启动 proxy
双击 start.bat
连接proxy
查看数据库
show databases;
只有一个库 proxy_demo
使用数据库
use proxy_demo;
创建表
- 建表语句
CREATE TABLE `user` (
`id` bigint(20) NOT NULL,
`client_id` bigint(20) NOT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;
- 执行情况
查看表
show tables;
只有一张表 user
真实库查看
和配置 actualDataNodes: ds_${1…2}.user_${1…2} 完全吻合。
插入数据
ID奇数 & 客户端ID偶数
- 插入数据
INSERT INTO `user` (`id`, `client_id`, `name`, `age`) VALUES (1, 10000, 'user_1', 18);
- 效果查看
- 库奇偶规则：偶数应插入库
db_demo_1 - 表奇偶规则：奇数应插入表
user_2 - 因此记录应存在于
db_demo_1的user_2表中
- 库奇偶规则：偶数应插入库
ID偶数 & 客户端ID奇数数
- 插入数据
INSERT INTO `user` (`id`, `client_id`, `name`, `age`) VALUES (2, 10001, 'user_2', 20);
- 效果查看
- 库奇偶规则：奇数应插入库
db_demo_2 - 表奇偶规则：偶数应插入表
user_1 - 因此记录应存在于
db_demo_2的user_1表中
- 库奇偶规则：奇数应插入库
