High-availability management method of MYSQL cluster

Abstract

The invention discloses a high-availability management method of an MYSQL cluster, and relates to the technical field of database management. Comprising the following steps: step 1, managing the availability of an MYSQL slave cluster: forwarding a request to service ip ports of a plurality of slaves through an ip port bound with a unified application service gateway device by using the routing forwarding of the unified application service gateway device agent software application, configuring heartbeat check of the port of the unified application service gateway device, and according to the heartbeat check, managing the availability of the MYSQL slave cluster; 2, managing availability of the MYSQL host: establishing a standby machine of the MYSQL host, creating a new virtual ip, pointing the virtual ip to the MYSQL host and the standby machine, and when a software application is connected with the host through the virtual ip to perform write-in operation, switching to the standby machine when the MYSQL host is abnormal, and transferring the write-in operation to the MYSQL standby machine; and step 3, performing host data synchronization of the MYSQL slave cluster: when the MYSQL host is abnormal, synchronously pointing the slave to the standby, enabling the standby to synchronize the newest host log, ensuring the newest data, and synchronizing the host data by the slave cluster.

Description

Technical Field

[0001] The present invention discloses a high-availability management method for a MYSQL cluster, which relates to the technical field of database management. Background Art

[0002] With the continuous development of software systems, the number of user accesses and concurrent requests has increased significantly, especially the access volume on the Web and mobile terminals has increased substantially. This change has put forward higher requirements for database systems, especially for the MYSQL database. A single MYSQL database can no longer meet the high-concurrency read and write requirements. Therefore, building a MYSQL cluster has become a common solution to improve the system access ability.

[0003] The MYSQL cluster usually adopts a master-slave architecture and a read-write separation strategy, where the master database is responsible for write operations and the slave databases handle read requests. This architecture is particularly suitable for scenarios with few writes and many reads. By setting up one master database and multiple slave databases, the concurrent processing ability of the system can be significantly improved. However, in actual applications, if a certain slave database fails, the availability of the entire MYSQL cluster may be affected, thus affecting the user experience. Summary of the Invention

[0004] In view of the problems of the prior art, the present invention provides a high-availability management method for a MYSQL cluster, providing a new solution for MYSQL database failover and recovery, and load balancing, aiming to achieve high-availability management using MYSQL as the database.

[0005] The specific solution proposed by the present invention is as follows:

[0006] The present invention provides a high-availability management method for a MYSQL cluster, including:

[0007] Step 1: Manage the availability of the MYSQL slave cluster: Utilize the routing and forwarding of the unified application service gateway device to proxy software applications, forward requests through the IP ports bound to the unified application service gateway device to the service IP ports of multiple slave machines, configure the heartbeat check of the unified application service gateway device ports, and according to the heartbeat check, when the slave machine service is unavailable, automatically cut off the routing of the unavailable slave machine service.

[0008] Step 2: Manage the availability of the MYSQL master: Establish a standby machine for the MYSQL master, create a virtual IP, point the virtual IP to the MYSQL master and the standby machine. When the software application connects to the master through the virtual IP for write operations, when the MYSQL master is abnormal, switch to the standby machine and transfer the write operation to the MYSQL standby machine.

[0009] Step 3: Synchronize the host data of the MySQL slave cluster: When the MySQL host has an exception, point the slave synchronization to the standby machine, and let the standby machine synchronize the latest host log to ensure the latest data for the slave cluster to synchronize the host data.

[0010] Further, in step 1 of the high-availability management method for a MySQL cluster, the A101030S device is used as the unified application service gateway device. By using the routing and forwarding of the A10 1030S device proxy software application, the request is forwarded to the service IP ports of multiple slave machines through the IP port bound by the A101030S device. Configure the heartbeat check for the A10 1030S device port. According to the heartbeat check, when the slave service is unavailable, automatically cut off the route of the unavailable slave service.

[0011] Further, in step 2 of the high-availability management method for a MySQL cluster, the keepalive plugin is used to create a virtual IP, and the virtual IP is pointed to the MySQL host and the standby machine. When the software application performs a write operation by connecting to the main database through the virtual IP, when the MySQL host has an exception, the keepalive plugin manages the connection to the host and switches to the standby machine.

[0012] Further, in step 3 of the high-availability management method for a MySQL cluster, mha is used to manage the host data synchronization of the MySQL slave cluster. When the MySQL host has an exception, mha manages the connection to the host, points the slave synchronization to the standby machine, and lets the standby machine synchronize the latest host log to ensure the latest data for the slave cluster to synchronize the host data.

[0013] The present invention also provides a high-availability management device for a MySQL cluster, including a slave availability management module, a host availability management module, and a synchronization management module.

[0014] The slave availability management module manages the availability of the MySQL slave cluster: By using the routing and forwarding of the unified application service gateway device proxy software application, the request is forwarded to the service IP ports of multiple slave machines through the IP port bound by the unified application service gateway device. Configure the heartbeat check for the unified application service gateway device port. According to the heartbeat check, when the slave service is unavailable, automatically cut off the route of the unavailable slave service.

[0015] The host availability management module manages the availability of the MySQL host: Establish a standby machine for the MySQL host, create a virtual IP, and point the virtual IP to the MySQL host and the standby machine. When the software application performs a write operation by connecting to the host through the virtual IP, when the MySQL host has an exception, switch to the standby machine and transfer the write operation to the MySQL standby machine.

[0016] The synchronization management module performs host data synchronization for the MySQL slave cluster: when the MySQL host has an exception, the slave synchronization is directed to the standby machine, allowing the standby machine to synchronize the latest host logs to ensure the latest data for the slave cluster to synchronize host data.

[0017] Furthermore, the slave availability management module of the high-availability management device for a MySQL cluster uses the A101030S device as a unified application service gateway device. By utilizing the routing and forwarding of the A10 1030S device proxy software application, the request is forwarded to the service IP ports of multiple slave machines through the IP ports bound to the A10 1030S device. The heartbeat check of the A10 1030S device ports is configured. According to the heartbeat check, when the slave service is unavailable, the routing of the unavailable slave service is automatically cut off.

[0018] Furthermore, the host availability management module of the high-availability management device for a MySQL cluster uses the keepalive plugin to create a virtual IP, which points the virtual IP to the MySQL host and the standby machine. When the software application performs a write operation by connecting to the main database through the virtual IP, when the MySQL host has an exception, the keepalive plugin manages the host connection and switches to the standby machine.

[0019] Furthermore, the synchronization management module of the high-availability management device for a MySQL cluster uses mha to manage the host data synchronization of the MySQL slave cluster. When the MySQL host has an exception, mha manages the host connection, directs the slave synchronization to the standby machine, allows the standby machine to synchronize the latest host logs to ensure the latest data for the slave cluster to synchronize host data.

[0020] The advantages of the present invention are:

[0021] The present invention respectively manages the failover and recovery of the slave machines and the host machines in the MySQL database cluster, and maintains the data synchronization of the slave machines to the host machine. Using MySQL as the database of the software system has high availability. Description of the Drawings

[0022] Figure 1 It is a schematic diagram of the heartbeat check process.

[0023] Figure 2 It is a schematic diagram of the host exception switching process.

[0024] Figure 3 It is a schematic diagram of the IP switching process for the slave machine to synchronize host data.

[0025] Figure 4 It is a schematic diagram of the mba management architecture. Detailed Embodiments

[0026] The present invention will be further described below in conjunction with the accompanying drawings and specific embodiments, so that those skilled in the art can better understand the present invention and be able to implement it, but the exemplified embodiments do not limit the present invention.

[0027] Embodiment 1

[0028] The present invention provides a high-availability management method for a MYSQL cluster, including:

[0029] Step 1: Manage the availability of the MYSQL slave cluster: Utilize the routing and forwarding of the unified application service gateway device proxy software application to forward requests through the IP ports bound to the unified application service gateway device to the service IP ports of multiple slaves. Configure the heartbeat check for the ports of the unified application service gateway device. According to the heartbeat check, when the slave service is unavailable, automatically cut off the routing of the unavailable slave service.

[0030] Among them, an A10 1030S device can be used as the unified application service gateway device. Utilize the routing and forwarding of the A10 1030S device proxy software application to forward requests through the IP ports bound to the A10 1030S device to the service IP ports of multiple slaves. Configure the heartbeat check for the ports of the A10 1030S device. According to the heartbeat check, when the slave service is unavailable, automatically cut off the routing of the unavailable slave service to ensure the overall high-availability state of the slave cluster.

[0031] Step 2: Manage the availability of the MYSQL master: Establish a standby machine for the MYSQL master, create a virtual IP, and point the virtual IP to the MYSQL master and the standby machine. When the software application writes data through the virtual IP to connect to the master, when the MYSQL master is abnormal, switch to the standby machine and transfer the write operation to the MYSQL standby machine.

[0032] The keepalive plugin can be used to create a virtual IP and point the virtual IP to the MYSQL master and the standby machine. When the software application writes data through the virtual IP to connect to the main database, when the MYSQL master is abnormal, manage the connection to the master through the keepalive plugin and switch to the standby machine.

[0033] Step 3: Synchronize the master data of the MYSQL slave cluster: When the MYSQL master is abnormal, point the slave synchronization to the standby machine, and let the standby machine synchronize the latest master log to ensure the latest data for the slave cluster to synchronize the master data.

[0034] The host data synchronization of the MySQL slave cluster can be managed by MHA. When the MySQL host has an exception, MHA manages the connection to the host, redirects the slave synchronization to the standby machine, and enables the standby machine to synchronize the latest host logs to ensure the latest data for the slave cluster to synchronize the host data. When the MySQL host has an exception, MBA drifts the virtual IP to the standby machine, deletes the configuration of the original host in MBA, and changes the configured host of the MySQL slave to the standby machine.

[0035] Embodiment 2

[0036] The present invention also provides a high-availability management device for a MySQL cluster, including a slave availability management module, a host availability management module, and a synchronization management module.

[0037] The slave availability management module manages the availability of the MySQL slave cluster: by using the routing and forwarding of the unified application service gateway device proxy software application, the request is forwarded to the service IP ports of multiple slave machines through the IP ports bound to the unified application service gateway device, and the heartbeat check of the unified application service gateway device port is configured. According to the heartbeat check, when the slave service is unavailable, the routing of the unavailable slave service is automatically cut off.

[0038] The host availability management module manages the availability of the MySQL host: a standby machine for the MySQL host is established, a virtual IP is newly created, and the virtual IP is pointed to the MySQL host and the standby machine. When the software application writes through the virtual IP and the MySQL host has an exception, it switches to the standby machine and transfers the write operation to the MySQL standby machine.

[0039] The synchronization management module performs the host data synchronization of the MySQL slave cluster: when the MySQL host has an exception, the slave synchronization is pointed to the standby machine, and the standby machine is allowed to synchronize the latest host logs to ensure the latest data for the slave cluster to synchronize the host data.

[0040] Regarding the information interaction and execution process among the above-mentioned modules in the device, since they are based on the same concept as the method embodiment of the present invention, the specific content can be referred to the description in the method embodiment of the present invention and will not be elaborated here.

[0041] Similarly, the device of the present invention respectively manages the failover and recovery of the slave and the host of the MySQL database cluster, and maintains the data synchronization from the slave to the host. Using MySQL as the database of the software system has high availability.

[0042] It should be noted that not all steps and modules in the above-mentioned processes and device structures are necessary, and some steps or modules can be ignored according to actual needs. The execution order of each step is not fixed and can be adjusted as needed. The system structures described in the above embodiments can be physical structures or logical structures, that is, some modules may be implemented by the same physical entity, or some modules may be implemented by multiple physical entities respectively, or some components in multiple independent devices may be jointly implemented.

[0043] The above-mentioned embodiments are only preferred embodiments cited to fully illustrate the present invention, and the protection scope of the present invention is not limited thereto. Equivalent substitutions or transformations made by those skilled in the art on the basis of the present invention are within the protection scope of the present invention. The protection scope of the present invention shall be subject to the claims.

Claims

1. A high-availability management method for a MySQL cluster, characterized in that Including: Step 1: Manage the availability of the MySQL slave cluster: Utilize the routing and forwarding of the unified application service gateway device proxy software application to forward requests through the IP ports bound to the unified application service gateway device to the service IP ports of multiple slaves. Configure the heartbeat check for the ports of the unified application service gateway device. According to the heartbeat check, when the slave service is unavailable, automatically cut off the route of the unavailable slave service. Step 2: Manage the availability of the MySQL master: Establish a standby for the MySQL master, create a virtual IP, and point the virtual IP to the MySQL master and the standby. When the software application writes through the virtual IP and the MySQL master is abnormal, switch to the standby and transfer the write operation to the MySQL standby. Step 3: Synchronize the master data of the MySQL slave cluster: When the MySQL master is abnormal, point the slave synchronization to the standby, and let the standby synchronize the latest master logs to ensure the latest data for the slave cluster to synchronize the master data.

2. A high-availability management method for a MySQL cluster according to claim 1, characterized in that in step 1, the A101030S device is used as the unified application service gateway device, and the routing and forwarding of the software application is proxied by the A10 1030S device to forward requests through the IP ports bound to the A10 1030S device to the service IP ports of multiple slaves. Configure the heartbeat check for the ports of the A10 1030S device. According to the heartbeat check, when the slave service is unavailable, automatically cut off the route of the unavailable slave service.

3. A high-availability management method for a MYSQL cluster according to claim 1, characterized in that In step 2, the keepalive plugin is used to create a virtual IP and point the virtual IP to the MySQL master and the standby. When the software application writes through the virtual IP and the MySQL master is abnormal, the keepalive plugin manages the connection to the master and switches to the standby.

4. A high-availability management method for a MYSQL cluster according to claim 1, characterized in that In step 3, mha is used to manage the master data synchronization of the MySQL slave cluster. When the MySQL master is abnormal, mha manages the connection to the master, points the slave synchronization to the standby, and lets the standby synchronize the latest master logs to ensure the latest data for the slave cluster to synchronize the master data.

5. A high-availability management device for a MySQL cluster, characterized in that Including a slave availability management module, a master availability management module, and a synchronization management module. The slave availability management module manages the availability of the MySQL slave cluster: Utilize the routing and forwarding of the unified application service gateway device proxy software application to forward requests through the IP ports bound to the unified application service gateway device to the service IP ports of multiple slaves. Configure the heartbeat check for the ports of the unified application service gateway device. According to the heartbeat check, when the slave service is unavailable, automatically cut off the route of the unavailable slave service. The master availability management module manages the availability of the MySQL master: Establish a standby for the MySQL master, create a virtual IP, and point the virtual IP to the MySQL master and the standby. When the software application writes through the virtual IP and the MySQL master is abnormal, switch to the standby and transfer the write operation to the MySQL standby. The synchronization management module performs host data synchronization for the MySQL slave cluster: when the MySQL host has an exception, the slave synchronization is pointed to the standby machine, allowing the standby machine to synchronize the latest host logs to ensure the latest data for the slave cluster to synchronize host data.

6. The highly available management device for a MYSQL cluster according to claim 5, characterized in that the slave The availability management module uses the A10 1030S device as the unified application service gateway device. By utilizing the routing and forwarding of the A10 1030S device proxy software application, the requests are forwarded to the service IP ports of multiple slave machines through the IP ports bound to the A10 1030S device. The heartbeat check of the A10 1030S device ports is configured. According to the heartbeat check, when the slave service is unavailable, the routing of the unavailable slave service is automatically cut off.

7. The highly available management device for a MYSQL cluster according to claim 5, characterized in that the host The availability management module uses the keepalive plugin to create a virtual IP and points the virtual IP to the MySQL host and the standby machine. When the software application performs a write operation by connecting to the main database through the virtual IP, when the MySQL host has an exception, the keepalive plugin manages the connection to the host and switches to the standby machine.

8. The high-availability management device for a MYSQL cluster according to claim 5, characterized in that The synchronization management module uses mha to manage the host data synchronization of the MySQL slave cluster. When the MySQL host has an exception, mha manages the connection to the host, points the slave synchronization to the standby machine, allows the standby machine to synchronize the latest host logs, ensures the latest data, and is used for the slave cluster to synchronize host data.

Images