《童虎学习笔记》16分钟ShardingSphere搭建MySQL分库分表

本文章配套视频	马上发布
本专栏全部文章	https://blog.csdn.net/tonghu_note/category_11716546.html
总目录	《童虎学习笔记》it技术教程总目录_童虎学习笔记的博客-CSDN博客

来我的dou音 aa10246666， 看配套视频

---

一、实战环境

机器名	用途	版本	IP地址
Shard1_node1	第1个分片	mysql  Ver 8.0.28	10.211.55.9
Shard2_node2	第2个分片	mysql  Ver 8.0.28	10.211.55.4
ShardingSphere_Proxy_node3	中间件	5.1.0	10.211.55.6
client_node4	客户端测试	mysql  Ver 8.0.28	10.211.55.7

---

二、 Shard1_node1和Shard2_node2上准备环境 1、创建ShardingSphere连接MySQL所使用的数据库和帐号

Shard1_node1上创建数据库 shard1和帐号appuser

mysql> create database shard1;
Query OK, 1 row affected (0.01 sec)

mysql> create user appuser@'%' identified with mysql_native_password by '111';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on shard1.* to appuser@'%';
Query OK, 0 rows affected (0.00 sec) 

Shard2_node2上创建数据库 shard2和帐号appuser

mysql> create database shard2;
Query OK, 1 row affected (0.01 sec)

mysql> create user appuser@'%' identified with mysql_native_password by '111';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on shard2.* to appuser@'%';
Query OK, 0 rows affected (0.00 sec) 

---

三、ShardingSphere-Proxy上准备环境 1、安装ShardingSphere-Proxy

安装及配置jdk(jdk的版本要依据你cpu来选择，我用的是mac虚出来的虚机)

tar xzf jdk-8u202-linux-arm64-vfp-hflt.tar.gz

mv jdk1.8.0_202 /usr/local/

/etc/profile最后添加如下配置

PATH=/usr/local/jdk1.8.0_202/bin:$PATH

source /etc/profile

 安装及配置ShardingSphere-Proxy

tar xzf apache-shardingsphere-5.1.0-shardingsphere-proxy-bin.tar.gz

mv apache-shardingsphere-5.1.0-shardingsphere-proxy-bin /usr/local/

2、下载mysql jdbc驱动并配置

到官网下载适合你的驱动 https://dev.mysql.com/downloads/connector/j/

我是debian系统，以下是我的安装方式，大家可以根据自已的操作系统环境来安装

root@node3:~# dpkg -i mysql-connector-java_8.0.29-1debian11_all.deb

root@node3:~# cd /usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/lib

root@node3:/usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/lib# cp /usr/share/java/mysql-connector-java-8.0.29.jar . 

 3、修改配置文件 server.yaml

rules:
  - !AUTHORITY
    users:
      - root@%:123
    provider:
      type: ALL_PRIVILEGES_PERMITTED

props:
  sql-show: true

4、修改配置文件 config-sharding.yaml

schemaName: sharding_db

dataSources:
  ds_0:
    url: jdbc:mysql://10.211.55.9:3306/shard1?serverTimezone=UTC&useSSL=false
    username: appuser
    password: 111
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  ds_1:
    url: jdbc:mysql://10.211.55.4:3306/shard2?serverTimezone=UTC&useSSL=false
    username: appuser
    password: 111
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..15}
      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,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 % 16}
#    t_order_item_inline:
#      type: INLINE
#      props:
#        algorithm-expression: t_order_item_${order_id % 2}
#  
  keyGenerators:
    snowflake:
      type: SNOWFLAKE

5、启动ShardingSphere-Proxy

root@node3:/usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/conf# ../bin/start.sh 
we find java version: java8, full_version=1.8.0_202
Starting the ShardingSphere-Proxy ...
The classpath is /usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/conf:.:/usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/lib/*:/usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/ext-lib/*
Please check the STDOUT file: /usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/logs/stdout.log

---

四、测试 1、在client_node4上连接ShardingSphere-Proxy

root@node4:~# mysql -h 10.211.55.6 -P 3307 -u root -p123 sharding_db
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 8.0.28-ShardingSphere-Proxy 5.1.0 Source distribution

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> 

2、在client_node4上连接ShardingSphere-Proxy，并创建分片表 t_order

mysql> create table t_order(user_id int, order_id int primary key);
Query OK, 0 rows affected (0.19 sec)

3、在ShardingSphere-Proxy上查看日志 stdout.log，可以看到分别在Shard1_node1和Shard2_node2上创建了16张表

[INFO ] 2022-05-04 10:28:57.048 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: create table t_order(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.048 [ShardingSphere-Command-1] ShardingSphere-SQL - SQLStatement: MySQLCreateTableStatement(containsNotExistClause=false)
[INFO ] 2022-05-04 10:28:57.048 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_0(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.048 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_1(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.048 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_2(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.048 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_3(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.048 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_4(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.048 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_5(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.048 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_6(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.048 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_7(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_8(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_9(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_10(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_11(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_12(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_13(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_14(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: create table t_order_15(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_0(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_1(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_2(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_3(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_4(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_5(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_6(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_7(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_8(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_9(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_10(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_11(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_12(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_13(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_14(user_id int, order_id int primary key)
[INFO ] 2022-05-04 10:28:57.049 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: create table t_order_15(user_id int, order_id int primary key)

4、在Shard1_node1上查看新建表的情况，新建了16张分片表

root@node1:~# mysql -uroot -proot

mysql> use shard1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_shard1 |
+------------------+
| t_order_0        |
| t_order_1        |
| t_order_10       |
| t_order_11       |
| t_order_12       |
| t_order_13       |
| t_order_14       |
| t_order_15       |
| t_order_2        |
| t_order_3        |
| t_order_4        |
| t_order_5        |
| t_order_6        |
| t_order_7        |
| t_order_8        |
| t_order_9        |
+------------------+
16 rows in set (0.00 sec)

5、在Shard2_node2上查看新建表的情况，新建了16张分片表

root@node2:~# mysql -uroot -proot

mysql> use shard2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_shard2 |
+------------------+
| t_order_0        |
| t_order_1        |
| t_order_10       |
| t_order_11       |
| t_order_12       |
| t_order_13       |
| t_order_14       |
| t_order_15       |
| t_order_2        |
| t_order_3        |
| t_order_4        |
| t_order_5        |
| t_order_6        |
| t_order_7        |
| t_order_8        |
| t_order_9        |
+------------------+
16 rows in set (0.00 sec)

6、在client_node4上连接ShardingSphere-Proxy，插入几条数据

root@node4:~# mysql -h 10.211.55.6 -P 3307 -u root -p123 sharding_db

mysql> 
mysql> insert into t_order values(1, 1);
Query OK, 1 row affected (0.19 sec)

mysql> insert into t_order values(2, 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_order values(1, 2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_order values(2, 2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_order values(1, 3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_order values(2, 3);
Query OK, 1 row affected (0.02 sec)

mysql> 

 在ShardingSphere-Proxy上，查看日志stdout.log，观察数据分布式插入的实际情况，可以看到数据库分别插入到了数据源ds_0和ds_1的分片表t_order_1、t_order_2、t_order_3

[INFO ] 2022-05-04 10:33:35.928 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: SHOW COLUMNS FROM t_order FROM sharding_db
[INFO ] 2022-05-04 10:33:35.928 [ShardingSphere-Command-2] ShardingSphere-SQL - SQLStatement: MySQLShowColumnsStatement(table=SimpleTableSegment(tableName=TableNameSegment(startIndex=18, stopIndex=24, identifier=IdentifierValue(value=t_order, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), fromSchema=Optional[org.apache.shardingsphere.sql.parser.sql.common.segment.dal.FromSchemaSegment@4c29f274], filter=Optional.empty)
[INFO ] 2022-05-04 10:33:35.929 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds_0 ::: SHOW COLUMNS FROM t_order_0 
[INFO ] 2022-05-04 10:33:35.937 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: select @@version_comment limit 1
[INFO ] 2022-05-04 10:33:35.937 [ShardingSphere-Command-2] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional[org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment@6965614b], lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-05-04 10:33:35.937 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds_0 ::: select @@version_comment limit 1
[INFO ] 2022-05-04 10:33:55.154 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: insert into t_order values(1, 1)
[INFO ] 2022-05-04 10:33:55.154 [ShardingSphere-Command-2] ShardingSphere-SQL - SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
[INFO ] 2022-05-04 10:33:55.154 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds_1 ::: insert into t_order_1 values(1, 1)
[INFO ] 2022-05-04 10:33:55.159 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: insert into t_order values(2, 1)
[INFO ] 2022-05-04 10:33:55.159 [ShardingSphere-Command-2] ShardingSphere-SQL - SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
[INFO ] 2022-05-04 10:33:55.159 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds_0 ::: insert into t_order_1 values(2, 1)
[INFO ] 2022-05-04 10:33:55.163 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: insert into t_order values(1, 2)
[INFO ] 2022-05-04 10:33:55.163 [ShardingSphere-Command-2] ShardingSphere-SQL - SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
[INFO ] 2022-05-04 10:33:55.163 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds_1 ::: insert into t_order_2 values(1, 2)
[INFO ] 2022-05-04 10:33:55.169 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: insert into t_order values(2, 2)
[INFO ] 2022-05-04 10:33:55.169 [ShardingSphere-Command-2] ShardingSphere-SQL - SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
[INFO ] 2022-05-04 10:33:55.169 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds_0 ::: insert into t_order_2 values(2, 2)
[INFO ] 2022-05-04 10:33:55.171 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: insert into t_order values(1, 3)
[INFO ] 2022-05-04 10:33:55.171 [ShardingSphere-Command-2] ShardingSphere-SQL - SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
[INFO ] 2022-05-04 10:33:55.171 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds_1 ::: insert into t_order_3 values(1, 3)
[INFO ] 2022-05-04 10:33:56.238 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: insert into t_order values(2, 3)
[INFO ] 2022-05-04 10:33:56.239 [ShardingSphere-Command-2] ShardingSphere-SQL - SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
[INFO ] 2022-05-04 10:33:56.239 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds_0 ::: insert into t_order_3 values(2, 3)

 7、在client_node4上连接ShardingSphere-Proxy，发起2条查询语句

mysql> select * from t_order where order_id=1;
+---------+----------+
| user_id | order_id |
+---------+----------+
|       2 |        1 |
|       1 |        1 |
+---------+----------+
2 rows in set (0.14 sec)

mysql> select * from t_order where order_id=2;
+---------+----------+
| user_id | order_id |
+---------+----------+
|       2 |        2 |
|       1 |        2 |
+---------+----------+
2 rows in set (0.02 sec) 

 在ShardingSphere-Proxy上，查看日志stdout.log，观察数据分布式查询的情况，可以看到order_id=1分别走的是数据源ds_0的分片表t_order_1和数据源ds_1的分片表t_order_1

[INFO ] 2022-05-04 10:39:58.265 [ShardingSphere-Command-3] ShardingSphere-SQL - Logic SQL: select * from t_order where order_id=1
[INFO ] 2022-05-04 10:39:58.265 [ShardingSphere-Command-3] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-05-04 10:39:58.265 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from t_order_1 where order_id=1
[INFO ] 2022-05-04 10:39:58.265 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from t_order_1 where order_id=1
[INFO ] 2022-05-04 10:40:03.970 [ShardingSphere-Command-3] ShardingSphere-SQL - Logic SQL: select * from t_order where order_id=2
[INFO ] 2022-05-04 10:40:03.970 [ShardingSphere-Command-3] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-05-04 10:40:03.970 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from t_order_2 where order_id=2
[INFO ] 2022-05-04 10:40:03.971 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from t_order_2 where order_id=2

好了，分库分表搭建完毕