Method and system for realizing data synchronization between heterogeneous databases based on logs

Abstract

The invention discloses a method and a system for realizing data synchronization between heterogeneous databases based on logs. The method comprises the following steps: acquiring connection information of a source database and a target database; querying a table structure of the source end database and generating an SQL statement to establish a data mapping rule; matching a corresponding rule model from a mapping rule model library; if the matching is not successful, the system abnormally quits and prompts the user to create a new mapping model and add the new mapping model to the mapping rule model base; converting a table structure of the source end database into a JSON character string based on a standard field type; an SQL statement required by the target database is output through the data mapping engine module; the message monitoring program reads the data in the temporary storage area and converts the data into data meeting the requirements of the target database; formatting the converted data into SQL statements supported by a target database; and sending the generated SQL statement to a target database to complete data migration. Accurate and controllable synchronization of the data and the model in a heterogeneous environment is realized.

Description

Technical Field

[0001] The present invention relates to the technical field of database processing, and in particular to a method and system for realizing data synchronization between heterogeneous databases based on logs. Background Art

[0002] Common heterogeneous database synchronization technologies are as follows: CDC data synchronization: is an efficient data integration technology designed to identify and capture changes in data in the database in real time, including insert, update, and delete operations. CDC can not only monitor data changes, but also fully record these changes in the order in which they occur, and transmit them to downstream systems or processes in real time. This approach can significantly improve the timeliness of data synchronization, ensure the consistency and accuracy of data between different systems, and is widely used in real-time data analysis, data warehouse updates, and data management in microservice architectures. The problem with CDC data synchronization is that first, the source database needs to be authorized and the relevant permissions opened in order to successfully capture data changes. Secondly, different types of databases and versions may have limitations, especially some early versions may not support CDC functions. In addition, not all types of databases are suitable for CDC synchronization, so caution is needed when choosing a database. Finally, it is worth noting that some database types only support data manipulation language (DML) operations, but not data definition language (DDL) operations, which means that CDC may not meet business needs when the table structure changes.

[0003] DataX: It is a powerful data synchronization tool independently developed by Alibaba, which aims to achieve efficient data transmission between heterogeneous data sources. It supports multiple types of databases and data storage systems, and can flexibly configure data reading and writing methods. With its plug-in architecture, users can expand functions according to their needs to meet complex data integration needs. DataX performs well in scenarios such as data migration, scheduled tasks, and real-time synchronization, and is an important tool for enterprises to achieve data fusion and integration. The problem with DataX is that: first, synchronization tasks need to be manually configured, and there is a lack of automation capabilities; second, this method cannot directly perceive deletion operations in the source table, and data changes beyond the query scope cannot be captured. In addition, slow queries will cause the performance of the database server to degrade, affect the execution speed of other queries, and increase query response time, thereby slowing down the performance of the entire system. Summary of the invention

[0004] The purpose of the present invention is to provide a method and system for realizing data synchronization between heterogeneous databases based on logs, so as to ensure smooth synchronization between different databases and effectively solve the problem of accurate and controllable synchronization of data and models in a heterogeneous environment.

[0005] The technical solution adopted by the present invention is: A method for realizing data synchronization between heterogeneous databases based on logs comprises the following steps: Step 1, respectively obtain the connection information of the source database and the target database; Furthermore, step 1 extracts the operation through the log file on the source database side and reversely generates the SQL execution script, which specifically includes the following steps: Step 1-1, Identify and read log files: Identify the log files of the source database and use the corresponding tools to read the log contents according to the database type.

[0006] Specifically, the log file structures of different database systems are different, such as MySQL's binary log (binlog) and Oracle's redo log (redo log). First, you must identify the log files of the source database and use appropriate tools to read the log contents according to the database type.

[0007] Step 1-2, parsing log files: according to the log formats of different databases, corresponding parsing tools should be used to extract and parse the operation information recorded in the log files into readable database operations; Specifically, the operations recorded in the log files need to be parsed into readable database operations, such as insert, update, delete, etc. For the log formats of different databases, corresponding parsing tools (such as MySQL's mysqlbinlog or Oracle's LogMiner) need to be used to extract the operation information.

[0008] Step 1-3, reverse generation of SQL script: from the parsed log information, convert each database operation into a corresponding SQL statement; Specifically, from the parsed log information, each database operation is converted into a corresponding SQL statement (such as INSERT, UPDATE, DELETE). At the same time, the transaction order and consistency need to be guaranteed to ensure that the original operation can be reproduced when executing these SQL scripts.

[0009] Step 2: Obtain all table definitions and metadata by querying the table structure of the source database, and generate SQL statements to establish data mapping rules; Furthermore, step 2 specifically includes the following steps: Step 2-1, extract table structure: Use SQL statements or system views to extract metadata of the source table, including column names, data types, indexes, and constraints; Specifically, use appropriate SQL statements (such as DESCRIBE or SHOW CREATE TABLE in MySQL, \d in PostgreSQL) or system views (such as INFORMATION_SCHEMA) to extract metadata of the source table, including column names, data types, indexes, constraints, and so on.

[0010] Step 2-2, SQL statement generation: Generate SQL statements compatible with the target database based on the obtained source table structure; For example, convert the NUMBER type in the Oracle database to DECIMAL in MySQL to ensure that the table creation can be performed correctly in the target database.

[0011] Step 2-3, create data mapping rule identifier: create a mapping rule identifier for the structure and data type of each table as a reference for data mapping; Specifically, the mapping rules should include the field correspondence between the source and target ends, the conversion logic of data types, and the requirements for special data processing (such as character set conversion, date format conversion, etc.).

[0012] Step 3: According to the established mapping rule identifier, determine whether the corresponding rule model can be matched in the mapping rule model library; if yes, execute step 5; otherwise, execute step 4; Specifically, the rule model defines the structure and data type mapping between the source database and the target database, ensuring that data conversion can be performed automatically during the migration process.

[0013] Furthermore, step 3 specifically includes the following steps: Step 3-1, query the model library: query the existing mapping models in the mapping rule model library through the mapping rule identifier, and try to find the corresponding structure mapping and data conversion rules.

[0014] Step 3-2, model matching: Return the matching model and verify it to ensure that the model is consistent with the current migration scenario.

[0015] Step 4: The system exits abnormally and prompts the user to create a new mapping model through the data type mapping module, and then saves the mapping model created by the user to the mapping rule model library; Specifically, step 4 is divided into two parts: exception handling and model update. Exception handling: If the model is empty, the system should exit abnormally and prompt the user to create a new mapping model through the data type mapping module. The newly created mapping model includes the mapping rules that define the source database fields and the target database fields, as well as the conversion logic of the data type. Model update: Save the mapping model created by the user to the mapping rule model library for future use. This operation ensures that the model library is gradually expanded and improved, improving the efficiency and automation of future migrations.

[0016] Step 5: Convert the table structure of the source database into a JSON string based on the standard field type according to the mapping rules; the JSON string contains the metadata and field attributes of the table, and uses the field data in the form of an array as a sub-attribute of the table metadata.

[0017] Furthermore, step 5 specifically includes the following steps: Step 5-1, metadata extraction: Extract basic information of the table, such as table name, column name, data type, index, primary key and foreign key, etc.

[0018] Step 5-2, standardize field types: Convert the field types of the source database to intermediate standard types (such as converting MySQL INT to universal INTEGER) to maintain consistency when migrating to different database systems.

[0019] Step 5-3, Convert to JSON string: Generate a JSON string containing field type, attribute, and index information based on the extracted metadata. The corresponding JSON string will be used as the data model processed during the migration process.

[0020] Step 6: Based on the table structure information in JSON format, the data mapping engine module outputs the SQL statements required by the target database and temporarily stores the converted data; Further, step 6 includes the following steps: Step 6-1, data conversion: According to the mapping rules and JSON structure, convert the source data into the format supported by the target database. For example, convert the date from the YYYY-MM-DD format of the source database to the standard format of the target database.

[0021] Step 6-2, temporary storage: Store the converted data in a temporary storage area for safe transmission and processing during data migration. The temporary storage area serves as a data transfer station to help ensure the integrity and traceability of the data during the migration process.

[0022] Step 7: The message listening program reads the data in the temporary storage area and converts the information into data that meets the requirements of the target database through the mapping rule model library; Specifically, the message listener on the migration platform is responsible for monitoring changes in the temporary storage area. When a new data message is detected, the data in the storage area is automatically read and combined with the metadata information of the target database, and the information is further converted through the mapping rule model library.

[0023] The key to this step is: Data monitoring: The message monitoring program continuously monitors the data changes in the temporary storage area to ensure that it can respond to the arrival of new data in a timely manner. Information conversion: According to the metadata of the target database (such as data type, field name, etc.), the data is converted again through the mapping rule model library to ensure that the data fully meets the requirements of the target database.

[0024] Step 8: Call the data mapping engine to format the converted data into SQL statements supported by the target database; the data mapping engine sends the generated SQL statements to the target database to complete the final data migration; Further, step 8 includes the following steps: Step 8-1, SQL statement generation: The data mapping engine generates corresponding SQL statements such as INSERT, UPDATE or CREATE TABLE according to the requirements of the target database.

[0025] Step 8-2, Execute SQL: Execute the generated SQL statements into the target database to ensure that the data and structure are fully synchronized in the target system.

[0026] The present invention also discloses a system for realizing data synchronization between heterogeneous databases based on logs, including a metadata module, a data type mapping module, a mapping rule model library module and a data mapping engine module; Metadata module: responsible for managing the table structure, index and constraint rules in the source and target databases; the metadata module realizes the abstract processing of different database table structures through a unified intermediate standard field type; Data type mapping module: responsible for supporting the mapping conversion between native data types and intermediate standard field types in different databases; the data type mapping module realizes the conversion between source database type and target database type through a set of predefined mapping tables; Mapping rule model library module: used to store all common data mapping rule models in the system; the rule model not only defines the mapping relationship between the data type and structure of the source database and the target database, but also contains various additional information required in the data migration process, including the mapping rules of indexes, foreign keys, and primary keys; Data mapping engine module: responsible for normalizing the extracted source data through mapping rules and outputting formatted data that meets the requirements of the target database; the data mapping engine parses the data structure and type of the source database according to the content of the mapping rule model library and converts it into a format acceptable to the target database; Furthermore, the mapping engine uses JSON as a standard data packet format to define the basic properties of table metadata and field data. The metadata of each table is represented by a JSON object, and the field data is a sub-attribute of the array. In this way, the mapping engine can effectively transfer data between the native attributes of the database and the intermediate standard field types.

[0027] The present invention adopts the above technical solution and has the following technical advantages: 1. Data consistency: By adapting the metadata and field attributes of different database systems, it is ensured that the consistency of data is maintained during the data migration and integration process. This can effectively avoid data loss or errors caused by data type mismatch or structural differences. 2. Improve data migration efficiency: The standardized and automated metadata adaptation process can greatly improve the efficiency of data migration. By establishing mapping rules and models, the migration platform can quickly convert the structure and data type of the source database to the format required by the target database, reducing manual intervention and configuration time. 3. Reduce system integration complexity: Adapting the metadata and field attributes of different databases can simplify the complexity of system integration. Through unified mapping rules, developers and database administrators can more easily understand and manage the integration process across multiple databases. 4. Enhance data flexibility: In a heterogeneous database environment, data flexibility is enhanced. Through the definition of intermediate standard field types, users can flexibly define and extend field attributes according to business needs, making the data structure more adaptable. 5. Reduce maintenance costs: By implementing the adaptation of metadata and field attributes, the maintenance costs caused by changes in database systems can be reduced. When the database architecture needs to be modified or updated, the adaptation mechanism can automatically adjust the mapping rules to reduce the workload of manual maintenance.

[0028] The present invention focuses on the metadata and data field synchronization method and system of heterogeneous databases, ensuring smooth synchronization between different databases and effectively solving the problem of accurate and controllable synchronization of data and models in heterogeneous environments. The present invention not only improves data consistency, but also significantly simplifies the data management process, further optimizing the overall performance and maintenance efficiency of the system. BRIEF DESCRIPTION OF THE DRAWINGS

[0029] The present invention is further described in detail below with reference to the accompanying drawings and specific embodiments; Figure 1 The present invention is a flowchart of a method for realizing data synchronization between heterogeneous databases based on logs. DETAILED DESCRIPTION

[0030] In order to make the purpose, technical solutions and advantages of the embodiments of the present application clearer, the technical solutions in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application.

[0031] like Figure 1 As shown, the present invention discloses a method for realizing data synchronization between heterogeneous databases based on logs, which comprises the following steps: Step 1, respectively obtain the connection information of the source database and the target database; Furthermore, step 1 extracts the operation through the log file on the source database side and reversely generates the SQL execution script, which specifically includes the following steps: Step 1-1, Identify and read log files: Identify the log files of the source database and use the corresponding tools to read the log contents according to the database type.

[0032] Specifically, the log file structures of different database systems are different, such as MySQL's binary log (binlog) and Oracle's redo log (redo log). First, you must identify the log files of the source database and use appropriate tools to read the log contents according to the database type.

[0033] Step 1-2, parsing log files: according to the log formats of different databases, corresponding parsing tools should be used to extract and parse the operation information recorded in the log files into readable database operations; Specifically, the operations recorded in the log files need to be parsed into readable database operations, such as insert, update, delete, etc. For the log formats of different databases, corresponding parsing tools (such as MySQL's mysqlbinlog or Oracle's LogMiner) need to be used to extract the operation information.

[0034] Step 1-3, reverse generation of SQL script: from the parsed log information, convert each database operation into a corresponding SQL statement; Specifically, from the parsed log information, each database operation is converted into a corresponding SQL statement (such as INSERT, UPDATE, DELETE). At the same time, the transaction order and consistency need to be guaranteed to ensure that the original operation can be reproduced when executing these SQL scripts.

[0035] Step 2: Obtain all table definitions and metadata by querying the table structure of the source database, and generate SQL statements to establish data mapping rules; Furthermore, step 2 specifically includes the following steps: Step 2-1, extract table structure: Use SQL statements or system views to extract metadata of the source table, including column names, data types, indexes, and constraints; Specifically, use appropriate SQL statements (such as DESCRIBE or SHOW CREATE TABLE in MySQL, \d in PostgreSQL) or system views (such as INFORMATION_SCHEMA) to extract metadata of the source table, including column names, data types, indexes, constraints, and so on.

[0036] Step 2-2, SQL statement generation: Generate SQL statements compatible with the target database based on the obtained source table structure; For example, convert the NUMBER type in the Oracle database to DECIMAL in MySQL to ensure that the table creation can be performed correctly in the target database.

[0037] Step 2-3, create data mapping rule identifier: create a mapping rule identifier for the structure and data type of each table as a reference for data mapping; Specifically, the mapping rules should include the field correspondence between the source and target ends, the conversion logic of data types, and the requirements for special data processing (such as character set conversion, date format conversion, etc.).

[0038] Step 3: According to the established mapping rule identifier, determine whether the corresponding rule model can be matched in the mapping rule model library; if yes, execute step 5; otherwise, execute step 4; Specifically, the rule model defines the structure and data type mapping between the source database and the target database, ensuring that data conversion can be performed automatically during the migration process.

[0039] Furthermore, step 3 specifically includes the following steps: Step 3-1, query the model library: query the existing mapping models in the mapping rule model library through the mapping rule identifier, and try to find the corresponding structure mapping and data conversion rules.

[0040] Step 3-2, model matching: Return the matching model and verify it to ensure that the model is consistent with the current migration scenario.

[0041] Step 4: The system exits abnormally and prompts the user to create a new mapping model through the data type mapping module, and then saves the mapping model created by the user to the mapping rule model library; Specifically, step 4 is divided into two parts: exception handling and model update. Exception handling: If the model is empty, the system should exit abnormally and prompt the user to create a new mapping model through the data type mapping module. The newly created mapping model includes the mapping rules that define the source database fields and the target database fields, as well as the conversion logic of the data type. Model update: Save the mapping model created by the user to the mapping rule model library for future use. This operation ensures that the model library is gradually expanded and improved, improving the efficiency and automation of future migrations.

[0042] Step 5: Convert the table structure of the source database into a JSON string based on the standard field type according to the mapping rules; the JSON string contains the metadata and field attributes of the table, and uses the field data in the form of an array as a sub-attribute of the table metadata.

[0043] Furthermore, step 5 specifically includes the following steps: Step 5-1, metadata extraction: Extract basic information of the table, such as table name, column name, data type, index, primary key and foreign key, etc.

[0044] Step 5-2, standardize field types: Convert the field types of the source database to intermediate standard types (such as converting MySQL INT to universal INTEGER) to maintain consistency when migrating to different database systems.

[0045] Step 5-3, Convert to JSON string: Generate a JSON string containing field type, attribute, and index information based on the extracted metadata. The corresponding JSON string will be used as the data model processed during the migration process.

[0046] Step 6: Based on the table structure information in JSON format, the data mapping engine module outputs the SQL statements required by the target database and temporarily stores the converted data; Further, step 6 includes the following steps: Step 6-1, data conversion: According to the mapping rules and JSON structure, convert the source data into the format supported by the target database. For example, convert the date from the YYYY-MM-DD format of the source database to the standard format of the target database.

[0047] Step 6-2, temporary storage: Store the converted data in a temporary storage area for safe transmission and processing during data migration. The temporary storage area serves as a data transfer station to help ensure the integrity and traceability of the data during the migration process.

[0048] Step 7: The message listening program reads the data in the temporary storage area and converts the information into data that meets the requirements of the target database through the mapping rule model library; Specifically, the message listener on the migration platform is responsible for monitoring changes in the temporary storage area. When a new data message is detected, the data in the storage area is automatically read and combined with the metadata information of the target database, and the information is further converted through the mapping rule model library.

[0049] The key to this step is: Data monitoring: The message monitoring program continuously monitors the data changes in the temporary storage area to ensure that it can respond to the arrival of new data in a timely manner. Information conversion: According to the metadata of the target database (such as data type, field name, etc.), the data is converted again through the mapping rule model library to ensure that the data fully meets the requirements of the target database.

[0050] Step 8: Call the data mapping engine to format the converted data into SQL statements supported by the target database; the data mapping engine sends the generated SQL statements to the target database to complete the final data migration; Further, step 8 includes the following steps: Step 8-1, SQL statement generation: The data mapping engine generates corresponding SQL statements such as INSERT, UPDATE or CREATE TABLE according to the requirements of the target database.

[0051] Step 8-2, Execute SQL: Execute the generated SQL statements into the target database to ensure that the data and structure are fully synchronized in the target system.

[0052] In summary, in the migration process of heterogeneous databases, business data migration is closely related to metadata migration. The migration of business data also needs to follow strict mapping rules to ensure that the business logic and data in the source database can be accurately restored in the target database. Business data migration process: The migration platform obtains SQL statements related to business operations from the source database. These statements may involve data operations such as insert, update, and delete. Subsequently, the platform parses and converts these SQL statements through the mapping rule model library, mapping the syntax and structure of the source database to a standard intermediate format. Mapping and conversion: After completing the intermediate format conversion, the data mapping engine intervenes to convert the data in the intermediate standard format into the SQL statements required by the target database by matching the mapping rules. The SQL statements processed by the engine meet the data type and structure requirements of the target database while ensuring that the business logic is accurately transmitted. Finally, the system executes these SQL statements to migrate the business data to the target database and complete the data migration process.

[0053] The specific principle of the present invention is described in detail below: A complete heterogeneous database mapping system should consist of multiple core modules, covering database table structure, data type mapping, mapping rule definition and data normalization processing. The main modules of the system include metadata module, data type mapping module, mapping rule model library module and data mapping engine module, and use JSON format to define the metadata of the table and the attributes of the field.

[0054] Metadata module: It is one of the core parts of the system and is responsible for managing the table structure, indexes and constraint rules in the source and target databases. In heterogeneous database systems, the metadata structure of each database is different, and the main function of the metadata module is to abstract the table structures of different databases through a unified intermediate standard field type. This not only ensures the consistency of the data structure during database migration, but also provides a basis for subsequent data conversion.

[0055] The functions of the metadata module include the following aspects: (1) Extracting database table structure: The metadata module obtains detailed information of the table from the source database, including structured information such as columns, data types, indexes, constraints, primary keys, and foreign keys. (2) Structural conversion: By establishing a mapping with the intermediate standard field type, the native table structure of different databases is converted into a unified standard structure. This mapping ensures that no matter what system the source database uses (such as MySQL, PostgreSQL, SQL Server, Oracle, etc.), its table structure can be abstracted into an intermediate format. (3) Intermediate standard field type definition: The standard field type maps the native fields of different databases to a unified set of types. For example, MySQL's VARCHAR and Oracle's VARCHAR2 are mapped to a unified String type. In order to ensure the commonality between heterogeneous databases, the metadata module relies on intermediate standard field types. This design allows effective interaction and data conversion between different database systems, thereby simplifying the data integration process and ensuring consistency and accuracy during data transmission and synchronization. By using standard field types, the system can better handle the differences between various data sources, improve the efficiency and flexibility of data integration, and meet diverse business needs.

[0056] Data type mapping module: In different database systems, the representation and syntax rules of data types may be completely different. The data type mapping module is responsible for supporting the mapping conversion between native data types and intermediate standard field types in different databases. It ensures seamless conversion of data types between heterogeneous databases through a set of standardized rules.

[0057] The data type mapping module uses a set of predefined mapping tables to achieve the conversion between the source database type and the target database type. For example: (1) String type mapping: Map MySQL's VARCHAR to the intermediate standard field String type, and determine the length and max_length attributes based on the length. (3) Date type mapping: Map SQLServer's DATETIME to the intermediate standard field Time type, and the extended attribute with_timezone can be set based on whether the source database field has a time zone. (3) Floating point type mapping: Map Oracle's NUMBER to the Float type, and set the M and D attributes based on the precision and number of digits.

[0058] In order to cope with the type differences that may occur between different databases, the data type mapping module also supports attribute extension. For example: (1) Character type extension: For variable-length character types (such as VARCHAR and TEXT), the attribute is used to control whether it is a variable-length type. (2) Binary data extension: For binary types (such as BLOB and CLOB), the storage capacity of the field is controlled. (3) Floating-point number extension: The precision of floating-point numbers is represented by M and D to ensure that the precision of numeric types can be accurately stored in the target database.

[0059] Mapping rule model library module: The mapping rule model library module is used to store all common data mapping rule models in the system. These rule models not only define the mapping relationship between the data type and structure of the source database and the target database, but also contain various additional information required in the data migration process, such as the mapping rules of indexes, foreign keys, and primary keys.

[0060] In the mapping rule model library, each rule model consists of the following parts: (1) Source and target field mapping: defines how the source database field is converted to the target database field, including data type conversion and attribute mapping. (2) Index and constraint mapping: defines how the source index and constraints are processed on the target side, including primary keys, unique constraints, etc. (3) Complex rule support: For special data structures or custom types, the rule model library allows users to customize conversion rules and implement special mapping requirements through extended define_sql.

[0061] During the system operation, users can update or add mapping rules as needed. The mapping rule model library supports dynamic expansion. When migrating a new database system or special data structure, users can define new mapping rules and store them in the model library to ensure that future migration tasks can be directly reused.

[0062] Data mapping engine module: The data mapping engine module is the execution core of the entire system. It is responsible for normalizing the extracted source data through mapping rules and outputting formatted data that meets the requirements of the target database. The data mapping engine parses the data structure and type of the source database according to the content of the mapping rule model library and converts it into a format acceptable to the target database.

[0063] The execution process of data mapping: (1) Data normalization: First, the mapping engine normalizes the source data. According to the mapping rules in the rule model library, the fields and data types of the source database are converted to intermediate standard field types, and the corresponding intermediate format data (such as JSON) is generated. (2) Data conversion: After data normalization is completed, the mapping engine further converts the intermediate standard data format (such as JSON) into the specific SQL or data format required by the target database. (3) Output data: Finally, the mapping engine outputs the formatted SQL statement or data file and submits it to the target database or storage device.

[0064] The mapping engine uses JSON as a standard data packet format to define the basic properties of table metadata and field data. Each table's metadata is represented by a JSON object, with field data as sub-attributes of an array. In this way, the mapping engine can effectively transfer data between the native attributes of the database and the intermediate standard field types.

[0065] The present invention discloses a method for realizing data synchronization between heterogeneous databases based on logs, and the specific implementation steps are as follows: 1. Configure the access information of the source and target databases: The process of extracting operations from log files on the source database and generating SQL execution scripts in reverse is as follows: Identification and reading of log files: The log file structures of different database systems are different, such as MySQL's binary log (binlog) and Oracle's redo log (redo log). First, you must identify the log files of the source database and use appropriate tools to read the log contents according to the database type.

[0066] Parsing log files: The operations recorded in the log files need to be parsed into readable database operations, such as insert, update, delete, etc. For different database log formats, you need to use corresponding parsing tools (such as MySQL's mysqlbinlog or Oracle's LogMiner) to extract the operation information.

[0067] Reverse generation of SQL scripts: From the parsed log information, each database operation is converted into a corresponding SQL statement (such as INSERT, UPDATE, DELETE). At the same time, the transaction order and consistency need to be guaranteed to ensure that the original operation can be reproduced when executing these SQL scripts.

[0068] 2. Obtain table structure and generate SQL statements to establish data mapping rules: After configuring the connection, the next step is to obtain all table definitions and metadata by querying the table structure of the source database. Since different database management systems (DBMS) have different management methods and SQL syntax for table structures, it is necessary to select an appropriate method to extract the table structure based on the specific DBMS. This step includes the following parts: Extract table structure: Use appropriate SQL statements (such as DESCRIBE or SHOW CREATETABLE in MySQL, \d in PostgreSQL) or system views (such as INFORMATION_SCHEMA) to extract metadata of the source table, including column names, data types, indexes, constraints, etc.

[0069] SQL statement generation: Generate SQL statements compatible with the target database based on the obtained source table structure. For example, convert the NUMBER type in the Oracle database to DECIMAL in MySQL to ensure that the table can be created correctly in the target database.

[0070] Establish data mapping rule identification: Create mapping rule identification for each table structure and data type as a reference for data mapping. The mapping rules should include the field correspondence between the source and target ends, the data type conversion logic, and the requirements for special data processing (such as character set conversion, date format conversion, etc.).

[0071] This step ensures that the structure of the source side can be smoothly migrated to the target side and provides a basis for subsequent data mapping and conversion.

[0072] 3. Matching mapping rules from the mapping rule model library: To ensure consistency and accuracy during the migration process, it is necessary to match the corresponding rule model from the mapping rule model library based on the established mapping rule identifier. The rule model defines the structure and data type mapping between the source database and the target database to ensure that data conversion can be performed automatically during the migration process. The specific steps include: Query model library: Through mapping rule identification, query the existing mapping models in the mapping rule model library and try to find the corresponding structure mapping and data conversion rules.

[0073] Model matching: Returns the matching model and verifies it to ensure that the model is consistent with the current migration scenario.

[0074] 4. Verify the mapping model and handle exceptions: After the model is matched, it is necessary to determine whether the returned mapping model is empty. If no matching rule model is found, the user needs to be guided to handle the exception: Exception handling: If the model is empty, the system should exit abnormally and prompt the user to create a new mapping model through the data type mapping module. The newly created mapping model includes the mapping rules that define the source database fields and the target database fields, as well as the data type conversion logic.

[0075] Model update: Save the mapping model created by the user to the mapping rule model library for future use. This operation ensures that the model library is gradually expanded and improved, improving the efficiency and automation of future migrations.

[0076] If a matching mapping model is found, continue with the next steps.

[0077] 5. Generate a JSON string based on standard field types: After obtaining the mapping rules, you need to convert the table structure of the source database into a JSON format based on the intermediate standard field types to make it easier to process during data migration. The JSON string contains the metadata and field attributes of the table, and uses the field data in the form of an array as a sub-attribute of the table metadata. The specific contents include: Metadata extraction: Extract basic information of the table, such as table name, column name, data type, index, primary key and foreign key, etc.

[0078] Standardize field types: Convert the field types of the source database to intermediate standard types (such as converting MySQL INT to universal INTEGER) to maintain consistency when migrating to different database systems.

[0079] Generate JSON string: Generate a JSON string containing field types, attributes, index information, etc. based on the extracted metadata. This string will be used as the data model processed during the migration process.

[0080] 6. Output the target data through the data mapping engine and store it in a temporary storage area: After obtaining the table structure information in JSON format, the data mapping engine module outputs the SQL statements required by the target database and temporarily stores the converted data for subsequent processing. The specific steps are as follows: Data conversion: According to the mapping rules and JSON structure, the source data is converted into the format supported by the target database. For example, the date is converted from the YYYY-MM-DD format of the source database to the standard format of the target database.

[0081] Temporary storage: Store the converted data in a temporary storage area for safe transmission and processing during data migration.

[0082] The temporary storage area serves as a data transfer station, helping to ensure data integrity and traceability during the migration process.

[0083] 7. The message listener reads the data in the temporary storage area and performs information conversion: The message listener on the migration platform is responsible for monitoring changes in the temporary storage area. When a new data message is detected, the data in the storage area is automatically read and combined with the metadata information of the target database, and the information is further converted through the mapping rule model library. The key points of this step are: Data monitoring: The message monitoring program continuously monitors data changes in the temporary storage area to ensure that it can respond to the arrival of new data in a timely manner.

[0084] Information conversion: According to the metadata of the target database (such as data type, field name, etc.), the data is converted again through the mapping rule model library to ensure that the data fully meets the requirements of the target database.

[0085] 8. Obtain the target SQL through the data mapping engine and complete the migration: After completing the information conversion, call the data mapping engine again to format the converted data into SQL statements supported by the target database. The data mapping engine sends the generated SQL statements to the target database to complete the final data migration. This step includes: SQL statement generation: The data mapping engine generates corresponding SQL statements such as INSERT, UPDATE or CREATE TABLE according to the requirements of the target database.

[0086] Execute SQL: Execute the generated SQL statements into the target database to ensure that the data and structure are fully synchronized in the target system.

[0087] Data migration: In the migration process of heterogeneous databases, business data migration is closely related to metadata migration. The migration of business data also needs to follow strict mapping rules to ensure that the business logic and data in the source database can be accurately restored in the target database. Business data migration process: The migration platform obtains SQL statements related to business operations from the source database. These statements may involve data operations such as insert, update, and delete. Subsequently, the platform parses and converts these SQL statements through the mapping rule model library, mapping the syntax and structure of the source database to a standard intermediate format. Mapping and conversion: After the intermediate format conversion is completed, the data mapping engine intervenes and converts the data in the intermediate standard format into the SQL statements required by the target database by matching the mapping rules. The SQL statements processed by the engine meet the data type and structure requirements of the target database while ensuring that the business logic is accurately transmitted. Finally, the system executes these SQL statements to migrate the business data to the target database and complete the data migration process.

[0088] In addition, when a match fails, an exception is thrown, requiring manual intervention to create a new mapping model. This process is not only time-consuming, but also prone to human errors, reducing migration efficiency. In order to solve this problem, as a preferred implementation, the introduction of artificial intelligence technology, especially the use of machine learning technology, can significantly improve the process of mapping rule generation. The optimization points of introducing artificial intelligence technology are: (1) Intelligent mapping rule generation: Through AI technology, we can automatically analyze historical data and existing data types to intelligently generate new adaptive mapping rules. The AI ​​system will deeply explore the metadata and field attributes between the source database and the target database, and extract effective mapping relationships by learning from past successful migration cases. This process enables AI to identify potential matching patterns, so that when matching fails, it can quickly provide recommended mapping rules and reduce reliance on manual intervention; (1) AI model training and optimization: Establishing and training AI models is the key to achieving this goal. By using existing migration data, AI models can learn the mapping relationships between different database systems and continuously optimize these rules. The feedback mechanism of migration results is crucial, which enables AI to adjust and improve mapping rules based on actual results after each migration, thereby improving the accuracy and adaptability of the model.

[0089] The present invention adopts the above technical solution and has the following technical advantages: 1. Data consistency: By adapting the metadata and field attributes of different database systems, it is ensured that the consistency of data is maintained during the data migration and integration process. This can effectively avoid data loss or errors caused by data type mismatch or structural differences. 2. Improve data migration efficiency: The standardized and automated metadata adaptation process can greatly improve the efficiency of data migration. By establishing mapping rules and models, the migration platform can quickly convert the structure and data type of the source database to the format required by the target database, reducing manual intervention and configuration time. 3. Reduce system integration complexity: Adapting the metadata and field attributes of different databases can simplify the complexity of system integration. Through unified mapping rules, developers and database administrators can more easily understand and manage the integration process across multiple databases. 4. Enhance data flexibility: In a heterogeneous database environment, data flexibility is enhanced. Through the definition of intermediate standard field types, users can flexibly define and extend field attributes according to business needs, making the data structure more adaptable. 5. Reduce maintenance costs: By implementing the adaptation of metadata and field attributes, the maintenance costs caused by changes in database systems can be reduced. When the database architecture needs to be modified or updated, the adaptation mechanism can automatically adjust the mapping rules to reduce the workload of manual maintenance.

[0090] The present invention focuses on the metadata and data field synchronization method and system of heterogeneous databases, ensuring smooth synchronization between different databases and effectively solving the problem of accurate and controllable synchronization of data and models in heterogeneous environments. The present invention not only improves data consistency, but also significantly simplifies the data management process, further optimizing the overall performance and maintenance efficiency of the system.

[0091] Obviously, the described embodiments are part of the embodiments of the present application, rather than all of the embodiments. In the absence of conflict, the embodiments in the present application and the features in the embodiments can be combined with each other. The components of the embodiments of the present application generally described and shown in the drawings here can be arranged and designed in various different configurations. Therefore, the detailed description of the embodiments of the present application is not intended to limit the scope of the application claimed for protection, but merely represents the selected embodiments of the present application. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians of the art without making creative work are within the scope of protection of the present application.

Claims

1. A method for realizing data synchronization between heterogeneous databases based on logs, characterized in that: It includes the following steps: Step 1, respectively obtain the connection information of the source database and the target database; Step 2: Obtain all table definitions and metadata by querying the table structure of the source database, and generate SQL statements to establish data mapping rules; Step 3: According to the established mapping rule identifier, determine whether the corresponding rule model can be matched in the mapping rule model library; If yes, go to step 5; Otherwise, go to step 4; Step 4: The system exits abnormally and prompts the user to create a new mapping model through the data type mapping module, and then saves the mapping model created by the user to the mapping rule model library; Step 5: Convert the table structure of the source database into a JSON string based on the standard field type according to the mapping rules; The JSON string contains the metadata and field attributes of the table, and uses the field data in the form of an array as a sub-attribute of the table metadata; Step 6: Based on the table structure information in JSON format, the data mapping engine module outputs the SQL statements required by the target database and temporarily stores the converted data; Step 7: The message listening program reads the data in the temporary storage area and converts the information into data that meets the requirements of the target database through the mapping rule model library; Step 8: Call the data mapping engine to format the converted data into SQL statements supported by the target database; the data mapping engine sends the generated SQL statements to the target database to complete the final data migration.

2. According to claim 1, a method for realizing data synchronization between heterogeneous databases based on logs is characterized in that: Step 1: On the source database side, extract operations from log files and reversely generate SQL execution scripts. The specific steps include: Step 1-1, Identify and read log files: Identify the log files of the source database and use the corresponding tools to read the log contents according to the database type; Step 1-2, parsing log files: according to the log formats of different databases, corresponding parsing tools should be used to extract and parse the operation information recorded in the log files into readable database operations; Step 1-3, reverse generation of SQL script: from the parsed log information, convert each database operation into a corresponding SQL statement.

3. The method for realizing data synchronization between heterogeneous databases based on logs according to claim 1, characterized in that: Step 2 specifically includes the following steps: Step 2-1, extract table structure: use SQL statements or system views to extract metadata of the source table, including column names, data types, indexes, and constraints; Step 2-2, SQL statement generation: Generate SQL statements compatible with the target database based on the obtained source table structure; Step 2-3, create data mapping rule identifier: Create a mapping rule identifier for the structure and data type of each table as a reference for data mapping.

4. The method for realizing data synchronization between heterogeneous databases based on logs according to claim 1, characterized in that: The rule model defines the structure and data type mapping between the source database and the target database, ensuring that data conversion can be performed automatically during the migration process.

5. The method for realizing data synchronization between heterogeneous databases based on logs according to claim 1, characterized in that: Step 3 specifically includes the following steps: Step 3-1, query model library: query the existing mapping model in the mapping rule model library through the mapping rule identifier, and try to find the corresponding structure mapping and data conversion rules; Step 3-2, model matching: Return the matching model and verify it to ensure that the model is consistent with the current migration scenario.

6. The method for realizing data synchronization between heterogeneous databases based on logs according to claim 1, characterized in that: In step 4, artificial intelligence technology is introduced to create a new mapping model.

7. The method for realizing data synchronization between heterogeneous databases based on logs according to claim 1, characterized in that: Step 5 specifically includes the following steps: Step 5-1, metadata extraction: extract basic information of the table, including table name, column name, data type, index, primary key and foreign key; Step 5-2, standardize field types: convert the field types of the source database into intermediate standard types so that they remain consistent when migrated to different database systems; Step 5-3, Convert to JSON string: Generate a JSON string containing field type, attribute, and index information based on the extracted metadata. The corresponding JSON string will be used as the data model processed during the migration process.

8. The method for realizing data synchronization between heterogeneous databases based on logs according to claim 1, characterized in that: Step 6 includes the following steps: Step 6-1, data conversion: according to the mapping rules and JSON structure, convert the source data into a format supported by the target database; Step 6-2, temporary storage: Store the converted data in a temporary storage area for safe transmission and processing during data migration.

9. A system for realizing data synchronization between heterogeneous databases based on logs, adopting a method for realizing data synchronization between heterogeneous databases based on logs as claimed in any one of claims 1 to 8, characterized in that: The system includes a metadata module, a data type mapping module, a mapping rule model library module and a data mapping engine module; Metadata module: responsible for managing the table structure, index and constraint rules in the source and target databases; the metadata module realizes the abstract processing of different database table structures through a unified intermediate standard field type; Data type mapping module: responsible for supporting the mapping conversion between native data types and intermediate standard field types in different databases; the data type mapping module realizes the conversion between source database type and target database type through a set of predefined mapping tables; Mapping rule model library module: used to store all common data mapping rule models in the system; the rule model not only defines the mapping relationship between the data type and structure of the source database and the target database, but also contains various additional information required in the data migration process, including the mapping rules of indexes, foreign keys, and primary keys; Data mapping engine module: responsible for normalizing the extracted source data through mapping rules and outputting formatted data that meets the requirements of the target database; The data mapping engine parses the data structure and type of the source database according to the content of the mapping rule model library, and converts it into a format acceptable to the target database.

10. The system for realizing data synchronization between heterogeneous databases based on logs according to claim 9, characterized in that: The mapping engine uses JSON as the standard data packet format to define the basic properties of table metadata and field data; the metadata of each table is represented by a JSON object, and the field data is the sub-property of the array.

Images