ShardingProxy从0-1使用详细步骤

最新推荐文章于 2024-07-31 19:38:21 发布

Ivyvivid

最新推荐文章于 2024-07-31 19:38:21 发布

阅读量1.3k

点赞数

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/Ivyvivid/article/details/131230104

版权

本文详细介绍了如何安装和配置ShardingSphere-Proxy进行数据库的读写分离、垂直分片和水平分片。通过修改配置文件，实现了不同场景下的数据分片策略，并展示了使用MySQL客户端和应用程序连接Proxy的示例。同时，文中提到了Sharding-Proxy对异构语言的支持和DBA的友好性。

摘要由CSDN通过智能技术生成

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配置文件，即为一个逻辑数据源。

Ivyvivid

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
ShardingProxy从0-1使用详细步骤

Sharding-Proxy的优势在于对异构语言的支持(无论使用什么语言，就都可以访问)，以及为DBA提供可操作入口。Sharding-Proxy 默认不支持hint，如需支持，请在conf/server.yaml中，将props的属性proxy.hint.enabled设置为true。在Sharding-Proxy中，HintShardingAlgorithm的泛型只能是String类型。Sharding-Proxy默认使用3307端口，可以通过启动脚本追加参数作为启动端口号。
复制链接

扫一扫