10-27 06:49 阅读 262

关注

Apache ShardingSphere 研读（3）：Shardingsphere-Proxy 演示

内容

本文重点关注 ShardingSphere-Proxy 的分库分表、读写分离和数据加密的演示

启动

1. 下载 apache-shardingsphere-5.0.0-beta-shardingsphere-proxy-bin.tar.gz

2. 修改配置，将examples\shardingsphere-proxy-example\shardingsphere-proxy-boot-mybatis-example\src\main\resources\conf 下面的三个配置文件：config-readwrite-splitting.yaml，config-sharding.yaml，server.yaml 复制到解压后的文件夹下的conf里面，修改数据库的配置。另外修改proxy端conf里面的config-encrypt.yaml 文件的数据库配置用于数据加解密测试

3. 将 mysql-connector-java-8.0.26.jar 复制到lib目录下

4. 启动 start.bat, 默认3307 端口（在start.bat后面加端口可启动指定端口）

   Starting the ShardingSphere-Proxy ... Thanks for using Atomikos! Evaluate http://www.atomikos.com/Main/ExtremeTransactions for advanced features and professional support or register at http://www.atomikos.com/Main/RegisterYourDownload to disable this message and receive FREE tips & advice [INFO ] 2021-08-25 09:15:47.928 [main] o.a.s.p.i.i.AbstractBootstrapInitializer - Database name is `MySQL`, version is `5.7.28-log` [INFO ] 2021-08-25 09:15:49.210 [main] o.a.s.p.frontend.ShardingSphereProxy - ShardingSphere-Proxy start success. 复制代码

功能演示

本文的 ShardingSphere-Proxy 为源码启动，启动入口为 shardingsphere-proxy 下的子模块 shardingsphere-proxy-bootstrap 的 Bootstrap

读写分离

入口

shardingsphere-proxy-boot-mybatis-example 项目的 ProxySpringBootStarterExample

配置

1. ShardingSphere-Proxy 配置

   schemaName: readwrite_splitting_db dataSources:   write_ds:     url: jdbc:mysql://127.0.0.1:3306/demo_write_ds?serverTimezone=UTC&useSSL=false     username: root     password: 123456     connectionTimeoutMilliseconds: 30000     idleTimeoutMilliseconds: 60000     maxLifetimeMilliseconds: 1800000     maxPoolSize: 50     minPoolSize: 1   read_ds_0:     url: jdbc:mysql://127.0.0.1:3306/demo_read_ds_0?serverTimezone=UTC&useSSL=false     username: root     password: 123456     connectionTimeoutMilliseconds: 30000     idleTimeoutMilliseconds: 60000     maxLifetimeMilliseconds: 1800000     maxPoolSize: 50     minPoolSize: 1   read_ds_1:     url: jdbc:mysql://127.0.0.1:3306/demo_read_ds_1?serverTimezone=UTC&useSSL=false     username: root     password: 123456     connectionTimeoutMilliseconds: 30000     idleTimeoutMilliseconds: 60000     maxLifetimeMilliseconds: 1800000     maxPoolSize: 50     minPoolSize: 1 rules: - !READWRITE_SPLITTING   dataSources:     pr_ds:       writeDataSourceName: write_ds       readDataSourceNames:         - read_ds_0         - read_ds_1 复制代码

2. shardingsphere-proxy-boot-mybatis-example 配置

   mybatis.config-location=classpath:META-INF/mybatis-config.xml spring.datasource.type=com.zaxxer.hikari.HikariDataSource spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.url=jdbc:mysql://localhost:3307/readwrite_splitting_db?useServerPrepStmts=true&cachePrepStmts=true&useLocalSessionState=true spring.datasource.username=root spring.datasource.password=root 复制代码

演示结果

1. 写 SQL 的 ShardingSphere-Proxy 日志

   [INFO ] 2021-09-07 15:50:38.674 [Connection-23-ThreadExecutor] ShardingSphere-SQL - Logic SQL: INSERT INTO t_order_item (order_id, user_id, status) VALUES (?, ?, ?); [INFO ] 2021-09-07 15:50:38.674 [Connection-23-ThreadExecutor] ShardingSphere-SQL - SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty) [INFO ] 2021-09-07 15:50:38.674 [Connection-23-ThreadExecutor] ShardingSphere-SQL - Actual SQL: write_ds ::: INSERT INTO t_order_item (order_id, user_id, status) VALUES (?, ?, ?); ::: [9, 9, INSERT_TEST] 复制代码

2. 读 SQL 的 ShardingSphere-Proxy 日志

   [INFO ] 2021-09-07 15:50:55.493 [Connection-23-ThreadExecutor] ShardingSphere-SQL - Logic SQL: SELECT * from t_order_item [INFO ] 2021-09-07 15:50:55.493 [Connection-23-ThreadExecutor] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty) [INFO ] 2021-09-07 15:50:55.493 [Connection-23-ThreadExecutor] ShardingSphere-SQL - Actual SQL: read_ds_0 ::: SELECT * from t_order_item [INFO ] 2021-09-07 15:50:55.452 [Connection-23-ThreadExecutor] ShardingSphere-SQL - Logic SQL: SELECT * FROM t_order; [INFO ] 2021-09-07 15:50:55.452 [Connection-23-ThreadExecutor] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty) [INFO ] 2021-09-07 15:50:55.452 [Connection-23-ThreadExecutor] ShardingSphere-SQL - Actual SQL: read_ds_1 ::: SELECT * FROM t_order; 复制代码

分库分表

入口

shardingsphere-proxy-boot-mybatis-example 项目的 ProxySpringBootStarterExample

配置

1. ShardingSphere-Proxy 配置

   schemaName: sharding_db dataSources:   ds_0:     url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false     username: root     password: 123456     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: 123456     connectionTimeoutMilliseconds: 30000     idleTimeoutMilliseconds: 60000     maxLifetimeMilliseconds: 1800000     maxPoolSize: 50     minPoolSize: 1 rules: - !SHARDING   tables:     t_order:       actualDataNodes: ds_${0..1}.t_order_${0..1}       tableStrategy:         standard:           shardingColumn: order_id           shardingAlgorithmName: t_order_inline       keyGenerateStrategy:         column: order_id         keyGeneratorName: snowflake     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:   shardingAlgorithms:     database_inline:       type: INLINE       props:         algorithm-expression: ds_${user_id % 2}     t_order_inline:       type: INLINE       props:         algorithm-expression: t_order_${order_id % 2}     t_order_item_inline:       type: INLINE       props:         algorithm-expression: t_order_item_${order_id % 2}   keyGenerators:     snowflake:       type: SNOWFLAKE       props:         worker-id: 123 复制代码

2. shardingsphere-proxy-boot-mybatis-example 配置

   mybatis.config-location=classpath:META-INF/mybatis-config.xml spring.datasource.type=com.zaxxer.hikari.HikariDataSource spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.url=jdbc:mysql://localhost:3307/sharding_db?useServerPrepStmts=true&cachePrepStmts=true&useLocalSessionState=true spring.datasource.username=root spring.datasource.password=root 复制代码

增加 &useLocalSessionState=true 解决启动后大量出现 select @@session.transaction_read_only

演示结果

1. 偶数的 user_id 落在 ds_0 库，奇数的 user_id 落在 ds_1 库

2. 偶数的 order_id 落在 t_order_0 库，奇数的 order_id 落在 t_order_1 库

加密

入口

shardingsphere-proxy-boot-mybatis-example 项目的 ProxySpringBootStarterExample

配置

1. ShardingSphere-Proxy 配置

   schemaName: encrypt_db dataSources:   ds_0:     url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false     username: root     password: 123456     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: 123456     connectionTimeoutMilliseconds: 30000     idleTimeoutMilliseconds: 60000     maxLifetimeMilliseconds: 1800000     maxPoolSize: 50     minPoolSize: 1 rules:   - !ENCRYPT     encryptors:       aes_encryptor:         type: AES         props:           aes-key-value: 123456abc       md5_encryptor:         type: MD5     tables:       t_user:         columns:           user_name:             cipherColumn: user_name             encryptorName: aes_encryptor           pwd:             cipherColumn: pwd             encryptorName: md5_encryptor 复制代码

2. shardingsphere-proxy-boot-mybatis-example 配置

   mybatis.config-location=classpath:META-INF/mybatis-config.xml spring.datasource.type=com.zaxxer.hikari.HikariDataSource spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.url=jdbc:mysql://localhost:3307/encrypt_db?useServerPrepStmts=true&cachePrepStmts=true&useLocalSessionState=true spring.datasource.username=root spring.datasource.password=root 复制代码

演示结果

---------------------------- Print User Data ----------------------- user_id: 1, user_name: test_mybatis_1, pwd: d8b04170dddc3fe760c403a2deb0414e ...... 复制代码

对比数据库的结果：

作者：如果可以改变
链接：https://juejin.cn/post/7023551487116574733