A node identification method and device for MySQL group replication

Abstract

The present invention relates to the technical field of MySQL group replication node identification, and specifically to a node identification method and device for MySQL group replication, including S1: related logic processing in the service startup initialization phase; S2: interactive collaboration between a node connection management module and a transaction processing flow; S3: interactive collaboration between a node connection management module and a transaction processing flow when an abnormal failure occurs in a MySQL group replication node. The present invention satisfies quasi-real-time detection, discovery, and adaptation of automatic master-slave switching of MGR nodes without changing the network link between the application system and the data through a node connection management module, a node data register, and a node status identification module, avoids connection reconstruction actions, reduces the complexity of application system configuration, and improves the service availability of the application system.

Description

Technical Field

[0001] The present invention relates to the technical field of MySQL group replication node identification, and in particular to a node identification method and device for MySQL group replication. Background Art

[0002] MySQL: A database software that stores business data and provides read and write services to application systems. It has transactional features.

[0003] MySQL Group Replication: It is a data synchronization mechanism for MySQL databases and also a deployment method for MySQL databases. In terms of data, MySQL Group Replication has high security, high consistency, and high fault tolerance. In terms of services, MySQL Group Replication has high scalability and high flexibility.

[0004] MySQL group replication node: an instance that makes up MySQL group replication. A MySQL group replication cluster consists of at least two instances and can support multiple instances.

[0005] According to the type of services provided to the outside world, MySQL group replication instances are divided into: master nodes - providing read and write services; slave nodes - providing read services;

[0006] According to the way of providing write services to the outside world, MySQL group replication is divided into: single-master mode - only one master node provides read and write services, and the remaining nodes (slave nodes) provide read-only services; multi-master mode - there are nodes that provide read and write services. MySQL group replication single-master mode supports the election of a new master node from the slave nodes and automatic switching when the master node fails;

[0007] In online transaction processing (OLTP) application systems, in order to avoid the increase in database time consumption caused by multiple write operations and the impact on business processing performance, MySQL group replication single-master mode is usually selected. Automatic switching to adapt to single-master mode has become a key factor for application systems to correctly and efficiently access MySQL group replication databases;

[0008] Currently, the existing technology uses two methods:

[0009] 1. Implement node identification and access of MySQL group replication based on underlying infrastructure: The adaptation and shielding of automatic master-slave switching of MySQL group replication can be implemented in the underlying infrastructure (such as DNS, etc.). Monitor MySQL group replication through monitoring tool software. Once automatic switching is found, adjust the mapping between domain name and IP in DNS (manually or automatically). The application system can correctly access the MySQL group replication database through the domain name;

[0010] 2. Implement node identification and access of MySQL group replication based on middleware: Database middleware (such as ProxySQL, etc.) can be used to adapt and shield the automatic master-slave switching of MySQL group replication, and the application system accesses the MySQL group replication database through the middleware;

[0011] However, both current methods have certain disadvantages:

[0012] 1. The existing technology discovers the automatic switching of MySQL group replication based on the monitoring program, and then triggers the DNS mapping adjustment. From the perspective of the application system, it is necessary to release the connection (with the old primary node) that has been established before the switch, and establish a new connection (with the new primary node). The application system must complete the establishment of the new connection before it can provide services normally to the outside world. Therefore, this method will seriously affect the external service availability and transaction processing performance of the application system during the switch (rebuilding the connection);

[0013] 2. Most of the existing database middleware that supports MySQL group replication is deployed independently, which increases resource investment and the complexity of the network link from the application system to the database.

[0014] Therefore, those skilled in the art provide a node identification method and device for MySQL group replication to solve the problems raised in the above background technology. Summary of the invention

[0015] The object of the present invention is to provide a node identification method and device for MySQL group replication to solve the problems raised in the above background technology.

[0016] To achieve the above object, the present invention provides the following technical solution: a node identification device for MySQL group replication, comprising:

[0017] Node connection management module: used to connect all nodes;

[0018] Node status register: used to store all node status data;

[0019] Node status identification module: used to identify the node status, determine the abnormality of the master node and automatically replace it with a new master node.

[0020] As a further solution of the present invention: a node identification method for MySQL group replication includes the following steps:

[0021] S1: related logic processing in the service startup initialization phase;

[0022] S2: Interaction and collaboration between the node connection management module and the transaction processing flow;

[0023] S3: When an abnormal failure occurs in a MySQL group replication node, the node connection management module interacts with the transaction processing flow.

[0024] As a further solution of the present invention: S1 includes the following sub-steps:

[0025] A1: In the system configuration file, configure the connection information and initial status information for all nodes of the MySQL group replication;

[0026] A2: Read the node information list from the configuration file and perform initialization actions for each node;

[0027] A3: Create a connection pool corresponding to the node based on the node's connection information, and establish a connection with the MySQL group replication node. Each node corresponds to an independent connection pool.

[0028] A4: Create a status register corresponding to the node according to the initial status data of the node, and set the initial status data into the register;

[0029] A5: Create a state recognition program corresponding to the node based on the node configuration information. The state recognition program will be triggered and executed at a fixed time.

[0030] As a further solution of the present invention: the connection information in A1 includes: MySQL connection string, user name, password, connection pool size, etc.;

[0031] The initial state information includes: initial master and slave node identities.

[0032] As a further solution of the present invention: the logic executed in A5 includes:

[0033] A51: Get a connection from the connection pool corresponding to the node;

[0034] A52: Access the MySQL group replication node through connection and obtain node status data;

[0035] A53: Set the status data to the status register corresponding to the node.

[0036] As a further solution of the present invention: S3 includes the following sub-steps:

[0037] B1: Get the status register list of all nodes in the MySQL group replication cluster;

[0038] B2: Search for the MySQL group replication master node from the list. If no valid master node can be found, an exception is thrown and the transaction fails.

[0039] B3: If a valid master node is found, obtain the connection from the connection pool corresponding to the node.

[0040] As a further solution of the present invention: S3 includes the following sub-steps:

[0041] C1: When the primary node (node ​​1) of the MySQL group replication cluster fails;

[0042] C2: MySQL group replication automatically elects a new master node (node ​​2), and the identity of node 2 is automatically converted from a slave node to a master node;

[0043] C3: The status recognition program of node 1 finds that node 1 is faulty and updates the status register of node 1;

[0044] C4: The status recognition program of node 2 finds that node 2 has automatically converted to the master node and updates the status register of node 2;

[0045] C5: The online transaction processing system obtains the status register list of all nodes;

[0046] C6: Identify the master node (node ​​2) from the list;

[0047] C7: Get a connection from the connection pool of the primary node (node ​​2).

[0048] Compared with the prior art, the beneficial effects of the present invention are as follows: the present invention, through the node connection management module, the node data register, and the node status identification module, can meet the requirements of quasi-real-time detection, discovery, and adaptation of automatic master-slave switching of MGR nodes without changing the network link between the application system and the data, avoid connection reconstruction actions, reduce the complexity of application system configuration, and improve the service availability of the application system. BRIEF DESCRIPTION OF THE DRAWINGS

[0049] Figure 1 It is a flowchart of S1 in a node identification method for MySQL group replication;

[0050] Figure 2 It is a flowchart of S2 in a node identification method for MySQL group replication;

[0051] Figure 3 A schematic diagram of the S3 process in a node identification method for MySQL group replication. DETAILED DESCRIPTION

[0052] See also Figures 1 to 3 In an embodiment of the present invention, a node identification device for MySQL group replication includes:

[0053] Node connection management module: used to connect all nodes;

[0054] Node status register: used to store all node status data;

[0055] Node status identification module: used to identify the node status, determine the abnormality of the master node and automatically replace it with a new master node.

[0056] Furthermore, a node identification method for MySQL group replication includes the following steps:

[0057] S1: related logic processing in the service startup initialization phase;

[0058] S2: Interaction and collaboration between the node connection management module and the transaction processing flow;

[0059] S3: When an abnormal failure occurs in a MySQL group replication node, the node connection management module interacts with the transaction processing flow.

[0060] Furthermore, S1 includes the following sub-steps:

[0061] A1: In the system configuration file, configure the connection information and initial status information for all nodes of the MySQL group replication;

[0062] A2: Read the node information list from the configuration file and perform initialization actions for each node;

[0063] A3: Create a connection pool corresponding to the node based on the node's connection information, and establish a connection with the MySQL group replication node. Each node corresponds to an independent connection pool.

[0064] A4: Create a status register corresponding to the node according to the initial status data of the node, and set the initial status data into the register;

[0065]

[0066]

[0067] A5: Create a state recognition program corresponding to the node based on the node configuration information. The state recognition program will be triggered and executed at a fixed time.

[0068] Furthermore, the connection information in A1 includes: MySQL connection string, user name, password, connection pool size, etc.;

[0069] The initial state information includes: initial master and slave node identities.

[0070] Furthermore, the logic executed in A5 includes:

[0071] A51: Get a connection from the connection pool corresponding to the node;

[0072] A52: Access the MySQL group replication node through connection and obtain node status data;

[0073] A53: Set the status data to the status register corresponding to the node.

[0074] Furthermore, S3 includes the following sub-steps:

[0075] B1: Get the status register list of all nodes in the MySQL group replication cluster;

[0076] B2: Search for the MySQL group replication master node from the list. If no valid master node can be found, an exception is thrown and the transaction fails.

[0077] B3: If a valid master node is found, obtain the connection from the connection pool corresponding to the node.

[0078] Furthermore, S3 includes the following sub-steps:

[0079] C1: When the primary node (node ​​1) of the MySQL group replication cluster fails;

[0080] C2: MySQL group replication automatically elects a new master node (node ​​2), and the identity of node 2 is automatically converted from a slave node to a master node;

[0081] C3: The status recognition program of node 1 finds that node 1 is faulty and updates the status register of node 1;

[0082] C4: The status recognition program of node 2 finds that node 2 has automatically converted to the master node and updates the status register of node 2;

[0083] C5: The online transaction processing system obtains the status register list of all nodes;

[0084] C6: Identify the master node (node ​​2) from the list;

[0085] C7: Get a connection from the connection pool of the primary node (node ​​2).

[0086] To sum up: the present invention uses a node connection management module, a node data register, and a node status identification module to meet the requirements of quasi-real-time detection, discovery, and adaptation of automatic master-slave switching of MGR nodes without changing the network link between the application system and the data, thereby avoiding connection reconstruction actions, reducing the complexity of application system configuration, and improving the service availability of the application system.

[0087] What is described above is only a preferred specific implementation manner of the present invention, but the protection scope of the present invention is not limited thereto. Any technician familiar with the technical field can make equivalent replacements or changes according to the technical scheme and inventive concept of the present invention within the technical scope disclosed by the present invention, which should be covered by the protection scope of the present invention.

Claims

1. A node identification method for MySQL group replication, characterized in that: The following steps are involved: S1: related logic processing in the service startup initialization phase; S2: Interaction and collaboration between the node connection management module and the transaction processing flow; wherein the node connection management module is used to connect all nodes; S3: When an abnormal failure occurs in a MySQL group replication node, the node connection management module interacts with the transaction processing flow; S1 includes the following sub-steps: A1: In the system configuration file, configure the connection information and initial status information for all nodes of the MySQL group replication; A2: Read the node information list from the configuration file and perform initialization actions for each node; A3: Create a connection pool corresponding to the node based on the node's connection information, and establish a connection with the MySQL group replication node. Each node corresponds to an independent connection pool. A4: creating a status register corresponding to the node according to the initial status information of the node, and setting the initial status information into the status register; wherein the status register is used to store status data of all nodes; A5: Create a state recognition program corresponding to the node according to the configuration information of the node, and the state recognition program is triggered and executed regularly; The logic executed in A5 includes: A51: Get a connection from the connection pool corresponding to the node; A52: Access the MySQL group replication node through connection and obtain node status data; A53: Set the node status data to the status register corresponding to the node; S3 includes the following sub-steps: B1: Get the status register list of all nodes in the MySQL group replication cluster; B2: Search for the MySQL group replication master node from the list. If no valid master node can be found, an exception is thrown and the transaction fails. B3: If a valid master node is found, obtain a connection from the connection pool corresponding to the node; S3 also includes the following sub-steps: C1: When the primary node of the MySQL group replication cluster, that is, node 1, fails; C2: The MySQL group replication cluster automatically elects a new master node, Node 2, and the identity of Node 2 is automatically converted from a slave node to a master node; C3: The status recognition program of node 1 finds that node 1 is faulty and updates the status register of node 1; C4: The status recognition program of node 2 finds that node 2 has automatically converted to the master node and updates the status register of node 2; C5: The online transaction processing system obtains the status register list of all nodes; C6: Identify the master node from the list; C7: Get a connection from the connection pool of the master node.

2. A node identification method for MySQL group replication according to claim 1, characterized in that: The connection information in A1 includes: MySQL connection string, user name, password and connection pool size; the initial state information includes: initial master and slave node identities.

3. A node identification device for MySQL group replication, used to implement the node identification method for MySQL group replication as claimed in claim 1, characterized in that: include: Node connection management module: used to connect all nodes; Node status register: used to store all node status data; Node status identification module: used to identify the node status, determine the abnormality of the master node and automatically replace it with a new master node.

Images