sharding proxy下载地址：https://archive.apache.org/dist/shardingsphere/
下载之后需要在lib目录下放一个mysql的驱动jar包
分库分表
配置
sharding-proxy安装目录/conf/config-sharding.yaml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
| databaseName: sharding
dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/sharding_db?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:mysql://127.0.0.1:3316/sharding_db?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
t_user_sharding:
actualDataNodes: ds_${0..1}.t_user_sharding${0..1}
tableStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: t_user_inline
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
bindingTables:
- t_user_sharding
defaultDatabaseStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: database_inline
defaultTableStrategy:
none:
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${id % 2}
t_user_inline:
type: INLINE
props:
algorithm-expression: t_user_${id % 2}
keyGenerators:
snowflake:
type: SNOWFLAKE
auditors:
sharding_key_required_auditor:
type: DML_SHARDING_CONDITIONS
|
- dataSources：配置的实际的节点，可以配置到库，mycat只能配置到连接
- rules：配置规则
- rules.tables：定义表以及表的相关配置，有些配置没有实际定义，需要从下面的策略中选择
- rules.bindingTables：配置表，从上述定义表中选择
- rules.defaultDatabaseStrategy：配置默认的分库策略，这里只是配置，但没有定义实际的策略，实际的策略从下面的配置选择
- rules.shardingAlgorithms：定义实际的分库分表策略，就是上面配置的
- rules.keyGenerators：主键生成策略，就是上面配置的
Java端测试
这里用的是sharding proxy5.4.0版本，如果用sharding proxy5.4.0之前的版本，会出现“Unknown system variable ‘query_cache_size’”错误
读写分离
配置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
| databaseName: read_write
dataSources:
write_ds:
url: jdbc:mysql://127.0.0.1:3306/write_read?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
read_ds_0:
url: jdbc:mysql://127.0.0.1:3316/write_read?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !READWRITE_SPLITTING
dataSources:
readwrite_ds:
writeDataSourceName: write_ds
readDataSourceNames:
- read_ds_0
loadBalancerName: random
loadBalancers:
random:
type: RANDOM
- !SHARDING
tables:
t_user_sharding:
actualDataNodes: write_ds.t_user_sharding
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
bindingTables:
- t_user_sharding
keyGenerators:
snowflake:
type: SNOWFLAKE
|
这里除了用到读写分离策略，还用到了分库分表策略（实际配置并没有分库分表），是为了自动生成id
Java端测试
Sharding JDBC
sharding jdbc可以理解为sharding proxy本地版，不需要再引入中间件添加系统复杂性了
sharding配置
这里的配置可以直接用sharding proxy conf目录下的配置，两者是同一个东西
数据源配置
1
2
3
4
|
spring.datasource.driver-class-name=org.apache.shardingsphere.driver.ShardingSphereDriver
spring.datasource.url=jdbc:shardingsphere:classpath:config-sharding.yaml
|
这里不需要配置账号和密码，url使用上面的sharding配置，驱动使用sharding自带的驱动即可
Java端测试
