MySQL to Doris

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

Overview

Use this guide to replicate MySQL data into Doris for real-time analytics and reporting workloads. NineData can create Doris table schemas, load existing MySQL data, and keep the target updated with incremental changes from MySQL Binlog.

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

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

Restrictions

• Source database and table names must comply with Doris naming rules. Names must start with a letter and can contain letters, numbers, and underscores. Spaces and other special characters are not supported.

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

• The source account must have SELECT permissions for schema and full replication, and REPLICATION CLIENT and REPLICATION SLAVE permissions for incremental replication. The target account must have DML and DDL permissions.

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

• If the synchronization objects are tables, do not use online DDL tools such as gh-ost or pt-online-schema-change to modify source objects during synchronization. Otherwise, the task may fail.

• Make sure each replicated table has a primary key or unique constraint, and that column names are unique. Otherwise, duplicate data may be replicated.

Procedure

1. Sign in to the NineData Console.

2. Go to Replication > Data Replication.

3. On the Replication page, click Create Replication.

4. On the Source & Target tab, configure the source and target, then click Next.

   Parameter	Description
   Name	Enter a meaningful data replication task name for later search and management. Up to 64 characters are supported.
   Source	Select the MySQL data source that contains the replication objects.
   Target	Select the Doris data source that receives the replicated objects.
   Type	Select what to replicate to the target data source. Schema: Replicate only the source database and table schemas. Data is not replicated. Full: Replicate all selected source objects and data. This is full data replication. Incremental: After full replication completes, replicate incremental changes based on the source logs. Click the icon to clear operation types that you do not want to replicate during incremental synchronization. Cleared operations are ignored during incremental synchronization. Note: Expand Target Table Preparation to choose the handling strategy for same-name tables or existing data in the target.
   Target Table Preparation	If target table already exists (required when Schema is selected) Pre-Check Error and Stop Task: Stop the task if the precheck detects a table with the same name. Skip and Continue Task: If the precheck detects a table with the same name, continue the task and show a prompt. During schema 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: If the precheck detects a table with the same name, continue the task and show a prompt. During schema replication, NineData deletes the table with the same name in the target database and copies the table schema from the source database again. If data replication is also enabled, NineData writes data after the schema is copied. Keep Schema and Clear Data, then Rewrite (available when schema and data are replicated together): If the precheck detects a table with the same name, continue the task and show a prompt. During schema replication, NineData keeps the target table schema. When data replication starts, NineData clears the data in the table with the same name and 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 if 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 new data. Clear target existing data before write: If the precheck detects existing data in the target table, delete the existing data before writing new data.

5. On the Objects tab, configure the replication objects, then click Next.

   Parameter	Description
   Replication Objects	Select the objects to replicate. Select All Objects to replicate all objects in the source database, or select Customized Object, choose the objects in the Source Object list, and click > to move them to the Target Object list.
   Blacklist (optional)	Click Add to add a blacklist rule. Select the databases or objects to exclude from replication. Blacklist rules are used to exclude specific databases or objects when Customized Object or Full Instance replication is selected. Left drop-down list: Select the database to add to the blacklist. Right drop-down list: Select one or more objects in the selected database. Leave this field empty to add the entire database to the blacklist. To exclude multiple databases, click Add again to add more rows.

6. On the Mapping tab, select an operation based on the selected replication type.

   • If Schema is included: configure the target table name, Key, and Distribute Key after the table schema is synchronized to Doris, and then click Save and Pre-Check. The table shows the default Doris Data Model, Key, and Distribute Key definitions for different MySQL table types.

     MySQL table type	Doris Data Model	Doris Key definition	Doris Distribute Key definition
     Tables with a primary key	Unique Model	Uses all primary key columns in the order defined by the source primary key.	Uses all primary key columns.
     Tables without a primary key but with a unique key	Unique Model	Uses all unique key columns in the order defined by the source unique key.	Uses all unique key columns.
     Tables without a primary key or unique key	Duplicate Model	Uses the first two columns in the table by default.	Uses the same columns as Key.

     tip

     Click Mapping & Filtering to customize target column names after synchronization. Set Data Filter to synchronize only rows that match the filter conditions. For example, for the sample employees table, the filter condition emp_no>=10005 excludes rows whose emp_no value is less than 10005.

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

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 schema replication completes.

     • Schema + Full, or Full: Starts after full replication completes.

     • Schema + Full + Incremental, or Incremental: Starts when incremental data is consistent with the source data 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 view the cause. After fixing the issue, 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 replication task starts running. Use these actions:

   • Click View Details to view the execution status of each replication phase.
   • Click Back to list to return to the Replication task list.

View replication results

1. Sign in to the NineData Console.

2. Go to Replication > Data Replication.

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

   

   No.	Area	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	After alerts are configured, NineData notifies users when the task fails. For more information, see Introduction to operational monitoring.
   3	More	Pause: Pause a task whose status is Running. Terminate: End a task that is not complete or is still being monitored, such as a task in incremental synchronization. A terminated task cannot be restarted. Proceed with caution. If the synchronization objects contain triggers, select the trigger replication option if applicable. Delete: Delete the task. Deleted tasks cannot be recovered. Proceed with caution.
   4	Structure Copy (shown when structure copy is included)	Shows structure copy progress and details. Click Logs to view the structure copy execution log. Click to view the latest information. Click View DDL in the Operation column of a target object to view the SQL playback.
   5	Full Copy (shown when full copy is included)	Shows full copy progress and details. Click Monitoring to view full copy metrics. During full copy, click Rate Limit 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 copy execution log. Click to view the latest information.
   6	Incremental Copy (shown when incremental copy is included)	Shows incremental copy metrics. Click View Threads to view currently running operations for the replication task, including: Thread ID: The thread ID. A replication task can 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 Rate Limit Settings to limit the write rate to the target data source. The unit is rows per second. Click Logs to view the incremental copy execution log. Click to view the latest information.
   7	Modify Object	Shows the modification history of replication objects. Click Modify Synchronized Object to configure replication objects . 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 Redo Comparison to compare data between the current source and target 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. Apply the SQL on the target data source to fix the inconsistency.
   9	Expand	Shows task details, including Replication Type, Replication Object, and Start Time.

Appendix 1: MySQL to Doris data type mapping

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

Category	MySQL Data Type	Doris Data Type
Numeric	TINYINT	TINYINT
	TINYINT UNSIGNED	SMALLINT
	SMALLINT	SMALLINT
	SMALLINT UNSIGNED	INT
	MEDIUMINT	INT
	MEDIUMINT UNSIGNED	BIGINT
	INT	INT
	INT UNSIGNED	BIGINT
	BIGINT	BIGINT
	BIGINT UNSIGNED	LARGEINT
	BIT(M)	INT
	Decimal	Decimal
	Numeric	Decimal
	Float	Float
	Double	DOUBLE
	BOOL|BOOLEAN	BOOLEAN
DATE AND TIME	DATE	DATEV2 (Doris version >= 1.2.0) DATE (Doris version < 1.2.0)
	DATETIME[(fsp)]	DATETIME (without FSP parameter from the source and Doris version >= 1.2.0, or Doris version <= 1.2.0) DATETIMEV2 (with FSP parameter from the source and Doris version >= 1.2.0)
	Timestamp[(fsp)]	DATETIME (without FSP parameter from the source and Doris version >= 1.2.0, or Doris version <= 1.2.0) DATETIMEV2 (with FSP parameter from the source and Doris version >= 1.2.0)
	Time[(fsp)]	VARCHAR
	YEAR[(4)]	INT
STRING	CHAR|VARCHAR	CHAR
	VARCHAR	VARCHAR Note: To avoid data migration loss, VARCHAR(n) is adjusted to VARCHAR(3*n) after migration to Doris.
	BINARY|VARBINARY	STRING
	TINYTEXT|TEXT|MEDIUMTEXT|LONGTEXT	STRING
	TINYBLOB|BLOB|MEDIUMBLOB|LONGBLOB	STRING
	ENUM	STRING
	SET	STRING
JSON	JSON	JSONB (Doris version >= 1.2.0) STRING (Doris version < 1.2.0)

Appendix 2: Precheck items

Check item	What NineData checks
Target database data existence check	Checks whether target objects already contain data.
Target same-name object check	Checks whether objects with the same names already exist in the target database.
Object name compliance check	Checks whether database and table names comply with Doris naming rules.
Source data source connection check	Checks the source gateway status, instance accessibility, and username and password.
Target data source connection check	Checks the target gateway status, instance accessibility, and username and password.
Target database permission check	Checks whether the target database account has the required permissions.
Source database permission check	Checks whether the source database account has the required permissions.
Source standby log update check	Checks whether log_slave_updates is set to ON when the source database is a standby database.
Source and target version check	Checks whether the source and target database versions are compatible.
Source Binlog check	Checks whether Binlog is enabled for the source database.
Source Binlog format check	Checks whether the source database binlog_format is ROW.
Source Binlog row image check	Checks whether the source database binlog_row_image is FULL.

Related documents

• Introduction to Data Replication