CN117909414A

CN117909414A - Method, device, equipment and medium for realizing informix and MySQL database synchronization based on XXL-JOB

Info

Publication number: CN117909414A
Application number: CN202410037909.8A
Authority: CN
Inventors: 请求不公布姓名
Original assignee: Xiamen Hanyin Co ltd
Current assignee: Xiamen Hanyin Co ltd
Priority date: 2024-01-10
Filing date: 2024-01-10
Publication date: 2024-04-19

Abstract

The invention discloses a method, a device, equipment and a medium for realizing informix synchronization with a MySQL database based on XXL-JOB, wherein the method comprises the following steps: after detecting a task for starting XXL-JOB, reading task parameters; acquiring data with established contents from informix databases, and storing the data in a designated first HashMap; acquiring all data with established contents from MySQL, and storing the data in a designated second HashMap; traversing the first HashMap, and inquiring corresponding data in the second HashMap by using a unique value, wherein if the corresponding data can be inquired, generating an MD5 value and an MD5 value of MySQL by using all contents of the data, judging, and if the values are different, marking the value as an updated value and putting the updated value into an updating pool; if the data cannot be queried, the data is put into a newly added pool; mySQL connections are obtained from the Druid connection pool, and the contents in the update pool and the newly added pool are updated in batches to MySQL in a transaction mode. The invention can solve the problem of low efficiency of data synchronization between heterogeneous data sources and improve the efficiency of data synchronization.

Description

Method, device, equipment and medium for realizing informix and MySQL database synchronization based on XXL-JOB

Technical Field

The invention relates to the technical field of databases, in particular to a method, a device, equipment and a medium for synchronizing informix and a MySQL database based on XXL-JOB.

Background

With the development of enterprise informatization construction, various systems have been developed, and data, which is the core of enterprise production and operation, is continuously transferred in these systems.

The data storage modes commonly used in the market at present comprise various structures such as a relational database, NOSQL, NEWSQL, a message queue, a text file and the like. Different data stores have different uses and each has advantages. Therefore, during the operation of the enterprise system, data needs to be exchanged and transmitted in different systems and storage structures so as to realize the synchronization of the data.

Currently, a common data synchronization method, such as a method for synchronizing data from informix databases to MySQL databases, is to read a piece of data from informix, query corresponding data in MySQL with a specified unique value, add the piece of data to MySQL if the query is not completed, and update the piece of data to MySQL if the query is completed.

However, the synchronization method only queries and updates one piece of data at a time, and the updating of one piece of data needs to be compared by querying twice in informix and MySQL, so that the operation speed is low, and the synchronization efficiency is low.

Disclosure of Invention

Accordingly, the present invention is directed to a method, apparatus, device and medium for synchronizing informix with MySQL database based on XXL-JOB implementation, so as to solve the above-mentioned problems.

The embodiment of the invention provides a method for realizing informix synchronization with a MySQL database based on XXL-JOB, which comprises the following steps:

After detecting a task for starting XXL-JOB, reading task parameters of the task; the task parameters comprise given content of data to be synchronized;

Acquiring data with the established content from a pre-configured informix database, processing partial column names, parameters and content of each piece of data, and storing the processed data into a designated first HashMap by taking a unique value as a key;

Acquiring all data with the established content from MySQL at one time, and storing the data in a designated second HashMap by taking a unique value as a key;

Traversing the first HashMap, and inquiring corresponding data in the second HashMap by using a unique value, wherein if the corresponding data can be inquired, generating an MD5 value by using all contents of the data, judging the MD5 value and the stored MD5 value in MySQL, and marking the data as an updated value if the values are different, and putting the updated value into an updating pool; if the corresponding data cannot be queried, the data are put into a newly added pool;

And acquiring a global Druid connection pool, acquiring MySQL connection from the Druid connection pool, and updating contents in the update pool and the newly added pool to MySQL in batches in a transaction mode in sequence.

Preferably, the task parameters further include at least one of an executor, a scheduling type, an operation policy, a routing policy, and a blocking processing policy.

Preferably, when processing the partial column name, parameters, content of each piece of data:

Mapping each column name in the data with a column name in a target table one by one, generating a designated Java object for each piece of data according to a mapping result, and assigning values; wherein the unique code of the data is used as an Id identification.

Preferably, the method further comprises:

Creating a global Druid connection pool at start-up; and the Druid connection pools manage a plurality of database connections, the database connections in the Druid connection pools are started in the data synchronization process, and after the synchronization is finished, database connection resources are released and put back into the Druid connection pools.

Preferably, the task is a timing task, and the timing task is automatically started according to a set time node.

The embodiment of the invention also provides a device for realizing informix synchronization with the MySQL database based on XXL-JOB, which comprises:

The task parameter reading unit is used for reading task parameters of a task after detecting the task for starting the XXL-JOB; the task parameters comprise given content of data to be synchronized;

The first storage unit is used for acquiring data with the set content from a pre-configured informix database, processing partial column names, parameters and content of each piece of data, and storing the processed data into a designated first HashMap by taking a unique value as a key;

The second storage unit is used for acquiring all the data with the set content from MySQL at one time and storing the data into a designated second HashMap by taking the unique value as a key;

The query unit is used for traversing the first HashMap, querying corresponding data in the second HashMap by using a unique value, wherein if the corresponding data can be queried, the MD5 value generated by all contents of the data and the MD5 value stored in MySQL are judged, and if the values are different, the data are marked as updated values and are put into an update pool; if the corresponding data cannot be queried, the data are put into a newly added pool;

The updating unit is used for acquiring a global Druid connection pool, acquiring MySQL connection from the Druid connection pool, and updating contents in the updating pool and the newly-added pool to MySQL in batches in a transaction mode in sequence.

Preferably, the method further comprises:

A connection pool creation unit for creating a global Druid connection pool at start-up; and the Druid connection pools manage a plurality of database connections, the database connections in the Druid connection pools are started in the data synchronization process, and after the synchronization is finished, database connection resources are released and put back into the Druid connection pools.

The embodiment of the invention also provides a device for synchronizing the MySQL database based on the XXL-JOB implementation informix, which comprises a memory and a processor, wherein the memory stores a computer program which can be executed by the processor to realize the method for synchronizing the MySQL database based on the XXL-JOB implementation informix.

The embodiment of the invention also provides a computer readable storage medium which stores a computer program, wherein the computer program can be executed by a processor of a device where the computer readable storage medium is located, so as to realize the method for synchronizing informix with a MySQL database based on XXL-JOB.

In summary, in this embodiment, all the synchronized data are collected and classified in advance by HashMap, and then the data are updated in batches. Compared with the original method that the database is updated once every time one piece of data is read, the method reduces the number of database connection requests through batch updating, thereby reducing the database connection time, greatly shortening the processing time of data synchronization and improving the efficiency of data synchronization.

In addition, the embodiment can reduce the load on the database when a large number of synchronous tasks are performed by introducing Druid connection pools.

Drawings

In order to more clearly illustrate the technical solutions of the present invention, the drawings that are needed in the embodiments will be briefly described below, and it is obvious that the drawings in the following description are only some embodiments of the present invention, and that other drawings can be obtained according to these drawings without inventive effort for a person skilled in the art.

Fig. 1 is a flowchart of a method for synchronizing a MySQL database with an XXL-JOB-based implementation informix according to a first embodiment of the present invention.

Fig. 2 is an interface diagram for task parameter setting in XXL-JOB.

Fig. 3 is a schematic structural diagram of an apparatus for synchronizing a MySQL database with an XXL-JOB implementation informix according to a second embodiment of the present invention.

Detailed Description

The following description of the embodiments of the present invention will be made clearly and completely with reference to the accompanying drawings, in which it is apparent that the embodiments described are only some embodiments of the present invention, but not all embodiments. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.

For a better understanding of the technical solution of the present invention, the following detailed description of the embodiments of the present invention refers to the accompanying drawings.

It should be understood that the described embodiments are merely some, but not all, embodiments of the invention. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.

Referring to fig. 1, a method for synchronizing a MySQL database with a XXL-JOB implementation informix according to a first embodiment of the present invention may be performed by a device (hereinafter, a synchronization device is detected) for synchronizing a MySQL database with a XXL-JOB implementation informix according to a XXL-JOB implementation, and in particular, by one or more processors in the synchronization device, so as to implement the following steps:

S101, after a task for starting an XXL-JOB is detected, reading task parameters of the task; wherein the task parameters include the intended content of the data to be synchronized.

In this embodiment, the synchronization device may be a device with data storage and data processing capabilities, such as an intelligent mobile terminal, a tablet computer, a desktop computer, a notebook computer, a workstation or a server, and the invention is not limited in particular.

In this embodiment, before data synchronization is performed, task parameters first need to be set in XXL-JOB. The task parameters at least comprise the established content of the data to be synchronized.

In one embodiment, as shown in FIG. 2, the predetermined content is 1000010013, which synchronizes all the data containing 1000010013.

In addition, the task parameters may further include an executor, a scheduling type, an operation policy, a routing policy, a blocking processing policy, and the like, which are specifically set according to actual needs, and the present invention is not limited in particular.

Furthermore, in some preferred embodiments, the task may be a timed task that is automatically initiated according to a set time node. Therefore, the automatic timing data synchronization can be realized, and the synchronous task is not required to be started manually at each synchronous time, so that the input of human resources is reduced, and the working efficiency is improved.

S102, acquiring data with the set content from a pre-configured informix database, processing partial column names, parameters and content of each piece of data, and storing the processed data into a designated first HashMap by taking a unique value as a key.

Taking fig. 2 as an example, first, data containing predetermined content 1000010013 is extracted, each column name in the extracted data is mapped with a column name in a target table one by one, a designated Java object is generated and assigned for each data according to the mapping result, and unique codes of the data are used as Id identifiers. And then, storing the processed data into a first specified HashMap (for example, named informixMap) by taking the unique value as a key, so that the data can be subjected to de-duplication operation at the same time, and repeated data pollution is avoided.

S103, all the data with the set content are obtained from MySQL at one time, and the unique value is used as a key to be stored in a designated second HashMap.

Similarly, all data containing the given content 1000010013 is obtained from MySQL at one time and stored in a second designated HashMap (e.g., mysqlMap) with the unique value as a key.

S104, traversing the first HashMap, and inquiring corresponding data in the second HashMap by using a unique value, wherein if the corresponding data can be inquired, the MD5 value generated by all contents of the data and the MD5 value stored in MySQL are judged, and if the values are different, the data is marked as an updated value and is put into an updated pool; if the corresponding data cannot be queried, the data are put into a newly added pool.

In this embodiment, the HashMap is used as a buffer pool in the data synchronization process, and compared with the existing single comparison updating mode, the efficiency of data synchronization is greatly improved through a batch updating mode.

S105, acquiring a global Druid connection pool, acquiring MySQL connection from the Druid connection pool, and updating contents in the update pool and the newly-added pool to MySQL in batches in a transaction mode in sequence.

Specifically, in this embodiment, through a single-case design mode, a Druid connection pool is created when the program is started, multiple database connections are managed in the Druid connection pool, and then in the data synchronization process, only the database connections in the Druid connection pool need to be started, and after synchronization is finished, the database connection resources are released and put back into the Druid connection pool for repeated use by subsequent programs. Compared with the original method that one database connection is created once every time one piece of data is updated, the method of creating Druid connection pools and a fixed number of database connection threads only needs to be created once when a program is started by Druid, so that repeated creation of database connections in the running process of the program is avoided, time and resources wasted in the connection process are reduced, and the efficiency of data synchronization is improved.

Referring to fig. 3, a second embodiment of the present invention further provides an apparatus for synchronizing informix with MySQL database based on XXL-JOB, which includes:

A task parameter reading unit 210, configured to read task parameters of a task that starts XXL-JOB after the task is detected; the task parameters comprise given content of data to be synchronized;

a first storing unit 220, configured to obtain data with the predetermined content from a preconfigured informix database, process a part of column name, parameter and content of each piece of data, and store the processed data into a designated first HashMap with a unique value as a key;

a second storing unit 230, configured to obtain all the data with the predetermined content from MySQL at one time, and store the data in a designated second HashMap with the unique value as a key;

The query unit 240 is configured to traverse the first HashMap, query the second HashMap for corresponding data with a unique value, wherein if the corresponding data can be queried, generate MD5 values with all contents of the piece of data, determine the MD5 values with the stored MD5 values in MySQL, and mark the piece of data as updated values if the values are different, and place the updated values in the update pool; if the corresponding data cannot be queried, the data are put into a newly added pool;

The updating unit 250 is configured to obtain a global Druid connection pool, obtain MySQL connections from the Druid connection pool, and update contents in the updating pool and the newly added pool to MySQL in batch in a transaction manner sequentially.

Preferably, the method further comprises:

The various means and process steps described above may be implemented by a computer program, for example, which may be divided into one or more units stored in the memory and executed by the processor to accomplish the present invention.

The processor may be a central processing unit (Central Processing Unit, CPU), other general purpose processor, digital signal processor (DIGITAL SIGNAL processor, DSP), application SPECIFIC INTEGRATED Circuit (ASIC), off-the-shelf programmable gate array (field-programmable GATE ARRAY, FPGA) or other programmable logic device, discrete gate or transistor logic device, discrete hardware components, or the like. A general purpose processor may be a microprocessor or the processor may be any conventional processor or the like.

The memory may be used to store the computer program and/or modules, and the processor may implement the various functions of the present invention by running or executing the computer program and/or modules stored in the memory, and invoking data stored in the memory. The memory may mainly include a storage program area and a storage data area, wherein the storage program area may store an operating system, an application program (such as a sound playing function, an image playing function, etc.) required for at least one function, and the like; the storage data area may store data (such as audio data, phonebook, etc.) created according to the use of the handset, etc. In addition, the memory may include high-speed random access memory, and may also include non-volatile memory, such as a hard disk, memory, plug-in hard disk, smart memory card (SMART MEDIA CARD, SMC), secure Digital (SD) card, flash memory card (FLASH CARD), at least one disk storage device, flash memory device, or other volatile solid-state storage device.

Wherein the electronic device or printer integrated unit, if implemented in the form of a software functional unit and sold or used as a stand-alone product, may be stored in a computer readable storage medium. Based on such understanding, the present invention may implement all or part of the flow of the method of the above embodiment, or may be implemented by a computer program to instruct related hardware, where the computer program may be stored in a computer readable storage medium, and when the computer program is executed by a processor, the computer program may implement the steps of each of the method embodiments described above. Wherein the computer program comprises computer program code which may be in source code form, object code form, executable file or some intermediate form etc. The computer readable medium may include: any entity or device capable of carrying the computer program code, a recording medium, a U disk, a removable hard disk, a magnetic disk, an optical disk, a computer memory, a read-only memory (ROM), a random access memory (RAM, random Access Memory), an electrical carrier signal, a telecommunications signal, a software distribution medium, and so forth. It should be noted that the computer readable medium contains content that can be appropriately scaled according to the requirements of jurisdictions in which such content is subject to legislation and patent practice, such as in certain jurisdictions in which such content is subject to legislation and patent practice, the computer readable medium does not include electrical carrier signals and telecommunication signals.

It should be noted that the above-described apparatus embodiments are merely illustrative, and the units described as separate units may or may not be physically separate, and units shown as units may or may not be physical units, may be located in one place, or may be distributed over a plurality of network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of this embodiment. In addition, in the drawings of the embodiment of the device provided by the invention, the connection relation between the modules represents that the modules have communication connection, and can be specifically implemented as one or more communication buses or signal lines. Those of ordinary skill in the art will understand and implement the present invention without undue burden.

While the foregoing is directed to the preferred embodiments of the present invention, it will be appreciated by those skilled in the art that changes and modifications may be made without departing from the principles of the invention, such changes and modifications are also intended to be within the scope of the invention.

Claims

1. A method for synchronizing informix with a MySQL database based on XXL-JOB, comprising:

2. The method of synchronizing a XXL-JOB-based implementation informix with a MySQL database of claim 1, wherein the task parameters further comprise at least one of an executor, a scheduling type, an operation policy, a routing policy, and a blocking processing policy.

3. The method of synchronizing informix with MySQL database based on XXL-JOB implementation of claim 1, wherein when processing partial column names, parameters, content of each piece of data:

4. The method of synchronizing informix with a MySQL database based on XXL-JOB implementation of claim 1, further comprising:

5. The method of synchronizing informix with MySQL database based on XXL-JOB implementation of claim 1, wherein the task is a timed task that is automatically started according to a set time node.

6. An apparatus for synchronizing informix with a MySQL database based on XXL-JOB, comprising:

7. The apparatus for synchronizing a XXL-JOB-based implementation informix with a MySQL database according to claim 6, wherein when processing the partial column names, parameters, content of each piece of data:

8. The apparatus for synchronizing a MySQL database with an XXL-JOB-based implementation informix of claim 6, further comprising:

9. An apparatus for synchronizing a XXL-JOB-based implementation informix with a MySQL database, comprising a memory and a processor, the memory having stored therein a computer program executable by the processor to implement a method of synchronizing a XXL-JOB-based implementation informix with a MySQL database as claimed in any one of claims 1 to 5.

10. A computer readable storage medium storing a computer program executable by a processor of a device in which the computer readable storage medium is located to implement a method of synchronizing informix with MySQL database based on XXL-JOB implementation as claimed in any one of claims 1 to 5.