A method for separating read and write operations in a database cluster

Abstract

The present invention discloses a method for separating read and write operations in a database cluster. By setting up middleware between the database cluster and the client, the middleware performs very detailed logical processing for separating read and write operations on all SQL statements, so as to accurately distribute the SQL statements sent by the client to the primary and standby nodes to the greatest extent, thereby greatly improving the overall efficiency of the database cluster.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and particularly to a method for separating read and write operations on a database cluster. Background Art

[0002] To ensure the security of data in a database cluster, a master-slave cluster environment with one master and multiple slaves is often used in a production environment. The working mode adopted is that all read and write operations of the client are sent to the master node. When the data on the master node changes, the data on the slave nodes also changes accordingly, so that the data information of the master and slave nodes is synchronized, thus achieving the purpose of master-slave synchronization. To further share the pressure on the master node and improve the utilization rate of the slave nodes, some people have proposed to add a query data function to the slave nodes through read-write separation. However, in the existing read-write separation scheme, queries on objects that have not been modified in the current transaction and previous transactions may still be assigned to the slave machine for execution. This will cause a large number of subsequent query statements in a long transaction to fail to be dispatched to the slave machine, ultimately affecting the efficiency of cluster data processing. Summary of the Invention

[0003] The present invention provides a method for separating read and write operations on a database cluster to solve the problem that the existing read-write separation scheme cannot accurately route SQL statements.

[0004] The present invention provides a method for separating read and write operations on a database cluster, the method comprising: determining whether the current SQL statement is sent to both the master node and the slave node at the same time. If so, sending the SQL statement to the master node and the slave node; if not, further determining whether the SQL statement is a read-type SQL statement or a write-type SQL statement;

[0005] For a write-type SQL statement, determining whether the SQL statement has an impact on subsequent SQL statements, and recording the SQL statements that have an impact on subsequent SQL statements in a transaction read-write splitter or a session read-write splitter;

[0006] Determining the master node or slave node to which all read SQL statements should be sent according to the transaction read-write splitter and the session read-write splitter;

[0007] Wherein, the transaction read-write splitter is created after starting a transaction to store preset objects of write statements in the transaction, and is destroyed after the transaction ends; the session read-write splitter is created when establishing a session connection, is used to store preset objects that are only used on the master node in the current session, and is destroyed after the session connection ends.

[0008] Optionally, before determining whether the current SQL statement is sent to both the master node and the slave node at the same time, the method further comprises:

[0009] Judge the current transaction state. If in a transaction, set a transaction flag; if not in a transaction, set a non-transaction flag.

[0010] Optionally, determine whether the SQL statement has an impact on subsequent SQL statements, and record the objects in the SQL statements that have an impact on subsequent SQL statements into a transaction read-write splitter or a session read-write splitter, including:

[0011] For preset objects in a transaction, record the preset objects into the transaction read-write splitter and mark the routing to the primary node; if not in a transaction, only record them in the cache.

[0012] For temporary preset objects, record them into the session read-write splitter and mark the routing to the primary node.

[0013] Optionally, determine whether the SQL statement has an impact on subsequent SQL statements, and record the objects in the SQL statements that have an impact on subsequent SQL statements into a transaction read-write splitter or a session read-write splitter, including:

[0014] For a write-type SQL statement carrying a transaction identifier and having no impact on subsequent read statements, only mark the SQL routing to the primary node;

[0015] For a write-type SQL statement carrying a transaction identifier and having an uncertain impact range, mark that the current SQL statement and all subsequent SQL statements in the transaction are sent to the primary node until the transaction ends;

[0016] For a write-type SQL statement carrying a transaction identifier and being able to identify all objects in the SQL statement, record the identified objects and their associated objects into the corresponding transaction read-write splitter, and mark the routing of the SQL statement to the primary node.

[0017] Optionally, the method further includes: for SQL statements with an indirect mutual influence relationship in the modification of preset objects, record the SQL statements with an influence relationship to provide a basis for sending the indirectly affected SQL statements to the primary and standby nodes.

[0018] Optionally, the method further includes: after the SQL statement modifies the table, adopt a time-delay processing mechanism to process the SQL statements in all subsequent sessions based on the data delay between the primary node database and the standby node data.

[0019] Optionally, when the SQL statement is sent to the standby node, enter the time-delay processing mechanism mode, that is, check whether the objects in the SQL are in the time-delay list. If so, send the SQL to the primary node; if not, send it to the standby node.

[0020] Optionally, the delay time in the delay processing mechanism is the stream replication delay time obtained from the database plus the stream replication delay time of the configuration file.

[0021] Optionally, after the SQL statements related to other objects such as creating users, inheriting tables, views, and triggers in the non-transaction mode are successfully executed, their objects are stored in the corresponding files, and if they fail, the cache is directly cleared;

[0022] After the entire transaction in the transaction mode is successfully executed, the objects in the cache are stored in the corresponding files, and if the transaction fails or is rolled back, the cache is cleared.

[0023] Optionally, the preset objects include tables, views, and triggers.

[0024] The beneficial effects of the present invention are as follows:

[0025] By setting a middleware between the database cluster and the client, the present invention performs very detailed read-write separation logic processing on all SQL statements through this middleware, so as to accurately distribute the SQL statements sent by the client to the master and standby nodes to the greatest extent, thereby greatly improving the overall efficiency of the database cluster.

[0026] The above description is only an overview of the technical solution of the present invention. In order to be able to understand the technical means of the present invention more clearly, it can be implemented according to the content of the specification. And in order to make the above and other purposes, features and advantages of the present invention more obvious and understandable, the specific embodiments of the present invention are specifically exemplified below. Brief Description of the Drawings

[0027] By reading the detailed description of the preferred embodiments below, various other advantages and benefits will become clear to those of ordinary skill in the art. The drawings are only for the purpose of showing the preferred embodiments and are not considered to be a limitation of the present invention. And throughout the drawings, the same reference numerals are used to represent the same components. In the drawings:

[0028] Figure 1 is a flowchart of a method for read-write separation of a database cluster provided by an embodiment of the present invention;

[0029] Figure 2 is a structural diagram of a method for read-write separation of a database cluster provided by an embodiment of the present invention;

[0030] Figure 3 is a flowchart of a method for judging transactions and non-transactions provided by an embodiment of the present invention;

[0031] Figure 4It is a schematic flowchart of the read and separation processing method for transactions and non-transaction modes provided by an embodiment of the present invention;

[0032] Figure 5 It is a schematic flowchart after the read-write separation processing provided by an embodiment of the present invention. Detailed implementation manners

[0033] In view of the problem that the existing read-write separation cannot accurately route SQL statements, the embodiment of the present invention sets up a middleware between the database cluster and the client, and through this middleware, very detailed read-write separation logic processing is performed on all SQL statements, so as to accurately distribute the SQL statements sent by the client to the primary and standby nodes to the greatest extent, thereby greatly improving the overall efficiency of the database cluster. The following further details the present invention in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and do not limit the present invention.

[0034] The current general read-write separation processing logic is to separately process SQL into two scenarios: transactions and non-transactions. When the session is in a non-transaction, the read statements are sent to the standby, and the write statements are sent to the primary. At the same time, when encountering situations such as temporary views and temporary tables, all subsequent statements in this session are sent to the primary. When in a transaction, the read-type SQL is sent to the standby node until a write-type SQL is encountered, and then all subsequent SQL statements are sent to the primary to ensure data accuracy.

[0035] In this solution, for queries on objects that have not been modified in the transaction and the previous transaction, they can actually be assigned to the standby machine for execution. However, the existing solution mechanically causes a large number of query statements after the write statement in a long transaction not to be dispatched to the standby machine, which will also reduce the overall efficiency of the database cluster.

[0036] That is to say, in the process of read-write separation, ordinary read-write separation needs to be considered, that is, write SQL is sent to the primary, and read SQL is sent to the standby. Secondly, it is necessary to consider that SQL related to temporary tables, temporary views, temporary domains, etc. can only be sent to the primary node. At the same time, the impact of the write statement in the transaction on the subsequent SQL needs to be considered, and the impact of the modification of one table in the transaction on other tables or views needs to be considered. Therefore, it is considered that after a table is modified, all tables and views affected by it should be sent to the primary node. When a table is modified, this table may cause other related tables to be modified, ultimately resulting in query errors.

[0037] Based on the above considerations, the embodiment of the present invention provides a method for read-write separation of a database cluster. Refer to Figure 1 , this method includes:

[0038] S101. Determine whether the current SQL statement is sent to both the primary node and the standby node at the same time. If so, proceed to S102; if not, proceed to S103;

[0039] S102. Send the SQL statement to the primary node and the standby node;

[0040] S103. Determine whether the SQL statement is a read-type SQL statement or a write-type SQL statement;

[0041] S104. For a write-type SQL statement, determine whether the SQL statement has an impact on subsequent SQL statements, and record the SQL statements that have an impact on subsequent SQL statements in the transaction read-write splitter or the session read-write splitter;

[0042] S105. Determine the primary node or standby node to which all read SQL statements should be sent according to the transaction read-write splitter and the session read-write splitter;

[0043] Among them, in the embodiment of the present invention, the transaction read-write splitter is created after starting a transaction to store the preset object of the write statement in the transaction, and is destroyed after the transaction ends; the session read-write splitter is created when establishing a session connection, and is used to store the preset object that is only used on the primary node in the current session, and is destroyed after the session connection ends.

[0044] As Figure 2 can be seen, in the embodiment of the present invention, a middleware is set between the database cluster and the client, and through this middleware, very detailed read-write separation logic processing is performed on all SQL statements, so that the SQL statements sent by the client can be distributed to the primary and standby nodes as accurately as possible to the greatest extent, thereby greatly improving the overall efficiency of the database cluster.

[0045] Specifically, before step S101 in the embodiment of the present invention, it further includes: determining the current transaction state. If it is in a transaction, set a transaction flag; if it is not in a transaction, set a non-transaction flag. The detailed process is specifically as Figure 3 shown.

[0046] The present invention sets a transaction flag and a non-transaction flag for subsequent parsing and use of SQL statements.

[0047] That is to say, for a postgres database cluster, some statements such as some SET statements and transaction control statements need to be sent to both the primary and standby database nodes simultaneously. If only distinguishing between read and write is done, it is easy to cause problems such as inconsistent settings in the primary and standby databases. In the embodiments of the present invention, for SQL statements, they are not simply divided into select statements and non-select statements. Instead, after each SQL statement is parsed, specific read-write separation judgments are made with reference to the read-write attributes of the SQL itself, the transaction read-write splitter, and the session read-write splitter. Therefore, the present invention can accurately distribute the SQL statements sent by the client to the primary and standby nodes, ultimately greatly improving the user experience.

[0048] When specifically implemented, step S104 in the embodiments of the present invention specifically includes:

[0049] For a preset object in a transaction, record the preset object to the transaction read-write splitter and mark it as routed to the primary node. If it is not a transaction, it is only recorded in the cache. For a temporary preset object, record it in the session read-write splitter and mark it as routed to the primary node.

[0050] It should be noted that the preset objects in the embodiments of the present invention are tables, views, triggers, etc. Specifically, those skilled in the art can set them according to actual needs, and the present invention does not make specific limitations in this regard.

[0051] For read-write separation in the embodiments of the present invention, first, each SQL is parsed, and the read-write attribute of the current SQL itself is judged. Then, further read-write judgments are made according to actual situations such as transactions, non-transactions, session read-write splitters, transaction read-write splitters, etc.

[0052] Specifically, for a write-type SQL statement carrying a transaction identifier and having no impact on subsequent read statements, only mark the SQL as routed to the primary node; for a write-type SQL statement carrying a transaction identifier and having an uncertain influence range, mark the current SQL statement and all subsequent SQL statements in the transaction as sent to the primary node until the transaction ends; for a write-type SQL statement carrying a transaction identifier and being able to identify all objects in the SQL statement, record the identified objects and their associated objects to the corresponding transaction read-write splitter and mark the SQL statement as routed to the primary node.

[0053] As Figure 4 shown, when specifically implemented, the embodiments of the present invention first judge whether the current SQL should be sent to both the primary and standby nodes simultaneously (such as some SET-type SQL and transaction control statements need to be sent to both the primary and standby nodes). If so, directly send the SQL to both the primary and standby nodes simultaneously;

[0054] If it is determined to be a read statement and the current is in a transaction, it is necessary to determine whether the transaction read-write shunt and the session read-write shunt have the object of this SQL. If it exists, mark it to be sent to the primary node; otherwise, send it to the standby node.

[0055] If it is determined to be a write statement, the following processing needs to be performed:

[0056] If it is an inherited table, view, trigger, etc. and is in a transaction, record its object to the transaction read-write shunt and mark the routing to the primary node; if it is not in a transaction, only record it to the cache;

[0057] If it is to create a temporary table, temporary view, etc., record its object to the session read-write shunt and mark the routing to the primary node;

[0058] In a transaction, if it is a write statement and has no impact on subsequent read statements, only mark the SQL routing to the primary node;

[0059] In a transaction, if it is a write statement and the impact scope is temporarily uncertain, mark the current SQL and all subsequent SQLs in the transaction to be sent to the primary node until the transaction ends;

[0060] In a transaction, if it is a write statement and all objects in the SQL can be identified, it is necessary to record the object and its associated objects (tables, views, etc.) to the transaction read-write shunt and mark the SQL routing to the primary node.

[0061] It can be seen that the present invention analyzes and records the object information of related tables, views, etc. modified by the write SQL in the transaction, and at the same time can judge the inherited tables, views affected by the object and the objects affected by the trigger, etc., and store the object in the transaction read-write shunt to provide a basis for reading and writing for subsequent SQLs.

[0062] The method described in the embodiment of the present invention further includes: for SQL statements with an indirect mutual influence relationship in the modification of preset objects, record the SQL statements with an influence relationship to provide a basis for sending the indirectly affected SQL statements to the primary and standby nodes.

[0063] Specifically, in the embodiment of the present invention, for the modification of a table with an indirect mutual influence relationship, such as objects like inherited tables, views, triggers, etc., it is necessary to record their mutual dependency relationships. This provides a basis for sending the indirectly affected SQL statements to the primary and standby nodes.

[0064] For the inheritance table, in the embodiment of the present invention, the relationships among all inheritance tables are obtained at startup and saved in the key:value data structure. When it is parsed that a certain SQL modifies the table value, the indirectly modified table can be found from the data structure of the inheritance table. When creating an inheritance table through the middleware, the relationships between the child table and the parent table (including the parent tables on which the parent table depends) are parsed and saved.

[0065] For views and triggers, a processing method similar to that of the inheritance table is adopted for the same views and triggers, and the dependency relationships among the views, triggers, and tables are saved in the key:value format. When a table is modified, the affected views and tables can be retrieved from this dependency relationship in a timely manner, providing a basis for accurately judging the subsequent affected SQL statements.

[0066] Due to the extremely short data delay between the primary and standby databases. During this short period, when querying information on the standby node, an incorrect result may be obtained. Therefore, the present invention adopts a time delay processing mechanism to judge the SQL primary and standby.

[0067] The method described in the embodiment of the present invention further includes: after the SQL statement modifies the table, a time delay processing mechanism is adopted to process the SQL statements in all subsequent sessions based on the data delay between the primary node database and the standby node data. Among them, the time delay in the time delay processing mechanism is the streaming replication delay time obtained from the database plus the streaming replication delay time in the configuration file.

[0068] When the SQL statement is sent to the standby node, it enters the time delay processing mechanism mode, that is, by checking whether the object in the SQL is in the time delay list. If it exists, the SQL is sent to the primary node; if not, it is sent to the standby node.

[0069] Specifically, the time delay processing mechanism of the embodiment of the present invention includes:

[0070] At startup, the minimum streaming replication delay time is obtained from the configuration file, and the streaming replication delay time is obtained from the database at regular intervals.

[0071] Calculate the streaming replication delay time as the streaming replication delay time obtained from the database plus the configuration file time.

[0072] When the SQL is sent to the standby node, it enters the time delay processing mechanism mode, that is, by checking whether the SQL object is in the time delay list. If it exists, the SQL is sent to the primary node; if not, it is sent to the standby node.

[0073] The present invention solves the problem of short-term data inconsistency caused by streaming replication between the primary and standby through the time delay processing mechanism, and further improves the user experience to a certain extent.

[0074] Such as Figure 5As shown in the figure, in specific implementation, after the SQL statements related to other objects such as creating users, inheritance tables, views, triggers, etc. in the non-transaction mode are successfully executed in the embodiments of the present invention, their objects are stored in the corresponding files, and if they fail, the cache is directly cleared; after the overall transaction in the transaction mode is successfully executed, the objects in the cache are stored in the corresponding files, and if the transaction fails or rolls back, the cache is cleared.

[0075] Generally speaking, the present invention specifically determines whether the SQL is sent to the primary or standby node by setting a transaction read-write splitter and a session read-write splitter. For the problem of data inconsistency between the primary and standby in a short period of time, a time-delay mechanism is adopted for processing, judging and recording the creation of temporary tables, temporary views, temporary domains, etc., and at the same time analyzing and recording the object information of related tables, views, etc. modified by the write SQL in the transaction. At the same time, it can judge the inheritance tables, views and objects affected by triggers of the object, etc., and store the object in the transaction read-write splitter to provide a read-write basis for subsequent SQL. Through the above processing, the SQL statements sent by the client can be distributed to the primary and standby nodes as accurately as possible to the greatest extent, thereby greatly improving the overall efficiency of the database cluster.

[0076] The following will be combined with Figures 3 - 5 A specific example is used to explain and illustrate the method of the present invention in detail:

[0077] The problems to be solved by the read-write separation in the embodiments of the present invention are as follows: In the process of read-write separation, ordinary read-write separation needs to be considered, that is, DML type statements need to send write type statements to the primary, and read type statements to the standby. Other DDL type statements, transaction control statements, etc. need to be judged according to the actual situation whether they are sent to the primary or standby node or even sent to the primary and standby at the same time; it is necessary to consider that whether in a transaction or not, each SQL statement needs to be parsed and processed, and the SQL cannot be simply divided into select and non-select statements for processing. Because some SQLs need to be sent by all nodes at the same time; due to the primary-standby consistency problem, query data may be incorrect, and the impact of each SQL on other sessions needs to be considered; secondly, it is necessary to consider that SQLs related to temporary tables, temporary views, temporary domains, etc. can only be sent to the primary node; it is necessary to consider the impact of using DDL statements, transaction control statements, and write statements in a transaction on the SQL after the statement in the transaction; it is necessary to consider the impact of the modification of a table in a transaction on other tables or views, so that after a table is modified, all the tables and views affected by it should be sent to the primary node; when a table is modified, this table may cause other related tables to be modified, resulting in query errors.

[0078] To solve at least one of the above problems, the solution adopted by the present invention is:

[0079] Step 1: Determine the current transaction state. If in a transaction, set a transaction flag and enter the transaction processing mode; if not in a transaction, set a non-transaction flag and enter the non-transaction processing mode, as specifically shown in Figure 3 as follows;

[0080] Step 2: For read-write splitting, each SQL needs to be parsed first to determine the read-write attribute of the current SQL itself, and then further read-write judgments are made according to actual situations such as transactions, non-transactions, session read-write shuntters, transaction read-write shuntters, etc., as specifically shown in Figure 4 as follows;

[0081] First, determine whether the current SQL should be sent to both the primary and standby nodes simultaneously (such as some SET-type SQLs and transaction control statements need to be sent to both the primary and standby nodes). If so, directly send the SQL to both the primary and standby nodes;

[0082] If it is determined to be a read statement and currently in a transaction, it is necessary to determine whether the objects of this SQL exist in the transaction read-write shunt and the session read-write shunt. If they exist, mark it to be sent to the primary node, otherwise send it to the standby node;

[0083] If it is determined to be a write statement, the following detailed processing steps are required;

[0084] If it is an inherited table, view, trigger, etc. and in a transaction, record its object in the transaction read-write shunt and mark it to be routed to the primary node; if not in a transaction, only record it in the cache;

[0085] If it is a creation of a temporary table, temporary view, etc., record its object in the session read-write shunt and mark it to be routed to the primary node;

[0086] In a transaction, if it is a write statement and has no impact on subsequent read statements, only mark the SQL to be routed to the primary node;

[0087] In a transaction, if it is a write statement and the impact range is temporarily uncertain, mark the current SQL and all subsequent SQLs in the transaction to be sent to the primary node until the transaction ends;

[0088] In a transaction, if it is a write statement and all objects in the SQL can be identified, it is necessary to record the object and its associated objects (tables, views, etc.) in the transaction read-write shunt and mark the SQL to be routed to the primary node;

[0089] Step 3: For the modification of tables, there are indirect mutual influence relationships, such as objects like inherited tables, views, triggers, etc. We need to record their mutual dependency relationships. This provides a basis for sending the indirectly affected SQL statements to the primary and standby nodes.

[0090] Inheritance table: At startup, obtain the relationships among all inheritance tables and save them in a key:value data structure. When it is parsed that a certain SQL modifies the table value, the indirectly modified table can be found from the data structure of the inheritance table. When creating an inheritance table through the middleware, the relationships between the child table and the parent table (including the parent tables on which the parent table depends) are parsed and saved.

[0091] Views and triggers: Similarly, views and triggers adopt a processing method similar to that of the inheritance table, and save the dependency relationships between views, triggers and tables in the key:value format. When a table is modified, the affected views and tables can be retrieved from this dependency relationship in a timely manner, providing a basis for accurately judging the subsequent affected SQL statements.

[0092] Step 4: Adopt a time delay mechanism to handle the impact of SQL on the SQL statements in all subsequent sessions within a certain period of time after modifying the table;

[0093] Due to the extremely short data delay between the primary and standby databases. During this short period of time, when querying information on the standby node, an incorrect result may be queried. Therefore, a time delay processing mechanism needs to be adopted to judge the primary and standby of SQL.

[0094] Time delay processing mechanism: At startup, obtain the minimum streaming replication delay time from the configuration file and regularly obtain the streaming replication delay time from the database. Calculate the streaming replication delay time as the streaming replication delay time obtained from the database plus the time in the configuration file.

[0095] When an SQL is sent to the standby node, it enters the time delay processing mechanism mode, that is, by checking whether the SQL object is in the time delay list. If it exists, the SQL is sent to the primary node; if not, it is sent to the standby node;

[0096] Step 5: In non-transaction mode, if the SQL statements for creating users, inheritance tables, views, triggers, etc. are executed successfully, their objects are stored in the corresponding files; if they fail, the cache is directly cleared. In transaction mode, when the transaction is executed successfully as a whole, the objects in the cache to be stored are stored in the corresponding files; if the transaction fails or is rolled back, the cache is cleared, as specifically Figure 5 shown.

[0097] Compared with the existing read-write separation solution, since the embodiments of the present invention adopt a more detailed and reasonable read-write separation processing logic, whether in a transaction or not, the read-write separation module can very accurately determine whether a specified SQL statement is a read statement, and can carefully determine whether the table information queried by the read statement is affected by the previous SQL statement, so as to determine which node the SQL is sent to. Through the processing of the read-write separation module, the pressure on the standby node in the database cluster can be significantly reduced, and the pressure on the master node can be reduced, thus significantly improving the overall efficiency of the database cluster.

[0098] Although the preferred embodiments of the present invention have been disclosed for illustrative purposes, those skilled in the art will realize that various improvements, additions and substitutions are also possible. Therefore, the scope of the present invention should not be limited to the above embodiments.

Claims

1. A method for separating read and write operations in a database cluster, characterized in that Applied to middleware, including: Determine whether the current SQL statement is sent to both the primary node and the standby node at the same time. If so, send the SQL statement to the primary node and the standby node. If not, further determine whether the SQL statement is a read-type SQL statement or a write-type SQL statement; For write-type SQL statements, for the preset objects in the transaction, record the preset objects to the transaction read-write shunt and mark the routing to the primary node. If it is not a transaction, only record it in the cache; for temporary preset objects, record them to the session read-write shunt and mark the routing to the primary node; for write-type SQL statements with a transaction identifier and the SQL statement has no impact on subsequent read statements, only mark the SQL routing to the primary node; for write-type SQL statements with a transaction identifier and the impact range of the SQL statement is uncertain, mark that the current SQL statement and all subsequent SQL statements in the transaction are sent to the primary node until the transaction ends; for write-type SQL statements with a transaction identifier and all objects in the SQL statement can be identified, record the identified objects and their associated objects to the corresponding transaction read-write shunt and mark the routing of the SQL statement to the primary node; Judge the primary node or standby node to which all read statement SQL statements should be sent according to the transaction read-write shunt and the session read-write shunt; Among them, the transaction read-write shunt is created after starting a transaction to store the preset objects of the write statements in the transaction. After the transaction ends, the transaction read-write shunt is destroyed; the session read-write shunt is created when establishing a session connection, used to store the preset objects of the current session and only used on the primary node, and is destroyed after the session ends the connection.

2. The method according to claim 1, wherein Before judging whether the current SQL statement is sent to both the primary node and the standby node at the same time, the method further includes: Judge the current transaction state. If it is in a transaction, set a transaction flag. If it is not in a transaction, set a non-transaction flag.

3. The method according to claim 1, wherein The method further includes: For SQL statements with an indirect mutual influence relationship in the modification of preset objects, record the SQL statements with an influence relationship to provide a basis for the indirectly affected SQL statements to be sent to the primary and standby nodes.

4. The method according to any one of claims 1 to 3, characterized in that, The method further includes: After the SQL statement modifies the table, adopt a time-delay processing mechanism to process all subsequent SQL statements in the session based on the data delay between the primary node database and the standby node data.

5. The method according to claim 4, characterized in that When the SQL statement is sent to the standby node, enter the time-delay processing mechanism mode, that is, check whether the object in the SQL is in the time-delay list. If it exists, the SQL is sent to the primary node. If not, it is sent to the standby node.

6. The method according to claim 5, characterized in that The time-delay in the time-delay processing mechanism is the streaming replication delay time obtained from the database plus the streaming replication delay time in the configuration file.

7. The method according to any one of claims 1-3, characterized in that After the SQL statements related to creating users, inheriting tables, views, triggers, and other objects in non-transaction mode are successfully executed, the objects are stored in the corresponding files. If it fails, the cache is directly cleared; After the entire transaction in transaction mode is successfully executed, the objects in the cache are stored in the corresponding files. If the transaction fails or is rolled back, the cache is cleared.

8. The method according to any one of claims 1-3, wherein The preset objects include tables, views, and triggers.

Images