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 to check whether the user has migration permission.

  1. SHOW GRANTS FOR 'migration_user';

Result sample:

  1. +------------------------------------------------------------------------------+
  2. |Grants for ${username}@${host}                                                |
  3. +------------------------------------------------------------------------------+
  4. |GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ${username}@${host}     |
  5. |.......                                                                       |
  6. +------------------------------------------------------------------------------+
  1. Grant insert, select, update and delete permissions to the physical library used in the migration

If you use a non-super admin account for migration, you need to make sure that the account has the permission to insert, select, update and delete on the physical library used for migration.

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

Please refer to MySQL GRANT

Complete procedure example

Requirements

  1. Prepare the source database, table, and data in MySQL.
  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.
  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. REGISTER STORAGE UNIT 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. STORAGE_UNITS(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. REGISTER MIGRATION SOURCE STORAGE UNIT 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  | job_item_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';

Result example:

  1. +------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
  2. | item | data_source | status                   | active | processed_records_count | inventory_finished_percentage | incremental_idle_seconds | error_message |
  3. +------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
  4. | 0    | ds_0        | EXECUTE_INCREMENTAL_TASK | true   | 6                       | 100                           | 81                       |               |
  5. +------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
  1. Verify data consistency.
  1. CHECK MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6' BY TYPE (NAME='CRC32_MATCH');

Data consistency check algorithm list:

  1. SHOW MIGRATION CHECK ALGORITHMS;

Result example:

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

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';

Result example:

  1. +---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
  2. | tables  | result | finished_percentage | remaining_seconds | check_begin_time        | check_end_time          | duration_seconds | error_message |
  3. +---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
  4. | t_order | true   | 100                 | 0                 | 2022-10-13 11:18:15.171 | 2022-10-13 11:18:15.878 | 0                |               |
  5. +---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
  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.

  1. Grant access to databases and tables

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

  1. GRANT CREATE, CONNECT ON DATABASE migration_ds_0 TO migration_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.

  1. \c migration_ds_0
  3. GRANT USAGE ON SCHEMA test TO GROUP migration_user;
  4. GRANT SELECT ON TABLE test.t_order TO migration_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

Requirements

  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. REGISTER STORAGE UNIT 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. STORAGE_UNITS(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. REGISTER MIGRATION SOURCE STORAGE UNIT 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  | job_item_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';

Result example:

  1. +------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
  2. | item | data_source | status                   | active | processed_records_count | inventory_finished_percentage | incremental_idle_seconds | error_message |
  3. +------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
  4. | 0    | ds_0        | EXECUTE_INCREMENTAL_TASK | true   | 6                       | 100                           | 81                       |               |
  5. +------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
  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';

Result example:

  1. +---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
  2. | tables  | result | finished_percentage | remaining_seconds | check_begin_time        | check_end_time          | duration_seconds | error_message |
  3. +---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
  4. | t_order | true   | 100                 | 0                 | 2022-10-13 11:18:15.171 | 2022-10-13 11:18:15.878 | 0                |               |
  5. +---------+--------+---------------------+-------------------+-------------------------+-------------------------+------------------+---------------+
  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.

  1. Grant access to databases and tables

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

  1. GRANT CREATE, CONNECT ON DATABASE migration_ds_0 TO migration_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.

  1. \c migration_ds_0
  3. GRANT USAGE ON SCHEMA test TO GROUP migration_user;
  4. GRANT SELECT ON TABLE test.t_order TO migration_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

  1. GRANT ALL PRIVILEGES TO migration_user;

Complete procedure example

Requirements

  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. REGISTER STORAGE UNIT 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. STORAGE_UNITS(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. REGISTER MIGRATION SOURCE STORAGE UNIT ds_0 (
  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  | job_item_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';

Result example:

  1. +------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
  2. | item | data_source | status                   | active | processed_records_count | inventory_finished_percentage | incremental_idle_seconds | error_message |
  3. +------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
  4. | 0    | ds_0        | EXECUTE_INCREMENTAL_TASK | true   | 6                       | 100                           | 81                       |               |
  5. +------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
  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';

Result example:

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

Please refer to RAL#Migration for more details.