场景一：数据分片 #

# pip install sphereex‑boot‑1.1.0.tar.gz
 Processing ./sphereex‑boot‑1.1.0.tar.gz
 Requirement already satisfied: ansible<=2.10.7,>=2.8.0 in /usr/lib/python2.7/site-packages (from sphereex-boot==1.1.0) (2.10.7)
 ······
  Building wheel for sphereex-boot (setup.py) ... done
  Created wheel for spex: filename=sphereex_boot-1.1.0-py2-none-any.whl size=212249 sha256=5de4a3b4f347da57697ad942f74284be38ecf12e5e0d1fefda06e5e4282c22eb
  Stored in directory: /root/.cache/pip/wheels/e5/c4/d7/64a96c1f48ffabc68df6aa164beb7a93a1ad4d81aba4b751d7

Successfully built sphereex-boot

Installing collected packages: sphereex-boot
Successfully installed sphereex‑boot‑1.1.0

# spex --version
Version 1.1.0

# mkdir -p /root/demo 
# cd /root/demo
# ll
mysql-connector-java-8.jar
sphereex-dbplusengine-proxy-1.2.0.tar.gz
apache-zookeeper-3.7.0-bin.tar.gz

# tar -xzvf apache-zookeeper-3.7.0-bin.tar.gz 
# cd apache-zookeeper-3.7.0-bin/conf
# cp zoo_sample.cfg /root/demo/zoo.cfg
# cd /root/demo
# ls zoo.cfg
zoo.cfg

# vi zoo.cfg
dataDir=/root/demo/zk
clientPort=2181  

# tar -zxvf sphereex-dbplusengine-proxy-1.2.0.tar.gz
# cd sphereex-dbplusengine-proxy-1.2.0
# cp -r conf /root/demo/conf
# cd /root/demo
# ls conf/global.yaml 
conf/global.yaml

# vi conf/global.yaml
mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: governance_ds
      server-lists: localhost:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500
  
rules:
  - !AUTHORITY
    users:
      - user: root@%
        password: root
      - user: sharding
        password: sharding
    privilege:
      type: ALL_PERMITTED
  - !TRANSACTION
    defaultType: XA
    providerType: Atomikos

# spex config template --type full --output ./
# ls cluster-template.yaml
cluster-template.yaml

# vi cluster-template.yaml
cluster_name: demo
install_user: root
install_password: 'root'
 
proxy:
  version: '1.2.0'
  install_dir: /root/demo/engine
  conf_dir: /root/demo/conf
  file: /root/demo/sphereex-dbplusengine-proxy-1.2.0.tar.gz
# agent_conf_file: 
  depend_files:
   - /root/demo/mysql-connector-java-8.jar
  port: 3308
  servers:
   - host: 127.0.0.1
 
zookeeper:
  version: '3.7.0'
  install_dir: /root/demo/zk
  data_dir: /root/demo/zk
  conf_file: /root/demo/zoo.cfg
  file: /root/demo/apache-zookeeper-3.7.0-bin.tar.gz
  port: 2181
  servers:
   - host: 127.0.0.1
     myid: 1

# spex config check -f cluster-template.yaml 
Proxy: The host:127.0.0.1 port:3308 missing 'agent_conf_file' value //忽略此报错即可，此处不需要agent。
No error with ZooKeeper

#  spex config add -f cluster-template.yaml 
Add cluster successfully

# spex cluster install --name demo
check proxy install dir exist!          
Completed......
create install directory          
127.0.0.1:3308 => success
install proxy          
127.0.0.1:3308 => success
copying shell file          
127.0.0.1:3308 => success
copying config file          
127.0.0.1:3308 => success
copying agent config file          
skipped host:127.0.0.1 item:None
copying depend file          
127.0.0.1:3308 => success
Completed......
Operation ZooKeeper
check ZooKeeper install dir exist!          
Completed......
Operation ZooKeeper
create ZooKeeper install directory          
127.0.0.1:2181 => success
create ZooKeeper data directory          
127.0.0.1:2181 => success
install ZooKeeper          
127.0.0.1:2181 => success
copy ZooKeeper config file          
127.0.0.1:2181 => success
create myid          
127.0.0.1:2181 => success
Completed......

# spex cluster start --name demo
Operation ZooKeeper
start ZooKeeper          
127.0.0.1:2181 => success
/usr/bin/java
Starting zookeeper ... STARTED
ZooKeeper JMX enabled by default
Using config: /root/demo/zk/apache-zookeeper-3.7.0-bin/bin/../conf/zoo.cfg
Completed......
start proxy          
/usr/bin/java
we find java version: java8, full_version=1.8.0_322, full_path=/usr/bin/java
The port is 3308
The classpath is /root/demo/engine/sphereex-dbplusengine-proxy-1.2.0/conf:.:/root/demo/engine/sphereex-dbplusengine-proxy-1.2.0/lib/*:/root/demo/engine/sphereex-dbplusengine-proxy-1.2.0/ext-lib/*
main class org.apache.shardingsphere.proxy.Bootstrap 3308 /root/demo/engine/sphereex-dbplusengine-proxy-1.2.0/conf 0.0.0.0 false
Starting the SphereEx-DB-Plus-Engine ... PID: 10335
Please check the STDOUT file: /root/demo/engine/sphereex-dbplusengine-proxy-1.2.0/logs/stdout.log
Completed......

# spex cluster status --name demo 
proxy status          
127.0.0.1:3308 => success
PID:10335 PORT:3308 %CPU:16.9 %MEM:0.2 START:14:09 TIME:0:01
Results summary
+-----------+------+------+------+------+-------+------+
|    HOST   | PORT | PID  | %CPU | %MEM | START | TIME |
+-----------+------+------+------+------+-------+------+
| 127.0.0.1 | 3308 |10335 | 16.9 | 0.2  | 14:09 | 0:01 |
+-----------+------+------+-------+------+------+------+
Completed......
Operation ZooKeeper
ZooKeeper status          
127.0.0.1:2181 => success
/usr/bin/java
Client port found: 2181. Client address: localhost. Client SSL: false.
Mode: standalone
ZooKeeper JMX enabled by default
Using config: /root/demo/zk/apache-zookeeper-3.7.0-bin/bin/../conf/zoo.cfg
Results summary
+-----------+------+------------------+
|    HOST   | PORT |      STATUS      |
+-----------+------+------------------+
| 127.0.0.1 | 2181 | Mode: standalone |
+-----------+------+------------------+

# mysql -uroot -p -P3308 -h127.0.0.1
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 4
Server version: 5.7.22 sphereex-dbplusengine-proxy-1.2.0 
 
Copyright (c) 2000, 2021, 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>

# scp -r sphere.license  root@10.10.10.1:/root/demo/conf

# cat /root/demo/conf/sphere.license 
Ic5OXgAAACMAAAACAAAACwAAAAxjcmVEaWdlc3RTSEEtNTEy

# mysql -uroot -p -P3308 -h127.0.0.1
sql> REGISTER LICENSE '[Ic5OXgAAACMAAAACAAAACwAAAAxjcmVEaWdlc3RTSEEtNTEy]';
Query OK, 0 rows affected (1.29 sec)

sql> SHOW LICENSE INFO;
| company_name | instance_count | resource_count | expiry_date         | generation_data     | status | license 
| sphereextest | 8              | 8              | 2023-01-01 00:00:00 | 2022-09-08 18:00:00 | valid  | Ic5OXgAAACMAAAACAAAACwAAAAxjcmVEaWdlc3RTSEEtNTEy

# mysql -uroot -proot -P13306 -h127.0.0.1
sql> CREATE DATABASE test01;
Query OK, 1 row affected (0.00 sec)

sql> CREATE DATABASE test02;
Query OK, 1 row affected (0.00 sec)

sql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test01             |
| test02             |
+--------------------+
5 rows in set (0.01 sec)

# mysql -uroot -proot -P3308 -h127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.7.22 SphereEx-DBPlusEngine-Proxy 1.2.0 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2021, 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.
 
sql> 

sql>  CREATE DATABASE testdb;
Query OK, 0 rows affected (0.65 sec)

sql> SHOW DATABASES;
+--------------------+
| schema_name        |
+--------------------+
| mysql              |
| information_schema |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

sql> USE testdb;
Database changed

sql> REGISTER STORAGE UNIT test01 (
    ->     HOST="127.0.0.1",
    ->     PORT=13306,
    ->     DB="test01",
    ->     USER="root",
    ->     PASSWORD="root"
    -> ),test02 (
    ->     HOST="127.0.0.1",
    ->     PORT=13306,
    ->     DB="test02",
    ->     USER="root",
    ->     PASSWORD="root"
    -> );
Query OK, 0 rows affected (0.09 sec)

sql> SHOW STORAGE UNITS\G
*************************** 1. row ***************************
                           name: test02
                           type: MySQL
                           host: 127.0.0.1
                           port: 13306
                             db: test02
connection_timeout_milliseconds: 30000
      idle_timeout_milliseconds: 60000
      max_lifetime_milliseconds: 2100000
                  max_pool_size: 50
                  min_pool_size: 1
                      read_only: false
               other_attributes: {"dataSourceProperties":{"cacheServerConfiguration":"true","elideSetAutoCommits":"true","useServerPrepStmts":"true","cachePrepStmts":"true","useSSL":"false","rewriteBatchedStatements":"true","cacheResultSetMetadata":"false","useLocalSessionState":"true","maintainTimeStats":"false","prepStmtCacheSize":"8192","tinyInt1isBit":"false","prepStmtCacheSqlLimit":"2048","serverTimezone":"UTC","netTimeoutForStreamingResults":"0","zeroDateTimeBehavior":"round"},"healthCheckProperties":{},"initializationFailTimeout":1,"validationTimeout":5000,"leakDetectionThreshold":0,"registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false}
*************************** 2. row ***************************
                           name: test01
                           type: MySQL
                           host: 127.0.0.1
                           port: 13306
                             db: test01
connection_timeout_milliseconds: 30000
      idle_timeout_milliseconds: 60000
      max_lifetime_milliseconds: 2100000
                  max_pool_size: 50
                  min_pool_size: 1
                      read_only: false
               other_attributes: {"dataSourceProperties":{"cacheServerConfiguration":"true","elideSetAutoCommits":"true","useServerPrepStmts":"true","cachePrepStmts":"true","useSSL":"false","rewriteBatchedStatements":"true","cacheResultSetMetadata":"false","useLocalSessionState":"true","maintainTimeStats":"false","prepStmtCacheSize":"8192","tinyInt1isBit":"false","prepStmtCacheSqlLimit":"2048","serverTimezone":"UTC","netTimeoutForStreamingResults":"0","zeroDateTimeBehavior":"round"},"healthCheckProperties":{},"initializationFailTimeout":1,"validationTimeout":5000,"leakDetectionThreshold":0,"registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false}
2 rows in set (0.00 sec)
 

sql> USE testdb;
Database changed

sql> CREATE SHARDING TABLE RULE t_user(
    ->     STORAGE_UNITS(test01,test02),
    ->     SHARDING_COLUMN=user_id,
    ->     TYPE(NAME="hash_mod",PROPERTIES("sharding-count"=4))
    ->     );
Query OK, 0 rows affected (0.07 sec)

sql> SHOW SHARDING TABLE RULE t_user\G
*************************** 1. row ***************************
                            table: t_user
                actual_data_nodes: 
              actual_data_sources: test01,test02
           database_strategy_type: 
         database_sharding_column: 
 database_sharding_algorithm_type: 
database_sharding_algorithm_props: 
              table_strategy_type: STANDARD
            table_sharding_column: user_id
    table_sharding_algorithm_type: hash_mod
   table_sharding_algorithm_props: sharding-count=4
              key_generate_column: 
               key_generator_type: 
              key_generator_props: 
                    auditor_types: 
               allow_hint_disable: 
1 row in set (0.01 sec)

sql> CREATE TABLE `t_user` (
    ->  `user_id` int NOT NULL,
    ->  `order_id` int NOT NULL,
    ->  `status` varchar(45) DEFAULT NULL,
    ->  PRIMARY KEY (`user_id`)
    -> );
Query OK, 0 rows affected (0.28 sec)

sql> INSERT INTO t_user VALUES
    -> (1,1,'active'),
    -> (2,2,'active'),
    -> (3,3,'active'),
    -> (4,4,'active');
Query OK, 4 rows affected (0.10 sec)

sql> PREVIEW SELECT * FROM t_user;
+------------------+---------------------------------------------------------+
| data_source_name | actual_sql                                              |
+------------------+---------------------------------------------------------+
| test01           | SELECT * FROM t_user_0 UNION ALL SELECT * FROM t_user_2 |
| test02           | SELECT * FROM t_user_1 UNION ALL SELECT * FROM t_user_3 |
+------------------+---------------------------------------------------------+
2 rows in set (0.04 sec)

# mysql -uroot -proot -P13306 -h127.0.0.1
sql> use test01;
sql> select * from t_user_0;
+---------+----------+--------+
| user_id | order_id | status |
+---------+----------+--------+
|       4 |        4 | active |
+---------+----------+--------+
1 row in set (0.00 sec)

sql> select * from t_user_2;
+---------+----------+--------+
| user_id | order_id | status |
+---------+----------+--------+
|       2 |        2 | active |
+---------+----------+--------+
1 row in set (0.00 sec)

sql> use test02;
sql> select * from t_user_1;
+---------+----------+--------+
| user_id | order_id | status |
+---------+----------+--------+
|       1 |        1 | active |
+---------+----------+--------+
1 row in set (0.00 sec)

sql> select * from t_user_3;
+---------+----------+--------+
| user_id | order_id | status |
+---------+----------+--------+
|       3 |        3 | active |
+---------+----------+--------+
1 row in set (0.00 sec)