Domestic database data real-time synchronization method and system in heterogeneous environment

Abstract

The invention discloses a domestic database data real-time synchronization method and system in a heterogeneous environment, belongs to the technical field of databases, and aims to solve the technical problem of how to realize a low-intrusion and high-compatibility real-time synchronization scheme. Comprising the steps of establishing a log monitoring service of a source database master-slave replication protocol; sending an instruction to a source database through a log monitoring service to obtain a binary log stream, identifying an event in a log, extracting table structure metadata, converting binary data into a JSON structured data change message, and broadcasting the JSON structured data change message to the downstream; the downstream obtains data change messages in a set mode, grouping is carried out with indication as a unit, corresponding execution statements are generated according to SQL grammar rules of a target database, and corresponding execution tasks are generated in each group; and concurrently executing the execution tasks of each group by utilizing the thread pool, executing the execution statements in the same group of tasks according to a sequence, and if the statements which fail to execute exist, marking the corresponding group to be failed.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a method and system for real-time synchronization of domestic database data in a heterogeneous environment. Background Art

[0002] In the current information and communication technology (ICT) innovation context, domestic databases are gradually replacing traditional databases. Whether in the switching and parallel operation of the application ICT innovation environment or based on the requirements of system business, there are multiple parallel database scenarios in heterogeneous environments. Real-time data synchronization between heterogeneous databases has become a difficult point in ICT innovation transformation. Existing mainstream data synchronization solutions usually rely on middleware to achieve data synchronization, but their functions have some limitations: most solutions are only for data transmission between specific databases. In complex heterogeneous environments, multiple different middlewares need to be configured for different database types, which results in high operation and maintenance costs.

[0003] How to achieve a low-intrusion, highly compatible real-time synchronization solution is a technical problem that needs to be solved. Summary of the Invention

[0004] The technical task of the present invention is to address the above shortcomings and provide a method and system for real-time synchronization of domestic database data in a heterogeneous environment to solve the technical problem of how to achieve a low-intrusion, highly compatible real-time synchronization solution.

[0005] In a first aspect, the present invention provides a method for real-time synchronization of domestic database data in a heterogeneous environment, comprising the following steps:

[0006] Register the log monitoring service: Establish a log monitoring service for the source database's master-slave replication protocol and register with the source database as a slave database through a TCP persistent connection.

[0007] Parsing transaction logs: The log monitoring service sends instructions to the source database to obtain binary log streams, identifies Write / Update / Delete events in the logs, extracts table structure metadata, converts the binary data into JSON-structured data change messages, and broadcasts them downstream.

[0008] Message escaping: The downstream obtains data change messages in a set manner, verifies data integrity, splits the original data change messages into transaction units, restores the event execution order, groups them into units of statements, and generates corresponding execution statements based on the SQL syntax rules of the target database. Each group generates a corresponding execution task.

[0009] Data synchronization: Use the thread pool to concurrently execute the execution tasks of each group. The execution statements in the same group of tasks are executed in sequence. If there is a statement that fails to execute, the corresponding group is marked as failed.

[0010] As a preferred option, during the registration log monitoring service process, a slave database identifier is dynamically generated, and the global transaction identifier GTID tracking mechanism is used to record the synchronization progress. The connection activity is maintained through regular heartbeat detection, and the reconnection mechanism is automatically triggered when the network is interrupted. At the same time, a whitelist filtering mechanism is established, which only monitors data operation events of specified databases and tables.

[0011] As a preference, during data synchronization, groups that fail to execute will be subject to different strategies based on the error type, including the following:

[0012] Recoverable errors: An incremental delay retry strategy is adopted with an initial delay time. The delay time doubles with each retry, and the maximum number of retries is a predetermined number. After multiple retries fail, an alarm is triggered and manual intervention is required.

[0013] Structural errors: trigger the automatic correction module, which is then re-executed to extend the field length and add new columns.

[0014] Fatal error: Immediately interrupts and triggers an alarm, requiring manual intervention, with a snapshot of the error context.

[0015] Preferably, during the binary log parsing process, the schema information of the source database table is dynamically loaded, and when the binary data is converted into a JSON structured data change message, the transaction timestamp, operation type and field type metadata are attached to the data change message.

[0016] Preferably, the target database syntax converter adopts a plug-in architecture and supports domestic database dialects; when a field type conflict is detected, type mapping conversion is automatically performed.

[0017] In a second aspect, the present invention provides a real-time data synchronization system for a domestic database in a heterogeneous environment, comprising a registration log monitoring service module, a transaction log parsing module, a message escape module, and a data synchronization module;

[0018] The registration log monitoring service module is used to perform the following operations: establish a log monitoring service for the master-slave replication protocol of the source database, and initiate registration with the source database as a slave database through a TCP persistent connection;

[0019] The transaction log parsing module performs the following operations: sends instructions to the source database through the log monitoring service to obtain the binary log stream, identifies the Write / Update / Delete events in the log, extracts the table structure metadata, converts the binary data into JSON-structured data change messages, and broadcasts them downstream;

[0020] The message escape module is used to perform the following operations: the downstream obtains data change messages in a set manner, verifies data integrity, splits the original data change messages into transaction units, restores the event execution order, groups them into units of statements, and generates corresponding execution statements according to the SQL syntax rules of the target database. Each group generates a corresponding execution task.

[0021] The data synchronization module is used to perform the following: use the thread pool to concurrently execute the execution tasks of each group, and the execution statements in the same group of tasks are executed in sequence. If there is a statement that fails to execute, the corresponding group is marked as failed.

[0022] Preferably, during the registration log monitoring service process, the registration log monitoring service module is used to perform the following: dynamically generate a slave database identifier, use the global transaction identifier GTID tracking mechanism to record the synchronization progress, maintain connection activity through periodic heartbeat detection, automatically trigger the reconnection mechanism when the network is interrupted, and establish a whitelist filtering mechanism at the same time. The whitelist filtering mechanism only monitors data operation events of specified databases and tables.

[0023] Preferably, the group that fails to execute in the data synchronization process module executes different strategies according to the error type, including the following:

[0024] Recoverable errors: An incremental delay retry strategy is adopted with an initial delay time. The delay time doubles with each retry, and the maximum number of retries is a predetermined number. After multiple retries fail, an alarm is triggered and manual intervention is required.

[0025] Structural errors: trigger the automatic correction module, which is then re-executed to extend the field length and add new columns.

[0026] Fatal error: Immediately interrupts and triggers an alarm, requiring manual intervention, with a snapshot of the error context.

[0027] Preferably, during the binary log parsing process, the transaction log parsing module is used to dynamically load the schema information of the source database table, and when converting the binary data into a JSON structured data change message, the transaction timestamp, operation type and field type metadata are attached to the data change message.

[0028] Preferably, the target database syntax converter adopts a plug-in architecture and supports domestic database dialects; when a field type conflict is detected, type mapping conversion is automatically performed.

[0029] The method and system for real-time synchronization of domestic database data in a heterogeneous environment of the present invention have the following advantages:

[0030] 1. High compatibility: This method has high scalability and can not only synchronize data with DAMO and HANGO databases, but also support other domestic databases;

[0031] 2. Low intrusion: This method uses an independent deployment service for data synchronization. It does not rely on other middleware and can be quickly integrated into existing database heterogeneous environments through pluggable deployment. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following briefly introduces the drawings required for use in the embodiments or descriptions of the prior art. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0033] The present invention will be further described below with reference to the accompanying drawings.

[0034] Figure 1 This is a flowchart of a method for real-time synchronization of domestic database data in a heterogeneous environment in Example 1. DETAILED DESCRIPTION

[0035] The present invention will be further described below with reference to the accompanying drawings and specific embodiments so that those skilled in the art can better understand the present invention and implement it. However, the embodiments given are not intended to limit the present invention. Unless there is a conflict, the embodiments of the present invention and the technical features in the embodiments may be combined with each other.

[0036] The embodiments of the present invention provide a method and system for real-time synchronization of domestic database data in a heterogeneous environment, which are used to solve the technical problem of how to achieve a low-intrusion, highly compatible real-time synchronization solution.

[0037] Example 1:

[0038] The present invention provides a real-time data synchronization method for a domestic database in a heterogeneous environment, which includes four steps: registering a log monitoring service, parsing a transaction log, escaping a message, and synchronizing the data.

[0039] Step S100 registers the log monitoring service: establishes the log monitoring service of the master-slave replication protocol of the source database, and initiates registration with the source database as a slave database through a TCP long connection.

[0040] As a specific implementation, during the registration log monitoring service process, the slave database identifier is dynamically generated, the global transaction identifier GTID tracking mechanism is used to record the synchronization progress, the connection activity is maintained through regular heartbeat detection, and the reconnection mechanism is automatically triggered when the network is interrupted. At the same time, a whitelist filtering mechanism is established, which only monitors data operation events of specified databases and tables.

[0041] This step establishes a log monitoring service based on the MySQL master-slave replication protocol, and registers with the source database as a slave through a TCP persistent connection. During the registration process, the slave identifier (server_id) is dynamically generated, and the synchronization progress is recorded using the GTID (Global Transaction Identifier) ​​tracking mechanism. Connection activity is maintained through periodic heartbeat detection (COM_PING instructions are sent every 30 seconds), and a reconnection mechanism is automatically triggered when the network is interrupted (maximum retries of 5 times). A whitelist filtering mechanism is also established to monitor only data operation events in specified databases and tables. The heartbeat detection interval is 30 seconds, and the connection activity is maintained through the COM_PING instruction.

[0042] Step S200 parses the transaction log: sends instructions to the source database through the log monitoring service to obtain the binary log stream, identifies the Write / Update / Delete events in the log, extracts the table structure metadata, converts the binary data into JSON structured data change messages and broadcasts them downstream.

[0043] During binary log parsing, the schema information of the source database table is dynamically loaded. When converting binary data into JSON-structured data change messages, the transaction timestamp, operation type, and field type metadata are appended to the data change messages.

[0044] In this step, the monitoring service sends the COM_BINLOG_DUMP command to the MySQL master database to continuously obtain the binary log stream. It identifies the Write / Update / Delete events in the log, extracts the table structure metadata (database name, table name), and converts the binary data into a JSON structured message to broadcast downstream.

[0045] Step S300: Message escape: The downstream obtains the data change message in a set manner, verifies the data integrity, splits the original data change message according to the transaction unit, and restores the event execution order, groups it into units, and generates the corresponding execution statement according to the SQL syntax rules of the target database. Each group generates a corresponding execution task.

[0046] The downstream of this step can obtain data change messages through message queues, TCP connections, shared files, etc. After verifying the data integrity, the original messages are split according to transaction units, the event execution order is restored, and the data is grouped by table name. The corresponding statements are generated according to the SQL syntax rules of DAMO and Hangao, and each group generates a corresponding execution task.

[0047] The target database syntax converter adopts a plug-in architecture and supports domestic database dialects. When a field type conflict is detected, it automatically performs type mapping conversion, such as converting MySQL's DATETIME to DAMO's TIMESTAMP(6) and TEXT type to Hangao's CLOB.

[0048] Step S400: Data synchronization: Use the thread pool to concurrently execute the execution tasks of each group. The execution statements in the same group of tasks are executed in sequence. If there is a statement that fails to execute, the corresponding group is marked as failed.

[0049] During data synchronization, groups that fail to execute the policy will be subject to different policies based on the error type, including the following:

[0050] (1) Recoverable error (error code 2013 / 1213): An incremental delay retry strategy is adopted, with an initial delay time of 100ms. The delay time doubles with each retry, and the maximum number of retries is 3. If multiple retries still fail, an alarm is triggered and manual intervention is required;

[0051] (2) Structural errors (such as field length overflow, missing columns): trigger the automatic correction module (such as extending the field length, adding new columns), and re-execute after correction. The automatic correction module performs the operations of extending the field length and adding new columns;

[0052] (3) Fatal error (e.g., table does not exist): Immediate interruption and triggering of an alarm, requiring manual intervention, with an error context snapshot attached.

[0053] During implementation, recoverable errors include connection timeout (error code 2013) and deadlock (error code 1213). An incremental delay retry strategy is employed: an initial delay of 100ms doubles with each retry, for a maximum of three retries. The automatic correction module includes a field expansion strategy: when a target database field is insufficient in length, an ALTER TABLE MODIFYCOLUMN operation is automatically executed to extend the field length to 1.2 times the source field length. When a new field is detected, an ALTER TABLE ADD COLUMN operation is automatically executed in the target database. When an index is detected as missing, an alert report is generated but no index creation is performed.

[0054] The method of this embodiment provides a real-time synchronization method for domestic database data in a heterogeneous environment through operations such as registering a log monitoring service, parsing transaction logs, assembling message packets, broadcasting messages, and synchronizing data. It overcomes the problems that the current data synchronization middleware has a narrow scope of application and can only synchronize certain specific databases and cannot perform real-time synchronization.

[0055] Example 2:

[0056] The present invention provides a real-time synchronization system for domestic database data in a heterogeneous environment, comprising a registration log monitoring service module, a transaction log parsing module, a message escape module and a data synchronization module.

[0057] The registration log monitoring service module is used to perform the following: establish a log monitoring service for the master-slave replication protocol of the source database, and initiate registration with the source database (MySQL) as a slave database through a TCP long connection.

[0058] During the registration log monitoring service process, the registration log monitoring service module is used to perform the following: dynamically generate slave database identifiers, use the global transaction identifier GTID tracking mechanism to record synchronization progress, maintain connection activity through periodic heartbeat detection, automatically trigger the reconnection mechanism when the network is interrupted, and establish a whitelist filtering mechanism. The whitelist filtering mechanism only monitors data operation events of specified databases and tables.

[0059] This module establishes a log monitoring service based on the MySQL master-slave replication protocol, registering with the source database as a slave through a TCP persistent connection. During the registration process, the slave identifier (server_id) is dynamically generated, and the GTID (Global Transaction Identifier) ​​tracking mechanism is used to record the synchronization progress. Connection activity is maintained through periodic heartbeat detection (COM_PING command is sent every 30 seconds), and a reconnection mechanism is automatically triggered when the network is disconnected (maximum 5 retries). A whitelist filtering mechanism is also established to monitor only data operation events in specified databases and tables. The heartbeat detection interval is 30 seconds, and the COM_PING command is used to maintain connection activity.

[0060] The transaction log parsing module performs the following operations: sends instructions to the source database through the log monitoring service to obtain the binary log stream, identifies the Write / Update / Delete events in the log, extracts the table structure metadata, and converts the binary data into JSON-structured data change messages for downstream broadcasting.

[0061] During binary log parsing, the transaction log parsing module is used to dynamically load the schema information of the source database table. When converting binary data into JSON-structured data change messages, the transaction timestamp, operation type, and field type metadata are appended to the data change messages.

[0062] This module monitors the service sending the COM_BINLOG_DUMP command to the MySQL master database to continuously obtain the binary log stream. It identifies Write / Update / Delete events in the log, extracts table structure metadata (database name, table name), and converts the binary data into a JSON structured message for downstream broadcasting.

[0063] The message escape module is used to perform the following: the downstream obtains data change messages in a set manner, verifies the data integrity, splits the original data change messages according to transaction units, and restores the event execution order, grouping them in units of statements. The corresponding execution statements are generated according to the SQL syntax rules of the target database, and each group generates a corresponding execution task.

[0064] The target database syntax converter adopts a plug-in architecture and supports domestic database dialects; when a field type conflict is detected, type mapping conversion is automatically performed.

[0065] The downstream of this module can obtain data change messages through message queues, TCP connections, shared files, etc. After verifying the data integrity, it splits the original message by transaction unit, restores the event execution order, groups them by table name, and generates corresponding statements according to the SQL syntax rules of DAMO and Hangao. Each group generates corresponding execution tasks.

[0066] The target database syntax converter adopts a plug-in architecture and supports domestic database dialects. When a field type conflict is detected, it automatically performs type mapping conversion, such as converting MySQL's DATETIME to DAMO's TIMESTAMP(6) and TEXT type to Hangao's CLOB.

[0067] The data synchronization module is used to perform the following: use the thread pool to concurrently execute the execution tasks of each group, and the execution statements in the same group of tasks are executed in sequence. If there is a statement that fails to execute, the corresponding group is marked as failed.

[0068] The groups that failed to execute in the data synchronization process module execute different strategies based on the error type, including the following:

[0069] (1) Recoverable error (error code 2013 / 1213): An incremental delay retry strategy is adopted, with an initial delay time of 100ms. The delay time doubles with each retry, and the maximum number of retries is 3. If multiple retries still fail, an alarm is triggered and manual intervention is required;

[0070] (2) Structural errors (such as field length overflow, missing columns): trigger the automatic correction module (such as extending the field length, adding new columns), and re-execute after correction. The automatic correction module performs the operations of extending the field length and adding new columns;

[0071] (3) Fatal error (e.g., table does not exist): Immediate interruption and triggering of an alarm, requiring manual intervention, with an error context snapshot attached.

[0072] In the implementation process, the recoverable error includes connection timeout (error code 2013) and deadlock (error code 1213), and an incremental delay retry strategy is adopted: the initial delay is 100 ms, the delay time is doubled each time, and the maximum retry is 3 times; the automatic correction module includes a field expansion strategy: when the target database field length is insufficient, the ALTER TABLE MODIFY COLUMN is automatically executed to expand the field length to 1.2 times of the source field. When a new field is detected, the ALTER TABLE ADD COLUMN operation is automatically executed in the target database; when an index is missing, a warning report is generated but not automatically created.

[0073] The system of the embodiment can execute the method disclosed in embodiment 1 to realize real-time synchronization of domestic database data in a heterogeneous environment.

[0074] The real-time synchronization method and system of domestic database data in a heterogeneous environment provided by the present application are described in detail above, and the principles and implementation modes of the present application are described by applying specific examples; the above embodiment is only used to help understand the method of the present application and its core idea; at the same time, for those skilled in the art, according to the idea of the present application, the specific implementation mode and application range will be changed; in view of the above, the content of the specification should not be understood as a limitation of the present application.

Claims

1. A method for real-time synchronization of domestic database data in a heterogeneous environment, characterized in that: The steps include: Register the log monitoring service: Establish a log monitoring service for the source database's master-slave replication protocol and register with the source database as a slave database through a TCP persistent connection. Parsing transaction logs: The log monitoring service sends instructions to the source database to obtain binary log streams, identifies Write / Update / Delete events in the logs, extracts table structure metadata, converts the binary data into JSON-structured data change messages, and broadcasts them downstream. Message escaping: The downstream obtains data change messages in a set manner, verifies data integrity, splits the original data change messages into transaction units, restores the event execution order, groups them into units of statements, and generates corresponding execution statements based on the SQL syntax rules of the target database. Each group generates a corresponding execution task. Data synchronization: Use the thread pool to concurrently execute the execution tasks of each group. The execution statements in the same group of tasks are executed in sequence. If there is a statement that fails to execute, the corresponding group is marked as failed.

2. The method for real-time synchronization of domestic database data in a heterogeneous environment according to claim 1 is characterized in that: During the registration of the log monitoring service, the slave database identifier is dynamically generated, and the global transaction identifier GTID tracking mechanism is used to record the synchronization progress. The connection activity is maintained through regular heartbeat detection. When the network is interrupted, the reconnection mechanism is automatically triggered. At the same time, a whitelist filtering mechanism is established. The whitelist filtering mechanism only monitors data operation events of specified databases and tables.

3. The method for real-time synchronization of domestic database data in a heterogeneous environment according to claim 1 is characterized in that: During data synchronization, groups that fail to execute the policy will be subject to different policies based on the error type, including the following: Recoverable errors: An incremental delay retry strategy is adopted with an initial delay time. The delay time doubles with each retry, and the maximum number of retries is a predetermined number. After multiple retries fail, an alarm is triggered and manual intervention is required. Structural errors: trigger the automatic correction module, which is then re-executed to extend the field length and add new columns. Fatal error: Immediately interrupts and triggers an alarm, requiring manual intervention, with a snapshot of the error context.

4. The method for real-time synchronization of domestic database data in a heterogeneous environment according to claim 1 is characterized in that: During binary log parsing, the schema information of the source database table is dynamically loaded. When converting binary data into JSON-structured data change messages, the transaction timestamp, operation type, and field type metadata are appended to the data change messages.

5. The method for real-time synchronization of domestic database data in a heterogeneous environment according to claim 1 is characterized in that: The target database syntax converter adopts a plug-in architecture and supports domestic database dialects; when a field type conflict is detected, type mapping conversion is automatically performed.

6. A real-time synchronization system for domestic database data in a heterogeneous environment, characterized by: Including registration log monitoring service module, transaction log parsing module, message escape module and data synchronization module; The registration log monitoring service module is used to perform the following operations: establish a log monitoring service for the master-slave replication protocol of the source database, and initiate registration with the source database as a slave database through a TCP persistent connection; The transaction log parsing module performs the following operations: sends instructions to the source database through the log monitoring service to obtain the binary log stream, identifies the Write / Update / Delete events in the log, extracts the table structure metadata, converts the binary data into JSON-structured data change messages, and broadcasts them downstream; The message escape module is used to perform the following operations: the downstream obtains data change messages in a set manner, verifies data integrity, splits the original data change messages into transaction units, restores the event execution order, groups them into units of statements, and generates corresponding execution statements according to the SQL syntax rules of the target database. Each group generates a corresponding execution task. The data synchronization module is used to perform the following: use the thread pool to concurrently execute the execution tasks of each group, and the execution statements in the same group of tasks are executed in sequence. If there is a statement that fails to execute, the corresponding group is marked as failed.

7. The real-time synchronization system for domestic database data in a heterogeneous environment according to claim 6 is characterized in that: During the registration log monitoring service process, the registration log monitoring service module is used to perform the following: dynamically generate slave database identifiers, use the global transaction identifier GTID tracking mechanism to record synchronization progress, maintain connection activity through periodic heartbeat detection, automatically trigger the reconnection mechanism when the network is interrupted, and establish a whitelist filtering mechanism. The whitelist filtering mechanism only monitors data operation events of specified databases and tables.

8. The real-time synchronization system for domestic database data in a heterogeneous environment according to claim 6 is characterized in that: The groups that failed to execute in the data synchronization process module execute different strategies based on the error type, including the following: Recoverable errors: An incremental delay retry strategy is adopted with an initial delay time. The delay time doubles with each retry, and the maximum number of retries is a predetermined number. After multiple retries fail, an alarm is triggered and manual intervention is required. Structural errors: trigger the automatic correction module, which is then re-executed to extend the field length and add new columns. Fatal error: Immediately interrupts and triggers an alarm, requiring manual intervention, with a snapshot of the error context.

9. The real-time synchronization system for domestic database data in a heterogeneous environment according to claim 6 is characterized in that: During binary log parsing, the transaction log parsing module is used to dynamically load the schema information of the source database table. When converting binary data into JSON-structured data change messages, the transaction timestamp, operation type, and field type metadata are appended to the data change messages.

10. The real-time synchronization system for domestic database data in a heterogeneous environment according to claim 6 is characterized in that: The target database syntax converter adopts a plug-in architecture and supports domestic database dialects; when a field type conflict is detected, type mapping conversion is automatically performed.

Images