Manual

Manual

Environment

JAVA，JDK 1.8+.

The migration scene we support:

Source Target
MySQL(5.1.15 ~ 5.7.x) MySQL
PostgreSQL(9.4 ~ ) PostgreSQL
openGauss(2.1.0) openGauss

Attention:

If the backend database is MySQL, please download mysql-connector-java-5.1.47.jar and put it into ${shardingsphere-proxy}/lib directory.

Privileges

We need to enable binlog for MySQL. Privileges of users scaling used should include Replication privileges.

+-----------------------------------------+---------------------------------------+
| Variable_name                           | Value                                 |
+-----------------------------------------+---------------------------------------+
| log_bin                                 | ON                                    |
| binlog_format                           | ROW                                   |
| binlog_row_image                        | FULL                                  |
+-----------------------------------------+---------------------------------------+

+------------------------------------------------------------------------------+
|Grants for ${username}@${host}                                                |
+------------------------------------------------------------------------------+
|GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ${username}@${host}     |
|.......                                                                       |
+------------------------------------------------------------------------------+

PostgreSQL need to support and open test_decoding feature.

DistSQL API

ShardingSphere-Scaling provides DistSQL API

Preview current sharding rule

Example:

preview select count(1) from t_order;

Response:

mysql> preview select count(1) from t_order;
+------------------+--------------------------------+
| data_source_name | sql                            |
+------------------+--------------------------------+
| ds_0             | select count(1) from t_order_0 |
| ds_0             | select count(1) from t_order_1 |
| ds_1             | select count(1) from t_order_0 |
| ds_1             | select count(1) from t_order_1 |
+------------------+--------------------------------+
4 rows in set (0.00 sec)

Start scaling job

  1. Add new data source resources

Please refer to RDL#Data Source for more details.

Example:

ADD RESOURCE ds_2 (
    URL="jdbc:mysql://127.0.0.1:3306/db2?serverTimezone=UTC&useSSL=false",
    USER=root,
    PASSWORD=root,
    PROPERTIES("maximumPoolSize"=10,"idleTimeout"="30000")
);
-- ds_3, ds_4
  1. Alter sharding table rule

Please refer to RDL#Sharding for more details.

Example:

ALTER SHARDING TABLE RULE t_order (
RESOURCES(ds_2, ds_3, ds_4),
SHARDING_COLUMN=order_id,
TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=10)),
GENERATED_KEY(COLUMN=another_id,TYPE(NAME=snowflake,PROPERTIES("worker-id"=123)))
);

If RESOURCES and sharding-count is changed, then scaling job will be emitted.

List scaling jobs

Please refer to RAL#Scaling for more details.

Example:

show scaling list;

Response:

mysql> show scaling list;
+--------------------+-----------------------+----------------------+--------+---------------------+---------------------+
| id                 | tables                | sharding_total_count | active | create_time         | stop_time           |
+--------------------+-----------------------+----------------------+--------+---------------------+---------------------+
| 659853312085983232 | t_order_item, t_order | 2                    | 0      | 2021-10-26 20:21:31 | 2021-10-26 20:24:01 |
| 660152090995195904 | t_order_item, t_order | 2                    | 0      | 2021-10-27 16:08:43 | 2021-10-27 16:11:00 |
+--------------------+-----------------------+----------------------+--------+---------------------+---------------------+
2 rows in set (0.04 sec)

Get scaling progress

Example:

show scaling status {jobId};

Response:

mysql> show scaling status 660152090995195904;
+------+-------------+----------+-------------------------------+--------------------------+
| item | data_source | status   | inventory_finished_percentage | incremental_idle_minutes |
+------+-------------+----------+-------------------------------+--------------------------+
| 0    | ds_1        | FINISHED | 100                           | 2834                     |
| 1    | ds_0        | FINISHED | 100                           | 2834                     |
+------+-------------+----------+-------------------------------+--------------------------+
2 rows in set (0.00 sec)

Current scaling job is finished, new sharding rule should take effect, and not if scaling job is failed.

status values:

Value Description
PREPARING preparing
RUNNING running
EXECUTE_INVENTORY_TASK inventory task running
EXECUTE_INCREMENTAL_TASK incremental task running
ALMOST_FINISHED almost finished
FINISHED finished
PREPARING_FAILURE preparation failed
EXECUTE_INVENTORY_TASK_FAILURE inventory task failed
EXECUTE_INCREMENTAL_TASK_FAILURE incremental task failed

Preview new sharding rule

Example:

preview select count(1) from t_order;

Response:

mysql> preview select count(1) from t_order;
+------------------+--------------------------------+
| data_source_name | sql                            |
+------------------+--------------------------------+
| ds_2             | select count(1) from t_order_0 |
| ds_2             | select count(1) from t_order_1 |
| ds_3             | select count(1) from t_order_0 |
| ds_3             | select count(1) from t_order_1 |
| ds_4             | select count(1) from t_order_0 |
| ds_4             | select count(1) from t_order_1 |
+------------------+--------------------------------+
6 rows in set (0.01 sec)

Other DistSQL

Please refer to RAL#Scaling for more details.