Database read-write separation method based on business data synchronization

Abstract

The invention discloses a database read-write separation method based on business data synchronization. The data synchronization method provided by the invention is applied to data transmission between different types of databases and different structure tables, the main database application generates data change messages during operation, consumers monitor and consume the messages and synchronize the data to the read-only database, and the data reading pressure of the main database is reduced; a complete operation and maintenance tool is provided, a read-only library can be constructed, the synchronization condition can be monitored, data comparison can be performed, the stability of the system is improved, and the workload of maintenance personnel is reduced.

Description

Technical Field

[0001] The present invention relates to the field of software technology, and in particular to a method for database read-write separation based on business data synchronization. Background Art

[0002] With the iteration of business systems, in order to meet various query and statistical needs, the query performance based on business database tables is getting worse and worse; at the same time, when read and write requests are concentrated on the business database, the database pressure is also increasing; in addition, there are many types of databases on the market today. Although many database vendors have provided read-write separation solutions, of course some databases do not have native solutions, so implementing data synchronization solutions by yourself can effectively reduce the extra workload and operation and maintenance difficulty brought by database adaptation.

[0003] In the existing design, the database's built-in read-write separation solution only supports data synchronization between tables with the same structure, and different types of databases have different read-write cluster construction methods. This increases the difficulty of project implementation and the complexity of operation and maintenance, and cannot meet the data synchronization between tables with different structures or even different storage methods. Summary of the invention

[0004] In view of the problems existing in the prior art, the present invention provides a database read-write separation method based on business data synchronization, which is used to solve the performance problem in the existing system due to the increase in query complexity.

[0005] The purpose of the present invention is achieved through the following technical solutions.

[0006] A database read-write separation method based on business data synchronization includes the following steps: When there is no MQ middleware, when the business system is running, when there is data update, the data is marked, and the scheduled task periodically polls and scans the data. According to the configured rules, the data is queried from the business database, assembled into the required format, and then synchronized to the read-only database; When there is MQ middleware, when the business system is running, when there is data update, the updated information is synchronously written to the message queue in the interceptor, and the corresponding consumer assembles the updated information into the required format according to the configuration and writes it to the read-only library.

[0007] Applied to the synchronization module and the operation and maintenance module, the synchronization module provides the system with the function of consuming changed data, and the operation and maintenance module is responsible for the functions of building, checking, and monitoring synchronization of the read-only library.

[0008] The synchronization module performs the following steps: The system writes the changed data that needs to be synchronized into the message queue according to the configuration; The system parses the messages in the message queue according to the configuration and parses the changed data into SQL; Audit the parsed SQL to avoid executing high-risk SQL; Execute the audited SQL in the read-only database and record the log.

[0009] The steps for the operation and maintenance module to assist in building a read-only library are as follows: Read the mapping relationship configuration between the business database and the read-only database table fields; Read the properties of the configuration fields in the business library; Generate a statement to create a read-only library table and execute it.

[0010] Compared with the prior art, the advantages of the present invention are: the data synchronization method provided by the present invention is applied to data transmission between different types of databases and tables of different structures. The main database application produces data change messages during operation, and consumers listen to and consume messages to synchronize data to the read-only database, thereby reducing the data reading pressure of the main database; a complete operation and maintenance tool is provided, which can build a read-only library, monitor synchronization status, and compare data, thereby improving the stability of the system and reducing the workload of maintenance personnel. In the process of database interaction, the data synchronization service of the present invention provides data transmission and error correction processing, and no longer relies on the capabilities of the database itself, but directly relies on the synchronization service to complete synchronization. It solves the performance problem in the existing system due to the increased query complexity. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] Figure 1 This is a data synchronization scenario provided by the present invention.

[0012] Figure 2 This is a structural diagram of the service module provided by the present invention.

[0013] Figure 3 A schematic diagram of the synchronization process provided by the present invention.

[0014] Figure 4 This is a schematic diagram of the read-only library initialization process provided by the present invention. DETAILED DESCRIPTION

[0015] The present invention is described in detail below in conjunction with the accompanying drawings and specific embodiments.

[0016] A database read-write separation method based on business data synchronization includes the following steps: The first synchronization solution is that when there is no MQ middleware, when the business system is running, when there is data update, the data is marked. Then the scheduled task regularly polls and scans the data, and according to the configured rules, the data is queried from the business database, assembled into the required format, and then synchronized to the read-only database.

[0017] The second synchronization solution is that when there is MQ middleware, when the business system is running, when there is data update, the updated information is synchronously written to the message queue in the interceptor. Then the corresponding consumer assembles the updated information into the required format according to the configuration, and then writes it to the read-only library.

[0018] The data synchronization scenario provided by the present invention is as follows Figure 1 shown.

[0019] Some terms used in the embodiments of the present invention are explained below to facilitate understanding by those skilled in the art.

[0020] (1) MQ (Message Queue): It is a container that stores messages during transmission. It is mostly used for communication between distributed systems.

[0021] In a possible implementation, the read-write separation strategy provided by existing databases on the market can be adopted, such as MYSQL database, Jincang database, etc. Figure 2 As shown in the figure, the synchronization service includes the key modules of synchronization and operation and maintenance. The synchronization module provides the system with the function of consuming changed data, and the operation and maintenance module is responsible for the functions of building, checking, and monitoring the synchronization of the read-only library.

[0022] Figure 3 As shown, the data synchronization process of the present invention is described, and the module includes the following steps: Step 1: The system writes the changed data that needs to be synchronized into the message queue according to the configuration.

[0023] Step 2: The system parses the messages in the message queue according to the configuration and converts the changed data into SQL.

[0024] Step 3: Audit the parsed SQL to avoid executing high-risk SQL.

[0025] Step 4: Execute the SQL statements that have passed the audit in the read-only database and record the logs.

[0026] Figure 4 As shown in the figure, the steps for the operation and maintenance module to assist in building a read-only library are described.

[0027] Step 1: Read the mapping relationship configuration between the business library and the read-only library table fields.

[0028] Step 2: Read the properties of the configuration fields in the business library.

[0029] Step 3: Generate and execute the create statement for the read-only library table.

[0030] The above are preferred embodiments of the present invention. It should be noted that, for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the principles of the present invention. These improvements and modifications are also considered to be within the scope of protection of the present invention.

Claims

1. A database read-write separation method based on business data synchronization, characterized in that The steps include: When there is no MQ middleware, when the business system is running, when there is data update, the data is marked, and the scheduled task periodically polls and scans the data. According to the configured rules, the data is queried from the business database, assembled into the required format, and then synchronized to the read-only database; When there is MQ middleware, when the business system is running, when there is data update, the updated information is synchronously written to the message queue in the interceptor, and the corresponding consumer assembles the updated information into the required format according to the configuration and writes it to the read-only library.

2. According to the method of claim 1, the database read-write separation method based on business data synchronization is characterized in that Applied to the synchronization module and the operation and maintenance module, the synchronization module provides the system with the function of consuming changed data, and the operation and maintenance module is responsible for the functions of building, checking, and monitoring synchronization of the read-only library.

3. According to the method of claim 2, the database read-write separation method based on business data synchronization is characterized in that The synchronization module performs the following steps: The system writes the changed data that needs to be synchronized into the message queue according to the configuration; The system parses the messages in the message queue according to the configuration and parses the changed data into SQL; Audit the parsed SQL to avoid executing high-risk SQL; Execute the audited SQL in the read-only database and record the log.

4. According to claim 2, a database read-write separation method based on business data synchronization is characterized in that The steps for the operation and maintenance module to assist in building a read-only library are as follows: Read the mapping relationship configuration between the business database and the read-only database table fields; Read the properties of the configuration fields in the business library; Generate a statement to create a read-only library table and execute it.

Images