How to Migrate from MySQL to TiDB for Seamless Business Switching?
What happens when the single-table data volume of a MySQL database reaches the hundred-million level?
This phenomenon indicates that the company's business has reached a new level. As the data volume increases, the company's scale also expands further, which is a very pleasing change. However, the other changes that come with it are not so welcome.
Firstly, you will find that the query performance of the MySQL database has declined significantly, and the response time for a single query is longer. Although indexes are a bit helpful, the larger the data volume, the larger the indexes will become, and the overhead of looking up indexes is also increasing.
Next, due to the large table size, table structure maintenance operations become very difficult. A DDL operation will take a long time, and the resulting table locking will have a significant impact on the business.
Then, storage space is running out. A single table with a hundred-million data volume is not to be underestimated. Under normal circumstances, the file size can reach hundreds of GB or even several TB.
More importantly, the single-point writing for the database where the large table is located will reach the performance bottleneck of the server. For example, during the peak period of Double 11 on Taobao, there will be millions of orders written per second, which will put a lot of pressure on the CPU, memory, and disk I/O, ultimately leading to slow business response.
Since MySQL mainly relies on vertical scaling to improve performance and storage, you need to keep stacking hardware configurations to meet business needs. With this comes a significant increase in database costs and a meager return that is not proportional to the expenditure.
When faced with this situation, sharding is a good solution. The data of a table is stored in different database instances, which can solve most of the data processing problems, but it introduces new complexity, posing a significant challenge to operations and development.
Is there a low-cost and efficient solution? Of course! TiDB can perfectly solve these problems.
What is 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 very mature community and a considerable number of code contributors that are continuously growing, making TiDB's maturity increasingly higher.
- Compatible with the MySQL ecosystem: TiDB is compatible with the MySQL protocol and most of MySQL's SQL syntax. Applications only need to make a few changes or even no changes to run on TiDB.
- Strong scalability: TiDB is a distributed database with very good read and write scalability, especially write scalability, which can store a large amount of data, easily storing hundreds of TB of data.
- Storage and computing separation architecture: Users can scale computing resources or storage resources separately to avoid 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 TiDB's storage and computing separation architecture, database scaling and upgrading no longer need to be stopped, which is particularly important for long-term online business systems.
These features provide an almost perfect solution for MySQL that is about to burst. We just need to migrate MySQL completely to TiDB. Here we need to use NineData to complete this work. NineData's data replication capability can easily transfer data between homogeneous and heterogeneous data sources, and has the following excellent features:
- Business does not stop during migration: NineData provides structural migration, full data migration, and incremental data migration capabilities. During the database migration process, the source can provide normal services. NineData can automatically complete structural migration, full data migration, and automatically start Binlog's real-time monitoring, collection, parsing, and replication capabilities. The incremental updated data from the source will be replicated in real-time to the target. When NineData enters the incremental data migration phase and the replication is delay-free, the business can perform read-only verification in MySQL and use NineData's data comparison tool for data consistency verification. After the business verification is passed, the business can be shut down and switched, and the entire migration process has a very short business downtime.
- Strong replication performance: During the database migration process, migration speed is undoubtedly an important factor affecting whether the business can be successfully switched. In this process, NineData has deeply optimized the performance of MySQL > TiDB data replication, based on log analysis, intelligent sharding, dynamic batching, data merging, and unique data formats, effectively ensuring the performance of full data replication and incremental data replication. Currently, NineData's full data replication performance is up to 200 GB/hour, and incremental data replication performance is up to 20,000 records/second.
- Comprehensive data quality assurance plan: NineData provides a variety of data consistency verification schemes, including full precision verification, quick verification, and incremental verification, which can strongly verify the consistency of migrated data. At the same time, when data inconsistency occurs, it can provide one-click repair capabilities. Based on NineData's data consistency verification capabilities, it can effectively avoid business migration failures caused by data quality.
Next, I will teach you how to migrate with NineData step by step.
Step 1: Enter Source and Target Data Sources
Log in to the NineData console, click on Data Source Management > Data Sources, and then click on Create Data Source on the page to select the data source you need to enter.
Configure according to the page prompts, and then click Create Data Source to complete the creation.
Step 2: Configure Synchronization Links
Log in to the NineData console, click on Data Replication > Data Replication, and then click on Create Replication.
Configure the replication task according to the page prompts. Since we want to achieve non-stop real-time data migration, we need to check Incremental Replication in the Replication Type.
After the configuration is completed, start the task. For all the synchronization objects you have configured, NineData will first perform a full migration of all the existing data, followed by real-time migration of the incremental data newly added in MySQL. All newly written data will be synchronized to TiDB without omission. Whenever the incremental data on the target side catches up with the source side, and the Latency in the task panel fluctuates between a few milliseconds or seconds, it means that the data in TiDB is currently the latest.
Step 3 (Optional): Verify the Integrity of Synchronized Data on the Target Side
In addition to the synchronization function, NineData also provides a comparison function between the source and target synchronized data after synchronization to ensure the integrity of the data on the target side.
Log in to the NineData console, click on Data Replication > Data Replication, and then click on the replication task ID created in Step 2.
Click on the Data Comparison tab to display the comparison results (if the Enable Data Consistency Comparison is not checked in the task configuration of Step 2, you need to click Enable Data Comparison here).
You can click on Re-compare on the page after a while to verify the synchronization results of the latest incremental data.
Step 4 (Optional): Configure Task Exception Alerts
Due to the large amount of data, you may need the system to monitor the task status in real-time and notify you immediately when there is an exception.
Log in to the NineData console, click on Data Replication > Data Replication, and then click on the replication task ID created in Step 2.
Click on Configure Alerts in the upper right corner.
Enter the Policy Name and click Save Configuration. You can directly use the built-in default rules to send SMS reminders when the task fails or the replication delay is greater than or equal to 10 minutes. You can also create custom rules to notify according to your needs.
Finally
After the migration is completed, a series of tests can be conducted to ensure that the application is fully compatible with TiDB. Due to TiDB's high compatibility with the MySQL protocol, the business can almost seamlessly transition from MySQL to TiDB. However, there is no absolute, and if the test finds that TiDB does not have good compatibility with the application, don't worry. The non-stop migration provided by NineData can ensure business continuity, and the migration process will not have any impact on the business.
As for the cost issue that some students care about, first of all, TiDB is open-source, and NineData's full migration is completely free, and an incremental migration service is also provided for free to achieve the non-stop migration introduced in this article, so the process of migrating from MySQL to TiDB will not incur any costs.