For systems running on a single database that urgently need to securely and simply migrate data to a horizontally sharded database.
Get ShardingSphere-Proxy. Please refer to proxy startup guide for details.
Modify the configuration file conf/server.yaml. Please refer to mode configuration for details.
Currently, mode must be Cluster, and the corresponding registry must be started in advance.
Configuration sample:
mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: governance_ds
server-lists: localhost:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
Proxy has included JDBC driver of PostgreSQL.
If the backend is connected to the following databases, download the corresponding JDBC driver jar package and put it into the ${shardingsphere-proxy}/ext-lib directory.
| Database | JDBC Driver | Reference |
|---|---|---|
| MySQL | mysql-connector-java-5.1.47.jar | Connector/J Versions |
| openGauss | opengauss-jdbc-3.0.0.jar |
If you are migrating to a heterogeneous database, then you could use more types of database. Introduce JDBC driver as above too.
sh bin/start.sh
logs/stdout.log. If you see the following statements:[INFO ] [main] o.a.s.p.frontend.ShardingSphereProxy - ShardingSphere-Proxy start success
The startup will have been successful.
6.1. Query configuration.
SHOW MIGRATION RULE;
The default configuration is as follows.
+--------------------------------------------------------------+--------------------------------------+------------------------------------------------------+
| read | write | stream_channel |
+--------------------------------------------------------------+--------------------------------------+------------------------------------------------------+
| {"workerThread":40,"batchSize":1000,"shardingSize":10000000} | {"workerThread":40,"batchSize":1000} | {"type":"MEMORY","props":{"block-queue-size":10000}} |
+--------------------------------------------------------------+--------------------------------------+------------------------------------------------------+
6.2. Alter configuration (Optional).
Since the migration rule has default values, there is no need to create it, only the ALTER statement is provided.
A completely configured DistSQL is as follows.
ALTER MIGRATION RULE (
READ(
WORKER_THREAD=40,
BATCH_SIZE=1000,
SHARDING_SIZE=10000000,
RATE_LIMITER (TYPE(NAME='QPS',PROPERTIES('qps'='500')))
),
WRITE(
WORKER_THREAD=40,
BATCH_SIZE=1000,
RATE_LIMITER (TYPE(NAME='TPS',PROPERTIES('tps'='2000')))
),
STREAM_CHANNEL (TYPE(NAME='MEMORY',PROPERTIES('block-queue-size'='10000')))
);
Configuration item description:
ALTER MIGRATION RULE (
READ( -- Data reading configuration. If it is not configured, part of the parameters will take effect by default.
WORKER_THREAD=40, -- Obtain the thread pool size of all the data from the source side. If it is not configured, the default value is used.
BATCH_SIZE=1000, -- The maximum number of records returned by a query operation. If it is not configured, the default value is used.
SHARDING_SIZE=10000000, -- Sharding size of all the data. If it is not configured, the default value is used.
RATE_LIMITER ( -- Traffic limit algorithm. If it is not configured, traffic is not limited.
TYPE( -- Algorithm type. Option: QPS
NAME='QPS',
PROPERTIES( -- Algorithm property
'qps'='500'
)))
),
WRITE( -- Data writing configuration. If it is not configured, part of the parameters will take effect by default.
WORKER_THREAD=40, -- The size of the thread pool on which data is written into the target side. If it is not configured, the default value is used.
BATCH_SIZE=1000, -- The maximum number of records for a batch write operation. If it is not configured, the default value is used.
RATE_LIMITER ( -- Traffic limit algorithm. If it is not configured, traffic is not limited.
TYPE( -- Algorithm type. Option: TPS
NAME='TPS',
PROPERTIES( -- Algorithm property.
'tps'='2000'
)))
),
STREAM_CHANNEL ( -- Data channel. It connects producers and consumers, used for reading and writing procedures. If it is not configured, the MEMORY type is used by default.
TYPE( -- Algorithm type. Option: MEMORY
NAME='MEMORY',
PROPERTIES( -- Algorithm property
'block-queue-size'='10000' -- Property: blocking queue size.
)))
);
DistSQL sample: configure READ for traffic limit.
ALTER MIGRATION RULE (
READ(
RATE_LIMITER (TYPE(NAME='QPS',PROPERTIES('qps'='500')))
)
);
Configure data reading for traffic limit. Other configurations use default values.
6.3. Restore configuration.
To restore the default configuration, also through the ALTER statement.
ALTER MIGRATION RULE (
READ(
WORKER_THREAD=40,
BATCH_SIZE=1000,
SHARDING_SIZE=10000000,
RATE_LIMITER (TYPE(NAME='QPS',PROPERTIES('qps'='500')))
),
WRITE(
WORKER_THREAD=40,
BATCH_SIZE=1000,
RATE_LIMITER (TYPE(NAME='TPS',PROPERTIES('tps'='2000')))
),
STREAM_CHANNEL (TYPE(NAME='MEMORY',PROPERTIES('block-queue-size'='10000')))
);