A MySQL database batch switching method based on high availability rehearsal

Abstract

The application relates to a MySQL database batch switching method based on high-availability rehearsal, and steps include: receiving a database switching application and entering a switching process; triggering a database switching work order, the switching work order containing all steps of database switching, and the atomic task of the switching work order being switching function logic; each atomic task triggers an execution tool of ansbile, the execution tool sends a switching script to a specified switching server each time triggering, executes specific instructions on the target server, and completes the specified logic of database switching; after the atomic task in the switching fails to execute, entering a processing process, triggering the next atomic task again after removing faults according to error information; and when the database batch switching executes successfully, checking whether the cluster state after switching and the business connection are normal. Compared with the prior art, the application does not rely on a third-party switching platform to realize database batch switching, and ensures that data is not lost in the switching process.

Description

Technical Field

[0001] This invention relates to the field of database switching technology, and in particular to a method for batch switching of MySQL databases based on high availability drills. Background Technology

[0002] As enterprise application systems increase, the scale of databases has reached thousands of clusters. In particular, the financial industry has placed higher demands on database stability. MySQL, as an open-source relational database, is widely used in small and medium-sized systems due to its advantages such as simple setup, low cost of use, and high stability. MySQL databases have master and slave databases. The master database is accessed externally. When the master database is damaged and cannot be repaired in a short time, in order to quickly restore business, a master-slave switch is performed on the MySQL database, and the new master database provides services. This invention will refer to the MySQL database as a database for short.

[0003] Currently, the best approach to database failover is for DBAs to manually perform the failover to ensure no business disruption, and then notify the relevant services to integrate. To guarantee a data-free and rapid failover, strict adherence to pre-defined instructions is crucial, leading to heavy reliance on manual operation and low efficiency. As database clusters grow to thousands of systems, database failover functionality must be platform-based and capable of batch operations. High-availability drills for batch database failovers should be conducted during routine database maintenance to ensure rapid failover and business recovery should a real database issue occur.

[0004] Current master-slave database switching solutions suffer from the following problems: For example, the master-slave database switching method disclosed in Chinese patent application CN202010887335.5 cannot be simultaneously supported by most current database switching platforms, which fail to provide visualization of the switching process, the ability to continue triggering after a fault, and batch triggering without mutual interference. Another example is the high-availability master-slave database switching method disclosed in Chinese patent application CN202310347352.3, which relies on third-party software MHA to execute the `masterha_master_switch` command. The implementation details of the database switching are built into the third-party software, managed by a third-party platform. This makes the switching logic dependent on the platform's management, creating a black box effect. Misjudgments can lead to incorrect database switching, and the technology lacks independent control. Furthermore, different companies have different switching scenarios, resulting in high costs for customized development. Summary of the Invention

[0005] The purpose of this invention is to overcome the shortcomings of the existing technology and provide a MySQL database batch switching solution based on high availability drills that can ensure no data loss.

[0006] The objective of this invention can be achieved through the following technical solutions: A method for batch switching of MySQL databases based on high availability drills, comprising the following steps: Receive database switch request and initiate the switch process; Trigger a database switch work order. The switch work order contains all the steps of database switching. The atomic task of the switch work order is the switch function logic. Each atomic task triggers the Ansible execution tool. Each time the execution tool is triggered, it sends the switching script to the specified switching server, executes the specific instructions on the target server, and completes the specified logic for database switching. If an atomic task in the process of switching fails, the process will proceed to the next atomic task after troubleshooting based on the error message. After the batch database switch is successfully executed, check whether the cluster status and business connections are normal after the switch.

[0007] As a preferred technical solution, the atomic tasks adopt a drag-and-drop atomic task orchestration, which allows atomic tasks to be arranged at any position in the entire processing flow and to be reused.

[0008] As a preferred technical solution, each registered server is managed by the Ansible tool, which has the highest execution privileges. Each atomic task will trigger a server instruction execution. The Ansible tool selects a relatively idle server from the execution machine to execute the task. The execution will transmit the script to the target server and trigger the operation steps specified in the script.

[0009] As a preferred technical solution, the specific steps of the database switching are as follows: Perform database status verification; The virtual IP address of the specified main database server is taken offline by command. The service connects to the database through DNS, and DNS resolves the virtual IP address of the database cluster. The virtual IP address and the main database are on different network cards of the same server. By setting the main database status to read-only through a global variable, all users are unable to connect. At the same time, the configuration file is modified so that the read-only setting is invalidated after the database is restarted. Verify the master-slave status from the database; The primary database enables full log flushing, recording and saving every connection and disconnection, as well as session-level statements. Full log flushing is disabled after the switchover is complete. Enable full log refresh on the database to record and save the connection and disconnection of the primary and standby machines and session-level statements before the switchover. Switch from the primary database to the secondary database, clear the original primary-secondary relationship, and record the position parameters of the secondary database before the switch. When switching from a master database to a slave database, the original master-slave relationship is cleared. The focus is on establishing a new master-slave relationship on the new slave database and checking the verification items for establishing the master-slave relationship. Perform a master-slave relationship check; Modify the configuration of the new primary database, including: setting the status of the new primary database to read-only, enabling scheduled tasks, and disabling full log refresh in the configuration file parameters and global variable parameters; The new primary database has been launched with VIP access for business connections. The original database will stop all processes not on the whitelist; After all processes of the original primary database are stopped and there is no business connection, the configuration of the original primary database is modified. Perform the master-slave relationship check again. If the check is successful, the switchover is complete.

[0010] As a preferred technical solution, the method of verifying the master-slave status in the database includes: querying the status of global variables to ensure that the data in the slave database and the master database are synchronized normally; verifying that case sensitivity of the slave database is disabled; verifying that the read-only status of the slave database is enabled so that all users cannot write to it; verifying whether the parameters in the configuration file are the same as the data in memory; and verifying that the update operation of the master database is enabled for the slave database records.

[0011] As a preferred technical solution, the method of setting the main database status to read-only via global variables is a temporary setting. Modifying the configuration file makes the read-only setting of the main database invalid after the database restarts.

[0012] As a preferred technical solution, the verification items for establishing the master-slave relationship include: Slave_IO_Running and Slave_SQL_Running, to ensure normal data synchronization between the new master and slave databases; and Last_IO_Error, to ensure that the master-slave relationship is established normally without any errors.

[0013] As a preferred technical solution, the master-slave relationship verification includes: verifying whether there is a delay between master and slave; determining whether the file read by the slave database is the same as the binlog file that the slave database has replayed in the master database, i.e., whether the slave database has completed the data replay; comparing and determining whether the log offset of the binlog file read by the slave database is the same as the offset of the binlog file that the slave database has replayed in the master database, i.e., whether the slave database has replayed in the specified offset.

[0014] As a preferred technical solution, if there are still connection processes outside the whitelist after the original master database has stopped all processes outside the whitelist three times, then the database process is restarted.

[0015] As a preferred technical solution, when the primary database is unable to provide services, the database failover procedure is as follows: The primary database virtual machine VIP of the specified server is taken offline via operating system commands. Before switching over, enable full log refresh in the database to record and save the connections and disconnections of the primary and standby servers, as well as session-level statements before the switchover. Switch from the primary database to the secondary database, clear the original primary-secondary relationship, and record the position parameters of the secondary database before the switch. Modify the configuration of the new primary database, change the configuration file parameters and global variable parameters to read-only, enable scheduled tasks and disable full log refresh; The new master database is online, VIPs are refreshed, and business connections are established.

[0016] Compared with the prior art, the present invention has the following beneficial effects: 1) This invention proposes a database batch switchover drill scheme that ensures no data loss. The switchover process is triggered by the database operation and maintenance management platform, and each operation step is visualized by the process processing platform. Each atomic task of the process processing platform triggers the Ansible script execution tool, and the execution script is transmitted to the designated server each time. The script execution tool ensures the network stability and efficiency of file execution.

[0017] 2) The switching process designed in this invention utilizes the database parameter function to ensure that no data is lost during the switching process, thus ensuring the efficiency of the switching. Attached Figure Description

[0018] Figure 1 This is a flowchart illustrating a method for batch switching of MySQL databases based on high availability drills according to the present invention. Figure 2 This is a flowchart illustrating the normal switching process of the MySQL database in this invention; Figure 3 This is a flowchart of the MySQL master database failover process of this invention. Detailed Implementation

[0019] The present invention will now be described in detail with reference to the accompanying drawings and specific embodiments. These embodiments are based on the technical solution of the present invention and provide detailed implementation methods and specific operating procedures. However, the scope of protection of the present invention is not limited to the following embodiments.

[0020] Example 1 This invention's technical solution triggers a process processing platform through a database operation and maintenance management platform. The process processing platform generates an independent and complete switchover work order. Each atomic task in the switchover work order triggers the Ansible execution tool. Each time the tool is triggered, it transmits the switchover script to the designated server to execute the specified switchover operation. This technical solution achieves a breakpoint-retryable, visualized switchover process through the call between the process processing platform and the Ansible tool platform. By designing strict database switchover steps and logic to ensure no data loss, it ultimately achieves a high-availability MySQL database batch switchover method for production environment deployment.

[0021] I. Overall process of the solution is as follows Figure 1 As shown: S1. The system's application manager initiates a database emergency switchover drill application and generates an application number.

[0022] S2. Log in to the database operation and maintenance management platform, trigger the database switch button, fill in the change order number, whitelist, select the switch mode (switchOver, failOver), and other parameters to trigger the switch process.

[0023] S3. Upon successful verification, a database switchover work order is triggered on the process management platform. The switchover work order contains all steps involved in the database switchover. The atomic tasks of the work order are the switchover functional logic co-written by business personnel and DBAs.

[0024] S4. Utilizing the visualization and recurring triggering capabilities of the workflow processing platform, each atomic task is triggered by the Ansible triggering tool, which sends the switching script to the designated switching server. On the target server, specific instructions are executed to complete the specified logic for database switching.

[0025] S5. If an atomic task fails during switching, it enters the manual processing flow. Based on the error message from the process processing platform, the fault is resolved, and manual processing is triggered again to enter the next atomic task.

[0026] S6. Determine whether the work order has been completed. If the execution is successful, manually check whether the cluster status and business connection are normal after the switch.

[0027] The core of this invention is the design of the database switching process visualization and breakpoint retry handling module and the database switching steps. The implementation of these two modules will be described in detail below.

[0028] II. The specific design of the database switching tool is as follows: The database switching process visualization and breakpoint retry handling modules provide a high success rate and stable operating environment for batch database switching.

[0029] 2.1. Processing Each switchover task consists of independent atomic tasks. These atomic tasks are similar to object-oriented coding methods; well-designed atomic tasks are reusable and independent of each other. For example, database validation can use the same atomic tasks for different databases; only the target server IP needs to be transmitted, and the status of the primary and backup servers is queried to adapt to different pre-switch validations. Each atomic task can be arranged in a drag-and-drop manner, meaning it can be placed anywhere in the entire processing flow and can be reused.

[0030] 2.2. Using the Ansible tool to implement database switching logic and achieve a stable execution environment. Currently, every registered server in the enterprise is managed by the Ansible tool and has the highest execution privileges. Each atomic task triggers a server instruction execution. Ansible selects a relatively idle server from the execution machine to execute the task, transmitting the script to the target server and triggering the operation steps specified in the script. Enterprises typically differentiate between network zones, each with its own firewall. The Ansible platform can access all servers without requiring the creation of functional accounts, reducing the need for environment configuration during each switch.

[0031] III. The specific design of the normal database switchover procedure is as follows: The database switchover process ensures no data loss during the switchover, guaranteeing its efficiency. As a key technology applicable in the financial industry, the operational process is as follows: Figure 2 As shown.

[0032] S3.1. Database Status Verification The database status verification function is designed to detect problems before the switchover. If the pre-switch verification fails, the database does not meet the requirements for this switchover and requires manual intervention. After repair, the switchover can be implemented using this technical solution. The verification includes: whether the password for the replication user matches the password for the specified user in the database, etc.

[0033] S3.2. Primary database virtual machine VIP offline. The VIP of a specified server is taken offline via operating system commands. Services connect to the database via DNS, which resolves the VIP of the database cluster. The VIP and the primary database reside on different network interface cards (NICs) on the same server. By adding a NIC to the primary database, both IP addresses can connect to the same server. This technical solution allows for switching between services when connections are interrupted.

[0034] S3.3. Set the primary database status to read-only. After the VIP goes offline, no new business links are connected to the cluster. It is necessary to ensure that existing transactions can be used normally, with business connections to a specific database and a specific ordinary user. This is achieved by setting the global variables `read_only` and `super_read_only` to read-only, preventing all users from connecting. Simultaneously, the configuration file is modified to ensure that the temporary settings are invalidated after a database restart.

[0035] S3.4. Verify the master-slave status from the database. Checking the global variables `Slave_IO_Running` and `Slave_SQL_Running` to ensure they are "YES" guarantees that the data synchronization between the slave and master databases is normal. `lower_case_table_name`: Disables case sensitivity for the slave database to prevent errors after a switch if table names are case-insensitive. Verifies `read_only` and `super_read_only` to ensure the slave database is read-only, preventing all users from writing to it. Verifies that the parameters in the configuration file match the data in memory to prevent the configuration file from being reread upon database restart. Enables the `log_slave_update` parameter, allowing the slave database to record update operations from the master database. These updates are logged to the binary log and transmitted to the slave database, ensuring data consistency before a switch.

[0036] S3.5. Enable full log flushing for the primary database. Full log refresh: `set global general_log=1`. This sets up a global dynamic log variable, enabling full logging. The database will record every connection and disconnection, as well as session-level statements, and save them to a specified file. The downside is that it records a large amount of log data; this parameter must be turned off after the switchover is complete.

[0037] S3.6. Enable full logging from the database Before the switchover, full log flushing also needs to be enabled on the database. Record the connection and disconnection of the primary and standby servers, as well as session-level statements, before the switchover and save them to a specified file.

[0038] S3.7. Switch from database to primary database The `stop slave` and `reset slave all` commands clear the original master-slave relationship and record the slave database's position before the switchover, including important parameters such as `Auto_Position`, `Master_Log_File`, and `Master_Log_Pos`.

[0039] S3.8. Switching from Master to Slave Database `stop slave` and `reset slave all` clear the existing master-slave relationship and then establish a new master-slave relationship on the new slave database. Verification items for establishing the master-slave relationship include: `Slave_IO_Running` and `Slave_SQL_Running` to ensure normal data synchronization between the new master and slave databases; and `Last_IO_Error` to ensure the master-slave relationship is established successfully without errors.

[0040] S3.9. Master-Slave Relationship Test Seconds_Behind_Master checks for potential delays between master and slave systems. The values ​​of Master_log_file and relay_master_log_file are identical to determine if the file read from the slave database is the same as the binlog file replayed from the slave to the master database. If the files are the same, it means the slave has completed the data replay. Read_master_log_pos and exec_master_log_pos represent the log offsets read from the master database's binlog file and the offsets replayed from the slave database to the master database's binlog file. Equal values ​​indicate that the slave has replayed to the specified offset.

[0041] S3.10. Modify the configuration of the new primary database. Modify the configuration file parameters and global variable parameters `read_only` and `super_read_only`. Enable scheduled tasks: `event_scheduler=1`, disable full logging: `general_log=0`.

[0042] S3.11. New main database VIP launched. The new owner can already provide services; VIP members can connect to the business.

[0043] S3.12. Kill session on the original primary database. All processes not on the whitelist will be stopped. If there are still connection processes not on the whitelist after three executions, the database process will be restarted.

[0044] S3.13. Modification of the original primary database configuration Once all processes have stopped, there will be no more business connections, and the original master database configuration can be modified.

[0045] S3.14. Perform the master-slave relationship check again. If the verification is successful, the switchover is complete.

[0046] IV. The specific design of the database failover steps is as follows: The database failover process occurs when the primary database is unavailable and cannot be restored quickly. In this case, a standby database is used to provide service, followed by manual data recovery. The procedure is as follows: Figure 3 As shown.

[0047] S4.1. Primary database virtual machine VIP offline. The VIP of a specified server is taken offline via operating system commands.

[0048] S4.2. Enable full logging from the database Before the switchover, full log flushing also needs to be enabled on the database. Record the connection and disconnection of the primary and standby servers, as well as session-level statements, before the switchover and save them to a specified file.

[0049] S4.3. Switch from database to primary database The `stop slave` and `reset slave all` commands clear the original master-slave relationship and record the slave database's position before the switchover, including important parameters such as `Auto_Position`, `Master_Log_File`, and `Master_Log_Pos`.

[0050] S4.4. Modify the configuration of the new primary database Modify the configuration file parameters and global variable parameters `read_only` and `super_read_only`. Enable scheduled tasks: `event_scheduler=1`, disable full logging: `general_log=0`.

[0051] S4.5. New main database VIP launched. The new master database is already available for service; once a VIP account is launched, business connections can be established.

[0052] This technical solution triggers a process processing platform through an operations and maintenance management platform. The process processing platform generates a complete switchover process, where each atomic task triggers an Ansible execution tool. Each time the tool is triggered, it transmits the switchover script to a designated server to execute the specified operations. This is a feasible switchover solution that utilizes designed switchover logic and allows for manual intervention after errors, enabling continued execution. It addresses the following issues compared to existing database switchover methods: 1. Low efficiency and high error rate of manual switchover. 2. No data loss during switchover. 3. Completely self-developed and technically controllable. 4. No reliance on third-party switchover platforms, resulting in controllable product development and iteration costs. This patent possesses good innovation and practicality, and does not overlap with existing patents, making it worthy of patent protection.

[0053] The preferred embodiments of the present invention have been described in detail above. It should be understood that those skilled in the art can make numerous modifications and variations based on the concept of the present invention without creative effort. Therefore, all technical solutions that can be obtained by those skilled in the art based on the concept of the present invention through logical analysis, reasoning, or limited experimentation on the basis of existing technology should be within the scope of protection defined by the claims.

Claims

1. A method for batch switching of MySQL databases based on high availability drills, characterized in that, The process processing platform is triggered by the database operation and maintenance management platform, generating an independent and complete switchover work order. Batch switching of MySQL databases, featuring breakpoint retry capabilities and visualized switchover processes, is achieved through the invocation of the process processing platform and the Ansible tool platform. Specific steps include: Receive database switch request and initiate the switch process; A database switchover work order is triggered. This work order contains all the steps involved in the database switchover, and the atomic task of the work order is the switchover function logic. The specific steps of the database switchover are as follows: Perform database status verification; The command takes the specified virtual IP address of the main database server offline. Set the main database status to read-only using a global variable; Verify the master-slave status from the database; The primary database is enabled with full log flushing (general_log=1), which records and saves every connection and disconnection, as well as session-level statements. Full log flushing is disabled after the switchover is complete. Enable full log refresh on the database (general_log=1) to record and save the connection and disconnection of the primary and standby machines and session-level statements before the switchover. Switch from the primary database to the secondary database, clear the original primary-secondary relationship, and record the position parameters of the secondary database before the switch. When switching from a master database to a slave database, the original master-slave relationship is cleared. The focus is on establishing a new master-slave relationship on the new slave database and checking the verification items for establishing the master-slave relationship. Perform a master-slave relationship check; Modify the configuration of the new primary database, including: setting the status of the new primary database to read-only, enabling scheduled tasks, and disabling full log refresh in the configuration file parameters and global variable parameters; The new primary database has been launched with VIP access for business connections. The original master database stopped all processes not on the whitelist; After all processes of the original primary database are stopped and there is no business connection, the configuration of the original primary database is modified. Perform the master-slave relationship check again. If the check is successful, the switchover is complete. Each atomic task triggers the Ansible execution tool. Each time the execution tool is triggered, it sends the switching script to the specified switching server, executes the specific instructions on the target server, and completes the specified logic for database switching. If an atomic task in the process of switching fails, the process will proceed to the next atomic task after troubleshooting based on the error message. After the batch database switch is successfully executed, check whether the cluster status and business connections are normal after the switch.

2. The method for batch switching of MySQL databases based on high availability drills according to claim 1, characterized in that, The atomic tasks are arranged in a drag-and-drop manner, allowing them to be placed at any position in the entire processing flow and to be reused.

3. The method for batch switching of MySQL databases based on high availability drills according to claim 1, characterized in that, Each registered server is managed by the Ansible tool, which has the highest execution privileges. Each atomic task triggers a server instruction execution. The Ansible tool selects a relatively idle server from the execution machine to execute the task. The execution process transmits the script to the target server and triggers the operation steps specified in the script.

4. The method for batch switching of MySQL databases based on high availability drills according to claim 1, characterized in that, The steps in the database switching process are implemented as follows: The virtual IP address of the specified main database server is taken offline by command. The service connects to the database through DNS, and DNS resolves the virtual IP address of the database cluster. The virtual IP address and the main database are on different network cards of the same server. By setting the main database status to read-only using a global variable, all users are unable to connect. At the same time, the configuration file is modified so that the read-only setting is invalidated after the database is restarted.

5. A method for batch switching of MySQL databases based on high availability drills according to claim 4, characterized in that, The database master-slave status verification includes: querying the status of global variables to ensure that the slave database data and the master database data are synchronized normally; verifying that case sensitivity of the slave database is disabled; verifying that the slave database is read-only so that all users cannot write to it; verifying whether the parameters in the configuration file are the same as the data in memory; and verifying that the slave database records the update operation of the master database are enabled.

6. The method for batch switching of MySQL databases based on high availability drills according to claim 4, characterized in that, The method of setting the main database status to read-only via global variables is a temporary setting. Modifying the configuration file will invalidate the read-only setting after the database restarts.

7. A method for batch switching of MySQL databases based on high availability drills according to claim 4, characterized in that, The verification items for establishing the master-slave relationship include: Slave_IO_Running and Slave_SQL_Running, which ensure that the new master and slave databases can synchronize data normally; and Last_IO_Error, which ensures that the master-slave relationship is established normally and there are no errors.

8. A method for batch switching of MySQL databases based on high availability drills according to claim 4, characterized in that, The master-slave relationship verification includes: verifying whether there is a delay between the master and slave; determining whether the file read by the slave database is the same as the binlog file that the slave database has replayed in the master database, i.e., whether the slave database has completed the data replay; and comparing whether the log offset of the binlog file read by the slave database is the same as the offset of the binlog file that the slave database has replayed in the master database, i.e., whether the slave database has replayed to the specified offset.

9. A method for batch switching of MySQL databases based on high availability drills according to claim 4, characterized in that, If, after the original master database executes three commands to stop all processes outside the whitelist, there are still connection processes outside the whitelist, then the database process will be restarted.

10. A method for batch switching of MySQL databases based on high availability drills according to claim 1, characterized in that, When the primary database is unable to provide services, the database failover procedure is as follows: The primary database virtual machine VIP of the specified server is taken offline via operating system commands. Before switching, enable full log refresh in the database (general_log=1) to record and save the connection and disconnection of the primary and standby machines and session-level statements before the switch. Switch from the primary database to the secondary database, clear the original primary-secondary relationship, and record the position parameters of the secondary database before the switch. Modify the configuration of the new primary database, change the configuration file parameters and global variable parameters to read-only, enable the scheduled task and disable full log refresh (general_log=0); The new primary database has launched a VIP service for business connections.

Images