文章目录
一、Sharding-Proxy简介
Sharding-Proxy是ShardingSphere的第二个产品,定位为透明化的数据库代理端,提供封装了数据库二进制协议的服务端版本,用于完成对异构语言的支持。 目前先提供MySQL版本,它可以使用任何兼容MySQL协议的访问客户端(如:MySQL Command Client, MySQL Workbench等操作数据,对DBA更加友好。
- 向应用程序完全透明,可直接当做MySQL使用
- 适用于任何兼容MySQL协议的客户端
Sharding-Proxy的优势在于对异构语言的支持,以及为DBA提供可操作入口。
二、Sharding-Proxy使用过程
注意事项:Sharding-Proxy 需要在JDK8的环境下使用,我在配置过程中使用JDK11启动会报错
2.1 获取
目前Sharding-Proxy提供了3种获取方式
- 二进制发布包
- Docker
- Helm
这里就介绍前两种。
2.2 使用二进制发布包安装
二进制包既可以Linux系统运行,又可以在Windows系统运行。
2.2.1 解压二进制包
- 按照所需版本下载,这里是5.1.1
- windows:使用解压软件解压文件。
- Linux:将文件上传至
/opt
目录,并解压
tar -zxvf apache-shardingsphere-5.1.1-shardingsphere-proxy-bin.tar.gz
2.2.2 引入依赖jar
如果后端连接MySQL数据库,需要下载MySQL驱动, 解压缩后将mysql-connector-java-5.1.49.jar拷贝到${sharding-proxy}\ext-lib目录。
如果后端连接PostgreSQL数据库,不需要引入额外依赖。
2.2.3 修改配置conf/server.yaml
2.2.4 启动
- Linux操作系统请运行bin/start.sh,
- Windows操作系统请运行bin/start.bat启动Sharding-Proxy。
- 使用默认配置启动:${sharding-proxy}\bin\start.sh
- 配置端口启动:${sharding-proxy}\bin\start.sh ${port}。默认端口是3307。
2.2.4 连接
使用客户端工具连接。如: mysql -h 127.0.0.1 -P 3307 -u root -p root
2.3 使用docker安装
2.3.1 启动docker容器
docker run -d \
-v /dhx/shardingproxy/conf:/opt/shardingsphere-proxy/conf \
-v /dhx/shardingproxy/ext-lib:/opt/shardingsphere-proxy/ext-lib \
-e ES_JAVA_OPTS="-Xmx256m -Xms256m -Xmn128m" \
-p 3321:3307 \
--name shardingproxy \
apache/shardingsphere-proxy:5.1.1
2.3.2 上传mysql驱动
将 mysql-connector-java-8.0.22.jar
驱动上传至/dhx/shardingproxy/ext-lib
目录下
2.3.3 修改server.yaml
rules:
- !AUTHORITY
users:
- root@%:root
provider:
type: ALL_PRIVILEGES_PERMITTED
props:
sql-show: true
将配置文件放置在/dhx/shardingproxy/conf
目录下
(注意:这里的- !AUTHORITY
坚决不能删除,我擅自将其删除,导致shardingsphere-proxy
总是无法正常启动)
2.3.4 启动容器
docker restart shardingproxy
验证
- 前往容器内部的
/opt/shardingsphere-proxy/logs/stdout.log
文件查看日志 - 使用
docker logs shardingproxy
查看实时日志。
......
06:41:10.343 [main] DEBUG io.netty.buffer.PooledByteBufAllocator - -Dio.netty.allocator.useCacheForAllThreads: true
06:41:10.343 [main] DEBUG io.netty.buffer.PooledByteBufAllocator - -Dio.netty.allocator.maxCachedByteBuffersPerChunk: 1023
06:41:10.384 [main] DEBUG io.netty.channel.DefaultChannelId - -Dio.netty.processId: 38 (auto-detected)
06:41:10.386 [main] DEBUG io.netty.channel.DefaultChannelId - -Dio.netty.machineId: 02:42:ac:ff:fe:11:00:02 (auto-detected)
06:41:10.430 [main] DEBUG io.netty.buffer.ByteBufUtil - -Dio.netty.allocator.type: pooled
06:41:10.430 [main] DEBUG io.netty.buffer.ByteBufUtil - -Dio.netty.threadLocalDirectBufferSize: 0
06:41:10.430 [main] DEBUG io.netty.buffer.ByteBufUtil - -Dio.netty.maxThreadLocalCharBufferSize: 16384
06:41:10.456 [epollEventLoopGroup-2-1] INFO io.netty.handler.logging.LoggingHandler - [id: 0xb34a76d5] REGISTERED
06:41:10.459 [epollEventLoopGroup-2-1] INFO io.netty.handler.logging.LoggingHandler - [id: 0xb34a76d5] BIND: 0.0.0.0/0.0.0.0:3307
06:41:10.461 [epollEventLoopGroup-2-1] INFO io.netty.handler.logging.LoggingHandler - [id: 0xb34a76d5, L:/0.0.0.0:3307] ACTIVE
06:41:10.462 [main] INFO org.apache.shardingsphere.proxy.frontend.ShardingSphereProxy - ShardingSphere-Proxy Memory mode started successfully
有打印started successfully
则启动成功!
2.3.5 远程连接
mysql -h 192.xxx.xxx.xxx -P 3321 -u root -p
show databases;
三、Sharding-Proxy读写分离
3.1 修改配置config-readwrite-splitting.yaml
# 配置逻辑数据库名
schemaName: readwrite_splitting_db
dataSources:
# 主库
write_ds:
url: jdbc:mysql://localhost:3307/test?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
# 从库0
read_ds_0:
url: jdbc:mysql://localhost:3308/test?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
重启容器
docker restart shardingproxy
3.2 实时查看日志
可以通过这种方式查看服务器中输出的SQL语句
docker exec -it shardingproxy env LANG=C.UTF8 /bin/bash
tail -f /opt/shardingsphere-proxy/logs/stdout.log
3.3 远程访问测试
如果cmd窗口不能显示中文
然后在窗口中输入chcp 65001
(65001代表utf-8编码),然后回车,即可看到窗口默认编码为utf-8编码了,但是此方法是临时的,只有当前窗口有效。
#连接数据库
mysql -h 192.xxx.xxx.xxx -P 3321 -u root -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_order |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.01 sec)
mysql> use readwrite_splitting_db;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| dept |
| t_user |
+----------------+
2 rows in set (0.01 sec)
mysql> select * from t_user;
+----+-----------------+
| id | name |
+----+-----------------+
| 3 | 大锤 |
| 4 | 我是王大锤 |
+----+-----------------+
2 rows in set (0.02 sec)
四、使用springboot访问Proxy
4.1 创建工程
基于之前使用的工程,新建一个子工程sharding-proxy-demo
4.2 添加依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--日志测试~-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<version>2.6.3</version>
<scope>test</scope>
</dependency>
<!-- mybatis-plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.2</version>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
</dependencies>
4.3 yaml配置文件
#====================基本配置
# 应用名称
spring.application.name=sharding-jdbc-demo
#开发环境设置
spring.profiles.active=dev
#mysql数据库连接
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://192.168.137.144: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.4 实体类
@Data
@TableName("t_user") //@TableName中的值对应着表名
public class User {
/**
* 主键
* @TableId中可以决定主键的类型,不写会采取默认值,默认值可以在yml中配置
* AUTO: 数据库ID自增
* INPUT: 用户输入ID
* ID_WORKER: 全局唯一ID,Long类型的主键
* ID_WORKER_STR: 字符串全局唯一ID
* UUID: 全局唯一ID,UUID类型的主键
* NONE: 该类型为未设置主键类型
*/
@TableId(type = IdType.AUTO)
private Long id;
private String name;
}
4.5 Mapper
@Mapper
public interface UserMapper extends BaseMapper<User> {
}
4.6 测试
@SpringBootTest
public class ProxyReadWriteTest {
@Autowired
private UserMapper userMapper;
@Test
public void testSelectList(){
List<User> dicts = userMapper.selectList(null);
dicts.forEach(System.out::println);
}
@Test
public void testSInsert(){
User user=new User();
user.setName("tigger");
userMapper.insert(user);
}
}
五、Sharding-Proxy垂直分片
5.1 修改配置config-sharding.yaml
# 配置逻辑数据库名
schemaName: sharding_db
dataSources:
ds_0:
url: jdbc:mysql://localhost:3307/test?serverTimezone=UTC&useSSL=false
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:mysql://localhost:3308/test?serverTimezone=UTC&useSSL=false
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
dept:
actualDataNodes: ds_0.dept
t_user:
actualDataNodes: ds_1.t_user
重启容器
docker restart shardingproxy
5.2 实时查看日志
可以通过这种方式查看服务器中输出的SQL语句
docker exec -it shardingproxy env LANG=C.UTF8 /bin/bash
tail -f /opt/shardingsphere-proxy/logs/stdout.log
5.3 远程访问测试
mysql -h 192.xxx.xxx.xxx -P 3321 -u root -p
mysql> show databases;
+--------------------+
| schema_name |
+--------------------+
| sharding_db |
| mysql |
| information_schema |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> use sharding_db;
Database changed
mysql> select * from dept;
+---------+------------------+-------------+
| dept_no | dept_name | dept_source |
+---------+------------------+-------------+
| 1 | 开发部 | db01 |
| 2 | 人事部 | db01 |
| 3 | 财务部 | db01 |
| 4 | 市场部 | db01 |
| 14 | 新开发部门1 | db01 |
| 16 | 市场部test | db03 |
+---------+------------------+-------------+
6 rows in set (0.02 sec)
mysql> select * from t_user;
+----+--------+
| id | name |
+----+--------+
| 3 | 大锤 |
| 4 | wang56 |
| 5 | tigger |
+----+--------+
3 rows in set (0.02 sec)
六、Sharding-Proxy水平分片
6.1 修改配置config-sharding.yaml
# 配置逻辑数据库名
schemaName: sharding_db
dataSources:
ds_user:
url: jdbc:mysql://localhost:3307/test?serverTimezone=UTC&useSSL=false
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_order0:
url: jdbc:mysql://localhost:3307/db_order?serverTimezone=UTC&useSSL=false
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_order1:
url: jdbc:mysql://localhost:3310/db_order?serverTimezone=UTC&useSSL=false
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
t_dict:
actualDataNodes: ds_order$->{0..1}.t_dict
t_user:
actualDataNodes: ds_user.t_user
t_order:
actualDataNodes: ds_order$->{0..1}.t_order$->{0..1}
tableStrategy:
standard:
shardingColumn: order_no
shardingAlgorithmName: alg_hash_mod
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: alg_mod
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
t_order_item:
actualDataNodes: ds_order${0..1}.t_order_item${0..1}
tableStrategy:
standard:
shardingColumn: order_no
shardingAlgorithmName: alg_hash_mod
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: alg_mod
keyGenerateStrategy:
column: order_item_id
keyGeneratorName: snowflake
bindingTables:
- t_order,t_order_item
broadcastTables:
- t_dict
shardingAlgorithms:
alg_mod:
type: MOD
props:
sharding-count: 2
alg_hash_mod:
type: HASH_MOD
props:
sharding-count: 2
keyGenerators:
snowflake:
type: SNOWFLAKE
重启容器
docker restart shardingproxy
3.2 实时查看日志
可以通过这种方式查看服务器中输出的SQL语句
docker exec -it shardingproxy env LANG=C.UTF8 /bin/bash
tail -f /opt/shardingsphere-proxy/logs/stdout.log
3.3 远程访问测试
mysql -h 192.xxx.xxx.xxx -P 3321 -u root -p
mysql> show databases;
mysql> use sharding_db;
mysql> show tables;
mysql> select * from dept; --测试水平分片
mysql> select * from t_user;--测试广播表