ShardingProxy
Sharding-Proxy是ShardingSphere的第二个产品,定位为透明化的数据库代理端,提供封装了数据库二进制协议的服务端版本,用于完成对异构语言的支持。
目前先提供MySQL版本
,它可以使用任何兼容MySQL协议的访问客户端(如:MySQL Command Client, MySQL Workbench等操作数据,对DBA更加友好。
- 向应用程序完全透明,可直接当做MySQL使用
- 适用于任何兼容MySQL协议的客户端
1 使用二进制发布包安装ShardingSphere-Proxy
目前 ShardingSphere-Proxy 提供了 3 种获取方式:
- 二进制发布包
- Docker
- Helm
这里我们使用二进制包的形式安装ShardingProxy, 这种安装方式既可以Linux系统运行,又可以在windows系统运行,步骤如下:
1) 解压二进制包
- 官方文档:
https://shardingsphere.apache.org/document/5.1.1/cn/user-manual/shardingsphere-proxy/startup/bin/
- 安装包下载
https://archive.apache.org/dist/shardingsphere/5.1.1/
-
解压
windows:使用解压软件解压文件
Linux:将文件上传至/opt目录,并解压
tar -zxvf apache-shardingsphere-5.1.1-shardingsphere-proxy-bin.tar.gz
2) 上传MySQL驱动
mysql-connector-java-8.0.22.jar
,将MySQl驱动放至 ext-lib
目录 ,该ext-lib目录需要自行创建。
3) 修改配置conf/server.yaml
# 配置用户信息 用户名密码,赋予管理员权限
rules:
- !AUTHORITY
users:
- root@%:root
provider:
type: ALL_PRIVILEGES_PERMITTED
#开启SQL打印
props:
sql-show: true
4) 启动ShardingSphere-Proxy
- Linux 操作系统请运行
bin/start.sh
- Windows 操作系统请运行
bin/start.bat
- 指定端口号和配置文件目录:
bin/start.bat ${proxy_port} ${proxy_conf_directory}
5) 远程连接ShardingSphere-Proxy
- 远程访问,默认端口3307
mysql -h192.168.52.12 -P3307 -uroot -p
6) 访问测试
show databases;
2 proxy实现读写分离
1) 修改配置config-readwrite-splitting.yaml
#schemaName用来指定->逻辑表名
schemaName: readwrite_splitting_db
dataSources:
write_ds:
url: jdbc:mysql://192.168.52.10:3306/test_rw?serverTimezone=UTC&useSSL=false&characterEncoding=utf-8
username: root
password: QiDian@666
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
read_ds_0:
url: jdbc:mysql://192.168.52.11:3306/test_rw?serverTimezone=UTC&useSSL=false&characterEncoding=utf-8
username: root
password: QiDian@666
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
2) 命令行测试
C:\Users\86187>mysql -h192.168.52.12 -P3307 -uroot -p
mysql> show databases;
+------------------------+
| schema_name |
+------------------------+
| readwrite_splitting_db |
| mysql |
| information_schema |
| performance_schema |
| sys |
+------------------------+
5 rows in set (0.01 sec)
mysql> use readwrite_splitting_db;
Database changed
mysql> show tables;
+----------------------------------+------------+
| Tables_in_readwrite_splitting_db | Table_type |
+----------------------------------+------------+
| users | BASE TABLE |
| products | BASE TABLE |
+----------------------------------+------------+
2 rows in set (0.01 sec)
mysql> select * from users;
+----+-------+------+
| id | NAME | age |
+----+-------+------+
| 2 | user2 | 21 |
| 3 | user3 | 22 |
+----+-------+------+
2 rows in set (0.02 sec)
3) 动态查看日志
tail -f /opt/apache-shardingsphere-5.1.1-shardingsphere-proxy-bin/logs/stdout.log
3 使用应用程序连接proxy
1) 创建项目
项目名称: sharding-proxy-test
Spring脚手架: http://start.aliyun.com
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>
3) 创建实体类
@TableName("products")
@Data
public class Products {
@TableId(value = "pid",type = IdType.AUTO)
private Long pid;
private String pname;
private int price;
private String flag;
}
4) 创建Mapper
@Mapper
public interface ProductsMapper extends BaseMapper<Products> {
}
5) 配置数据源
# 应用名称
spring.application.name=sharding-proxy-demo
#mysql数据库 (实际连接的是proxy)
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://192.168.52.12:3307/readwrite_splitting_db?serverTimezone=GMT%2B8&useSSL=false&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=root
#mybatis日志
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
6) 测试
@SpringBootTest
class ShardingproxyDemoApplicationTests {
@Autowired
private ProductsMapper productsMapper;
/**
* 读数据测试
*/
@Test
public void testSelect(){
productsMapper.selectList(null).forEach(System.out::println);
}
@Test
public void testInsert(){
Products products = new Products();
products.setPname("洗碗机");
products.setPrice(1000);
products.setFlag("1");
productsMapper.insert(products);
}
}
4 Proxy实现垂直分片
1) 修改配置config-sharding.yaml
schemaName: sharding_db
#
dataSources:
ds_0:
url: jdbc:mysql://192.168.52.10:3306/ivy_payorder_db?characterEncoding=UTF-8&useSSL=false
username: root
password: QiDian@666
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:mysql://192.168.52.11:3306/ivy_user_db?characterEncoding=UTF-8&useSSL=false
username: root
password: QiDian@666
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
pay_order:
actualDataNodes: ds_0.pay_order
users:
actualDataNodes: ds_1.users
2) 动态查看日志
tail -f /opt/apache-shardingsphere-5.1.1-shardingsphere-proxy-bin/logs/stdout.log
3) 远程访问
C:\Users\86187>mysql -h192.168.52.12 -P3307 -uroot -p
mysql> show databases;
+------------------------+
| schema_name |
+------------------------+
| readwrite_splitting_db |
| sharding_db |
| mysql |
| information_schema |
| performance_schema |
| sys |
+------------------------+
6 rows in set (0.02 sec)
mysql> use sharding_db;
Database changed
mysql> show tables;
+-----------------------+------------+
| Tables_in_sharding_db | Table_type |
+-----------------------+------------+
| t_district | BASE TABLE |
| pay_order | BASE TABLE |
| users | BASE TABLE |
+-----------------------+------------+
3 rows in set (0.10 sec)
mysql> select * from pay_order;
+----------+---------+--------------+-------+
| order_id | user_id | product_name | COUNT |
+----------+---------+--------------+-------+
| 2001 | 1003 | 电视 | 0 |
+----------+---------+--------------+-------+
1 row in set (0.17 sec)
5 Proxy实现水平分片
1) 修改配置config-sharding.yaml
schemaName: sharding_db
dataSources:
ivy_course_db0:
url: jdbc:mysql://192.168.52.10:3306/ivy_course_db0?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: QiDian@666
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ivy_course_db1:
url: jdbc:mysql://192.168.52.11:3306/ivy_course_db1?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: QiDian@666
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
t_course:
actualDataNodes: ivy_course_db${0..1}.t_course_${0..1}
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: alg_mod
tableStrategy:
standard:
shardingColumn: cid
shardingAlgorithmName: alg_hash_mod
keyGenerateStrategy:
column: cid
keyGeneratorName: snowflake
shardingAlgorithms:
alg_mod:
type: MOD
props:
sharding-count: 2
alg_hash_mod:
type: HASH_MOD
props:
sharding-count: 2
keyGenerators:
snowflake:
type: SNOWFLAKE
2) 远程访问
mysql> use sharding_db;
Database changed
mysql> show tables;
+-----------------------+------------+
| Tables_in_sharding_db | Table_type |
+-----------------------+------------+
| t_district | BASE TABLE |
| t_course_section_0 | BASE TABLE |
| t_course | BASE TABLE |
| t_course_section_1 | BASE TABLE |
+-----------------------+------------+
4 rows in set (0.04 sec)
mysql> select * from t_course;
3) 动态查看日志
tail -f /opt/apache-shardingsphere-5.1.1-shardingsphere-proxy-bin/logs/stdout.log
4) 测试广播表
mysql> select * from t_district;
+---------------------+---------------+-------+
| id | district_name | LEVEL |
+---------------------+---------------+-------+
| 1592493879469277185 | 昌平区 | 1 |
+---------------------+---------------+-------+
1 row in set (0.06 sec)
6 Proxy实现绑定表与广播表
1) 修改配置config-sharding.yaml
schemaName: sharding_db
dataSources:
ivy_course_db0:
url: jdbc:mysql://192.168.52.10:3306/ivy_course_db0?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: QiDian@666
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ivy_course_db1:
url: jdbc:mysql://192.168.52.11:3306/ivy_course_db1?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: QiDian@666
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
t_course:
actualDataNodes: ivy_course_db${0..1}.t_course_${0..1}
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: alg_mod
tableStrategy:
standard:
shardingColumn: cid
shardingAlgorithmName: alg_hash_mod
keyGenerateStrategy:
column: cid
keyGeneratorName: snowflake
broadcastTables:
- t_district
shardingAlgorithms:
alg_mod:
type: MOD
props:
sharding-count: 2
alg_hash_mod:
type: HASH_MOD
props:
sharding-count: 2
keyGenerators:
snowflake:
type: SNOWFLAKE
2) 远程访问-测试绑定表
mysql> use sharding_db;
Database changed
mysql> show tables;
+-----------------------+------------+
| Tables_in_sharding_db | Table_type |
+-----------------------+------------+
| t_course_section | BASE TABLE |
| t_district | BASE TABLE |
| t_course | BASE TABLE |
+-----------------------+------------+
3 rows in set (0.03 sec)
mysql> select * from t_course_section;
mysql> select * from t_course c inner join t_course_section cs on c.cid = cs.cid;
3) 动态查看日志
tail -f /opt/apache-shardingsphere-5.1.1-shardingsphere-proxy-bin/logs/stdout.log
4) 测试广播表
mysql> select * from t_district;
+---------------------+---------------+-------+
| id | district_name | LEVEL |
+---------------------+---------------+-------+
| 1592493879469277185 | 昌平区 | 1 |
+---------------------+---------------+-------+
1 row in set (0.06 sec)
7 总结
- Sharding-Proxy的优势在于对异构语言的支持(无论使用什么语言,就都可以访问),以及为DBA提供可操作入口。
Sharding-Proxy 默认不支持hint,如需支持,请在conf/server.yaml中,将props的属性proxy.hint.enabled设置为true。在Sharding-Proxy中,HintShardingAlgorithm的泛型只能是String类型。
- Sharding-Proxy默认使用3307端口,可以通过启动脚本追加参数作为启动端口号。如: bin/start.sh 3308
- Sharding-Proxy使用conf/server.yaml配置注册中心、认证信息以及公用属性。
- Sharding-Proxy支持多逻辑数据源,每个以"config-"做前缀命名yaml配置文件,即为一个逻辑数据源。