Logo
Scenario two：Data Encryption

Scenario two：Data Encryption #

In this scenario, SphereEx-Console (a SphereEx enterprise data service visualization platform) is used to install a SphereEx-DBPlusEngine cluster, and data encryption can be quickly implemented through SphereEx-Console.

The data encryption scenario is limited to using SphereEx-Console to quickly build a local SphereEx-DBPlusEngine cluster and link a MySQL database to realize data encryption (other components and database installation are not included).

Operation Steps #

create-encryption-flow

Prepare resource environment #

InstanceIP AddressService PortRemark
1SphereEx-Boot127.0.0.1/SphereEx-Boot Toolkit
2SphereEx-Console127.0.0.18088SphereEx-Console Toolkit
3SphereEx-DBPlusEngine127.0.0.13212SphereEx-DBPlusEngine Cluster
4MySQL 8.0127.0.0.13306Install the database (master database) required by Sphere-Console through SphereEx-Boot
5MySQL 8.0127.0.0.13307Install the database (slave database) required by Sphere-Console through SphereEx-Boot
6ZooKeeper127.0.0.13666/3667ZooKeeper
7MySQL 8.0/13306Database for encryption
(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 #

Refers to Scenario two：Install SphereEx-Boot。

If SphereEx-Boot has been installed, this step can be ignored.

Install SphereEx-Console #

Before installing SphereEx-Console, make sure that the operating system has installed JDK 1.8.

1. Check JDK Version #

# java -version
openjdk version "1.8.0_312"
OpenJDK Runtime Environment (build 1.8.0_312-b07)
OpenJDK 64-Bit Server VM (build 25.312-b07, mixed mode)

2. Export Configuration File #

# mkdir console
# cd console
# spex console export    
# ls console_install.yaml
console_install.yaml

3. Modify Configuration File #

Modify according to the following parameter values, or modify according to the actual environment and keep the default parameters for unmentioned parameters.

# vi console_install.yaml
###################### SphereEx-Console Deploy Configuration Example #########################
 
# This file is an example configuration file.
# Here you can configure for the SphereEx-Console deploy.
# Deploy to remote must use ssh
 
#===================== Mysql Deploy Configuration ========================
 
# Required, Whether to install a new Mysql server
# If true Mysql master-slave will be installed
 
install_mysql: true
 
# If install Mysql is true the param is required
 
mysql:
  master:
    # install on local or remote
    file: /root/console/mysql.tar.xz
    host: 127.0.0.1
    listen_port: 3306
    ssh_user: root
    ssh_password: root
    ssh_port: 22
    install_dir: /root/console
 
  slave:
    # install on local or remote
    host: 127.0.0.1
    file: /root/console/mysql.tar.xz
    # If two Mysql servers are on the same machine, listen port must be different
    listen_port: 3307
    ssh_user: root
    ssh_password: root
    ssh_port: 22
    install_dir: /root/console
 
 #==================== Console Deploy Configuration =======================
console:
  # install on local or remote
  ssh_user: root
  ssh_password: root
  ssh_port: 22
  host: 127.0.0.1
  # Required, SphereEx-Console file. for example:/root/console/SphereEx-Console.tar.gz
  file: /root/console/sphereex-console.1.1.0.tar.gz
  # Required, deployment directory, remote or local
  install_dir: /root/console
  # Required, deployment directory, remote or local
  console_config:
    server:
      port: 8088
    # software home path
    software:
      home: /opt/software

4. Execute Install Console Commend #

# spex console install -n console_demo -c console_install.yaml
create install directory          
host:127.0.0.1 status:success
install mysql8          
host:127.0.0.1 status:success
push mysql conf          
host:127.0.0.1 status:success
create install directory          
host:127.0.0.1 status:success
install mysql8          
host:127.0.0.1 status:success
push mysql conf          
host:127.0.0.1 status:success
start mysql          
host:127.0.0.1 status:success
start mysql          
host:127.0.0.1 status:success
push mysql shell          
host:127.0.0.1 status:success
opt shell          
host:127.0.0.1 status:success
push mysql shell          
host:127.0.0.1 status:success
opt shell          
host:127.0.0.1 status:success
create install directory          
host:127.0.0.1 status:success
push console file          
host:127.0.0.1 status:success
install console          
host:127.0.0.1 status:success
push console yml          
host:127.0.0.1 status:success
remove install file          
host:127.0.0.1 status:success
pull console sql          
host:127.0.0.1 status:success
pull console sql data          
host:127.0.0.1 status:success
push console sql          
host:127.0.0.1 status:success
init console mysql task          
host:127.0.0.1 status:success

5. Start Console。 #

Enter the installation directory of the console and execute the startup command

# cd sphereex-console-1.1.0
# bin/start.sh 
Starting the SphereEx-Console ...
Please check the STDOUT file: /root/console/sphereex-console-1.1.0/logs/stdout.log

6. Visit Console #

Use the server’s external network IP address and port to access the SphereEx-Console platform through a browser, and log in with the account and password in the configuration file.

Such as: 127.0.0.1:8088

Default username: admin

Default password: admin

Console 登录页

Console 概览页

Create Cluster Using SphereEx-Console #

This part mainly introduces how to use SphereEx-Console to create a SphereEx-DBPlusEngine cluster and implement data encryption.

Operation Steps #

Console create cluster

1. Register Host #

Select “Resources-Host” in the left navigation bar, click “Register Host”, fill in the required information, and click “OK” to complete the registration of the host.

注册主机

2. Create Governance Centre #

Upload the ZooKeeper installation package to the /opt/software path

Select “Resources-Governance Center” in the left navigation bar, click “New”, fill in the required information, and click “OK” to complete the creation of the governance center.

创建治理中心

配置监控

3. Create Cluster #

Upload the installation package of SphereEx-DBPlusEngine to /opt/software path.

Fill in the necessary information, and select the previously created governance centre.

创建集群

增加计算节点

After filling in the required information, click the “Install” button.

When the “Install” button turns grey, the operation result shows “Installation successful”, indicating that the cluster has been created successfully. Click OK to return to the cluster list.

安装成功

On the “Register Storage Node” interface, select the database to be sharded and fill in the required information.

注册存储节点

Click Probe, after the probing is successful, click OK.

Register the second database using the same method.

节点管理

4. Create Logic database #

Click “Logical Database” in the left navigation bar to enter the list of logical databases, select the cluster name “cluster test001”, and click “Create Logical Database”.

Enter the “logic database name”, select the storage node, click the symbol “>”, and click OK to associate the database with the cluster.

节点管理

5. Create Table #

Select “Object”, select the cluster and logical database, and click “New Table”.

Enter the table creation statement according to the reference example, and click OK.

sql> CREATE TABLE t_encrypt(
    id INT(8), 
    mobile VARCHAR(50), 
    id_card_no VARCHAR(50)
);

新建表

6. Create Encrypt Rule #

Select the created data table: t_encrypt, click “Add Encryption” –> select “General” encryption –> click “Add Encryption Plugin”

添加加密插件

Select “Encryption Algorithm”, select “Column to Encrypt”, check “Store Plaintext”, and click “Add” –> click “OK”.

添加插件

7. Insert Data #

Login SphereEx-DBPlusEngine Cluster, Execute insert data commend

# mysql -uroot -proot -P3212 -h127.0.0.1
sql> use logical001;
sql> INSERT INTO t_encrypt (id, mobile_plain, id_card_no)
    -> VALUES (1, 18236483857, 220605194709308170),
    ->        (2, 15686689114, 360222198806088804),
    ->        (3, 13983621809, 430204195612042092);
Query OK, 3 rows affected (0.02 sec)

8. Check Data Encryption Result #

Login SphereEx-DBPlusEngine Cluster, View data is ciphertext display.

Note: If you need to display plain text in the SphereEx-DBPlusEngine cluster, just uncheck “Use encrypted column query”.

# mysql -uroot -proot -P3212 -h127.0.0.1
sql> use logical001;
sql> SELECT * FROM t_encrypt;
+------+----------------------------------+--------------------+
| id   | mobile                           | id_card_no         |
+------+----------------------------------+--------------------+
|    1 | 6f655ea3b73e32d54ae2d34d672c0c55 | 220605194709308170 |
|    2 | a2193d0e8de4c2c4f166448e22ba97ae | 360222198806088804 |
|    3 | 3cf1fd442630071ca664bbd5e683f7f6 | 430204195612042092 |
+------+----------------------------------+--------------------+
3 rows in set (0.00 sec)

Log in to MySQL to view the data in ciphertext, see mobile_cipher.

Because “store plaintext” is checked, the mobile phone number column is also stored in plaintext in the database, see mobile_plain.

# mysql -uroot -proot -P13306 -h127.0.0.1
sql> use ss03;
sql> select * from t_encrypt;
+------+----------------------------------+--------------+--------------------+
| id   | mobile_cipher                    | mobile_plain | id_card_no         |
+------+----------------------------------+--------------+--------------------+
|    1 | 6f655ea3b73e32d54ae2d34d672c0c55 | 18236483857  | 220605194709308170 |
|    2 | a2193d0e8de4c2c4f166448e22ba97ae | 15686689114  | 360222198806088804 |
|    3 | 3cf1fd442630071ca664bbd5e683f7f6 | 13983621809  | 430204195612042092 |
+------+----------------------------------+--------------+--------------------+