运行一个简单的ShardingSphere例子

最新推荐文章于 2024-07-28 15:44:17 发布

一曲笛骁奏给谁听

最新推荐文章于 2024-07-28 15:44:17 发布

阅读量810

点赞数 1

分类专栏： Java 文章标签： java mysql

本文链接：https://blog.csdn.net/qq_39378493/article/details/118314998

版权

Java 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

背景

近期在学习秦老师的Java训练营，用秦老师给的例子将ShardingSphere启动并运行，在此记录一下。

部署ShardingSphere

官网下载地址：https://archive.apache.org/dist/shardingsphere/

tar -zxvf apache-shardingsphere-5.0.0-alpha-shardingsphere-proxy-bin.tar.gz

cd apache-shardingsphere-5.0.0-alpha-shardingsphere-proxy-bin

cd conf

修改config-sharding.yaml、server.yaml这两个配置文件

# 逻辑库名称
schemaName: sharding_db

dataSourceCommon:
 username: root
 password: password
 connectionTimeoutMilliseconds: 30000
 idleTimeoutMilliseconds: 60000
 maxLifetimeMilliseconds: 1800000
 maxPoolSize: 5
 minPoolSize: 1
 maintenanceIntervalMilliseconds: 30000

# 数据源
dataSources:
 ds_0:
   url: jdbc:mysql://mysql.camp.com:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
 ds_1:
   url: jdbc:mysql://mysql.camp.com:3306/demo_ds_1?serverTimezone=UTC&useSSL=false

rules:
- !SHARDING
 tables:
   # 逻辑表
   t_order:
     actualDataNodes: ds_${0..1}.t_order_${0..1}
     # 分表规则
     tableStrategy:
       standard:
         shardingColumn: order_id
         # 规则名为t_order_inline
         shardingAlgorithmName: t_order_inline
     keyGenerateStrategy:
       column: order_id
       keyGeneratorName: snowflake
   t_order_item:
     actualDataNodes: ds_${0..1}.t_order_item_${0..1}
     tableStrategy:
       standard:
         shardingColumn: order_id
         shardingAlgorithmName: t_order_item_inline
     keyGenerateStrategy:
       column: order_item_id
       keyGeneratorName: snowflake
 bindingTables:
   - t_order,t_order_item
 defaultDatabaseStrategy:
   standard:
     shardingColumn: user_id
     shardingAlgorithmName: database_inline
 defaultTableStrategy:
   none:
 
 shardingAlgorithms:
   # 分库规则
   database_inline:
     type: INLINE
     props:
       algorithm-expression: ds_${user_id % 2}
   # 分表规则
   t_order_inline:
     type: INLINE
     props:
       algorithm-expression: t_order_${order_id % 2}
   t_order_item_inline:
     type: INLINE
     props:
       algorithm-expression: t_order_item_${order_id % 2}
 
 keyGenerators:
   snowflake:
     # 雪花算法
     type: SNOWFLAKE
     props:
       worker-id: 123

# governance:
#  name: governance_ds
#  registryCenter:
#    type: ZooKeeper
#    serverLists: localhost:2181
#    props:
#      retryIntervalMilliseconds: 500
#      timeToLiveSeconds: 60
#      maxRetries: 3
#      operationTimeoutMilliseconds: 500
#  overwrite: true

authentication:
 users:
   root:
     password: password
#    sharding:
#      password: sharding 
#      authorizedSchemas: sharding_db

props:
 max-connections-size-per-query: 1
 acceptor-size: 16  # The default value is available processors count * 2.
 executor-size: 16  # Infinite by default.
 proxy-frontend-flush-threshold: 128  # The default value is 128.
   # LOCAL: Proxy will run with LOCAL transaction.
   # XA: Proxy will run with XA transaction.
   # BASE: Proxy will run with B.A.S.E transaction.
 proxy-transaction-type: LOCAL
 proxy-opentracing-enabled: false
 proxy-hint-enabled: false
 query-with-cipher-column: false
 sql-show: true
 check-table-metadata-enabled: false

数据库结构：

## 分库分表

create schema demo_ds_0;
create schema demo_ds_1;

CREATE TABLE IF NOT EXISTS demo_ds_0.t_order_0
(
    order_id BIGINT NOT NULL AUTO_INCREMENT,
    user_id  INT    NOT NULL,
    status   VARCHAR(50),
    PRIMARY KEY (order_id)
);
CREATE TABLE IF NOT EXISTS demo_ds_0.t_order_1
(
    order_id BIGINT NOT NULL AUTO_INCREMENT,
    user_id  INT    NOT NULL,
    status   VARCHAR(50),
    PRIMARY KEY (order_id)
);
CREATE TABLE IF NOT EXISTS demo_ds_1.t_order_0
(
    order_id BIGINT NOT NULL AUTO_INCREMENT,
    user_id  INT    NOT NULL,
    status   VARCHAR(50),
    PRIMARY KEY (order_id)
);
CREATE TABLE IF NOT EXISTS demo_ds_1.t_order_1
(
    order_id BIGINT NOT NULL AUTO_INCREMENT,
    user_id  INT    NOT NULL,
    status   VARCHAR(50),
    PRIMARY KEY (order_id)
);

insert into t_order(user_id, status)
values (1, 'OK'),
       (1, 'FAIL');
insert into t_order(user_id, status)
values (2, 'OK'),
       (2, 'FAIL');


CREATE TABLE IF NOT EXISTS demo_ds_0.t_order_item_0
(
    order_item_id BIGINT NOT NULL AUTO_INCREMENT,
    order_id      BIGINT NOT NULL,
    user_id       INT    NOT NULL,
    status        VARCHAR(50),
    PRIMARY KEY (order_item_id)
);
CREATE TABLE IF NOT EXISTS demo_ds_0.t_order_item_1
(
    order_item_id BIGINT NOT NULL AUTO_INCREMENT,
    order_id      BIGINT NOT NULL,
    user_id       INT    NOT NULL,
    status        VARCHAR(50),
    PRIMARY KEY (order_item_id)
);
CREATE TABLE IF NOT EXISTS demo_ds_1.t_order_item_0
(
    order_item_id BIGINT NOT NULL AUTO_INCREMENT,
    order_id      BIGINT NOT NULL,
    user_id       INT    NOT NULL,
    status        VARCHAR(50),
    PRIMARY KEY (order_item_id)
);
CREATE TABLE IF NOT EXISTS demo_ds_1.t_order_item_1
(
    order_item_id BIGINT NOT NULL AUTO_INCREMENT,
    order_id      BIGINT NOT NULL,
    user_id       INT    NOT NULL,
    status        VARCHAR(50),
    PRIMARY KEY (order_item_id)
);

修改完配置并创建完数据库结构后，就可以运行bin/start.sh脚本启动ShardingSphere。

MySQL客户端

登录进去后，Server version可以确认登录进来的是ShardingSphere。

mysql -uroot -h127.0.0.1 -P 3307 -p -A
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.21-ShardingSphere-Proxy 5.0.0-RC1

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use sharding_db;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_demo_ds_0 |
+---------------------+
| t_order             |
| t_order_item        |
+---------------------+
2 rows in set (0.01 sec)

mysql> select * from t_order;
Empty set (0.04 sec)

mysql> insert into t_order(user_id, status) values (1, 'OK'),(1, 'FAIL');
       insert into t_order(user_id, status) values (2, 'OK'),(2, 'FAIL');
Query OK, 2 rows affected (0.42 sec)

Query OK, 2 rows affected (0.01 sec)

mysql> select * from t_order;
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 616394853475397634 |       2 | FAIL   |
| 616394853475397633 |       2 | OK     |
| 616394852011585536 |       1 | OK     |
| 616394852011585537 |       1 | FAIL   |
+--------------------+---------+--------+
4 rows in set (0.01 sec)

mysql>

ShardingSphere日志

日志位置在logs/stdout.log

[INFO ] 22:10:55.080 [ShardingSphere-Command-13] ShardingSphere-SQL - Logic SQL: show tables
[INFO ] 22:10:55.080 [ShardingSphere-Command-13] ShardingSphere-SQL - SQLStatement: MySQLShowTablesStatement(fromSchema=Optional.empty)
[INFO ] 22:10:55.080 [ShardingSphere-Command-13] ShardingSphere-SQL - Actual SQL: ds_0 ::: show tables
[INFO ] 22:10:55.080 [ShardingSphere-Command-13] ShardingSphere-SQL - Actual SQL: ds_1 ::: show tables
[INFO ] 22:11:22.935 [ShardingSphere-Command-14] ShardingSphere-SQL - Logic SQL: select * from t_order
[INFO ] 22:11:22.935 [ShardingSphere-Command-14] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty)
[INFO ] 22:11:22.935 [ShardingSphere-Command-14] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from t_order_0
[INFO ] 22:11:22.935 [ShardingSphere-Command-14] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from t_order_1
[INFO ] 22:11:22.935 [ShardingSphere-Command-14] ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from t_order_0
[INFO ] 22:11:22.935 [ShardingSphere-Command-14] ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from t_order_1
[INFO ] 22:13:05.005 [ShardingSphere-Command-15] ShardingSphere-SQL - Logic SQL: insert into t_order(user_id, status) values (1, 'OK'),        (1, 'FAIL')
[INFO ] 22:13:05.005 [ShardingSphere-Command-15] ShardingSphere-SQL - SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
[INFO ] 22:13:05.005 [ShardingSphere-Command-15] ShardingSphere-SQL - Actual SQL: ds_1 ::: insert into t_order_0(user_id, status, order_id) values (1, 'OK', 616394852011585536)
[INFO ] 22:13:05.005 [ShardingSphere-Command-15] ShardingSphere-SQL - Actual SQL: ds_1 ::: insert into t_order_1(user_id, status, order_id) values (1, 'FAIL', 616394852011585537)
[INFO ] 22:13:05.132 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: insert into t_order(user_id, status) values (2, 'OK'),        (2, 'FAIL')
[INFO ] 22:13:05.132 [ShardingSphere-Command-0] ShardingSphere-SQL - SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
[INFO ] 22:13:05.132 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: ds_0 ::: insert into t_order_1(user_id, status, order_id) values (2, 'OK', 616394853475397633)
[INFO ] 22:13:05.132 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: ds_0 ::: insert into t_order_0(user_id, status, order_id) values (2, 'FAIL', 616394853475397634)
[INFO ] 22:18:32.810 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: select * from t_order
[INFO ] 22:18:32.810 [ShardingSphere-Command-1] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty)
[INFO ] 22:18:32.810 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from t_order_0
[INFO ] 22:18:32.810 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from t_order_1
[INFO ] 22:18:32.810 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from t_order_0
[INFO ] 22:18:32.810 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from t_order_1

从日志的内容可以看出，当查询t_order时，ShardingSphere会去各个库、表查询，然后把数据合在一起并返回；当插入数据时，ShardingSphere会根据规则去相应的库、表添加数据。

插曲

在部署ShardingSphere时，遇到了一个小问题，错误日志如下。在lib下添加JDBC的jar可以解决。

Exception in thread "main" org.apache.shardingsphere.infra.exception.ShardingSphereException: Cannot load JDBC driver class `com.mysql.jdbc.Driver`, make sure it in ShardingSphere-Proxy's classpath.
        at org.apache.shardingsphere.proxy.backend.communication.jdbc.datasource.factory.JDBCRawBackendDataSourceFactory.validateDriverClassName(JDBCRawBackendDataSourceFactory.java:79)
        at org.apache.shardingsphere.proxy.backend.communication.jdbc.datasource.factory.JDBCRawBackendDataSourceFactory.build(JDBCRawBackendDataSourceFactory.java:59)
        at org.apache.shardingsphere.proxy.initializer.impl.AbstractBootstrapInitializer.createDataSources(AbstractBootstrapInitializer.java:94)
        at org.apache.shardingsphere.proxy.initializer.impl.AbstractBootstrapInitializer.lambda$createDataSourcesMap$0(AbstractBootstrapInitializer.java:88)
        at java.util.stream.Collectors.lambda$toMap$58(Collectors.java:1321)
        at java.util.stream.ReduceOps$3ReducingSink.accept(ReduceOps.java:169)
        at java.util.Iterator.forEachRemaining(Iterator.java:116)
        at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801)
        at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:482)
        at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:472)
        at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)
        at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
        at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:566)
        at org.apache.shardingsphere.proxy.initializer.impl.AbstractBootstrapInitializer.createDataSourcesMap(AbstractBootstrapInitializer.java:88)
        at org.apache.shardingsphere.proxy.initializer.impl.AbstractBootstrapInitializer.createSchemaContexts(AbstractBootstrapInitializer.java:73)
        at org.apache.shardingsphere.proxy.initializer.impl.AbstractBootstrapInitializer.init(AbstractBootstrapInitializer.java:63)
        at org.apache.shardingsphere.proxy.Bootstrap.main(Bootstrap.java:48)