Manual

MySQL user guide

Environment

Supported MySQL versions: 5.1.15 to 8.0.x.

Authority required

  1. Enable binlog

MySQL 5.7 my.cnf configuration sample:

  1. [mysqld]
  2. server-id=1
  3. log-bin=mysql-bin
  4. binlog-format=row
  5. binlog-row-image=full
  6. max_connections=600

Run the following command and check whether binlog is enabled.

  1. show variables like '%log_bin%';
  2. show variables like '%binlog%';

If the following information is displayed, binlog is enabled.

  1. +-----------------------------------------+---------------------------------------+
  2. | Variable_name                           | Value                                 |
  3. +-----------------------------------------+---------------------------------------+
  4. | log_bin                                 | ON                                    |
  5. | binlog_format                           | ROW                                   |
  6. | binlog_row_image                        | FULL                                  |
  7. +-----------------------------------------+---------------------------------------+
  1. Grant Replication-related permissions for MySQL account.

Run the following command and see whether the user has migration permission.

  1. SHOW GRANTS FOR 'user';

Result sample:

  1. +------------------------------------------------------------------------------+
  2. |Grants for ${username}@${host}                                                |
  3. +------------------------------------------------------------------------------+
  4. |GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ${username}@${host}     |
  5. |.......                                                                       |
  6. +------------------------------------------------------------------------------+

Complete procedure example

Prerequisite

  1. Prepare the source database, table, and data in MySQL.

Sample:

  1. DROP DATABASE IF EXISTS migration_ds_0;
  2. CREATE DATABASE migration_ds_0 DEFAULT CHARSET utf8;
  4. USE migration_ds_0
  6. CREATE TABLE t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));
  8. 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. Prepare the target database in MySQL.

Sample:

  1. DROP DATABASE IF EXISTS migration_ds_10;
  2. CREATE DATABASE migration_ds_10 DEFAULT CHARSET utf8;
  4. DROP DATABASE IF EXISTS migration_ds_11;
  5. CREATE DATABASE migration_ds_11 DEFAULT CHARSET utf8;
  7. DROP DATABASE IF EXISTS migration_ds_12;
  8. CREATE DATABASE migration_ds_12 DEFAULT CHARSET utf8;

Procedure

  1. Create a new logical database in proxy and configure resources and rules.
  1. CREATE DATABASE sharding_db;
  3. USE sharding_db
  5. ADD RESOURCE ds_2 (
  6.     URL="jdbc:mysql://127.0.0.1:3306/migration_ds_10?serverTimezone=UTC&useSSL=false",
  7.     USER="root",
  8.     PASSWORD="root",
  9.     PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
  10. ), ds_3 (
  11.     URL="jdbc:mysql://127.0.0.1:3306/migration_ds_11?serverTimezone=UTC&useSSL=false",
  12.     USER="root",
  13.     PASSWORD="root",
  14.     PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
  15. ), ds_4 (
  16.     URL="jdbc:mysql://127.0.0.1:3306/migration_ds_12?serverTimezone=UTC&useSSL=false",
  17.     USER="root",
  18.     PASSWORD="root",
  19.     PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
  20. );
  22. CREATE SHARDING TABLE RULE t_order(
  23. RESOURCES(ds_2,ds_3,ds_4),
  24. SHARDING_COLUMN=order_id,
  25. TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="6")),
  26. KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
  27. );

If you are migrating to a heterogeneous database, you need to execute the table-creation statements in proxy.

  1. Configure the source resources in proxy.
  1. ADD MIGRATION SOURCE RESOURCE ds_0 (
  2.     URL="jdbc:mysql://127.0.0.1:3306/migration_ds_0?serverTimezone=UTC&useSSL=false",
  3.     USER="root",
  4.     PASSWORD="root",
  5.     PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
  6. );
  1. Start data migration.
  1. MIGRATE TABLE ds_0.t_order INTO t_order;

Or you can specify a target logical database.

  1. MIGRATE TABLE ds_0.t_order INTO sharding_db.t_order;
  1. Check the data migration job list.
  1. SHOW MIGRATION LIST;

Result example:

  1. +---------------------------------------+---------+----------------------+--------+---------------------+-----------+
  2. | id                                    | tables  | sharding_total_count | active | create_time         | stop_time |
  3. +---------------------------------------+---------+----------------------+--------+---------------------+-----------+
  4. | j01016e501b498ed1bdb2c373a2e85e2529a6 | t_order | 1                    | true   | 2022-10-13 11:16:01 | NULL      |
  5. +---------------------------------------+---------+----------------------+--------+---------------------+-----------+
  1. View the data migration details.
  1. SHOW MIGRATION STATUS 'j01016e501b498ed1bdb2c373a2e85e2529a6';
  2. +------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
  3. | item | data_source | status                   | active | processed_records_count | inventory_finished_percentage | incremental_idle_seconds | error_message |
  4. +------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
  5. | 0    | ds_0        | EXECUTE_INCREMENTAL_TASK | true   | 6                       | 100                           | 81                       |               |
  6. +------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
  1. Verify data consistency.
  1. CHECK MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6' BY TYPE (NAME='CRC32_MATCH');
  2. Query OK, 0 rows affected (0.09 sec)

Data consistency check algorithm list:

  1. SHOW MIGRATION CHECK ALGORITHMS;
  2. +-------------+--------------------------------------------------------------+----------------------------+
  3. | type        | supported_database_types                                     | description                |
  4. +-------------+--------------------------------------------------------------+----------------------------+
  5. | CRC32_MATCH | MySQL                                                        | Match CRC32 of records.    |
  6. | DATA_MATCH  | SQL92,MySQL,MariaDB,PostgreSQL,openGauss,Oracle,SQLServer,H2 | Match raw data of records. |
  7. +-------------+--------------------------------------------------------------+----------------------------+

If encrypt rule is configured in target proxy, then DATA_MATCH could be used.

If you are migrating to a heterogeneous database, then DATA_MATCH could be used.

Query data consistency check progress:

  1. SHOW MIGRATION CHECK STATUS 'j01016e501b498ed1bdb2c373a2e85e2529a6';
  2. +---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
  3. | tables  | result | finished_percentage | remaining_seconds | check_begin_time        | check_end_time          | duration_seconds | error_message |
  4. +---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
  5. | t_order | true   | 100                 | 0                 | 2022-10-13 11:18:15.171 | 2022-10-13 11:18:15.878 | 0                |               |
  6. +---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
  1. Commit the job.
  1. COMMIT MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6';
  1. Refresh table metadata.
  1. REFRESH TABLE METADATA;

Please refer to RAL#Migration for more details.

PostgreSQL user guide

Environment

Supported PostgreSQL version: 9.4 or later.

Authority required

  1. Enable test_decoding.

  2. Modify WAL Configuration.

postgresql.conf configuration sample:

  1. wal_level = logical
  2. max_wal_senders = 10
  3. max_replication_slots = 10
  4. wal_sender_timeout = 0
  5. max_connections = 600

Please refer to Write Ahead Log and Replication for details.

  1. Configure PostgreSQL and grant Proxy the replication permission.

pg_hba.conf instance configuration:

  1. host replication repl_acct 0.0.0.0/0 md5

Please refer to The pg_hba.conf File for details.

Complete procedure example

Prerequisite

  1. Prepare the source database, table, and data in PostgreSQL.
  1. DROP DATABASE IF EXISTS migration_ds_0;
  2. CREATE DATABASE migration_ds_0;
  4. \c migration_ds_0
  6. CREATE TABLE t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));
  8. 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. Prepare the target database in PostgreSQL.
  1. DROP DATABASE IF EXISTS migration_ds_10;
  2. CREATE DATABASE migration_ds_10;
  4. DROP DATABASE IF EXISTS migration_ds_11;
  5. CREATE DATABASE migration_ds_11;
  7. DROP DATABASE IF EXISTS migration_ds_12;
  8. CREATE DATABASE migration_ds_12;

Procedure

  1. Create a new logical database in proxy and configure resources and rules.
  1. CREATE DATABASE sharding_db;
  3. \c sharding_db
  5. ADD RESOURCE ds_2 (
  6.     URL="jdbc:postgresql://127.0.0.1:5432/migration_ds_10",
  7.     USER="postgres",
  8.     PASSWORD="root",
  9.     PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
  10. ), ds_3 (
  11.     URL="jdbc:postgresql://127.0.0.1:5432/migration_ds_11",
  12.     USER="postgres",
  13.     PASSWORD="root",
  14.     PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
  15. ), ds_4 (
  16.     URL="jdbc:postgresql://127.0.0.1:5432/migration_ds_12",
  17.     USER="postgres",
  18.     PASSWORD="root",
  19.     PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
  20. );
  22. CREATE SHARDING TABLE RULE t_order(
  23. RESOURCES(ds_2,ds_3,ds_4),
  24. SHARDING_COLUMN=order_id,
  25. TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="6")),
  26. KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
  27. );

If you are migrating to a heterogeneous database, you need to execute the table-creation statements in proxy.

  1. Configure the source resources in proxy.
  1. ADD MIGRATION SOURCE RESOURCE ds_0 (
  2.     URL="jdbc:postgresql://127.0.0.1:5432/migration_ds_0",
  3.     USER="postgres",
  4.     PASSWORD="root",
  5.     PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
  6. );
  1. Enable data migration.
  1. MIGRATE TABLE ds_0.t_order INTO t_order;

Or you can specify a target logical database.

  1. MIGRATE TABLE ds_0.t_order INTO sharding_db.t_order;

Or you can specify a source schema name.

  1. MIGRATE TABLE ds_0.public.t_order INTO sharding_db.t_order;
  1. Check the data migration job list.
  1. SHOW MIGRATION LIST;

Result example:

  1. +---------------------------------------+---------+----------------------+--------+---------------------+-----------+
  2. | id                                    | tables  | sharding_total_count | active | create_time         | stop_time |
  3. +---------------------------------------+---------+----------------------+--------+---------------------+-----------+
  4. | j01016e501b498ed1bdb2c373a2e85e2529a6 | t_order | 1                    | true   | 2022-10-13 11:16:01 | NULL      |
  5. +---------------------------------------+---------+----------------------+--------+---------------------+-----------+
  1. View the data migration details.
  1. SHOW MIGRATION STATUS 'j01016e501b498ed1bdb2c373a2e85e2529a6';
  2. +------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
  3. | item | data_source | status                   | active | processed_records_count | inventory_finished_percentage | incremental_idle_seconds | error_message |
  4. +------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
  5. | 0    | ds_0        | EXECUTE_INCREMENTAL_TASK | true   | 6                       | 100                           | 81                       |               |
  6. +------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
  1. Verify data consistency.
  1. CHECK MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6';
  2. Query OK, 0 rows affected (0.09 sec)

Query data consistency check progress:

  1. SHOW MIGRATION CHECK STATUS 'j01016e501b498ed1bdb2c373a2e85e2529a6';
  2. +---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
  3. | tables  | result | finished_percentage | remaining_seconds | check_begin_time        | check_end_time          | duration_seconds | error_message |
  4. +---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
  5. | t_order | true   | 100                 | 0                 | 2022-10-13 11:18:15.171 | 2022-10-13 11:18:15.878 | 0                |               |
  6. +---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
  1. Commit the job.
  1. COMMIT MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6';
  1. Refresh table metadata.
  1. REFRESH TABLE METADATA;

Please refer to RAL#Migration for more details.

openGauss user guide

Environment

Supported openGauss version: 2.0.1 to 3.0.0.

Authority required

  1. Modify WAL configuration.

postgresql.conf configuration sample:

  1. wal_level = logical
  2. max_wal_senders = 10
  3. max_replication_slots = 10
  4. wal_sender_timeout = 0
  5. max_connections = 600

Please refer to Write Ahead Log and Replication for details.

  1. Configure openGauss and grant Proxy the replication permission.

pg_hba.conf instance configuration:

  1. host replication repl_acct 0.0.0.0/0 md5

Please refer to Configuring Client Access Authentication and Example: Logic Replication Code for details.

Complete procedure example

Prerequisite

  1. Prepare the source database, table, and data in openGauss.
  1. DROP DATABASE IF EXISTS migration_ds_0;
  2. CREATE DATABASE migration_ds_0;
  4. \c migration_ds_0
  6. CREATE TABLE t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));
  8. 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. Prepare the target database in openGauss.
  1. DROP DATABASE IF EXISTS migration_ds_10;
  2. CREATE DATABASE migration_ds_10;
  4. DROP DATABASE IF EXISTS migration_ds_11;
  5. CREATE DATABASE migration_ds_11;
  7. DROP DATABASE IF EXISTS migration_ds_12;
  8. CREATE DATABASE migration_ds_12;

Procedure

  1. Create a new logical database and configure resources and rules.
  1. CREATE DATABASE sharding_db;
  3. \c sharding_db
  5. ADD RESOURCE ds_2 (
  6.     URL="jdbc:opengauss://127.0.0.1:5432/migration_ds_10",
  7.     USER="gaussdb",
  8.     PASSWORD="Root@123",
  9.     PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
  10. ), ds_3 (
  11.     URL="jdbc:opengauss://127.0.0.1:5432/migration_ds_11",
  12.     USER="gaussdb",
  13.     PASSWORD="Root@123",
  14.     PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
  15. ), ds_4 (
  16.     URL="jdbc:opengauss://127.0.0.1:5432/migration_ds_12",
  17.     USER="gaussdb",
  18.     PASSWORD="Root@123",
  19.     PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
  20. );
  22. CREATE SHARDING TABLE RULE t_order(
  23. RESOURCES(ds_2,ds_3,ds_4),
  24. SHARDING_COLUMN=order_id,
  25. TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="6")),
  26. KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
  27. );

If you are migrating to a heterogeneous database, you need to execute the table-creation statements in proxy.

  1. Configure the source resources in proxy.
  1. ADD MIGRATION SOURCE RESOURCE ds_2 (
  2.     URL="jdbc:opengauss://127.0.0.1:5432/migration_ds_0",
  3.     USER="gaussdb",
  4.     PASSWORD="Root@123",
  5.     PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
  6. );
  1. Enable data migration.
  1. MIGRATE TABLE ds_0.t_order INTO t_order;

Or you can specify a target logical database.

  1. MIGRATE TABLE ds_0.t_order INTO sharding_db.t_order;

Or you can specify a source schema name.

  1. MIGRATE TABLE ds_0.public.t_order INTO sharding_db.t_order;
  1. Check the data migration job list.
  1. SHOW MIGRATION LIST;

Result example:

  1. +---------------------------------------+---------+----------------------+--------+---------------------+-----------+
  2. | id                                    | tables  | sharding_total_count | active | create_time         | stop_time |
  3. +---------------------------------------+---------+----------------------+--------+---------------------+-----------+
  4. | j01016e501b498ed1bdb2c373a2e85e2529a6 | t_order | 1                    | true   | 2022-10-13 11:16:01 | NULL      |
  5. +---------------------------------------+---------+----------------------+--------+---------------------+-----------+
  1. View the data migration details.
  1. SHOW MIGRATION STATUS 'j01016e501b498ed1bdb2c373a2e85e2529a6';
  2. +------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
  3. | item | data_source | status                   | active | processed_records_count | inventory_finished_percentage | incremental_idle_seconds | error_message |
  4. +------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
  5. | 0    | ds_0        | EXECUTE_INCREMENTAL_TASK | true   | 6                       | 100                           | 81                       |               |
  6. +------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
  1. Verify data consistency.
  1. CHECK MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6';
  2. Query OK, 0 rows affected (0.09 sec)

Query data consistency check progress:

  1. SHOW MIGRATION CHECK STATUS 'j01016e501b498ed1bdb2c373a2e85e2529a6';
  2. +---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
  3. | tables  | result | finished_percentage | remaining_seconds | check_begin_time        | check_end_time          | duration_seconds | error_message |
  4. +---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
  5. | t_order | true   | 100                 | 0                 | 2022-10-13 11:18:15.171 | 2022-10-13 11:18:15.878 | 0                |               |
  6. +---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
  1. Commit the job.
  1. COMMIT MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6';
  1. Refresh table metadata.
  1. REFRESH TABLE METADATA;

Please refer to RAL#Migration for more details.