ShardingSphere-Proxy
# ShardingSphere-Proxy
# 1. 启动ShardingSphere-Proxy
# 1.1. 获取
目前 ShardingSphere-Proxy 提供了 3 种获取方式:
# 1.2. 使用二进制发布包安装
二进制包既可以Linux系统运行,又可以在windows系统运行
step1:解压二进制包
apache-shardingsphere-5.1.1-shardingsphere-proxy-bin.tar.gz
windows:使用解压软件解压文件
Linux:将文件上传至/opt目录,并解压
tar -zxvf apache-shardingsphere-5.1.1-shardingsphere-proxy-bin.tar.gz
step2:MySQL驱动
mysql-connector-java-8.0.22.jar
将MySQl驱动放至解压目录中的ext-lib
目录
spte3:修改配置conf/server.yaml
rules:
- !AUTHORITY
users:
- root@%:root
provider:
type: ALL_PRIVILEGES_PERMITTED
props:
sql-show: true
2
3
4
5
6
7
8
9
spte4:启动ShardingSphere-Proxy
Linux 操作系统请运行 bin/start.sh
Windows 操作系统请运行 bin/start.bat
指定端口号和配置文件目录:bin/start.bat ${proxy_port} ${proxy_conf_directory}
step5:远程连接ShardingSphere-Proxy
远程访问
mysql -h192.168.100.1 -P3307 -uroot -p
step6:访问测试
show databases;
# 1.3. 使用Docker安装
step1:启动Docker容器
docker run -d \
-v /cnlxc/server/proxy-a/conf:/opt/shardingsphere-proxy/conf \
-v /cnlxc/server/proxy-a/ext-lib:/opt/shardingsphere-proxy/ext-lib \
-e ES_JAVA_OPTS="-Xmx256m -Xms256m -Xmn128m" \
-p 3321:3307 \
--name server-proxy-a \
apache/shardingsphere-proxy:5.1.1
2
3
4
5
6
7
step2:上传MySQL驱动
将MySQl驱动上传至/atguigu/server/proxy-a/ext-lib
目录
spte3:修改配置server.yaml
rules:
- !AUTHORITY
users:
- root@%:root
provider:
type: ALL_PRIVILEGES_PERMITTED
props:
sql-show: true
2
3
4
5
6
7
8
9
将配置文件上传至/atguigu/server/proxy-a/conf
目录
spte4:重启容器
docker restart server-proxy-a
step5:远程连接ShardingSphere-Proxy
ShardingSphere-Proxy容器中默认情况下没有mysql命令行客户端的安装,因此需要远程访问
mysql -h192.168.100.201 -P3321 -uroot -p
step6:访问测试
show databases;
常见问题:docker容器无法远程连接
容器可以成功的创建并启动,但是无法远程连接。排除防火墙和网络等问题后,看看是不是因为容器内存不足导致。
原因:
容器可分配内存不足
查看办法:
进入容器后查看ShardingSphere-Proxy的日志,如有有cannot allocate memory
,则说明容器内存不足
docker exec -it server-proxy-a env LANG=C.UTF-8 /bin/bash
cd /opt/shardingsphere-proxy/logs
tail stdout.log
2
3
解决方案:
创建容器的时候使用JVM参数
-e ES_JAVA_OPTS="-Xmx256m -Xms256m -Xmn128m"
# 2. ShardingSphere-Proxy读写分离
# 2.1. 修改配置文件
修改配置config-readwrite-splitting.yaml
schemaName: readwrite_splitting_db
dataSources:
write_ds:
url: jdbc:mysql://192.168.100.201:3306/db_user?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
read_ds_0:
url: jdbc:mysql://192.168.100.201:3307/db_user?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
read_ds_1:
url: jdbc:mysql://192.168.100.201:3308/db_user?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !READWRITE_SPLITTING
dataSources:
readwrite_ds:
type: Static
props:
write-data-source-name: write_ds
read-data-source-names: read_ds_0,read_ds_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
将配置文件上传至/atguigu/server/proxy-a/conf
目录
重启容器
docker restart server-proxy-a
# 2.2. 实时查看日志
可以通过这种方式查看服务器中输出的SQL语句
docker exec -it server-proxy-a env LANG=C.UTF-8 /bin/bash
tail -f /opt/shardingsphere-proxy/logs/stdout.log
2
# 2.3. 远程访问测试
mysql> show databases;
mysql> use readwrite_splitting_db;
mysql> show tables;
mysql> select * from t_user;
mysql> select * from t_user;
mysql> insert into t_user(uname) values('wang5');
2
3
4
5
6
# 2.4 应用程序访问Proxy
# 代码实现
点击查看
创建项目
项目类型:Spring Initializr
SpringBoot脚手架:http://start.aliyun.com
项目名:sharding-proxy-demo
SpringBoot版本:2.3.7.RELEASE
添加依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
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
创建实体类
@TableName("t_user")
@Data
public class User {
@TableId(type = IdType.AUTO)
private Long id;
private String uname;
}
2
3
4
5
6
7
8
创建Mapper
@Mapper
public interface UserMapper extends BaseMapper<User> {
}
2
3
# 配置数据源
# 应用名称
spring.application.name=sharding-proxy-demo
# 开发环境设置
spring.profiles.active=dev
#mysql数据库连接(proxy)
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://192.168.100.201:3321/readwrite_splitting_db?serverTimezone=GMT%2B8&useSSL=false
spring.datasource.username=root
spring.datasource.password=root
#mybatis日志
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
2
3
4
5
6
7
8
9
10
11
12
13
# 测试
package com.atguigu.shardingproxydemo;
@SpringBootTest
class ShardingProxyDemoApplicationTests {
@Autowired
private UserMapper userMapper;
/**
* 读数据测试
*/
@Test
public void testSelectAll(){
List<User> users = userMapper.selectList(null);
users.forEach(System.out::println);
}
}
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 3. ShardingSphere-Proxy垂直分片
# 3.1. 修改配置文件
修改配置config-sharding.yaml
schemaName: sharding_db
dataSources:
ds_0:
url: jdbc:mysql://192.168.100.201:3301/db_user?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:mysql://192.168.100.201:3302/db_order?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
t_user:
actualDataNodes: ds_0.t_user
t_order:
actualDataNodes: ds_1.t_order
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
# 3.2. 实时查看日志
可以通过这种方式查看服务器中输出的SQL语句
docker exec -it server-proxy-a env LANG=C.UTF-8 /bin/bash
tail -f /opt/shardingsphere-proxy/logs/stdout.log
2
# 3.3. 远程访问测试
mysql> show databases;
mysql> use sharding_db;
mysql> show tables;
mysql> select * from t_order;
mysql> select * from t_user;
2
3
4
5
# 4. ShardingSphere-Proxy水平分片
# 4.1. 修改配置文件
修改配置config-sharding.yaml
schemaName: sharding_db
dataSources:
ds_user:
url: jdbc:mysql://192.168.100.201:3301/db_user?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_order0:
url: jdbc:mysql://192.168.100.201:3310/db_order?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_order1:
url: jdbc:mysql://192.168.100.201:3311/db_order?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
t_user:
actualDataNodes: ds_user.t_user
t_order:
actualDataNodes: ds_order${0..1}.t_order${0..1}
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: alg_mod
tableStrategy:
standard:
shardingColumn: order_no
shardingAlgorithmName: alg_hash_mod
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
t_order_item:
actualDataNodes: ds_order${0..1}.t_order_item${0..1}
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: alg_mod
tableStrategy:
standard:
shardingColumn: order_no
shardingAlgorithmName: alg_hash_mod
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
bindingTables:
- t_order,t_order_item
broadcastTables:
- t_dict
shardingAlgorithms:
alg_inline_userid:
type: INLINE
props:
algorithm-expression: server-order$->{user_id % 2}
alg_mod:
type: MOD
props:
sharding-count: 2
alg_hash_mod:
type: HASH_MOD
props:
sharding-count: 2
keyGenerators:
snowflake:
type: SNOWFLAKE
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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
# 4.2. 实时查看日志
可以通过这种方式查看服务器中输出的SQL语句
docker exec -it server-proxy-a env LANG=C.UTF-8 /bin/bash
tail -f /opt/shardingsphere-proxy/logs/stdout.log
2
# 4.3. 远程访问测试
mysql> show databases;
mysql> use sharding_db;
mysql> show tables;
mysql> select * from t_order; --测试水平分片
mysql> select * from t_dict; --测试广播表
2
3
4
5