环境(windows,别的环境无需纠结,流程都一样):
shardingpoxy: 4.1.1 ;
mavn 引用:
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.1.1</version>
</dependency>
注意坑点:创建普通的maven项目,不要用spring boot 。原因:后面将spring boot打包成jar放入poxy的lib里启动的话,会报找不到你重写的类
分表(分库原理一样,去官网查看配置写法,略微有区别)
1.重写
PreciseShardingAlgorithm<传参的数据类型(对应自己配置文件中的shardingColumn指定的字段类型,下文会提示)>, RangeShardingAlgorithm<传参的数据类型>两个类,分别对应 sharding poxyd的preciseAlgorithmClassName,rangeAlgorithmClassName的参数字段字段
public class ShardingTable implements PreciseShardingAlgorithm<String>/*, RangeShardingAlgorithm<Long>*/ {
//精确查询
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
//逻辑自己写,根据逻辑判断告诉 shardingpoxy选择那张表 或那个库的作用
//return "ds_0"; //返回配置的数据源别名 或 表的实际名
return "t_order202106";
}
//范围查询
/* @Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {
return null;
}*/
}
2.项目打包成jar,放入shardingpoxy的lib目录内
下载:apache-shardingsphere-4.1.1-sharding-proxy-bin.tar.gz 文件(官网上全部版本里就能找到,Windows、linux都是这个文件,自己根据需求选择版本不同而已)
坑点:解压时,一定要用命令行解压,不然用工具会导致poxy内名字比较长的jar包解压出现问题
tar zxvf apache-shardingsphere-4.1.1-sharding-proxy-bin.tar.gz
3.配置 shardingpoxy
注意:poxy只是代理,如果是动态表,自己根据规则创建动态表
在conf目录内:server.yaml(配置代理端和注册中心)
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: true
allow.range.query.with.inline.sharding: false
config-sharding.yaml(规则配置中心)
schemaName: sharding_db #访问的别名代理数据库(项目或客户端连接的代理数据库)
#
dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC&useSSL=false
username: root
password: root123
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.t_order${2021..2022}${01..12}
tableStrategy:
# inline:
# shardingColumn: order_id
# algorithmExpression: ds_${order_id % 2}
standard:
shardingColumn: creat_time
#对应 自定的规则类名
preciseAlgorithmClassName: com.example.demo.test.ShardingTable
rangeAlgorithmClassName: com.example.demo.test.ModuloShardingTableAlgorithm
# bindingTables:
# - t_order
# defaultTableStrategy:
# none:
config-master_slave.yaml(主从复制的配置,主从需要自己配置)
4.启动
bin文件 (默认连接端口 3307)