Skip to main content

MySQL Migration Synchronization to StarRocks

NineData data replication supports the synchronization of structure, full data, and incremental data from MySQL to StarRocks data sources.

Feature Introduction​

NineData data replication supports high-performance replication of structure, full data, and incremental data between data sources. For certain data sources, it also provides bidirectional replication, enabling the quick construction of geo-distributed active-active business architectures.

  • Structure: Supports the replication of object structures between homogeneous and heterogeneous data sources, greatly reducing the barrier to data replication between two sources.
  • Full Data: Achieves row-level concurrent batch replication through intelligent data sharding, effectively ensuring replication performance. The independently developed novel breakpoint resume technology ensures the accuracy of data in tables without primary keys.
  • Incremental Data: Supports DML and DDL incremental data replication for all object types. By leveraging technologies such as row-level concurrency and hotspot merging, it delivers robust replication performance.
  • Bidirectional Real-time Data Replication (only between MySQL instances): Direct bidirectional replication of data between multiple nodes ensures that all node data remains up-to-date.

With the above features, it is easy and efficient to achieve scenarios such as full|incremental data replication, full|incremental data migration, full|incremental data synchronization, data integration, and seamless migration without downtime, providing enterprises with flexible and reliable data replication solutions.

Background Information​

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 become a popular data warehouse product in China, in addition to ClickHouse and Doris.

StarRocks is built based on the Doris version and has been developing as an independent branch since 2021. Currently, many enterprises choose to build reporting analysis systems, enterprise data warehouses, and ad-hoc query analysis based on StarRocks. Based on these platform capabilities, applications such as user behavior analysis, AB testing platforms, log retrieval analysis, user profile analysis, and order analysis can be constructed.

As a data warehouse, enterprises need to process various source data through ETL before warehousing for data analysis. Relational databases, due to their extensive application scenarios and scale, have also become the core data source for StarRocks data integration.

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.

Prerequisites​

  • The source and target data sources have been added to NineData. For details on how to add them, please refer to Adding Data Sources.

  • The source database type is MySQL or a MySQL-like database, such as MySQL, MariaDB, PolarDB MySQL, TDSQL-C, GaussDB MySQL, Aurora, etc.

  • The target database is StarRocks 3.1, 3.0, 2.5, 2.4, 2.3, 2.2, 2.1, 2.0.

  • For incremental replication, the source data source must have Binlog enabled, and the Binlog-related parameters are set as follows:

    • binlog_format=ROW
    • binlog_row_image=FULL
    tip

    If the source data source is a replica database, to ensure the acquisition of complete Binlog logs, the log_slave_updates parameter also needs to be enabled.

  • You must have the following permissions for the source and target data sources.

    Replication Type
    		Source Data SourceTarget Data Source
    Full ReplicationSELECTTABLE-related permissions (ALTER, DROP, SELECT, INSERT, UPDATE, DELETE)
    Incremental ReplicationSELECT, REPLICATION CLIENT, REPLICATION SLAVETABLE-related permissions (ALTER, DROP, SELECT, INSERT, UPDATE, DELETE)

Usage Limitations​

  • The data replication feature is only for user databases in the data source, and system databases will not be replicated. For example, system libraries such as information_schema, mysql, performance_schema, sys in MySQL-type data sources will not be replicated.
  • Before performing data synchronization, it is necessary to assess the performance of the source and target data sources, and it is recommended to perform data synchronization during off-peak business hours. Otherwise, the full data initialization will occupy certain read and write resources of the source and target data sources, leading to increased database load.
  • It is recommended to ensure that each table in the synchronization object has a primary key or unique constraint, and the column names are unique, otherwise, the same data may be synchronized repeatedly.

Operation Steps​

Commercialization Notice

NineData’s data replication product has been commercialized. You can still use 10 replication tasks for free, with the following considerations:

  • Among the 10 replication tasks, you can include 1 task, with a specification of Micro.

  • Tasks with a status of do not count towards the 10-task limit. If you have already created 10 replication tasks and want to create more, you can terminate previous replication tasks and then create new ones.

  • When creating replication tasks, you can only select the you have purchased. Specifications that have not been purchased will be grayed out and cannot be selected. If you need to purchase additional specifications, please contact us through the customer service icon at the bottom right of the page.

  1. Log in to the NineData Console.

  2. Click on in the left navigation bar.

  3. On the page, click on in the upper right corner.

  4. On the tab, configure according to the table below and click .

    Parameter
    		Description
    Enter the name of the data synchronization task. To facilitate subsequent search and management, please use a meaningful name. Up to 64 characters are supported.
    The data source where the synchronization object is located.
    The data source that receives the synchronization object.
    Select the content to be replicated to the target data source.
    • : Only synchronize the database table structure of the source data source, without synchronizing data.
    • : Synchronize all objects and data of the source data source, that is, full data replication.
    • : After the full synchronization is completed, incrementally synchronize based on the logs of the source data source. Click on the setting icon to deselect some operation types according to your needs. After deselection, these operations will be ignored in the incremental synchronization.
    Note: You can also click to expand and select the processing strategy when there are tables with the same name or the same data.
    （Required when is selected）
    • : Stop the task when the same name table is detected during the pre-inspection phase.
    • : Send a prompt and continue the task when the same name table is detected during the pre-inspection phase. During the structure replication, ignore the table with the same name. If you also perform data replication, the data will be appended to the table with the same name without overwriting the existing data.
    • : Send a prompt and continue the task when the same name table is detected during the pre-inspection phase. During the structure replication, delete the table with the same name in the target database and re-replicate the table structure based on the source database. If you also perform data replication, the data will be written after the table structure replication is completed.
    • （Optional when both structure and data replication are performed）: Send a prompt and continue the task when the same name table is detected during the pre-inspection phase. During the structure replication, retain the table structure in the target database, and clear the data in the table with the same name at the beginning of the data replication, then replicate from the original table again.
    （Required when is not selected）
    • : Stop the task when data is detected in the target table during the pre-inspection phase.
    • : Ignore the data when it is detected in the target table during the pre-inspection phase, and append the other data.
    • : Delete the data when it is detected in the target table during the pre-inspection phase, and re-enter it.

  5. On the tab, configure the following parameters, and then click .

    Parameter
    		Description
    Select the content to be replicated. You can choose to replicate all content from the source database, or you can choose , select the content to be replicated in the list, and click > to add it to the right list.

  6. On the tab, select different operations based on the selected replication type.

    • Including : Configure the table name after the target table is synchronized to the target data source, and click .

      tip

      You can click on the on the right side of the page to customize the name of the column name synchronized to the target data source. In addition, you can also set , and only data that meets the filtering conditions will be synchronized to the target data source. For example, for the test data employees table, set the filtering condition to emp_no>=10005, then data with emp_no column less than 10005 will not be synchronized to the target data source.

    • Not including : The system defaults to selecting the database with the same name in the target data source. If it does not exist, you need to manually select the target library. The table name and column name in the target library need to be consistent with the synchronization object. If they are not consistent, you can also manually map the table name and column name.

  7. On the tab, wait for the system to complete the pre-inspection, and after the pre-inspection is passed, click .

    tip
    • You can check . After the synchronization task is completed, automatically start the data consistency comparison based on the source data source to ensure that the data on both sides is consistent. Depending on the you selected, the timing for starting is as follows:
      • : Start after the structure replication is completed.
      • +, : Start after the full replication is completed.
      • ++, : Start when the incremental data is consistent with the source data source for the first time and is 0 seconds. You can click to view the synchronization delay on the page. sync_delay
    • If the pre-inspection does not pass, you need to click in the column on the right side of the target inspection item to investigate the cause of the failure, manually fix it, and then click to re-execute the pre-inspection until it passes.
    • If the is , you can fix or ignore it according to the specific situation.

  8. On the page, it prompts , and the synchronization task starts running. At this time, you can perform the following operations:

    • Click to view the execution of each stage of the synchronization task.
    • Click to return to the task list page.

View Synchronization Results​

  1. Log in to the NineData Console.

  2. Click on in the left navigation bar.

  3. Click on the of the target synchronization task on the page, and the page description is as follows.

    result-starrocks

    No.
    		Function
    		Description
    1Synchronization DelayThe data synchronization delay between the source data source and the target data source, 0 seconds means there is no delay between the two ends. At this time, you can choose to switch the business to the target data source to achieve smooth migration.
    2Configure AlertsAfter configuring the alert, the system will notify you through the method you choose when the task fails. For more information, please refer to Introduction to Operations Monitoring.
    3More
    • Pause: Pause the task, only tasks in the Running status are optional.
    • Terminate: End the unfinished or listening (i.e., incremental synchronization) task. After terminating the task, it cannot be restarted, please proceed with caution. If the synchronization object contains triggers, the trigger replication option will pop up, please choose as needed.
    • Delete: Delete the task, the task cannot be restored after deletion, please proceed with caution.
    4Structure Replication (Displayed in scenarios including structure replication)Display the progress and detailed information of structure replication.
    • Click on the Logs on the right side of the page: View the execution logs of structure replication.
    • Click on the refresh on the right side of the page: View the latest information.
    • Click on View DDL in the Operation column on the right side of the target object in the list: You can view the SQL replay.
    5Full Replication (Displayed in scenarios including full replication)Display the progress and detailed information of full replication.
    • Click on the Monitoring on the right side of the page: View the monitoring metrics during the full replication process. During the full replication process, you can also click on the Throttling Settings on the right side of the monitoring metric page to limit the rate of writing to the target data source per second. The unit is rows/second.
    • Click on the Logs on the right side of the page: View the execution logs of full replication.
    • Click on the refresh on the right side of the page: View the latest information.
    6Incremental Replication (Displayed in scenarios including incremental replication)Display the monitoring metrics of incremental replication.
    • Click on on the right side of the page: View the operations currently being executed by the current replication task, including:
      • : The replication task is executed by multiple threads for replication operations, displaying the current thread number.
      • : Details of the SQL statement currently being executed by the current thread.
      • : The response time of the current thread, if this value increases, it means that the current thread may be stuck for some reason.
      • : The timestamp when the current thread started.
      • : The current status of the thread.
    • Click on the Throttling Settings on the right side of the page: Limit the rate of writing to the target data source per second. The unit is rows/second.
    • Click on the Logs on the right side of the page: View the execution logs of incremental replication.
    • Click on the refresh on the right side of the page: View the latest information.
    7Modify ObjectDisplay the modification records of the synchronization object.
    • Click on Modify Synchronization Object on the right side of the page to configure the synchronization object.
    • Click on the refresh on the right side of the page: View the latest information.
    8Data ComparisonDisplay the results of data comparison between the source data source and the target data source. If you have not enabled data comparison, please click on Enable Data Comparison on the page.
    • Click on Re-compare on the right side of the page: Re-initiate the comparison between the current source and target data.
    • Click on the Logs on the right side of the page: View the execution logs of consistency comparison.
    • Click on the Monitoring (only displayed when data comparison is displayed) on the right side of the page: View the trend chart of comparison RPS (number of records compared per second). Click on Details to view records from earlier times.
    • Click on details in the Operation column on the right side of the comparison list (displayed when inconsistent): View the comparison details of the source and target ends.
    • Click on sql in the Operation column on the right side of the comparison list (displayed when inconsistent): Generate a change SQL, you can directly copy this SQL to the target data source for execution, and modify the inconsistent content.
    9ExpandDisplay the detailed information of the current replication task, including Replication Type, Replication Object, Start Time, etc.

Appendix 1: MySQL and StarRocks Data Type Mapping Table​

During the data replication process, MySQL data types are mapped to corresponding StarRocks data types.

CategoryMySQL Data TypeStarRocks Data Type
NumericTINYINTSMALLINT
TINYINT UNSIGNEDSMALLINT
SMALLINTSMALLINT
SMALLINT UNSIGNEDINT
MEDIUMINTINT
MEDIUMINT UNSIGNEDINT
INTINT
INT UNSIGNEDBIGINT
BIGINTBIGINT
BIGINT UNSIGNEDLARGEINT
BIT(M)SAMLLINT
DECIMALDECIMAL
NUMERICDECIMAL
FLOATFLOAT
DOUBLEDOUBLE
BOOL，BOOLEANBOOLEAN
DATE AND TIMEDATEDATE
DATETIME[(fsp)]DATETIME
DATETIME[(fsp)]DATETIME
TIME[(fsp)]VARCHAR
YEAR[(4)]INT
STRINGCHAR/VARCHARCHAR
VARCHARVARCHAR
BINARY/VARBINARYBINARY/VARBINARY
TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXTSTRING
TINYBLOB/BLOB/MEDIUMBOLB/LONGBLOBSTRING
ENUMSTRING
SETSTRING
JSONJSONJSON

Appendix 2: Pre-inspection Item Overview​

Check ItemCheck Item DescriptionDoes Not Pass to Prevent Task ContinuationFailure Reason and Repair Strategy
Source Data Source Connection TestTest the connectivity of the source data sourceYes
  • Failure Reason: The connection between NineData and the source database is not open.
  • Repair Method:
    • Self-built database: Please check if the database is configured with a firewall, and confirm whether NineData's service IP is open.
    • ECS self-built database: Please confirm the security group settings of the ECS instance where the data source is located, and whether it allows access from NineData's service IP. If not allowed, modify the security group to open NineData IP and re-precheck.
    • Cloud database: Please confirm the security group | whitelist settings of the cloud database, and whether it allows access from NineData's service IP. If not allowed, you can modify the security group | whitelist to open NineData IP and re-precheck.
    • JDBC connection failed: Please confirm if the database's account password is incorrect. If incorrect, please adjust the data source configuration first, and then re-precheck.
Target Data Source Connection TestTest the connectivity of the target data sourceYes
  • Failure Reason: The connection between NineData and the target data source is not open.
  • Repair Method:
    • Cloud database: Please confirm the security group | whitelist settings of the cloud database, and whether it allows access from NineData's service IP. If not allowed, you can modify the security group | whitelist to open NineData IP and re-precheck.
    • JDBC connection failed: Please confirm if the database's account password is incorrect. If incorrect, please adjust the data source configuration first, and then re-precheck.
Source Data Source Permission CheckCheck the account permissions of the source data sourceYes
  • Failure Reason: Insufficient permissions for the source database.
  • Repair Method: Grant SELECT, REPLICATION CLIENT, REPLICATION SLAVE permissions to the account of the source data source, and re-precheck.
Target Data Source Permission CheckCheck the account permissions of the target data sourceYes
  • Failure Reason: Insufficient permissions for the target database.
  • Repair Method: Grant CREATE/ALTER/DROP TABLE, SELECT, INSERT, UPDATE, DELETE, REFERENCES, CREATE, TEMPORARY, and USAGE permissions to the account of the target data source, and re-precheck.
Whether the Source Database has Binlog EnabledCheck if the Binlog is enabled for the source databaseYes
  • Failure Reason: Your source database does not have Binlog enabled.
  • Repair Method: Turn on Binlog and re-precheck.
Whether the Source Database binlog format supports row modeCheck if the binlog format of the source database is rowYes
  • Failure Reason: The binlog format of your source database is not in row mode.
  • Repair Method: Please refer to the MySQL official documentation to modify it, and then re-precheck.
Whether binlog_row_image is fullCheck if the binlog_row_image of the source database is fullYes
  • Failure Reason: The binlog_row_image of your source database is not full.
  • Repair Method: Please refer to the MySQL official documentation to modify it, and then re-precheck.
Check for Existing Objects with the Same NameCheck if there are objects with the same name as the objects to be replicated in the target
  • Yes: When the same name object processing strategy is selected as "Pre-inspection error and stop task".
  • No: Other situations.
  • Failure Reason: There are objects with the same name in the target data source.
  • Repair Method:
    • Modify the same name object processing strategy and re-precheck.
    • Delete the object with the same name and re-precheck.
Data Existence CheckCheck if the replication object in the target data source already has data
  • Yes: When the same name object processing strategy is "Pre-inspection error and stop task".
  • No: Other situations.
  • Failure Reason: There is the same data in the target data source.
  • Repair Method:
    • Modify the existing data processing strategy and re-execute the pre-inspection.
    • Manually clear the existing data in the target data source and re-execute the pre-inspection.

Introduction to Data Replication

Last updated on