A data synchronization method, system, electronic device and storage medium
By parsing the redo logs into JSON files and utilizing a buffer pool and RocketMQ middleware, the synchronization interruption problem caused by writer exceptions was resolved, achieving efficient and accurate database data synchronization.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- 浙江省人力资源和社会保障信息中心
- Filing Date
- 2022-09-21
- Publication Date
- 2026-06-12
Smart Images
Figure CN115495521B_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of data processing technology, specifically to a data synchronization method, system, electronic device, and storage medium. Background Technology
[0002] In today's information age, databases are indispensable systems for storing information. Furthermore, many business scenarios involve data synchronization between databases. However, as business volume increases, so does the number of database servers, making data synchronization between these servers cumbersome and error-prone. Existing database synchronization methods are based on scenarios where the log analyzer and writer are functioning correctly. However, external factors such as sudden power outages can cause the writer to exit abnormally. Restarting the writer makes it impossible to determine the last read position, requiring real-time recording of the file read position for rewriting. This approach inevitably introduces problems. Therefore, a database synchronization method is needed that can resume reading from the interrupted position even when the log analyzer and writer encounter abnormalities. Summary of the Invention
[0003] The purpose of this invention is to overcome the problem that existing database synchronization methods cannot solve the problem of writer anomalies, and to provide a data synchronization method, system, electronic device and storage medium.
[0004] To achieve the above objectives, the present invention provides a data synchronization method, comprising the following steps:
[0005] Read and parse the redo logs from different source databases, process the parsed log files to obtain JSON files, store the JSON files into different queues of the first buffer pool according to the different source databases and the order in which they were obtained, and then store the JSON files into the RocketMQ middleware in order.
[0006] The JSON files in the RocketMQ middleware are retrieved sequentially. The JSON files are then stored in different queues of the second buffer pool according to different source databases and the retrieval order. The JSON files are parsed sequentially to obtain SQL statements corresponding to different data table names, and then written to the corresponding data tables in the target database corresponding to the source database.
[0007] As one possible implementation, during the process of storing the JSON files sequentially into the RocketMQ middleware, each time a JSON file is stored in the RocketMQ middleware, a unique code is automatically matched, the sorting order is recorded, and the JSON files in the RocketMQ middleware are retrieved sequentially according to the sorting order.
[0008] As one possible implementation, the process of sequentially retrieving JSON files from the RocketMQ middleware also includes: if an exception causes an interruption in the retrieval process, upon receiving a re-retrieval instruction, retrieving the JSON files from the RocketMQ middleware sequentially from the point of interruption according to the order of arrangement.
[0009] As one possible implementation, during the process of storing JSON files into different queues of the first buffer pool according to different source databases and the order in which they are retrieved, if the connection with the source database is abnormal, the first buffer pool starts a listening mode to listen for whether the normal connection is restored. If it is normal, the storage continues; if it is abnormal, the storage continues with other source databases.
[0010] As one possible implementation, during the process of storing JSON files into different queues of the second buffer pool according to different source databases and the order in which they are retrieved, if the connection with the target database fails, the second buffer pool starts a listening mode to listen for whether the normal connection is restored. If it is restored, the writing continues; if it fails, the writing continues with other target databases.
[0011] Accordingly, the present invention also provides a data synchronization system, comprising:
[0012] The log analyzer reads and parses redo logs from different source databases, processes the parsed log files to obtain JSON files, and stores the JSON files into different queues of the first buffer pool according to the different source databases and the order in which they were retrieved. Then, the JSON files are stored in the RocketMQ middleware in order.
[0013] The writer sequentially retrieves JSON files from the RocketMQ middleware, stores the JSON files into different queues of the second buffer pool according to different source databases and the retrieval order, parses the JSON files sequentially to obtain SQL statements corresponding to different data table names, and writes them into the corresponding data tables of the target database corresponding to the source database.
[0014] As one possible implementation, during the process of storing JSON files into different queues of the first buffer pool according to different source databases and the order in which they are retrieved, if the connection with the source database is abnormal, the first buffer pool starts a listening mode to listen for whether the normal connection is restored. If it is normal, the storage continues; if it is abnormal, the storage continues with other source databases.
[0015] As one possible implementation, during the process of storing JSON files into different queues of the second buffer pool according to different source databases and the order in which they are retrieved, if the connection with the target database fails, the second buffer pool starts a listening mode to listen for whether the normal connection is restored. If it is restored, the writing continues; if it fails, the writing continues with other target databases.
[0016] Accordingly, the present invention also provides an electronic device, comprising: at least one processor, and a memory communicatively connected to at least one of the processors; the at least one processor is configured to read a program from the memory for executing any of the methods described herein.
[0017] Accordingly, the present invention also provides a computer-readable storage medium storing instructions that, when executed on a computer, cause the computer to perform any of the methods described herein.
[0018] The beneficial effects of this invention are as follows: This invention discloses a data synchronization method, system, electronic device, and storage medium. A log analyzer reads and parses redo logs from different source databases, processes the parsed log files to obtain JSON files, and stores these JSON files sequentially into different queues of a first buffer pool according to the different source databases and the order in which they were retrieved. The JSON files are then stored sequentially into the RocketMQ middleware. A writer then sequentially retrieves the JSON files from the RocketMQ middleware and stores them sequentially into different queues of a second buffer pool according to the different source databases and the order in which they were retrieved. The JSON files are then parsed sequentially to obtain SQL statements corresponding to different table names and written to the corresponding data tables in the target database corresponding to the source database. This technology solves the problem of needing to restart synchronization from the beginning when the writer encounters an error, which is a problem in existing technologies. Instead, it enables writing to resume from where it was interrupted, improving synchronization efficiency. Furthermore, by adding a buffer pool, it enables the recording of changes in different databases in sequence and writing them sequentially, preventing write errors. Moreover, when a connection error occurs between the source and target databases, a listening mode can be enabled through the buffer pool to determine if the connection is normal. If it is not normal, writing to other databases can continue, improving efficiency. Attached Figure Description
[0019] Figure 1 This is a schematic diagram of the data synchronization method steps in an embodiment of the present invention. Detailed Implementation
[0020] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.
[0021] See Figure 1 This embodiment provides a data synchronization method, including the following steps:
[0022] Step S100: Read and parse the redo logs from different source databases, process the parsed log files to obtain JSON files, store the JSON files into different queues of the first buffer pool according to the different source databases and the order of acquisition, and then store the JSON files into the RocketMQ middleware in order.
[0023] Step S200: Obtain the JSON files in the RocketMQ middleware in sequence, store the JSON files into different queues of the second buffer pool according to different source databases and the order of acquisition, parse the JSON files in sequence to obtain SQL statements corresponding to different data table names, and write them into the corresponding data tables of the target database corresponding to the source database.
[0024] During step S100, when storing JSON files into different queues of the first buffer pool according to different source databases and the order in which they were retrieved, if a connection with a source database fails, the first buffer pool starts a listening mode to check if a normal connection is restored. If the connection is restored, the storage continues; otherwise, the storage continues with other source databases.
[0025] In this embodiment, the main component executing step S100 is a log analyzer, which includes a first buffer pool. Specifically, firstly, the log analyzer is started and connected to the source databases, including Oracle and MySQL databases. If the connection is successful, the log analyzer begins monitoring online logs; if the connection fails, the program exits. The log analyzer reads and parses the redo logs from the source databases, serializes the parsed logs into JSON files, and stores the JSON files into different queues in the first buffer pool according to the source databases and the order in which they were retrieved. This allows the writer to record the JSON files according to different databases and the order in which they were retrieved. The JSON files are then stored in the RocketMQ middleware, and synchronization is achieved by reading from the RocketMQ middleware, ensuring the accuracy of data recording. At this time, the RocketMQ middleware also serves as a data buffer. In addition, when a JSON file is stored in the RocketMQ middleware, it is automatically assigned an encoding. The files are encoded one-to-one according to the data stored in the file, and the recording order is maintained.
[0026] In step S200, during the process of storing the JSON file in the RocketMQ middleware, each time a JSON file is stored in the RocketMQ middleware, a unique code is automatically matched, the sorting order is recorded, and the JSON files in the RocketMQ middleware are retrieved sequentially according to the sorting order.
[0027] The process of retrieving the JSON files in the RocketMQ middleware in the order of arrangement also includes: if an exception causes the retrieval to be interrupted, after receiving the re-retrieval instruction, retrieving the JSON files in the RocketMQ middleware in the order of arrangement from the point of interruption.
[0028] After obtaining the SQL statements corresponding to different table names, the step of writing them to the corresponding data tables in the target database corresponding to the source database specifically includes pushing the SQL statements to different message queues according to the table names, and the different message queues writing the received SQL statements corresponding to different table names to the corresponding data tables in the target database corresponding to the source data.
[0029] The methods for retrieving JSON files from the RocketMQ middleware in sequence include retrieving JSON files from the RocketMQ middleware in sequence using the pushconsumer method.
[0030] During the process of storing JSON files into different queues of the second buffer pool according to different source databases and the order in which they were retrieved, if a connection with the target database fails, the second buffer pool starts a listening mode to check whether a normal connection has been restored. If the connection is restored, writing continues; otherwise, writing to other target databases continues.
[0031] In this embodiment, the main body executing step S200 is the writer. The writer is equipped with a second buffer pool. If a writer exception occurs during the process of the writer obtaining the JSON file, such as an external computer suddenly losing power or other external factors causing the writer to malfunction, the writer will directly restart writing from the point of interruption according to the arrangement order of the JSON files in the RocketMQ middleware, that is, obtain the JSON file in the RocketMQ middleware from the point of interruption, parse the JSON file to obtain the SQL statements corresponding to different table names, and write them into the target database.
[0032] In addition, if the target database connected to the writer experiences an abnormal disconnection, the second buffer pool will start a listening mode to monitor whether the connection with the target database has been restored. If it is restored, writing continues; if it is still not restored, writing to other target databases continues. This ensures that the writer will not stop running due to a connection failure of one database, but will continue writing to other target databases.
[0033] Based on the same inventive concept, embodiments of the present invention also provide a data synchronization system, including:
[0034] The log analyzer reads and parses redo logs from different source databases, processes the parsed log files to obtain JSON files, and stores the JSON files into different queues of the first buffer pool according to the different source databases and the order in which they were retrieved. Then, the JSON files are stored in the RocketMQ middleware in order.
[0035] The writer sequentially retrieves JSON files from the RocketMQ middleware, stores these JSON files into different queues of the second buffer pool according to different source databases and the retrieval order, and parses the JSON files sequentially to obtain SQL statements corresponding to different table names, writing them to the corresponding data tables in the target database corresponding to the source databases. Based on the same inventive concept, this embodiment also provides an electronic device. Regarding its components, please refer to the foregoing descriptions; repeated details will not be elaborated upon. It includes: at least one processor, and a memory communicatively connected to the at least one processor; the at least one processor is used to read a program from the memory for executing the method described above.
[0036] During the process of storing JSON files into different queues of the first buffer pool according to different source databases and the order in which they were retrieved, if a connection with a source database fails, the first buffer pool starts a listening mode to check whether a normal connection has been restored. If the connection is restored, the storage continues; if not, the storage continues with other source databases.
[0037] During the process of storing JSON files into different queues of the second buffer pool according to different source databases and the order in which they were retrieved, if a connection with the target database fails, the second buffer pool starts a listening mode to check whether a normal connection has been restored. If the connection is restored, writing continues; otherwise, writing to other target databases continues.
[0038] Based on the same inventive concept, embodiments of the present invention also provide a computer-readable storage medium. For the components, please refer to the foregoing description. Repeated descriptions will not be repeated. The medium stores instructions that, when executed on a computer, cause the computer to perform the methods described above.
[0039] Although the present invention has been disclosed above with reference to preferred embodiments, it is not intended to limit the present invention. Any person skilled in the art can make possible changes and modifications to the technical solutions of the present invention by utilizing the methods and techniques disclosed above without departing from the spirit and scope of the present invention. Therefore, any simple modifications, equivalent changes and alterations made to the above embodiments based on the technical essence of the present invention without departing from the content of the technical solutions of the present invention shall fall within the protection scope of the technical solutions of the present invention.
Claims
1. A data synchronization method, characterized by, Includes the following steps: The system reads and parses redo logs from different source databases, processes the parsed log files to obtain JSON files, and stores these JSON files sequentially into different queues in the first buffer pool according to their source databases and retrieval order. These JSON files are then stored sequentially into the RocketMQ middleware. Synchronization is achieved by reading from the RocketMQ middleware. Each time a JSON file is stored in the RocketMQ middleware, a unique code is automatically matched, the order is recorded, and the JSON files in the middleware are retrieved sequentially according to this order. If an exception interrupts the retrieval process, upon receiving a re-retrieval command, the JSON files in the RocketMQ middleware are retrieved sequentially from the point of interruption according to the order of retrieval. The JSON files in the RocketMQ middleware are retrieved sequentially. The JSON files are then stored in different queues of the second buffer pool according to different source databases and the retrieval order. The JSON files are parsed sequentially to obtain SQL statements corresponding to different data table names, and then written to the corresponding data tables in the target database corresponding to the source database.
2. The data synchronization method of claim 1, wherein, During the process of storing JSON files into different queues of the first buffer pool according to different source databases and the order in which they were retrieved, if a connection with a source database fails, the first buffer pool starts a listening mode to check whether a normal connection has been restored. If the connection is restored, the storage continues; if not, the storage continues with other source databases.
3. The data synchronization method of claim 1, wherein, During the process of storing JSON files into different queues of the second buffer pool according to different source databases and the order in which they were retrieved, if a connection with the target database fails, the second buffer pool starts a listening mode to check whether a normal connection has been restored. If the connection is restored, writing continues; otherwise, writing to other target databases continues.
4. A data synchronization system, characterized by include: The log analyzer reads and parses redo logs from different source databases, processes the parsed log files to obtain JSON files, and stores these JSON files sequentially into different queues in the first buffer pool according to the source database and the retrieval order. These JSON files are then stored sequentially into the RocketMQ middleware, and synchronization is achieved by reading from the RocketMQ middleware. Each time a JSON file is stored in the RocketMQ middleware, a unique code is automatically matched, the order is recorded, and the JSON files in the RocketMQ middleware are retrieved sequentially according to this order. If an exception causes an interruption in the retrieval process, upon receiving a re-retrieval command, the JSON files in the RocketMQ middleware are retrieved sequentially from the point of interruption according to the order of retrieval. The writer sequentially retrieves JSON files from the RocketMQ middleware, stores the JSON files into different queues of the second buffer pool according to different source databases and the retrieval order, parses the JSON files sequentially to obtain SQL statements corresponding to different data table names, and writes them into the corresponding data tables of the target database corresponding to the source database.
5. The data synchronization system of claim 4, wherein, During the process of storing JSON files into different queues of the first buffer pool according to different source databases and the order in which they were retrieved, if a connection with a source database fails, the first buffer pool starts a listening mode to check whether a normal connection has been restored. If the connection is restored, the storage continues; if not, the storage continues with other source databases.
6. The data synchronization system of claim 4, wherein, During the process of storing JSON files into different queues of the second buffer pool according to different source databases and the order in which they were retrieved, if a connection with the target database fails, the second buffer pool starts a listening mode to check whether a normal connection has been restored. If the connection is restored, writing continues; otherwise, writing to other target databases continues.
7. An electronic device, characterized in that, include: At least one processor, and a memory communicatively connected to at least one of the processors; At least one of the processors is configured to read a program from the memory for executing the method as described in any one of claims 1-3.
8. A computer-readable storage medium, characterized in that, The medium stores instructions that, when executed on a computer, cause the computer to perform the method as described in any one of claims 1-3.