0101prox-shardingsphere-中间件
1 启动ShardingSphere-Proxy
1.1 获取
目前 ShardingSphere-Proxy 提供了 3 种获取方式：
- 二进制发布包
- Docker
- Helm
这里我们使用Docker安装。
1.2 使用Docker安装
step1：启动Docker容器
docker run -d \
-v /Users/gaogzhen/data/docker/shardingsphere/proxy/proxy-a/conf:/opt/shardingsphere-proxy/conf \
-v /Users/gaogzhen/data/docker/shardingsphere/proxy/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.1docker run -d -e ES_JAVA_OPTS="-Xmx256m -Xms256m -Xmn128m" -p 3322:3307 --name server-proxy-a1 apache/shardingsphere-proxy:5.1.1
step2：上传MySQL驱动
将MySQl驱动上传至/Users/gaogzhen/data/docker/shardingsphere/proxy/proxy-a/ext-lib目录
spte3：修改配置server.yaml
rules:- !AUTHORITYusers:- root@%:rootprovider:type: ALL_PRIVILEGES_PERMITTEDprops:sql-show: true
将配置文件上传至/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
解决方案：创建容器的时候使用JVM参数
-e ES_JAVA_OPTS="-Xmx256m -Xms256m -Xmn128m"
2 ShardingSphere-Proxy读写分离
2.1 修改配置文件
修改配置config-readwrite-splitting.yaml
schemaName: readwrite_splitting_dbdataSources:write_ds:url: jdbc:mysql://172.17.0.2:3306/db_user?serverTimezone=UTC&useSSL=falseusername: rootpassword: 123456connectionTimeoutMilliseconds: 30000idleTimeoutMilliseconds: 60000maxLifetimeMilliseconds: 1800000maxPoolSize: 50minPoolSize: 1read_ds_0:url: jdbc:mysql://172.17.0.3:3306/db_user?serverTimezone=UTC&useSSL=falseusername: rootpassword: 123456connectionTimeoutMilliseconds: 30000idleTimeoutMilliseconds: 60000maxLifetimeMilliseconds: 1800000maxPoolSize: 50minPoolSize: 1read_ds_1:url: jdbc:mysql://172.17.0.4:3306/db_user?serverTimezone=UTC&useSSL=falseusername: rootpassword: 123456connectionTimeoutMilliseconds: 30000idleTimeoutMilliseconds: 60000maxLifetimeMilliseconds: 1800000maxPoolSize: 50minPoolSize: 1rules:
- !READWRITE_SPLITTINGdataSources:readwrite_ds:type: Staticprops:write-data-source-name: write_dsread-data-source-names: read_ds_0,read_ds_1
- 这里我的数据源IP地址使用的docker默认网络分配的地址
将配置文件上传至/Users/gaogzhen/data/docker/shardingsphere/proxy/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.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(name) values('李四');
如下图2.3-1所示：
2.4 应用程序访问Proxy
4.1、创建项目
项目类型：Spring Initializr
SpringBoot脚手架：http://start.aliyun.com
项目名：sharding-proxy-demo
SpringBoot版本：2.7.6
4.2、添加依赖
<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>
4.3、创建实体类
package com.gaogzhen.shardingsphereproxydemo.entity;@TableName("t_user")
@Data
public class User {@TableId(type = IdType.AUTO)private Long id;private String uname;
}4.4、创建Mapper
package com.gaogzhen.shardingsphereproxydemo.mapper;@Mapper
public interface UserMapper extends BaseMapper<User> {
}
4.5、配置数据源
# 应用名称
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://127.0.0.1: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
4.6、测试
package com.gaogzhen.shardingsphereproxydemo;import com.gaogzhen.shardingsphereproxydemo.entity.User;
import com.gaogzhen.shardingsphereproxydemo.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;import java.util.List;@SpringBootTest
class ShardingsphereProxyDemoApplicationTests {@Autowiredprivate UserMapper userMapper;/*** 读数据测试*/@Testpublic void testSelectAll(){List<User> users = userMapper.selectList(null);users.forEach(System.out::println);}}
3 ShardingSphere-Proxy垂直分片
3.1 修改配置文件
修改配置config-sharding.yaml
schemaName: sharding_dbdataSources:ds_0:url: jdbc:mysql://172.17.0.2:3301/db_user?serverTimezone=UTC&useSSL=falseusername: rootpassword: 123456connectionTimeoutMilliseconds: 30000idleTimeoutMilliseconds: 60000maxLifetimeMilliseconds: 1800000maxPoolSize: 50minPoolSize: 1ds_1:url: jdbc:mysql://172.17.0.3:3302/db_order?serverTimezone=UTC&useSSL=falseusername: rootpassword: 123456connectionTimeoutMilliseconds: 30000idleTimeoutMilliseconds: 60000maxLifetimeMilliseconds: 1800000maxPoolSize: 50minPoolSize: 1rules:
- !SHARDINGtables:t_user:actualDataNodes: ds_0.t_usert_order:actualDataNodes: ds_1.t_order
3.2 实时查看日志
可以通过这种方式查看服务器中输出的SQL语句
docker exec -it server-proxy-a env LANG=C.UTF-8 /bin/bash
tail -f /opt/shardingsphere-proxy/logs/stdout.log
3.3 远程访问测试
mysql> show databases;
mysql> use sharding_db;
mysql> show tables;
mysql> select * from t_order;
mysql> select * from t_user;
4 ShardingSphere-Proxy水平分片
4.1 修改配置文件
修改配置config-sharding.yaml
schemaName: sharding_dbdataSources:ds_user:url: jdbc:mysql://172.17.0.2:3301/db_user?serverTimezone=UTC&useSSL=falseusername: rootpassword: 123456connectionTimeoutMilliseconds: 30000idleTimeoutMilliseconds: 60000maxLifetimeMilliseconds: 1800000maxPoolSize: 50minPoolSize: 1ds_order0:url: jdbc:mysql://172.17.0.3:3310/db_order?serverTimezone=UTC&useSSL=falseusername: rootpassword: 123456connectionTimeoutMilliseconds: 30000idleTimeoutMilliseconds: 60000maxLifetimeMilliseconds: 1800000maxPoolSize: 50minPoolSize: 1ds_order1:url: jdbc:mysql://172.17.0.4:3311/db_order?serverTimezone=UTC&useSSL=falseusername: rootpassword: 123456connectionTimeoutMilliseconds: 30000idleTimeoutMilliseconds: 60000maxLifetimeMilliseconds: 1800000maxPoolSize: 50minPoolSize: 1rules:
- !SHARDINGtables:t_user:actualDataNodes: ds_user.t_usert_order:actualDataNodes: ds_order${0..1}.t_order${0..1}databaseStrategy:standard:shardingColumn: user_idshardingAlgorithmName: alg_modtableStrategy:standard:shardingColumn: order_noshardingAlgorithmName: alg_hash_modkeyGenerateStrategy:column: idkeyGeneratorName: snowflaket_order_item:actualDataNodes: ds_order${0..1}.t_order_item${0..1}databaseStrategy:standard:shardingColumn: user_idshardingAlgorithmName: alg_modtableStrategy:standard:shardingColumn: order_noshardingAlgorithmName: alg_hash_modkeyGenerateStrategy:column: idkeyGeneratorName: snowflakebindingTables:- t_order,t_order_itembroadcastTables:- t_dictshardingAlgorithms:alg_inline_userid:type: INLINEprops:algorithm-expression: server-order$->{user_id % 2}alg_mod:type: MODprops:sharding-count: 2alg_hash_mod:type: HASH_MODprops:sharding-count: 2keyGenerators:snowflake:type: SNOWFLAKE4.2 实时查看日志
可以通过这种方式查看服务器中输出的SQL语句
docker exec -it server-proxy-a env LANG=C.UTF-8 /bin/bash
tail -f /opt/shardingsphere-proxy/logs/stdout.log
4.3 远程访问测试
mysql> show databases;
mysql> use sharding_db;
mysql> show tables;
mysql> select * from t_order; --测试水平分片
mysql> select * from t_dict; --测试广播表
如下图4.3.-1所示：
结语
如果小伙伴什么问题或者指教，欢迎交流。
QQ:806797785
仓库源代码地址：https://gitee.com/gaogzhen/shardingsphere-proxy-demo.git
参考链接:
[1]ShardingSphere5实战教程[CP/OL].2022-09-14.p18-23.
[2]0101读写分离测试-jdbc-shardingsphere-中间件[CP/OL].
[3]0102垂直分片-jdbc-shardingsphere[CP/OL].
[4]0103水平分片-jdbc-shardingsphere[CP/OL].