MySQL to SelectDB

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

Overview

Use this workflow to migrate MySQL data to SelectDB and keep the target database updated with incremental source 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 data source must be SelectDB.

• 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 follow SelectDB 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 in the data source. System databases are not replicated. For example, MySQL system databases such as information_schema, mysql, performance_schema, and sys are not replicated.
• The source account must have SELECT privileges for schema and full data replication, SHOW VIEW privileges for view replication, and REPLICATION CLIENT and REPLICATION SLAVE privileges for incremental replication. The target account must have DML and DDL privileges.
• Before starting data synchronization, evaluate the performance of the source and target data sources. Run synchronization during off-peak hours when possible. Otherwise, full data initialization consumes read and write resources on both data sources and increases database load.
• If the source data source contains views, functions, stored procedures, triggers, or events, the definer information of these objects is changed on the target data source to the account used by the current synchronization task to access the target data source.
• Make sure every table in the synchronization objects has a primary key or unique constraint, and that column names are unique. Otherwise, duplicate rows may be synchronized.
• If triggers exist in the source data source, NineData synchronizes the triggers only after incremental synchronization ends.

Procedure

1. Sign in to the NineData Console.

2. In the navigation menu, click 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 SelectDB 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 operation types 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 content from the source database, or select Customized Object, choose the objects in the Source Object list, and click > to add 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 SelectDB, and then click Save and Pre-Check. The table shows the default SelectDB Data Model, Key, and Distribute Key definitions for different MySQL table types.

     MySQL table type	SelectDB Data Model	SelectDB Key definition	SelectDB 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	By default, select the first two columns in the table.	Uses the same columns as Key.

     tip

     Click Mapping & Filtering to customize 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, select the target database manually. 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 replication 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. In the navigation menu, click 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.

   

   Number	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 Replication (shown when structure replication is included)	Shows structure replication progress and details. Click Logs to view the structure replication 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 Replication (shown when full replication is included)	Shows full replication progress and details. Click Monitoring to view full replication metrics. During full replication, 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 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 the operations currently running 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 replication execution log. Click to view the latest information.
   7	Modify Objects	Shows the modification history of replication objects. Click Modify Synchronization Objects 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 Recompare 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 SelectDB data type mapping

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

Category	MySQL Data Type	SelectDB 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 (SelectDB version ≥ 2.2.x) DATE (SelectDB version < 2.2.x)
	DATETIME[(fsp)]	DATETIME (source without FSP parameter and SelectDB version ≥ 2.2.x, or SelectDB version ≤ 2.2.x) DATETIMEV2 (source with FSP parameter and SelectDB version ≥ 2.2.x)
	Timestamp[(fsp)]	DATETIME (source without FSP parameter and SelectDB version ≥ 2.2.x, or SelectDB version ≤ 2.2.x) DATETIMEV2 (source with FSP parameter and SelectDB version ≥ 2.2.x)
	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 SelectDB.
	BINARY|VARBINARY	STRING
	TINYTEXT|TEXT|MEDIUMTEXT|LONGTEXT	STRING
	TINYBLOB|BLOB|MEDIUMBLOB|LONGBLOB	STRING
	ENUM	STRING
	SET	STRING
JSON	JSON	JSONB (SelectDB version ≥ 2.2.x) STRING (SelectDB version < 2.2.x)

Appendix 2: Precheck items

Check item	What NineData checks
Target Database Data Existence Check	Checks whether data already exists for the objects to be replicated in the target database.
Target Database Same Name Object Existence Check	Checks whether objects to be replicated already exist in the target database.
Check Object Name Compliance	Checks whether the database and table names comply with SelectDB naming rules.
Source Data Source Connection Check	Checks the source data source gateway status, instance accessibility, username, and password.
Target Data Source Connection Check	Checks the target data source gateway status, instance accessibility, 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.
Check if Source Database log_slave_updates is Supported	Checks whether log_slave_updates is set to ON when the source database is a standby database.
Source Data Source and Target Data Source Version Check	Checks whether the source and target database versions are compatible.
Check if Source Database is Enabled with Binlog	Checks whether Binlog is enabled for the source database.
Check if Source Database Binlog Format is Supported	Checks whether the source database binlog_format is ROW.
Check if Source Database binlog_row_image is Supported	Checks whether the source database binlog_row_image is FULL.

Related documents

• Introduction to Data Replication