Database system and processing method of database system

Abstract

The application provides a database system and a processing method of the database system, which comprises: at least two databases deployed in different places, each database corresponding to a canal server and a canal client; when the canal server of any database detects that the query statement is executed, the logical log in the database is pulled; the canal client obtains the logical log and converts it into a query statement entity; the canal client generates a corresponding query statement according to the data change information in the query statement entity and judges whether it is a preset type, if it is the preset type, whether it is executed by other databases is judged, if it is not executed by other databases, the corresponding query statement is sent to each other database; if it is not the preset type, the corresponding query statement is sent to each other database to complete data synchronization, so as to avoid the risk of database paralysis and unavailability.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and in particular to a database system and a method for processing a database system. Background Technology

[0002] With the continuous popularization of the Internet and the development of information technology, the data in databases is becoming increasingly large and complex. Over time, this has brought potential risks and hidden dangers to the stable operation of databases.

[0003] In existing technologies, data recovery in the database is achieved by sending data to the database through a disaster recovery backup center.

[0004] However, in existing technologies, a failure of the disaster recovery backup center will result in data unavailability and database paralysis. Summary of the Invention

[0005] This application provides a database system and a method for processing the database system, in order to solve the problem in the prior art that a failure of the disaster recovery center will lead to data unavailability and database paralysis.

[0006] In a first aspect, this application provides a database system, comprising: at least two geographically deployed databases, each database corresponding to a deployed Canal server and a Canal client;

[0007] For any database, when the Canal server corresponding to the database detects that the database has executed a query statement, it retrieves the logical logs generated by the database executing the query statement.

[0008] When the Canal client corresponding to any of the database detects that the Canal server has retrieved the logical log, it obtains the logical log and converts the logical log into a query statement entity.

[0009] The Canal client generates a corresponding query statement based on the data change information in the query statement entity.

[0010] The Canal client determines whether the type of the corresponding query statement is a preset type. If the type of the corresponding query statement is a preset type, it determines whether the corresponding query statement is a query statement that has been executed by other databases. If the corresponding query statement is not a query statement that has been executed by other databases, it sends the corresponding query statement to each of the other databases.

[0011] If the type of the corresponding query statement is not a preset type, then the corresponding query statement will be sent to other databases.

[0012] Each of the other databases executes the corresponding query statement to complete data synchronization across all databases.

[0013] In one possible design, the Canal client generates a corresponding query statement based on the data change information in the query statement entity, including: obtaining the query statement type, column data values ​​before synchronization, column data values ​​after synchronization, and column attributes from the data change information; and generating a corresponding query statement based on the query statement type, column data values ​​before synchronization, column data values ​​after synchronization, and column attributes.

[0014] In one possible design, the type of the query statement is an insert type; correspondingly, generating the corresponding query statement based on the type of the query statement, the column data value before synchronization, the column data value after synchronization, and the column attributes includes: determining that the column data value before synchronization is zero, and the column data value after synchronization is the inserted value; determining the attributes of the column containing the inserted value; and generating the corresponding query statement based on the inserted value and the attributes of the column containing the inserted value.

[0015] In one possible design, the type of the query statement is an update type; correspondingly, generating the corresponding query statement based on the type of the query statement, the column data value before synchronization, the column data value after synchronization, and the column attributes includes: determining that the column data value before synchronization is the value before the update, and the column data value after synchronization is the value after the update; determining the attributes of the column containing the updated value; and generating the corresponding query statement based on the value before the update, the updated value, and the attributes of the column containing the updated value.

[0016] In one possible design, the type of the query statement is a deletion type; correspondingly, generating the corresponding query statement based on the type of the query statement, the column data value before synchronization, the column data value after synchronization, and the column attributes includes: determining that the column data value before synchronization is the value before deletion, and the column data value after synchronization is zero; determining the attributes of the column containing the value before deletion; and generating the corresponding query statement based on the value before deletion and the attributes of the column containing the value before deletion.

[0017] In one possible design, the preset type is an insert type and / or an update type, and both insert and update type query statements are configured with unique direction data information; all databases are configured with a status table to store the direction data information; correspondingly, the Canal client determines whether the corresponding query statement is a query statement executed in another database by: comparing the direction data information configured in the corresponding query statement with the direction data information stored in the status table of the other database; if the direction data information stored in the status table of the other database does not contain the direction data information configured in the corresponding query statement, then it is determined that the corresponding query statement was not executed in the other database; if the direction data information stored in the status table of the other database contains the direction data information configured in the corresponding query statement, then it is determined that the corresponding query statement was executed in the other database.

[0018] In one possible design, the Canal client is configured with a synchronization whitelist for query data. Accordingly, if the corresponding query statement is not a query statement executed by other databases, after sending the corresponding query statement to each other database, the process further includes: determining whether the corresponding query statement exists in the synchronization whitelist; if the corresponding query statement exists in the synchronization whitelist, then sending the corresponding query statement to each other database; if the corresponding query statement does not exist in the synchronization whitelist, then ending the process.

[0019] Secondly, this application provides a method for processing a database system, the database system comprising: at least two databases deployed in different locations, each database corresponding to a deployed Canal server and a Canal client;

[0020] The method includes:

[0021] For any database, when the Canal server corresponding to the database detects that the database has executed a query statement, it retrieves the logical logs generated by the database executing the query statement.

[0022] When the Canal client corresponding to any of the database detects that the Canal server has retrieved the logical log, it obtains the logical log and converts the logical log into a query statement entity.

[0023] The Canal client generates a corresponding query statement based on the data change information in the query statement entity.

[0024] The Canal client determines whether the type of the corresponding query statement is a preset type. If the type of the corresponding query statement is a preset type, it determines whether the corresponding query statement is a query statement that has been executed by other databases. If the corresponding query statement is not a query statement that has been executed by other databases, it sends the corresponding query statement to each of the other databases.

[0025] If the type of the corresponding query statement is not a preset type, then the corresponding query statement will be sent to other databases.

[0026] Each of the other databases executes the corresponding query statement to complete data synchronization across all databases.

[0027] In one possible design, the Canal client generates a corresponding query statement based on the data change information in the query statement entity, including: obtaining the query statement type, column data values ​​before synchronization, column data values ​​after synchronization, and column attributes from the data change information; and generating a corresponding query statement based on the query statement type, column data values ​​before synchronization, column data values ​​after synchronization, and column attributes.

[0028] In one possible design, the type of the query statement is an insert type; correspondingly, generating the corresponding query statement based on the type of the query statement, the column data value before synchronization, the column data value after synchronization, and the column attributes includes: determining that the column data value before synchronization is zero, and the column data value after synchronization is the inserted value; determining the attributes of the column containing the inserted value; and generating the corresponding query statement based on the inserted value and the attributes of the column containing the inserted value.

[0029] The database system and its processing method provided in this application propose a database system that deploys at least two databases in different locations. After a query statement is executed in any database, the system retrieves the logical log of the data through the Canal server, and then obtains the query statement based on the logical log through the Canal client. Based on the query statement, the system realizes data synchronization with other databases, thereby avoiding the risk of database paralysis and unavailability. Attached Figure Description

[0030] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0031] Figure 1A schematic diagram of the database system provided in this application;

[0032] Figure 2 This is a schematic flowchart of the database system processing method provided in the embodiments of this application. Detailed Implementation

[0033] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0034] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, use and processing of the relevant data must comply with relevant laws, regulations and standards, and corresponding operation entry points are provided for users to choose to authorize or refuse.

[0035] With the rapid development of internet technology, data in databases has become one of the most important assets for enterprises. Databases face a large amount of traffic access daily, which can pose potential risks and hidden dangers to their stability over time. Therefore, most databases in the current technology are equipped with disaster recovery backup centers. When a database fails, the disaster recovery backup center can send backup data to the database, enabling data recovery. However, in current technology, the disaster recovery backup center and any database are mostly located in the same place. Therefore, both the disaster recovery backup center and any database are prone to simultaneous failure, leading to data unavailability and increasing the risk of database paralysis.

[0036] To address the aforementioned technical problems, this application proposes the following technical concept: The inventors conceived of a method that deploys databases in different locations and combines a Canal server and a Canal client to achieve data synchronization between two databases. This ensures that if one database fails, the other can still function normally, reducing the risk of database failure. Detailed embodiments are described below.

[0037] refer to Figure 1 , Figure 1This is a schematic diagram of the database system provided in this application. In this embodiment, the database system specifically includes: at least two geographically deployed databases 101 and 102, each corresponding to a deployed Canal server 103 and a Canal client 104.

[0038] For any database 101, when the corresponding Canal server 103 detects that a query statement has been executed in any database 101, it pulls the logical logs generated by the execution of the query statement in any database 101.

[0039] The query statement can be a data definition statement or a data manipulation statement. The execution process of the data definition statement is very slow and may have a significant impact on data synchronization. Therefore, this application does not synchronize the data definition statement.

[0040] The query statement can be an SQL (Structured Query Language) statement. The logical log can be a binlog (Binary Log); the binlog contains update and change information for the SQL statements.

[0041] The Canal server (103) can be set up as follows: pull the Canal image using a container engine; create and run the Canal container; modify the Canal container's configuration information and restart the Canal container to complete the Canal server setup. Modifying the Canal container's configuration information may include: the database address (canal.instance.master.address); the database username (canal.instance.dbUsername); and the database password (canal.instance.dbPassword).

[0042] Among them, the Canal client 104 and the Canal server 103 are bound together.

[0043] When the Canal client 104 corresponding to any database 101 detects that the Canal server 103 has pulled the logical log, it obtains the logical log and converts the logical log into a query statement entity.

[0044] The query entity can be a CanalEntry (Canal Entry, Canal entry) entity.

[0045] Specifically, the logical logs are first parsed to extract key information, and then this key information is encapsulated into a query statement entity.

[0046] The Canal client 104 generates the corresponding query statement based on the data change information in the query statement entity.

[0047] Specifically, it retrieves the query statement type, column data values ​​before synchronization, column data values ​​after synchronization, and column attributes from the data change information.

[0048] The data change information can include: header, entryType, beforeColumns, and afterColumns. The header contains metadata information such as log location, event type, database name, table name, and column attributes. The entryType indicates the type of change statement, such as insert, update, or delete. beforeColumns represents the column data values ​​before synchronization, and afterColumns represents the column data values ​​after synchronization.

[0049] Generate the corresponding query statement based on the query statement type, the column data values ​​before synchronization, the column data values ​​after synchronization, and the column attributes.

[0050] In one possible design, the query statement is of type insert; accordingly, based on the query statement type, the column data value before synchronization, the column data value after synchronization, and the column attributes, a corresponding query statement is generated, including: determining that the column data value before synchronization is zero, and the column data value after synchronization is the inserted value; determining the attributes of the column containing the inserted value; and generating a corresponding query statement based on the inserted value and the attributes of the column containing the inserted value.

[0051] In one possible design, the query statement is of type update. Accordingly, based on the query statement type, the column data values ​​before synchronization, the column data values ​​after synchronization, and the column attributes, a corresponding query statement is generated, including: determining that the column data values ​​before synchronization are the values ​​before the update, and the column data values ​​after synchronization are the values ​​after the update; determining the attributes of the column containing the updated values; and generating a corresponding query statement based on the values ​​before the update, the updated values, and the attributes of the column containing the updated values.

[0052] In one possible design, the query statement is of type delete. Accordingly, based on the query statement type, the column data value before synchronization, the column data value after synchronization, and the column attributes, a corresponding query statement is generated, including: determining that the column data value before synchronization is the value before deletion, and the column data value after synchronization is zero; determining the attributes of the column containing the value before deletion; and generating a corresponding query statement based on the value before deletion and the attributes of the column containing the value before deletion.

[0053] Canal client 104 determines whether the type of the corresponding query statement is a preset type. If the type of the corresponding query statement is a preset type, it determines whether the corresponding query statement is a query statement that has been executed by other databases 102. If the corresponding query statement is not a query statement that has been executed by other databases 102, it sends the corresponding query statement to each other database 102.

[0054] The preset types can be: insert type and / or update type.

[0055] If the type of the corresponding query statement is not the preset type, the corresponding query statement will be sent to each of the other databases 102.

[0056] Among them, types that are not preset types can be deleted types.

[0057] In other databases 102, each database executes the corresponding query statement to complete the data synchronization of all databases.

[0058] In summary, the database system provided in this application deploys at least two databases in different locations. After a query statement is executed in any database, the logical log of the data is pulled through the Canal server, and the query statement is obtained through the Canal client based on the logical log. Based on the query statement, data synchronization is achieved with other databases, thereby avoiding the risk of database failure and unavailability.

[0059] based on Figure 1 This application embodiment focuses on describing the process by which the Canal client 104 determines whether a corresponding query statement is a query statement that has been executed in other databases. The preset types are insert type and / or update type, and both insert type and update type queries are configured with unique direction data information. All databases are configured with a status table to store the direction data information, as detailed below:

[0060] The directional data information configured in the corresponding query statement is compared with the directional data information stored in the status tables of other databases.

[0061] Table 1 is a status table from another database. As shown in Table 1, "Columns" are column information for direction data, and "Remarks" are the starting points for direction data.

[0062] Table 1. Status Tables in Other Databases

[0063] idc varchar(255) no Data Center db_cluster varchar(255) no Database cluster

[0064] Among them, a distributed unique primary key generation mechanism is used to generate a corresponding primary key for each direction data information in each state table, which ensures the uniqueness of the direction data information and maintains the consistency of the query statements corresponding to the direction data information.

[0065] If the direction data information corresponding to the query statement configuration is not stored in the status table of other databases, it is determined that the corresponding query statement has not been executed in other databases.

[0066] In one possible design, the Canal client is configured with a synchronization whitelist for query data. After determining that the corresponding query statement has not been executed in other databases, the Canal client checks whether the corresponding query statement exists in the synchronization whitelist. If the corresponding query statement exists in the synchronization whitelist, it is sent to the other databases; if the corresponding query statement does not exist in the synchronization whitelist, the process ends.

[0067] If the direction data information corresponding to the query statement configuration exists in the direction data information stored in the status table of another database, then it is determined that the corresponding query statement will be executed in another database.

[0068] In summary, by configuring a status table in the database to store direction data, when a query is of insert or update type, the direction data configured for that query is compared with the direction data stored in the status tables of other databases. If the corresponding direction data is not found in the status table, it means the query has not been executed in another database; if it is, it means the query has been executed in another database. By setting a status table, it is possible to quickly determine whether any execution statement has been executed in another database, avoiding duplicate operations and improving overall database performance.

[0069] In addition, by setting a whitelist in the Canal client, queries not on the whitelist can be prevented from being synchronized to other databases, thus improving database resource utilization. Furthermore, the whitelist can also prevent data synchronization problems caused by erroneous or malicious queries.

[0070] It should also be noted that the Canal client needs to record the synchronization log of the query statements; at the same time, the Canal client should be configured with an audit function to audit the query statements based on the synchronization log.

[0071] Figure 2 This is a schematic flowchart illustrating the processing method of the database system provided in an embodiment of this application. Figure 2As shown, the database system includes at least two geographically deployed databases, each corresponding to a deployed Canal server and a Canal client.

[0072] The methods include:

[0073] S201: For any database, when the Canal server for any database detects that a query statement has been executed in any database, pull the logical log generated by the execution of the query statement in any database.

[0074] S202: When the Canal client for any database detects that the Canal server has retrieved the logical logs, it obtains the logical logs and converts them into query statement entities.

[0075] S203: The Canal client generates the corresponding query statement based on the data change information in the query statement entity.

[0076] Specifically, the query statement type, column data values ​​before synchronization, column data values ​​after synchronization, and column attributes are obtained from the data change information; based on the query statement type, column data values ​​before synchronization, column data values ​​after synchronization, and column attributes, the corresponding query statement is generated.

[0077] In one possible design, the query statement is of type insert; accordingly, based on the query statement type, the column data value before synchronization, the column data value after synchronization, and the column attributes, a corresponding query statement is generated, including: determining that the column data value before synchronization is zero, and the column data value after synchronization is the inserted value; determining the attributes of the column containing the inserted value; and generating a corresponding query statement based on the inserted value and the attributes of the column containing the inserted value.

[0078] S204: The Canal client determines whether the type of the corresponding query statement is a preset type. If the type of the corresponding query statement is a preset type, it determines whether the corresponding query statement is a query statement that has been executed by other databases. If the corresponding query statement is not a query statement that has been executed by other databases, it sends the corresponding query statement to each other database.

[0079] S205 If the type of the corresponding query statement is not the preset type, then the corresponding query statement will be sent to the other databases.

[0080] S206: Each database in the other databases executes the corresponding query statement to complete the data synchronization of all databases.

[0081] In summary, the database system provided in this application deploys at least two databases in different locations. After a query statement is executed in any database, the logical log of the data is pulled through the Canal server, and the query statement is obtained through the Canal client based on the logical log. Based on the query statement, data synchronization is achieved with other databases, thereby avoiding the risk of database failure and unavailability.

[0082] Those skilled in the art will understand that all or part of the steps of the above-described method embodiments can be implemented by hardware related to program instructions. The aforementioned program can be stored in a computer-readable storage medium. When executed, the program performs the steps of the above-described method embodiments; and the aforementioned storage medium includes various media capable of storing program code, such as ROM, RAM, magnetic disks, or optical disks.

[0083] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of this application, and are not intended to limit them. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some or all of the technical features therein. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of this application.

Claims

1. A database system, characterized in that, include: At least two databases are deployed in different locations, each database corresponding to a deployed Canal server and a Canal client; For any database, when the Canal server corresponding to the database detects that the database has executed a query statement, it retrieves the logical logs generated by the database executing the query statement. When the Canal client corresponding to any of the database detects that the Canal server has retrieved the logical log, it obtains the logical log and converts the logical log into a query statement entity. The Canal client generates a corresponding query statement based on the data change information in the query statement entity. The Canal client determines whether the type of the corresponding query statement is a preset type. If the type of the corresponding query statement is a preset type, it determines whether the corresponding query statement is a query statement that has been executed by other databases. If the corresponding query statement is not a query statement that has been executed by other databases, it sends the corresponding query statement to each of the other databases. If the Canal client determines that the type of the corresponding query statement is not a preset type, it will send the corresponding query statement to other databases. Each of the other databases executes the corresponding query statement to complete data synchronization across all databases.

2. The system according to claim 1, characterized in that, The Canal client generates a corresponding query statement based on the data change information in the query statement entity, including: Obtain the query statement type, column data values ​​before synchronization, column data values ​​after synchronization, and column attributes from the data change information; Based on the type of the query statement, the column data values ​​before synchronization, the column data values ​​after synchronization, and the column attributes, a corresponding query statement is generated.

3. The system according to claim 2, characterized in that, The query statement mentioned above is of type insert; Accordingly, generating the corresponding query statement based on the query statement type, the column data values ​​before synchronization, the column data values ​​after synchronization, and the column attributes includes: The column data value before synchronization is determined to be zero, and the column data value after synchronization is the inserted value; Determine the attributes of the column containing the inserted value; Based on the inserted value and the attributes of the column in which the inserted value is located, a corresponding query statement is generated.

4. The system according to claim 2, characterized in that, The query statement mentioned above is of type update. Accordingly, generating the corresponding query statement based on the query statement type, the column data values ​​before synchronization, the column data values ​​after synchronization, and the column attributes includes: The column data values ​​before synchronization are determined to be the values ​​before the update, and the column data values ​​after synchronization are the values ​​after the update. Determine the attributes of the column containing the updated value; Based on the value before the update, the value after the update, and the attributes of the column containing the value after the update, a corresponding query statement is generated.

5. The system according to claim 2, characterized in that, The query statement mentioned above is of the delete type; Accordingly, generating the corresponding query statement based on the query statement type, the column data values ​​before synchronization, the column data values ​​after synchronization, and the column attributes includes: The column data value before synchronization is determined to be the value before deletion, and the column data value after synchronization is zero. Determine the attributes of the column containing the value before deletion; Generate a corresponding query statement based on the value before deletion and the attributes of the column containing the value before deletion.

6. The system according to claim 2, characterized in that, The preset types are insert type and / or update type, and the query statements of insert type and update type are configured with unique direction data information; all databases are configured with a status table to store the direction data information; Accordingly, the Canal client determines whether the corresponding query statement is a query statement that has been executed in other databases, including: The direction data information configured in the corresponding query statement is compared with the direction data information stored in the status tables of other databases; If the direction data information configured for the corresponding query statement is not present in the direction data information stored in the status table of the other database, then it is determined that the corresponding query statement was not executed in the other database. If the direction data information configured for the corresponding query statement exists in the direction data information stored in the status table of the other database, then it is determined that the corresponding query statement is executed in the other database.

7. The system according to any one of claims 1 to 6, characterized in that, The Canal client is configured with a synchronization whitelist for querying data; Accordingly, if the corresponding query statement is not a query statement that has been executed by other databases, then after sending the corresponding query statement to the other databases, the process further includes: Determine whether the corresponding query statement exists in the synchronization whitelist. If the corresponding query statement exists in the synchronization whitelist, then send the corresponding query statement to each of the other databases. If the corresponding query statement is not found in the synchronized whitelist, the process ends.

8. A processing method for a database system, characterized in that, The database system includes: at least two databases deployed in different locations, each database corresponding to a deployed Canal server and a Canal client; The method includes: For any database, when the Canal server corresponding to the database detects that the database has executed a query statement, it retrieves the logical logs generated by the database executing the query statement. When the Canal client corresponding to any of the database detects that the Canal server has retrieved the logical log, it obtains the logical log and converts the logical log into a query statement entity. The Canal client generates a corresponding query statement based on the data change information in the query statement entity. The system determines whether the type of the corresponding query statement is a preset type. If the type of the corresponding query statement is a preset type, the Canal client determines whether the corresponding query statement is a query statement that has been executed by other databases. If the corresponding query statement is not a query statement that has been executed by other databases, the system sends the corresponding query statement to each of the other databases. If the type of the corresponding query statement is not a preset type, then the corresponding query statement will be sent to other databases. Each of the other databases executes the corresponding query statement to complete data synchronization across all databases.

9. The method according to claim 8, characterized in that, The Canal client generates a corresponding query statement based on the data change information in the query statement entity, including: Obtain the query statement type, column data values ​​before synchronization, column data values ​​after synchronization, and column attributes from the data change information; Based on the type of the query statement, the column data values ​​before synchronization, the column data values ​​after synchronization, and the column attributes, a corresponding query statement is generated.

10. The method according to claim 9, characterized in that, The query statement mentioned above is of type insert; Accordingly, generating the corresponding query statement based on the query statement type, the column data values ​​before synchronization, the column data values ​​after synchronization, and the column attributes includes: The column data value before synchronization is determined to be zero, and the column data value after synchronization is the inserted value; Determine the attributes of the column containing the inserted value; Based on the inserted value and the attributes of the column in which the inserted value is located, a corresponding query statement is generated.

Images