How to Synchronize MySQL Data to StarRocks in Real-Time and Efficiently?

Apache StarRocks is a high-performance, real-time analytical database based on the MPP architecture, known for its ultra-fast and easy-to-use features. It has now become a popular data warehouse product in China, in addition to ClickHouse and Doris.

NineData provides end-to-end data replication capabilities to help enterprises quickly achieve real-time replication of MySQL to StarRocks, enhancing the value of data applications.

Why Choose StarRocks?

Let's list some of the more prominent capabilities of StarRocks:

• Ultra-Fast Querying: StarRocks employs a comprehensive vectorized engine, equipped with a newly designed CBO (Cost Based Optimizer) optimizer, which far surpasses similar products in query speed (especially for multi-table join queries).
• Real-Time Data Analysis: StarRocks can effectively support real-time data analysis and achieve efficient querying of real-time updated data.
• Easy Deployment and Maintenance: StarRocks clusters do not rely on any other components, making them easy to deploy and maintain. The minimalist architecture design reduces system complexity and maintenance costs, while also enhancing system reliability and scalability.

Through StarRocks, enterprises can build the following systems:

• Reporting and Analysis System: StarRocks' query performance is superior to similar products, enabling enterprises to analyze massive order data in real-time, quickly grasp sales conditions, user behavior, and other information to support decision-making.
• Enterprise Data Warehouse: StarRocks can unify the management of data lakes and data warehouses, placing high concurrency and real-time requirements for business analysis in StarRocks, and also using External Catalog and external tables for analysis on data lakes.
• Ad-Hoc Query Analysis: Based on StarRocks' vectorized querying and CBO optimizer technology, it can generate an optimal query plan for complex queries, adapting to SQL of any complexity.

MySQL, due to its extensive application scenarios and scale, has become the core data source for StarRocks data integration. However, as a data warehouse, if enterprises want to replicate various data from MySQL, it must go through ETL processing before it can be warehoused for data analysis, and this is not a simple process.

How to Synchronize MySQL Data to StarRocks in Real-Time?

As we all know, synchronizing data between two completely unrelated data management systems, especially in real-time, has several issues that must be addressed:

• Table Initialization: Before synchronizing data, it is necessary to manually create tables with structures consistent with the source on the StarRocks side, and then it can receive data from the source. When there are a large number of tables and complex table structures on the source side, the workload is huge and prone to errors.
• Data Structure Mapping: How to ensure the integrity of the data after synchronization from the source? Unless you have an extremely thorough understanding of the data models of both and ensure absolute accuracy, you can achieve the desired result.
• Real-Time Synchronization Rate: The premise of real-time data analysis through StarRocks is that the data in StarRocks must be the most up-to-date, i.e., completely consistent with the source MySQL, for the analysis results to be meaningful. Ensuring this is not an easy task.
• Source Side DDL Statement Linkage: How to ensure that the synchronization link is not interrupted when the data structure on the source side changes? It is necessary to capture the DDL changes of the source MySQL in real-time and synchronize them in the target StarRocks in a timely manner.

If these issues can be resolved smoothly, the real-time synchronization work will be very smooth. So how to solve it? There are definitely methods. For the above 4 issues, NineData has absolute authority, let's first look at its features:

• Structure Replication: Based on the characteristics of the target data source, automatically and efficiently complete table creation, data structure mapping, and other work.
• Replication Performance: Based on dynamic batch accumulation, parallel replication, Stream Load, and other technologies, the replication performance can easily reach 200 MB/S.
• DDL Capture and Execution: Real-time detection of DDL operations on the source side and synchronization in the target StarRocks to ensure that other business changes can proceed stably.

Let's see how to operate.

Step One: Enter Source and Target Data Sources

1. 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.

   

2. Configure according to the page prompts, and then click Create Data Source to complete the creation.

   

Step Two: Configure Synchronization Links

1. Log in to the NineData console, click on Data Replication > Data Replication, and then click on Create Replication.

   

2. Configure the replication task according to the page prompts. Since we want to achieve long-term real-time data synchronization, we need to check Incremental Replication in the Replication Type.

   

3. 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, and then synchronize the incremental data newly added in MySQL in real-time. All newly written data will be synchronized to StarRocks without omission. Whenever the incremental data on the target side catches up with the source side, the task panel will display Delay 0 seconds, indicating that the data in StarRocks is the most up-to-date.

   

Step Three (Optional): Verify the Integrity of Synchronized Data on the Target Side

In addition to the synchronization function, NineData also provides a comparison function for the source and target synchronized data after synchronization to ensure the integrity of the data on the target side.

1. Log in to the NineData console, click on Data Replication > Data Replication, and then click on the replication task ID created in Step Two.

   

2. Click on the Data Comparison tab to display the comparison results (if Enable Data Consistency Comparison is not checked in the task configuration of Step Two, you need to click Enable Data Comparison here).

   

3. You can click Re-compare on the page after a while to verify the synchronization results of the latest incremental data.

   

Step Four (Optional): Configure Task Exception Alerts

Since it is a long-term task, you may need the system to monitor the task status in real-time and notify you immediately when there is an exception.

1. Log in to the NineData console, click on Data Replication > Data Replication, and then click on the replication task ID created in Step Two.

   

2. Click on Configure Alerts in the upper right corner.

   

3. Enter the Policy Name and click Save Configuration. You can directly use the built-in default rules to send a text message reminder 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.

   

Summary

So far, you have achieved a long-term stable real-time synchronization link from MySQL to StarRocks through the NineData data replication product, without having to consider any issues encountered during the synchronization process, making it easy to use the high-performance, real-time data warehouse StarRocks as a platform for enterprise data analysis.