Scenario one：Data Sharding #
In this scenario, SphereEx-Boot (a command-line tool for managing SphereEx-DBPlusEngine clusters) is used to install a SphereEx-DBPlusEngine cluster, and quickly implement data sharding through the DistSQL command line.
The data sharding scenario is limited to quickly building a SphereEx-DBPlusEngine cluster locally and connecting to the MySQL database to implement data sharding, excluding other components and database installation.
Operation Steps #
Prepare resource environment #
| Instance | IP Address | Service Port | Remark | |
|---|---|---|---|---|
| 1 | SphereEx-Boot | 127.0.0.1 | / | SphereEx- Boot Toolkit |
| 2 | SphereEx-DBPlusEngine | 127.0.0.1 | 3308 | SphereEx-DBPlusEngine Cluster |
| 3 | ZooKeeper | 127.0.0.1 | 2181 | ZooKeeper |
| 4 | MySQL 8.0 | 127.0.0.1 | 13306 | Database for sharding (need to be prepared in advance) |
Description: It needs to be replaced with the IP address and port in the actual environment during operation.
Install SphereEx-Boot #
Installation preparation #
Before installing SphereEx-Boot, make sure the operating system has installed the following software:
sshpass 1.0.0+
Python 2.7 Or Python 3.5+
pip 20.0.0+
Install SphereEx-Boot #
- Execute the following command to install SphereEx-Boot.
# 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
Check SphereEx-Boot installation results #
Execute the command to check the version of SphereEx-Boot. If the output version is the currently installed version, the installation is successful.
# spex --version
Version 1.1.0
Create a cluster using SphereEx-Boot #
This chapter introduces how to use SphereEx-Boot to quickly create a SphereEx-DBPlusEngine cluster.
Operation Steps #
Install Cluster #
- Cluster Planning
| Cluster Name | IP Address | Account | Password |
|---|---|---|---|
| demo | 127.0.0.1 | root | root |
Note: You need to replace it with your own IP address, login account and password during operation.
- Upload the installation package
- Use MySQL
Upload mysql-connector-java-8.jar, sphereex-dbplusengine-proxy-1.2.0.tar.gz, apache-zookeeper-3.7.0-bin.tar.gz installation packages to / root/demo path.
Note: The actual name of the installation package here will change according to the version change. Just upload the corresponding installation package according to the actual situation.
# 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
- Use PostgreSQL
Use any PostgreSQL client connection. Such as: psql -U root -h 127.0.0.1 -p 3308.
- Use openGauss
Copy the openGauss JDBC driver prefixed with the org.opengauss package name to the directory /root/demo.
Connect using any openGauss client. Such as: gsql -U root -h 127.0.0.1 -p 3308.
- Obtain and configure the
zoo.cfgfile
zoo.cfg is the configuration file of ZooKeeper, and the system installs Zookeeper software according to the information in this configuration file.
# 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
Configure the zoo.cfg file, replace the data directory and port parameter values, and keep other parameters as default.
# vi zoo.cfg
dataDir=/root/demo/zk
clientPort=2181
- Obtain and configure
server.yamlfile
The conf file needs to be obtained, and the server.yaml file needs to be configured in this step.
The server.yaml file is the configuration file of the DBPlusEngine cluster, which includes Zookeeper related information, cluster user information, and transaction related settings, etc.
# 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/server.yaml
conf/server.yaml
Configure the server.yaml file, and remove the comments of the following parameters.
# vi conf/server.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
- Obtain and configure the installation cluster file
Generate configuration file
# spex config template --type full --output ./
# ls cluster-template.yaml
cluster-template.yaml
Initialize the cluster topology file and modify the cluster topology file according to the actual environment. The modified information is as follows:
# 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
- Check the cluster topology file
# 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
- Add cluster configuration file to
SphereEx-Boottool management environment
# spex config add -f cluster-template.yaml
Add cluster successfully
- Install cluster
# 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......
Start cluster #
# 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......
View cluster running status
# 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 |
+-----------+------+------------------+
Login cluster #
This chapter introduces how to connect to the created cluster
# 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>
Register License #
- Upload License
Upload the obtained license file to the default storage directory: /demo/conf.
# scp -r sphere.license root@10.10.10.1:/root/demo/conf
- View License-key content
# cat /root/demo/conf/sphere.license
Ic5OXgAAACMAAAACAAAACwAAAAxjcmVEaWdlc3RTSEEtNTEy
- Register License
Login SphereEx- DBPlusEngine cluster, register License.
# mysql -uroot -p -P3308 -h127.0.0.1
sql> REGISTER LICENSE '[Ic5OXgAAACMAAAACAAAACwAAAAxjcmVEaWdlc3RTSEEtNTEy]';
Query OK, 0 rows affected (1.29 sec)
- Check whether the license is valid.
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
Data sharding #
This chapter will introduce how to use DistSQL (command tool) to create data shards. You can also use SphereEx-Console (SphereEx enterprise data service visualization platform) to implement data sharding.
Operation steps #
Create shards #
- Create distributed database
a. Create data source
Login MySQL database: 127.0.0.1/13306， create database test01 and test02 e.g.
# 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)
b.Create logic database
Login Proxy
# 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>
Create logic database
sql> CREATE DATABASE testdb;
Query OK, 0 rows affected (0.65 sec)
View logic database
sql> SHOW DATABASES;
+--------------------+
| schema_name |
+--------------------+
| mysql |
| information_schema |
| performance_schema |
| sys |
| testdb |
+--------------------+
5 rows in set (0.00 sec)
c.Add data source
Switch to logic database testdb
sql> USE testdb;
Database changed
Add data source
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)
d.View data source information
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)
So far, the distributed database has been created.
- Create sharding table rule
Select logic database
sql> USE testdb;
Database changed
Create data sharding rule
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)
View data sharding rule
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)
- Create table and insert data
a.Create table
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)
b.Insert data
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)
c.Preview shard information
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)
- View sharding result
Login database test01, view actual sharding data
# 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)
Login database test02, view actual sharding data
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)