Manual

Manual

Environment

JAVA,JDK 1.8+.

The migration scene we support:

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

Attention:

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

RDBMS JDBC driver Reference
MySQL mysql-connector-java-5.1.47.jar Connector/J Versions
openGauss opengauss-jdbc-2.0.1-compatibility.jar

Supported features:

Feature MySQL PostgreSQL openGauss
Inventory migration Supported Supported Supported
Incremental migration Supported Supported Supported
Create table automatically Supported Unsupported Supported
DATA_MATCH data consistency check Supported Supported Supported
CRC32_MATCH data consistency check Supported Unsupported Unsupported

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:

Type AutoTableRule TableRule
Definition Auto Sharding Algorithm User-Defined Sharding Algorithm

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:

Value Description
PREPARING preparing
RUNNING running
EXECUTE_INVENTORY_TASK inventory task running
EXECUTE_INCREMENTAL_TASK incremental task running
ALMOST_FINISHED almost finished
FINISHED finished (The whole process is completed, and the new rules have been taken effect)
PREPARING_FAILURE preparation failed
EXECUTE_INVENTORY_TASK_FAILURE inventory task failed
EXECUTE_INCREMENTAL_TASK_FAILURE incremental task failed

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.