CN115391457B - Cross-database data synchronization method, device and storage medium - Google Patents

Cross-database data synchronization method, device and storage medium Download PDF

Info

Publication number
CN115391457B
CN115391457B CN202211014584.9A CN202211014584A CN115391457B CN 115391457 B CN115391457 B CN 115391457B CN 202211014584 A CN202211014584 A CN 202211014584A CN 115391457 B CN115391457 B CN 115391457B
Authority
CN
China
Prior art keywords
statement
database
sql
written
insert
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Active
Application number
CN202211014584.9A
Other languages
Chinese (zh)
Other versions
CN115391457A (en
Inventor
崔雪征
徐庆
刘哲生
韩丽敏
张金龙
李娟�
贾小龙
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Kunlun Digital Technology Co ltd
China National Petroleum Corp
Original Assignee
Kunlun Digital Technology Co ltd
China National Petroleum Corp
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Kunlun Digital Technology Co ltd, China National Petroleum Corp filed Critical Kunlun Digital Technology Co ltd
Priority to CN202211014584.9A priority Critical patent/CN115391457B/en
Publication of CN115391457A publication Critical patent/CN115391457A/en
Application granted granted Critical
Publication of CN115391457B publication Critical patent/CN115391457B/en
Active legal-status Critical Current
Anticipated expiration legal-status Critical

Links

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
    • G06F16/278—Data partitioning, e.g. horizontal or vertical partitioning
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/22—Indexing; Data structures therefor; Storage structures
    • G06F16/2282—Tablespace storage structures; Management thereof
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/242—Query formulation
    • G06F16/2433—Query languages
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/245—Query processing
    • G06F16/2453—Query optimisation
    • G06F16/24534—Query rewriting; Transformation
    • Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
    • Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
    • Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Physics & Mathematics (AREA)
  • Databases & Information Systems (AREA)
  • Data Mining & Analysis (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Computational Linguistics (AREA)
  • Mathematical Physics (AREA)
  • Computing Systems (AREA)
  • Software Systems (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

The application discloses a data synchronization method, a data synchronization device and a storage medium across databases, and belongs to the technical field of databases. The method is applied to the server and comprises the following steps: acquiring a first Structured Query Language (SQL) statement in a redo log of a first database; writing a first SQL statement of a target table in a first database into an intermediate system to obtain a written first SQL statement; converting the written first SQL sentence to obtain a second SQL sentence; the second SQL statement is written into the second database such that the data of the first database is synchronized into the second database. The method can be used for rapidly synchronizing the data of the first database into the second database, and improves the data synchronization efficiency.

Description

Cross-database data synchronization method, device and storage medium
Technical Field
The present application relates to the field of database technologies, and in particular, to a method, an apparatus, and a storage medium for synchronizing data across databases.
Background
Both the Oracle database and the MySQL database belong to relational databases. In a relational database, data is typically stored in rows and columns, a series of rows and columns being referred to as tables, and a set of tables constituting the database. There is a portion of data that needs to be synchronized between the different relational databases.
In the related art, when synchronizing the Oracle database data into the MySQL database, the following steps are generally performed by a server: firstly, acquiring a data operation language (Data Manipulation Language, DML) statement of an Oracle database, then converting the DML statement into a DML statement meeting the grammar of a MySQL database, and then sending the converted DML statement to the MySQL database, so that the MySQL database realizes data synchronization.
However, when the MySQL database performs data synchronization, only the syntax of the DML statement is converted, but the MySQL database is not fully compatible with the syntax and the primary key type of the Oracle database, so that the required synchronization time is long, and the efficiency of data synchronization is affected.
Disclosure of Invention
In view of this, the application provides a method, a device and a storage medium for data synchronization across databases, which can rapidly synchronize the data of a first database to a second database, thereby improving the efficiency of data synchronization.
Specifically, the method comprises the following technical scheme:
the embodiment of the application provides a data synchronization method across databases, which comprises the following steps:
in one aspect, an embodiment of the present application provides a method for synchronizing data across databases, applied to a server, where the method includes:
Acquiring a first Structured Query Language (SQL) statement in a redo log of a first database, wherein the first SQL statement comprises a first data operation language (DML) statement;
writing a first SQL statement of a target table in the first database into an intermediate system to obtain a written first SQL statement, wherein one target table is positioned in one partition of the intermediate system, and the first database comprises at least one target table;
converting the written first SQL sentence to obtain a second SQL sentence, wherein the second SQL sentence is an SQL sentence conforming to the grammar and the primary key type of a second database, and the second SQL sentence comprises a second DML sentence;
writing the second SQL statement into a second database based on the second DML statement so that the data of the first database is synchronized into the second database.
In some embodiments, after the writing the first SQL statement of the target table in the first database into the intermediate system to obtain the written first SQL statement, the method further includes:
analyzing the written first SQL statement to obtain a first SQL statement with a key-value structure;
And taking the first SQL statement of the key-value structure as the written first SQL statement.
In some embodiments, the converting the written first SQL statement to obtain a second SQL statement includes:
and converting the first function and the first primary key type in the written first SQL sentence to obtain a second function and a second primary key type of the second SQL sentence.
In some embodiments, the converting the first function and the first primary key type in the written first SQL statement to obtain the second function and the second primary key type of the second SQL statement includes:
responding to the fact that a first function in the written first SQL statement has functions with the same parameters, roles and names in the second SQL statement, and taking the first function in the written first SQL statement as a second function of the second SQL statement;
responding to the first main key type in the written first SQL sentence, wherein the main key type with the same parameters, roles and names exists in the second SQL sentence, and the first main key type in the written first SQL sentence is used as the second main key type of the second SQL sentence;
deleting the first function in the written first SQL statement in response to the first function in the written first SQL statement not having parameters and/or functions with the same function in the second SQL statement;
Deleting the first main key type in the written first SQL statement in response to the first main key type in the written first SQL statement not having parameters and/or the main key type with the same function in the second SQL statement;
responding to the fact that a first function in the written first SQL statement has the functions with the same parameters and roles and different names in the second SQL statement, and replacing the first function in the written first SQL statement with a second function of the second SQL statement;
and in response to the existence of the primary key types with the same parameters and roles and different names in the second SQL statement, replacing the first primary key type in the written first SQL statement with the second primary key type of the second SQL statement.
In some embodiments, the writing the second SQL statement into a second database based on the second DML statement includes:
determining the type of the second DML statement based on the second DML statement, wherein the second DML statement comprises an INSERT statement, an UPDATE statement and a DELETE statement;
responding to the current second DML statement as an INSERT statement, writing the data indexed by the INSERT statement into an array for caching, splicing the INSERT statement to obtain and execute the spliced INSERT statement, and writing the data indexed by the spliced INSERT statement into the second database;
Responding to the current second DML statement as an UPDATE statement, executing an INSERT statement before the UPDATE statement, and executing the UPDATE statement to write the data indexed by the UPDATE statement into the second database;
and responding to the current second DML statement as a DELETE statement, executing an INSERT statement before the DELETE statement, and executing the DELETE statement to write the data indexed by the DELETE statement into the second database.
In some embodiments, writing the data indexed by the INSERT statement into an array for caching, assembling the INSERT statement, and obtaining and executing the assembled INSERT statement includes:
and assembling the INSERT sentences into one INSERT sentence in response to the fact that the number of the INSERT sentences reaches a preset capacity or the time interval of the INSERT sentences assembled last time reaches a preset time.
In some embodiments, the first database has an analyze redo log and archive log toolkit, the obtaining a first SQL statement in a redo log of the first database comprises:
and acquiring and obtaining the first SQL statement from the redox log of the first database by utilizing the analysis redo log and archiving log tool package.
In some embodiments, the first database is an Oracle database and the second database is a MySQL database.
In another aspect, an embodiment of the present application provides a data synchronization apparatus across databases, where the apparatus includes:
the system comprises an acquisition module, a first database and a second database, wherein the acquisition module is used for acquiring a first Structured Query Language (SQL) statement in a redo log of the first database, wherein the first SQL statement comprises a first data operation language (DML) statement;
the first writing module is used for writing a first SQL statement of a target table in the first database into an intermediate system to obtain the written first SQL statement, wherein one target table is positioned in one partition of the intermediate system, and the first database comprises at least one target table;
the conversion module is used for converting the written first SQL statement to obtain a second SQL statement, wherein the second SQL statement is an SQL statement conforming to the grammar and the primary key type of the second database, and the second SQL statement comprises a second DML statement;
and the second writing module is used for writing the second SQL statement into a second database based on the second DML statement so that the data of the first database are synchronized into the second database.
In yet another aspect, embodiments of the present application provide a non-transitory computer-readable storage medium, which when executed by a processor of an electronic device, causes the electronic device to perform a method of data synchronization across databases in any of the aspects described above.
According to the data synchronization method across databases, when the method is executed by a server, the first SQL statement of the target table in the first database can be written into an intermediate system by acquiring the first SQL statement in the redox log of the first database, so that the written first SQL statement is obtained; then converting the written first SQL sentence to obtain a second SQL sentence conforming to the grammar and the primary key type of the second database; because the second SQL statement comprises a second DML statement, the second SQL statement can be written into the second database based on the second DML statement, so that the data of the first database is synchronized into the second database. In addition, as one target table of the first database is positioned in one partition of the intermediate system, the data in the target table of the first database is conveniently synchronized on the second database according to the partition sequence, and the server is conveniently used for writing the data into the second database, so that the synchronization efficiency is improved. According to the method, the written first SQL statement is converted into the second SQL statement conforming to the grammar and the main key type of the second database before synchronization, namely, the compatibility problem existing between the databases is fully considered, so that the data of the first database can be quickly synchronized when being synchronized to the second database, and the efficiency of data synchronization is improved.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings required for the description of the embodiments will be briefly described below, and it is apparent that the drawings in the following description are only some embodiments of the present application, and other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.
FIG. 1 is a schematic diagram illustrating an implementation environment for a method of data synchronization across databases, according to an example embodiment;
FIG. 2 is a flowchart of a method for synchronizing data across databases according to an embodiment of the present application;
FIG. 3 is a flowchart of another method for synchronizing data across databases according to an embodiment of the present application;
FIG. 4 is a flowchart of writing a second SQL statement into a second database based on a second DML statement in a cross-database data synchronization method according to an embodiment of the present application;
fig. 5 is a schematic structural diagram of a data synchronization device across databases according to an embodiment of the present application.
Detailed Description
The following description of the embodiments of the present application will be made clearly and fully with reference to the accompanying drawings, in which it is evident that the embodiments described are some, but not all embodiments of the application. All other embodiments, which can be made by those skilled in the art based on the embodiments of the application without making any inventive effort, are intended to be within the scope of the application.
Some data needs to be synchronized in real time among different databases. Currently, the business library is an Oracle database, one of the analysis libraries is a MySQL database, and when the business library needs to be analyzed, data needs to be synchronized from the Oracle database to the MySQL database in real time.
Because the problem that the data synchronization efficiency is affected due to long synchronization time exists when the Oracle database data is synchronized into the MySQL database in the related art, the embodiment of the application provides a data synchronization method across databases in order to solve the problem existing in the related art.
FIG. 1 is a schematic diagram illustrating an implementation environment for a method of data synchronization across databases, according to an example embodiment. Referring to fig. 1, the implementation environment includes: a server 101 and an application 102, a first database 103, an intermediate system 104 and a second database 105 installed on the server 101.
Wherein the application 102, the first database 103, the intermediate system 104 and the second database 105 can all run on the server 101, and the first database 103, the intermediate system 104 and the second database 105 are directly connected to the application 102, respectively.
Fig. 2 is a flowchart of a method for synchronizing data across databases according to an embodiment of the present application. The method is applied to the server, and specifically comprises the following steps, referring to fig. 2.
201, a first SQL (Structured Query Language) statement in a redox log of a first database is obtained, wherein the first SQL statement comprises a first DML statement.
202, writing a first SQL statement of a target table in a first database into an intermediate system to obtain the written first SQL statement, wherein one target table is positioned in one partition of the intermediate system, and the first database comprises at least one target table.
203, converting the written first SQL sentence to obtain a second SQL sentence, wherein the second SQL sentence is an SQL sentence conforming to the grammar and the primary key type of the second database, and the second SQL sentence comprises a second DML sentence.
204, writing a second SQL statement into the second database based on the second DML statement such that the data of the first database is synchronized into the second database.
According to the data synchronization method across databases, the first SQL statement of the target table in the first database can be written into the intermediate system by acquiring the first SQL statement in the redox log of the first database, so that the written first SQL statement is obtained; then converting the written first SQL sentence to obtain a second SQL sentence conforming to the grammar and the primary key type of the second database; because the second SQL statement comprises a second DML statement, the second SQL statement can be written into the second database based on the second DML statement, so that the data of the first database is synchronized into the second database. In addition, as one target table of the first database is positioned in one partition of the intermediate system, the data in the target table of the first database is conveniently synchronized on the second database according to the partition sequence, and the server is conveniently used for writing the data into the second database, so that the synchronization efficiency is improved.
Therefore, according to the data synchronization method across databases, the written first SQL statement is converted into the second SQL statement conforming to the grammar and the primary key type of the second database before synchronization, that is, the compatibility problem existing between the databases is fully considered, so that the data of the first database can be quickly synchronized when being synchronized to the second database, and the data synchronization efficiency is improved.
In some embodiments, after writing the first SQL statement of the target table in the first database into the intermediate system to obtain the written first SQL statement, the method further comprises:
analyzing the written first SQL statement to obtain a first SQL statement with a key-value structure;
and taking the first SQL statement of the key-value structure as the written first SQL statement.
In some embodiments, converting the written first SQL statement to obtain the second SQL statement comprises:
and converting the first function and the first main key type in the written first SQL sentence to obtain a second function and a second main key type of the second SQL sentence.
In some embodiments, converting the first function and the first primary key type in the written first SQL statement to obtain the second function and the second primary key type of the second SQL statement includes:
Responding to the written first function in the first SQL statement to have the function with the same parameters, roles and names in the second SQL statement, and taking the written first function in the first SQL statement as the second function of the second SQL statement;
responding to the written first main key type in the first SQL sentence, wherein the main key type with the same parameters, roles and names exists in the second SQL sentence, and the written first main key type in the first SQL sentence is used as the second main key type of the second SQL sentence;
deleting the first function in the written first SQL statement in response to the first function in the written first SQL statement not having parameters and/or functions with the same function in the second SQL statement;
deleting the first main key type in the written first SQL statement in response to the first main key type in the written first SQL statement not having parameters and/or the main key type with the same function in the second SQL statement;
responding to the fact that the first function in the written first SQL sentence has the functions with the same parameters and roles and different names in the second SQL sentence, and replacing the first function in the written first SQL sentence with the second function of the second SQL sentence;
and in response to the existence of the primary key types with the same parameters and roles and different names in the second SQL statement, replacing the first primary key type in the written first SQL statement with the second primary key type of the second SQL statement.
In some embodiments, writing the second SQL statement into the second database based on the second DML statement includes:
determining a type of a second DML statement based on the second DML statement, the second DML statement including an INSERT statement, an UPDATE statement, and a DELETE statement;
responding to the current second DML statement as an INSERT statement, writing the data indexed by the INSERT statement into an array for caching, splicing the INSERT statement to obtain and execute the spliced INSERT statement, and writing the data indexed by the spliced INSERT statement into a second database;
responding to the current second DML statement as an UPDATE statement, executing an INSERT statement before the UPDATE statement, and executing the UPDATE statement to write the data indexed by the UPDATE statement into a second database;
and in response to the current second DML statement being a DELETE statement, executing an INSERT statement preceding the DELETE statement, and executing the DELETE statement again to write the data indexed by the DELETE statement into the second database.
In some embodiments, writing the data indexed by the INSERT statement into the array for caching, assembling the INSERT statement, and obtaining and executing the assembled INSERT statement includes:
And assembling the INSERT sentences into one INSERT sentence in response to the fact that the number of the INSERT sentences reaches a preset capacity or the time interval between the INSERT sentences and the last assembling reaches a preset time.
In some embodiments, the first database has an analyze redo log and archive log toolkit, and obtaining the first SQL statement in the redo log of the first database comprises:
and acquiring and obtaining a first SQL statement from the redox log of the first database by utilizing the analysis redo log and the archiving log tool package.
In some embodiments, the first database is an Oracle database and the second database is a MySQL database.
Fig. 3 is a flowchart of another method for synchronizing data across databases according to an embodiment of the present application. Applied to the server, see fig. 3, the method comprises the following steps.
301, a first structured query language SQL statement in a redo log of a first database is obtained.
The first SQL statement comprises a first DML statement, and the first DML statement is used for operating on data.
The method has the function of acquiring the index address and the data operation instruction of the data in the first database, so that the data in the first database can be operated conveniently.
In some embodiments, the first database has an analyze redo log and archive log toolkit.
When the first database has an analyze redo log and archive log toolkit, this step may be implemented as follows: and acquiring and obtaining a first SQL statement from the redox log of the first database by utilizing the analysis redo log and the archiving log tool package.
302, writing a first SQL statement of a target table in a first database into an intermediate system to obtain a written first SQL statement.
Wherein one target table is located in a partition of the intermediate system and the first database comprises at least one target table.
By setting one target table of the first database in one partition of the intermediate system, the data in the target table of the first database is conveniently synchronized on the second database according to the partition sequence, and the server is conveniently used for writing the data into the second database, so that the synchronization efficiency is improved.
In some embodiments, the intermediate system may be a Kafka system. The Kafka system is a high-throughput distributed publish-subscribe message system and has a plurality of partitions for caching and processing data, and has the characteristics of low delay and high availability.
In some embodiments, the corresponding first SQL data may be written into the corresponding partition according to the table name of the target table.
And 303, analyzing the written first SQL sentence to obtain a first SQL sentence with a key-value structure.
The first SQL statement is parsed into a key-value structure to facilitate indexing data. The key-value structure is a data structure, key is a keyword, value is a data value, and the key-value structure is beneficial to inquiring and storing data.
304, using the first SQL statement of the key-value structure as the written first SQL statement.
By taking the parsed key-value structure as the written first SQL statement, the synchronization of the data can be better realized.
And 305, converting the written first SQL sentence to obtain a second SQL sentence.
The second SQL statement is an SQL statement conforming to the grammar and the primary key type of the second database, and the second SQL statement comprises a second DML statement.
The written first SQL sentence is converted into the second SQL sentence conforming to the grammar and the main key type of the second database before synchronization, namely the problem that the MySQL database is incompletely compatible with the grammar and the main key type of the Oracle database is fully considered, so that the data of the first database can be quickly synchronized when being synchronized to the second database.
In some embodiments, the first database may be an Oracle database and the second database may be a MySQL database.
The method specifically comprises the following steps: and converting the first function and the first main key type in the written first SQL sentence to obtain a second function and a second main key type of the second SQL sentence.
Further, converting the first function and the first primary key type in the written first SQL statement to obtain the second function and the second primary key type of the second SQL statement may include the following embodiments:
in a first embodiment, in response to a first function in a written first SQL statement having a function with the same parameters, roles, and names in a second SQL statement, the first function in the written first SQL statement is taken as a second function of the second SQL statement.
When the functions with the same parameters, roles and names exist in the first database and the second database, the reservation operation is carried out, namely, the function conversion is not needed.
In a second embodiment, in response to the first main key type in the written first SQL statement having the main key type with the same parameters, roles and names in the second SQL statement, the first main key type in the written first SQL statement is used as the second main key type of the second SQL statement.
When the primary key types with the same parameters, roles and names exist in the first database and the second database, the reservation operation is carried out, namely conversion of the primary key types is not needed.
In a third embodiment, the first function in the written first SQL statement is deleted in response to the first function in the written first SQL statement not having parameters and/or functions that function identically in the second SQL statement.
And when the first database and the second database do not have the functions with the same parameters or the same roles or the functions with the same parameters and the same roles, deleting operation is carried out, namely deleting the first function in the written first SQL statement.
In a fourth embodiment, the first primary key type in the written first SQL statement is deleted in response to the first primary key type in the written first SQL statement not having parameters and/or acting the same primary key type in the second SQL statement.
And when the primary key types which are the same or have the same function or the same parameters and the same function do not exist in the first database and the second database, deleting operation is carried out, namely deleting the first function in the written first SQL statement.
In a fifth embodiment, in response to a first function in a written first SQL statement having the same parameters and roles and different names in a second SQL statement, the first function in the written first SQL statement is replaced with a second function in the second SQL statement.
And when the functions with the same parameters and roles and different names exist in the first database and the second database, performing replacement operation, namely replacing the first function with the function conforming to the name of the second database.
In a sixth embodiment, in response to the first primary key type in the written first SQL statement having the same parameters and roles and different names in the second SQL statement, the first primary key type in the written first SQL statement is replaced with the second primary key type of the second SQL statement.
And when the primary key types with the same parameters and roles and different names exist in the first database and the second database, performing replacement operation, namely replacing the first primary key type with the primary key type conforming to the name of the second database.
306, writing a second SQL statement into the second database based on the second DML statement such that the data of the first database is synchronized into the second database.
Because the second DML statement may be used to operate on the data, the server may write the second SQL statement into the second database, enabling the synchronization of the data of the first database into the second database.
In some embodiments, the application program on the server writes the second SQL statement into the second database in a parallel manner, or even writes on multiple servers at the same time, and the same target table writes in the same thread. In addition, when an application program on a server performs parallel writing, a process and a thread need to be used, the thread is a unit of a process, and a task of one process is executed on the thread.
Referring to fig. 4, this step may specifically include:
3061, determining a type of the second DML statement based on the second DML statement, the second DML statement including an INSERT statement, an UPDATE statement, and a DELETE statement.
The INSERT statement is used for performing insertion processing on the data; the UPDATE statement is used for updating the data; the DELETE statement is used to DELETE data.
3062, in response to the current second DML statement being an INSERT statement, writing data indexed by the INSERT statement into the array for caching, assembling the INSERT statement to obtain and execute the assembled INSERT statement, and writing the data indexed by the assembled INSERT statement into the second database.
When the current second DML statement is an INSERT statement, in order to ensure continuity of data insertion operation, after data indexed by the INSERT statement is written into the array for caching, the INSERT statement is spliced, so that the spliced INSERT statement is obtained and executed, and the data indexed by the spliced INSERT statement is written into the second database.
In some embodiments, the array is an array within an application for caching data.
In some embodiments, in order to prevent the situation of data congestion, writing the data indexed by the INSERT statement into the array for caching, assembling the INSERT statement, and obtaining and executing the assembled INSERT statement may specifically include: and in response to the fact that the number of INSERT sentences reaches a preset capacity or the time interval of last splicing reaches a preset time, splicing the INSERT sentences into one INSERT sentence, namely, the control of data writing can be realized by controlling the capacity and splicing time, and the data indexed by the spliced INSERT sentences is written into a second database.
In one embodiment, the preset capacity may be 2000 pieces and the preset time may be 1 minute.
For example, when data indexed by 2000 INSERT sentences is cached, assembling the 2000 INSERT sentences into one INSERT sentence and executing the INSERT sentence, and writing the data indexed by the assembled INSERT sentence into a second database; when the time interval between the two adjacent INSERT sentences and the last splicing reaches the preset time of 1 minute, splicing all INSERT sentences in the 1 minute into one INSERT sentence, executing the INSERT sentence, and writing the data indexed by the spliced INSERT sentence into a second database to realize controllable time interval.
In one embodiment, when a situation that individual data repetition causes synchronization failure occurs, splitting the spliced INSERT statement into a single INSERT statement to execute and write in, so as to ensure that data indexed by the spliced INSERT statement at least once occurs in the second database, and realize synchronization of the data.
3063, in response to the current second DML statement being an UPDATE statement, executing an INSERT statement preceding the UPDATE statement, and then executing the UPDATE statement to write the data indexed by the UPDATE statement into the second database.
When the current second DML statement is an UPDATE statement, the UPDATE statement is executed through the INSERT statement before the UPDATE statement, so that the data can be updated, and the accuracy of the synchronous data can be ensured.
3064, in response to the current second DML statement being a DELETE statement, executing an INSERT statement preceding the DELETE statement, and then executing the DELETE statement to write the data indexed by the DELETE statement into the second database.
When the current second DML statement is a DELETE statement, the data can be ensured to be deleted by executing the INSERT statement before the DELETE statement and then executing the DELETE statement, and the accuracy of the synchronous data can be ensured.
In practical application, by utilizing the data synchronization method across databases provided by the embodiment of the application, real-time synchronization is performed from the Oracle database to the MySQL database, so that the data in the Oracle database can be seen in the MySQL database in a few seconds at the highest, a large number of tables can be supported, real-time synchronization of a large number of data is performed, and the tables are newly added, only configuration files need to be modified, so that the method is very convenient and quick.
According to the data synchronization method across databases, written first SQL sentences are converted into second SQL sentences conforming to grammar and main key types of the second databases before synchronization, namely compatibility problems among the databases are fully considered, so that data of the first databases can be quickly synchronized when the data are synchronized to the second databases, and data synchronization efficiency is improved.
Fig. 5 is a schematic structural diagram of a data synchronization device across databases according to an embodiment of the present application. Referring to fig. 5, the apparatus includes:
an obtaining module 501, configured to obtain a first SQL statement in a redox log of a first database, where the first SQL statement includes a first DML statement;
the first writing module 502 is configured to write a first SQL statement of a target table in a first database into the intermediate system to obtain a written first SQL statement, where one target table is located in a partition of the intermediate system, and the first database includes at least one target table;
A conversion module 503, configured to convert the written first SQL statement to obtain a second SQL statement, where the second SQL statement is an SQL statement that conforms to a grammar and a primary key type of the second database, and the second SQL statement includes a second DML statement;
a second writing module 504, configured to write a second SQL statement into the second database based on the second DML statement, so that the data of the first database is synchronized into the second database.
In some embodiments, the data synchronization device across databases provided in the embodiments of the present application further includes:
the analysis module is used for analyzing the written first SQL statement to obtain a first SQL statement with a key-value structure;
and the third writing module is used for taking the first SQL statement of the key-value structure as the written first SQL statement.
In some embodiments, the conversion module 503 includes:
the conversion unit is used for converting the first function and the first main key type in the written first SQL sentence to obtain the second function and the second main key type of the second SQL sentence.
In some embodiments, the conversion unit comprises:
and the first function subunit is used for responding to the fact that the first function in the written first SQL statement has the function with the same parameters, roles and names in the second SQL statement, and taking the first function in the written first SQL statement as the second function of the second SQL statement.
The first main key type subunit is used for responding to the fact that the first main key type in the written first SQL statement has the main key type with the same parameters, roles and names in the second SQL statement, and taking the first main key type in the written first SQL statement as the second main key type of the second SQL statement.
And the second function subunit is used for deleting the first function in the written first SQL statement in response to the fact that the first function in the written first SQL statement does not have parameters and/or functions with the same function in the second SQL statement.
And the second main key type subunit is used for deleting the first main key type in the written first SQL statement in response to the fact that the first main key type in the written first SQL statement does not have parameters and/or has the same main key type in the second SQL statement.
And the third function subunit is used for replacing the first function in the written first SQL statement with the second function of the second SQL statement in response to the fact that the first function in the written first SQL statement has the functions with the same parameters and functions and different names in the second SQL statement.
And the third main key type subunit is used for replacing the first main key type in the written first SQL statement with the second main key type of the second SQL statement in response to the existence of the main key types with the same parameters and roles and different names in the second SQL statement.
In some embodiments, the second write module 504 includes:
and a determining unit configured to determine a type of the second DML statement based on the second DML statement, the second DML statement including an INSERT statement, an UPDATE statement, and a DELETE statement.
The inserting unit is used for responding to the fact that the current second DML statement is an INSERT statement, writing data indexed by the INSERT statement into the array for caching, and splicing the INSERT statement into one INSERT statement to obtain and execute the spliced INSERT statement in response to the fact that the number of the INSERT statement reaches a preset capacity or a preset time interval of last splicing is reached, so that the data indexed by the spliced INSERT statement are written into the second database.
And the updating unit is used for responding to the current second DML statement as an UPDATE statement, executing an INSERT statement before the UPDATE statement and then executing the UPDATE statement so as to write the data indexed by the UPDATE statement into the second database.
And the deleting unit is used for responding to the current second DML statement as a DELETE statement, executing an INSERT statement before the DELETE statement and then executing the DELETE statement so as to write the data indexed by the DELETE statement into the second database.
In some embodiments, the insertion unit includes:
And the assembling subunit is used for assembling the INSERT sentences into one INSERT sentence in response to the fact that the number of the INSERT sentences reaches a preset capacity or the time interval of last assembling reaches a preset time.
In some embodiments, the acquisition module 501 includes:
and the acquisition unit is used for acquiring and acquiring the first SQL statement from the redox log of the first database by utilizing the analysis redo log and the archiving log tool package.
In some embodiments, the first database is an Oracle database and the second database is a MySQL database.
According to the data synchronization device across databases, the written first SQL statement is converted into the second SQL statement conforming to the grammar and the main key type of the second database before synchronization, so that the compatibility problem existing between the databases is fully considered, and the data of the first database can be quickly synchronized when being synchronized to the second database, and the data synchronization efficiency is improved.
In an exemplary embodiment, a computer non-transitory computer readable storage medium, such as a memory including program code executable by a processor in an electronic device to perform the method of data synchronization across databases in the above embodiments is also provided. For example, the computer non-volatile computer readable storage medium may be Read-Only Memory (ROM), random access Memory (Random Access Memory, RAM), compact disk Read-Only Memory (CD-ROM), magnetic tape, floppy disk, optical data storage device, etc.
Those of ordinary skill in the art will appreciate that all or a portion of the steps of the above embodiments may be implemented by hardware, or may be implemented by a program for instructing a relevant hardware, where the program may be stored in a computer-readable storage medium, and the storage medium may be a read-only memory, a magnetic disk or an optical disk, etc.
In the present disclosure, the terms "first" and "second" are used for descriptive purposes only and are not to be construed as indicating or implying relative importance. The term "plurality" refers to two or more, unless explicitly defined otherwise.
Other embodiments of the application will be apparent to those skilled in the art from consideration of the specification and practice of the application disclosed herein. This application is intended to cover any variations, uses, or adaptations of the application following, in general, the principles of the application and including such departures from the present disclosure as come within known or customary practice within the art to which the application pertains. The specification and examples are to be regarded in an illustrative manner only.
It is to be understood that the application is not limited to the precise arrangements and instrumentalities shown in the drawings, which have been described above, and that various modifications and changes may be effected without departing from the scope thereof. The scope of the application is limited only by the appended claims.

Claims (7)

1. A method of data synchronization across databases, applied to a server, the method comprising:
acquiring a first Structured Query Language (SQL) statement in a redo log of a first database, wherein the first SQL statement comprises a first data operation language (DML) statement;
writing a first SQL statement of a target table in the first database into an intermediate system to obtain a written first SQL statement, wherein one target table is positioned in one partition of the intermediate system, and the first database comprises at least one target table; the method further comprises the steps of after the first SQL statement of the target table in the first database is written into the intermediate system to obtain the written first SQL statement: analyzing the written first SQL statement to obtain a first SQL statement with a key-value structure; taking the first SQL statement of the key-value structure as the written first SQL statement;
converting the written first SQL sentence to obtain a second SQL sentence, wherein the second SQL sentence is an SQL sentence conforming to the grammar and the primary key type of a second database, and the second SQL sentence comprises a second DML sentence;
Writing the second SQL statement into a second database based on the second DML statement, so that the data of the first database is synchronized into the second database, wherein the mode of writing the second SQL statement into the second database is parallel writing, and the same target table is written in the same thread; wherein writing the second SQL statement into a second database based on the second DML statement comprises: determining the type of the second DML statement based on the second DML statement, wherein the second DML statement comprises an INSERT statement, an UPDATE statement and a DELETE statement; responding to the current second DML statement as an INSERT statement, writing the data indexed by the INSERT statement into an array for caching, splicing the INSERT statement to obtain and execute the spliced INSERT statement, and writing the data indexed by the spliced INSERT statement into the second database; writing the data indexed by the INSERT statement into an array for caching, splicing the INSERT statement, and obtaining and executing the spliced INSERT statement comprises the following steps: responsive to the time interval between the INSERT statement and the last splicing reaches a preset time, splicing the INSERT statement into one INSERT statement, wherein the preset time is 1 minute; splitting the spliced INSERT statement into a single INSERT statement to be executed and written when the condition that individual data are repeated to cause synchronization failure occurs, so as to ensure that data indexed by the spliced INSERT statement occur at least once in the second database; responding to the current second DML statement as an UPDATE statement, executing an INSERT statement before the UPDATE statement, and executing the UPDATE statement to write the data indexed by the UPDATE statement into the second database; and responding to the current second DML statement as a DELETE statement, executing an INSERT statement before the DELETE statement, and executing the DELETE statement to write the data indexed by the DELETE statement into the second database.
2. The method for synchronizing data across databases according to claim 1, wherein converting the written first SQL statement to obtain a second SQL statement comprises:
and converting the first function and the first primary key type in the written first SQL sentence to obtain a second function and a second primary key type of the second SQL sentence.
3. The method for synchronizing data across databases according to claim 2, wherein said converting a first function and a first primary key type in said written first SQL statement to obtain a second function and a second primary key type of said second SQL statement comprises:
responding to the fact that a first function in the written first SQL statement has functions with the same parameters, roles and names in the second SQL statement, and taking the first function in the written first SQL statement as a second function of the second SQL statement;
responding to the first main key type in the written first SQL sentence, wherein the main key type with the same parameters, roles and names exists in the second SQL sentence, and the first main key type in the written first SQL sentence is used as the second main key type of the second SQL sentence;
Deleting the first function in the written first SQL statement in response to the first function in the written first SQL statement not having parameters and/or functions with the same function in the second SQL statement;
deleting the first main key type in the written first SQL statement in response to the first main key type in the written first SQL statement not having parameters and/or the main key type with the same function in the second SQL statement;
responding to the fact that a first function in the written first SQL statement has the functions with the same parameters and roles and different names in the second SQL statement, and replacing the first function in the written first SQL statement with a second function of the second SQL statement;
and in response to the existence of the primary key types with the same parameters and roles and different names in the second SQL statement, replacing the first primary key type in the written first SQL statement with the second primary key type of the second SQL statement.
4. The method of claim 1, wherein the first database has an analysis redo log and archive log toolkit, and the obtaining the first SQL statement in the redo log of the first database comprises:
And acquiring and obtaining the first SQL statement from the redox log of the first database by utilizing the analysis redo log and archiving log tool package.
5. The method of claim 1, wherein the first database is an Oracle database and the second database is a MySQL database.
6. A data synchronization apparatus across a database, the apparatus comprising:
the system comprises an acquisition module, a first database and a second database, wherein the acquisition module is used for acquiring a first Structured Query Language (SQL) statement in a redo log of the first database, wherein the first SQL statement comprises a first data operation language (DML) statement;
the first writing module is used for writing a first SQL statement of a target table in the first database into an intermediate system to obtain the written first SQL statement, wherein one target table is positioned in one partition of the intermediate system, and the first database comprises at least one target table; after the first SQL statement of the target table in the first database is written into the intermediate system to obtain the written first SQL statement, the first writing module is further used for analyzing the written first SQL statement to obtain a first SQL statement of a key-value structure; taking the first SQL statement of the key-value structure as the written first SQL statement;
The conversion module is used for converting the written first SQL statement to obtain a second SQL statement, wherein the second SQL statement is an SQL statement conforming to the grammar and the primary key type of the second database, and the second SQL statement comprises a second DML statement;
the second writing module is used for writing the second SQL statement into a second database based on the second DML statement so that the data of the first database are synchronized into the second database, wherein the mode of writing the second SQL statement into the second database is parallel writing, and the same target table is written in the same thread; the second writing module is further configured to determine a type of the second DML statement based on the second DML statement, where the second DML statement includes an INSERT statement, an UPDATE statement, and a DELETE statement; responding to the current second DML statement as an INSERT statement, writing the data indexed by the INSERT statement into an array for caching, splicing the INSERT statement to obtain and execute the spliced INSERT statement, and writing the data indexed by the spliced INSERT statement into the second database; writing the data indexed by the INSERT statement into an array for caching, splicing the INSERT statement, and obtaining and executing the spliced INSERT statement comprises the following steps: responsive to the time interval between the INSERT statement and the last splicing reaches a preset time, splicing the INSERT statement into one INSERT statement, wherein the preset time is 1 minute; splitting the spliced INSERT statement into a single INSERT statement to be executed and written when the condition that individual data are repeated to cause synchronization failure occurs, so as to ensure that data indexed by the spliced INSERT statement occur at least once in the second database; responding to the current second DML statement as an UPDATE statement, executing an INSERT statement before the UPDATE statement, and executing the UPDATE statement to write the data indexed by the UPDATE statement into the second database; and responding to the current second DML statement as a DELETE statement, executing an INSERT statement before the DELETE statement, and executing the DELETE statement to write the data indexed by the DELETE statement into the second database.
7. A non-transitory computer readable storage medium, wherein instructions in the storage medium, when executed by a processor of an electronic device, enable the electronic device to perform the method of data synchronization across databases of any one of claims 1 to 5.
CN202211014584.9A 2022-08-23 2022-08-23 Cross-database data synchronization method, device and storage medium Active CN115391457B (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202211014584.9A CN115391457B (en) 2022-08-23 2022-08-23 Cross-database data synchronization method, device and storage medium

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202211014584.9A CN115391457B (en) 2022-08-23 2022-08-23 Cross-database data synchronization method, device and storage medium

Publications (2)

Publication Number Publication Date
CN115391457A CN115391457A (en) 2022-11-25
CN115391457B true CN115391457B (en) 2023-09-12

Family

ID=84119752

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202211014584.9A Active CN115391457B (en) 2022-08-23 2022-08-23 Cross-database data synchronization method, device and storage medium

Country Status (1)

Country Link
CN (1) CN115391457B (en)

Families Citing this family (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN116501789A (en) * 2023-06-28 2023-07-28 本原数据(北京)信息技术有限公司 Database data writing method and device, electronic equipment and storage medium

Citations (14)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US7702698B1 (en) * 2005-03-01 2010-04-20 Yahoo! Inc. Database replication across different database platforms
CN102129478A (en) * 2011-04-26 2011-07-20 广州从兴电子开发有限公司 Database synchronization method and system thereof
CN102346775A (en) * 2011-09-26 2012-02-08 苏州博远容天信息科技有限公司 Method for synchronizing multiple heterogeneous source databases based on log
CN103020112A (en) * 2012-11-06 2013-04-03 深圳中兴网信科技有限公司 Heterogeneous database synchronizing system and heterogeneous database synchronizing method
CN103067483A (en) * 2012-12-25 2013-04-24 广东邮电职业技术学院 Remote data increment synchronization method and device based on data package
CN105491106A (en) * 2015-11-18 2016-04-13 中国石油天然气集团公司 Real-time synchronization system and method for oil well logging master-slave database systems
CN107025249A (en) * 2016-02-02 2017-08-08 上海格尔软件股份有限公司 A kind of method of quick support synchronizing multiple databases exploitation
CN109933630A (en) * 2019-03-19 2019-06-25 武汉达梦数据库有限公司 Database data real-time synchronization method and equipment
CN109960710A (en) * 2019-01-16 2019-07-02 平安科技（深圳）有限公司 Method of data synchronization and system between database
CN109992595A (en) * 2019-04-11 2019-07-09 北京启迪区块链科技发展有限公司 Different database conversion method, apparatus, equipment and storage medium
KR20200092095A (en) * 2019-01-24 2020-08-03 주식회사 웨어밸리 Transaction control method to synchronize DML statements in relational database to NoSQL database
CN113282605A (en) * 2021-04-14 2021-08-20 上海瀚之友信息技术服务有限公司 Real-time synchronization system and method for heterogeneous database
WO2022001750A1 (en) * 2020-06-29 2022-01-06 中兴通讯股份有限公司 Data synchronization processing method, electronic device, and storage medium
CN114443599A (en) * 2020-11-06 2022-05-06 腾讯科技（北京）有限公司 Data synchronization method and device, electronic equipment and storage medium

Family Cites Families (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US11893041B2 (en) * 2019-05-15 2024-02-06 International Business Machines Corporation Data synchronization between a source database system and target database system

Patent Citations (14)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US7702698B1 (en) * 2005-03-01 2010-04-20 Yahoo! Inc. Database replication across different database platforms
CN102129478A (en) * 2011-04-26 2011-07-20 广州从兴电子开发有限公司 Database synchronization method and system thereof
CN102346775A (en) * 2011-09-26 2012-02-08 苏州博远容天信息科技有限公司 Method for synchronizing multiple heterogeneous source databases based on log
CN103020112A (en) * 2012-11-06 2013-04-03 深圳中兴网信科技有限公司 Heterogeneous database synchronizing system and heterogeneous database synchronizing method
CN103067483A (en) * 2012-12-25 2013-04-24 广东邮电职业技术学院 Remote data increment synchronization method and device based on data package
CN105491106A (en) * 2015-11-18 2016-04-13 中国石油天然气集团公司 Real-time synchronization system and method for oil well logging master-slave database systems
CN107025249A (en) * 2016-02-02 2017-08-08 上海格尔软件股份有限公司 A kind of method of quick support synchronizing multiple databases exploitation
CN109960710A (en) * 2019-01-16 2019-07-02 平安科技（深圳）有限公司 Method of data synchronization and system between database
KR20200092095A (en) * 2019-01-24 2020-08-03 주식회사 웨어밸리 Transaction control method to synchronize DML statements in relational database to NoSQL database
CN109933630A (en) * 2019-03-19 2019-06-25 武汉达梦数据库有限公司 Database data real-time synchronization method and equipment
CN109992595A (en) * 2019-04-11 2019-07-09 北京启迪区块链科技发展有限公司 Different database conversion method, apparatus, equipment and storage medium
WO2022001750A1 (en) * 2020-06-29 2022-01-06 中兴通讯股份有限公司 Data synchronization processing method, electronic device, and storage medium
CN114443599A (en) * 2020-11-06 2022-05-06 腾讯科技（北京）有限公司 Data synchronization method and device, electronic equipment and storage medium
CN113282605A (en) * 2021-04-14 2021-08-20 上海瀚之友信息技术服务有限公司 Real-time synchronization system and method for heterogeneous database

Also Published As

Publication number Publication date
CN115391457A (en) 2022-11-25

Similar Documents

Publication Publication Date Title
CN106874281B (en) Method and device for realizing database read-write separation
CN112131237B (en) Data synchronization method, device, equipment and computer readable medium
CN109086382B (en) Data synchronization method, device, equipment and storage medium
CN111324607B (en) SQL statement multiplexing method and device
EP4170509A1 (en) Method for playing back log on data node, data node, and system
CN112328702B (en) Data synchronization method and system
CN115391457B (en) Cross-database data synchronization method, device and storage medium
CN111651519A (en) Data synchronization method, data synchronization device, electronic device, and storage medium
CN114691704A (en) Metadata synchronization method based on MySQL binlog
CN114254016A (en) Data synchronization method, device and equipment based on elastic search and storage medium
CN112131214A (en) Method, system, equipment and storage medium for data writing and data query
CN111159020B (en) Method and device applied to synchronous software test
CN115080666A (en) Data synchronization method, system, electronic device and storage medium
CN116501700B (en) APP formatted file offline storage method, device, equipment and storage medium
CN113468209A (en) High-speed memory database access method for power grid monitoring system
CN114595286A (en) Data synchronization method and device, electronic equipment and storage medium
CN111767282A (en) MongoDB-based storage system, data insertion method and storage medium
CN116842244A (en) Search engine data synchronization method, system, device and storage medium
CN115658815A (en) CDC (control data center) -based data synchronization method
CN114153857A (en) Data synchronization method, data synchronization apparatus, and computer-readable storage medium
CN116561138A (en) Data processing method and device
CN113590651A (en) Cross-cluster data processing system and method based on HQL
CN112612647A (en) Log parallel replay method, device, equipment and storage medium
CN112685431A (en) Asynchronous caching method, device, system, electronic equipment and storage medium
CN112817989B (en) Data processing method, data processing device, storage medium and electronic equipment

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination
GR01 Patent grant
GR01 Patent grant