From MySQL to GaussDB Distributed

• On-premises MySQL databases
• MySQL databases on an ECS

GaussDB Distributed (MySQL-compatible)

Source database user

SELECT

Statement: GRANT SELECT ON <database>.<table> to <user>;

SELECT, LOCK TABLES, REPLICATION SLAVE, and REPLICATION CLIENT

Statement: GRANT SELECT, LOCK TABLES ON <database>.<table> to drsUser; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to <user>;

For a full+incremental task, if the source database version is 8.0.2 or later, the XA_RECOVER_ADMIN permission is required to prevent data loss caused by uncommitted XA transactions during startup or task editing.

Destination database user

• Database-level permissions: Log in to the postgres base database as user root or other DATABASE users with the Sysadmin role, and grant the CREATE and CONNECT permissions to user DATABASE.

  Statement: GRANT CREATE, CONNECT ON DATABASE <database> TO <user>

• Schema-level permissions: Log in to the database as user root or user DATABASE with the Sysadmin role, or the owner of the database, and grant the CREATE and USAGE permissions of the schema to the user.

  Statement: GRANT CREATE, USAGE ON SCHEMA <schema> TO <user>

• Table-level permissions: Log in to the database as user root or user DATABASE with the Sysadmin role, or the owner of the database, and grant the DML permission for tables in the schema to the user. (The SELECT permission is required only for tables without primary keys.)

  Statement for granting the DML permission on all tables in the schema: GRANT SELECT, UPDATE, INSERT, DELETE, INDEX, ALTER ON ALL TABLES IN SCHEMA <schema> TO <user>

  Statement for granting the DML permission on a specified table in the schema: GRANT SELECT, UPDATE, INSERT, DELETE, INDEX, ALTER ON TABLE <schema.table> TO <user>

• If gs_loader is used to create system catalogs (such as public.pgxc_copy_error_log and public.gs_copy_summary) in the destination database, the all privilege permission is required for accessing the system catalogs.For details, see gs_loader.

Synchronization objects

• Only table structures, table data, and indexes can be synchronized.
• Only tables with primary keys can be synchronized.
• Only MyISAM and InnoDB tables can be synchronized.
• Virtual columns are synchronized as common columns, and no data is written or incremental synchronization is performed.
• Other database objects such as stored procedures cannot be synchronized.
• A user-defined table that has no primary key and is a partitioned table cannot be synchronized. Otherwise, the task may fail.
• Full and incremental synchronizations do not support invisible columns. Invisible columns can be synchronized since MySQL 8.0.23. For example:

  CREATE TABLE `test11` (
    `id` int NOT NULL,
    `c1` int DEFAULT NULL /*!80023 INVISIBLE */,
    PRIMARY KEY (`id`));

Restrictions on the source database

• The names of the source databases and tables cannot contain non-ASCII characters, or the following special characters: .<'>/\"

• The source database does not support the reset master or reset master to command, which may cause DRS task failures or data inconsistency.
• If the source MySQL database does not support TLS 1.2 or is a self-built database of an earlier version (earlier than 5.6.46 or between 5.7.0 and 5.7.28), you need to submit an O&M application for testing the SSL connection.

Restrictions on usage

General

• Do not perform data restoration on the source database.
• During data synchronization, do not upgrade the source MySQL database across major versions. Otherwise, data may become inconsistent or the synchronization task may fail (data, table structures, and keywords may cause compatibility changes after the cross-version upgrade). You are advised to create a synchronization task again if the source MySQL database is upgraded across major versions.

Full synchronization

• When a DRS task is being started or in the full synchronization phase, do not perform DDL operations on the source database. Otherwise, the task may be abnormal.
• During table structure synchronization in a full synchronization, the column name cannot be CTID, XMIN, CMIN, XMAX, CMAX, TABLEOID, XC_NODE_ID, TID, OID, GS_TUPLE_UID or TABLEBUCKETID. Otherwise, the table fails to be created.
• When the structure of a partitioned table is synchronized during a full synchronization, the partitioned table is converted to a non-partitioned table. During incremental synchronization, operations on partitioned tables in the source database may fail to be synchronized to the destination database or fail to be executed in the destination database.

Incremental synchronization

• Do not modify or delete the usernames, passwords, and permissions of the source and destination databases or change the ports of the source and destination databases.
• If the session variable character_set_client is set to binary, some data may include garbled characters.
• During incremental synchronization, the following DDL operations are supported by default: CREATE_TABLE, RENAME_TABLE, ADD_COLUMN, RENAME_COLUMN, MODIFY_COLUMN, CHANGE_COLUMN, ALTER_COLUMN, DROP_INDEX, and RENAME_INDEX. You can select the DDL operations to be synchronized on the object selection page as required.
• During an incremental synchronization, tables whose primary key type is tinyblob, blob, mediumblob, longblob, tinytext, text, mediumtext, longtext, clob, nclob, bytea, or binary cannot be deleted or updated on the destination database.

Stopping a task

• Stop a task normally.

  Before a task is completed, ensure that the source and destination databases are connected and pay attention to the synchronization status reported by the synchronization log.

• Forcibly stop a task.

  If you forcibly stop a task, DRS resources will be released.

Troubleshooting

• If any problem occurs during task creation, startup, full synchronization, incremental synchronization, or completion, rectify the fault by referring to Troubleshooting.

Other restrictions

• If the DCC does not support instances with 4 vCPUs and 8 GB of memory or higher instance specifications, the synchronization task cannot be created.
• In MySQL, different tables in the same database (schema) can have the same index name or constraint name. In GaussDB, the index and constraint names are unique in the same schema, and the length is limited. To prevent conflicts between index and constraint names, the original index name in the table is changed to the following format after synchronization: hash value + original index name (which may be truncated) + _key. The hash value is calculated based on the original database name_original table name_original index name. Similarly, the original constraint name in the table is changed to the following format: hash value + original constraint name (which may be truncated) + _key.
• By default, the MySQL-to-GaussDB synchronization task does not support loopback and cascading synchronization. That is, data cannot be synchronized from instance A to instance B and then from instance B to instance C.
• After a table in the source database is synchronized to the destination database, the table is distributed in hash mode and cannot be replicated.
• Before creating a DRS task, if concurrency control rules of SQL statements are configured for the source database, the DRS task may fail.
• If the network is reconnected within 30 seconds, real-time synchronization will not be affected. If the network is interrupted for more than 30 seconds, the synchronization task will fail.
• The destination table can contain more columns than the source table. However, the following failures must be avoided:
  • Assume that extra columns on the destination database cannot be null or have default values. If newly inserted data records are synchronized from the source database to the destination database, the extra columns will become null, which does not meet the requirements of the destination database and will cause the task to fail.
  • Assume that extra columns on the destination database must be fixed at a default value and have a unique constraint. If newly inserted data records are synchronized from the source database to the destination database, the extra columns will contain multiple default values. That does not meet the unique constraint of the destination database and will cause the task to fail.
• The changes to the source database cannot be synchronized to the destination database in multiple tasks at the same time. Otherwise, data inconsistency and synchronization failure may occur.
• Only the database-level character set can be set for GaussDB. If the table-level and field-level character sets are set for MySQL, the table structure may fail to be created due to character length differences.
• In a full synchronization for the table structure, the length of char, varchar, nvarchar, enum, and set characters in the source database automatically increases by byte in the destination database (because the length of the destination database is in the unit of byte). The increase multiple depends on the character set of columns in the source database. For example, if the character set of the source database is UTF8, increase the length (byte) by three times. If the character set of the source database is UTF8MB4, increase the length (byte) by four times.
• Do not use an imprecise value type as the primary key in the database. This feature affects the synchronization of UPDATE and DELETE statements in the DRS incremental scenario.
• If a table without a primary key contains large fields (tinyblob, blob, mediumblob, longblob, tinytext, text, mediumtext, longtext, clob, nclob, bytea and binary), data of the large fields may be inconsistent during incremental synchronization.
• Floating-point data is approximate numbers and depends on the OS platform and underlying implementation. FLOAT and DOUBLE data is inaccurate. If you synchronize floating-point data between MySQL and GaussDB, there may be some differences. For details, see the official MySQL documentation.
• MySQL and GaussDB process zero time (0000-00-00 00:00:00) in different ways. During value comparison, if there is zero time in the source or destination database, the comparison result shows that data is inconsistent.
• If a table in the source MySQL database contains a binary field with a fixed length, the MySQL driver adds \0 to the end of the data based on the length. As a result, there may be data inconsistency after the data is synchronized to the destination GaussDB database.
• If the source database contains non-standard floating-point data and the data can be written in loose mode but cannot be written in strict mode, there may be data inconsistency during synchronization.
• Two-phase commit is not supported.
• If the source table to be synchronized has the AUTO_INCREMENT attribute, DRS automatically updates the start value of the GaussDB auto-increment sequence corresponding to the integer sequence of the table when the task is complete. The updated value is the maximum value of the sequence plus 10,000.
• If the table structure is synchronized, column names in the destination database will be converted to lowercase letters by default. If the table to be synchronized already exists in the destination database, the column names of the synchronization object table in the destination database must be in lowercase. If column names in the destination database are required to be case sensitive, choose Service Tickets > Create Service Ticket in the upper right corner of the management console to submit a service ticket.
• The DDL operation of renaming an unselected table is filtered out during the synchronization. As a result, the task may fail or data may be inconsistent.
  • If you rename table A to the name of table B and tables A and B are selected for synchronization, this RENAME statement will not be filtered out.
  • If you rename table A to the name of table B but table B is not synchronized, this RENAME statement will be filtered out.
  • You are not advised to perform the rename operation in the many-to-one synchronization scenario. Otherwise, the task may fail or data may be inconsistent.