shardingsphere-proxy(5.4.1)部署(单分表+分表同时加密+单分表)

环境:
Os:Centos 7
版本:5.4.1
说明:
a.使用二进制分发包启动 Proxy，需要环境具备 Java JRE 8 或更高版本。
b.java版本
[root@es soft]# java -version
java version "1.8.0_361"
Java(TM) SE Runtime Environment (build 1.8.0_361-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.361-b09, mixed mode)

1.下载
下载地址
https://archive.apache.org/dist/shardingsphere/5.4.1/

下载的文件如下:
192.168.1.113机器
cd /soft
wget https://archive.apache.org/dist/shardingsphere/5.4.1/apache-shardingsphere-5.4.1-shardingsphere-proxy-bin.tar.gz

2.下载jdk
可以在oracle官网下载,我这里下载的文件如下:
mysql-connector-java-8.0.11.tar.gz
cd /soft
wget https://downloads.mysql.com/archives/get/p/3/file/mysql-connector-java-8.0.11.tar.gz

3.解压安装
cd /soft
[root@localhost shardingsphere-proxy]#tar -xvf apache-shardingsphere-5.4.1-shardingsphere-proxy-bin.tar.gz
[root@localhost shardingsphere-proxy]#mv apache-shardingsphere-5.4.1-shardingsphere-proxy-bin /opt/shardingsphere-proxy-5.4.1

4.引入MySQL依赖
在sharding-proxy 路径下新建 ext-lib 目录，将 mysql-connector-java-8.0.11.jar 驱动包放进来.
[root@localhost shardingsphere-proxy]# cd /opt/shardingsphere-proxy-5.4.1
[root@localhost shardingsphere-proxy]# mkdir ext-lib

[root@localhost shardingsphere-proxy]#cd /soft
[root@localhost shardingsphere-proxy]#tar -xvf mysql-connector-java-8.0.11.tar.gz
[root@localhost shardingsphere-proxy]#cd mysql-connector-java-8.0.11
[root@localhost mysql-connector-java-8.0.11]# cp mysql-connector-java-8.0.11.jar /opt/shardingsphere-proxy-5.4.1/ext-lib/

5.修改 server.yaml 文件
在使用默认配置之前，最好备份一下
[root@localhost conf]#cd /opt/shardingsphere-proxy-5.4.1
[root@localhost conf]#cp server.yaml bak_server.yaml

vi /opt/shardingsphere-proxy-5.4.1/conf/server.yaml
内容如下:

######################################################################################################
# 
# If you want to configure governance, authorization and proxy properties, please refer to this file.
# 
######################################################################################################

mode:
  type: Standalone
#  repository:
#    type: ZooKeeper
#    props:
#      namespace: governance_ds
#      server-lists: localhost:2181
#      retryIntervalMilliseconds: 500
#      timeToLiveSeconds: 60
#      maxRetries: 3
#      operationTimeoutMilliseconds: 500
#
authority:
  users:
    - user: root@%
      password: 123456
#    - user: sharding
#      password: sharding
  privilege:
    type: ALL_PERMITTED
#
#transaction:
#  defaultType: XA
#  providerType: Atomikos
#
#sqlParser:
#  sqlCommentParseEnabled: false
#  sqlStatementCache:
#    initialCapacity: 2000
#    maximumSize: 65535
#  parseTreeCache:
#    initialCapacity: 128
#    maximumSize: 1024

说明:
以下配置的账号是proxy层的账号,与数据源配置的账号没有关系，访问proxy层的3307端口，需要通过该账号进行连接

authority:
  users:
    - user: root@%
      password: 123456

6.尝试启动
/opt/shardingsphere-proxy-5.4.1/bin/start.sh

shardingsphere-proxy默认使用的端口是3307

[root@host113 bin]# ss -nlp|grep 3307
tcp    LISTEN     0      128      :::3307                 :::*                   users:(("java",pid=5894,fd=340))

7.尝试连接shardingsphere-proxy

需要安装mysql客户端

[root@host113 bin]# /home/mysql57/bin/mysql -h 192.168.1.113 -uroot -p123456 -P3307
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: 5.7.22-ShardingSphere-Proxy 5.4.1 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> show databases;
+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shardingsphere     |
| sys                |
+--------------------+
5 rows in set (0.02 sec)

这里既没有做分表,也没有做表字段加密,输出就是这样的,下面尝试做分表和字段加密

##############################单分表###############################################
说明:
a.需要提前在真实的数据库服务器上创建库tb_test;

1.准备配置文件
配置文件以shardingsphere表现的库名命名,方便管理
cd /opt/shardingsphere-proxy-5.4.1/conf
cp config-sharding.yaml config-sharding-db_test.yaml

vi config-sharding-db_test.yaml
找到mysql的段落,注意格式(层级之间缩进2个空格)
内容如下

######################################################################################################
#
# If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.
#
######################################################################################################

databaseName: sharding_db_test
#
dataSources:
  ds_0:
    url: jdbc:mysql://192.168.1.113:3306/db_test?serverTimezone=UTC&useSSL=false ##这个库需要提前创建好,下面的账号需要对该库有所有的权限
    username: root
    password: hxl3040
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
#  ds_1:
#    url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
#    username: root
#    password:
#    connectionTimeoutMilliseconds: 30000
#    idleTimeoutMilliseconds: 60000
#    maxLifetimeMilliseconds: 1800000
#    maxPoolSize: 50
#    minPoolSize: 1
#
rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_0.t_order_${0..9} ##这里的ds_0与上面配置的名称保持一致,我这里分了0-9共10个表
      tableStrategy:
        standard:
          shardingColumn: order_id               ##分表规则字段
          shardingAlgorithmName: t_order_inline  ##分表规则算法,下面的具体的定义
      keyGenerateStrategy:
        column: order_id
        keyGeneratorName: snowflake
      auditStrategy:
        auditorNames:
          - sharding_key_required_auditor
        allowHintDisable: true
#    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:
  defaultAuditStrategy:
    auditorNames:
      - sharding_key_required_auditor
    allowHintDisable: true
#
  shardingAlgorithms:         ##分表规则
#    database_inline:
#      type: INLINE
#      props:
#        algorithm-expression: ds_${user_id % 2}
    t_order_inline:          ##分表规则算法
      type: INLINE
      props:
        algorithm-expression: t_order_${order_id % 10}  ##mod算法
#    t_order_item_inline:
#      type: INLINE
#      props:
#        algorithm-expression: t_order_item_${order_id % 2}
#
  keyGenerators:
    snowflake:
      type: SNOWFLAKE

  auditors:
    sharding_key_required_auditor:
      type: DML_SHARDING_CONDITIONS
#
#- !BROADCAST
#  tables:
#    - t_address

2.重启
/opt/shardingsphere-proxy-5.4.1/bin/stop.sh
/opt/shardingsphere-proxy-5.4.1/bin/start.sh

3.登录

[root@host113 conf]# /home/mysql57/bin/mysql -h 192.168.1.113 -uroot -p123456 -P3307
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: 5.7.22-ShardingSphere-Proxy 5.4.1 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> show databases;
+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sharding_db_test   |
| shardingsphere     |
| sys                |
+--------------------+
6 rows in set (0.02 sec)

这个时候可以看到库sharding_db_test,目前是没有任何表的
mysql> use sharding_db_test;
Database changed
mysql> show tables;
Empty set (0.01 sec)

4.创建表
这里建表必须是通过proxy层创建
/home/mysql57/bin/mysql -h 192.168.1.113 -uroot -p123456 -P3307
use sharding_db_test;
CREATE TABLE `t_order` (
`order_id` bigint NOT NULL ,
`order_name` varchar(100) DEFAULT NULL,
`total_money` varchar(100) DEFAULT NULL,
`user_id` bigint NOT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

mysql> show tables;
+----------------------------+
| Tables_in_sharding_db_test |
+----------------------------+
| t_order |
+----------------------------+
1 row in set (0.00 sec)

这个时候登录真实的数据库,查看有几个表

[root@host113 bin]# /home/mysql57/bin/mysql -h 192.168.1.113 -uroot -phxl3040 -P3306
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 2640986
Server version: 5.7.39-log MySQL Community Server (GPL)

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> use db_test;
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_db_test |
+-------------------+
| t_order_0         |
| t_order_1         |
| t_order_2         |
| t_order_3         |
| t_order_4         |
| t_order_5         |
| t_order_6         |
| t_order_7         |
| t_order_8         |
| t_order_9         |
+-------------------+
10 rows in set (0.00 sec)

　　总共10个表

5.通过proxy层尝试写入数据
/home/mysql57/bin/mysql -h 192.168.1.113 -uroot -p123456 -P3307
use sharding_db_test;
insert into t_order(order_id,order_name,total_money,user_id) values(1,'name1',100,1);
insert into t_order(order_id,order_name,total_money,user_id) values(2,'name2',200,2);
insert into t_order(order_id,order_name,total_money,user_id) values(3,'name3',300,3);
insert into t_order(order_id,order_name,total_money,user_id) values(4,'name4',400,4);
insert into t_order(order_id,order_name,total_money,user_id) values(5,'name5',500,5);
insert into t_order(order_id,order_name,total_money,user_id) values(6,'name6',600,6);
insert into t_order(order_id,order_name,total_money,user_id) values(7,'name7',700,7);
insert into t_order(order_id,order_name,total_money,user_id) values(8,'name8',800,8);
insert into t_order(order_id,order_name,total_money,user_id) values(9,'name9',900,9);
insert into t_order(order_id,order_name,total_money,user_id) values(10,'name10',1000,10);

mysql> select * from t_order;
ERROR 16000 (44000): SQL audit failed, error message: Not allow DML operation without sharding conditions.
mysql> select count(1) from t_order;
ERROR 16000 (44000): SQL audit failed, error message: Not allow DML operation without sharding conditions.

解决办法1:

修改配置文件,将如下项注释掉
      auditStrategy:
        auditorNames:
          - sharding_key_required_auditor
        allowHintDisable: true

  defaultAuditStrategy:
    auditorNames:
      - sharding_key_required_auditor
    allowHintDisable: true

  auditors:
    sharding_key_required_auditor:
      type: DML_SHARDING_CONDITIONS
修改如下:
#      auditStrategy:
#        auditorNames:
#          - sharding_key_required_auditor
#        allowHintDisable: true


#  defaultAuditStrategy:
#    auditorNames:
#      - sharding_key_required_auditor
#    allowHintDisable: true

#  auditors:
#    sharding_key_required_auditor:
#      type: DML_SHARDING_CONDITIONS

然后重新启动
/opt/shardingsphere-proxy-5.4.1/bin/stop.sh
/opt/shardingsphere-proxy-5.4.1/bin/start.sh

解决办法2:
mysql命令带上-c参数登录

/home/mysql57/bin/mysql -h 192.168.1.113 -uroot -p123456 -P3307 -c
mysql> use sharding_db_test;
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> /* SHARDINGSPHERE_HINT: DISABLE_AUDIT_NAMES=sharding_key_required_auditor */
    -> SELECT * FROM t_order;
+----------+------------+-------------+---------+
| order_id | order_name | total_money | user_id |
+----------+------------+-------------+---------+
|       10 | name10     | 1000        |      10 |
|        1 | name1      | 100         |       1 |
|        2 | name2      | 200         |       2 |
|        3 | name3      | 300         |       3 |
|        4 | name4      | 400         |       4 |
|        5 | name5      | 500         |       5 |
|        6 | name6      | 600         |       6 |
|        7 | name7      | 700         |       7 |
|        8 | name8      | 800         |       8 |
|        9 | name9      | 900         |       9 |
+----------+------------+-------------+---------+
10 rows in set (0.03 sec)


mysql> /* SHARDINGSPHERE_HINT: DISABLE_AUDIT_NAMES=sharding_key_required_auditor */
    -> select count(1) from t_order;
+----------+
| count(1) |
+----------+
|       10 |
+----------+
1 row in set (0.03 sec)


或是同一行执行
mysql> /* SHARDINGSPHERE_HINT: DISABLE_AUDIT_NAMES=sharding_key_required_auditor */ SELECT * FROM t_order;
+----------+------------+-------------+---------+
| order_id | order_name | total_money | user_id |
+----------+------------+-------------+---------+
|       10 | name10     | 1000        |      10 |
|        1 | name1      | 100         |       1 |
|        2 | name2      | 200         |       2 |
|        3 | name3      | 300         |       3 |
|        4 | name4      | 400         |       4 |
|        5 | name5      | 500         |       5 |
|        6 | name6      | 600         |       6 |
|        7 | name7      | 700         |       7 |
|        8 | name8      | 800         |       8 |
|        9 | name9      | 900         |       9 |
+----------+------------+-------------+---------+
10 rows in set (0.01 sec)


登录真实的数据库查看数据分布
[root@host113 bin]# /home/mysql57/bin/mysql -h 192.168.1.113 -uroot -phxl3040 -P3306
mysql> use db_test;
mysql> show tables;
+-------------------+
| Tables_in_db_test |
+-------------------+
| t_order_0         |
| t_order_1         |
| t_order_2         |
| t_order_3         |
| t_order_4         |
| t_order_5         |
| t_order_6         |
| t_order_7         |
| t_order_8         |
| t_order_9         |
+-------------------+
10 rows in set (0.00 sec)

mysql> select * from t_order_0;
+----------+------------+-------------+---------+
| order_id | order_name | total_money | user_id |
+----------+------------+-------------+---------+
|       10 | name10     | 1000        |      10 |
+----------+------------+-------------+---------+
1 row in set (0.00 sec)

mysql> select * from t_order_1;
+----------+------------+-------------+---------+
| order_id | order_name | total_money | user_id |
+----------+------------+-------------+---------+
|        1 | name1      | 100         |       1 |
+----------+------------+-------------+---------+
1 row in set (0.00 sec)

mysql> select * from t_order_2;
+----------+------------+-------------+---------+
| order_id | order_name | total_money | user_id |
+----------+------------+-------------+---------+
|        2 | name2      | 200         |       2 |
+----------+------------+-------------+---------+
1 row in set (0.00 sec)

mysql> select * from t_order_3;
+----------+------------+-------------+---------+
| order_id | order_name | total_money | user_id |
+----------+------------+-------------+---------+
|        3 | name3      | 300         |       3 |
+----------+------------+-------------+---------+
1 row in set (0.00 sec)

mysql> select * from t_order_4;
+----------+------------+-------------+---------+
| order_id | order_name | total_money | user_id |
+----------+------------+-------------+---------+
|        4 | name4      | 400         |       4 |
+----------+------------+-------------+---------+
1 row in set (0.00 sec)

mysql> select * from t_order_5;
+----------+------------+-------------+---------+
| order_id | order_name | total_money | user_id |
+----------+------------+-------------+---------+
|        5 | name5      | 500         |       5 |
+----------+------------+-------------+---------+
1 row in set (0.00 sec)

mysql> select * from t_order_6;
+----------+------------+-------------+---------+
| order_id | order_name | total_money | user_id |
+----------+------------+-------------+---------+
|        6 | name6      | 600         |       6 |
+----------+------------+-------------+---------+
1 row in set (0.00 sec)

mysql> select * from t_order_7;
+----------+------------+-------------+---------+
| order_id | order_name | total_money | user_id |
+----------+------------+-------------+---------+
|        7 | name7      | 700         |       7 |
+----------+------------+-------------+---------+
1 row in set (0.00 sec)

mysql> select * from t_order_8;
+----------+------------+-------------+---------+
| order_id | order_name | total_money | user_id |
+----------+------------+-------------+---------+
|        8 | name8      | 800         |       8 |
+----------+------------+-------------+---------+
1 row in set (0.00 sec)

mysql> select * from t_order_9;
+----------+------------+-------------+---------+
| order_id | order_name | total_money | user_id |
+----------+------------+-------------+---------+
|        9 | name9      | 900         |       9 |
+----------+------------+-------------+---------+
1 row in set (0.00 sec)

可以看到数据分布到每张表了

############################################分表+加密#######################################
说明:
a.若是对表进行了分片同时也加密，配置在config-sharding.yaml文件中修改
b.单纯只是加密不分片，只需要在config-encrypt.yaml配置文件中修改
c.若有多个数据库下的表进行分片，建议采用单独yaml文件的形式配置数据源

1.准备配置文件
配置文件以shardingsphere表现的库名命名,方便管理
cd /opt/shardingsphere-proxy-5.4.1/conf
cp config-sharding.yaml config-sharding-db_test01.yaml

vi config-sharding-db_test01.yaml
找到mysql的段落,注意格式(层级之间缩进2个空格)
内容如下

######################################################################################################
#
# If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.
#
######################################################################################################

databaseName: sharding_db_test01
#
dataSources:
  ds_0:
    url: jdbc:mysql://192.168.1.113:3306/db_test01?serverTimezone=UTC&useSSL=false
    username: root
    password: hxl3040
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
#  ds_1:
#    url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
#    username: root
#    password:
#    connectionTimeoutMilliseconds: 30000
#    idleTimeoutMilliseconds: 60000
#    maxLifetimeMilliseconds: 1800000
#    maxPoolSize: 50
#    minPoolSize: 1
#
rules:
- !SHARDING
  tables:
    t_order_a:
      actualDataNodes: ds_0.t_order_a_${0..9}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_a_inline ##名称自定义,需要与下面的保持一致
      keyGenerateStrategy:
        column: order_id
        keyGeneratorName: snowflake
      auditStrategy:
        auditorNames:
          - sharding_key_required_auditor
        allowHintDisable: true
#    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:
  defaultAuditStrategy:
    auditorNames:
      - sharding_key_required_auditor
    allowHintDisable: true
#
  shardingAlgorithms:
#    database_inline:
#      type: INLINE
#      props:
#        algorithm-expression: ds_${user_id % 2}
     t_order_a_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_a_${order_id % 10} ##t_order_a这里是proxy体现的表名,分发到t_order_a_0..9
#    t_order_item_inline:
#      type: INLINE
#      props:
#        algorithm-expression: t_order_item_${order_id % 2}
#
  keyGenerators:
    snowflake:
      type: SNOWFLAKE

  auditors:
    sharding_key_required_auditor:
      type: DML_SHARDING_CONDITIONS
#
#- !BROADCAST
#  tables:
#    - t_address

- !ENCRYPT
  encryptors:
    aes_encryptor:
      type: AES
      props:
        aes-key-value: 123456abc
  tables:
    t_order_a:
      columns:
        proxy_mobile: ##这个字段是proxy层的字段 而不是数据库的实际自动，建议与数据库真正的字段保持一致，我这里为了演示，特意与数据库真实字段区分,在proxy创建表时必须使用该字段
          cipher:
            name: mobile ##这个是数据库真正的字段
            encryptorName: aes_encryptor

2.重启
/opt/shardingsphere-proxy-5.4.1/bin/stop.sh
/opt/shardingsphere-proxy-5.4.1/bin/start.sh

3.创建表
这里建表必须是通过proxy层创建
/home/mysql57/bin/mysql -h 192.168.1.113 -uroot -p123456 -P3307
use sharding_db_test01;
CREATE TABLE `t_order_a` (
`order_id` bigint NOT NULL ,
`order_name` varchar(100) DEFAULT NULL,
`total_money` varchar(100) DEFAULT NULL,
`user_id` bigint NOT NULL,
`proxy_mobile` varchar(128) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这里的proxy_mobile是proxy层的字段，真正在数据库上创建的字段是mobile

mysql> show tables;
+------------------------------+
| Tables_in_sharding_db_test01 |
+------------------------------+
| t_order_a |
+------------------------------+
1 row in set (0.00 sec)

登录具体的数据库查看

/home/mysql57/bin/mysql -h 192.168.1.113 -uroot -phxl3040 -P3306

mysql> use db_test01;
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_db_test01 |
+---------------------+
| t_order_a_0         |
| t_order_a_1         |
| t_order_a_2         |
| t_order_a_3         |
| t_order_a_4         |
| t_order_a_5         |
| t_order_a_6         |
| t_order_a_7         |
| t_order_a_8         |
| t_order_a_9         |
+---------------------+
10 rows in set (0.00 sec)


查看表结构(这个是真实数据库的表结构)
CREATE TABLE `t_order_a_0` (
  `order_id` bigint(20) NOT NULL,
  `order_name` varchar(100) DEFAULT NULL,
  `total_money` varchar(100) DEFAULT NULL,
  `user_id` bigint(20) NOT NULL,
  `mobile` varchar(4000) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

4.从proxy侧写入数据
注意带上-c 选项,因为我们后续查询可能不带分表字段的条件,让hint能生效
/home/mysql57/bin/mysql -h 192.168.1.113 -uroot -p123456 -P3307 -c

use sharding_db_test01;
insert into t_order_a(order_id,order_name,total_money,user_id,proxy_mobile) values(1,'name1',100,1,'158123456');
insert into t_order_a(order_id,order_name,total_money,user_id,proxy_mobile) values(2,'name2',200,2,'158123456');
insert into t_order_a(order_id,order_name,total_money,user_id,proxy_mobile) values(3,'name3',300,3,'158123456');
insert into t_order_a(order_id,order_name,total_money,user_id,proxy_mobile) values(4,'name4',400,4,'158123456');
insert into t_order_a(order_id,order_name,total_money,user_id,proxy_mobile) values(5,'name5',500,5,'158123456');
insert into t_order_a(order_id,order_name,total_money,user_id,proxy_mobile) values(6,'name6',600,6,'158123456');
insert into t_order_a(order_id,order_name,total_money,user_id,proxy_mobile) values(7,'name7',700,7,'158123456');
insert into t_order_a(order_id,order_name,total_money,user_id,proxy_mobile) values(8,'name8',800,8,'158123456');
insert into t_order_a(order_id,order_name,total_money,user_id,proxy_mobile) values(9,'name9',900,9,'158123456');
insert into t_order_a(order_id,order_name,total_money,user_id,proxy_mobile) values(10,'name10',1000,10,'158123456');

查看
mysql> /* SHARDINGSPHERE_HINT: DISABLE_AUDIT_NAMES=sharding_key_required_auditor */ SELECT * FROM t_order_a;
+----------+------------+-------------+---------+--------------+
| order_id | order_name | total_money | user_id | proxy_mobile |
+----------+------------+-------------+---------+--------------+
|       10 | name10     | 1000        |      10 | 158123456    |
|        1 | name1      | 100         |       1 | 158123456    |
|        2 | name2      | 200         |       2 | 158123456    |
|        3 | name3      | 300         |       3 | 158123456    |
|        4 | name4      | 400         |       4 | 158123456    |
|        5 | name5      | 500         |       5 | 158123456    |
|        6 | name6      | 600         |       6 | 158123456    |
|        7 | name7      | 700         |       7 | 158123456    |
|        8 | name8      | 800         |       8 | 158123456    |
|        9 | name9      | 900         |       9 | 158123456    |
+----------+------------+-------------+---------+--------------+
10 rows in set (0.02 sec)


从真实数据库查询
/home/mysql57/bin/mysql -h 192.168.1.113 -uroot -phxl3040 -P3306 -c
mysql> use db_test01;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_db_test01 |
+---------------------+
| t_order_a_0         |
| t_order_a_1         |
| t_order_a_2         |
| t_order_a_3         |
| t_order_a_4         |
| t_order_a_5         |
| t_order_a_6         |
| t_order_a_7         |
| t_order_a_8         |
| t_order_a_9         |
+---------------------+
10 rows in set (0.00 sec)

mysql> select * from t_order_a_0;
+----------+------------+-------------+---------+--------------------------+
| order_id | order_name | total_money | user_id | mobile                   |
+----------+------------+-------------+---------+--------------------------+
|       10 | name10     | 1000        |      10 | AWWFGyhgDB7XkzxnVWKjwA== |
+----------+------------+-------------+---------+--------------------------+
1 row in set (0.00 sec)

mysql> select * from t_order_a_1;
+----------+------------+-------------+---------+--------------------------+
| order_id | order_name | total_money | user_id | mobile                   |
+----------+------------+-------------+---------+--------------------------+
|        1 | name1      | 100         |       1 | AWWFGyhgDB7XkzxnVWKjwA== |
+----------+------------+-------------+---------+--------------------------+
1 row in set (0.00 sec)

mysql> select * from t_order_a_2;
+----------+------------+-------------+---------+--------------------------+
| order_id | order_name | total_money | user_id | mobile                   |
+----------+------------+-------------+---------+--------------------------+
|        2 | name2      | 200         |       2 | AWWFGyhgDB7XkzxnVWKjwA== |
+----------+------------+-------------+---------+--------------------------+
1 row in set (0.00 sec)

mysql> select * from t_order_a_3;
+----------+------------+-------------+---------+--------------------------+
| order_id | order_name | total_money | user_id | mobile                   |
+----------+------------+-------------+---------+--------------------------+
|        3 | name3      | 300         |       3 | AWWFGyhgDB7XkzxnVWKjwA== |
+----------+------------+-------------+---------+--------------------------+
1 row in set (0.00 sec)

mysql> select * from t_order_a_4;
+----------+------------+-------------+---------+--------------------------+
| order_id | order_name | total_money | user_id | mobile                   |
+----------+------------+-------------+---------+--------------------------+
|        4 | name4      | 400         |       4 | AWWFGyhgDB7XkzxnVWKjwA== |
+----------+------------+-------------+---------+--------------------------+
1 row in set (0.00 sec)

mysql> select * from t_order_a_5;
+----------+------------+-------------+---------+--------------------------+
| order_id | order_name | total_money | user_id | mobile                   |
+----------+------------+-------------+---------+--------------------------+
|        5 | name5      | 500         |       5 | AWWFGyhgDB7XkzxnVWKjwA== |
+----------+------------+-------------+---------+--------------------------+
1 row in set (0.00 sec)

mysql> select * from t_order_a_6;
+----------+------------+-------------+---------+--------------------------+
| order_id | order_name | total_money | user_id | mobile                   |
+----------+------------+-------------+---------+--------------------------+
|        6 | name6      | 600         |       6 | AWWFGyhgDB7XkzxnVWKjwA== |
+----------+------------+-------------+---------+--------------------------+
1 row in set (0.00 sec)

mysql> select * from t_order_a_7;
+----------+------------+-------------+---------+--------------------------+
| order_id | order_name | total_money | user_id | mobile                   |
+----------+------------+-------------+---------+--------------------------+
|        7 | name7      | 700         |       7 | AWWFGyhgDB7XkzxnVWKjwA== |
+----------+------------+-------------+---------+--------------------------+
1 row in set (0.00 sec)

mysql> select * from t_order_a_8;
+----------+------------+-------------+---------+--------------------------+
| order_id | order_name | total_money | user_id | mobile                   |
+----------+------------+-------------+---------+--------------------------+
|        8 | name8      | 800         |       8 | AWWFGyhgDB7XkzxnVWKjwA== |
+----------+------------+-------------+---------+--------------------------+
1 row in set (0.00 sec)

mysql> select * from t_order_a_9;
+----------+------------+-------------+---------+--------------------------+
| order_id | order_name | total_money | user_id | mobile                   |
+----------+------------+-------------+---------+--------------------------+
|        9 | name9      | 900         |       9 | AWWFGyhgDB7XkzxnVWKjwA== |
+----------+------------+-------------+---------+--------------------------+
1 row in set (0.00 sec)

　　可以看到加密字段已经加密

##################################只加密######################################
1.准备配置文件
配置文件以shardingsphere表现的库名命名,方便管理
cd /opt/shardingsphere-proxy-5.4.1/conf
cp config-encrypt.yaml config-encrypt-db_test02.yaml

vi config-encrypt-db_test02.yaml
找到mysql的段落,注意格式(层级之间缩进2个空格)
内容如下:

######################################################################################################
#
# If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.
#
######################################################################################################
#
databaseName: encrypt_db_test02
#
dataSources:
  ds_0:
    url: jdbc:mysql://192.168.1.113:3306/db_test02?serverTimezone=UTC&useSSL=false
    username: root
    password: hxl3040
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
#  ds_1:
#    url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
#    username: root
#    password:
#    connectionTimeoutMilliseconds: 30000
#    idleTimeoutMilliseconds: 60000
#    maxLifetimeMilliseconds: 1800000
#    maxPoolSize: 50
#    minPoolSize: 1
#
rules:
- !ENCRYPT
  encryptors:
    aes_encryptor:
      type: AES
      props:
        aes-key-value: 123456abc
  tables:
    t_order_b:
      columns:
        proxy_mobile: ##这个字段是proxy层的字段 而不是数据库的实际字段，建议与数据库真正的字段保持一致，我这里为了演示，特意与数据库真实字段区分
          cipher:
            name: mobile ##这个是数据库真正的字段
            encryptorName: aes_encryptor

2.重启
/opt/shardingsphere-proxy-5.4.1/bin/stop.sh
/opt/shardingsphere-proxy-5.4.1/bin/start.sh

3.创建表
这里建表必须是通过proxy层创建
/home/mysql57/bin/mysql -h 192.168.1.113 -uroot -p123456 -P3307
use encrypt_db_test02;
CREATE TABLE `t_order_b` (
`order_id` bigint NOT NULL ,
`order_name` varchar(100) DEFAULT NULL,
`total_money` varchar(100) DEFAULT NULL,
`user_id` bigint NOT NULL,
`proxy_mobile` varchar(128) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这里的proxy_mobile是proxy层的字段，真正在数据库上创建的字段是mobile

真实数据库的表结构是这样的
CREATE TABLE `t_order_b` (
`order_id` bigint(20) NOT NULL,
`order_name` varchar(100) DEFAULT NULL,
`total_money` varchar(100) DEFAULT NULL,
`user_id` bigint(20) NOT NULL,
`mobile` varchar(4000) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

4.写入数据
通过proxy写入数据

/home/mysql57/bin/mysql -h 192.168.1.113 -uroot -p123456 -P3307
use encrypt_db_test02;
insert into t_order_b(order_id,order_name,total_money,user_id,proxy_mobile) values(1,'name1',100,1,'158123456');
insert into t_order_b(order_id,order_name,total_money,user_id,proxy_mobile) values(2,'name2',200,2,'158123456');
insert into t_order_b(order_id,order_name,total_money,user_id,proxy_mobile) values(3,'name3',300,3,'158123456');
insert into t_order_b(order_id,order_name,total_money,user_id,proxy_mobile) values(4,'name4',400,4,'158123456');
insert into t_order_b(order_id,order_name,total_money,user_id,proxy_mobile) values(5,'name5',500,5,'158123456');
insert into t_order_b(order_id,order_name,total_money,user_id,proxy_mobile) values(6,'name6',600,6,'158123456');
insert into t_order_b(order_id,order_name,total_money,user_id,proxy_mobile) values(7,'name7',700,7,'158123456');
insert into t_order_b(order_id,order_name,total_money,user_id,proxy_mobile) values(8,'name8',800,8,'158123456');
insert into t_order_b(order_id,order_name,total_money,user_id,proxy_mobile) values(9,'name9',900,9,'158123456');
insert into t_order_b(order_id,order_name,total_money,user_id,proxy_mobile) values(10,'name10',1000,10,'158123456');

proxy端查询
mysql> select * from t_order_b;
+----------+------------+-------------+---------+--------------+
| order_id | order_name | total_money | user_id | proxy_mobile |
+----------+------------+-------------+---------+--------------+
|        1 | name1      | 100         |       1 | 158123456    |
|        2 | name2      | 200         |       2 | 158123456    |
|        3 | name3      | 300         |       3 | 158123456    |
|        4 | name4      | 400         |       4 | 158123456    |
|        5 | name5      | 500         |       5 | 158123456    |
|        6 | name6      | 600         |       6 | 158123456    |
|        7 | name7      | 700         |       7 | 158123456    |
|        8 | name8      | 800         |       8 | 158123456    |
|        9 | name9      | 900         |       9 | 158123456    |
|       10 | name10     | 1000        |      10 | 158123456    |
+----------+------------+-------------+---------+--------------+
10 rows in set (0.04 sec)

真实数据库端查询
mysql> select * from t_order_b;
+----------+------------+-------------+---------+--------------------------+
| order_id | order_name | total_money | user_id | mobile                   |
+----------+------------+-------------+---------+--------------------------+
|        1 | name1      | 100         |       1 | AWWFGyhgDB7XkzxnVWKjwA== |
|        2 | name2      | 200         |       2 | AWWFGyhgDB7XkzxnVWKjwA== |
|        3 | name3      | 300         |       3 | AWWFGyhgDB7XkzxnVWKjwA== |
|        4 | name4      | 400         |       4 | AWWFGyhgDB7XkzxnVWKjwA== |
|        5 | name5      | 500         |       5 | AWWFGyhgDB7XkzxnVWKjwA== |
|        6 | name6      | 600         |       6 | AWWFGyhgDB7XkzxnVWKjwA== |
|        7 | name7      | 700         |       7 | AWWFGyhgDB7XkzxnVWKjwA== |
|        8 | name8      | 800         |       8 | AWWFGyhgDB7XkzxnVWKjwA== |
|        9 | name9      | 900         |       9 | AWWFGyhgDB7XkzxnVWKjwA== |
|       10 | name10     | 1000        |      10 | AWWFGyhgDB7XkzxnVWKjwA== |
+----------+------------+-------------+---------+--------------------------+

posted @ 2026-10-12 18:24 slnngk 阅读(7) 评论(0) 收藏举报

刷新页面返回顶部

shardingsphere-proxy(5.4.1)部署(单分表+分表同时加密+单分表)

公告