From DDM to MySQL

• DDM instances

• On-premises MySQL databases
• MySQL databases on an ECS
• MySQL databases on other clouds
• RDS for MySQL

Database permissions

• The source database DDM account must have at least one permission, for example, SELECT. The physical sharded database account must have the following permissions: SELECT, SHOW VIEW, EVENT, LOCK TABLES, REPLICATION SLAVE and REPLICATION CLIENT.

• The destination database user must have the following permissions: SELECT, CREATE, ALTER, DROP, DELETE, INSERT, and UPDATE. The root account of the RDS for MySQL DB instance has the preceding permissions by default.

Synchronization object

• Full synchronization supports the synchronization of data, table structures, and indexes.
• The source database cannot contain tables whose sharding keys are timestamp.
• The sharding key of the source table must be added to the primary key and unique key of the destination table, which means that the primary key and unique key columns of the destination table must contain the sharded columns of the source table to avoid data conflict and inconsistency.

Source database

• During the incremental synchronization, the binlog of the source sharded database must be enabled and use the row-based format.
• If the storage space is sufficient, store the source database binlog for as long as possible. The recommended retention period is three days.
• During an incremental synchronization, the server_id value of the MySQL source database must be set. If the source database version is MySQL 5.6 or earlier, the server_id value ranges from 2 to 4294967296. If the source database is MySQL 5.7 or later, the server_id value ranges from 1 to 4294967296.
• The database names and table names of the source sharding middleware cannot contain the following characters: '<>/\ and non-ASCII characters.
• Enable skip-name-resolve for the MySQL source database to reduce the possibility of connection timeout.
• Enable GTID of the source database.

Destination database

• The destination database is an on-premises MySQL database.
• The destination DB instance must have sufficient storage space.
• If the destination database (excluding MySQL system database) has the same name as the source database, the table structures in the destination database must be consistent with those in the source database.
• The character set of the destination database must be the same as that of the source database.
• The time zone of the destination database must be the same as that of the source database.
• During a synchronization, a large amount of data is written to the destination database. If the value of the max_allowed_packet parameter of the destination database is too small, data cannot be written. You are advised to set the max_allowed_packet parameter to a value greater than 100 MB.

Precautions

• If the data types are incompatible, the synchronization may fail.
• If the source database contains a duplicate primary key or unique key, the data synchronized to the destination database will be less than that in the source database. Therefore, you must check and correct the data before starting the synchronization task.
• If the destination DB instance is an RDS for MySQL instance, tables encrypted using Transparent Data Encryption (TDE) cannot be synchronized.
• If the destination 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.
• 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 cannot be null or have default values. If newly inserted data records are synchronized from the source to the destination, the extra columns will become null, which does not meet the requirements of the destination and will cause the task to fail.
  • Assume that extra columns on the destination must be fixed at a default value and have a unique constraint. If newly inserted data records are synchronized from the source to the destination, the extra columns will contain multiple default values. That does not meet the unique constraint of the destination and will cause the task to fail.
• Resumable upload is supported, but data may be repeatedly inserted into a table that does not have a primary key when the server system breaks down.
• After a task is created, the destination database cannot be set to read-only.
• After a task is created, you cannot add schemas to the source database or modify the old schema to associate with the new RDS DB instance. Otherwise, data cannot be synchronized or the task fails.
• During synchronization, do not modify or delete the usernames, passwords, permissions, or ports of the source and destination databases.
• During the synchronization, do not change the sharding key of a table on the source DDM instance, or change an unsharded or broadcast table to a sharded table, or change a sharded table to an unsharded or broadcast table.
• During an incremental synchronization, do not perform the restoration operation on the source database.
• During incremental synchronization, some DDL operations are supported.
  • DROP_DATABASE, DROP_TABLE, TRUNCATE_TABLE, CREATE_VIEW and DROP_VIEW are not supported.
  • Online DDL is not supported.
  • Tables can be created. For example:

    create table `ddl_test` (id int, c1 varchar(25), primary key(id));
    create table `ddl_test_gho` like `ddl_test`;

  • Tables can be renamed. Both the source and destination tables must be selected. For example:

    rename table `ddl_test` to `ddl_test_new`;

  • Columns in a table can be added and modified, but cannot be deleted. For example:

    alter table `ddl_test` add column `c2` varchar(25); 
    alter table `ddl_test` modify column `c1` varchar(50);
    alter table `ddl_test` alter c1 set default 'xxx';

  • Table indexes can be modified. For example:

    alter table `ddl_test` drop primary key; 
    alter table `ddl_test` add primary key(id); 
    alter table `ddl_test` add index  `ddl_test_uk`(id);
    alter table `ddl_test` drop index `ddl_test_uk`;

  • In table-level synchronization, you can add columns, modify columns, and add primary keys and normal indexes.
  • During database-level synchronization, you can create tables, rename tables, add columns, modify columns, and add primary keys and normal indexes.
  • The name of a table, column, or index to be added or modified cannot exceed 63 characters. Otherwise, the task fails.
  • If a primary key is added to a table that does not have a primary key in the source database, the DDL operation must contain the first column. Otherwise, the task fails.
• During incremental synchronization, perform DDL operations on the same table or column during off-peak hours at an interval of more than 1 minute.