Method for heterogeneous database synchronization based on timing schedule

Abstract

The application discloses a heterogeneous database synchronization method based on timing scheduling, and the method is characterized in that: a source database, a target database and a synchronization server are connected to the same network, a data MT table for recording data change is created on the source database through the synchronization server; a trigger of a source table on the source database is called through the synchronization server, the change of the source table data is monitored through the source table trigger, and the change is written into the MT table; a synchronization database list is created on the synchronization server, and the attributes of the source database and the target database are recorded; a task list is created on the synchronization server, the source database and the target database are defined, the source database and the target database are connected, the target table is created in the target database according to the source table field attribute of the source database; and the heterogeneous database synchronization method is low in cost, convenient to operate and not limited by the operating system and the database version.

Description

Technical Field

[0001] This invention belongs to the field of computer technology, and more specifically, relates to a method for synchronizing heterogeneous databases based on timed scheduling. Background Technology

[0002] With the rapid development of information technology, the amount of data generated by enterprises and organizations is exploding. This massive amount of data requires storage and management. As businesses expand, they often deploy multiple different business systems, which may use different database management systems and even be geographically dispersed. To achieve business collaboration and data sharing, it is necessary to synchronize data from different databases. For example, a company's contract system, sales system, inventory system, and financial system need to share customer data, material data, and order data. These application systems typically use different databases. Data sharing and interaction between different applications can be achieved in several ways, such as through interface development or by data synchronization. Since interface development involves a large workload, data synchronization has a clear advantage.

[0003] Existing database synchronization software products can achieve real-time synchronization between different databases. However, these software products are expensive, complex to configure, and require continued purchase with each database version upgrade. The principle behind these products is to read the database's transaction log. The transaction log records all modification operations performed on the database, including inserts, updates, and deletes. By analyzing the transaction log, changes in data can be detected, and the logs are transmitted to the target database and applied. This mechanism necessitates parsing logs for different database formats, which is typically complex. Furthermore, database synchronization software products are often developed by third-party vendors, not database vendors, so log parsing cannot guarantee complete accuracy.

[0004] In addition, the built-in synchronization technology of the database, in MySQL, is transmitted and applied through BINLOG. The limitation of this method is that different types of databases cannot be synchronized, and even if they are of the same type, they cannot be synchronized across operating system platforms, and the database versions must also be consistent. Summary of the Invention

[0005] To address the aforementioned issues, this invention proposes a heterogeneous database synchronization method based on timed scheduling, providing a lower-cost and more convenient database synchronization method that is not limited by operating system or database version.

[0006] To achieve the objectives of this invention, the adopted technical solution is a heterogeneous database synchronization method based on timed scheduling, wherein the database is a relational database, and the method includes the following steps:

[0007] S1: Connect the network to the source database, target database, and synchronization server. Create an MT table on the source database through the synchronization server to record changes in the data.

[0008] S2: Connect to the source database through the synchronization server and call the trigger of the source table in the source database. The source table trigger monitors the addition, deletion and change of data in the source table and writes the changes to the MT table. The synchronization server captures the data changes of the source table in real time through the MT table.

[0009] S3: Create a list of synchronized databases on the synchronization server, recording the attributes of the source and destination databases;

[0010] S4: Create a task list on the synchronization server, define the source database and the destination database to be synchronized, connect the source database and the destination database, and create the destination table in the destination database according to the source table field attributes in the source database;

[0011] S5: The synchronization server continuously captures data changes from the source table in real time through the MT table according to the set time intervals based on the created task list, and synchronizes them to the target table in the target database.

[0012] Preferably, the MT table recording data changes mainly includes the table name, primary key value, operation code, and operation time.

[0013] Optionally, the trigger for the source table in the source database is invoked by entering the table name, primary key field name, and trigger name in the synchronization server.

[0014] Preferably, the attributes of the source database and destination database recorded in the synchronization database list are the main fields of the source database and destination database, including database ID, database type, database version, character set, date format, connection method, username, and password.

[0015] Optionally, there may be multiple target databases.

[0016] Optionally, the synchronization server includes a timed execution process that detects whether the table structure definitions of the source database and the destination database are consistent. If the table structure definitions of the source database and the destination database are found to be inconsistent, the synchronization server will issue a proactive reminder.

[0017] Optionally, there may be multiple source tables in the source database and multiple destination tables in the destination database.

[0018] Optionally, the relational database is Oracle, SQL Server, MySQL, PostgreSQL, DM, OceanBase, and their various derivative versions.

[0019] Compared with the prior art, the present invention has the following beneficial effects:

[0020] Mainstream database vendors offer synchronization features called replication, but these often don't support all products. Some vendors also offer synchronization between heterogeneous databases, such as DSG and GoldenGate. However, the underlying principle is to extract redo logs or archived logs (archive logs for Oracle, binlogs for MySQL) from the source database, deliver them to the target database via TCP / IP, and then parse and restore them on the target database. This achieves real-time data synchronization between the target and source databases, but this method is costly and has significant limitations. This solution, based on user-defined synchronization rules, creates a set of triggers on the source database to record changes in the source table data. These changes are parsed into executable SQL statements, which are then executed on the target database to restore the data. It features simplified configuration tailored to the characteristics of relational databases, achieving the same synchronization effect as commercial products, while being more flexible and cost-effective. It does not restrict the operating system platforms, versions, or whether the source and target databases are the same product. Attached Figure Description

[0021] The above and other objects, features and advantages of the present invention will become more apparent from the accompanying drawings, in which like reference numerals generally denote like parts.

[0022] Figure 1 The flowchart of a heterogeneous database synchronization method based on timed scheduling according to an embodiment of the present invention is shown.

[0023] Figure 2 A schematic diagram of heterogeneous database synchronization operation according to an embodiment of the present invention is shown. Detailed Implementation

[0024] While preferred embodiments of the invention are shown in the accompanying drawings, it should be understood that the invention can be implemented in various forms and should not be limited to the embodiments set forth herein. Rather, these embodiments are provided so that the invention will be more thorough and complete, and will fully convey the scope of the invention to those skilled in the art.

[0025] like Figure 1 As shown, this invention proposes a heterogeneous database synchronization method based on timed scheduling, wherein the database is a relational database, and the method includes the following steps:

[0026] S1: Connect the network to the source database, target database, and synchronization server. Create an MT table on the source database through the synchronization server to record changes in the data.

[0027] S2: Connect to the source database through the synchronization server and call the trigger of the source table in the source database. The source table trigger monitors the addition, deletion and change of data in the source table and writes the changes to the MT table. The synchronization server captures the data changes of the source table in real time through the MT table.

[0028] S3: Create a list of synchronized databases on the synchronization server, recording the attributes of the source and destination databases;

[0029] S4: Create a task list on the synchronization server, define the source database and the destination database to be synchronized, connect the source database and the destination database, and create the destination table in the destination database according to the source table field attributes in the source database;

[0030] S5: The synchronization server continuously captures data changes from the source table in real time through the MT table according to the set time intervals based on the created task list, and synchronizes them to the target table in the target database.

[0031] The network connects the source database, target database, and synchronization server, placing them on the same network. The synchronization server then creates an MT table on the source database to record changes in the data.

[0032]

[0033]

[0034] Specifically, the MT table that records data changes mainly includes the table name, primary key value, operation code (e.g., INSERT, UPDATE, DELETE), and operation time (accurate to the second).

[0035] The MT table format and sample data are as follows:

[0036]

[0037] The synchronization server connects to the source database and invokes triggers on the source tables. Each source table has its own trigger. The synchronization server inputs the table name, primary key field name, and trigger name. The source table triggers monitor the addition, deletion, and modification of data in the source tables and write the changes to the MT table. The synchronization server then uses the MT table to capture these data changes in real time.

[0038] Create a metadata table, namely the synchronization database list, on the synchronization server to record the attributes of the source and destination databases.

[0039] Specifically, the main fields of the recorded database include database ID, database type, database version, character set, date format, connection method, username, and password;

[0040] The format and sample data for the synchronized database list table are as follows:

[0041]

[0042] Create a task list on the synchronization server, define the source database and the destination database to be synchronized, connect to the source and destination databases, and create a destination table in the destination database based on the source table field attributes in the source database. The task list table format and sample data are as follows:

[0043] task_id source_db_id dest_db_id 1 1 2

[0044] Optionally, there can be multiple target databases.

[0045] Based on the source table field attributes from the source database, create the destination table in the destination database. Creating the destination table specifically refers to automatically generating the basic table definition in the destination database using a synchronization server, based on the source table field attributes and metadata definitions. Ensure that the table field names in the source and destination tables are consistent and that the field attributes are appropriate. For example:

[0046] The source table's field type is Oracle's NUMBER, while the destination table is a MySQL database that does not have a NUMBER field type. Therefore, DECIMAL needs to be used instead.

[0047] The source database uses the GBK character set, while the destination database uses the UTF8 character set. The field definition of the source table is VARCHAR2(10), and the destination table should use VARCHAR(30).

[0048] If the destination database does not support it, a prompt should be displayed, and the user should manually decide whether to abandon the synchronization of this table. For example, if the source database uses UTF8 and the destination database uses GBK, garbled characters will occur.

[0049] The source database is MySQL, where the maximum length of a VARCHAR field is 65536. If the destination database is Oracle, the maximum length of a VARCHAR2 field is only 4000.

[0050] The source database is Oracle and the field type is LONG. If the destination database is MySQL, the LONG type is not available.

[0051] The source database can contain multiple source tables, and the destination database can contain multiple destination tables.

[0052] Specifically, the synchronization server includes a timed process that checks whether the table structure definitions of the source database and the destination database are consistent. If the table structure definitions of the source database and the destination database are found to be inconsistent, an active reminder is issued, and the user can choose whether to ignore this anomaly and continue synchronization.

[0053] The synchronization server continuously captures data changes from the source table in real time through the MT table according to the created task list and synchronizes them to the target table in the target database at set time intervals. If a user needs to modify the table structure definition of the source table, the synchronization task process should be stopped first, and then the modifications should be made. Furthermore, after the source table structure is modified, the target table should be modified synchronously, or the target table definition should be regenerated.

[0054] When a synchronization task is deleted, the synchronization server automatically deletes the MT table and all triggers in the source database, and the destination table in the destination database. The source and destination databases can run on different operating system platforms, and they do not need to be in the same data center.

[0055] Specifically, relational databases can be Oracle, SQL Server, MySQL, PostgreSQL, DM, OceanBase, and various derivative versions such as GaussDB, OpenGauss, Kingbase, MogDB, PolarDB, TiDB, and other databases.

[0056] This method achieves near real-time performance through polling scheduling. Synchronization interrupted for various reasons can be resumed later, and it can be used for database cloning and backup.

[0057] This invention can take the form of a combination of software and hardware embodiments. Furthermore, this invention can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code. The solutions in the embodiments of this invention can be implemented using various computer languages, such as the object-oriented programming language Java.

[0058] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams.Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0059] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0060] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0061] Although preferred embodiments of the invention have been described, those skilled in the art, upon learning the basic inventive concept, can make other changes and modifications to these embodiments. Therefore, the appended claims are intended to be interpreted as including both the preferred embodiments and all changes and modifications falling within the scope of the invention.

[0062] Obviously, those skilled in the art can make various modifications and variations to this invention without departing from its spirit and scope. Therefore, if these modifications and variations fall within the scope of the claims of this invention and their equivalents, this invention also intends to include these modifications and variations.

Claims

1. A heterogeneous database synchronization method based on timed scheduling, wherein the database is a relational database, characterized in that, The method includes the following steps: S1: Connect the network to the source database, target database, and synchronization server. Create an MT table on the source database through the synchronization server to record changes in the data. S2: Connect to the source database through the synchronization server and call the trigger of the source table in the source database. The source table trigger monitors the addition, deletion and change of data in the source table and writes the changes to the MT table. The synchronization server captures the data changes of the source table in real time through the MT table. S3: Create a list of synchronized databases on the synchronization server, recording the attributes of the source and destination databases; S4: Create a task list on the synchronization server, define the source database and the destination database to be synchronized, connect the source database and the destination database, and create the destination table in the destination database according to the source table field attributes in the source database; S5: The synchronization server continuously captures data changes from the source table in real time through the MT table according to the set time intervals based on the created task list, and synchronizes them to the target table in the target database.

2. The heterogeneous database synchronization method based on timed scheduling according to claim 1, characterized in that, The MT table, which records changes in its data, has main fields including table name, primary key value, operation code, and operation time.

3. The heterogeneous database synchronization method based on timed scheduling according to claim 2, characterized in that, To invoke the trigger of the source table in the source database, you need to enter the table name, primary key field name, and trigger name on the synchronization server.

4. The heterogeneous database synchronization method based on timed scheduling according to claim 3, characterized in that, The attributes of the source and destination databases recorded in the synchronized database list are the main fields of the source and destination databases, including database ID, database type, database version, character set, date format, connection method, username, and password.

5. The heterogeneous database synchronization method based on timed scheduling according to claim 4, characterized in that, There can be multiple target databases.

6. The heterogeneous database synchronization method based on timed scheduling according to claim 5, characterized in that, The synchronization server includes a timed process for detecting whether the table structure definitions of the source database and the destination database are consistent. If the table structure definitions of the source database and the destination database are found to be inconsistent, the synchronization server will issue a proactive reminder.

7. The heterogeneous database synchronization method based on timed scheduling according to claim 6, characterized in that, The source database source table and the destination database destination table can be multiple.

8. The heterogeneous database synchronization method based on timed scheduling according to claim 7, characterized in that, The relational databases mentioned are Oracle, SQL Server, MySQL, PostgreSQL, DM, OceanBase, and their various derivative versions.

Images