Logo
Manual

Manual #

MySQL User Manual #

Environment #

MySQL 5.1.15 ~ 8.0.x.

Privileges #

  1. Start binlog.

MySQL 5.7 my.cnf configuration example:

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

Run the following command to confirm that binlog is enabled:

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

If you receive the following result, it means that the binlog is enabled:

+-----------------------------------------+---------------------------------------+
| Variable_name                           | Value                                 |
+-----------------------------------------+---------------------------------------+
| log_bin                                 | ON                                    |
| binlog_format                           | ROW                                   |
| binlog_row_image                        | FULL                                  |
+-----------------------------------------+---------------------------------------+
  1. Grant the MySQL account Replication permission.

Execute the following command to see if the user has migration permissions:

SHOW GRANTS FOR 'user';

Example results:

+------------------------------------------------------------------------------+
|Grants for ${username}@${host}                                                |
+------------------------------------------------------------------------------+
|GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ${username}@${host}     |
|.......                                                                       |
+------------------------------------------------------------------------------+
  1. Grant DDL DML permissions for MySQL account

MySQL account needs part of DDL and all DML permissions. Example:

GRANT CREATE, DROP, INDEX, SELECT, INSERT, UPDATE, DELETE ON *.* TO `user`@`%`;

For details please see MySQL GRANT.

Complete Procedure Example #

Prerequisite #

  1. Create a database in MySQL.

Example:

DROP DATABASE IF EXISTS resharding_ds_0;
CREATE DATABASE resharding_ds_0 DEFAULT CHARSET utf8;
    
DROP DATABASE IF EXISTS resharding_ds_1;
CREATE DATABASE resharding_ds_1 DEFAULT CHARSET utf8;
    
DROP DATABASE IF EXISTS resharding_ds_2;
CREATE DATABASE resharding_ds_2 DEFAULT CHARSET utf8;

Procedure #

  1. Create a new logical database in proxy, configure resources and rules, create tables and initialize some data.
CREATE DATABASE sharding_db;

USE sharding_db;

REGISTER STORAGE UNIT ds_0 (
    URL="jdbc:mysql://127.0.0.1:3306/resharding_ds_0?useServerPrepStmts=true&serverTimezone=UTC&useSSL=false&characterEncoding=utf-8",
    USER="root",
    PASSWORD="root"
), ds_1 (
    URL="jdbc:mysql://127.0.0.1:3306/resharding_ds_1?useServerPrepStmts=true&serverTimezone=UTC&useSSL=false&characterEncoding=utf-8",
    USER="root",
    PASSWORD="root"
);
    
CREATE SHARDING TABLE RULE t_order(
STORAGE_UNITS(ds_0,ds_1),
SHARDING_COLUMN=order_id,
TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="4")),
KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
);

CREATE TABLE t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));

INSERT INTO t_order (order_id, user_id, status) VALUES (1,2,'ok'),(2,4,'ok'),(3,6,'ok'),(4,1,'ok'),(5,3,'ok'),(6,5,'ok');
  1. Add a new storage units in the proxy.

Example:

REGISTER STORAGE UNIT ds_2 (
    URL="jdbc:mysql://127.0.0.1:3306/resharding_ds_2?useServerPrepStmts=true&serverTimezone=UTC&useSSL=false&characterEncoding=utf-8",
    USER="root",
    PASSWORD="root"
);
  1. Start scaling-in and scaling-out.

Added ds_2 data source, example:

RESHARD TABLE t_order BY(
    STORAGE_UNITS(ds_0, ds_1, ds_2),
    SHARDING_COLUMN=order_id,
    TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="6")),
    KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
);
  1. View the list of scaling-in and scaling-out operations.
SHOW RESHARDING LIST;

Example results:

+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
| id                                    | tables  | job_item_count       | active | create_time         | stop_time |
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
| j51017f973ac82cb1edea4f5238a258c25e89 | t_order | 2                    | true   | 2022-10-25 10:10:58 | NULL      |
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
  1. View the details of scaling-in and scaling-out.
SHOW RESHARDING STATUS 'j51017f973ac82cb1edea4f5238a258c25e89';

Example results:

+------+-------------+--------------------------+--------+-------------------------+-------------------------------+-------------------+--------------------------+---------------+
| item | data_source | status                   | active | processed_records_count | inventory_finished_percentage | remaining_seconds | incremental_idle_seconds | error_message |
+------+-------------+--------------------------+--------+-------------------------+-------------------------------+-------------------+--------------------------+---------------+
| 0    | ds_0        | EXECUTE_INCREMENTAL_TASK | true   | 3                       | 100                           | 0                 | 92                       |               |
| 1    | ds_1        | EXECUTE_INCREMENTAL_TASK | true   | 3                       | 100                           | 0                 | 92                       |               |
+------+-------------+--------------------------+--------+-------------------------+-------------------------------+-------------------+--------------------------+---------------+
  1. Execute write stop (Optional).

This statement will intercept the addition, deletion, modification and partial DistSQL, which is optional.

STOP RESHARDING SOURCE WRITING 'j51017f973ac82cb1edea4f5238a258c25e89';
  1. Perform data consistency verification.
CHECK RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89' BY TYPE (NAME='CRC32_MATCH');

Data consistency verification algorithm type comes from:

SHOW RESHARDING CHECK ALGORITHMS;
+-------------+--------------------------------------------------------------+----------------------------+
| type        | supported_database_types                                     | description                |
+-------------+--------------------------------------------------------------+----------------------------+
| CRC32_MATCH | MySQL                                                        | Match CRC32 of records.    |
| DATA_MATCH  | SQL92,MySQL,MariaDB,PostgreSQL,openGauss,Oracle,SQLServer,H2 | Match raw data of records. |
+-------------+--------------------------------------------------------------+----------------------------+

When data encryption is enabled, you need to use DATA_MATCH.

Query data consistency verification progress:

SHOW RESHARDING CHECK STATUS 'j51017f973ac82cb1edea4f5238a258c25e89';

Example results:

+---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
| tables  | result | finished_percentage | remaining_seconds | check_begin_time        | check_end_time          | duration_seconds | error_message |
+---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
| t_order | true   | 100                 | 0                 | 2022-10-25 10:13:33.220 | 2022-10-25 10:13:35.338 | 2                |               |
+---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
  1. Switch metadata.
APPLY RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

Preview the rule after it takes effect.

PREVIEW SELECT * FROM t_order;

Example results:

+------------------+-----------------------------------------------------------------+
| data_source_name | actual_sql                                                      |
+------------------+-----------------------------------------------------------------+
| ds_0             | select * from v1_t_order_0 UNION ALL select * from v1_t_order_3 |
| ds_1             | select * from v1_t_order_1 UNION ALL select * from v1_t_order_4 |
| ds_2             | select * from v1_t_order_2 UNION ALL select * from v1_t_order_5 |
+------------------+-----------------------------------------------------------------+
  1. Recover write stop (optional).

If the write was stopped before, recovery is required.

RESTORE RESHARDING SOURCE WRITING 'j51017f973ac82cb1edea4f5238a258c25e89';
  1. Complete jobs of scaling-in and scaling-out.

Example:

COMMIT RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

PostgreSQL User Manual #

Environment #

PostgreSQL 9.4+

Privileges #

  1. Start test_decoding.

  2. Adjust the WAL configuration.

postgresql.conf configuration example:

wal_level = logical
max_wal_senders = 10
max_replication_slots = 10
wal_sender_timeout = 0
max_connections = 600

For details please see Write Ahead Log and Replication.

  1. Configuring PostgreSQL allows the proxy to have replication permissions.

pg_hba.conf configuration example:

host replication repl_acct 0.0.0.0/0 md5

For details please see The pg_hba.conf File.

  1. Grant DDL DML permissions for PostgreSQL account.

If you are using a non-super admin account for reshard, you need to GRANT CREATE and CONNECT privileges on the database used for reshard.

GRANT CREATE, CONNECT ON DATABASE resharding_ds_0 TO user;

The account also needs to have access to the migrated tables and schema. Take the t_order table under test schema as an example.

\c resharding_ds_0

GRANT USAGE ON SCHEMA test TO GROUP user;
GRANT SELECT ON TABLE test.t_order TO user;

PostgreSQL has the concept of OWNER, and if the account is the OWNER of a database, SCHEMA, or table, the relevant steps can be omitted.

Please refer to PostgreSQL GRANT.

Complete Procedure Example #

Prerequisite #

  1. Create a database in PostgreSQL.

Example:

DROP DATABASE IF EXISTS resharding_ds_0;
CREATE DATABASE resharding_ds_0;

DROP DATABASE IF EXISTS resharding_ds_1;
CREATE DATABASE resharding_ds_1;
    
DROP DATABASE IF EXISTS resharding_ds_2;
CREATE DATABASE resharding_ds_2;

Procedure #

  1. Create a new logical database in proxy, configure resources and rules, create tables and initialize some data.
CREATE DATABASE sharding_db;

\c sharding_db;

REGISTER STORAGE UNIT ds_0 (
    URL="jdbc:postgresql://127.0.0.1:5432/resharding_ds_0",
    USER="root",
    PASSWORD="root"
), ds_1 (
    URL="jdbc:postgresql://127.0.0.1:5432/resharding_ds_1",
    USER="root",
    PASSWORD="root"
);
    
CREATE SHARDING TABLE RULE t_order(
STORAGE_UNITS(ds_0,ds_1),
SHARDING_COLUMN=order_id,
TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="4")),
KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
);

CREATE TABLE t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));

INSERT INTO t_order (order_id, user_id, status) VALUES (1,2,'ok'),(2,4,'ok'),(3,6,'ok'),(4,1,'ok'),(5,3,'ok'),(6,5,'ok');
  1. Add a new storage unit in the proxy.

Example:

REGISTER STORAGE UNIT ds_2 (
    URL="jdbc:postgresql://127.0.0.1:5432/resharding_ds_2",
    USER="root",
    PASSWORD="root"
);
  1. Start scaling-in and scaling-out.

Added data source ds_2, example:

RESHARD TABLE t_order BY(
    STORAGE_UNITS(ds_0, ds_1, ds_2),
    SHARDING_COLUMN=order_id,
    TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="6")),
    KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
);
  1. View the list of scaling-in and scaling-out operations.
SHOW RESHARDING LIST;

Example results:

+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
| id                                    | tables  | job_item_count       | active | create_time         | stop_time |
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
| j51017f973ac82cb1edea4f5238a258c25e89 | t_order | 2                    | true   | 2022-10-25 10:10:58 | NULL      |
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
  1. View the details of scaling-in and scaling-out.
SHOW RESHARDING STATUS 'j51017f973ac82cb1edea4f5238a258c25e89';

Example results:

+------+-------------+--------------------------+--------+-------------------------+-------------------------------+-------------------+--------------------------+---------------+
| item | data_source | status                   | active | processed_records_count | inventory_finished_percentage | remaining_seconds | incremental_idle_seconds | error_message |
+------+-------------+--------------------------+--------+-------------------------+-------------------------------+-------------------+--------------------------+---------------+
| 0    | ds_0        | EXECUTE_INCREMENTAL_TASK | true   | 3                       | 100                           | 0                 | 92                       |               |
| 1    | ds_1        | EXECUTE_INCREMENTAL_TASK | true   | 3                       | 100                           | 0                 | 92                       |               |
+------+-------------+--------------------------+--------+-------------------------+-------------------------------+-------------------+--------------------------+---------------+
  1. Execute write stop (optional).

This statement will intercept the addition, deletion, modification and partial DistSQL, which is optional.

STOP RESHARDING SOURCE WRITING 'j51017f973ac82cb1edea4f5238a258c25e89';
  1. Perform data consistency verification.
CHECK RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

Query data consistency verification progress:

SHOW RESHARDING CHECK STATUS 'j51017f973ac82cb1edea4f5238a258c25e89';

Example results:

+---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
| tables  | result | finished_percentage | remaining_seconds | check_begin_time        | check_end_time          | duration_seconds | error_message |
+---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
| t_order | true   | 100                 | 0                 | 2022-10-25 10:13:33.220 | 2022-10-25 10:13:35.338 | 2                |               |
+---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
  1. Switch metadata.
APPLY RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

Preview the rule after it takes effect.

PREVIEW SELECT * FROM t_order;

Example results:

+------------------+-----------------------------------------------------------------+
| data_source_name | actual_sql                                                      |
+------------------+-----------------------------------------------------------------+
| ds_0             | select * from v1_t_order_0 UNION ALL select * from v1_t_order_3 |
| ds_1             | select * from v1_t_order_1 UNION ALL select * from v1_t_order_4 |
| ds_2             | select * from v1_t_order_2 UNION ALL select * from v1_t_order_5 |
+------------------+-----------------------------------------------------------------+
  1. Recover write stop (optional).

If the write was stopped before, recovery is required.

RESTORE RESHARDING SOURCE WRITING 'j51017f973ac82cb1edea4f5238a258c25e89';
  1. Complete jobs of scaling-in and scaling-out.

Example:

COMMIT RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

openGauss User Manual #

Environment #

openGauss 2.0.1 ~ 3.0.0

Privileges #

  1. Adjust the WAL configuration.

postgresql.conf configuration example:

wal_level = logical
max_wal_senders = 10
max_replication_slots = 10
wal_sender_timeout = 0
max_connections = 600

For details please see Write Ahead Log and Replication.

  1. Configuring PostgreSQL allows the proxy to have replication permissions.

pg_hba.conf configuration example:

host replication repl_acct 0.0.0.0/0 md5

For details please see Configuring Client Access Authentication and Example: Logic Replication Code.

  1. Grant DDL DML permissions for openGauss account.

If you are using a non-super admin account for resharding, you need to GRANT CREATE and CONNECT privileges on the database used for resharding.

GRANT CREATE, CONNECT ON DATABASE resharding_ds_0 TO user;

The account also needs to have access to the reshard tables and schema. Take the t_order table under test schema as an example.

\c resharding_ds_0

GRANT USAGE ON SCHEMA test TO GROUP user;
GRANT SELECT ON TABLE test.t_order TO user;

openGauss has the concept of OWNER, and if the account is the OWNER of a database, SCHEMA, or table, the relevant steps can be omitted.

openGauss does not allow normal accounts to operate in public schema, so if the migrated table is in public schema, you need to authorize additional.

Please refer to openGauss GRANT

GRANT ALL PRIVILEGES TO user;

Complete Procedure Example #

Prerequisite #

  1. Create a database in openGauss.

Example:

DROP DATABASE IF EXISTS resharding_ds_0;
CREATE DATABASE resharding_ds_0;

DROP DATABASE IF EXISTS resharding_ds_1;
CREATE DATABASE resharding_ds_1;
    
DROP DATABASE IF EXISTS resharding_ds_2;
CREATE DATABASE resharding_ds_2;

Procedure #

  1. Create a new logical database in proxy, configure resources and rules, create tables and initialize some data.
CREATE DATABASE sharding_db;

USE sharding_db;

REGISTER STORAGE UNIT ds_0 (
    URL="jdbc:opengauss://127.0.0.1:5432/resharding_ds_0",
    USER="root",
    PASSWORD="root"
), ds_1 (
    URL="jdbc:opengauss://127.0.0.1:5432/resharding_ds_1",
    USER="root",
    PASSWORD="root"
);
    
CREATE SHARDING TABLE RULE t_order(
STORAGE_UNITS(ds_0,ds_1),
SHARDING_COLUMN=order_id,
TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="4")),
KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
);

CREATE TABLE t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));

INSERT INTO t_order (order_id, user_id, status) VALUES (1,2,'ok'),(2,4,'ok'),(3,6,'ok'),(4,1,'ok'),(5,3,'ok'),(6,5,'ok');
  1. Add a new storage unit in the proxy

Example:

REGISTER STORAGE UNIT ds_2 (
    URL="jdbc:opengauss://127.0.0.1:5432/resharding_ds_2",
    USER="root",
    PASSWORD="root"
);
  1. Start scaling-in and scaling-out.

Added ds_2 data source, example:

RESHARD TABLE t_order BY(
    STORAGE_UNITS(ds_0, ds_1, ds_2),
    SHARDING_COLUMN=order_id,
    TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="6")),
    KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
);
  1. View the list of scaling-in and scaling-out operations.
SHOW RESHARDING LIST;

Example results:

+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
| id                                    | tables  | job_item_count       | active | create_time         | stop_time |
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
| j51017f973ac82cb1edea4f5238a258c25e89 | t_order | 2                    | true   | 2022-10-25 10:10:58 | NULL      |
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
  1. View the details of scaling-in and scaling-out.
SHOW RESHARDING STATUS 'j51017f973ac82cb1edea4f5238a258c25e89';

Example results:

+------+-------------+--------------------------+--------+-------------------------+-------------------------------+-------------------+--------------------------+---------------+
| item | data_source | status                   | active | processed_records_count | inventory_finished_percentage | remaining_seconds | incremental_idle_seconds | error_message |
+------+-------------+--------------------------+--------+-------------------------+-------------------------------+-------------------+--------------------------+---------------+
| 0    | ds_0        | EXECUTE_INCREMENTAL_TASK | true   | 3                       | 100                           | 0                 | 92                       |               |
| 1    | ds_1        | EXECUTE_INCREMENTAL_TASK | true   | 3                       | 100                           | 0                 | 92                       |               |
+------+-------------+--------------------------+--------+-------------------------+-------------------------------+-------------------+--------------------------+---------------+
  1. Execute write stop (optional)

This statement will intercept the addition, deletion, modification and partial DistSQL, which is optional.

STOP RESHARDING SOURCE WRITING 'j51017f973ac82cb1edea4f5238a258c25e89';
  1. Perform data consistency verification.
CHECK RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

Query data consistency verification progress:

SHOW RESHARDING CHECK STATUS 'j51017f973ac82cb1edea4f5238a258c25e89';

Example results:

+---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
| tables  | result | finished_percentage | remaining_seconds | check_begin_time        | check_end_time          | duration_seconds | error_message |
+---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
| t_order | true   | 100                 | 0                 | 2022-10-25 10:13:33.220 | 2022-10-25 10:13:35.338 | 2                |               |
+---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
  1. Switch metadata.
APPLY RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

Preview the rule after it takes effect.

PREVIEW SELECT * FROM t_order;

Example results:

+------------------+-----------------------------------------------------------------+
| data_source_name | actual_sql                                                      |
+------------------+-----------------------------------------------------------------+
| ds_0             | select * from v1_t_order_0 UNION ALL select * from v1_t_order_3 |
| ds_1             | select * from v1_t_order_1 UNION ALL select * from v1_t_order_4 |
| ds_2             | select * from v1_t_order_2 UNION ALL select * from v1_t_order_5 |
+------------------+-----------------------------------------------------------------+
  1. Recover write stop (optional).

If the write was stopped before, recovery is required.

RESTORE RESHARDING SOURCE WRITING 'j51017f973ac82cb1edea4f5238a258c25e89';
  1. Complete scaling-in and scaling-out.

Example:

COMMIT RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';