宁波市政务网站建设和管理标准/单页网站设计

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: SNOWFLAKE

4.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].