Method for synchronizing MySQL database to ClickHouse database

Abstract

A method for synchronizing a MySQL database to a ClickHouse database relates to the technical field of different database synchronization processing methods, and comprises the following steps: step 1, configuring the MySQL database; starting a binlog function, setting a binlog format as an ROW mode, and starting a GTID mode; step 2, configuring a ClickHouse database; defining a ClickHouse table structure generation rule in the configuration file; step 3, synchronizing total data; step 4, synchronizing incremental data; monitoring a data change event by analyzing a binlog of the MySQL; 5, recording a synchronization state; and creating a synchronization state file, and recording a binlog file position, an offset and a GTID set so as to ensure that the synchronization can be continued from a correct GTID position after being interrupted. The efficient synchronization of data is realized, the deployment process is simplified, and the development and maintenance cost is reduced.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of different database synchronization processing methods, and in particular to a method for synchronizing a MySQL database to a ClickHouse database. BACKGROUND

[0002] MySQL is an open-source relational database that uses row storage and focuses on OLTP scenarios, supports complete ACID transactions and rich indexes, and adapts to high-frequency single / small batch data insertion, deletion, modification and query, and is commonly used in order systems, user centers and other core businesses. ClickHouse is an open-source columnar storage database that focuses on OLAP scenarios, is based on vectorized execution and partitioning and bucketing design, is good at PB-level data complex aggregation analysis, has fast query response, and is commonly used in real-time reporting, user behavior analysis and other big data scenarios.

[0003] During project implementation, the performance of the MySQL database is limited when processing large-scale data analysis and complex queries, especially for real-time analysis and columnar storage requirements. ClickHouse database is a high-performance columnar database suitable for processing large-scale data analysis tasks, so it is necessary to synchronize data in the MySQL database to ClickHouse in real time to support efficient data query and analysis. In the prior art, the synchronization method from MySQL to other databases has certain limitations, such as the need for additional coding, performance bottlenecks or data consistency problems. SUMMARY

[0004] In summary, the purpose of the present application is to overcome the drawbacks of the prior art and to provide a method for synchronizing a MySQL database to a ClickHouse database for efficient and automated real-time data synchronization from a MySQL database to a ClickHouse database.

[0005] The technical solution proposed to achieve the purpose of the present application is as follows: A method for synchronizing a MySQL database to a ClickHouse database, characterized in that the method comprises the following steps: Step 1: Configure the MySQL database; enable the binlog log function, set the binlog format to ROW mode, enable the GTID (Global Transaction Identifier) mode, and specify a unique server_id; Step two, configure ClickHouse database; define ClickHouse table structure generation rules in the configuration file, including MySQL connection information (such as host, port, username, password, GTID related information), ClickHouse connection information (host, port, database name), MySQL database name to be synchronized, table name and ClickHouse target table name; Step three, full data synchronization; start MySQL transaction, execute `START TRANSACTION WITH CONSISTENT SNAPSHOT;` to obtain a consistent snapshot; record the current GTID set (such as `gtid_executed`) as the starting point of full synchronization; read the full data of the specified table from the MySQL database, generate the corresponding INSERT statement, set `version=1` and `is_deleted=0` (representing valid data), and batch insert the data into the ClickHouse target table; ensure that the partition key and sorting key of the ClickHouse table (based on the MySQL primary key) are configured according to the data analysis requirements; commit the transaction (`COMMIT;`), complete the full synchronization, and record the final GTID set for the starting point of subsequent incremental synchronization; Step four, incremental data synchronization; by parsing the MySQL binlog log, combined with GTID information, listen to data change events (insert, update, delete), starting from the GTID set recorded in the full synchronization; convert the changed data into ClickHouse INSERT statement, indicate update and delete operation through `version` and `is_deleted` field, and synchronize to ClickHouse target table; ReplacingMergeTree engine automatically merges data according to `version` field, retains the latest version, and processes delete mark according to `is_deleted` field; Step five, record synchronization status; create synchronization status file (such as.db file), record binlog file position, offset (position) and GTID set, to ensure that the synchronization can continue from the correct GTID position after interruption.

[0006] The application has the advantages that the application realizes efficient synchronization of data, simplifies the deployment process, guarantees data consistency, ensures that the sorting key of the ClickHouse table is sorted according to the primary key of the MySQL table, optimizes data merging and query performance, reduces synchronization delay, does not need manual coding, reduces development and maintenance costs, is suitable for various business scenarios, and improves system robustness. BRIEF DESCRIPTION OF DRAWINGS

[0007] Figure 1 The application is a step flowchart. DETAILED DESCRIPTION

[0008] The method steps of the application are further described below in combination with the accompanying drawings and specific embodiments of the application.

[0009] Referring to Figure 1 The application discloses a method for synchronizing a MySQL database to a ClickHouse database, which comprises the following steps: Step 1: Configure the MySQL database; enable the binlog log function, set the binlog format to ROW mode, enable the GTID mode, i.e., the global transaction identification mode, and specify a unique server_id; Add the following configurations in the MySQL configuration file (such as my.cnf or my.ini): ``` [mysqld] server_id = 1 log_bin = mysql-bin binlog_format = ROW gtid_mode = ON enforce_gtid_consistency = ON ``` Restart the MySQL service to apply the configurations and ensure that the GTID mode is enabled to support reliable transaction tracking.

[0010] Step 2: Configure the ClickHouse database; i.e., configure the ClickHouse table structure and mapping rules.

[0011] Define the ClickHouse table structure generation rules in the configuration file, including MySQL connection information (such as host, port, username, password, GTID related information), ClickHouse connection information (host, port, database name), the name of the MySQL database to be synchronized, the table name, and the target table name in ClickHouse.

[0012] Automatically identify the primary key field of a MySQL table by querying MySQL's `INFORMATION_SCHEMA` (e.g., `INFORMATION_SCHEMA.KEY_COLUMN_USAGE`) to obtain the primary key column name and type.

[0013] Add a `version` (UInt64 type, used to represent data version) and `is_deleted` (UInt8 type, 0 indicates valid, 1 indicates deleted) field to each ClickHouse target table. The table engine uses ReplacingMergeTree to support update and delete operations.

[0014] If the target table does not exist in ClickHouse, it will be automatically created using ClickHouse's SQL statements. The table structure will be consistent with the MySQL table structure, and the field types will be mapped according to ClickHouse's optimization rules (e.g., mapping MySQL's VARCHAR to ClickHouse's String). `version` will be specified as the version column in the ReplacingMergeTree. The table's sort key (`ORDER BY`) will be set to the MySQL table's primary key field.

[0015] Example MySQL table, assuming the primary key is `id`: SQL CREATE TABLE mysql_db.users ( id INT PRIMARY KEY, name VARCHAR(255) ); ``` - Corresponding ClickHouse table structure: SQL CREATE TABLE test_db.users ( id UInt32, name (String) version UInt64, is_deleted UInt8 ) ENGINE = ReplacingMergeTree(version,is_deleted) ORDER BY (id); ``` The `ORDER BY (id)` clause is based on the MySQL table's primary key `id`, ensuring that the sort key of the ClickHouse table matches the MySQL primary key. The `is_deleted` column is defined in the table structure as a UInt8 type (0 for valid, 1 for deleted). It is automatically identified by the ReplacingMergeTree engine during merging and is used to mark deleted rows; it does not need to be explicitly specified in the `ENGINE` definition.

[0016] Step 3: Full data synchronization; start a MySQL transaction to ensure data consistency during the full synchronization process.

[0017] Execute `START TRANSACTION WITH CONSISTENT SNAPSHOT;` to obtain a consistent snapshot.

[0018] Record the current GTID set (e.g., `gtid_executed`) as the starting point for full synchronization.

[0019] Read the full data from the specified table in the MySQL database, generate the corresponding INSERT statement, set `version=1` and `is_deleted=0` (indicating valid data), and insert the data in batches into the ClickHouse target table.

[0020] Ensure that the partition key and sort key (based on the MySQL primary key) of the ClickHouse table are configured according to your data analysis needs.

[0021] Commit the transaction (`COMMIT;`) to complete the full synchronization and record the final GTID set, which will serve as the starting point for subsequent incremental synchronization.

[0022] Step 4: Incremental data synchronization; By parsing the MySQL binlog and combining it with GTID information, listen for data change events (insert, update, delete), starting from the GTID set of the full synchronization records.

[0023] The changed data is converted into ClickHouse INSERT statements, using the `version` and `is_deleted` fields to represent update and delete operations, and synchronized to the ClickHouse target table. The ReplacingMergeTree engine automatically merges data based on the `version` field, retaining the latest version, and handles deletion markers based on the `is_deleted` field.

[0024] The specific steps include: Step 4.1: Read the binlog log and parse out the changed table name, operation type (INSERT, UPDATE, DELETE) and changed data based on GTID.

[0025] Step 4.2: Generate the ClickHouse INSERT statement based on the operation type: **INSERT**: Set `version=current timestamp` (or increment the version number), `is_deleted=0`.

[0026] **UPDATE**: Inserts a new record, sets `version=current timestamp+1` (or increments the version number), and `is_deleted=0`.

[0027] **DELETE**: Inserts a new record, sets `version=current timestamp+1` (or increments the version number), and `is_deleted=1`.

[0028] Step 4.3: Execute the INSERT operation through ClickHouse's client API or SQL interface. The ReplacingMergeTree engine will automatically handle the data merging.

[0029] Example INSERT statement: SQL INSERT INTO test_db.users (id, name, version, is_deleted) VALUES (1,'Alice', 16987654321, 0); -- Insert INSERT INTO test_db.users (id, name, version, is_deleted) VALUES (1,'Bob', 16987654322, 0); -- Update INSERT INTO test_db.users (id, name, version, is_deleted) VALUES (1,'Bob', 16987654323, 1); -- delete ```.

[0030] Because the deduplication and deletion processes in ReplacingMergeTree occur during the background merging process, the `FINAL` modifier must be used in the query to ensure that the latest deduplicated data is returned, excluding rows where `is_deleted=1`. For example: SQL SELECT * FROM test_db.users FINAL WHERE name = 'test'; ```

[0031] Step 5: Record the synchronization status; create a synchronization status file (such as a .db file) to record the binlog file position, offset, and GTID set to ensure that synchronization can continue from the correct GTID position after an interruption.

[0032] Regularly check the availability of binlog logs and GTID to ensure the continuity and consistency of synchronization.

[0033] Optional cleanup: To permanently delete rows marked `is_deleted=1`, you can enable the ClickHouse table setting `allow_experimental_replacing_merge_with_cleanup` and configure `min_age_to_force_merge_seconds` to automatically clean up older deleted rows in the partition, or run it manually: SQL OPTIMIZE TABLE test_db.users FINAL CLEANUP; ```.

[0034] This invention utilizes MySQL's binlog, GTID mode, and ClickHouse's ReplacingMergeTree engine to achieve efficient data synchronization, simplify the deployment process, ensure data consistency, and ensure that the sort key of the ClickHouse table is sorted according to the primary key of the MySQL table.

[0035] The synchronization method of the present invention has the following beneficial effects: High consistency: By using MySQL transaction snapshots and GTID mode, we ensure that full and incremental synchronized data are not out of order, thus guaranteeing data consistency.

[0036] High performance: Utilizes ClickHouse's ReplacingMergeTree engine and a sort key based on the MySQL primary key to optimize data merging and query performance and reduce synchronization latency.

[0037] Automation and simplicity: ClickHouse table structures and mapping rules are automatically generated through configuration files, eliminating the need for manual coding and reducing development and maintenance costs.

[0038] Flexibility: Supports insert, update, and delete operations, and enables efficient data change processing through the version and is_deleted fields, making it suitable for various business scenarios.

[0039] Recoverability: The synchronization status file records the binlog position and GTID set, supporting the continuation of synchronization from the correct position after interruption, thus improving system robustness.

[0040] Query optimization: The sort key of the ClickHouse table is based on the MySQL primary key, and the data organization is consistent with MySQL, which significantly improves query efficiency.

[0041] Real-time data analysis: Suitable for scenarios that require real-time synchronization of data from a MySQL database to ClickHouse for efficient analysis, such as financial transaction analysis, user behavior analysis, log analysis, etc.

[0042] Big Data Processing: In scenarios involving large-scale datasets (such as terabytes of data), ClickHouse's columnar storage and query performance can be leveraged to meet high throughput requirements.

[0043] Data warehouse: As part of the data warehouse architecture, business data from MySQL is synchronized to ClickHouse to build a high-performance analytical database.

Claims

1. A method for synchronizing a MySQL database to a ClickHouse database, characterized in that... The method includes the following steps: Step 1: Configure the MySQL database; enable binlog logging, set the binlog format to ROW mode, enable GTID mode, and specify a unique server_id; Step 2, configure the ClickHouse database; define the ClickHouse table structure generation rules in the configuration file, including MySQL connection information, ClickHouse connection information, the name of the MySQL database to be synchronized, the table name, and the target table name in ClickHouse; Step 3: Full data synchronization; Start a MySQL transaction and execute `START TRANSACTION WITH CONSISTENTSNAPSHOT;` to obtain a consistent snapshot; record the current GTID set as the starting point for full synchronization; read all data from the specified table in the MySQL database, generate the corresponding INSERT statement, set `version=1` and `is_deleted=0`, and insert the data in batches into the ClickHouse target table; ensure that the partition key and sort key of the ClickHouse table are configured according to data analysis requirements; commit the transaction (`COMMIT;`) to complete the full synchronization, and record the final GTID set as the starting point for subsequent incremental synchronization; Step 4: Incremental data synchronization; By parsing MySQL's binlog and combining it with GTID information, data change events are monitored, starting with the GTID set of full synchronization records. The changed data is converted into ClickHouse INSERT statements, using the `version` and `is_deleted` fields to represent update and delete operations, and synchronized to the ClickHouse target table. The ReplacingMergeTree engine automatically merges data based on the `version` field, retaining the latest version, and processes deletion markers based on the `is_deleted` field. Step 5: Record the synchronization status; create a synchronization status file to record the binlog file position, offset, and GTID set to ensure that synchronization can continue from the correct GTID position after an interruption.

2. The method for synchronizing a MySQL database to a ClickHouse database according to claim 1, characterized in that: In step one, add the following configuration to the MySQL configuration file: ``` [mysqld] server_id = 1 log_bin = mysql-bin binlog_format = ROW gtid_mode = ON enforce_gtid_consistency = ON ``` Restart the MySQL service to apply the configuration.

3. The method for synchronizing a MySQL database to a ClickHouse database according to claim 1, characterized in that: In step two, the primary key field of the MySQL table is automatically identified by querying `INFORMATION_SCHEMA` in MySQL to obtain the primary key column name and type. `version` and `is_deleted` fields are added to each ClickHouse target table, and the table engine uses ReplacingMergeTree to support update and delete operations. If the target table does not exist in ClickHouse, it is automatically created using ClickHouse's SQL statements. The table structure is consistent with the MySQL table structure, and the field types are mapped according to ClickHouse's optimization rules. `version` is specified as the version column in ReplacingMergeTree. The table's sort key `ORDER BY` is set to the primary key field of the MySQL table.

4. The method for synchronizing a MySQL database to a ClickHouse database according to claim 1, characterized in that: Step four: First, read the binlog logs and parse out the changed table name, operation type, and changed data based on GTID; then, generate the ClickHouse INSERT statement according to the operation type. **INSERT**: Sets `version=current timestamp` or an incrementing version number, `is_deleted=0`; **UPDATE**: Inserts a new record, sets `version=current timestamp+1` or increments the version number, and `is_deleted=0`; **DELETE**: Inserts a new record, sets `version=current timestamp+1` or increments the version number, and `is_deleted=1`; Finally, the ReplacingMergeTree engine automatically handles data merging by performing INSERT operations via ClickHouse's client API or SQL interface.

5. The method for synchronizing a MySQL database to a ClickHouse database according to claim 1, characterized in that: Step five also includes periodically checking the availability of binlog logs and GTID to ensure the continuity and consistency of synchronization; to permanently delete rows marked `is_deleted=1`, you can enable the ClickHouse table setting `allow_experimental_replacing_merge_with_cleanup` and configure `min_age_to_force_merge_seconds` to automatically clean up older deleted rows in the partition, or run it manually: SQL OPTIMIZE TABLE test_db.users FINAL CLEANUP; ```。

Images