Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: MYSQL数据迁移到TIDB数据库
|
username: 每天当牛马
Scenario: There are 210,000 tables in MYSQL.
How to quickly migrate MYSQL data to TIDB?
|
username: 每天当牛马
|
username: With such a large amount of data, you might need to consider downtime in addition to speed. I suggest choosing DM.
TiDB Data Migration (DM) is a convenient data migration tool that supports full data migration and incremental data synchronization from databases compatible with the MySQL protocol (MySQL, MariaDB, Aurora MySQL) to TiDB. Using the DM tool helps simplify the data migration process and reduce the operational costs of data migration.
|
username: 
|
username: Your data volume is very small. You can directly use the Navicat tool to export SQL from MySQL, then connect to the TiDB database and execute the SQL.
|
username: Is the data volume large? The table creation speed in TiDB is not fast.
|
username: 
|
username: What is your main issue? Is it that the table structure is too large and TiDB DDL is slow, causing long durations?
If the issue is the slowness of 280,000 DDLs, you can migrate the table structure first and then migrate the data.
Table structure migration:
- TiDB version 7.5 already supports parallel DDL. Refer to: DDL 语句的执行原理及最佳实践 | PingCAP 文档中心
Higher versions of Lightning also create tables in parallel, by default according to region-concurrency (PS: I haven’t tested this, you can test it yourself). If it doesn’t work, split it up and run multiple instances of Lightning. - If it’s still slow, shut down other TiDB Servers, leaving only one, to reduce the time for DDL to sync schema versions to other TiDBs. In my tests, the time for a single create table operation reduced from 0.5 seconds to 0.1 seconds.
This should solve the table creation issue.
Data migration:
Just migrate normally. You didn’t mention a large data volume in your question. 
|
username: 
|
username: There are many tables, and it also depends on the data volume. If the data volume is also large, DM is obviously not suitable. The manual mentions DUMPLING and TIGHTLING.
21 million tables, if it’s a single table, the data is very small, just a few dozen rows.
|
username: First migrate the structure, then you can either write a program yourself or use official tools.
|
username: 
|
username: Are there really that many tables in a MySQL database? When migrating MySQL to TiDB, for optimal performance, some tables still need to be modified.
With so many tables, one significant cost of migration is indeed the efficiency of table creation. This is because DDL operations in TiDB are actually serialized internally, with table creation tasks being executed sequentially by the TiDB DDL owner. Each time the DDL owner creates a table, it triggers a DDL schema version change, and each schema version change needs to be synchronized with other TiDB DDL workers. Therefore, when the number of tables to be created is relatively large, the serialized table creation approach can result in excessively long table creation times.
One low-tech method is to have multiple MySQL clients concurrently execute table creation statements (all directly connected to the DDL owner). This way, more DDLs can enter the queue, saving the time spent waiting in line. To speed up table creation, it is recommended to use 2 clients; using 3 clients doesn’t make much of a difference.
Finally, use the lightning local mode to import the data.
|
username: The data volume is very small, the simplest way is to use Navicat, open multiple windows and batch process quickly.
|
username: Although there are many tables, the data volume is so small that you can directly use the Navicat tool. This avoids a lot of deployment and configuration operations.
|
username: Both DM and Flink CDC can be used, or Alibaba’s DTS and Huawei’s DRS. There are quite a few tools available.
|
username: For full data synchronization with DM, there won’t be any issues at all. It’s the optimal solution.