Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: mysql备份还原tidb
[Test Environment for TiDB]
[TiDB Version] V7.1.2
[Encountered Issue]
An internal business system (intranet) uses a MySQL database. We need to restore this MySQL database to a local test environment on TiDB. The intranet system can only provide a MySQL backup file xxxxx.sql, and TiDB cannot directly connect to the MySQL database. The MySQL backup file is approximately 150GB. How can we efficiently restore such a large MySQL backup file to TiDB? We found that using ordinary database connection tools to restore the .sql file is too slow. Seeking advice from experts.
Use tidb-lightning TiDB Lightning 快速上手 | PingCAP 文档中心
and pay attention to the naming format of the files.
If convenient, you can directly refer to Quick Start and use dumpling to export data from MySQL. This will make the import process smoother~
You can refer to the following article: Migrate Data from SQL Files to TiDB 从 SQL 文件迁移数据到 TiDB | PingCAP 文档中心
TiDB cannot directly connect to the MySQL database. Use Dumpling to export the MySQL data first, and then use Lightning to import it.
For bulk data migration, use Dumpling for export and Lightning for import. Both support multiple concurrent operations and are very fast.
For incremental real-time synchronization, use DM for MySQL to TiDB and TiCDC for TiDB to MySQL. The performance, efficiency, and stability are all very good.
The MySQL backup file should be a logical backup created using mysqldump. You can directly copy the file to the server where TiDB is located and restore it using the command mysql -hxxx -P4000 -uroot -pxxx databasename < xxx.sql. This is how we restore in our testing environment. It is important to ensure that the backup file and the TiDB server are together; if they are not, data loss is likely due to network packet loss, although we did not investigate this issue deeply at the time.
Can you use lightning or this, how many days would it take to restore 150 like this?
You can learn about Alibaba’s tool DataX. Synchronizing from MySQL to MySQL probably also takes a few hours.
Refer to the dumpling export and lightning import tools. Export the physical files from MySQL (tool name unknown) and use lightning for import. The speed should be faster.
If the intranet can only provide .sql files, I understand that these are backup files exported by mysqldump. In this case, you can only import them into a MySQL database in the test environment first, then use dumpling to export in the test environment, and use lighting to import. If dumpling can be deployed offline in the intranet, then you can directly use lighting to import.
Lightning and Dumpling correspond to each other. He probably doesn’t have permission to use Dumpling.
It is recommended to request the source database account and refer to steps 1 and 2 in the following link to export and import the data yourself:
Migrating Data from a Large MySQL Database to TiDB | PingCAP Documentation Center
It is possible to use mysqldump to export data, which indicates that there is permission to access.
You can first use dumpling to export data on the node corresponding to mysqldump, then transfer the compressed data package to the TiDB machine, and then use lightning to import it. This should not be a big problem.
You might encounter a bunch of compatibility errors. The file is too large, making it difficult to pinpoint 
Try using general tools like DataX or CloudCanal.
First, use Dumpling to export the MySQL data.
Then, use Lightning to import it.
Use mysqldump to export directly, then use mysql -u -P4000 to import into TiDB.
For importing, definitely use tidb-lightning. For exporting, it’s best to use dumpling if possible. If not, regular single insert SQL or CSV files can also be recognized. However, if you want to speed up the import process, you can manually split the files into multiple parts after receiving them.
|
username: Dais
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: