- Release Notes and Announcements
- Product Introduction
- Purchase Guide
- Getting Started
- Preparations
- Business Evaluation
- Network Preparation
- DTS Service Permission Preparation
- Database and Permission Preparation
- Configuring Binlog in Self-Built MySQL
- Data Migration
- Databases Supported by Data Migration
- Cross-Account TencentDB Instance Migration
- Migration to MySQL Series
- Migrating to MySQL
- Migrating to MariaDB
- Migrating to TDSQL-C for MySQL
- Migrating to TDSQL MySQL
- Migrating to TDSQL TDStore
- Advanced Operations for MySQL Series Migration
- Processing for MySQL Series Pre-Check Failure
- Database Connection Check
- Peripheral Check
- Version Check
- Source Instance Permission Check
- Partial Database Parameter Check
- Target Instance Permission Check
- Target Database Content Conflict Check
- Target Database Space Check
- Binlog Parameter Check
- Foreign Key Dependency Check
- View Check
- Advanced Object Check
- Warning Item Check
- TDSQL Proxy Check
- TDSQL Kernel Check
- DDL Ring Linkage Synchronization Detection for Same Database and Table Objects
- DDL Synchronization Conflict Detection for the Same Target and Same Database and Table Object
- Offline Migration of MySQL Data
- Migrating to PostgreSQL
- Migration from PostgreSQL to TencentDB for PostgreSQL
- Migration from TDSQL-C PostgreSQL to TencentDB for PostgreSQL
- PostgreSQL Physical Migration
- Processing for PostgreSQL Series Pre-Check Failure
- Database Connection Check
- Object Dependency Check
- Version Check
- Source or Target Instance Permission Check
- Incremental Migration Pre Check
- Structural Compatibility Check
- Structural Conflict Check
- Account Conflict Check
- Parameter Configuration Conflict Check
- Plugin Compatibility Check
- Multi Task Conflict Detection
- Long Transaction Check
- Migrating to MongoDB
- Migration from MongoDB to MongoDB
- Fix for Verification Failure
- MongoDB Connection Check
- Database/Table Content Conflict Check
- Source Database Node Role Check
- Oplog Check
- Source/Target Database Account Permission Check
- Database Version Check
- Database Capacity Check
- Target Database Load Check
- ShardKey Check
- Source Database Balancer Check
- Time Series Collection Verification
- Compression Algorithm Verification
- MongoDB Data Consistency Verification
- Migrating to SQL Server
- Migrating to TencentDB for Redis
- Migrating to KeeWiDB
- Task Management
- Task Status Description
- Viewing Task
- Configuring Task
- Verifying Task
- Start Task
- Retrying Task (MySQL)
- Retrying Task (Redis)
- Speed Limit (MySQL)
- Speed Limit (Redis)
- Complete Task
- Terminating Task
- Deleting Task
- Pausing Task
- Resuming Task
- Changing Configuration
- Terminating Task
- Deisolating Task
- Deleting Task
- Managing Tag
- Creating a Similar Task
- Viewing Log
- Data Sync
- Databases Supported by Data Sync
- Cross-Account TencentDB Instance Sync
- Sync to MySQL series
- Sync to MySQL
- Sync to MariaDB
- Sync to TDSQL-C for MySQL
- Sync to TDSQL for MySQL
- Sync to TDSQL TDStore
- MySQL Series Advanced Sync Operations
- MySQL series Pre Validation Failed Processing
- Database Connection Check
- Peripheral Check
- Version Check
- Source Instance Permission Check
- Partial Database Parameter Check
- Target Database Content Conflict Check
- Target Database Space Check
- Binlog Parameter Check
- Foreign Key Dependency Check
- View Check
- Advanced Object Check
- Warning Item Check
- TDSQL Proxy Check
- TDSQL kernel check
- DLL Ring Sync Check for Single Database/Table Object
- DLL Sync Conflict Check for Single Database/Table Object in the Same Target
- Synchronize to PostgreSQL
- Synchronization from PostgreSQL to PostgreSQL
- PostgreSQL Pre Validation does not Pass Processing
- Database Connection Check
- Object Dependency Check
- Version Check
- Source or Target Instance Permission Check
- Incremental Migration Pre Check
- Structural Compatibility Check
- Structural Conflict Check
- Account Conflict Check
- Parameter Configuration Conflict Check
- Plugin Compatibility Check
- Primary Key Dependency Check
- Check the Key Parameters of the Instance
- Multi Task Conflict Detection
- Synchronize to Kafka
- Task Management
- Task Status Description
- Viewing Task
- Configuring Task
- Verifying Task
- Starting Task
- Retrying Task (MySQL)
- Speed Limit
- Stopping Task
- Modify Sync configuration
- Pausing Task
- Resuming Task
- Resetting Task
- Deleting Task
- Changing Configuration
- Pay-as-You-Go to Monthly Subscription
- Terminating/Returning Instance
- Renewing Task
- Deisolating Task
- Deleting Instance
- Creating a Similar Task
- Viewing Log
- Deleting Task
- Data Subscription (Kafka Edition)
- Databases Supported by Data Subscription
- MySQL series Data Subscription
- Creating MySQL or TDSQL for MySQL Data Subscription
- Creating TDSQL for MySQL Data Subscription
- Creating MariaDB/Percona Data Subscription
- Consuming MySQL Data
- Advanced Subscription Operations
- Fix for Verification Failure
- Data Subscription for TDSQL PostgreSQL
- MongoDB Data Subscription
- Task Management
- Consumption Management
- Fix for Verification Failure
- Cutover Description
- Monitoring and Alarms
- Ops Management
- Practical Tutorial
- Synchronizing Local Database to the Cloud
- Creating Two-Way Sync Data Structure
- Creating Many-to-One Sync Data Structure
- Creating Multi-Site Active-Active IDC Architecture
- Selecting Data Sync Conflict Resolution Policy
- Using CLB as Proxy for Cross-Account Database Migration
- Migrating Self-Built Databases to Tencent Cloud Databases via CCN
- Best Practices for DTS Performance Tuning
- FAQs
- Error Handling
- API Documentation
- History
- Introduction
- API Category
- Making API Requests
- (NewDTS) Data Migration APIs
- DescribeMigrationJobs
- ModifyMigrationJob
- CompleteMigrateJob
- DescribeMigrationDetail
- CreateMigrationService
- CreateMigrateCheckJob
- DescribeMigrationCheckJob
- SkipCheckItem
- StartMigrateJob
- StopMigrateJob
- ResumeMigrateJob
- RecoverMigrateJob
- ModifyMigrateName
- ModifyMigrateJobSpec
- IsolateMigrateJob
- DestroyMigrateJob
- DescribeMigrateDBInstances
- PauseMigrateJob
- ContinueMigrateJob
- Data Sync APIs
- DescribeSyncJobs
- ModifySyncJobConfig
- CreateModifyCheckSyncJob
- DescribeModifyCheckSyncJobResult
- StartModifySyncJob
- CreateSyncJob
- ConfigureSyncJob
- CreateCheckSyncJob
- SkipSyncCheckItem
- DescribeCheckSyncJobResult
- StartSyncJob
- StopSyncJob
- ResumeSyncJob
- ResizeSyncJob
- RecoverSyncJob
- IsolateSyncJob
- DestroySyncJob
- PauseSyncJob
- ContinueSyncJob
- Data Consistency Check APIs
- Data Types
- Error Codes
- DTS API 2018-03-30
- Service Agreement
- Release Notes and Announcements
- Product Introduction
- Purchase Guide
- Getting Started
- Preparations
- Business Evaluation
- Network Preparation
- DTS Service Permission Preparation
- Database and Permission Preparation
- Configuring Binlog in Self-Built MySQL
- Data Migration
- Databases Supported by Data Migration
- Cross-Account TencentDB Instance Migration
- Migration to MySQL Series
- Migrating to MySQL
- Migrating to MariaDB
- Migrating to TDSQL-C for MySQL
- Migrating to TDSQL MySQL
- Migrating to TDSQL TDStore
- Advanced Operations for MySQL Series Migration
- Processing for MySQL Series Pre-Check Failure
- Database Connection Check
- Peripheral Check
- Version Check
- Source Instance Permission Check
- Partial Database Parameter Check
- Target Instance Permission Check
- Target Database Content Conflict Check
- Target Database Space Check
- Binlog Parameter Check
- Foreign Key Dependency Check
- View Check
- Advanced Object Check
- Warning Item Check
- TDSQL Proxy Check
- TDSQL Kernel Check
- DDL Ring Linkage Synchronization Detection for Same Database and Table Objects
- DDL Synchronization Conflict Detection for the Same Target and Same Database and Table Object
- Offline Migration of MySQL Data
- Migrating to PostgreSQL
- Migration from PostgreSQL to TencentDB for PostgreSQL
- Migration from TDSQL-C PostgreSQL to TencentDB for PostgreSQL
- PostgreSQL Physical Migration
- Processing for PostgreSQL Series Pre-Check Failure
- Database Connection Check
- Object Dependency Check
- Version Check
- Source or Target Instance Permission Check
- Incremental Migration Pre Check
- Structural Compatibility Check
- Structural Conflict Check
- Account Conflict Check
- Parameter Configuration Conflict Check
- Plugin Compatibility Check
- Multi Task Conflict Detection
- Long Transaction Check
- Migrating to MongoDB
- Migration from MongoDB to MongoDB
- Fix for Verification Failure
- MongoDB Connection Check
- Database/Table Content Conflict Check
- Source Database Node Role Check
- Oplog Check
- Source/Target Database Account Permission Check
- Database Version Check
- Database Capacity Check
- Target Database Load Check
- ShardKey Check
- Source Database Balancer Check
- Time Series Collection Verification
- Compression Algorithm Verification
- MongoDB Data Consistency Verification
- Migrating to SQL Server
- Migrating to TencentDB for Redis
- Migrating to KeeWiDB
- Task Management
- Task Status Description
- Viewing Task
- Configuring Task
- Verifying Task
- Start Task
- Retrying Task (MySQL)
- Retrying Task (Redis)
- Speed Limit (MySQL)
- Speed Limit (Redis)
- Complete Task
- Terminating Task
- Deleting Task
- Pausing Task
- Resuming Task
- Changing Configuration
- Terminating Task
- Deisolating Task
- Deleting Task
- Managing Tag
- Creating a Similar Task
- Viewing Log
- Data Sync
- Databases Supported by Data Sync
- Cross-Account TencentDB Instance Sync
- Sync to MySQL series
- Sync to MySQL
- Sync to MariaDB
- Sync to TDSQL-C for MySQL
- Sync to TDSQL for MySQL
- Sync to TDSQL TDStore
- MySQL Series Advanced Sync Operations
- MySQL series Pre Validation Failed Processing
- Database Connection Check
- Peripheral Check
- Version Check
- Source Instance Permission Check
- Partial Database Parameter Check
- Target Database Content Conflict Check
- Target Database Space Check
- Binlog Parameter Check
- Foreign Key Dependency Check
- View Check
- Advanced Object Check
- Warning Item Check
- TDSQL Proxy Check
- TDSQL kernel check
- DLL Ring Sync Check for Single Database/Table Object
- DLL Sync Conflict Check for Single Database/Table Object in the Same Target
- Synchronize to PostgreSQL
- Synchronization from PostgreSQL to PostgreSQL
- PostgreSQL Pre Validation does not Pass Processing
- Database Connection Check
- Object Dependency Check
- Version Check
- Source or Target Instance Permission Check
- Incremental Migration Pre Check
- Structural Compatibility Check
- Structural Conflict Check
- Account Conflict Check
- Parameter Configuration Conflict Check
- Plugin Compatibility Check
- Primary Key Dependency Check
- Check the Key Parameters of the Instance
- Multi Task Conflict Detection
- Synchronize to Kafka
- Task Management
- Task Status Description
- Viewing Task
- Configuring Task
- Verifying Task
- Starting Task
- Retrying Task (MySQL)
- Speed Limit
- Stopping Task
- Modify Sync configuration
- Pausing Task
- Resuming Task
- Resetting Task
- Deleting Task
- Changing Configuration
- Pay-as-You-Go to Monthly Subscription
- Terminating/Returning Instance
- Renewing Task
- Deisolating Task
- Deleting Instance
- Creating a Similar Task
- Viewing Log
- Deleting Task
- Data Subscription (Kafka Edition)
- Databases Supported by Data Subscription
- MySQL series Data Subscription
- Creating MySQL or TDSQL for MySQL Data Subscription
- Creating TDSQL for MySQL Data Subscription
- Creating MariaDB/Percona Data Subscription
- Consuming MySQL Data
- Advanced Subscription Operations
- Fix for Verification Failure
- Data Subscription for TDSQL PostgreSQL
- MongoDB Data Subscription
- Task Management
- Consumption Management
- Fix for Verification Failure
- Cutover Description
- Monitoring and Alarms
- Ops Management
- Practical Tutorial
- Synchronizing Local Database to the Cloud
- Creating Two-Way Sync Data Structure
- Creating Many-to-One Sync Data Structure
- Creating Multi-Site Active-Active IDC Architecture
- Selecting Data Sync Conflict Resolution Policy
- Using CLB as Proxy for Cross-Account Database Migration
- Migrating Self-Built Databases to Tencent Cloud Databases via CCN
- Best Practices for DTS Performance Tuning
- FAQs
- Error Handling
- API Documentation
- History
- Introduction
- API Category
- Making API Requests
- (NewDTS) Data Migration APIs
- DescribeMigrationJobs
- ModifyMigrationJob
- CompleteMigrateJob
- DescribeMigrationDetail
- CreateMigrationService
- CreateMigrateCheckJob
- DescribeMigrationCheckJob
- SkipCheckItem
- StartMigrateJob
- StopMigrateJob
- ResumeMigrateJob
- RecoverMigrateJob
- ModifyMigrateName
- ModifyMigrateJobSpec
- IsolateMigrateJob
- DestroyMigrateJob
- DescribeMigrateDBInstances
- PauseMigrateJob
- ContinueMigrateJob
- Data Sync APIs
- DescribeSyncJobs
- ModifySyncJobConfig
- CreateModifyCheckSyncJob
- DescribeModifyCheckSyncJobResult
- StartModifySyncJob
- CreateSyncJob
- ConfigureSyncJob
- CreateCheckSyncJob
- SkipSyncCheckItem
- DescribeCheckSyncJobResult
- StartSyncJob
- StopSyncJob
- ResumeSyncJob
- ResizeSyncJob
- RecoverSyncJob
- IsolateSyncJob
- DestroySyncJob
- PauseSyncJob
- ContinueSyncJob
- Data Consistency Check APIs
- Data Types
- Error Codes
- DTS API 2018-03-30
- Service Agreement
Sync from MySQL/MariaDB/Percona to TencentDB for MariaDB
Last updated: 2023-03-02 11:38:06
This document describes how to use the data sync feature of DTS to sync data from MySQL/MariaDB/Percona to TencentDB for MariaDB.
The following deployment modes of the source database are supported:
Self-built MySQL and TencentDB for MySQL.
Self-built MariaDB and TencentDB for MariaDB.
Self-built Percona.
This document describes how to sync data from MariaDB to TencentDB for MariaDB. The requirements and steps of data sync from MySQL and Percona to TencentDB for MariaDB are basically the same.
Notes
When DTS performs full data sync, it will occupy some resources in the source instance, which may increase the load of the source instance and the database pressure. If your database has low configurations, we recommend that you sync the data during off-peak hours.
To avoid duplicate data, make sure that the tables to be synced have a primary key or non-null unique key.
Sync is implemented without locks by default, during which no global lock (the FTWRL lock) is added to the source database, and only tables without a primary key are locked.
During data sync, DTS will use the account that executes the sync task to write the system database
__tencentdb__ in the source database to record the data comparison information during the sync task.To ensure that subsequent data problems can be located, the
__tencentdb__ system database in the source database will not be deleted after the sync task ends.The
__tencentdb__ system database uses a single-threaded connection wait mechanism and occupies a very small space, about 0.01%–0.1% of the storage space of the source database; for example, if the source database is 50 GB, __tencentdb__ will be about 5–50 MB. Therefore, it has almost no impact on the performance of the source database and will not preempt resources.Prerequisites
Prerequisites
The source and target databases must meet the requirements for the sync feature and version as instructed in Databases Supported by Data Sync.
Permissions required of the source database:
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;
Permissions required of the target database: 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.
Application restrictions
Only basic tables, views, procedures, and functions can be synced.
When views, procedures, and functions are synced, DTS will check whether
user1 corresponding to DEFINER ([DEFINER = user1]) in the source database is the same as the sync account user2, and if not, DTS will change the SQL SECURITY attribute of user1 in the target database from DEFINER to INVOKER ([INVOKER = user1]) after the sync, and set the DEFINER in the target database to the sync account user2 ([DEFINER = sync account user2]). If the view definition in the source database is too complex, the task may fail.If the source MySQL database is a non-GTID instance, DTS doesn't support HA switch for it. If it is switched, DTS incremental sync may be interrupted.
Only data with the following three database engines can be synced: InnoDB, MyISAM, and TokuDB. Tables with other engines will be skipped during sync by default. If there is compressed TokuDB engine data in the source database, the target database must support the compression mode before sync can be performed; otherwise, the task will report an error.
Correlated data objects must be synced together; otherwise, sync will fail. Common correlations include table referenced by views, view referenced by views, and tables correlated through primary/foreign keys.
During incremental sync, if the source database has distributed transactions or generates binlog statements in the
STATEMENT format, sync will fail.If the source database is Alibaba Cloud ApsaraDB RDS for MySQL, then the tables to be synced on v5.6 must have a primary key, while tables on v5.7 and later are unrestricted. If the source database is Amazon RDS for MySQL, then the tables to be synced must have a primary key.
If the binlog of the source database has a GTID hole, it may compromise the performance of the sync task and cause the task to fail.
Scenarios that contain both DML and DDL statements in the same transaction are not supported and will trigger errors during task execution.
Geometry data types are not supported and will trigger errors during task execution.
The
ALTER VIEW statement is not supported and will be skipped during sync.Operation restrictions
During the sync, do not perform the following operations; otherwise, the sync task will fail:
Do not modify or delete user information (including username, password, and permissions) in the source and target databases and port numbers.
Do not run distributed transactions in the source database.
Do not write binlog data in the
STATEMENT format into the source database.Do not clear binlogs in the source database.
Do not delete the system table
__tencentdb__ during incremental sync.Synchronizable SQL Operations
Operation Type | SQL Statement |
DML | INSERT, UPDATE, DELETE |
DDL | CREATE DATABASE, DROP DATABASE, ALTER DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE, RENAME TABLE, CREATE VIEW, DROP VIEW, CREATE INDEX, DROP INDEX |
Notes:
DDL operations involving partitions cannot be synced.
CREATE TABLE table name AS SELECT is not supported.Environment requirements
Type | Environment Requirement |
Requirements for the source database | The source and target databases can be connected. Requirements for instance parameters: The server_id parameter in the source database must be set manually and cannot be 0.row_format for the source databases/tables cannot be set to FIXED.The values of the lower_case_table_names variable in both the source and target databases must be the same.The connect_timeout variable in the source database must be greater than or equal to 10.Requirements for binlog parameters: The log_bin variable in the source database must be set to ON.The binlog_format variable in the source database must be set to ROW.The binlog_row_image variable in the source database must be set to FULL.On MySQL 5.6 or later, if the gtid_mode variable is not ON, an alarm will be triggered. We recommend you enable gtid_mode.It is not allowed to set do_db and ignore_db.If the source instance is a replica database, the log_slave_updates variable must be set to ON.We recommend you retain the binlog of the source database for at least three days; otherwise, the task cannot be resumed from the checkpoint and will fail if it is suspended or interrupted for longer than the time set for binlog retention. Foreign key dependency: Foreign key dependency can be set to only one of the following two types: NO ACTION and RESTRICT.During partial table sync, tables with foreign key dependency must be migrated. The environment variable innodb_stats_on_metadata must be set to OFF. |
Requirements for the target database | The target database version must be later than or equal to the source database version. The target database must have sufficient storage space. If you select Full data initialization as the initialization type, the target database space must be at least 1.2 times the space of databases/tables to be synced in the source database. The target database cannot have sync objects such as tables and views with the same name as those in the source database. The max_allowed_packet parameter of the target database must be set to 4 MB or above. |
Directions
You can refer to the directions described in Sync from MySQL/MariaDB/Percona to TencentDB for MySQL.
Yes
No
Was this page helpful?