CN114281794A - Database system based on binary log server - Google Patents
Database system based on binary log server Download PDFInfo
- Publication number
- CN114281794A CN114281794A CN202111338379.3A CN202111338379A CN114281794A CN 114281794 A CN114281794 A CN 114281794A CN 202111338379 A CN202111338379 A CN 202111338379A CN 114281794 A CN114281794 A CN 114281794A
- Authority
- CN
- China
- Prior art keywords
- binary log
- database
- server
- log file
- binary
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a database system based on a binary log server, which relates to the technical field of database architecture optimization and comprises the following steps: the shared storage server is mounted on a main database and at least one binary log server and is used for storing the binary log files generated by the main database in real time; and the secondary database is connected with the binary log server, and the binary log server is used for acquiring the binary log file in real time from the shared storage server and continuously forwarding the binary log file to the secondary database when the primary database normally operates, acquiring a cut-off log time node of the secondary database when the primary database fails, and continuously forwarding the binary log file after the cut-off log time node to the secondary database, so that the master-slave switching of the primary database and the secondary database is realized. The method has the advantages that the situation of transaction loss during master-slave switching is effectively avoided, and perfect data protection is provided; and providing a suitable data source for various application scenes to meet different requirements of developers.
Description
Technical Field
The invention relates to the technical field of database architecture optimization, in particular to a database system based on a binary log server.
Background
A master-SLAVE architecture of a classical MySQL DATABASE is shown in fig. 1, in which a master DATABASE (master DATABASE) transmits a binary log (binlog) to a SLAVE DATABASE (SLAVE DATABASE) through a network, and the SLAVE DATABASE (SLAVE DATABASE) performs data replay by applying the binary log, so as to achieve the purpose of master-SLAVE synchronization. When the master DATABASE (master DATABASE) fails, the high availability management server (HA manager server) switches the SLAVE DATABASE (SLAVE DATABASE) role to the master DATABASE (master DATABASE) and disconnects the master DATABASE (master DATABASE) role, and the SLAVE DATABASE (SLAVE DATABASE) serves as a new master DATABASE (master DATABASE).
The master-slave architecture of fig. 1 may guarantee the reliability and integrity of data to some extent, but if part of the binary log is not ready to be transmitted to the slave database due to network delay or other reasons during the master-slave failover, a part of the transaction loss accident may be caused, which is not acceptable in the application scenarios such as payment finance.
Disclosure of Invention
Aiming at the problems in the prior art, the invention provides a database system based on a binary log server, which comprises the following components:
the shared storage server is mounted on a main database and at least one binary log server and is used for storing the binary log files generated by the main database in real time;
and the secondary database is connected with the binary log server, the binary log server is used for acquiring the binary log file from the shared storage server in real time and continuously forwarding the binary log file to the secondary database when the primary database normally operates, acquiring a cut-off log time node of the secondary database when the primary database fails, and continuously forwarding the binary log file after the cut-off log time node to the secondary database so as to realize the master-slave switching of the primary database and the secondary database.
Preferably, the number of the binary log servers is two, and the two binary log servers are mutually active and standby.
Preferably, the binary log server is provided with at least one service port for receiving and providing the data service of the binary log file according to the external service requirement.
Preferably, the service port is a pre-release port, and the service requirement is a pre-release requirement, the binary log server receives the service requirement through the pre-release port and restores the binary log file in the slave database to the restoration time point according to the external pre-release requirement including a restoration time node;
the secondary database is used for carrying out snapshot processing on the recovered binary log file and then mounting the binary log file to a pre-release database server connected with the secondary database, so that a test pre-release environment is provided for developers.
Preferably, the service port is an audit port, and the service requirement is an audit requirement, then the binary log server receives the service requirement 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;
and the audit server receives the binary log file, analyzes the binary log file and audits the binary log file.
Preferably, the service port is a warehouse port, and the service requirement is a data warehouse service requirement, the binary log server receives the service requirement through the 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;
and the data warehouse server receives the binary log file, analyzes the binary log file, and converts the row data of the analyzed binary log file into column data and stores the column data.
Preferably, the data warehouse server is configured to convert the parsed row data of the binary log file into the columnar data through a row-to-column storage engine component.
Preferably, the master database and the slave database are MySQL databases.
The technical scheme has the following advantages or beneficial effects:
1) the binary log server is combined with the shared storage server to provide services for the master database and the slave database, so that the situation of transaction loss during master-slave switching is effectively avoided, and perfect data protection is provided;
2) a service port is provided through the binary log server, the binary log file is distributed, and a proper data source is provided for various application scenes so as to meet different requirements of developers.
Drawings
FIG. 1 is a diagram of a classical MySQL database master-slave architecture in accordance with a preferred embodiment of the present invention;
FIG. 2 is a diagram illustrating a database system based on a binary log server according to a preferred embodiment of the present invention;
FIG. 3 is a schematic diagram of a binary log server 3 according to a preferred embodiment of the present invention;
fig. 4 is a schematic diagram illustrating a structure of the binary log server 3 providing data services according to a preferred embodiment of the present invention.
Detailed Description
The invention is described in detail below with reference to the figures and specific embodiments. The present invention is not limited to the embodiment, and other embodiments may be included in the scope of the present invention as long as the gist of the present invention is satisfied.
In accordance with the above-mentioned problems occurring in the prior art, there is provided in a preferred embodiment of the present invention a database system based on a binary log server, comprising:
the shared storage server 1 is mounted on a main database 2 and at least one binary log server 3, and the shared storage server 1 is used for storing a binary log file generated by the main database 2 in real time;
the secondary database 4 is connected with the binary log server 3, the binary log server 3 is used for acquiring the binary log files from the shared storage server 1 in real time and continuously forwarding the binary log files to the secondary database 4 when the primary database 2 operates normally, acquiring a cut-off log time node of the secondary database 4 when the primary database 2 fails, and continuously forwarding the binary log files after the cut-off log time node to the secondary database 4, so that the master-slave switching between the primary database 2 and the secondary database 4 is realized.
Specifically, in this embodiment, the shared storage server 1 uses raid and dual controllers for protection. The shared storage server 1 is utilized to mount on a master database 2 and a binary log server 3 of a high availability architecture. The binary log file binlog generated on the master database 2 is written directly to the shared storage server 1. Two binary log servers 3 are connected to the shared storage server 1 in a highly available manner, and unlike the conventional master-slave architecture, the binary log files are not directly transmitted from the master database 2 to the slave database 4 through the network, but the latest and most complete binary log files are acquired by connecting the binary log servers 3 to the shared storage server 1. Therefore, the binary log server 3 has a complete binary log file in real time. And further forwards the binary log file to the slave database 4 so that the latest binary log file is obtained from the database, thereby applying the binary log file.
The slave database 4 takes over the role of master database 2 after a failure of the master database 2. If all the binary applications are not completed in the secondary database 4, the binary log server 3 compares the expiration log time nodes of the application expiration from the secondary database 4, and forwards the binary log files which are not yet applied to the secondary database 4 until the secondary database 4 is completely applied. The binary log server 3 obtains the complete log from the shared storage server 1 and transmits the complete log to the slave database 4 for application. Thus, the integrity of the production data is guaranteed.
In the preferred embodiment of the present invention, there are two binary log servers 3, and the two binary log servers 3 are active and standby each other.
In the preferred embodiment of the present invention, as shown in fig. 3, the binary log server 3 is provided with at least one service port 31 for receiving and providing the data service of the binary log file according to the external service requirement.
In a preferred embodiment of the present invention, as shown in fig. 4, the service port 31 is a pre-release port, and the service requirement is a pre-release requirement, then the binary log server 3 receives the pre-release port and restores the binary log file in the database 4 to the restoration time point according to the external pre-release requirement containing the restoration time node;
the slave database 4 is used for carrying out snapshot processing on the recovered binary log file and then mounting the binary log file to a pre-release database server 5 connected with the slave database 4, so as to provide a test pre-release environment for developers.
Specifically, the traditional slave database of the master-slave architecture can only serve as a read-only database to provide query services to the outside, in the embodiment, by providing the pre-release port, the binary log file at a specified time can be recovered, and online services such as writing, testing, querying and the like are provided to the outside in a snapshot mount mode, so that a test pre-release environment is provided for developers without affecting data and performance of the slave database, and the utilization rate of company assets is maximized.
Further, the specified time may be specified by a recovery time node included in the pre-release requirement, and if data two days ago needs to be recovered, the recovery time node may be a certain time node two days ago. It can be understood 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, and after the slave database 4 performs snapshot processing on the recovered binary log file and mounts the recovered binary log file to the pre-release database server 5, the binary log server 3 is further configured to update the slave database 4, so that the slave database 4 and the master database 2 achieve data synchronization again.
In a preferred embodiment of the present invention, as shown in fig. 4, the service port 31 is an audit port, and the service requirement is an audit requirement, then the binary log server 3 receives the service requirement through the audit port and distributes the stored binary log file to the audit server 6 connected to the binary log server 3 according to the external audit requirement;
and the audit server 6 receives the binary log file, analyzes the binary log file and audits the binary log file.
Specifically, when the traditional master-slave architecture is used for auditing, a master database needs to be queried or sensitive fields need to be audited from the master database, so that a large amount of performance of the master database or the slave database is occupied, and the performance of the master database or the slave database is reduced. 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 request, and the audit server 6 performs audit after analyzing the binary log file, where the audit includes but is not limited to positioning sensitive fields and non-compliance data, so as to prompt an application in time and prompt correction. It can be seen that in the embodiment, the auditing skips the query of the master database or the slave database, so that the auditing purpose is achieved without affecting the performance of the production database. Preferably, the audit server 6 is provided with a parsing component 61 to implement parsing of the binary log file.
In a preferred embodiment of the present invention, as shown in fig. 4, the service port 31 is a warehouse port, and the service requirement is a data warehouse service requirement, then the binary log server 3 receives the binary log file through the warehouse port and distributes the stored binary log file to a data warehouse server 7 connected to the binary log server 3 according to the external data warehouse service requirement;
the data warehouse server 7 receives the binary log file, analyzes the binary log file, and converts the line data of the analyzed binary log file into the column data and stores the column data.
Specifically, the conventional master-slave architecture can only provide query services by using a slave library, while the MySQL database is not good at doing OLAP (online data analysis), in this embodiment, through the warehouse counting port, the binary log server 3 can distribute the stored binary log file to the data warehouse server 7 after receiving the data warehouse service requirement, and then the data warehouse server analyzes, converts and stores the binary log file to generate a warehouse server, so as to provide warehouse service (OLAP service) for the data analysis department.
It is understood that, in addition to the three data services, the binary log file distributed by the binary log server 3 can provide data services including, but not limited to, real-time data analysis, data flow direction analysis, and data queue processing, and thus, the description thereof is omitted here.
In the preferred embodiment of the present invention, the data warehouse server 7 is used for converting the row data of the parsed binary log file into the columnar data through a row-to-column storage engine component 71.
In the preferred embodiment of the present invention, the master database 2 and the slave database 4 are MySQL databases.
While the invention has been described with reference to a preferred embodiment, it will be understood by those skilled in the art that various changes in form and detail may be made therein without departing from the spirit and scope of the invention.
Claims (8)
1. A binary log server based database system, comprising:
the shared storage server is mounted on a main database and at least one binary log server and is used for storing the binary log files generated by the main database in real time;
and the secondary database is connected with the binary log server, the binary log server is used for acquiring the binary log file from the shared storage server in real time and continuously forwarding the binary log file to the secondary database when the primary database normally operates, acquiring a cut-off log time node of the secondary database when the primary database fails, and continuously forwarding the binary log file after the cut-off log time node to the secondary database so as to realize the master-slave switching of the primary database and the secondary database.
2. The database system of claim 1, wherein there are two binary journal servers, and wherein the two binary journal servers are active and standby with each other.
3. The database system of claim 1, wherein 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.
4. The database system according to claim 3, wherein 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 restoration time point according to the external pre-release requirement including the restoration time node;
the secondary database is used for carrying out snapshot processing on the recovered binary log file and then mounting the binary log file to a pre-release database server connected with the secondary database, so that a test pre-release environment is provided for developers.
5. The database system according to claim 3, wherein the service port is an audit port, and the service requirement is an audit requirement, then the binary log server receives the service requirement 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;
and the audit server receives the binary log file, analyzes the binary log file and audits the binary log file.
6. The database system of claim 3, wherein the service port is a warehouse port and the service requirement is a data warehouse service requirement, the binary log server receives the binary log file through the 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;
and the data warehouse server receives the binary log file, analyzes the binary log file, and converts the row data of the analyzed binary log file into column data and stores the column data.
7. The database system of claim 6, wherein the data warehouse server is configured to convert the parsed row data of the binary log file into columnar data via a row-to-column storage engine component.
8. The database system of claim 1, wherein the master database and the slave database are MySQL databases.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111338379.3A CN114281794A (en) | 2021-11-12 | 2021-11-12 | Database system based on binary log server |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111338379.3A CN114281794A (en) | 2021-11-12 | 2021-11-12 | Database system based on binary log server |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN114281794A true CN114281794A (en) | 2022-04-05 |
Family
ID=80869217
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202111338379.3A Pending CN114281794A (en) | 2021-11-12 | 2021-11-12 | Database system based on binary log server |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN114281794A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO2024093263A1 (en) * | 2022-11-02 | 2024-05-10 | 华为技术有限公司 | Data processing system, method and apparatus, and related device |
-
2021
- 2021-11-12 CN CN202111338379.3A patent/CN114281794A/en active Pending
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO2024093263A1 (en) * | 2022-11-02 | 2024-05-10 | 华为技术有限公司 | Data processing system, method and apparatus, and related device |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107402963B (en) | Search data construction method, incremental data pushing device and equipment | |
| US9047392B2 (en) | System and method for conversion of JMS message data into database transactions for application to multiple heterogeneous databases | |
| CA3061603A1 (en) | System and method for storing contract data structures on permissioned distributed ledgers | |
| CA2533737C (en) | Fast application notification in a clustered computing system | |
| US8301600B1 (en) | Failover recovery in a distributed data store | |
| CN107958010B (en) | Method and system for online data migration | |
| US20020035559A1 (en) | System and method for a decision engine and architecture for providing high-performance data querying operations | |
| US20020152104A1 (en) | Synchronization of planning information in a high availability planning and scheduling architecture | |
| US11379828B2 (en) | Distributed computing and storage network implementing high integrity, high bandwidth, low latency, secure processing | |
| Spirovska et al. | Wren: Nonblocking reads in a partitioned transactional causally consistent data store | |
| CN104572689A (en) | Data synchronizing method, device and system | |
| CN110569269A (en) | data synchronization method and system | |
| US20210286799A1 (en) | Automated transaction engine | |
| CN113672350B (en) | Application processing method and device and related equipment | |
| CN114281794A (en) | Database system based on binary log server | |
| CN111404737B (en) | Disaster recovery processing method and related device | |
| WO2021082925A1 (en) | Transaction processing method and apparatus | |
| CN110555064A (en) | data service system and method for insurance business | |
| CN111581227A (en) | Event pushing method and device, computer equipment and storage medium | |
| US20240004902A1 (en) | Performance of row table to columnar table replication | |
| CN112905676A (en) | Data file importing method and device | |
| CN115757552A (en) | Bank historical data management system based on distributed micro-service | |
| US7024371B2 (en) | High availability planning and scheduling architecture | |
| CN115080666A (en) | Data synchronization method, system, electronic device and storage medium | |
| CN114218236A (en) | Database cluster metadata management method |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |