Manual

Manual

Environment

JAVA，JDK 1.8+.

The migration scene we support:

Attention:

If the backend database is in following table, please download JDBC driver jar and put it into ${shardingsphere-proxy}/lib directory.

Supported features:

Attention:

For RDBMS which Create table automatically feature is not supported, we need to create sharding tables manually.

Privileges

MySQL

1. Enable binlog

Configuration Example of MySQL 5.7 my.cnf:

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=row
binlog-row-image=full
max_connections=600

Execute the following SQL to confirm whether binlog is turned on or not:

show variables like '%log_bin%';
show variables like '%binlog%';

As shown below, it means binlog has been turned on:

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

1. Privileges of account that scaling use should include Replication privileges.

Execute the following SQL to confirm whether the user has migration permission or not:

SHOW GRANTS 'user';

Result Example:

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

PostgreSQL

1. Enable test_decoding feature.

2. Adjust WAL configuration

Configuration Example of postgresql.conf:

wal_level = logical
max_replication_slots = 10

Please refer to Write Ahead Log and Replication for more details.

DistSQL API for auto mode

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.

Create database on underlying RDBMS first, it will be used in following DistSQL.

Example:

ADD RESOURCE ds_2 (
    URL="jdbc:mysql://127.0.0.1:3306/scaling_ds_2?serverTimezone=UTC&useSSL=false",
    USER=root,
    PASSWORD=root,
    PROPERTIES("maximumPoolSize"=10,"idleTimeout"="30000")
), ds_3 (
    URL="jdbc:mysql://127.0.0.1:3306/scaling_ds_3?serverTimezone=UTC&useSSL=false",
    USER=root,
    PASSWORD=root,
    PROPERTIES("maximumPoolSize"=10,"idleTimeout"="30000")
), ds_4 (
    URL="jdbc:mysql://127.0.0.1:3306/scaling_ds_4?serverTimezone=UTC&useSSL=false",
    USER=root,
    PASSWORD=root,
    PROPERTIES("maximumPoolSize"=10,"idleTimeout"="30000")
);

1. Alter all sharding table rule

Currently, scaling job could only be emitted by executing ALTER SHARDING TABLE RULE DistSQL.

Please refer to RDL#Sharding for more details.

SHARDING TABLE RULE support two types: TableRule and AutoTableRule. Following is a comparison of the two sharding rule types:

Meaning of fields in DistSQL is the same as YAML configuration, please refer to YAML Configuration#Sharding for more details.

Example of alter AutoTableRule:

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

RESOURCES is altered from (ds_0, ds_1) to (ds_2, ds_3, ds_4), and sharding-count is altered from 4 to 6, it will emit scaling job.

Uncompleted example of alter TableRule:

ALTER SHARDING ALGORITHM database_inline (
TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="ds_${user_id % 3 + 2}"))
);
ALTER SHARDING TABLE RULE t_order (
DATANODES("ds_${2..4}.t_order_${0..1}"),
DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=user_id,SHARDING_ALGORITHM=database_inline),
TABLE_STRATEGY(TYPE=standard,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=t_order_inline),
GENERATED_KEY(COLUMN=order_id,TYPE(NAME=snowflake,PROPERTIES("worker-id"=123)))
), t_order_item (
DATANODES("ds_${2..4}.t_order_item_${0..1}"),
DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=user_id,SHARDING_ALGORITHM=database_inline),
TABLE_STRATEGY(TYPE=standard,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=t_order_item_inline),
GENERATED_KEY(COLUMN=order_item_id,TYPE(NAME=snowflake,PROPERTIES("worker-id"=123)))
);

algorithm-expression of database_inline is alerted from ds_${user_id % 2} to ds_${user_id % 3 + 2}, and DATANODES of t_order is alerted from ds_${0..1}.t_order_${0..1} to ds_${2..4}.t_order_${0..1}, it will emit scaling job.

Currently, ALTER SHARDING ALGORITHM will take effect immediately, but table rule will not, it might cause inserting data into source side failure, so alter sharding table rule to AutoTableRule is recommended for now.

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                    | false  | 2021-10-26 20:21:31 | 2021-10-26 20:24:01 |
| 660152090995195904 | t_order_item, t_order | 2                    | false  | 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:

If status fails, you can check the log of proxy to view the error stack and analyze the problem.

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.

DistSQL API for manual mode

Data consistency check and switch configuration could be emitted manually. Please refer to RAL#Scaling for more details.

Attention: It’s still under development.