CN114077751A

CN114077751A - Method, system and storage medium for separating database read and write

Info

Publication number: CN114077751A
Application number: CN202010826554.2A
Authority: CN
Inventors: 韩硕
Original assignee: China Telecom Corp Ltd
Current assignee: China Telecom Corp Ltd
Priority date: 2020-08-17
Filing date: 2020-08-17
Publication date: 2022-02-22

Abstract

The present disclosure provides a method, a system, and a storage medium for database read-write separation. The method for separating read and write from database is characterized in that: an operation identification and determination step, for identifying and determining whether an operation is a write operation or a read operation; a labeling step for identifying and determining the operation identification and determination step The read operation and the write operation are marked with a read mark and a write mark respectively; in the connecting step, according to the read mark and the write mark marked on the read operation and the write operation in the mark step, the marked read mark and the write mark are marked. The operations entered into the databases corresponding to the read flags and the write flags respectively; and an execution step of executing data operations corresponding to the marked operations from the connecting step in the corresponding databases.

Description

Method, system and storage medium for separating database read and write

Technical Field

The present disclosure relates to the field of interface call database read-write security, and more particularly, to a method, a system, and a storage medium for database read-write separation, which improve the database read-write separation security.

Background

With the wide popularization of internet applications, the storage and access of mass data become a bottleneck problem of system design. For a large internet application, millions or even billions of page views per day undoubtedly place a considerable load on the database. And great problems are caused to the stability and expansibility of the system.

In order to reduce the use pressure of a system database server, a plurality of current systems adopt multi-computer synchronous data and configure a read-write separation architecture mode. The read-write separation is to disperse database read-write operations to different nodes. Fig. 1 shows a schematic structure diagram of a read-write separation system in the prior art. In fig. 1, a server sends a read-write request to a database connection pool, and a method is read or written for the request from the server mainly by means of matching a section expression by means of a method name for identifying a method body. And then placing a plurality of database connections in the database connection pool, entering read library connection when the read method is identified, and entering write library connection when the write method is identified. The read bank connection referred to herein may also be referred to as a slave bank connection, and the write bank connection may also be referred to as a master bank connection. And establishing a data master-slave copy relationship between the writing library and the reading library, monitoring the log-bin of the writing library by the reading library, and updating data synchronously by the reading library when the data of the writing library is changed.

Disclosure of Invention

However, in the current technology, the read-write method mainly depends on matching identification of codes, when a code is mismatched (human factors are more, such as writing identification for matching the read method, missing matching, etc.), especially when the read method is followed by the write method, because there is no matching rule, the system does not switch database connections when performing database interaction, thereby causing data modification to be performed in the read database, for example, adding a new piece of data, and thus causing the slave database to be the data log + 1. But the master library data log is not incremented and thus there are 1 more slave library data logs than master library logs. Since this data can be seen in the read library, when the user modifies this data again later, the operation fails because this data does not exist in the write library, and when the user performs the data write operation again in the master library later, the master library data log +1 has already records with the same id in the slave library data log, so that the operation record of the master library cannot be copied. The master-slave copy relationship of the data can be interrupted, resulting in a series of data disturbances for the following operations. Maintenance personnel are then required to manually repair the data and reestablish the master-slave copy relationship of the data. Therefore, complicated manual error correction maintenance is needed subsequently, and the reading and writing separation of the database is influenced, so that the system cannot normally operate.

The present disclosure is made in view of the above problems, and provides a method, a system, and a storage medium for database read/write separation, which can improve security of database read/write separation.

The following presents a simplified summary of the disclosure in order to provide a basic understanding of some aspects of the disclosure. However, it should be understood that this summary is not an exhaustive overview of the disclosure. It is not intended to identify key or critical elements of the disclosure or to delineate the scope of the disclosure. Its sole purpose is to present some concepts of the disclosure in a simplified form as a prelude to the more detailed description that is presented later.

The method for separating database read and write is characterized by comprising the following steps:

an operation identification determination step of identifying whether the operation is a write operation or a read operation;

a labeling step of labeling a read mark and a write mark respectively for the read operation and the write operation identified and determined in the operation identification determination step;

a connection step, wherein the marked operations respectively enter a database corresponding to the read marks and the write marks according to the read marks and the write marks marked for the read operations and the write operations in the marking step; and

an execution step of executing, in a corresponding database, a data operation corresponding to the labeled operation from the connection step.

The system for separating database read and write according to the present disclosure is characterized by comprising:

the server is used for identifying and judging whether the operation is a write operation or a read operation;

the gateway is used for respectively marking a read mark and a write mark on the read operation and the write operation which are identified and judged by the server;

a database connection pool, which makes the marked operation enter the database corresponding to the read mark and the write mark respectively according to the read mark and the write mark marked by the gateway for the read operation and the write operation; and

and the database executes data operation corresponding to the marked operation sent by the database connection pool.

According to the computer-readable storage medium of the present disclosure, which stores a computer program, the computer program causes a computer to execute a database read-write separation method, the database read-write separation method including:

According to one or more embodiments of the present disclosure, database read-write separation security can be improved.

Drawings

The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments of the disclosure and together with the description, serve to explain the principles of the disclosure.

The present disclosure may be more clearly understood from the following detailed description with reference to the accompanying drawings, in which:

fig. 1 shows a schematic structure diagram of a read-write separation system in the prior art.

Fig. 2 is a schematic structural diagram of the database read-write separation system of the present disclosure.

Fig. 3 is a flowchart schematically showing a method of separating reading and writing of a database.

Fig. 4 is a flowchart of error correction when a write operation is erroneously handled as a read operation.

Detailed Description

Various exemplary embodiments of the present disclosure will now be described in detail with reference to the accompanying drawings. It should be noted that: the relative arrangement of the components and steps, the numerical expressions, and numerical values set forth in these embodiments do not limit the scope of the present disclosure unless specifically stated otherwise.

Meanwhile, it should be understood that the sizes of the respective portions shown in the drawings are not drawn in an actual proportional relationship for the convenience of description.

The following description of at least one exemplary embodiment is merely illustrative in nature and is in no way intended to limit the disclosure, its application, or uses.

Techniques, methods, and apparatus known to those of ordinary skill in the relevant art may not be discussed in detail but are intended to be part of the specification where appropriate.

In all examples shown and discussed herein, any particular value should be construed as merely illustrative, and not limiting. Thus, other examples of the exemplary embodiments may have different values.

It should be noted that: like reference numbers and letters refer to like items in the following figures, and thus, once an item is defined in one figure, further discussion thereof is not required in subsequent figures.

Fig. 2 is a schematic structural diagram of a read-write separation system of a database according to the present disclosure. Hereinafter, the structure of the read/write separation system of the present disclosure will be described with reference to fig. 2.

In fig. 2, a server 201 is a server for receiving and managing read and write operations from clients. In addition, the server 201 also establishes different operation rights for users, for example, one user has a write right, and the other user has only a read right. The server 201 also determines whether the received operation is an operation or a write operation, and transmits the result of the determination to the gateway 202. Different labels are respectively marked in the gateway 202 for identifying the determined read operation and write operation. For example, if it is determined to be a read operation, the gateway marks S for the read operation, and if it is determined to be a write operation, the gateway marks M for the write operation. Note that the label here is not limited to this, and may be any label other than S, M as long as it can distinguish between a read operation and a write operation. After the gateway marks the read operation and the write operation, the marked read operation or write operation is sent to the database connection pool 203. The database connection pool 203 is responsible for allocating, managing and releasing database connections. In the database connection pool 203, a user configured to have a write right logs in a connection master library (write library), and a user configured to have a read right only logs in a connection slave library (read library). Thus, the database connection pool 203 assigns read operations to the

slave libraries

205, 206 and write operations to the master library 204. A write operation is performed at the master library 204 and a read operation is performed at the

slave libraries

205, 206. In addition, the

slave banks

205, 206 listen, for example, for the log-bin of the write bank 204, and when the write bank data changes, the read bank synchronizes the update data, thereby achieving master-slave synchronization. The number of slave pools is not limited to 2, and may be any number.

Hereinafter, a flow of the method of database read-write separation of the present disclosure is described with reference to fig. 3. Fig. 3 is a flowchart schematically showing a method of separating reading and writing of a database.

First, in step S301 of fig. 3, users with different operation rights are established by the server 201, for example, one of the users has a write right, and the other users only have a read right. Of course, besides this, there may be a write-right user, and a read-right user, for example, when there are multiple slave libraries (read libraries), may all use this read-right user to access the connection. There is no particular limitation.

Next, in step S302, the database connection pool 203 is configured such that a user having write authority logs in to connect with the master library (write library), and a user having read authority only logs in to connect with the slave library (read library). The configuration herein can prevent erroneous writing to the slave bank when a write operation is erroneously assigned to the slave bank. I.e. it is guaranteed that write operations are not performed in the slave bank (read bank). Thereby ensuring that the data master-slave copy relationship is not destroyed.

Next, in step S303, the operation type is identified by the server 201, and it is determined whether the operation is a write operation or a read operation. Next, in step S304, the gateway 202 performs different labeling on different operations according to the determination result of the server 201. Here, a read operation is labeled S to mean to be performed at the slave bank, and a write operation is labeled M to mean to be performed at the master bank. Then, in step S305, the database connection pool 203 decides a library connection to which the user enters, based on the content tagged by the gateway 202. When the database connection pool 203 determines that it is a read operation labeled S, it walks the library connection to perform the data operation, i.e., walks the library connection to perform the data read operation. When the database connection pool 203 determines that the write operation is a write operation labeled M, the walk-write library connection performs the data operation, i.e., the walk-master library connection performs the data write operation. In the case where the slave bank connection is performed to perform the data read operation, the flow proceeds to step S306. In step S306, a read operation is performed in the slave bank (slave database), and then the read data is returned from the slave bank. In the case of performing a data write operation by walking the master connection, the flow proceeds to step S307, and a write operation is performed in the master (master database), whereby the data of the master changes. At this time, the slave library discovers the master library data changes and duplicates the master library data.

When a marking error occurs once, for example, when a write operation is recognized as a read operation by mistake or is not recognized and the database connection is not switched after the last read operation, the error correction can be carried out through the method, so that the write operation cannot be executed in the slave library and can be distributed to the master library again for correct execution.

Details of the operation performed when an error is noted will be described below with reference to fig. 4. Fig. 4 is a flowchart of error correction when a write operation is erroneously handled as a read operation. Here, the steps of establishing different operation authorities for the database in step S301, configuring the database connection pool in step S302, and identifying and determining the operation type by the server in step S303 are omitted, and the detailed explanation is made starting from the step of labeling the operation by the gateway in step S304.

First, in step S401, an operation to be performed on the database is labeled in advance by the gateway 202. Here, the gateway 202 performs annotation according to the operation determined by the server 201. Labeled M in the case of a write operation, meaning to be performed at the master bank, and labeled S in the case of a read operation, meaning to be performed at the slave bank.

Then, the operation labeled M performs data operation by entering the master library through connection of the master library in the database connection pool (step S407), and the operation labeled S performs data operation by entering the slave library through connection of the slave library in the database connection pool (step S402). After being marked as S by the gateway, in step S402, the database connection pool 203 sends the read operation to the slave according to the marked content S. Then in step S403, if the label is correct, then the read operation is performed at the slave library, and then step S410 is entered where the read data is returned from the slave library.

If the annotation is incorrect, i.e., when the write operation is misidentified as a read operation (or unrecognized, without switching the database connection after the previous read operation) is performed in the slave library (read library), the write operation cannot be performed because the slave library connection user does not have write authority. I.e., the database execution authority write operation is not being performed at the slave library. Thus, the execution of the write operation fails (step S404). The slave library still listens to the master library data log. Then, the process proceeds to step S405, where the database reports errors without writing permission, for example, sending error reporting information. An example of this is the issue error code Err 1290. Of course, other codes indicating error reporting are possible, and are not particularly limited herein. Then, in step S406, the server captures error information issued from the library, for example, indicating an abnormality that the write operation cannot be performed without the write authority, and notifies the gateway that the operation is to be performed secondarily. The exception is discovered, for example, by capturing the error code by the server. Gateway 202 is then notified for secondary distribution. Then, the secondary labeling is started again from step S401, and the operation is labeled as M.

When the operation is labeled as M, the process proceeds to step S407. The case where the operation is labeled M includes both the case where the write operation is initially correctly labeled M by the gateway 202 and the case where the write operation is initially incorrectly labeled S and the server notifies the gateway 202 after finding an error that the write operation is labeled M for secondary distribution. In step S407, the database connection pool 203 enters the annotated content into the master library connection, and then proceeds to step S408. In step S408, a data write operation is performed in the master library, and the master library data is modified. Then, the process proceeds to step S409, where the slave library monitors that the master library data has changed and updates its own data. The read-write separation mechanism still operates normally.

According to the disclosure, tedious manual error correction maintenance is avoided, the write operation cannot be executed in the slave library and can be distributed to the master library again for correct execution, the master-slave copy relationship of the data cannot be damaged, the database read-write separation mechanism cannot be influenced, and the system still can normally operate.

Because the invention is labeled by the gateway, the database entering through the database connection pool can be changed by changing the labeled content without re-judging after the error is found, thereby reducing the labor and time for re-judging the operation type after the error is found. In addition, different authorities are allocated to users, so that the problem that the master-slave relationship of data is destroyed due to the fact that writing operation is carried out on a slave library in the prior art can be prevented. The invention adds gateway label to improve the read-write separation safety of the database.

It should be appreciated that reference throughout this specification to "an embodiment" or similar language means that a particular feature, structure, or characteristic described in connection with the embodiment is included in at least one embodiment of the present disclosure. Thus, appearances of the phrases "in embodiments of the present disclosure" and similar language throughout this specification do not necessarily all refer to the same embodiment.

One skilled in the art will appreciate that the present disclosure can be implemented as a system, method, or computer-readable medium (e.g., non-transitory storage medium) as a computer program product. Accordingly, the present disclosure may be embodied in various forms, such as an entirely hardware embodiment, an entirely software embodiment (including firmware, resident software, micro-program code, etc.) or an embodiment combining software and hardware aspects that may all be referred to hereinafter as a "circuit," module "or" system. Furthermore, the present disclosure may also be embodied in any tangible media as a computer program product having computer usable program code stored thereon.

The present disclosure is described with reference to flowchart illustrations and/or block diagrams of systems, methods and computer program products according to specific embodiments of the disclosure. It will be understood that each block of the flowchart illustrations and/or block diagrams, and any combination of blocks in the flowchart illustrations and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be executed by a processor of a general purpose computer, special purpose computer, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, implement the functions or acts specified in the flowchart and/or block diagram block or blocks.

Flowcharts and block diagrams of the architecture, functionality, and operation of which systems, methods and computer program products according to various embodiments of the present disclosure may be implemented are shown in the accompanying drawings. Accordingly, each block in the flowchart or block diagrams may represent a module, segment, or portion of program code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in the drawings may be executed substantially concurrently, or in some cases, in the reverse order from the drawing depending on the functions involved. It will also be noted that each block of the block diagrams and/or flowchart illustration, and combinations of blocks in the block diagrams and/or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.

Having described embodiments of the present disclosure, the foregoing description is intended to be exemplary, not exhaustive, and not limited to the disclosed embodiments. Many modifications and variations will be apparent to those of ordinary skill in the art without departing from the scope and spirit of the described embodiments. The terminology used herein is chosen in order to best explain the principles of the embodiments, the practical application, or technical improvements to the market technology, or to enable others of ordinary skill in the art to understand the embodiments disclosed herein.

Claims

1. A method for separating read from write of a database, comprising:

a labeling step of labeling the read operation and the write operation identified and determined in the operation identification determination step with a read mark and a write mark, respectively;

a connection step of entering the marked operation into a database corresponding to the read mark and the write mark respectively according to the read mark and the write mark marked on the read operation and the write operation in the marking step; and

an execution step of executing, in the corresponding database, a data operation corresponding to the labeled operation from the connection step.

2. The database read-write separation method according to claim 1, further comprising:

a right assignment step of assigning read right or write right to different users,

in the executing step, when the marked mark does not conform to the actual operation, judging whether the data operation fails according to the read authority and the write authority distributed in the authority distributing step.

3. The database read-write separation method according to claim 2, further comprising:

and an error correction step, wherein when the data operation is judged to fail in the execution step, the marking step is informed, so that secondary marking different from primary marking is carried out on the operation again.

4. A method of database read-write separation according to claim 3,

when the labeling step is notified, an error message indicating that the operation has failed is transmitted to the labeling step to notify.

5. A database read-write separation method according to claim 1,

in the connecting step, an operation marked as a read operation is entered into a slave bank for performing a read operation when the read flag is set, and an operation marked as a write operation is entered into a master bank for performing a write operation when the write flag is set.

6. A system for separating read and write of a database is characterized by comprising:

7. The database read-write separation system of claim 6,

the server assigns read or write rights to different users,

and when the marked mark is not in accordance with the actual operation, judging whether the data operation fails according to the read authority and the write authority distributed by the server.

8. A database read-write separation system as claimed in claim 7,

and when the data operation is judged to be failed according to the read permission and the write permission distributed by the server, the gateway is informed so as to carry out secondary marking different from the primary marking on the operation again.

9. The database read-write separation system of claim 8,

when the gateway is notified, the server captures error information indicating that the operation has failed and sends the error information to the gateway to notify.

10. The database read-write separation system of claim 6,

in the database connection pool, when the reading mark is used, the operation marked as the reading operation is entered into a slave library used for executing the reading operation, and when the writing mark is used, the operation marked as the writing operation is entered into a master library used for executing the writing operation.

11. A computer-readable storage medium storing a computer program, the computer program causing a computer to execute a database read-write separation method, the database read-write separation method comprising:

12. The computer-readable storage medium according to claim 11, wherein the computer program causes the computer to further perform a right assignment step of assigning a read right or a write right to a different user,

13. The computer-readable storage medium according to claim 12, wherein the computer program causes the computer to further execute an error correction step, and when it is determined in the execution step that the data operation has failed, notifies the labeling step so that a secondary labeling different from the primary labeling is performed again for the operation.

14. The computer-readable storage medium of claim 13,

the computer program causes a computer to notify, when notifying the labeling step, by transmitting error information indicating that the operation failed to the labeling step.

15. The computer-readable storage medium according to claim 11, wherein the computer program causes a computer to, in the connecting step, cause an operation labeled as a read operation to enter a slave bank for performing a read operation in the case of the read flag, and cause an operation labeled as a write operation to enter a master bank for performing a write operation in the case of the write flag.