How to Stably and Efficiently Import and Export TiDB Data?

For seasoned DBAs who have been in the database industry for years, TiDB is by no means unfamiliar. As a truly open-source distributed database independently developed by PingCAP, its advanced design concepts and rich ecosystem of tools can be considered a synonym for industry innovation and performance leadership.

Who is TiDB?

Obviously, this section seems a bit redundant here, but we also want to provide a popular science explanation for those who are not familiar with TiDB. TiDB is an open-source distributed relational database management system developed by PingCAP, aimed at solving the limitations of traditional relational databases in terms of scalability and flexibility. It has many advantages:

• TiDB is an open-source product: TiDB has a quite mature community and a considerable number of code contributors that are continuously growing, which makes the maturity of TiDB increasingly high.
• Strong scalability: TiDB is a distributed database with very good read and write scalability, especially write scalability, which can store massive amounts of data, easily storing hundreds of TB of data.
• Storage and computing separation architecture: Allows users to scale computing resources or storage resources separately, avoiding the waste of resources on one side.
• HTAP capabilities: The integration of OLTP and OLAP capabilities provides HTAP hybrid load capabilities, giving TiDB strong business processing and data querying capabilities.
• Online scaling and upgrading: Under the storage and computing separation architecture of TiDB, database scaling and upgrading no longer need to be stopped, which is particularly important for long-term online business systems.

The drawbacks of TiDB's supporting tools?

TiDB is very strong, and in terms of supporting tools, TiDB also provides a variety of functions, such as Dumpling, Lightning, Data Migration (DM), Backup & Restore, TiCDC, etc. These tools each have different usage scenarios and limitations. To master these tools, a lot of time needs to be spent on learning.

However, even if you can use these tools very skillfully, you may encounter some problems. For example, in terms of data import and export of TiDB, the Dumpling tool is used to export data from the TiDB database, and Lightning is used for data import of TiDB. These two tools have the following limitations:

• When exporting a larger single table (over 1 TB) with the Dumpling tool, it may cause TiDB memory overflow (OOM) due to the large amount of data.

• The Lightning tool requires the use of tools such as tikv-importer, which is complex to operate and difficult to get started, and the TiDB cluster cannot provide normal services to the outside after the tool is running.

• The most important point is that using data import and export tools (including the above two tools) will have a negative impact on the database's own business read and write, reducing the performance of the database.

Overall, using these tools more or less requires the database to cooperate, which will have a great impact on the availability of the business, which is a bit contrary to the focus of enterprises. For enterprises, the most important thing I care about is the stability of the database. You cannot have any impact on my business, and then I also need to be efficient and easy to operate. Is there a way?

NineData's TiDB Import and Export Solution

In the latest version released by NineData, support for TiDB data import and export is provided. Thanks to the natural independence of NineData, when importing and exporting data to TiDB, there is no longer a need for TiDB databases to make all kinds of compromises. Let TiDB do what it should do, and leave the rest to NineData.

In terms of functionality, the import function supports SQL, CSV, EXCEL and other file formats, and also supports a variety of custom configurations, such as customizing which column of which table to import to, the execution strategy when encountering name conflicts, etc.; the export function supports exporting through SQL statements or directly selecting the target database table, and also supports data, structure, structure + data in three forms, and supports exporting to SQL, CSV, EXCEL three file types, in addition to this, there are a variety of advanced settings (large field export, SQL script expansion, triggers | functions | views | stored procedures | event export, etc.) to choose from.

In terms of operation, the page guides the process in a foolproof manner throughout, and as long as there is no language barrier, it can be easily played.

In terms of security, when non-administrator users perform import and export, the system automatically generates an approval process, and only after the administrator approves can it actually be executed on the TiDB data source.

Functional Practical Demonstration

Data Export

1. Log in to the NineData console, click on Database DevOps > Data Export, and then click on Create Data Export on the page. Configure the export task according to the prompts on the following page, and then click on Create Data Export.

   

2. After the task is completed, you can click on Download on the task details page to download the exported file to the local area. This file can be used for the following Data Import task.

   

   Data Import

   1. Log in to the NineData console, click on Database DevOps > Data Import, and then click on Create Data Import on the page. Configure the import task according to the prompts on the following page, and then click on Create Data Import.

      

   2. Wait for the task to be completed.

      

Summary

In this functional verification process, it can be found that NineData provides good support for the data import and export functions of TiDB, and perfectly avoids the performance impact on the business database brought by the official supporting tools, truly achieving the stability, efficiency, simplicity, and security that enterprises care about.