5.0 proxy 加密字段(加解密)无密文数据写入(已解决,使用了beta版本导致)

方便更快捷的说明问题,可以按需填写(可删除)

使用环境:

  • 版本:shardingsphere-proxy 5.0, mysql 5.7.33 ,sysbench 1.0.20

场景、问题:

  • 希望仅使用加解密功能,仅配置了server.yaml config-encrypt.yaml

在使用 sysbench 连接 proxy后,可插入明文数据,但proxy 未写入密文数据,且,无异常日志(已在logback中开启debug)。

已进行操作:

配置如下:
server.yaml:

mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: governance_ds
      server-lists: localhost:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500
  overwrite: false
#
rules:
  - !AUTHORITY
    users:
      - root@%:root
      - sharding@:sharding
    provider:
      type: SCHEMA_PRIVILEGES_PERMITTED
      props:
        user-schema-mappings: root@=test_encrypt_db
#开启了 sql-show

config-encrypt.yaml:

rules:
- !ENCRYPT
  encryptors:
    aes_encryptor:
      type: AES
      props:
        aes-key-value: 123456abc
  tables:
    sbtest1:
      columns:
        pad:
          plainColumn: pad
          cipherColumn: pad_cipher
          encryptorName: aes_encryptor
  queryWithCipherColumn: false
  • sysbench

sysbench --test=/usr/local/share/sysbench/oltp_insert.lua \
--mysql-host=127.0.0.1 \
--mysql-port=3307 \
--mysql-user=root \
--mysql-password='root' \
--mysql-db=test_encrypt_db \
--db-driver=mysql \
--tables=1 \
--table-size=5 \
--report-interval=10 \
--threads=1 \
--time=1  \
run

现状:

  • proxy 日志:
[DEBUG] 2021-12-21 17:41:57.223 [epollEventLoopGroup-3-4] o.a.s.p.f.m.c.MySQLCommandExecutorFactory - Execute packet type: COM_QUERY, value: MySQLComQueryPacket(sql=INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 3, '58167453823-83640066807-74514925129-40960490574-17975838966-24877661340-54811402689-13359132452-55911536024-83532833603', '05330259242-89286695780-24406558891-52415790982-64789714911'))
[DEBUG] 2021-12-21 17:41:57.225 [epollEventLoopGroup-3-4] o.a.s.db.protocol.codec.PacketCodec - Write to client 94a5b6d3 :
         +-------------------------------------------------+
         |  0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f |
+--------+-------------------------------------------------+----------------+
|00000000| 09 00 00 01 00 01 fc 29 01 02 00 00 00          |.......).....   |
+--------+-------------------------------------------------+----------------+
[DEBUG] 2021-12-21 17:41:57.226 [epollEventLoopGroup-3-4] o.a.s.db.protocol.codec.PacketCodec - Read from client 94a5b6d3 :
         +-------------------------------------------------+
         |  0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f |
+--------+-------------------------------------------------+----------------+
|00000000| ec 00 00 00 03 49 4e 53 45 52 54 20 49 4e 54 4f |.....INSERT INTO|
|00000010| 20 73 62 74 65 73 74 31 20 28 69 64 2c 20 6b 2c | sbtest1 (id, k,|
|00000020| 20 63 2c 20 70 61 64 29 20 56 41 4c 55 45 53 20 | c, pad) VALUES |
|00000030| 28 30 2c 20 33 2c 20 27 30 38 34 31 36 30 32 35 |(0, 3, '08416025|
|00000040| 38 38 38 2d 33 30 39 30 35 37 36 37 38 32 36 2d |888-30905767826-|
|00000050| 31 31 31 30 36 34 30 31 37 36 37 2d 35 38 38 39 |11106401767-5889|
|00000060| 33 39 34 33 30 32 31 2d 31 34 34 36 39 32 39 36 |3943021-14469296|
|00000070| 30 30 37 2d 31 33 33 32 37 35 30 35 38 30 39 2d |007-13327505809-|
|00000080| 32 33 34 35 36 38 30 31 36 33 35 2d 37 32 33 35 |23456801635-7235|
|00000090| 35 38 36 31 37 36 30 2d 34 30 37 33 37 38 35 35 |5861760-40737855|
|000000a0| 32 33 38 2d 38 35 31 30 35 37 36 35 34 34 39 27 |238-85105765449'|
|000000b0| 2c 20 27 35 39 36 32 36 33 30 32 31 39 31 2d 33 |, '59626302191-3|
|000000c0| 38 32 31 33 35 32 32 38 35 35 2d 33 30 35 38 39 |8213522855-30589|
|000000d0| 34 39 36 35 32 32 2d 39 34 39 31 31 32 39 36 32 |496522-949112962|
|000000e0| 30 35 2d 39 36 31 35 39 38 36 31 39 38 31 27 29 |05-96159861981')|
+--------+-------------------------------------------------+----------------+

mysql binlog:

BEGIN
/*!*/;
# at 238579
#211221 17:41:57 server id 1  end_log_pos 238639 CRC32 0x3f9e9b16 	Table_map: `test`.`sbtest1` mapped to number 110
# at 238639
#211221 17:41:57 server id 1  end_log_pos 238864 CRC32 0xd08a251d 	Write_rows: table id 110 flags: STMT_END_F
### INSERT INTO `test`.`sbtest1`
### SET
###   @1=299
###   @2=3
###   @3='65646576557-33952162748-54174853993-93624712464-52992736987-24436095506-84529487368-95307018245-48084391576-29687510347'
###   @4='36729657164-91671244932-15838263943-12927474525-73645575542'
###   @5=''
# at 238864
#211221 17:41:57 server id 1  end_log_pos 238895 CRC32 0x3e7b3527 	Xid = 611
COMMIT/*!*/;

开启 sql-show 看看真实 sql 呢

开启了,真实的sql见proyx日志

我用你的配置 5.0 是可以的。

[INFO ] 2021-12-22 09:25:30.051 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Logic SQL: INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 3, '58167453823-83640066807-74514925129-40960490574-17975838966-24877661340-54811402689-13359132452-55911536024-83532833603', '05330259242-89286695780-24406558891-52415790982-64789714911')
[INFO ] 2021-12-22 09:25:30.051 [Connection-1-ThreadExecutor] ShardingSphere-SQL - SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
[INFO ] 2021-12-22 09:25:30.051 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: INSERT INTO sbtest1 (id, k, c, pad_cipher, pad) VALUES (0, 3, '58167453823-83640066807-74514925129-40960490574-17975838966-24877661340-54811402689-13359132452-55911536024-83532833603', 'Q5ypG4xI3wr9dodW4mSVE1SnMWFNN6Pw1sVqCKCWiIE/vQXAOhTHpYB9INb2N72tq/mjB8v/Yprgd06SYZkqyA==', '05330259242-89286695780-24406558891-52415790982-64789714911')

我重新下载了5.0 的版本后问题解决了,感谢~

原来的5.0 版本是在12月2日下载的,可能是beta版

京ICP备2021015875号