Self-managed MySQL to ClickHouse Data Replication

NineData Data Replication supports full and incremental replication from self-managed MySQL to ClickHouse.

Overview

Use this guide to replicate self-managed MySQL data into ClickHouse for analytical workloads. NineData can create ClickHouse table schemas, load existing MySQL data, and continue applying 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 source MySQL 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 ClickHouse login account must have access_management set to 1.

  tip

  Configuration method:

  Open the User.xml file in /etc/clickhouse-server, find the target user, and add <access_management>1</access_management>.

Notes

• An unsigned integer field in the source MySQL data source is mapped to a UInt type in ClickHouse. For other MySQL to ClickHouse mappings, see Appendix.

• In most MySQL to ClickHouse replication scenarios, include schema replication. NineData migrates the MySQL table schema to ClickHouse and inserts two system columns into the ClickHouse tables involved in replication. If the replication type does not include schema replication, make sure of the following:

  • The tables in the ClickHouse data source that are included in replication must match the MySQL table schema.

  • Add the following two system columns to each ClickHouse table that is included in replication so the table can record DML operations and Binlog time.

    Column name	Data type	Default value	Description
    _jz_data_sign	Int8	DEFAULT 1	Records the DML operation type so data in ClickHouse stays consistent with MySQL. INSERT: 1. DELETE: -1. UPDATE: split into one INSERT record and one DELETE record.
    _jz_data_time	String	DEFAULT now()	Records the Binlog time.

    Example:

    CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
    (
        ...
        _jz_data_sign Int8 DEFAULT 1 COMMENT 'replication data update sign: add = 1, remove = -1',
        _jz_data_time String DEFAULT now() COMMENT 'replication data update time'
    ) ENGINE = engine

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.
• The source account must have SELECT permissions for schema and full replication, SHOW VIEW permissions for view 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 task, and then click Next.

   Parameters	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 ClickHouse data source that receives the replication objects.
   Type	Schema: Synchronize only the source table schema. Full: Synchronize all selected source objects and data. This is full replication. Incremental: After full replication completes, synchronize 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.
   Incremental start time	Required only when Incremental is selected. From start time: Start incremental replication from the start time of the current replication task. Custom Time: Select the incremental replication start time and the time zone for the data source region. If the selected time is earlier than the task start time, the task fails if a DDL operation occurred during that period.
   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, and 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 list, and click > to move them to the Target list.
   Blocklist (optional)	Click Add to add a blocklist rule. Select the databases or objects to exclude from replication. Blocklists are used to exclude specific databases or objects when All Objects or Customized Object replication is selected. Left drop-down list: Select the database to add to the blocklist. Right drop-down list: Select one or more objects in the selected database. Leave this field empty to add the entire database to the blocklist. 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.

   • Contains Schema: Configure the target table name after the table is synchronized to the target data source, and then click Save and Pre-Check.

     tip

     Click Mapping & Filtering to customize target column names after synchronization. Set Data Filter by using comparison expressions. Only data that matches the filter conditions is synchronized to the target data source.

   • Does not contain Schema: 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 Precheck tab, wait for NineData to complete the precheck. After the precheck passes, click Launch.

   tip

   • Select Enable data consistency comparison to compare the target data with the source data after the synchronization task completes. The comparison start time depends on the replication Type:

     • Schema: Starts after schema replication completes.

     • Schema + Full: Starts after full replication completes.

     • Schema + Full + Incremental (SQL Server is not currently supported): Starts when incremental data is consistent with the source data for the first time and the 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 warnings, 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 of each phase of the synchronization task.
   • Click Back to list to return to the Replication 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	Delay	Shows the replication 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 Alert	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	Schema (shown when schema replication is included)	Shows schema replication progress and details. Click Log to view the schema replication execution log. Click Refresh to view the latest information. Click View DDL in the Action column of a target table to view the SQL playback.
   5	Full (shown when full replication is included)	Shows full replication progress and details. Click Monitor to view full replication metrics. Click Log to view the full replication execution log. Click Refresh to view the latest information.
   6	Incremental (shown when incremental replication is included)	Shows incremental replication metrics. Click Log to view the incremental replication execution log. Click Refresh to view the latest information.
   7	Modify Objects	Shows the modification history of replication objects. Click Modify Objects to configure replication objects. Click Refresh to view the latest information.
   8	Comparison	Shows data comparison results between the source and target, including Schema Comparison and Data Comparison. If comparison is not enabled, click Enable Comparison on the page. Click Re-compare to compare data between the current source and target again. Click Log to view the consistency comparison execution log. Click Monitor (shown only for data comparison) to view the comparison RPS trend. Click Details to view earlier records. Click in the Actions column of the comparison list to view DDL comparison details for the source and target table definitions. Click in the Actions 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	Show Details	Shows task details, including Type, Replication Object, and Started.

Appendix 1: MySQL to ClickHouse data type mapping

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

MySQL	ClickHouse
INT, MEDIUMNINT	Int32
BIT	UInt64
TINYINT	Int8
SMALLINT UNSIGNED, YEAR	UInt16
BIGINT	Int64
DECIMAL	Decimal
FLOAT	Float32
DOUBLE	Float64
DATE	Date32
DATETIME, TIMESTAMP	DateTime64
TIME, CHAR, VARCHAR, BINARY, VARBINARY, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT, ENUM, SET, GEOMETRY, GEOMETRYCOLLECTION, JSON	String
POINT, MULTIPOINT	Point
LINESTRING, MULTILINESTRING	Ring
POLYGON	Polygon
MULTIPOLYGON	MultiPolygon

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.
Target system field check	Checks whether the target table contains the _sign and _version system fields.
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