A database system based on binary log server

Abstract

The present invention provides a database system based on a binary log server, and relates to the technical field of database architecture optimization, including: a shared storage server, mounted on a master database and at least one binary log server, and used to store binary log files generated by the master database in real time; a slave database, connected to the binary log server, the binary log server is used to obtain binary log files in real time from the shared storage server when the master database is operating normally, and continuously forward them to the slave database, and obtain the cut-off log time node of the slave database when the master database fails, and continue to forward the binary log files after the cut-off log time node to the slave database, so as to realize the master-slave switching of the master database and the slave database. The beneficial effect is to effectively avoid the situation of transaction loss during the master-slave switching, and provide perfect data protection; provide suitable data sources for various application scenarios to meet the different needs of developers.

Description

Technical Field

[0001] The present invention relates to the technical field of database architecture optimization, and in particular to a database system based on a binary log server. Background Art

[0002] The classic MySQL database master-slave architecture is as follows Figure 1 As shown in the figure, the master database (master DATABASE) transmits binary logs (binlog) to the slave database (SLAVE Database) through the network, and the slave database (SLAVE Database) replays the data by applying the binary logs to achieve the purpose of master-slave synchronization. When the master database (master DATABASE) fails, the high availability management server (HA manager server) will switch the slave database (SLAVE Database) role to the master database (master DATABASE), and disconnect the master database (master DATABASE) role. The slave database (SLAVE Database) serves the outside world as the new master database (master DATABASE).

[0003] Figure 1 This master-slave architecture can ensure data reliability and integrity to a certain extent. However, if some binary logs are not transmitted to the slave database in time due to network delays or other reasons during the master-slave failover process, some transactions will be lost, which is unacceptable in application scenarios such as payment and finance. Summary of the invention

[0004] In view of the problems existing in the prior art, the present invention provides a database system based on a binary log server, comprising:

[0005] A shared storage server mounted on a main database and at least one binary log server, the shared storage server being used to store binary log files generated by the main database in real time;

[0006] The slave database is connected to the binary log server. The binary log server is used to obtain the binary log file in real time from the shared storage server and continuously forward it to the slave database when the master database is operating normally, and to obtain the cutoff log time node of the slave database when the master database fails, and continue to forward the binary log file after the cutoff log time node to the slave database, so as to realize the master-slave switching of the master database and the slave database.

[0007] Preferably, there are two binary log servers, and the two binary log servers serve as a primary and a backup for each other.

[0008] Preferably, the binary log server is provided with at least one service port for receiving and providing data services of the binary log file according to external service requirements.

[0009] Preferably, the service port is a pre-release port, and the service requirement is a pre-release requirement, then the binary log server receives through the pre-release port and restores the binary log file in the slave database to the recovery time point according to the external pre-release requirement including the recovery time node;

[0010] The slave database is used to mount the restored binary log file to a pre-release database server connected to the slave database after snapshot processing, so as to provide a test pre-release environment for developers.

[0011] Preferably, the service port is an audit port, and the service requirement is an audit requirement, then the binary log server receives through the audit port and distributes the stored binary log file to an audit server connected to the binary log server according to the external audit requirement;

[0012] The audit server receives the binary log file, and performs post-analysis audit on the binary log file.

[0013] Preferably, the service port is a data warehouse port, and the service requirement is a data warehouse service requirement, then the binary log server receives through the data warehouse port and distributes the stored binary log file to a data warehouse server connected to the binary log server according to the external data warehouse service requirement;

[0014] The data warehouse server receives the binary log file, parses the binary log file, converts the row data of the parsed binary log file into columnar data, and stores the converted data.

[0015] Preferably, the data warehouse server uses a row-to-column storage engine component to convert the row data of the parsed binary log file into columnar data.

[0016] Preferably, the master database and the slave database are MySQL databases.

[0017] The above technical solution has the following advantages or beneficial effects:

[0018] 1) The binary log server is combined with the shared storage server to provide services for the master and slave databases, effectively avoiding transaction loss during master-slave switching and providing complete data protection;

[0019] 2) Provide service ports through the binary log server, distribute binary log files, and provide suitable data sources for various application scenarios to meet the different needs of developers. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] Figure 1 A schematic diagram of a classic MySQL database master-slave architecture in a preferred embodiment of the present invention;

[0021] Figure 2 The present invention is a schematic diagram of a structure of a database system based on a binary log server in a preferred embodiment of the present invention;

[0022] Figure 3 This is a schematic diagram of the structure of the binary log server 3 in a preferred embodiment of the present invention;

[0023] Figure 4 This is a structural diagram of a binary log server 3 providing data services in a preferred embodiment of the present invention. DETAILED DESCRIPTION

[0024] The present invention is described in detail below in conjunction with the accompanying drawings and specific embodiments. The present invention is not limited to this embodiment, and other embodiments may also fall within the scope of the present invention as long as they conform to the gist of the present invention.

[0025] In a preferred embodiment of the present invention, based on the above problems existing in the prior art, a database system based on a binary log server is provided, comprising:

[0026] A shared storage server 1 is mounted on a main database 2 and at least one binary log server 3. The shared storage server 1 is used to store binary log files generated by the main database 2 in real time.

[0027] The slave database 4 is connected to the binary log server 3. The binary log server 3 is used to obtain the binary log files from the shared storage server 1 in real time and continuously forward them to the slave database 4 when the master database 2 is operating normally. When the master database 2 fails, the binary log server 3 obtains the cutoff log time node of the slave database 4 and continues to forward the binary log files after the cutoff log time node to the slave database 4, thereby realizing the master-slave switching between the master database 2 and the slave database 4.

[0028] Specifically, in this embodiment, the above-mentioned shared storage server 1 is protected by raid and dual controllers. The shared storage server 1 is mounted on the master database 2 and the binary log server 3 of the high-availability architecture. The binary log file binlog generated on the master database 2 is directly written to the shared storage server 1. The two binary log servers 3 are connected to the shared storage server 1 in a high-availability manner. Unlike the traditional master-slave architecture, the binary log file is not directly transmitted from the master database 2 to the slave database 4 through the network, but the binary log server 3 is connected to the shared storage server 1 to obtain the latest and most complete binary log file. Therefore, the binary log server 3 has a real-time and complete binary log file. Then forward the binary log file to the slave database 4, so that the slave database obtains the latest binary log file, and then applies the binary log file.

[0029] When the master database 2 fails, the slave database 4 takes over the role of the master database 2. If the slave database 4 has not completed all binary applications at this time, the binary log server 3 will compare the end-of-log time node of the slave database 4 application, and continue to forward the unapplied binary log files to the slave database 4 until they are fully applied. The binary log server 3 obtains the complete log from the shared storage server 1 and transmits it to the slave database 4 for application. Therefore, the integrity of the production data is guaranteed.

[0030] In a preferred embodiment of the present invention, there are two binary log servers 3, and the two binary log servers 3 serve as a primary and a backup for each other.

[0031] In a preferred embodiment of the present invention, Figure 3 As shown, the binary log server 3 is provided with at least one service port 31 for receiving and providing data services of binary log files according to external service requirements.

[0032] In a preferred embodiment of the present invention, Figure 3 and Figure 4 As shown, the service port 31 is a pre-release port, and the service demand is a pre-release demand. Then the binary log server 3 receives through the pre-release port and recovers the binary log file in the database 4 to the recovery time point according to the external pre-release demand including the recovery time node;

[0033] The slave database 4 is used to mount the restored binary log file to a pre-release database server 5 connected to the slave database 4 after snapshot processing, so as to provide a test pre-release environment for developers.

[0034] Specifically, the slave database of the traditional master-slave architecture can only provide query services to the outside world as a read-only database. In this embodiment, by providing a pre-release port, the binary log file of the specified time can be restored, and writing, testing, query and other online services can be provided to the outside world in the form of snapshot mounting, providing developers with a test pre-release environment without affecting the data and performance of the slave database, thereby maximizing the company's asset utilization.

[0035] Furthermore, the above-mentioned specified time can be specified by the recovery time node included in the pre-release requirement. For example, if data from two days ago needs to be restored, the recovery time node here can be a time node two days ago. It is understandable that when the pre-release requirement is not received, the slave database 4 and the master database 2 are usually in a data synchronization state. After the slave database 4 performs snapshot processing on the restored binary log file and mounts it to the pre-release database server 5, the binary log server 3 is also used to update the slave database 4 so that the slave database 4 and the master database 2 can achieve data synchronization again.

[0036] In a preferred embodiment of the present invention, Figure 3 and Figure 4 As shown, the service port 31 is an audit port, and the service requirement is an audit requirement. Then the binary log server 3 receives through the audit port and distributes the stored binary log files to an audit server 6 connected to the binary log server 3 according to the external audit requirement;

[0037] The audit server 6 receives the binary log file, and performs audit on the binary log file after parsing.

[0038] Specifically, when auditing in a traditional master-slave architecture, it is necessary to query the master database or the slave database to audit sensitive fields, which will occupy a large amount of performance of the master database or the slave database, resulting in a decrease in the performance of the master database or the slave database. In this embodiment, through the audit port, the binary log server 3 can distribute the binary log file to the audit server 6 after receiving the audit demand. The audit server 6 performs an audit after parsing the binary log file. The audit includes but is not limited to locating sensitive fields and non-compliant data, thereby promptly reminding the application and urging it to correct it. It can be seen that in this embodiment, the audit skips the query of the master database or the slave database, thereby achieving the audit purpose without affecting the performance of the production database. Preferably, the audit server 6 is provided with a parsing component 61 to implement the parsing of the binary log file.

[0039] In a preferred embodiment of the present invention, Figure 3 and Figure 4As shown, the service port 31 is a data warehouse port, and the service demand is a data warehouse service demand. Then the binary log server 3 receives through the data warehouse port and distributes the stored binary log files to a data warehouse server 7 connected to the binary log server 3 according to the external data warehouse service demand;

[0040] The data warehouse server 7 receives the binary log file, parses the binary log file, converts the row data of the parsed binary log file into columnar data, and stores the data.

[0041] Specifically, the traditional master-slave architecture can only use the slave database to provide query services, and the MySQL database is not good at doing OLAP (online data analysis). In this embodiment, through the data warehouse port, the binary log server 3 can distribute the stored binary log files to the data warehouse server 7 after receiving the data warehouse service demand, and then the data warehouse server uses the binary log files to parse, transform, and store the generated data warehouse server, providing data warehouse services (OLAP services) for the data analysis department.

[0042] It is understandable that, in addition to the above three data services, the binary log files distributed by the binary log server 3 can also provide data services including but not limited to real-time data analysis, data flow trend analysis, and data queue processing, which will not be described in detail here.

[0043] In a preferred embodiment of the present invention, the data warehouse server 7 uses a row-to-column storage engine component 71 to convert the row data of the parsed binary log file into columnar data.

[0044] In a preferred embodiment of the present invention, the master database 2 and the slave database 4 are MySQL databases.

[0045] The above description is only a preferred embodiment of the present invention, and does not limit the implementation mode and protection scope of the present invention. For those skilled in the art, it should be aware that all solutions obtained by equivalent substitutions and obvious changes made using the contents of this specification and illustrations should be included in the protection scope of the present invention.

Claims

1. A database system based on a binary log server, characterized in that: include: A shared storage server mounted on a main database and at least one binary log server, the shared storage server being used to store binary log files generated by the main database in real time; A slave database is connected to the binary log server. The binary log server is used to obtain the binary log file from the shared storage server in real time and continuously forward it to the slave database when the master database is running normally, and to obtain the cutoff log time node of the slave database when the master database fails, and continue to forward the binary log file after the cutoff log time node to the slave database, so as to realize the master-slave switching between the master database and the slave database; The binary log server is provided with at least one service port for receiving and providing data services of the binary log file according to external service requirements.

2. The database system according to claim 1, characterized in that: There are two binary log servers, and the two binary log servers serve as a primary and a backup for each other.

3. The database system according to claim 1, characterized in that: The service port is a pre-release port, and the service requirement is a pre-release requirement, then the binary log server receives through the pre-release port and restores the binary log file in the slave database to the recovery time point according to the external pre-release requirement including the recovery time node; The slave database is used to mount the restored binary log file to a pre-release database server connected to the slave database after snapshot processing, so as to provide a test pre-release environment for developers.

4. The database system according to claim 1, characterized in that: The service port is an audit port, and the service requirement is an audit requirement, then the binary log server receives through the audit port and distributes the stored binary log file to an audit server connected to the binary log server according to the external audit requirement; The audit server receives the binary log file, and performs post-analysis audit on the binary log file.

5. The database system according to claim 1, characterized in that: The service port is a data warehouse port, and the service requirement is a data warehouse service requirement, then the binary log server receives through the data warehouse port and distributes the stored binary log file to a data warehouse server connected to the binary log server according to the external data warehouse service requirement; The data warehouse server receives the binary log file, parses the binary log file, converts the row data of the parsed binary log file into columnar data, and stores the converted data.

6. The database system according to claim 5, characterized in that: The data warehouse server converts the row data of the parsed binary log file into columnar data through a row-to-column storage engine component.

7. The database system according to claim 1, characterized in that: The master database and the slave database are MySQL databases.

Images