南宁seo推广经验,广州百度seo公司,网站被百度k,做魔杖网站1 启动ShardingSphere-Proxy
1.1 获取
目前 ShardingSphere-Proxy 提供了 3 种获取方式#xff1a;
二进制发布包DockerHelm
这里我们使用Docker安装。
1.2 使用Docker安装
step1#xff1a;启动Docker容器
docker run -d \
-v /Users/gaogzhen/data/docker/shardings…1 启动ShardingSphere-Proxy
1.1 获取
目前 ShardingSphere-Proxy 提供了 3 种获取方式
二进制发布包DockerHelm
这里我们使用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.1step2上传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-astep5远程连接ShardingSphere-Proxy
ShardingSphere-Proxy容器中默认情况下没有mysql命令行客户端的安装因此需要远程访问
mysql -h192.168.100.201 -P3321 -uroot -pstep6访问测试
show databases;常见问题docker容器无法远程连接
容器可以成功的创建并启动但是无法远程连接。排除防火墙和网络等问题后看看是不是因为容器内存不足导致。
原因容器可分配内存不足
查看办法进入容器后查看ShardingSphere-Proxy的日志如有有cannot allocate memory则说明容器内存不足
docker exec -it server-proxy-a env LANGC.UTF-8 /bin/bash
cd /opt/shardingsphere-proxy/logs
tail stdout.log 解决方案创建容器的时候使用JVM参数
-e ES_JAVA_OPTS-Xmx256m -Xms256m -Xmn128m2 ShardingSphere-Proxy读写分离
2.1 修改配置文件
修改配置config-readwrite-splitting.yaml
schemaName: readwrite_splitting_dbdataSources:write_ds:url: jdbc:mysql://172.17.0.2:3306/db_user?serverTimezoneUTCuseSSLfalseusername: rootpassword: 123456connectionTimeoutMilliseconds: 30000idleTimeoutMilliseconds: 60000maxLifetimeMilliseconds: 1800000maxPoolSize: 50minPoolSize: 1read_ds_0:url: jdbc:mysql://172.17.0.3:3306/db_user?serverTimezoneUTCuseSSLfalseusername: rootpassword: 123456connectionTimeoutMilliseconds: 30000idleTimeoutMilliseconds: 60000maxLifetimeMilliseconds: 1800000maxPoolSize: 50minPoolSize: 1read_ds_1:url: jdbc:mysql://172.17.0.4:3306/db_user?serverTimezoneUTCuseSSLfalseusername: 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-a2.2 实时查看日志
可以通过这种方式查看服务器中输出的SQL语句
docker exec -it server-proxy-a env LANGC.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、添加依赖
dependenciesdependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactId/dependencydependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdscoperuntime/scope/dependencydependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-boot-starter/artifactIdversion3.3.1/version/dependencydependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactIdoptionaltrue/optional/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-test/artifactIdscopetest/scopeexclusionsexclusiongroupIdorg.junit.vintage/groupIdartifactIdjunit-vintage-engine/artifactId/exclusion/exclusions/dependency
/dependencies4.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 BaseMapperUser {
}4.5、配置数据源
# 应用名称
spring.application.namesharding-proxy-demo
# 开发环境设置
spring.profiles.activedev#mysql数据库连接proxy
spring.datasource.driver-class-namecom.mysql.jdbc.Driver
spring.datasource.urljdbc:mysql://127.0.0.1:3321/readwrite_splitting_db?serverTimezoneGMT%2B8useSSLfalse
spring.datasource.usernameroot
spring.datasource.passwordroot#mybatis日志
mybatis-plus.configuration.log-implorg.apache.ibatis.logging.stdout.StdOutImpl4.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(){ListUser 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?serverTimezoneUTCuseSSLfalseusername: rootpassword: 123456connectionTimeoutMilliseconds: 30000idleTimeoutMilliseconds: 60000maxLifetimeMilliseconds: 1800000maxPoolSize: 50minPoolSize: 1ds_1:url: jdbc:mysql://172.17.0.3:3302/db_order?serverTimezoneUTCuseSSLfalseusername: rootpassword: 123456connectionTimeoutMilliseconds: 30000idleTimeoutMilliseconds: 60000maxLifetimeMilliseconds: 1800000maxPoolSize: 50minPoolSize: 1rules:
- !SHARDINGtables:t_user:actualDataNodes: ds_0.t_usert_order:actualDataNodes: ds_1.t_order3.2 实时查看日志
可以通过这种方式查看服务器中输出的SQL语句
docker exec -it server-proxy-a env LANGC.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?serverTimezoneUTCuseSSLfalseusername: rootpassword: 123456connectionTimeoutMilliseconds: 30000idleTimeoutMilliseconds: 60000maxLifetimeMilliseconds: 1800000maxPoolSize: 50minPoolSize: 1ds_order0:url: jdbc:mysql://172.17.0.3:3310/db_order?serverTimezoneUTCuseSSLfalseusername: rootpassword: 123456connectionTimeoutMilliseconds: 30000idleTimeoutMilliseconds: 60000maxLifetimeMilliseconds: 1800000maxPoolSize: 50minPoolSize: 1ds_order1:url: jdbc:mysql://172.17.0.4:3311/db_order?serverTimezoneUTCuseSSLfalseusername: 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 LANGC.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].
