Operation and Maintenance Method, Device, Equipment and Storage Medium of MYSQL Database

Abstract

The present application provides an operation and maintenance method, device, equipment, and storage medium for a MySQL database. The method includes obtaining a database instance and database instance information, and generating an instance topology structure for the database instance according to the database instance information. The instance topology structure includes a primary database instance and multiple slave database instances. The primary database instance is used to process write requests and some read requests, and the slave database instances are used to process read requests. When the primary database instance fails, the slave database instances are converted into the primary database instance. A database request is received. When the database request is a write request, the write request is routed to the primary database instance. When the database request is a read request, the read request is routed to the slave database instance. This method constructs an instance topology structure to set up primary and slave database instances. In case of a failure, the slave database instance can be switched to the primary database instance, making the system highly available. And read-write separation is achieved.

Description

Technical Field

[0001] This application relates to the technical field of database operation and maintenance, and particularly to an operation and maintenance method, device, equipment, and storage medium for a MYSQL database. Background Art

[0002] With the rapid development of information technology, Internet technology has been widely applied in various fields. As a leading open-source database, MYSQL is gradually becoming the mainstream database solution in the Internet field with its characteristics of low cost, easy deployment, and high performance.

[0003] As an important part of the application system, the database directly determines the overall performance and availability of the system. Usually, multiple links such as system design, development, and deployment are strongly related to the database. Whether the database has performance bottlenecks and high availability plays a crucial role in the operation of the entire system. The current main implementation methods in the field of database operation and maintenance are as follows: having read-write separation but not high availability; having high availability but not read-write separation, that is, currently, the database cannot achieve both read-write separation and high availability at the same time. This method will have the following problems: (1) The lack of high availability will lead to a decrease in system availability, and in case of a failure, only manual intervention is possible, which depends on the maintenance experience of database maintenance personnel; (2) Without read-write separation, the main database has high resource overhead, which will lead to a decrease in system performance, stability, and even failures; (3) Manual intervention requires cross-team coordination and communication, resulting in an increase in communication costs and an extension of the fault resolution time.

[0004] Application Content

[0005] In view of this, embodiments of this application provide an operation and maintenance method, device, equipment, and storage medium for a MYSQL database.

[0006] In a first aspect, embodiments of this application provide an operation and maintenance method for a MYSQL database, and the method includes:

[0007] Obtain a database instance and database instance information, and generate an instance topology structure for the database instance according to the database instance information; wherein the instance topology structure includes a main database instance and multiple slave database instances; the main database instance is used to process write requests and some read requests, and the slave database instances are used to process read requests; when the main database instance fails, the slave database instances are converted into the main database instance;

[0008] Receive a database request, and when the database request is a write request, route the write request to the main database instance;

[0009] When the database request is a read request, route the read request to the slave database instance.

[0010] In a second aspect, an operation and maintenance device for a MYSQL database provided by an embodiment of the present application includes:

[0011] An acquisition module, configured to acquire a database instance and database instance information, and generate an instance topology structure for the database instance according to the database instance information; wherein the instance topology structure includes a primary database instance and multiple slave database instances; the primary database instance is used to process write requests and some read requests, and the slave database instances are used to process read requests; when the primary database instance fails, the slave database instances are converted into the primary database instance;

[0012] A request receiving module, configured to receive database requests;

[0013] A request routing module, configured to route the write request to the primary database instance when the database request is a write request; and route the read request to the slave database instances when the database request is a read request.

[0014] In a third aspect, an embodiment of the present application provides a terminal device, including: a memory; one or more processors coupled to the memory; and one or more application programs, wherein the one or more application programs are stored in the memory and configured to be executed by the one or more processors, and the one or more application programs are configured to execute the operation and maintenance method for the MYSQL database provided in the first aspect above.

[0015] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, in which program code is stored, and the program code can be called by a processor to execute the operation and maintenance method for the MYSQL database provided in the first aspect above.

[0016] The operation and maintenance method, device, equipment, and storage medium for the MYSQL database provided by the embodiments of the present application acquire a database instance and database instance information, and generate an instance topology structure for the database instance according to the database instance information; wherein the instance topology structure includes a primary database instance and multiple slave database instances; the primary database instance is used to process write requests and some read requests, and the slave database instances are used to process read requests; when the primary database instance fails, the slave database instances are converted into the primary database instance; receive database requests, route the write request to the primary database instance when the database request is a write request; and route the read request to the slave database instances when the database request is a read request.

[0017] The above operation and maintenance method of the MYSQL database constructs an instance topology structure for database instances according to their mutual relationships (i.e., topological relationships), and sets a primary database instance and a secondary database instance. When a failure occurs in the primary database instance, the secondary database instance can be switched to the primary database instance without manual intervention. On the one hand, the fault handling speed is improved, and on the other hand, the availability of the system is improved, making the system highly available. And the read requests and write requests of the database are processed separately, thus realizing read-write separation. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, the following will briefly introduce the drawings required for use in the description of the embodiments or the prior art. Obviously, the drawings in the following description are only the embodiments of the present application. For those of ordinary skill in the art, other drawings can be obtained according to the provided drawings without creative efforts.

[0019] Figure 1 Schematic diagram of the application scenario of the operation and maintenance method of the MYSQL database provided by the embodiment of the present application;

[0020] Figure 2 Schematic diagram of the structure of a terminal device or a server provided by an embodiment of the present application;

[0021] Figure 3 Schematic diagram of the flow of the operation and maintenance method of the MYSQL database provided by an embodiment of the present application;

[0022] Figure 4 Schematic diagram of the process of a database request accessing a database provided by an embodiment of the present application;

[0023] Figure 5 Structure diagram of the operation and maintenance device of the MYSQL database provided by an embodiment of the present application;

[0024] Figure 6 Schematic diagram of the structure of a terminal device provided by an embodiment of the present application;

[0025] Figure 7 Schematic diagram of the structure of a computer-readable storage medium provided by an embodiment of the present application. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0026] The following will clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only a part of the embodiments of the present application, rather than all of the embodiments. Based on the embodiments of the present application, all other embodiments obtained by those of ordinary skill in the art without creative efforts belong to the scope of protection of the present application.

[0027] To describe the present application in more detail, a method, apparatus, terminal device, and computer storage medium for the operation and maintenance of a MYSQL database provided by the present application will be specifically described below with reference to the accompanying drawings.

[0028] Please refer to Figure 1 , Figure 1 which shows a schematic diagram of the application scenario of the method for the operation and maintenance of the MYSQL database provided by the embodiments of the present application. This application scenario includes the terminal device 100 provided by the embodiments of the present application. The terminal device 100 can be various electronic devices with a display screen (such as the structural diagrams of 102, 104, 106, and 108), including but not limited to smart phones and computer devices. Among them, the computer device can be at least one of devices such as a desktop computer, a portable computer, a laptop computer, and a tablet computer. A user operates the terminal device 100 to issue an operation and maintenance operation instruction for the MYSQL database. The terminal device 100 executes the method for the operation and maintenance of the MYSQL database of the present application. For the specific process, please refer to the embodiments of the method for the operation and maintenance of the MYSQL database.

[0029] Secondly, the terminal device 100 can generally refer to one of multiple terminal devices. Only the terminal device 100 is used as an example in this embodiment. Those skilled in the art can understand that the number of the above-mentioned terminal devices can be more or less. For example, the above-mentioned terminal devices can be only a few, or dozens or hundreds of the above-mentioned terminal devices, or more. The embodiments of the present application do not limit the number and type of the terminal devices. The terminal device 100 can be used to execute a method for the operation and maintenance of a MYSQL database provided in the embodiments of the present application.

[0030] In an optional implementation manner, in addition to the terminal device 100 provided by the embodiments of the present application, this application scenario may further include a server, and a network is provided between the server and the terminal device. The network is used as a medium for providing a communication link between the terminal device and the server. The network can include various connection types, such as wired, wireless communication links, or fiber optic cables, etc.

[0031] It should be understood that the numbers of the terminal device, the network, and the server are only illustrative. According to the implementation requirements, there can be any number of terminal devices, networks, and servers. For example, the server can be a server cluster composed of multiple servers, etc. Among them, the terminal device interacts with the server through the network to receive or send messages, etc. The server can be a server that provides various services. The server can be used to execute the steps of a method for the operation and maintenance of a MYSQL database provided in the embodiments of the present application. In addition, when the terminal device executes a method for the operation and maintenance of a MYSQL database provided in the embodiments of the present application, some steps can be executed on the terminal device, and some steps can be executed on the server, which is not limited here.

[0032] Further, if Figure 2 As shown, the terminal device or server includes an application layer, a load balancer, a database proxy layer, a data storage layer, and a router. The router includes a routing management module, a monitoring module, a topology management module, a statistics module, and an alarm module. The application layer is used for terminals to initiate database requests (i.e., SQL requests) through apps, web browsers, etc. The load balancer is used to receive database requests and forward them to the database proxy layer. The database proxy layer routes them to the corresponding database instance through the router, and reads and writes data to the data storage layer (i.e., the database) through the database instance. After completion, the data is returned in sequence, and the terminal can finally see the desired data. This is a complete database request.

[0033] The routing management module is used to enter or obtain the database instances and database instance information that need to be managed. The database instance information includes IP, Port, monitoring account, application account, number of connections (i.e., Connection), maximum number of connections (i.e., Max_Connection), routing rules (i.e., query_rules), monitoring frequency per unit time, monitoring switch (monitor_enable) switch status (e.g., true state), and other information.

[0034] The monitoring module checks the connectivity of the database instance using ping (a command in the MySQL protocol used to check the database instance's liveness and idle connections) and the number of connections (connections) according to the configured monitoring user (which can be determined by the monitoring account). The frequency of these checks can be set using the parameters mysql-monitor_connect_interval and mysql-monitor_ping_interval. Ping and connection logs are stored in the mysql_server_ping_log and mysql_server_connect_log tables. If the ping and connection status of the database instance are normal, the monitoring module connects to the database instance, reads the read_only value of the instance, determines whether the instance is assigned to a read or write group, and then assigns the read / write group. If the read_only value of the instance changes, the monitoring module automatically changes the read / write group upon detecting the change, without requiring manual intervention. When database requests from the application layer pass through the proxy layer and enter the router, the router reads the configured routing rules and performs read / write split routing, routing read requests to instances in the read group and write requests to instances in the write group. After completing the read-write separation, the monitoring module forms an instance topology structure based on the topological relationship of the database instance, and writes the instance topology structure into the topology management module, which manages the instance topology structure.

[0035] The topology management module is used to discover the instance topology structure. The instance topology structure can support automatic discovery and manual discovery modes. Automatic discovery relies on the discovery of the monitoring module. Manual discovery can be done by inputting the IP and Port information of the database instance through the Discover button on the web management interface or the client tool command line to discover the topology relationship to form the instance topology structure. It supports the functions of instance topology display, instance topology change, and instance topology abnormality repair. Among them, the instance topology structure includes the master database instance and the slave database instance. The topology management module is also used to scan and monitor the instance topology structure at the unit monitoring frequency of the monitoring module. When a fault is found in the master database instance, such as the master database instance downtime or the master database instance process hangs, the topology management module performs fault identification through a safe and reliable judgment mechanism. (1) First, connect to the master database instance through the server. If the master database instance is found to be disconnected, it cannot be determined that the master database instance is faulty at this time; (2) Connect to the slave database instance through the server and check the replication status of the slave database instance (show slave status) to confirm whether the replication thread is normal. Repeat multiple retries (e.g., 3-5) until the primary database instance is confirmed to be faulty. The topology management module then performs a failover, automatically switches over, breaks the master-slave replication relationship, and reselects a more optimal slave database instance as the primary database instance. After the automatic switchover is complete, the routing management module is notified to take the faulty primary database instance offline. The faulty primary database instance in the MYSQL server (i.e., mysql_server) is automatically set to the OFFLINE_HARD state, preventing new connections from being routed to it. Existing connections are also forcibly disconnected.

[0036] The security module is used to intercept and block abnormal SQL and abnormal IP addresses, such as slow SQL reaching the threshold of 5s and malicious IP requests, after the database requests (i.e. SQL requests) issued by the application layer enter the routing module through the router, and automatically add them to the blacklist to ensure the performance and availability of the database. At the same time, it can optimize the SQL quality in the R&D stage.

[0037] The statistics module is used to perform statistical analysis on SQL request interception, such as the number of abnormal SQL, IP number, SQL execution time, number of read routes, number of write routes, etc. It can analyze and generate daily / weekly / monthly reports.

[0038] The alarm module is used to implement customized alarms in the monitoring module, topology management module, security module, and statistics module, such as database instance ping, connection exception alarms, topology change alarms, abnormal SQL interception alarms, daily / weekly / monthly statistical report alarms, etc.

[0039] Based on this, an operation and maintenance method for a MYSQL database is provided in an embodiment of the present application. Please refer to Figure 3 , Figure 3 which shows a schematic flowchart of an operation and maintenance method for a MYSQL database provided in an embodiment of the present application. Taking the method applied to Figure 1 the terminal device as an example, it includes the following steps:

[0040] Step S110: Obtain a database instance and database instance information, and generate an instance topology structure based on the database instance information.

[0041] The instance topology structure includes a primary database instance and multiple slave database instances; the primary database instance is used to process write requests and some read requests, and the slave database instances are used to process read requests; when the primary database instance fails, the slave database instances are converted into the primary database instance.

[0042] Specifically, a database is actually a series of physical files on a disk. A database instance is a data structure located in physical memory, consisting of threads and a memory pool. It is the database instance that is truly used to operate on the database files (i.e., a series of physical files). The shared memory pool can be accessed by all processes. When a user wants to access or store data in the database, it must be achieved through the database instance, rather than directly reading the files on the hard disk. In fact, the normal process of reading a database into memory is that a group of background processes in the database instance read the data files from the disk into the memory of the database instance, and then after operating on the data in the memory, write it to the database through a group of background processes in the memory of the database instance. In MYSQL, generally, one database instance operates on one or more databases; in a cluster scenario, multiple database instances operate on one or more databases.

[0043] First, the number of database instances can be multiple, and each database instance has its corresponding information (i.e., data volume instance information). The database instance information includes but is not limited to IP, Port (port), monitoring account, application account, connection number (i.e., Connection), maximum connection number (i.e., Max_Connection), routing rules (i.e., query_rules), monitoring frequency per unit time, monitoring switch (monitor_enable) switch status (such as the ture status), etc.

[0044] Second, when there are multiple database instances, there may be mutual relationships (i.e., topological relationships) among the database instances. The database instances can be used as nodes, and the mutual relationships among the database instances can be used to construct a topology graph, thereby forming an instance topology structure.

[0045] In an alternative embodiment, the topological relationship between two database instances can be determined based on information such as the IP and Port (i.e., port) of the database instance.

[0046] In addition, there are multiple database instances in the instance topology structure. One of these database instances can be set as the master database instance, and the others as slave database instances. The master database instance is mainly used to process write requests and some read requests; for example, the master database instance can be used to process all write requests and 10% of the read requests. The slave database instances are used to process read requests.

[0047] Furthermore, when the master database instance fails, a slave database instance is converted into the master database instance. Optionally, any one of the slave database instances can be selected to be converted into the master database instance.

[0048] In a preferred embodiment, the conversion of a slave database instance into the master database instance includes: selecting the slave database instance with the optimal performance to be converted into the master database instance.

[0049] Specifically, when it is necessary to select a slave database instance that can be converted into the master database instance among multiple slave database instances, it can be selected according to the performance of the slave database instance, that is, the slave database instance with excellent performance can be selected to be converted into the master database instance, so as to ensure the performance and service life of the master database instance.

[0050] Step S120: Receive a database request.

[0051] Step S130: When the database request is a write request, route the write request to the master database instance. When the database request is a read request, route the read request to the slave database instance.

[0052] Among them, the database request is also called an SQL request. The request generally includes a read request and a write request. The read request is used to read data from the database, while the write request is used to write data into the database.

[0053] In this embodiment, after receiving the database request, it is judged. When the database request is a read request, the read request is sent to the slave database instance, so as to write the data to be written into the corresponding database. When the database request is a write request, the write request is sent to the master database instance, and the data to be obtained is never read from the corresponding database.

[0054] The above operation and maintenance method of the MYSQL database constructs an instance topology structure based on the mutual relationship (i.e., the topology relationship) of database instances, and sets a primary database instance and a secondary database instance. When a failure occurs in the primary database instance, the secondary database instance can be switched to the primary database instance without manual intervention. On the one hand, the fault handling speed is improved, and on the other hand, the availability of the system is improved, making the system highly available. And the read requests and write requests of the database are processed separately, thus realizing read-write separation.

[0055] In one embodiment, before performing step S110 of generating an instance topology structure according to database instance information, it further includes: performing connectivity detection and read-write grouping on each database instance in turn according to the database instance information to form a read-group database instance and a write-group database instance; wherein the read-group database instance can only process read requests, and the write-group database instance can process read requests or write requests.

[0056] Among them, connectivity refers to whether the database instance can be normally connected to the database, and whether the database and the database instance are both alive, so that data can be normally read and written.

[0057] Read-write grouping means determining whether a database instance belongs to the read group or the write group for later read-write separation. Among them, the read-group database instance can only process read requests, and the write-group database instance can process read requests or write requests.

[0058] It should be noted that the primary database instance can only be a write-group database instance; the secondary database instance is usually a read-group database instance, and can also be a write-group database instance.

[0059] The read-group database instance and the write-group database instance can be converted into each other.

[0060] In this embodiment, performing connectivity detection on the database instance can exclude abnormal database instances so as to process database requests. Performing read-write grouping on the database instance facilitates read-write separation when processing database requests.

[0061] Furthermore, a specific implementation method for performing connectivity detection and read-write grouping on the database instance is given, which is described as follows:

[0062] In one embodiment, the database instance information includes a monitoring account and the number of connections; performing connectivity detection on each database instance in turn according to the database instance information includes: performing connectivity detection on the database instance according to the monitoring account and the number of connections by means of heartbeat detection.

[0063] Specifically, the connectivity of the database instance can be detected for the monitored user (i.e., determined through the monitoring account) by means of ping and the number of connections (i.e., Connection). The detection frequency can be set according to the parameters mysql - monitor_connect_interval and mysql - monitor_ping_interval. The logs of ping and the number of connections can be stored in the mysql_server_ping_log and mysql_server_connect_log data tables.

[0064] Among them, ping refers to a command in the MYSQL protocol, which is a heartbeat detection method used to detect whether the database instance is alive and whether the connection is idle.

[0065] By adopting this method, the connectivity of the database instance can be determined quickly and conveniently, thus excluding abnormal database instances.

[0066] In one embodiment, the database instance information includes a read - write flag; each database instance is grouped into a read group database instance and a write group database instance according to the database instance information, including: for each database instance, judging the read - write flag of the database instance; when the read - write flag is a read - only flag, the database instance is classified into the read group database instance; otherwise, the database instance is classified into the write group database instance.

[0067] Specifically, the read - write flag can be read_only in the database instance, and read_only can take two values. When read_only is 0, it means the database instance is readable and writable, belonging to the write group. When read_only is 1, it means the database instance can only be read and cannot be written, belonging to the read group.

[0068] It should be noted that the value of read_only can be switched (for example, it can be switched from 0 to 1 or from 1 to 0). If the read_only of the database instance changes, the monitoring will automatically change the read - write group after reading the change without manual intervention.

[0069] Furthermore, an implementation method for judging the failure of the primary database instance is provided, and this implementation method will be described in detail below.

[0070] In one embodiment, the failure of the primary database instance is determined by the following method: when the primary database instance is out of contact, check the master-slave replication status of the slave database instances in the instance topology; determine whether the replication thread is normal according to the master-slave replication status; when the replication thread is abnormal, repeatedly execute the steps of checking the master-slave replication status and determining whether the replication thread is normal until the preset number of repetitions is reached; count the number of times the replication thread is abnormal, and when the number is greater than the preset threshold, determine that the primary database instance has failed.

[0071] Specifically, first connect to the primary database instance. When it is found that the primary data instance is out of contact, it cannot be directly determined that the primary database instance has failed. At this time, it is necessary to connect to the slave database instances in the instance topology and check the master-slave replication status from the slave database instances to confirm whether the replication thread is normal. When the replication thread is not normal, perform multiple retries (for example, it can be repeated 3-5 times). When the number of times the replication line is abnormal is greater than the preset threshold, it can be determined that the primary database instance has failed.

[0072] Among them, the master-slave replication status can be viewed through the show slave status command.

[0073] The preset threshold is a pre-set value, which can be set according to actual needs.

[0074] Adopting this method can quickly and accurately determine whether the primary database instance has failed.

[0075] In one embodiment, after receiving a database request, it further includes: judging the database request, and intercepting the abnormal request when the database request is an abnormal request.

[0076] Specifically, after receiving a database request, the database request can be judged to see if it is an abnormal request. When it is an abnormal request, the abnormal request can be intercepted. Specifically, it can be judged whether the database request is abnormal through the IP of the database request. For example, when the IP of the database request is abnormal and the number of IP abnormalities exceeds the preset number (for example, 10 times or 50 times, etc.) within a period of time (such as one hour or one day, etc.), it can be determined that the database request is an abnormal request.

[0077] The way to intercept abnormal requests can be to add them to the blacklist, which can guarantee the performance and availability of the database, and at the same time can optimize the quality of database requests during the R & D stage.

[0078] In one embodiment, the method further includes counting the intercepted requests for abnormal requests, such as the number of abnormal requests, the number of IPs, the execution time of database requests, the number of read routing, the number of write routing, etc., and can be analyzed and generate daily / weekly / monthly reports.

[0079] In one embodiment, the method further includes: when an exception occurs, sending an alarm message. For example, an alarm for abnormal database instance connectivity, an alarm for the failure of the primary database instance, an alarm for abnormal request interception, and generating an alarm statistics report on a daily / weekly / monthly basis, etc.

[0080] To understand the solution of this application, a detailed workflow of a database is given. Please refer to Figure 4 , and in combination with Figure 2 , the application layer (the terminal through APP, WEB, etc.) initiates an SQL (i.e., a database request), the database request is received by the load balancer, the load balancer forwards the database request to the database proxy layer. After the database proxy layer receives the database request, it enters the router. The router detects whether the database request is an abnormal request. If it is an abnormal request, it will be intercepted, that is, the interception is implemented by the SQL interceptor in the figure. The intercepted database request will not be able to enter the data storage layer and will be returned to the database proxy layer by the router and then returned layer by layer to the application layer; if the database request has not been intercepted, the router automatically matches the database request to the read group or write group through the routing management module, and then routes the database request to the data storage layer. The data storage layer completes reading / writing the data and returns it layer by layer to the application layer, and the application layer can then see the desired data. In addition, when the database request enters the router, if the primary database instance fails at this time (i.e., the primary database fails), the monitoring module in the router discovers the primary database failure. After the topology management module receives the primary database failure message, it automatically performs a database master-slave switch and correctly routes the database request to the switched primary database instance, completing the automatic fault recovery and returning the data to the application layer step by step.

[0081] It should be understood that although the steps in the flowchart of Figure 3 are shown in sequence according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless there is a clear description in this article, the execution of these steps has no strict order limit, and these steps can be executed in other orders. Moreover Figure 3 at least a part of the steps in

[0082] may include multiple sub-steps or multiple stages. These sub-steps or stages are not necessarily executed at the same time, but can be executed at different times. The execution order of these sub-steps or stages is not necessarily sequential either, but can be executed alternately or in turn with at least a part of other steps or sub-steps or stages of other steps.

[0082] In the above embodiments disclosed in this application, a method for operating and maintaining a MYSQL database is described in detail. For the above method disclosed in this application, it can be implemented by various forms of devices. Therefore, this application also discloses an operating and maintaining device for a MYSQL database corresponding to the above method, and specific embodiments are given below for detailed description.

[0083] Please refer to Figure 5 , an operation and maintenance device for a MYSQL database disclosed in an embodiment of the present application, mainly including:

[0084] An acquisition module 510, configured to acquire a database instance and database instance information, and generate an instance topology structure according to the database instance information; wherein the instance topology structure includes a primary database instance and multiple slave database instances; the primary database instance is used to process write requests and some read requests, and the slave database instances are used to process read requests; when the primary database instance fails, the slave database instances are converted into the primary database instance.

[0085] A request receiving module 520, configured to receive database requests.

[0086] A request routing module 530, configured to route a write request to the primary database instance when the database request is a write request; and route a read request to a slave database instance when the database request is a read request.

[0087] In one embodiment, the device further includes:

[0088] A detection and grouping module, configured to sequentially perform connectivity detection and read / write grouping on each database instance according to the database instance information, so as to form a read group database instance and a write group database instance; wherein the read group database instance can only process read requests, and the write group database instance can process read requests or write requests.

[0089] In one embodiment, the detection and grouping module is configured to perform connectivity detection on the database instance according to the monitoring account and the number of connections by means of a heartbeat detection method.

[0090] In one embodiment, the database instance information includes a read / write identifier; the detection and grouping module is configured to, for each database instance, determine the read / write identifier of the database instance; when the read / write identifier is a read-only identifier, classify the database instance as a read group database instance; otherwise, classify the database instance as a write group database instance.

[0091] In one embodiment, the device further includes:

[0092] A replication status viewing module, configured to view the master-slave replication status of the slave database instances in the instance topology structure when the primary database instance is out of contact.

[0093] A replication thread anomaly determination module, configured to determine whether the replication thread is normal according to the master-slave replication status;

[0094] A re-execution module, configured to, when the replication thread is abnormal, repeatedly execute the steps of viewing the master-slave replication status and determining whether the replication thread is normal until a preset number of repetitions is reached;

[0095] A fault determination module is used to count the number of times of abnormal replication threads. When the number is greater than a preset threshold, it is determined that the main database instance has a fault.

[0096] In one embodiment, the device further includes:

[0097] An exception interception module is used to judge database requests. When a database request is an abnormal request, the abnormal request is intercepted.

[0098] In one embodiment, the device further includes:

[0099] A database instance conversion module is used to select the one with the optimal performance from the slave database instances and convert it into the main data instance.

[0100] For the specific limitations of the MYSQL database operation and maintenance device, reference can be made to the limitations on the method in the above text, which will not be elaborated here. Each module in the above device can be implemented in whole or in part by software, hardware, and their combination. The above modules can be embedded in the processor in the terminal device in hardware form or independent of the processor, or stored in the memory in the terminal device in software form, so as to facilitate the processor to call and execute the operations corresponding to the above modules.

[0101] Please refer to Figure 6 , Figure 6 which shows the structural block diagram of a terminal device provided in an embodiment of the present application. The terminal device 60 may be a computer device. The terminal device 60 in the present application may include one or more of the following components: a processor 62, a memory 64, and one or more application programs. One or more application programs may be stored in the memory 64 and configured to be executed by one or more processors 62. One or more application programs are configured to execute the methods described in the embodiments of the MYSQL database operation and maintenance method.

[0102] The processor 62 may include one or more processing cores. The processor 62 connects various parts within the entire terminal device 60 through various interfaces and circuits. By running or executing instructions, programs, code sets, or instruction sets stored in the memory 64, and by invoking data stored in the memory 64, it performs various functions of the terminal device 60 and processes data. Optionally, the processor 62 may be implemented in at least one hardware form of digital signal processing (DSP), field-programmable gate array (FPGA), or programmable logic array (PLA). The processor 62 may integrate a combination of one or several of a central processing unit (CPU), a graphics processing unit (GPU) for reporting and verifying buried point data, and a modem, etc. Among them, the CPU mainly processes the operating system, user interface, application programs, etc.; the GPU is responsible for rendering and drawing the display content; the modem is used to process wireless communication. It can be understood that the above-mentioned modem may not be integrated into the processor 62 and may be implemented separately through a communication chip.

[0103] The memory 64 may include random access memory (RAM) and may also include read-only memory. The memory 64 can be used to store instructions, programs, code, code sets, or instruction sets. The memory 64 may include a program storage area and a data storage area. Among them, the program storage area may store instructions for implementing the operating system, instructions for implementing at least one function (such as touch function, sound playback function, image playback function, etc.), instructions for implementing each of the following method embodiments, etc. The data storage area may also store data created during the use of the terminal device 60.

[0104] Those skilled in the art can understand that Figure 6 the structure shown in

[0105] is only a block diagram of a part of the structure related to the solution of this application, and does not constitute a limitation on the terminal device to which the solution of this application is applied. The specific terminal device may include more or fewer components than those shown in the figure, or combine some components, or have a different component layout.

[0106] Please refer to Figure 7, which shows a structural block diagram of a computer-readable storage medium provided by an embodiment of the present application. Program code is stored in the computer-readable storage medium 70, and the program code can be called by a processor to execute the method described in the above-described embodiment of the operation and maintenance method of the MYSQL database.

[0107] The computer-readable storage medium 70 can be an electronic memory such as a flash memory, an EEPROM (electrically erasable programmable read-only memory), an EPROM, a hard disk, or a ROM. Optionally, the computer-readable storage medium 70 includes a non-transitory computer-readable storage medium. The computer-readable storage medium 70 has a storage space for the program code 72 that executes any of the method steps in the above method. These program codes can be read out from or written into one or more computer program products. The program code 72 can be compressed in a suitable form, for example.

[0108] In the description of this specification, the description with reference to terms such as "one embodiment", "some embodiments", "example", "specific example", or "some examples" means that the specific features, structures, materials, or characteristics described in connection with the embodiment or example are included in at least one embodiment or example of the present application. In this specification, the schematic representations of the above terms do not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials, or characteristics described can be combined in a suitable manner in any one or more embodiments or examples. In addition, without conflict, those skilled in the art can combine and combine the different embodiments or examples described in this specification and the features of different embodiments or examples.

[0109] The above description of the disclosed embodiments enables those skilled in the art to implement or use the present application. Various modifications to these embodiments will be obvious to those skilled in the art, and the general principles defined herein can be implemented in other embodiments without departing from the spirit or scope of the present application. Therefore, the present application will not be limited to the embodiments shown herein, but will be accorded the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. An operation and maintenance method for a MYSQL database, characterized in that, The method includes: Obtain a database instance and database instance information, and generate an instance topology structure for the database instance according to the database instance information; wherein the instance topology structure includes a primary database instance and multiple slave database instances; the primary database instance is used to process write requests and some read requests, and the slave database instances are used to process read requests; when the primary database instance fails, the slave database instances are converted into the primary database instance; Receive a database request; When the database request is a write request, route the write request to the primary database instance; when the database request is a read request, route the read request to the slave database instances; Before generating the instance topology structure according to the database instance information, it further includes: Perform connectivity detection and read / write grouping on each database instance in turn according to the database instance information to form a read-group database instance and a write-group database instance; wherein the read-group database instance can only process read requests, and the write-group database instance can process read requests or write requests; The database instance information includes a monitoring account and the number of connections; the performing connectivity detection on each database instance in turn according to the database instance information includes: Perform connectivity detection on the database instance according to the monitoring account and the number of connections through a heartbeat detection method; The database instance information includes a read / write identifier; the performing read / write grouping on each database instance according to the database instance information to form a read-group database instance and a write-group database instance includes: For each database instance, judge the read / write identifier of the database instance; When the read / write identifier is a read-only identifier, classify the database instance into the read-group database instance; otherwise, classify the database instance into the write-group database instance; The failure of the primary database instance is determined by the following method: When the primary database instance is out of contact, view the master-slave replication status of the slave database instances in the instance topology structure; Determine whether the replication thread is normal according to the master-slave replication status; When the replication thread is abnormal, repeatedly execute the steps of viewing the master-slave replication status and determining whether the replication thread is normal until a preset number of repetitions is reached; Count the number of times the replication thread is abnormal, and when the number is greater than a preset threshold, determine that the primary database instance has failed.

2. The method according to claim 1, wherein After receiving the database request, it further includes: Judge the database request, and intercept the abnormal request when the database request is an abnormal request.

3. The method according to claim 1, wherein The conversion of the slave database instance into the primary database instance includes: Select one of the slave database instances with the optimal performance and convert it into the primary database instance.

4. An operation and maintenance device for a MYSQL database, characterized in that, The device includes: An acquisition module, configured to acquire a database instance and database instance information, and generate an instance topology structure for the database instance according to the database instance information; wherein the instance topology structure includes a primary database instance and multiple slave database instances; the primary database instance is configured to process write requests and partial read requests, and the slave database instances are configured to process read requests; when the primary database instance fails, the slave database instances are converted into the primary database instance; A request receiving module, configured to receive database requests; A request routing module, configured to route the write request to the primary database instance when the database request is a write request; and route the read request to the slave database instances when the database request is a read request; A detection and grouping module, configured to sequentially perform connectivity detection and read / write grouping on each database instance according to the database instance information, so as to form a read group database instance and a write group database instance; wherein the read group database instances can only process read requests, and the write group database instances can process read requests or write requests; Specifically, the detection and grouping module is configured to perform connectivity detection on the database instance according to a monitoring account and the number of connections by using a heartbeat detection method; The database instance information includes a read / write identifier; the detection and grouping module is further configured to, for each database instance, determine the read / write identifier of the database instance; when the read / write identifier is a read-only identifier, classify the database instance into the read group database instance; otherwise, classify the database instance into the write group database instance; A replication status viewing module, configured to view the master-slave replication status of the slave database instances in the instance topology structure when the primary database instance is out of contact; A replication thread anomaly determination module, configured to determine whether the replication thread is normal according to the master-slave replication status; A re-execution module, configured to, when the replication thread is abnormal, repeatedly execute the steps of viewing the master-slave replication status and determining whether the replication thread is normal until a preset number of repetitions is reached; A fault determination module, configured to count the number of times of replication thread anomalies, and determine that the primary database instance has failed when the number is greater than a preset threshold; 5. A terminal device, characterized in that, Including: A memory; One or more processors, coupled to the memory; One or more applications, wherein the one or more applications are stored in the memory and configured to be executed by the one or more processors, and the one or more applications are configured to execute the method according to any one of claims 1-3; 6. A computer-readable storage medium, characterized in that, Program code is stored in the computer-readable storage medium, and the program code can be called by the processor to execute the method according to any one of claims 1-3.

Images