MySQL to TiDB Migration: Step-by-Step Guide for DBAs

Migrating from MySQL to TiDB enables horizontal scalability, strong consistency, and cloud-native operations. This guide provides a comprehensive, practical approach for DBAs to migrate production workloads from MySQL to TiDB with minimal downtime.

Table of Contents

1. Why Migrate to TiDB?
2. Pre-Migration Assessment
3. Schema Conversion
4. Data Migration
5. Incremental Sync and Cutover
6. Post-Migration Validation
7. Common Issues and Solutions
8. Best Practices

Why Migrate to TiDB?

• Horizontal Scalability: Scale out by adding nodes
• MySQL Compatibility: Supports most MySQL syntax and tools
• Cloud-Native: Built for Kubernetes and hybrid cloud
• HTAP: Hybrid transactional/analytical processing
• Strong Consistency: Distributed transactions with Raft

Pre-Migration Assessment

• Inventory: List all databases, tables, and critical applications
• Compatibility Check: Use TiDB Compatibility Checker
• Performance Baseline: Capture current QPS, latency, and slow queries
• Data Volume: Estimate total data size and growth rate
• Downtime Window: Plan for cutover and rollback

-- Check MySQL version and features
SELECT VERSION();
SHOW ENGINES;
SHOW VARIABLES LIKE 'sql_mode';

Schema Conversion

1. Export MySQL Schema

mysqldump -h <mysql_host> -u <user> -p --no-data --routines --triggers --events mydb > mydb_schema.sql

2. Review and Adjust Schema

• Remove unsupported features (e.g., spatial indexes, FULLTEXT on non-TiDB tables)
• Convert ENUM/SET types to VARCHAR if needed
• Replace UNSIGNED with signed types (TiDB does not support unsigned)
• Remove ZEROFILL, AUTO_INCREMENT is supported
• Check for partitioned tables (TiDB supports RANGE/HASH)

-- Example: Convert ENUM to VARCHAR
ALTER TABLE users MODIFY status VARCHAR(20);

-- Remove unsupported index types
DROP INDEX idx_fulltext ON articles;

3. Import Schema to TiDB

mysql -h <tidb_host> -P 4000 -u <user> -p mydb < mydb_schema.sql

Data Migration

1. Full Data Migration with TiDB Data Migration (DM)

• TiDB Data Migration (DM) supports full and incremental sync
• Deploy DM-master and DM-worker nodes

# Example DM task configuration
name: mydb-migration
mode: all
shard-mode: "pessimistic"

mysql-instances:
  - source-id: "mysql-replica-01"
    meta:
      binlog-name: "mysql-bin.000001"
      binlog-pos: 4

target-database:
  host: "tidb-host"
  port: 4000
  user: "tidb_user"
  password: "tidb_pass"

black-white-list:
  bw-list:
    do-dbs: ["mydb"]

# Save as mydb-task.yaml

# Start DM task
./dmctl --master-addr=127.0.0.1:8261 start-task mydb-task.yaml

2. Alternative: Lightning for Large Datasets

• TiDB Lightning for fast import of large data

# Export MySQL data as CSV or SQL
mysqldump -h <mysql_host> -u <user> -p --single-transaction --quick --no-create-info mydb > mydb_data.sql

# Configure Lightning and import
lightning -config tidb-lightning.toml

Incremental Sync and Cutover

• DM supports real-time binlog replication (incremental sync)
• Monitor lag with query-status in dmctl
• Schedule cutover during low-traffic window
• Stop writes to MySQL, wait for DM to catch up
• Point applications to TiDB

# Check DM sync status
./dmctl --master-addr=127.0.0.1:8261 query-status

# Stop MySQL writes (read-only mode)
SET GLOBAL read_only = ON;

# Wait for lag to reach 0, then cutover

Post-Migration Validation

• Data Consistency: Use sync-diff-inspector
• Application Testing: Run integration and performance tests
• Query Plan Review: Use EXPLAIN to compare plans
• Monitor Metrics: QPS, latency, error rates

# Run sync-diff-inspector
sync-diff-inspector --config diff-config.toml

Common Issues and Solutions

Best Practices

• Test migration in staging with production-like data
• Use DM for minimal downtime and incremental sync
• Monitor both source and target during migration
• Document all schema changes and migration steps
• Plan rollback strategy in case of issues
• Engage with PingCAP support for complex migrations

Conclusion

Migrating from MySQL to TiDB unlocks scalability and cloud-native features for modern workloads. With careful planning, the right tools, and thorough validation, you can achieve a seamless migration with minimal risk.

For expert assistance with MySQL to TiDB migrations, our team provides database migration consulting and cloud-native database solutions.

Need help migrating from MySQL to TiDB? Our certified DBAs can help you plan and execute a seamless migration. Contact us for a free consultation.