This document describes how to use DTS to synchronize data from MySQL, MariaDB, Percona, TDSQL-C MySQL, and TDSQL Boundless databases to TencentDB for MariaDB.
The deployment types supported by the source database are as follows:
Self-built MySQL and TencentDB for MySQL
Self-built MariaDB and TencentDB for MariaDB
Self-built Percona
TencentDB TDSQL-C MySQL.
TencentDB TDSQL Boundless.
Since the requirements and operation steps for synchronizing MySQL, MariaDB, Percona, TDSQL-C MySQL, and TDSQL Boundless to TencentDB for MariaDB are essentially the same for the five scenarios, this section only uses MariaDB-to-MariaDB data synchronization as an example. For other scenarios, see relevant content.
Must-Knows
During full data synchronization, DTS consumes certain resources of the source instance, which may lead to increased load and additional pressure on the database. If your database configuration is insufficient, it is recommended to perform the task during off-peak hours.
To avoid data duplication, ensure that the tables to be synchronized have primary keys or non-null unique keys.
A lock-free method is adopted by default. No global lock (FTWRL) is applied to the source database during synchronization. Only the tables without primary keys will be locked at the table level, while no locks will be added to other tables.
During data synchronization, DTS uses the account for executing the synchronization task to write to the system database __tencentdb__ in the source database. This database records information from data comparison during synchronization.
To ensure that data comparison issues can be traced later, the __tencentdb__ database in the source will not be deleted after the synchronization task ends.
The __tencentdb__ system database occupies minimal space, approximately 0.01% to 0.1% of the source database's storage capacity. For example, for a 50 GB source database, the __tencentdb__ system database is approximately 5 MB–50 MB. It employs a single-threaded, connection-waiting mechanism, thus having a negligible impact on source database performance and not preempting any resources.
2. Establish the access channel between DTS and the database in advance according to the access type you want to use. For details, see Network Preparations. Internet Data Center (IDC) self-built databases/LighthouseDB/Databases of other cloud vendors: The access method can be set to Public Network/DC/VPN Access/CCN. Self-built databases on Cloud Virtual Machine (CVM) instances: The access method can be set to Self-Built on CVM.
TencentDB instances: The access method can be set to Cloud Database.
3. The following permissions on the source database are required:
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW, PROCESS, SELECT ON *.* TO 'account'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON `__tencentdb__`.* TO 'account'@'%';
FLUSH PRIVILEGES;
The following permissions on the target database are required: ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER, and UPDATE.
Use Instructions
Synchronization Objects
1. Cross-instance synchronization and synchronization within the same instance are supported. Synchronization within the same instance is only supported from MariaDB to MariaDB.
2. Only basic tables, views, stored procedures, and functions are supported for synchronization.
3. Correlated data objects need to be synchronized simultaneously; otherwise, the synchronization will fail. Common relationships include: views referencing tables, views referencing views, and primary-foreign key associated tables.
4. To avoid data duplication, it is recommended to synchronize tables that have primary keys or non-null unique keys.
5. Synchronizing the entire source instance is not supported. If a database creation operation (CREATE DATABASE) is performed at the source during the subsequent synchronization task, it is recommended that you manually create the corresponding database object at the target before starting the synchronization task, or modify synchronization configurations to add the new database object after the synchronization task is started.
6. If the CREATE TABLE or RENAME TABLE operation is performed at the source during a synchronization task, you should select the entire database where the table resides when selecting synchronization objects during the configuration phase. Otherwise, the newly created or renamed tables will not be synchronized to the target.
7. When synchronizing views, stored procedures, and functions, DTS checks whether the DEFINER corresponding to user1 ([DEFINER = user1]) in the source database matches the synchronization account user2. If they do not match, after synchronization, DTS will modify the SQL SECURITY property of user1 in the target database from DEFINER to INVOKER ([INVOKER = user1]), and set DEFINER in the target database to the synchronization account user2 ([DEFINER = synchronization account user2]). Excessively complex view definitions in the source database may cause task failure.
8. When using Alibaba Cloud ApsaraDB RDS for MySQL as the source database, MySQL 5.6 does not support synchronizing tables without primary keys, whereas this restriction does not apply to MySQL 5.7 and later versions. For Amazon RDS for MySQL as the source, tables without primary keys also cannot be synchronized.
9. Only InnoDB, MyISAM, and TokuDB database engines are supported for synchronization. Tables using other engine types are skipped by default during synchronization. If the source database contains TokuDB engine data in compressed mode, the target database must support compressed mode synchronization. Otherwise, the task will report errors.
10. When the target is a TencentDB for MySQL instance that uses the RocksDB engine, it supports converting the source InnoDB/TokuDB engine to the target RocksDB engine. However, the following constraints exist when InnoDB is converted to RocksDB.
10.1 If the primary key, or part of the primary key, in a table to be migrated from the source database uses TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT field types, SQL statements involving value equality (such as DELETE FROM table_name WHERE primary_key_column = 'some text') will not take effect after synchronization. This is because these field types do not support value equality on the target, which may result in data inconsistencies between the source and target databases.
10.2 During the data consistency check, if the primary key, or part of the primary key, in a table to be migrated from the source database uses TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT field types and the table contains more than 50,000 rows, the table will be skipped during the check.
10.3 Due to differences in the underlying storage of DOUBLE data types between the RocksDB and InnoDB engines, when the DOUBLE values on the source are close to the upper limit (such as -1.7976931348623157E+308), false positives may occur during the creation of the consistency check task. Although the actual data is consistent, the check results may indicate inconsistency.
10.4 Migration is not supported for partitioned tables.
Data Types
1. During incremental synchronization, if binlog statements in STATEMENT format are generated in the source database, data synchronization will fail.
2. If global transaction identifier (GTID) gaps in the binlog of the source database, it may affect the performance of the synchronization task and even cause the task to fail.
3. A single transaction cannot contain both DML and DDL statements at the same time. Such transactions will cause task errors.
4. Geometry-related data types are not supported. Such data will cause task errors.
5. ALTER VIEW statements are not supported and will be skipped during synchronization.
HA Switches
If the source is a non-GTID instance, DTS does not support a high availability (HA) switch on the source. If a switch is performed on the source MySQL, the DTS incremental synchronization may be interrupted.
Policy for Primary Key Conflicts
When the policy for primary key conflicts is set to report an error upon conflicts, do not perform dual-write on the target during the full synchronization phase of the task. Otherwise, if a primary key conflict occurs, DTS will not report an error but directly overwrite the conflicting data in the target database with the data from the source database.
Operation Restrictions
Do not perform the following operations during synchronization. Otherwise, they will cause the synchronization task to fail.
1. During structure initialization and full data initialization scenarios, DDL operations are not supported when the step of the synchronization task is full export or full import.
2. Do not modify or delete user information (including usernames, passwords, and permissions) and port numbers in the source and target databases.
3. Do not perform binlog cleanup operations on the source database.
Use Limits and Recommendations on Synchronization Within the Same Instance
Synchronization within the same instance is a more complex scenario. It is recommended to create only one same-instance synchronization task on a single database instance, or ensure that multiple tasks have no overlapping synchronization objects to prevent unexpected data synchronization results.
The use limits of the same-instance synchronization scenario include, but are not limited to the following:
1. Renaming requirements
When databases and tables are synchronized within the same instance, it is necessary to rename the database and table names to avoid binlog loops and prevent DTS task failures or data exceptions. Therefore, for full database synchronization, the database name must be changed; for single table synchronization, only the table name needs to be renamed; full instance synchronization is not supported (as it does not meet the renaming requirements).
When a single table is being synchronized within the same instance, operations on the database must be prohibited. For example, when db1.t1 is being synchronized to db1.t2, CREATE/DROP/ALTER DATABASE operations on the source database (db1) are prohibited to prevent DDL loops that may cause DTS task failures.
2. Synchronization within the same instance does not support cascaded and bidirectional synchronization.
Cascaded or bidirectional synchronization tasks within the same instance are not supported. Examples include: multiple cascaded tasks (for example, configuring two tasks on the same database instance in which Task 1 synchronizes table a to table b, and Task 2 synchronizes table b to table c, attempting to achieve cascaded synchronization from table a to table b to table c); or bidirectional synchronization (for example, configuring two tasks in which Task 1 synchronizes table a to table b, and Task 2 synchronizes table b to table a, attempting to achieve bidirectional synchronization between table a and table b).
Cascaded synchronization cannot be configured within the same-instance synchronization tasks. For example, it is not supported to configure a task to synchronize table a to table b and another task to synchronize table b to table c, in an attempt to form a cascade from table a to table b to table c.
3. Synchronization within the same instance does not support complex topologies.
When the same-instance synchronization is integrated with other upstream and downstream DTS tasks to form a more complex topology, the data flow becomes increasingly intricate and may fail to meet expectations. This approach is not recommended.
Supported SQL Operations for Synchronization
|
| INSERT, UPDATE, and DELETE |
| DROP DATABASE, ALTER DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE, RENAME TABLE, CREATE VIEW, DROP VIEW, CREATE INDEX, and DROP INDEX |
Note:
The data synchronization feature currently does not support synchronization of an entire instance, nor does it support the CREATE DATABASE statement.
Environment Requirements
|
Source database requirements | The networks between the source and target databases can be connected. Instance parameter requirements: The server_id parameter of the source database needs to be manually set and cannot be set to 0. The row_format of source database tables cannot be set to FIXED. The lower_case_table_names variable must be set to the same value for both the source and target databases. The connect_timeout variable for the source database must be set to a value greater than 10. Binlog parameter requirements: The log_bin variable on the source must be set to ON. The binlog_format variable on the source must be set to ROW. The binlog_row_image variable on the source must be set to FULL. When the gtid_mode variable is not set to ON in MySQL 5.6 or later, a warning will be triggered. It is recommended to enable gtid_mode. Setting do_db and ignore_db is not allowed. The log_slave_updates variable must be set to ON when the source instance is a secondary database. It is recommended to retain binlogs in the source database for at least 3 days. Otherwise, if the task is paused or interrupted for a period longer than the binlog retention period, the task may fail due to the inability to resume. Foreign key dependencies: Foreign key dependencies can only be set to NO ACTION or RESTRICT. During synchronization of specific databases or tables, tables with foreign key dependencies must be fully included. The environment variable innodb_stats_on_metadata must be set to OFF. |
Target database requirements | The version of the target database must be greater than or equal to that of the source database. The target database must have sufficient storage space. If full data initialization is selected as the initial type, the target database storage capacity must be at least 1.2 times the space required by the source databases and tables to be synchronized. The target database cannot contain synchronization objects such as tables and views with the same names as those in the source database. The max_allowed_packet parameter for the target database must be set to at least 4M. |
Operation Steps