Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of "Sharding-Proxy sub-database sub-table and data encryption", the editor shows you the operation process through the actual case, the operation method is simple and fast, practical, hope that this "Sharding-Proxy sub-library sub-table and data encryption how to achieve" article can help you solve the problem.

Sharding-Proxy sub-library, sub-table and data encryption

Mainly summarize and share the experience of using shardingshpere-proxy in the actual project.

Working with scen

The company plans to develop two SaaS platforms for new government media and digital villages as new profit growth points. Considering the number of users and data in the future, it is decided to divide the database according to the tenant (contracted customer). For some sensitive data, such as ID number, mobile phone number, etc., use database-level encryption and decryption, do not store plaintext data.

After reviewing some existing database middleware and sub-database sub-table solutions on the Internet, the company decided to use Sharding-Proxy as the database middleware for sub-database sub-table and data encryption. The main reason is that it is less intrusive to the code, developers do not need to pay attention to it, reduces the cost of learning, and is DBA-friendly. On the other hand, ShardingSphere has entered the Apache incubator, which is completely open source and free, the community is active, and the version iteration is fast.

The ShardingSphere-5.0 used this time

Configuration file explains server.yaml

Resources folder, under the conf folder

There are registry configuration, login connection configuration and basic configuration.

Mode: type: Cluster # Cluster repository: type: ZooKeeper # use zookeeper props: namespace: governance_ds server-lists: 192.168.1.100 repository 2181 retryIntervalMilliseconds: 50000 timeToLiveSeconds: 60 maxRetries: 3 operationTimeoutMilliseconds: 50000 overwrite: true

Mode.type: Cluster uses cluster configuration, and a single deployment can also be set to Cluster, which has no effect.

Mode.repository configuration storage mode, you can choose to use ZooKeeper

Mode.overwrite, the configuration loading mode, and whether the local configuration overrides the configuration center configuration. True is override, whichever is local. Synchronizing local configuration to zookeeper;false is subject to zookeeper.

There are also some basic configurations, such as whether to print SQL, etc., which are all defaulted for the time being.

Config-sharding.yaml

Resources folder, under the conf folder

SchemaName database connection, database name

DataSources data source

Rules rule

! SHARDING subdatabase and table rules

Tables table

ActualDataNodes is actually for library tables.

DatabaseStrategy sub-database policy none regardless of database sub-table

DefaultDatabaseStrategy default sub-library policy

DefaultTableStrategy default sub-table policy

DefaultKeyGenerateStrategy default primary key policy

ShardingAlgorithms custom slicing algorithm

KeyGenerators primary key generation strategy

Config-encrypt.yaml

Resources folder, under the conf folder

SchemaName database connection, database name

DataSources data source

Rules rule

! ENCRYPT data encryption

Encryptors encryption policy. You can choose AES or MD5. You can choose the encryption policy in the specific fields below.

Aes_encryptor,aes can be configured to add salt

Tables table

Columns field s

Id_number logical field

PlainColumn original field

CipherColumn encryption field

EncryptorName encryption policy

Whether to use encrypted fields when querying in queryWithCipherColumn

Other

You can download the source code or download the program to see, there are configuration file cases, are commented out, a PostgreSQL, a MySQL

At present, we only use these two functions, and the other functions have not been studied for the time being, so we won't say much about them.

In actual use, sub-library, sub-table and data encryption are used together, so only one configuration file is used, all of which are placed under rules

I will post my configuration file later.

Use situation

For the time being, the new government media SaaS platform only uses sub-libraries and sub-tables.

Digital village SaaS platform, using sub-database and table and data encryption

Sub-database sub-table, mainly based on tenant sub-database, part of the table, and according to some business fields to do sub-table.

Sub-table policy, which is written in 8 tables by default: algorithm-expression: monitor_record_$ {media_id% 8}

We divide the database based on the tenant's hash, but for some tenants, we want to specify the database, so we need to customize the database and table policy.

The sub-library is required to support hashing and specification. A custom sub-library policy class is written, and there is a static map. When parsing executes SQL, you first get it from map. If you cannot get it, then get it according to the hash.

Data encryption, mainly adding encrypted fields and processing historical data

You can write a static method to process existing data

Summary

Sharding-proxy fully supports sub-database and data encryption by tenant, which is enough for us to use

Easy to use, just download the latest stable version and install it.

If there is no custom database and table policy requirement, and only the existing policy is used, you only need to modify the configuration file deployment.

If you need to customize the policy of sub-database and sub-table, and it is not complicated, write the class and package it, and put it under ext-lib.

Example of a profile section

SchemaName: digital_villagedataSources: ds: url: jdbc:postgresql://192.168.1.xxx:5432/digital_village?currentSchema=public&serverTimezone=UTC&useSSL=false username: postgres password: xxxxxx connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 120 minPoolSize: 1 ds_0: url: jdbc:postgresql://192.168.1.xxx:5432/digital_village_0?currentSchema=public&serverTimezone=UTC&useSSL=false ds_1: url: jdbc:postgresql://192.168.1 .xxx: 5432/digital_village_1?currentSchema=public&serverTimezone=UTC&useSSL=false password: xxxxxrules: -! SHARDING tables: # tables that require sub-libraries According to the tenant id sub-library cms_basic_info: actualDataNodes: ds_$ {0.3} .cms _ basic_info cms_column: actualDataNodes: ds_$ {0.3} .cms _ column cms_content: actualDataNodes: ds_$ {0.3} .cms _ content cms_content_text: actualDataNodes: ds_$ {0.3} .cms _ content_text cms_ Menu_column_bind: actualDataNodes: ds_$ {0.3} .cms _ menu_column_bind cms_message_board: actualDataNodes: ds_$ {0.3} .cms _ message_board # tables that do not require sublibraries and tables All are stored in ds data source auth_cfg_catalog_data_permission: actualDataNodes: ds.auth_cfg_catalog_data_permission databaseStrategy: none: auth_cfg_column_data_permission: actualDataNodes: ds.auth_cfg_column_data_permission databaseStrategy: # default sub-library policy defaultDatabaseStrategy: standard: shardingColumn: customer_id # points Library field shardingAlgorithmName: customer_id_inline # Sub-library rules: defaultTableStrategy: none: # default primary key policy defaultKeyGenerateStrategy: column: id keyGeneratorName: snowflake # Custom sharding algorithm shardingAlgorithms: customer_id_inline: type: CLASS_BASED props: strategy: standard algorithmClassName: cn.lonsun.dv.DigitalVillageShardingAlgorithm # Primary key Generation policy keyGenerators: snowflake: type: SNOWFLAKE worker-id: 123 -! ENCRYPT encryptors: aes_encryptor: type: AES aes-key-value: xxxwwaS213123SAD md5_encryptor: type: MD5 party_position: columns: mobile: plainColumn: mobile cipherColumn: mobile_cipher encryptorName: Aes_encryptor village_population: id_number: plainColumn: id_number cipherColumn: id_number_cipher queryWithCipherColumn: true on "Sharding-Proxy sub-library sub-table and how to achieve data encryption" is here. Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.