MySQL to StarRocks Data Replication

NineData Data Replication supports schema replication, full data replication, and incremental replication from MySQL-compatible data sources to StarRocks.

Overview

Use this guide to initialize StarRocks tables from MySQL and keep analytics, reporting, and data warehouse workloads current with source-side changes.

Before you begin

• Add the source and target data sources to NineData. For details, see Add Data Source.

• The supported MySQL source versions range from 5.1 to 9.0.

• The target database must be StarRocks 3.1, 3.0, 2.5, 2.4, 2.3, 2.2, 2.1, or 2.0.

• For incremental replication, the source data source must have Binlog enabled with the following parameters:

  • binlog_format=ROW

  • binlog_row_image=FULL

    tip

    If the source data source is a standby database, enable log_slave_updates to capture the complete Binlog.

• The source and target accounts must have the following permissions.

  Replication type	Source data source	Target data source
  Full replication	SELECT	Table-related permissions: ALTER, DROP, SELECT, INSERT, UPDATE, and DELETE.
  Incremental replication	SELECT, REPLICATION CLIENT, and REPLICATION SLAVE	Table-related permissions: ALTER, DROP, SELECT, INSERT, UPDATE, and DELETE.

Restrictions

• Data replication applies only to user databases. System databases are not replicated. For MySQL data sources, system databases include information_schema, mysql, performance_schema, and sys.
• Before data synchronization, evaluate source and target performance, and run synchronization during off-peak hours when possible. Full data initialization consumes read and write resources on both sides and increases database load.
• Make sure each replicated table has a primary key or unique constraint, and that column names are unique. Otherwise, duplicate rows may be synchronized.

Procedure

1. Sign in to the NineData Console.

2. In the navigation menu, click Replication.

3. On the Replication page, click Create Replication.

4. On the Source & Target tab, set the following fields, then click Next.

   Parameter	Description
   Name	Enter a meaningful task name. Up to 64 characters are supported.
   Source	Select the MySQL data source that contains the objects to replicate.
   Target	Select the StarRocks data source that receives the replicated objects.
   Type	Select the content to replicate to the target data source. Schema: Replicate only the source database and table structure. Data is not replicated. Full: Replicate all objects and data from the source data source. This is full data replication. Incremental: After full synchronization completes, replicate incremental changes based on source logs. Click the icon to clear operation types that should not be replicated during incremental synchronization. Cleared operations are ignored during incremental synchronization. Note: Expand Target Table Preparation and choose the handling strategy for same-name tables or existing data in the target.
   If target table already exists(Required when Schema is selected)	Pre-Check Error and Stop Task: Stop the task when the precheck detects a table with the same name. Skip and Continue Task: Show a message and continue the task if the precheck detects a table with the same name. During structure replication, NineData skips that table. If data replication is also enabled, NineData appends data to the existing table without overwriting the original data. Delete Objects and Rewrite: Show a message and continue the task if the precheck detects a table with the same name. During structure replication, NineData deletes the table with the same name in the target database and copies the table structure from the source database again. If data replication is also enabled, NineData writes data after structure replication completes. Keep Schema and Clear Data, then Rewrite(Optional when both structure and data replication are performed): Show a message and continue the task if the precheck detects a table with the same name. During structure replication, NineData keeps the target table structure and clears the data in the same-name table when data replication starts, then copies data from the source table again.
   Target Table Exists Data(Required when Schema is not selected)	Pre-Check Error and Stop Task: Stop the task when the precheck detects existing data in the target table. Ignore existing target data and append to it.: If the precheck detects existing data in the target table, ignore that data and append the rest. Clear target existing data before write: If the precheck detects existing data in the target table, delete that data and write it again.

5. On the Objects tab, configure the following parameter, then click Next.

   Parameter	Description
   Replication Objects	Select the objects to replicate. Choose All Objects to replicate all objects in the source database, or choose Customized Object, select objects in the Source Object list, and click > to move them to the Target Object list.

6. On the Mapping tab, choose the action that matches the selected replication type.

   • If Schema is included: configure the target table name after synchronization, and then click Save and Pre-Check.

     tip

     Click Mapping & Filtering to customize target column names after synchronization. Set Data Filter so that only data that matches the filter conditions is synchronized. For example, if you set the filter condition to emp_no>=10005 for the employees table, rows with emp_no less than 10005 are not synchronized.

   • If "Schema" is not included: NineData selects the database with the same name in the target data source by default. If it does not exist, select the target database manually. The table and column names in the target database must match the replication objects. If they do not match, map them manually.

7. On the Pre-check tab, wait for NineData to complete the precheck. After the precheck passes, click Launch.

   tip

   • Select Enable data consistency comparison to compare target data with source data after the synchronization task completes. The start time of Enable data consistency comparison depends on the selected Type:
     • Schema: Starts after structure replication completes.
     • Schema+Full, Full: Starts after full replication completes.
     • Schema+Full+Incremental, Incremental: Starts when incremental data is consistent with the source data source for the first time and Delay is 0 seconds. Click View Details to view the synchronization delay on the Details page.
   • If the precheck fails, click Details in the Actions column for the failed item to investigate the cause, fix it, and then click Check Again to run the precheck again.
   • For items whose Result is Warning, fix the issue or ignore the warning.

8. On the Launch page, the Launch Successfully message appears and the synchronization task starts. Use these actions:

   • Click View Details to view each synchronization stage.
   • Click Back to list to return to the Replication task list.

View replication results

1. Sign in to the NineData Console.

2. In the navigation menu, click Replication.

3. On the Replication page, click the Task ID of the target replication task. The task details page contains the following areas.

   

   No.	Function	Description
   1	Synchronization Delay	Shows the synchronization delay between the source and target. 0 seconds means there is no delay, which is the expected state before a smooth traffic switch.
   2	Configure Alerts	Notifies you when the task fails. For more information, see Introduction to Operations Monitoring.
   3	More	Pause: Pause the task. Only tasks in the Running state can be selected. Terminate: End an unfinished task or a task that is still being monitored, such as a task in incremental synchronization. After a task is terminated, it cannot be restarted. Proceed with caution. If the synchronization object contains triggers, the trigger replication option appears. Choose the option if applicable. Delete: Delete the task. Deleted tasks cannot be restored. Proceed with caution.
   4	Structure Replication (shown when structure replication is included)	Shows structure replication progress and details. Click Logs to view structure replication logs. Click to view the latest information. Click View DDL in the Operation column of a target object to view the SQL replay.
   5	Full Replication (shown when full replication is included)	Shows full replication progress and details. Click Monitoring to view metrics during full replication. Click Throttling Settings on the monitoring page to limit the write rate to the target data source. The unit is rows per second. Click Logs to view the full replication execution log. Click to view the latest information.
   6	Incremental Replication (shown when incremental replication is included)	Shows incremental replication metrics. Click View Threads to view currently running replication operations, including: Thread ID: The current thread ID. Replication tasks use multiple threads for replication operations. Execute SQL: Details of the SQL statement that the current thread is executing. Response Time: The response time of the current thread. If this value increases, the thread may be blocked. Event Time: The timestamp when the current thread started. Status: The current thread status. Click Throttling Settings to limit the write rate to the target data source. The unit is rows per second. Click Logs to view the incremental replication execution log. Click to view the latest information.
   7	Modify Object	Shows the modification history of the replication object. Click Modify Synchronization Object to configure the replication object. Click to view the latest information.
   8	Data Comparison	Shows data comparison results between the source and target. If data comparison is not enabled, click Enable Data Comparison on the page. Click Re-compare to compare the current source and target data again. Click Logs to view the consistency comparison execution log. Click Monitoring to view the comparison RPS trend. Click Details to view earlier records. Click in the Operation column of the comparison list (shown when data is inconsistent) to view source and target comparison details. Click in the Operation column of the comparison list (shown when data is inconsistent) to generate change SQL. Copy the SQL to the target data source and run it to fix the inconsistency.
   9	Expand	Shows task details, including Replication Type, Replication Object, and Start Time.

Appendix 1: MySQL to StarRocks Data Type Mapping

During data replication, NineData maps MySQL data types to corresponding StarRocks data types.

Category	MySQL Data Type	StarRocks Data Type
Numeric	TINYINT	SMALLINT
	TINYINT UNSIGNED	SMALLINT
	SMALLINT	SMALLINT
	SMALLINT UNSIGNED	INT
	MEDIUMINT	INT
	MEDIUMINT UNSIGNED	INT
	INT	INT
	INT UNSIGNED	BIGINT
	BIGINT	BIGINT
	BIGINT UNSIGNED	LARGEINT
	BIT(M)	SMALLINT
	DECIMAL	DECIMAL
	NUMERIC	DECIMAL
	FLOAT	FLOAT
	DOUBLE	DOUBLE
	BOOL, BOOLEAN	BOOLEAN
DATE AND TIME	DATE	DATE
	DATETIME[(fsp)]	DATETIME
	DATETIME[(fsp)]	DATETIME
	TIME[(fsp)]	VARCHAR
	YEAR[(4)]	INT
STRING	CHAR/VARCHAR	CHAR
	VARCHAR	VARCHAR
	BINARY/VARBINARY	BINARY/VARBINARY
	TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT	STRING
	TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB	STRING
	ENUM	STRING
	SET	STRING
JSON	JSON	JSON

Appendix 2: Precheck items

Check item	What NineData checks	Blocks task continuation	Failure reason and repair strategy
Source data source connection test	Tests connectivity to the source data source	Yes	Failure reason: NineData cannot connect to the source database. How to fix: Self-managed database: Check whether the database uses a firewall, and make sure NineData service IP addresses are allowed. Self-managed database on ECS: Check the security group for the ECS instance that hosts the data source. If NineData service IP addresses are not allowed, update the security group and run the precheck again. Cloud database: Check the security group or whitelist settings. If NineData service IP addresses are not allowed, update the security group or whitelist and run the precheck again. JDBC connection failed: Check whether the database username or password is incorrect. If it is incorrect, update the data source configuration and run the precheck again.
Target data source connection test	Tests connectivity to the target data source	Yes	Failure reason: NineData cannot connect to the target data source. How to fix: Cloud database: Check the security group or whitelist settings. If NineData service IP addresses are not allowed, update the security group or whitelist and run the precheck again. JDBC connection failed: Check whether the database username or password is incorrect. If it is incorrect, update the data source configuration and run the precheck again.
Source data source permission check	Checks the account permissions of the source data source	Yes	Failure reason: The source database account does not have sufficient permissions. How to fix: Grant `SELECT`, `REPLICATION CLIENT`, and `REPLICATION SLAVE` permissions to the source data source account, and run the precheck again.
Target data source permission check	Checks the account permissions of the target data source	Yes	Failure reason: The target database account does not have sufficient permissions. How to fix: Grant `CREATE`, `ALTER`, `DROP TABLE`, `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `REFERENCES`, `TEMPORARY`, and `USAGE` permissions to the target data source account, and run the precheck again.
Source Binlog check	Checks whether Binlog is enabled for the source database	Yes	Failure reason: Binlog is not enabled for the source database. How to fix: Enable Binlog and run the precheck again.
Source Binlog format check	Checks whether the source database `binlog_format` is `ROW`	Yes	Failure reason: The source database `binlog_format` is not `ROW`. How to fix: See the MySQL official documentation to modify it, and then run the precheck again.
Source Binlog row image check	Checks whether the source database `binlog_row_image` is `FULL`	Yes	Failure reason: The source database `binlog_row_image` is not `FULL`. How to fix: See the MySQL official documentation to modify it, and then run the precheck again.
Same-name object existence check	Checks whether the target contains objects with the same name as the objects to replicate	Yes: When the same-name object handling strategy is "Precheck error and stop task". No: Other situations.	Failure reason: Same-name objects exist in the target data source. How to fix: Modify the same-name object handling strategy and run the precheck again. Delete the same-name object and run the precheck again.
Target data existence check	Checks whether target objects already contain data	Yes: When the same-name object handling strategy is "Precheck error and stop task". No: Other situations.	Failure reason: Existing data is detected in the target data source. How to fix: Modify the existing data handling strategy and run the precheck again. Manually clear the existing data in the target data source and run the precheck again.

Related links

• Introduction to Data Replication