MySQL-DTS: One-Stop MySQL/TiDB Sync with Online DDL Compatibility

Product Overview

In enterprise database operations, data migration, continuous synchronization, and disaster-recovery preparation are frequent and high-risk tasks. Traditional open-source solutions often require stitching together full-migration tools, Binlog consumers, and orchestration systems, with extensive customization for multi-database sync, online DDL, large tables, and wide-column tables.

To lower the deployment and usage barrier, the authors developed MySQL-DTS, a self-contained data synchronization tool that connects directly to source and target endpoints without external message queues or sync components. It automatically handles schema synchronization, full data migration, and continuous incremental sync. The tool runs stably in production environments; the source endpoint supports MySQL, while the target endpoint supports both MySQL and TiDB.

Core Capabilities

1. Three Sync Modes Covering Different Scenarios

MySQL-DTS offers three working modes that users can select based on business goals:

Schema Sync : Synchronizes only table structures, suitable for pre-creating tables, initializing test environments, and aligning database schemas; exits automatically after completion.

Full Sync : Automatically synchronizes table structures and existing data, suitable for one-time data relocation, instance migration, and disaster-recovery initialization; exits automatically after completion.

Incremental Sync : Sequentially completes schema sync, full data sync, and continuous incremental sync, suitable for near-real-time synchronization, online migration cutover, and disaster-recovery data preparation; the task runs persistently.

The incremental mode automatically executes the full pipeline "schema sync → full sync → incremental sync" without requiring multiple task submissions.

2. Full Sync: Balancing Speed and Stability

Full sync must solve two problems: quickly moving existing data and establishing an accurate starting point for subsequent incremental sync. MySQL-DTS obtains a consistent snapshot for the full data view while recording the corresponding Binlog position, enabling seamless continuity between full data and later incremental changes, reducing risk of data loss or duplicate replay.

Multi-table Concurrency : Syncs multiple tables simultaneously, fully utilizing source, target, and network I/O throughput.

Single-table Shard Concurrency : For tables with numeric primary keys, splits by primary-key range into multiple chunks for parallel read/write.

Global Concurrency Control : Limits overall resource consumption via a maximum concurrent chunk count.

Smart Pagination Strategy : Numeric primary keys use range-based chunking; non-numeric keys automatically use keyset cursor pagination.

Large-field Table Protection : Automatically detects tables with TEXT, BLOB, JSON, etc., to reduce memory footprint and avoid size-limit errors.

3. Incremental Sync: Continuous Change Replay

After full sync, MySQL-DTS continuously consumes ROW-format Binlog from the source via the MySQL replication protocol, replaying INSERT, UPDATE, DELETE, and DDL events to the target according to a whitelist rule.

Dual Position Modes : Supports both GTID and traditional file+pos, covering MySQL 5.5 and above.

Table-level Ordered Scheduling : Events for the same table are routed to a fixed worker, guaranteeing intra-table change order.

Batch Commit : Workers aggregate changes by row-count threshold or wait time and commit in batches.

Checkpoint Resume : Confirmed incremental positions are persisted; on restart the task continues from the safe checkpoint.

4. Continuous Operation and Failure Recovery

Incremental tasks may run for days, months, or longer. MySQL-DTS provides a complete runtime guarantee around position safety, connection recovery, and error handling:

Safe Position Advancement : Uses the low-water mark across all concurrent workers as the recoverable position.

GTID Auto-reconnect : On transient network interruption or primary failover, reconnects and resumes consumption from the safe position.

File+pos Conservative Handling : On connection loss, exits conservatively and resumes only after confirming source instance status.

Error Classification : Retries transient errors; terminates immediately on unrecoverable errors with clear diagnostics.

Monitoring & Alerting : Full sync shows migration progress; incremental sync includes alerting mechanisms.

5. DDL Sync and Online Schema Change Compatibility

During the incremental phase, MySQL-DTS syncs DDL by default and adds compatibility for common online schema change tools in production:

pt-online-schema-change Compatibility : Automatically identifies temporary triggers, shadow tables, and table-swap operations, filtering out irrelevant temporary objects.

gh-ost Compatibility : Recognizes control tables, shadow tables, and multi-table RENAME workflows, ensuring online DDL and incremental data stay in order.

TiDB DDL Compatibility : Automatically handles DDL syntax and execution differences between MySQL and TiDB to maintain structural consistency.

6. Multi-database Sync and Database Renaming

For MySQL instances containing multiple business databases, MySQL-DTS can synchronize all of them in a single task without launching separate processes per database.

A single task syncs multiple databases from the same source instance, including all business tables in each.

Supports source-to-target database rename mapping (e.g., source db1 → target aaa).

Multiple databases share one Binlog consumption pipeline and sync position, reducing duplicate tasks and connection overhead.

Built-in system-database blacklist prevents accidental sync of system tables.

7. Pre-flight Checks and Safety Guards

To expose risks before sync starts, MySQL-DTS runs multiple checks at task launch:

Foreign Key Check : Detects foreign key constraints on source tables to avoid sync failures due to dependency order during concurrent writes.

Identifier Compliance Check : Validates that database, table, and column names are legal on the target.

Stored Procedure/Trigger Check : By default halts the task if such objects are found; user can explicitly enable after confirmation.

Target Protection : If a same-named table exists on the target, the task errors out instead of silently dropping or overwriting.

Version Check : Requires target MySQL version ≥ source MySQL version.

Typical Use Cases

MySQL-DTS applies to database version upgrades, heterogeneous database migration, data replica synchronization, and on-premises-to-cloud migration:

MySQL Version Upgrade : Create a higher-version target instance and migrate data via DTS.

MySQL to TiDB Migration : Handles DDL syntax and execution differences between the two databases.

MySQL Data Backup : Use same instance as source and target, syncing data to another database on the same instance.

On-premises to Zhihui Cloud Migration : Migrate MySQL from local data centers or VMs to the Zhihui Cloud platform.

Advantages Over Traditional Sync Solutions

Traditional approaches typically combine full-migration tools, Binlog consumers, and orchestration systems, leaving users to manage position handoffs, failure recovery, and runtime operations. MySQL-DTS integrates these capabilities into a single task lifecycle:

One Task Spans the Full Pipeline : Automatically executes schema sync, full migration, and continuous incremental sync.

Designed for Long-running Operation : Safe checkpoints, GTID auto-reconnect, and file+pos conservative exit strategy.

Adapts to Complex Production Environments : Supports multi-database mapping, large-table sharding, large-field protection, and pt-osc/gh-ost online DDL.

Configurable and Constrained Resource Consumption : Table concurrency, chunk concurrency, and write batch size are all adjustable.

Direct and Transparent Sync Link : Data flows directly from source to target without external platforms or message queue intermediaries.

Summary and Outlook

MySQL-DTS covers the complete MySQL data synchronization lifecycle, providing unified sync capabilities for one-time data relocation, online migration, TiDB migration, and disaster-recovery preparation. Its core value lies in:

Reliability : Consistent snapshots, safe positions, checkpoint resume, and error classification reduce data risk during migration and long-term sync.

Controllability : Large-field protection, concurrency degrees, chunk sizes, and write batch policies are tunable per business environment.

Compatibility : Supports GTID and file+pos across MySQL 5.5, 5.6, 8.0; supports TiDB targets; compatible with pt-osc and gh-ost online schema changes.

Future plans include enhancing monitoring and web-based management, and extending support to heterogeneous targets such as PostgreSQL, making data migration and continuous sync simpler, more transparent, and more controllable.