ShardingSphere搭建MySQL分库分表

参考：https://blog.csdn.net/tonghu_note/article/details/124578688
https://www.jianshu.com/p/0157b86cd46c
https://blog.csdn.net/weixin_45892228/article/details/127629929

下载shardingsphere

wget  https://archive.apache.org/dist/shardingsphere/5.1.2/apache-shardingsphere-5.1.2-shardingsphere-proxy-bin.tar.gz
tar -zxvf apache-shardingsphere-5.1.2-shardingsphere-proxy-bin.tar.gz
mv apache-shardingsphere-5.1.2-shardingsphere-proxy-bin /data/software/
cd /data/software/apache-shardingsphere-5.1.2-shardingsphere-proxy-bin/conf

修改配置：
vi config-sharding.yaml

databaseName: sharding_db

dataSources:
 ds_0:
   url: jdbc:mysql://172.22.3.217:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
   username: hzero
   password: Q4E%kizUy2#m
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
   minPoolSize: 1
 ds_1:
   url: jdbc:mysql://172.22.3.217:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
   username: hzero
   password: Q4E%kizUy2#m
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
   minPoolSize: 1

rules:
- !SHARDING
 tables:
   t_order:
     actualDataNodes: ds_${0..1}.t_order_${0..1}
     tableStrategy:
       standard:
         shardingColumn: order_id
         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

 scalingName: default_scaling
 scaling:
   default_scaling:
     input:
       workerThread: 40
       batchSize: 1000
     output:
       workerThread: 40
       batchSize: 1000
     streamChannel:
       type: MEMORY
       props:
         block-queue-size: 10000
     completionDetector:
       type: IDLE
       props:
         incremental-task-idle-seconds-threshold: 1800
     dataConsistencyChecker:
       type: DATA_MATCH
       props:
         chunk-size: 1000

vi server.yaml

mode:
  type: Standalone
  repository:
    type: File
    props:
      path: /data/software/apache-shardingsphere-5.1.2-shardingsphere-proxy-bin/repo_pg
  overwrite: false #追加
#注意：users为用于登录计算节点的用户名，授权主机和密码的组合。格式：<username>@<hostname>:<password>，hostname 为 % 或空字符串表示不限制授权主机
rules:
 - !AUTHORITY
   users:
     - root@%:root
     - sharding@:sharding
   provider:
     type: ALL_PERMITTED
 - !TRANSACTION
   defaultType: XA
   providerType: Atomikos
   # # When the provider type is Narayana, the following properties can be configured or not
   # props:
   #   recoveryStoreUrl: jdbc:mysql://127.0.0.1:3306/jbossts
   #   recoveryStoreDataSource: com.mysql.jdbc.jdbc2.optional.MysqlDataSource
   #   recoveryStoreUser: root
   #   recoveryStorePassword: 12345678
 - !SQL_PARSER
   sqlCommentParseEnabled: true
   sqlStatementCache:
     initialCapacity: 2000
     maximumSize: 65535
   parseTreeCache:
     initialCapacity: 128
     maximumSize: 1024

props:
 max-connections-size-per-query: 1
 kernel-executor-size: 16  # Infinite by default.
 proxy-frontend-flush-threshold: 128  # The default value is 128.
 proxy-hint-enabled: false
 sql-show: false
 check-table-metadata-enabled: false
 show-process-list-enabled: false
   # Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
   # The default value is -1, which means set the minimum value for different JDBC drivers.
 proxy-backend-query-fetch-size: -1
 check-duplicate-table-enabled: false
 proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.
   # Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution
   # and block other clients if client connections are more than `proxy-frontend-executor-size`, especially executing slow SQL.
 proxy-backend-executor-suitable: OLAP
 proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
 sql-federation-enabled: false
   # Available proxy backend driver type: JDBC (default), ExperimentalVertx
 proxy-backend-driver-type: JDBC
 proxy-mysql-default-version: 5.7.22 # In the absence of schema name, the default version will be used.
 proxy-default-port: 3307 # Proxy default port.
 proxy-netty-backlog: 1024 # Proxy netty backlog.

启动、查看日志

• 下载mysql驱动
  https://downloads.mysql.com/archives/c-j/
  image.png

https://cdn.mysql.com/archives/mysql-connector-java-8.0/mysql-connector-j-8.0.32.zip
#下载mysql驱动到该目录下: 
cd /data/software/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/lib
#启动
 ../bin/start.sh 3308
#停用
 ../bin/stop.sh 3308
ps -ef|grep shard
tail -f -n 1000 stdout.log 

连接、创建表

mysql -h 192.168.16.144 -P 3308 -u root -proot sharding_db

image.png

创建表、数据

create table t_order(user_id int, order_id int primary key);
insert into t_order values(1, 1);
insert into t_order values(2, 1);
insert into t_order values(1, 2);
insert into t_order values(2, 2);
insert into t_order values(1, 3);
insert into t_order values(2, 3);

image.png