Manual #
MySQL User Manual #
Environment #
MySQL 5.1.15 ~ 8.0.x.
Privileges #
- 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 |
+-----------------------------------------+---------------------------------------+
- 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} |
|....... |
+------------------------------------------------------------------------------+
Complete Procedure Example #
Prerequisite #
- 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 #
- 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');
- Add a new data source 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"
);
- 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"))
);
- View the list of scaling-in and scaling-out operations.
SHOW RESHARDING LIST;
Example results:
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
| id | tables | sharding_total_count | active | create_time | stop_time |
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
| j51017f973ac82cb1edea4f5238a258c25e89 | t_order | 2 | true | 2022-10-25 10:10:58 | NULL |
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
- 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 | |
+------+-------------+--------------------------+--------+-------------------------+-------------------------------+-------------------+--------------------------+---------------+
- Execute write stop (Optional).
This statement will intercept the addition, deletion, modification and partial DistSQL, which is optional.
STOP RESHARDING SOURCE WRITING 'j51017f973ac82cb1edea4f5238a258c25e89';
- 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 | |
+---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
- 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 |
+------------------+-----------------------------------------------------------------+
- Recover write stop (optional).
If the write was stopped before, recovery is required.
RESTORE RESHARDING SOURCE WRITING 'j51017f973ac82cb1edea4f5238a258c25e89';
- Complete jobs of scaling-in and scaling-out.
Example:
COMMIT RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';
PostgreSQL User Manual #
Environment #
PostgreSQL 9.4+
Privileges #
Start test_decoding.
Adjust the WAL configuration.
postgresql.conf
configuration example:
wal_level = logical
max_wal_senders = 10
max_replication_slots = 10
max_connections = 600
For details please see Write Ahead Log and Replication.
- 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.
Complete Procedure Example #
Prerequisite #
- 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 #
- 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');
- Add a new data source in the proxy.
Example:
REGISTER STORAGE UNIT ds_2 (
URL="jdbc:postgresql://127.0.0.1:5432/resharding_ds_2",
USER="root",
PASSWORD="root"
);
- 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"))
);
- View the list of scaling-in and scaling-out operations.
SHOW RESHARDING LIST;
Example results:
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
| id | tables | sharding_total_count | active | create_time | stop_time |
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
| j51017f973ac82cb1edea4f5238a258c25e89 | t_order | 2 | true | 2022-10-25 10:10:58 | NULL |
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
- 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 | |
+------+-------------+--------------------------+--------+-------------------------+-------------------------------+-------------------+--------------------------+---------------+
- Execute write stop (optional).
This statement will intercept the addition, deletion, modification and partial DistSQL, which is optional.
STOP RESHARDING SOURCE WRITING 'j51017f973ac82cb1edea4f5238a258c25e89';
- 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 | |
+---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
- 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 |
+------------------+-----------------------------------------------------------------+
- Recover write stop (optional).
If the write was stopped before, recovery is required.
RESTORE RESHARDING SOURCE WRITING 'j51017f973ac82cb1edea4f5238a258c25e89';
- Complete jobs of scaling-in and scaling-out.
Example:
COMMIT RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';
openGauss User Manual #
Environment #
openGauss 2.0.1 ~ 3.0.0
Privileges #
- 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.
- 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.
Complete Procedure Example #
Prerequisite #
- 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 #
- 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');
- Add a new data source in the proxy
Example:
REGISTER STORAGE UNIT ds_2 (
URL="jdbc:opengauss://127.0.0.1:5432/resharding_ds_2",
USER="root",
PASSWORD="root"
);
- 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"))
);
- View the list of scaling-in and scaling-out operations.
SHOW RESHARDING LIST;
Example results:
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
| id | tables | sharding_total_count | active | create_time | stop_time |
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
| j51017f973ac82cb1edea4f5238a258c25e89 | t_order | 2 | true | 2022-10-25 10:10:58 | NULL |
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
- 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 | |
+------+-------------+--------------------------+--------+-------------------------+-------------------------------+-------------------+--------------------------+---------------+
- Execute write stop (optional)
This statement will intercept the addition, deletion, modification and partial DistSQL, which is optional.
STOP RESHARDING SOURCE WRITING 'j51017f973ac82cb1edea4f5238a258c25e89';
- 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 | |
+---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
- 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 |
+------------------+-----------------------------------------------------------------+
- Recover write stop (optional).
If the write was stopped before, recovery is required.
RESTORE RESHARDING SOURCE WRITING 'j51017f973ac82cb1edea4f5238a258c25e89';
- Complete scaling-in and scaling-out.
Example:
COMMIT RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';