CN112307054B - Reading and writing method for active and standby servers of double active databases - Google Patents
Reading and writing method for active and standby servers of double active databases Download PDFInfo
- Publication number
- CN112307054B CN112307054B CN202011299688.XA CN202011299688A CN112307054B CN 112307054 B CN112307054 B CN 112307054B CN 202011299688 A CN202011299688 A CN 202011299688A CN 112307054 B CN112307054 B CN 112307054B
- Authority
- CN
- China
- Prior art keywords
- agent
- sql statement
- database
- main
- active
- 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.)
- Active
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/07—Responding to the occurrence of a fault, e.g. fault tolerance
- G06F11/14—Error detection or correction of the data by redundancy in operation
- G06F11/1402—Saving, restoring, recovering or retrying
- G06F11/1446—Point-in-time backing up or restoration of persistent data
- G06F11/1448—Management of the data involved in backup or backup restore
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- Computing Systems (AREA)
- Quality & Reliability (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention relates to a reading and writing method for a main and standby server of a double-active database, belonging to the technical field of application of the main and standby servers of the double-active database; the technical problem to be solved is as follows: the improvement of a read-write method of a main and standby server of a double-active database is provided; the technical scheme for solving the technical problem is as follows: establishing two databases which can be mutually accessed; setting an agent for each of the two databases, wherein all the data addition, deletion and modification are executed through the agents; setting one of the agents as a main agent and the other as a secondary agent; setting a main agent responsible for sequencing the SQL statement, wherein the auxiliary agent has no right to sequence; when an application sends an SQL statement to a database for execution through a main agent, the main agent distributes a sequence number to the SQL statement before sending the SQL statement to the database for execution, and sends the sequence number and the SQL statement to a secondary agent; the invention is applied to the reading and writing of the active and standby servers of the double active databases.
Description
Technical Field
The invention discloses a read-write method for a main and standby server of a double-active database, belonging to the technical field of application of the main and standby servers of the double-active database.
Background
At present, a relational database is used in the industry to manage structured data, such as MySQL, postgressSQL and Oracle, and the relational database is widely applied to various social fields such as the Internet, finance and the like; in the scheme, a main database can be read and written, while a backup database is generally only readable and cannot be written, and when a main database server cannot work, a user can switch to use the backup database so as to efficiently use the database and reduce data loss; however, the above-mentioned usage needs the application program to modify the database pointing direction, and the backup database needs to monitor the health of the main database, which is easy to generate false alarm and error switching, resulting in inconsistent data.
Disclosure of Invention
In order to overcome the defects in the prior art, the invention aims to solve the technical problems that: the improvement of the read-write method of the active and standby servers of the double active databases is provided.
In order to solve the technical problems, the invention adopts the technical scheme that: a dual active database master/standby server read-write method includes the following steps:
the method comprises the following steps: establishing two databases, wherein the initial states of the two databases are required to be consistent;
step two: setting an agent for each of the two databases, wherein the two agents are required to be capable of communicating with each other, and all data addition, deletion and modification are performed through the agents;
step three: setting one of the agents as a main agent and the other as a secondary agent;
step four: setting a main agent responsible for sequencing the SQL statement, wherein the auxiliary agent has no right to sequence;
step five: when an application sends an SQL statement to a database for execution through a main agent, the main agent distributes a sequence number to the SQL statement before sending the SQL statement to the database for execution, and sends the sequence number and the SQL statement to a secondary agent;
step six: when an application sends an SQL statement through a secondary agent, the secondary agent firstly sends the SQL statement to a main agent, and the main agent distributes a sequence number to the SQL statement after receiving the SQL statement and sends the sequence number back to the secondary agent;
step seven: and the main agent or the auxiliary agent sequentially sends all numbered SQL sentences into the database to be executed according to the same sequence.
The main agent and the auxiliary agent arranged in the third step can be freely exchanged according to needs, and a database or an agent with large use amount is used as the main agent.
Compared with the prior art, the invention has the beneficial effects that: the server read-write improvement method provided by the invention can effectively improve the usability of the database, reduce the difficulty of double-activity database and hot standby operation, and reduce the risk of data loss, and can be used on a server-based database, such as mysql, and an embedded database based on files, such as sqlite.
Detailed Description
The invention provides a reading and writing method for a main server and a standby server of a double-active database, which mainly comprises the following steps:
the method comprises the following steps: two databases are set up, which can be off-site, but need to be accessible to each other. The initial state of the database is consistent. Agents communicate with each other, typically with an average communication delay;
step two: an agent is set up for each of the two databases, and all addition/deletion/modification of data should be performed by the agent. Query operations can generally be initiated directly to the database, bypassing the agent;
step three: setting one agent as a main agent and the other agent as a secondary agent;
step four: the main agent is responsible for sequencing the SQL statement, and the auxiliary agent has no right to sequence;
step five: when an application sends an SQL statement to a database for execution through a main agent, the main agent distributes a sequence number to the SQL statement before sending the SQL statement to the database for execution, and sends the sequence number and the SQL statement to a secondary agent;
step six: when an application sends an SQL statement through the secondary agent, the secondary agent sends the SQL statement to the main agent, and the main agent distributes a sequence number to the SQL statement after receiving the SQL statement and sends the sequence number back to the secondary agent;
step seven: and the main agent/the auxiliary agent sequentially sends all numbered SQL sentences into the database to be executed according to the same sequence. SQL sentences executed by the auxiliary agent have the possibility of delaying because the sequence number needs to be taken to the main agent, but database writing or modification is not influenced;
step eight: the administrator can freely set and change the roles of the main agent and the auxiliary agent. Typically, a database/agent with a relatively large amount of usage will be set as the master.
The invention provides a method for reading and writing a main and standby server of a double-active database, which enables the main server and the standby server to be readable and writable; during execution, two agents are added in front of a main server and a standby server, each agent is connected with a database server, and all data writing requests must be sent to a database through the agents.
Taking an agent in front of a main server as a main agent, and taking an agent in front of a backup server as a backup agent; when a write request (writing SQL sentences such as INSERT, UPDATE and DELETE) is sent to the agent, if the agent is a main agent, a sequence number is generated by the agent, the sequence number and the SQL sentences are sent to a backup agent, if the agent is the backup agent, the SQL sentences are sent to the main agent, the main agent returns a sequence number to the backup agent, and the sequence number is increased from 0; and finally, the main agent and the backup agent execute the received SQL statements (whether received from the client or the slave agent) in sequence according to the sequence numbers from small to large.
When the main server sends a fault, the roles of the exchange main agent and the backup agent can be manually set, the main server and the backup server can work normally no matter whether the main role and the backup role of the exchange agent are exchanged or not, but after the main server and the backup agent are correctly set, the writing performance of the backup server can be improved, because the action of requesting the serial number is executed locally, the time is hardly consumed, but the remote request (the backup agent takes the number of the main agent) is slow, and in addition, the read data can bypass the agent and is directly connected to the local database server for reading.
Finally, it should be noted that: the above embodiments are only used to illustrate the technical solution of the present invention, and not to limit the same; while the invention has been described in detail and with reference to the foregoing embodiments, it will be understood by those skilled in the art that: the technical solutions described in the foregoing embodiments may still be modified, or some or all of the technical features may be equivalently replaced; and the modifications or the substitutions do not make the essence of the corresponding technical solutions depart from the scope of the technical solutions of the embodiments of the present invention.
Claims (2)
1. A double-active database master and standby server read-write method is characterized in that: the method comprises the following steps:
the method comprises the following steps: establishing two databases, wherein the initial states of the two databases are required to be consistent;
step two: setting an agent for each of the two databases, wherein the two agents are required to be capable of communicating with each other, and all data addition, deletion and modification are performed through the agents;
step three: setting one of the agents as a main agent and the other as a secondary agent;
step four: setting a main agent responsible for sequencing the SQL statement, wherein the auxiliary agent has no right to sequence;
step five: when an application sends an SQL statement to a database for execution through a main agent, the main agent distributes a sequence number to the SQL statement before sending the SQL statement to the database for execution, and sends the sequence number and the SQL statement to a secondary agent;
step six: when an application sends an SQL statement through a secondary agent, the secondary agent firstly sends the SQL statement to a main agent, and the main agent distributes a serial number to the SQL statement after receiving the SQL statement and sends the serial number back to the secondary agent;
step seven: and the main agent or the auxiliary agent sequentially sends all numbered SQL sentences into the database to be executed according to the same sequence.
2. The method according to claim 1, wherein the method comprises the following steps: the main agent and the auxiliary agent arranged in the third step can be freely exchanged according to needs, and a database or an agent with large use amount is used as the main agent.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011299688.XA CN112307054B (en) | 2020-11-18 | 2020-11-18 | Reading and writing method for active and standby servers of double active databases |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011299688.XA CN112307054B (en) | 2020-11-18 | 2020-11-18 | Reading and writing method for active and standby servers of double active databases |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN112307054A CN112307054A (en) | 2021-02-02 |
| CN112307054B true CN112307054B (en) | 2023-02-28 |
Family
ID=74335186
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202011299688.XA Active CN112307054B (en) | 2020-11-18 | 2020-11-18 | Reading and writing method for active and standby servers of double active databases |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112307054B (en) |
Citations (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| KR20040039648A (en) * | 2002-11-04 | 2004-05-12 | (주)키씨 | Database tuning menagement method |
| CN101038591A (en) * | 2007-04-11 | 2007-09-19 | 华为技术有限公司 | Method and system for synchronizing data base |
| CN101706795A (en) * | 2009-11-30 | 2010-05-12 | 上海世范软件技术有限公司 | Method for synchronizing data of database in active/standby server |
| CN101876997A (en) * | 2009-12-01 | 2010-11-03 | 广州从兴电子开发有限公司 | Primary and secondary memory database synchronizing method and system |
| CN103336782A (en) * | 2013-05-30 | 2013-10-02 | 莱诺斯科技(北京)有限公司 | Relational distributed database system |
| WO2017177941A1 (en) * | 2016-04-13 | 2017-10-19 | 中兴通讯股份有限公司 | Active/standby database switching method and apparatus |
| CN110188114A (en) * | 2019-05-27 | 2019-08-30 | 上海达梦数据库有限公司 | A kind of optimization method of data manipulation, device, system, equipment and storage medium |
| CN111352766A (en) * | 2018-12-21 | 2020-06-30 | 中国移动通信集团山东有限公司 | Database double-activity implementation method and device |
| CN111382012A (en) * | 2020-03-03 | 2020-07-07 | 广州鼎甲计算机科技有限公司 | Backup method and device for MySQL cloud database, computer equipment and storage medium |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CA2838736A1 (en) * | 2011-06-07 | 2012-12-13 | Caris Mpi, Inc. | Molecular profiling for cancer |
-
2020
- 2020-11-18 CN CN202011299688.XA patent/CN112307054B/en active Active
Patent Citations (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| KR20040039648A (en) * | 2002-11-04 | 2004-05-12 | (주)키씨 | Database tuning menagement method |
| CN101038591A (en) * | 2007-04-11 | 2007-09-19 | 华为技术有限公司 | Method and system for synchronizing data base |
| CN101706795A (en) * | 2009-11-30 | 2010-05-12 | 上海世范软件技术有限公司 | Method for synchronizing data of database in active/standby server |
| CN101876997A (en) * | 2009-12-01 | 2010-11-03 | 广州从兴电子开发有限公司 | Primary and secondary memory database synchronizing method and system |
| CN103336782A (en) * | 2013-05-30 | 2013-10-02 | 莱诺斯科技(北京)有限公司 | Relational distributed database system |
| WO2017177941A1 (en) * | 2016-04-13 | 2017-10-19 | 中兴通讯股份有限公司 | Active/standby database switching method and apparatus |
| CN111352766A (en) * | 2018-12-21 | 2020-06-30 | 中国移动通信集团山东有限公司 | Database double-activity implementation method and device |
| CN110188114A (en) * | 2019-05-27 | 2019-08-30 | 上海达梦数据库有限公司 | A kind of optimization method of data manipulation, device, system, equipment and storage medium |
| CN111382012A (en) * | 2020-03-03 | 2020-07-07 | 广州鼎甲计算机科技有限公司 | Backup method and device for MySQL cloud database, computer equipment and storage medium |
Non-Patent Citations (2)
| Title |
|---|
| 基于存储双活技术的调度自动化系统高可靠性架构研究;宋英华 等;《供用电》;20200315;第37卷(第3期);58-63+70 * |
| 基于网络抓包的数据库即时备份方法;傅瑞军;《计算机工程与设计》;20101228(第24期);201-204 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN112307054A (en) | 2021-02-02 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10747745B2 (en) | Transaction execution commitment without updating of data row transaction status | |
| US7734580B2 (en) | Readable physical storage replica and standby database system | |
| Baker et al. | Megastore: Providing scalable, highly available storage for interactive services. | |
| US9311311B2 (en) | Archival management of database logs | |
| Sciascia et al. | Scalable deferred update replication | |
| US7996633B2 (en) | Sequencing transactions and operations | |
| Amiri et al. | Highly concurrent shared storage | |
| Lindström et al. | IBM solidDB: In-Memory Database Optimized for Extreme Speed and Availability. | |
| US7917596B2 (en) | Super master | |
| US20120054158A1 (en) | Reduced Disk Space Standby | |
| EP3722973B1 (en) | Data processing method and device for distributed database, storage medium, and electronic device | |
| CN108509462B (en) | Method and device for synchronizing activity transaction table | |
| JP7101566B2 (en) | Multiversion Concurrency Control (MVCC) in non-volatile memory | |
| US11182405B2 (en) | High throughput cross database table synchronization and transactional replication in federated databases | |
| CN104317944B (en) | A kind of timestamp dynamic adjustment concurrency control method based on formula | |
| CN101714167B (en) | Method and device for accessing monofile database | |
| WO2021225646A1 (en) | Database implementation for different application versions | |
| CN107533474A (en) | A kind of transaction methods and device | |
| WO2002082276A1 (en) | System and method for reorganizing stored data | |
| CN112307054B (en) | Reading and writing method for active and standby servers of double active databases | |
| CN110377230B (en) | Atomic writing method and device applied to solid state disk | |
| US10846014B2 (en) | Concept for group memory write instructions | |
| Dong et al. | Fine-Grained Re-Execution for Efficient Batched Commit of Distributed Transactions | |
| US20220027337A1 (en) | Parallel processing of changes in a distributed system | |
| Coelho et al. | PH1: A transactional middleware for NoSQL |
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 | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |