Database master-slave replication method, device, computer device and storage medium

Abstract

The present application relates to a method, device, computer device and storage medium for master-slave replication of a database. The method includes: in response to a database master-slave replication request corresponding to the master database to be replicated, performing a cluster operation on the database cluster corresponding to the master database, setting the state of the database cluster to the master-slave replication mode, and determining the first latency state of each slave database in the database cluster; if the first latency state of each slave database is not delayed, obtaining an identifier corresponding to the database master-slave replication request for representing a database preset transaction; sending the identifier to each slave database so that each slave database performs a database master-slave replication setting operation according to the identifier. By using this method, it is possible to avoid data inconsistency stored in the master database and the slave database caused by latency between the master and slave databases, thereby improving the accuracy of database master-slave replication.

Description

Technical Field

[0001] This application relates to the technical field of database processing, and particularly to a method, device, computer device, and storage medium for database master-slave replication. Background Art

[0002] With the development of database processing technology, a technology for realizing database master-slave replication through the GTID mode has emerged. GTID, that is, the global transaction identifier, is used to ensure that each transaction committed on the master database has a unique ID in the cluster. Compared with the traditional database master-slave replication method, the database master-slave replication based on the GTID mode has many advantages such as higher security, simpler failover, and lower data loss rate, and has become a popular method for database master-slave replication.

[0003] However, in the current database master-slave replication method based on the GTID mode, the data stored in the master database may be inconsistent with the data in the slave database before replication, and the accuracy of database master-slave replication is relatively low. Summary of the Invention

[0004] Based on this, it is necessary to provide a method, device, computer device, and storage medium for database master-slave replication to solve the above technical problems.

[0005] A method for database master-slave replication, the method includes:

[0006] In response to a database master-slave replication request corresponding to the master database to be replicated, perform a cluster operation on the database cluster corresponding to the master database, set the state of the database cluster to the master-slave replication mode, and determine the first delay state of each slave database in the database cluster;

[0007] If the first delay state of each slave database is not delayed, obtain an identifier for representing a database preset transaction corresponding to the database master-slave replication request;

[0008] Send the identifier to each slave database so that each slave database performs a database master-slave replication setting operation according to the identifier.

[0009] In one of the embodiments, the master-slave replication mode is the GTID master-slave replication mode; the identifier is a global transaction identifier; the obtaining of an identifier corresponding to the database master-slave replication request for characterizing a preset transaction of a database includes: obtaining a global transaction identifier corresponding to the database master-slave replication request; the sending of the identifier to each slave database so that each slave database performs a database master-slave replication setting operation according to the identifier includes: sending the global transaction identifier to each slave database so that each slave database performs a database GTID master-slave replication setting operation according to the global transaction identifier.

[0010] In one of the embodiments, determining the first delay state of each slave database in the database cluster includes: obtaining the node delay time between the slave database and the master database; if the node delay time is greater than a preset time threshold, determining that the first delay state of the slave database is delay; or if the node delay time is less than or equal to the time threshold, determining that the first delay state of the slave database is no delay.

[0011] In one of the embodiments, after determining that the first delay state of the slave database is a delay, the method further includes: canceling the cluster operation on the database cluster, and restoring the state of the database cluster to a cluster mode.

[0012] In one of the embodiments, before obtaining the global transaction identifier corresponding to the database master-slave replication request, it also includes: setting each slave database to a maintenance state and setting the master database to a read-only state; after sending the global transaction identifier to each slave database so that each slave database performs a database GTID master-slave replication setting operation according to the global transaction identifier, it also includes: restoring the master database to a readable and writable state and releasing the maintenance state of each slave database.

[0013] In one of the embodiments, after setting the master database to a read-only state, the method further includes: determining a second delay state of each slave database in the database cluster; and obtaining the global transaction identifier if the second delay state of each slave database is no delay.

[0014] In one of the embodiments, determining the second delay state of each slave database in the database cluster includes: comparing file information of preset files in the master database and the slave database; if there is no difference in the file information, determining that the second delay state of the slave database is no delay.

[0015] In one embodiment, after comparing the file information of the preset files in the master database and the slave database, the following steps are further included: If there are differences in the file information, compare the file information of the preset files in the master database and the slave database again at a preset comparison time interval, and obtain the current comparison count; If the current comparison count reaches a preset count threshold and there are still differences in the file information, determine that the second delay state of the slave database is delayed, cancel the clustering operation on the database cluster, and restore the state of the database cluster to the cluster mode.

[0016] In one embodiment, before obtaining the global transaction identifier, the following steps are further included: Determine whether the master database pre-stores a global transaction identifier; If the master database pre-stores a global transaction identifier, reset the master database.

[0017] In one embodiment, after performing a clustering operation on the database cluster corresponding to the master database, the following steps are further included: Obtain the replication status of each slave database, and determine a delayed replication slave database from each slave database; The delayed replication slave database is a slave database in a delayed replication state; Cancel the delayed replication state of the delayed replication slave database; After sending the identifier to each slave database to enable each slave database to perform a database master-slave replication setting operation according to the identifier, the following step is further included: Restore the delayed replication slave database to the delayed replication state.

[0018] A database master-slave replication device, the device includes:

[0019] A first delay determination module, configured to respond to a database master-slave replication request corresponding to a master database to be replicated, perform a clustering operation on the database cluster corresponding to the master database, set the state of the database cluster to the master-slave replication mode, and determine the first delay state of each slave database in the database cluster;

[0020] An identifier acquisition module, configured to obtain an identifier for characterizing a database preset transaction corresponding to the database master-slave replication request if the first delay state of each slave database is not delayed;

[0021] A database replication module, configured to send the identifier to each slave database to enable each slave database to perform a database master-slave replication setting operation according to the identifier.

[0022] A computer device, including a memory and a processor, where the memory stores a computer program, and the processor implements the steps of the above method when executing the computer program.

[0023] A computer-readable storage medium stores a computer program thereon, and when the computer program is executed by a processor, the steps of the above method are implemented.

[0024] In the above database master-slave replication method, device, computer device, and storage medium, in response to a database master-slave replication request corresponding to a master database to be replicated, a cluster operation is performed on the database cluster corresponding to the master database, the state of the database cluster is set to the master-slave replication mode, and the first delay status of each slave database in the database cluster is determined; if the first delay status of each slave database is not delayed, an identifier for characterizing a database preset transaction corresponding to the database master-slave replication request is obtained; the identifier is sent to each slave database so that each slave database performs a database master-slave replication setting operation according to the identifier. By determining the delay status of each slave database before performing the replication operation on the slave database, and only performing the replication operation when there is no delay, the present application can avoid data inconsistency in the data stored in the master database and the slave database caused by the delay between the master and slave databases, thereby improving the accuracy of database master-slave replication. BRIEF DESCRIPTION OF THE DRAWINGS

[0025] Figure 1 It is a schematic flowchart of a database master-slave replication method in an embodiment;

[0026] Figure 2 It is a schematic structural diagram of a database cluster in an embodiment;

[0027] Figure 3 It is a schematic flowchart of determining the first delay status of each slave database in an embodiment;

[0028] Figure 4 It is a schematic flowchart of obtaining a global transaction identifier in an embodiment;

[0029] Figure 5 It is a schematic flowchart of a database master-slave replication method in another embodiment;

[0030] Figure 6 It is a schematic flowchart of database schema change in an application example;

[0031] Figure 7 It is a schematic flowchart of database schema change in another application example;

[0032] Figure 8 It is a schematic block diagram of a database master-slave replication device in an embodiment;

[0033] Figure 9 It is an internal structural diagram of a computer device in an embodiment. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0034] In order to make the objectives, technical solutions and advantages of the present application more clear and understandable, the present application will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0035] In one embodiment, as Figure 1 shown, a method for master-slave replication of a database is provided. In this embodiment, an example is given where this method is applied to a terminal. It can be understood that this method can also be applied to a server, and can also be applied to a system including a terminal and a server, and is implemented through the interaction between the terminal and the server. In this embodiment, the method includes the following steps:

[0036] Step S101, in response to a database master-slave replication request corresponding to the master database to be replicated, the terminal performs a cluster operation on the database cluster corresponding to the master database, sets the state of the database cluster to the master-slave replication mode, and determines the first delay state of each slave database in the database cluster.

[0037] Among them, a database cluster refers to a cluster composed of multiple databases. This cluster may include one master database and several slave databases. For example, the structure of this database cluster may be as Figure 2 shown. This cluster contains 4 databases, namely database 201, database 202, database 203, and database 204. Among them, database 201 can be used as the master database in this database cluster, while database 202, database 203, and database 204 are used as the slave databases in this database cluster.

[0038] When a user needs to perform master-slave replication on a certain master database, the master database that needs to perform master-slave replication can be used as the master database to be replicated. The user can trigger a database request through the terminal so that the terminal can execute the replication operation on the master database. Specifically, the terminal can first, according to the database cluster corresponding to the master database to be replicated, and each slave database in this database cluster. For example, if it is necessary to replicate database 201, then the corresponding one is as Figure 2 shown database cluster. Each slave database in the database cluster refers to database 202, database 203, and database 204. The terminal can set the state of this database cluster from the cluster mode to the master-slave replication mode by triggering a cluster operation on the database cluster corresponding to the master database to be replicated. The master-slave replication mode can include various modes such as asynchronous mode, semi-synchronous mode, multi-source replication mode, and GTID master-slave replication mode. Then, the delay state between each slave database and the master database in the database cluster can be determined as the first delay state.

[0039] Step S102, if the first delay status of each slave database is not delayed, the terminal obtains an identifier corresponding to the database master-slave replication request and used to represent the preset transaction of the database.

[0040] Among them, the identifier is mainly used to represent a transaction pre-designed for a certain database. It can be the number of a committed transaction. When the database receives this number, it can execute the transaction operation corresponding to this number. Therefore, after triggering the database master-slave replication request, the terminal needs to find the corresponding global transaction identifier. For example, if the replication request is to replicate a certain piece of data information A added in the master database, then the terminal needs to obtain the identifier corresponding to the replicated data information A.

[0041] Specifically, when the terminal determines that all slave databases are not delayed, it can obtain an identifier corresponding to the database master-slave replication request according to the request and used to represent the preset transaction of the database.

[0042] Step S103, the terminal sends the identifier to each slave database so that each slave database performs the database master-slave replication setting operation according to the identifier.

[0043] Finally, the terminal can send the identifier obtained in step S102 to each slave database in the database cluster so that each slave database can perform the corresponding database master-slave replication setting operation according to the identifier to implement the replication operation for the master database.

[0044] In the above database master-slave replication method, the terminal responds to the database master-slave replication request corresponding to the master database to be replicated, performs cluster operations on the database cluster corresponding to the master database, sets the status of the database cluster to the master-slave replication mode, and determines the first delay status of each slave database in the database cluster; if the first delay status of each slave database is not delayed, it obtains an identifier corresponding to the database master-slave replication request and used to represent the preset transaction of the database; and sends the identifier to each slave database so that each slave database performs the database master-slave replication setting operation according to the identifier. By determining the delay status of each slave database before performing the replication operation on the slave database, and only performing the replication operation when there is no delay, this application can avoid data inconsistency between the master database and the slave database caused by the delay between the master and slave databases, thereby improving the accuracy of database master-slave replication.

[0045] In one embodiment, the master-slave replication mode is the GTID master-slave replication mode; the identifier is the global transaction identifier; step S102 may further include: the terminal obtains the global transaction identifier corresponding to the database master-slave replication request; step S103 may further include: the terminal sends the global transaction identifier to each slave database, so that each slave database performs the database GTID master-slave replication setting operation according to the global transaction identifier.

[0046] The master-slave replication mode used in this embodiment is the GTID master-slave replication mode. This replication mode can set a unique identifier for each slave database that can be used to identify global transactions, that is, the global transaction identifier. This identifier can be represented by a certain parameter, which can be the GTID parameter. Specifically, when the master-slave replication mode adopts the GTID master-slave replication mode, the terminal can use the GTID parameter corresponding to the database master-slave replication request as the global transaction identifier and send this GTID parameter to each slave database, so that the slave database executes the transaction corresponding to this GTID parameter to achieve GTID master-slave replication.

[0047] Further, as Figure 3 shown, in step S101, for the terminal to determine the first delay status of each slave database in the database cluster, it may further include:

[0048] Step S301, the terminal obtains the node delay time between the slave database and the master database.

[0049] Among them, the node delay time refers to the delay time between the slave database and the master database. This node delay time can be obtained by the terminal reading the parameters of each slave database. For example, the terminal can read the seconds_behind_master parameter in each slave database as the node delay time between this slave database and the master database.

[0050] Step S302, if the node delay time is greater than the preset time threshold, the terminal determines that the first delay status of the slave database is delayed;

[0051] Step S303, if the node delay time is less than or equal to the time threshold, the terminal determines that the first delay status of the slave database is not delayed.

[0052] Afterwards, the terminal can compare the obtained node delay time with a certain pre-set time threshold, and determine whether there is a delay state according to the size relationship between the node delay time and the time threshold. Specifically, when the node delay time is larger than the pre-set time threshold, that is, the node delay time is at a relatively large value, then the terminal can determine that there is a delay between the slave database and the master database, that is, the first delay state is a delay, and if the node delay time is less than or equal to the pre-set time threshold, then the terminal can consider that the delay time between the slave database and the master database is still within an acceptable range, so in this case, the terminal can determine that the first delay state of the slave database is no delay.

[0053] Furthermore, after step S302, the method may further include: the terminal cancels the cluster operation on the database cluster, and restores the state of the database cluster to the cluster mode.

[0054] Since the master database and the slave database directly execute database replication without being in a state of data synchronization, it is easy to cause the accuracy of database master-slave replication to decrease. Therefore, in this embodiment, if the first delay state of any slave database is a delay, the terminal can cancel the cluster operation of the database cluster and restore the state of the database cluster from the GTID master-slave replication mode to the cluster mode before the cluster operation.

[0055] In this embodiment, the terminal can determine whether each slave database is delayed by the size relationship between the node delay time and the time threshold, thereby improving the accuracy of delay status judgment. At the same time, when a delay occurs in the slave database, the cluster operation can be stopped and restored to the cluster mode to stop the replication process, thereby ensuring the stability of the data stored in the master database and the slave database when synchronization fails.

[0056] In one embodiment, before the terminal obtains the global transaction identifier corresponding to the database master-slave replication request, it may also include: the terminal sets each slave database to a maintenance state and sets the master database to a read-only state; after the terminal sends the global transaction identifier to each slave database so that each slave database performs the database GTID master-slave replication setting operation according to the global transaction identifier, it may also include: restoring the master database to a readable and writable state and releasing the maintenance state of each slave database.

[0057] Specifically, in order to prevent the user from modifying the data stored in the master database during the master-slave replication of the database, thereby causing the data synchronization with the slave database to fail, in this embodiment, the terminal can set the master database to a read-only state before executing the master-slave replication of the database, that is, not allowing the user to modify the master database, and set the corresponding slave database to a maintenance state. When the master-slave replication of the database is completed, the states of the master database and the slave database need to be restored, so the terminal needs to restore the master database from a read-only state to a normal read-write state, and cancel the maintenance state set for the slave database.

[0058] Furthermore, if Figure 4 As shown, after the terminal sets the main database to read-only status, it can also include:

[0059] Step S401, the terminal determines a second delay state of each slave database in the database cluster;

[0060] The second delay state refers to the terminal setting the slave database to the maintenance state and the master database to the read-only state, and then the terminal can confirm the delay state of each slave database and the master database again to obtain it. Specifically, after the terminal sets the database cluster to the GTID master-slave replication mode, it needs to first confirm the delay state of each slave database in the database cluster, that is, confirm the first delay state. After that, the terminal can set the slave database to the maintenance state, and can also set the master database to the read-only state, and confirm the delay state of each slave database and the master database again as the confirmation of the second delay state.

[0061] Step S402: If the second delay status of each slave database is no delay, the terminal obtains a global transaction identifier.

[0062] After the terminal determines the second delay state of each slave database in step S401, only when the second delay state of each slave database is no delay, the terminal will obtain the global transaction identifier corresponding to the database master-slave replication request to perform database master-slave replication.

[0063] Furthermore, step S401 may further include: the terminal compares file information of preset files in the master database and the slave database; if there is no difference in the file information, determining that the second delay state of the slave database is no delay.

[0064] For the judgment of the second latency state between the slave database and the master database, it is no longer carried out by means of latency time, but by comparing the file information of a certain preset file in the slave database with the corresponding file in the master database. The preset file can be one or multiple. If there are no differences in the file information of multiple files, that is, when the file information of all preset files is the same, the terminal can confirm that the second latency state of the slave database has not occurred latency.

[0065] For example, the preset files can be Relay_Master_Log_File and Exec_Master_Log_Pos. The terminal can compare the Relay_Master_Log_File and Exec_Master_Log_Pos of each slave database with the file information in the Relay_Master_Log_File and Exec_Master_Log_Pos in the master database respectively. Only when the file information of Relay_Master_Log_File in all slave databases is the same as that in the master database, and the file information of Exec_Master_Log_Pos in all slave databases is the same as that in the master database, can the global transaction identifier be obtained.

[0066] In addition, after the terminal compares the file information of the preset files in the master database and the slave database, it also includes: if there are differences in the file information, compare the file information of the preset files in the master database and the slave database again at the preset comparison time interval, and obtain the current comparison times; if the current comparison times reach the preset number threshold and the file information still has differences, determine that the second latency state of the slave database has occurred latency, cancel the cluster operation on the database cluster, and restore the state of the database cluster to the cluster mode.

[0067] If there are differences in the file information of the preset files in the slave database compared with the file information of the master database, then the terminal will perform the comparison operation again. Specifically, the user can determine the time interval between two comparisons by presetting the time interval for each comparison. The terminal can then perform the comparison of the file information of the preset files in the slave database and the master database again at the set comparison time interval. If there are still differences, it needs to be compared again, and the corresponding comparison times of each comparison operation are obtained as the current comparison times.

[0068] After that, the terminal can determine the magnitude relationship between the current comparison count and a preset count threshold. If the current comparison count is less than the preset count threshold, then if there are still differences in the file information, the comparison can be performed again. If the current comparison count has reached the set count threshold and there are still differences in the file information, then the terminal will stop the next round of comparison, determine that the second delay status of the slave database is a delay, cancel the cluster operation on the database cluster, and restore the status of the database cluster from the GTID master-slave replication mode to the cluster mode before the cluster operation. If there are no differences in the file information, then the terminal can determine that the second delay status of the slave database is not a delay. In this case, the terminal can execute the step of obtaining the global transaction identifier.

[0069] For example: The user can preset the comparison count threshold to 3 times and the time interval for each comparison to 0.1 s. Then when the terminal performs the first comparison of file information, if the result is that there are differences in the file information of the slave database, then the terminal will perform the comparison of file information again after 0.1 s after the comparison is completed, that is, the second comparison of file information, and determine that the current comparison count is 2. If the comparison result of the second comparison is that there are no differences in the file information of all slave databases, then the terminal can obtain the global transaction identifier to perform master-slave replication. If there are still differences between the file information of the slave database and the master database, then since the current comparison count is 2 and has not reached the set count threshold of 3, the third comparison can be performed again. If the third comparison still shows that there are differences in the file information, then since the comparison count has reached the set count threshold, even if there are still differences in the file information, the terminal will not perform the next round of comparison, but directly determine that the second delay status of the slave database is a delay and cancel the cluster operation on the database cluster, and restore the status of the database cluster to the cluster mode.

[0070] Further, before the terminal obtains the global transaction identifier in step S402, it may further include: the terminal determines whether the master database has pre-stored the global transaction identifier; if the master database has pre-stored the global transaction identifier, then reset the master database.

[0071] To avoid problems in the process of setting the global transaction identifier during master-slave replication, before the terminal obtains the global transaction identifier, it can first determine whether the master database has already pre-stored the global transaction identifier. If the master database has pre-stored the global transaction identifier, then the master database needs to be reset to avoid problems in the process of setting the global transaction identifier.

[0072] In the above embodiments, the present application also sets the slave database to a maintenance state before performing master-slave replication, and at the same time sets the master database to a read-only state. After the setting is completed, it is again confirmed whether there is a delay in the slave database, and the confirmation of the delay is by comparing whether there are differences in the file information of the preset files in the slave database and the master database. If they are different, they are compared again. Only when the number of comparisons is greater than the preset number threshold is it confirmed that the second delay state is a delay, and at the same time the database cluster is restored, thereby improving the accuracy of master-slave replication of the database. In addition, in this embodiment, before performing master-slave replication, a process of determining whether the master database pre-stores a global transaction identifier is further performed. If the global transaction identifier is pre-stored, the master database is reset, thereby solving the problems existing in the process of setting the global transaction identifier and further improving the accuracy of master-slave replication of the database.

[0073] In addition, in one embodiment, after the terminal performs a cluster operation on the database cluster corresponding to the master database in step S101, it further includes: the terminal obtains the replication status of each slave database, and determines a delayed replication slave database from each slave database; the delayed replication slave database is a slave database in a delayed replication state; cancel the delayed replication state of the delayed replication slave database; after the terminal sends the global transaction identifier to each slave database in step S103 so that each slave database performs a database GTID master-slave replication setting operation according to the global transaction identifier, it further includes: restoring the delayed replication slave database to the delayed replication state.

[0074] Among them, the replication status refers to the data replication status of the slave database. For example, some slave databases perform update replication immediately after the master database data is updated, and there are also slave databases that perform replication after a period of time after the master database data is updated, that is, the delayed replication state. The delayed replication slave database refers to the slave database that performs replication after a period of time after the master database data is updated, that is, the slave database in the delayed replication state.

[0075] Specifically, in order to enable the master database and the slave database to be in a data synchronization state at any time during the master-slave replication process, after performing a cluster operation on the database cluster, the terminal can first find the slave database in the delayed replication state from the slave databases of the database cluster, and cancel the delayed replication state of the above slave database to keep it in a data synchronization state with the master database. After the master-slave replication is completed, the state of the above slave database can be restored to the original delayed replication state.

[0076] In this embodiment, the terminal can find the slave database in the delayed replication state from the database cluster, cancel the delayed replication state before the master-slave replication of the database, and restore its delayed replication state after the replication is completed, so as to ensure that the master database and the slave database can always be in the data synchronization state during the master-slave replication process, and further improve the accuracy of the master-slave replication of the database.

[0077] In one embodiment, as Figure 5 shown, a master-slave replication method for a database is further provided. In this embodiment, this method is exemplified by being applied to a terminal. In this embodiment, the method includes the following steps:

[0078] Step S501, in response to a master-slave replication request corresponding to the master database to be replicated, the terminal performs a cluster operation on the database cluster corresponding to the master database, obtains the replication status of each slave database, determines the delayed replication slave databases from each slave database, and cancels the delayed replication status of the delayed replication slave databases;

[0079] Step S502, the terminal sets the status of the database cluster to the GTID master-slave replication mode and obtains the node delay time between the slave database and the master database;

[0080] Step S503, if the node delay time is greater than a preset time threshold, the terminal determines that the first delay status of the slave database is delayed; cancels the cluster operation on the database cluster and restores the status of the database cluster to the cluster mode;

[0081] Step S504, if the node delay time is less than or equal to the time threshold, the terminal determines that the first delay status of the slave database is not delayed, sets each slave database to the maintenance state, and sets the master database to the read-only state;

[0082] Step S505, the terminal compares the file information of the preset files in the master database and the slave database;

[0083] Step S506, if there are differences in the file information, the terminal compares the file information of the preset files in the master database and the slave database again at a preset comparison time interval and obtains the current comparison times; if the current comparison times reach the preset number threshold and there are still differences in the file information, it is determined that the second delay status of the slave database is delayed, and the cluster operation on the database cluster is cancelled, and the status of the database cluster is restored to the cluster mode;

[0084] Step S507, if there are no differences in the file information, the terminal determines that the second delay status of the slave database is not delayed, and determines whether the master database pre-stores a global transaction identifier; if the master database pre-stores a global transaction identifier, the master database is reset, and the global transaction identifier is obtained;

[0085] Step S508, the terminal sends the global transaction identifier to each slave database, so that each slave database performs the database GTID master-slave replication setting operation according to the global transaction identifier;

[0086] Step S509: the terminal restores the delayed replication slave database to the delayed replication state, restores the master database to a readable and writable state, and releases the maintenance state of each slave database.

[0087] In the above embodiment, the terminal determines the delay state of each slave database before executing the copy operation on the slave database, and only executes the copy operation when no delay occurs, which can avoid the inconsistency of the data stored in the master database and the slave database data caused by the delay between the master and slave databases, thereby improving the accuracy of the master-slave replication of the database. In addition, before executing the master-slave replication, the slave database is set to the maintenance state, and the master database is set to the read-only state, and after the setting is completed, it is confirmed again whether there is a delay in the slave database, and the delay is confirmed by comparing the file information of the preset files in the slave database and the master database. If they are not the same, they are compared again. Only when the number of comparisons is greater than the preset number threshold, the second delay state is confirmed to be a delay, and the database cluster is restored at the same time, thereby improving the accuracy of the master-slave replication of the database. In addition, this embodiment further executes the process of determining whether the master database has pre-stored global transaction identifiers before executing the master-slave replication. If the global transaction identifiers are pre-stored, the master database is reset, thereby solving the problems existing in the process of setting the global transaction identifiers, and further improving the accuracy of the master-slave replication of the database. And the terminal can find the slave database in the delayed replication state in the slave database cluster, and cancel the delayed replication state before the master-slave replication of the database, and restore its delayed replication state after the replication is completed, thereby ensuring that the master database and the slave database can be in a data synchronization state at any time during the master-slave replication process, further improving the accuracy of the master-slave replication of the database.

[0088] In an application example, a MySQL database cluster is provided to enable master-slave replication change management based on the GTID mode, such as Figure 6 As shown, the GTID mode change management of the MySQL database cluster can be used. This application instance provides a complete operation and maintenance process in terms of GTID change implementation plan, implementation process, change record, and service support. The database cluster can provide external services without differentiation before and after the change.

[0089] Specifically, this application example may include the following steps: Figure 7 As shown:

[0090] For the database cluster management after MySQL 5.6 version, if you need to set up master-slave replication in GTID mode, this solution has a standardized process for change handling for clusters of different architecture types. The main processes are as follows:

[0091] (1) Cancel the delayed replication node: If there is a database node with delayed replication in the cluster, it is necessary to cancel the delayed replication status of this node to make the node resume normal synchronization;

[0092] (2) Check the delay status of the slave nodes in the cluster: Ensure that the data of all nodes in the cluster is consistent;

[0093] Note: In this step, a configurable parameter is defined (node delay time: gtid_all_node_delay_time). If the Seconds_Behind_Master parameter of the slave database in the cluster is greater than this configuration parameter, it is determined that the slave database has a delay.

[0094] (3) Set the maintenance status: Set all cluster nodes to the maintenance status to ensure that the database enters maintenance;

[0095] (4) Set the master node to read-only mode: Make sure that no more data can be updated on the master database, and only data can be read, and data updates cannot be achieved;

[0096] (5) Confirm the master-slave synchronization delay: Check whether the Relay_Master_Log_File and Exec_Master_Log_Pos of all slave nodes are consistent with the master database;

[0097] Note: In this step, configurable parameters are defined (master-slave synchronization delay retry times: gtid_all_node_delay_time, retry interval: gtid_all_node_delay_check_retry_interval). By adjusting these two parameters, the consistency and integrity verification of the cluster master-slave synchronization can be ensured. Specifically, it can be compared whether the Relay_Master_Log_File and Exec_Master_Log_Pos of all slave nodes are consistent with the master node. If they are inconsistent, they need to be compared again according to the retry interval until the Relay_Master_Log_File and Exec_Master_Log_Pos are consistent with the master node, or the retry times reach the set master-slave synchronization delay retry times.

[0098] (6) Check whether there is GTID information in the master database: Before setting the GTID mode, it is necessary to judge whether there is already GTID information in the master database. If it exists, the master database needs to be reset;

[0099] (7) Set the GTID mode parameters for all nodes: Send the GTID mode parameters to all nodes in the cluster;

[0100] Note: This step realizes the automatic GTID change of the entire MySQL cluster. If it fails, it can be automatically rolled back, that is, return to step (1) again, which improves the operation and maintenance cost compared with manual reconstruction. It can achieve continuous database operation, automatic verification of slave databases, failure rollback, and switching of log records.

[0101] (8) Restart the database in sequence;

[0102] (9) Confirm the GTID parameters of all database nodes;

[0103] (10) Configure the slave database;

[0104] (11) Recover the delayed replication nodes;

[0105] (12) Check the master-slave synchronization status;

[0106] (13) Reset the master node to the read-write state;

[0107] (14) Remove the maintenance state of the database cluster;

[0108] The MySQL operations involved in the change process are as follows

[0109] (1) Cancel the delayed replication nodes:

[0110] - stop slave

[0111] - change master to master_delay = 0

[0112] - start slave

[0113] (2) Check the delay status of the slave nodes in the cluster:

[0114] - show slave status>Seconds_Behind_Master

[0115] (3) Set the master node to read-only mode:

[0116] - set global super_read_only = ON

[0117] - set global read_only = ON

[0118] - set global event_scheduler = OFF

[0119] (4) Confirm the master-slave synchronization delay:

[0120] - show master status> File, Position

[0121] - show slave status> Relay_Master_Log_File, Exec_Master_Log_Pos

[0122] (5) Check whether there is GTID information in the master database

[0123] - show master status> Executed_Gtid_Set

[0124] (6) GTID parameter settings:

[0125] gtid_mode = on

[0126] enforce_gtid_consistency = on

[0127] log_slave_updates = on

[0128] binlog_format = row

[0129] skip_slave_start = 1

[0130] slave_parallel_workers = 8

[0131] (7) Restart all databases:

[0132] - crm resource stop

[0133] - crm resource start

[0134] - show master status

[0135] - $datadir / .. / mysql_*restart

[0136] (8) Confirm the GTID parameters of all database nodes:

[0137] - select @@GLOBAL.GTID_MODE as gtid_mode

[0138] (9) Configure the slave on the slave

[0139] - stop slave

[0140] - Reset slave

[0141] - CHANGE MASTER TO MASTER_HOST=”, MASTER_PORT=3306, MASTER_USER=”, MASTER_PASSWORD=”, master_auto_position=1

[0142] - Start slave

[0143] (10) Resume the delayed replication node

[0144] - Stop slave

[0145] - Reset slave

[0146] - CHANGE MASTER TO MASTER_HOST=”, MASTER_PORT=3306, MASTER_USER=”, MASTER_PASSWORD=”, master_auto_position=1

[0147] - Change master to master_delay=28800

[0148] - Start slave

[0149] (11) Check the master - slave synchronization status

[0150] - Show slave status

[0151] (12) Reset the master node to be read - write

[0152] - Set global super_read_only=OFF

[0153] - Set global read_only=OFF

[0154] - Set global event_scheduler=ON

[0155] Through the above application examples, the master - slave cluster of the database can automatically perform GTID mode changes, improving the operation and maintenance efficiency; and provides a complete set of standardized GTID mode master - slave replication change processes to promote accurate database operation and maintenance; at the same time, it reduces manual intervention, provides batch processing, and simplifies the GTID change steps of the database master - slave cluster.

[0156] It should be understood that although the steps in the flowchart of the present application are sequentially shown according to the arrows, these steps do not necessarily need to be executed sequentially in the order indicated by the arrows. Unless there is a clear indication in this document, the execution of these steps has no strict order limit, and these steps can be executed in other orders. Moreover, at least a part of the steps in the figure may include multiple steps or multiple stages. These steps or stages do not necessarily need to be executed at the same moment, but can be executed at different moments. The execution order of these steps or stages does not necessarily need to be sequential, but can be executed alternately or in turn with at least a part of other steps or steps or stages in other steps.

[0157] In one embodiment, as Figure 8 shown, a database master-slave replication device is provided, including: a first delay determination module 801, an identifier acquisition module 802, and a database replication module 803, where:

[0158] The first delay determination module 801 is configured to, in response to a database master-slave replication request corresponding to the master database to be replicated, perform a cluster operation on the database cluster corresponding to the master database, set the state of the database cluster to the master-slave replication mode, and determine the first delay state of each slave database in the database cluster;

[0159] The identifier acquisition module 802 is configured to, if the first delay state of each slave database is not delayed, acquire an identifier corresponding to the database master-slave replication request for characterizing a database preset transaction;

[0160] The database replication module 803 is configured to send the identifier to each slave database, so that each slave database performs a database master-slave replication setting operation according to the identifier.

[0161] In one embodiment, the master-slave replication mode is the GTID master-slave replication mode; the identifier is a global transaction identifier; the identifier acquisition module 802 is further configured to acquire a global transaction identifier corresponding to the database master-slave replication request; the database replication module 803 is further configured to send the global transaction identifier to each slave database, so that each slave database performs a database GTID master-slave replication setting operation according to the global transaction identifier.

[0162] In one embodiment, the first delay determination module 801 is further configured to acquire the node delay time between the slave database and the master database; if the node delay time is greater than a preset time threshold, determine that the first delay state of the slave database is delayed; if the node delay time is less than or equal to the time threshold, determine that the first delay state of the slave database is not delayed.

[0163] In one embodiment, the database master-slave replication device further includes: a cluster mode recovery module, which is used to cancel the cluster operation on the database cluster and restore the state of the database cluster to the cluster mode.

[0164] In one embodiment, the database master-slave replication device also includes: a database status change module, which is used to set each slave database to a maintenance state and set the master database to a read-only state; and is used to restore the master database to a readable and writable state and release the maintenance state of each slave database.

[0165] In one embodiment, the database master-slave replication device further includes: a second delay determination module, used to determine the second delay state of each slave database in the database cluster; if the second delay state of each slave database is no delay, obtain the global transaction identifier.

[0166] In one embodiment, the second delay determination module is further used to compare file information of preset files in the master database and the slave database; if there is no difference in the file information, the second delay state of the slave database is determined to be no delay.

[0167] In one embodiment, the second delay determination module is further used to compare the file information of the preset file in the main database and the slave database again according to the preset comparison time interval if there is a difference in the file information, and obtain the current comparison number; if the current comparison number reaches the preset number threshold and there are still differences in the file information, it is determined that the second delay state of the slave database is a delay, and the cluster operation of the database cluster is canceled, and the state of the database cluster is restored to the cluster mode.

[0168] In one embodiment, the database master-slave replication device further includes: a database reset module, which is used to determine whether the master database has pre-stored global transaction identifiers; if the master database has pre-stored global transaction identifiers, reset the master database.

[0169] In one embodiment, the database master-slave replication device also includes: a delayed replication change module, which is used to obtain the replication status of each slave database and determine the delayed replication slave database from each slave database; the delayed replication slave database is a slave database in a delayed replication state; cancel the delayed replication state of the delayed replication slave database; and restore the delayed replication slave database to a delayed replication state.

[0170] For the specific limitations of the database master-slave replication device, reference can be made to the limitations of the database master-slave replication method in the above text, which will not be elaborated here. Each module in the above database master-slave replication device can be implemented in whole or in part by software, hardware, or a combination thereof. The above-mentioned modules can be embedded in the processor of the computer device in hardware form or independent of it, or stored in the memory of the computer device in software form, so that the processor can call and execute the operations corresponding to each of the above modules.

[0171] In one embodiment, a computer device is provided. The computer device can be a terminal, and its internal structure diagram can be as Figure 9 shown. The computer device includes a processor, a memory, a communication interface, a display screen, and an input device connected through a system bus. Among them, the processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system and a computer program. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The communication interface of the computer device is used to communicate with an external terminal in a wired or wireless manner. The wireless manner can be implemented through WIFI, a carrier network, NFC (Near Field Communication), or other technologies. When the computer program is executed by the processor, it implements a database master-slave replication method. The display screen of the computer device can be a liquid crystal display screen or an electronic ink display screen. The input device of the computer device can be a touch layer covering the display screen, or a button, a trackball, or a touchpad provided on the housing of the computer device, or an external keyboard, touchpad, or mouse, etc.

[0172] Those skilled in the art can understand that Figure 9 the structure shown in

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

[0174] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, it implements the steps in the above method embodiments.

[0175] Those of ordinary skill in the art can understand that all or part of the processes in the methods of the above embodiments can be completed by instructing relevant hardware through a computer program. The computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above various methods. Among them, any reference to a memory, storage, database, or other medium used in the various embodiments provided in the present application can include at least one of non-volatile and volatile memories. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, or optical memory, etc. Volatile memory can include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM), etc.

[0176] The technical features of the above embodiments can be combined arbitrarily. For the sake of brevity of description, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, it should be considered as the scope described in this specification.

[0177] The above-described embodiments merely represent several implementation manners of the present application. The description is relatively specific and detailed, but it should not be construed as a limitation on the scope of the invention patent. It should be noted that for those of ordinary skill in the art, without departing from the concept of the present application, several modifications and improvements can still be made, and these all belong to the protection scope of the present application. Therefore, the protection scope of the patent of the present application shall be subject to the appended claims.

Claims

1. A method for master-slave replication of a database, characterized in that, the method includes: In response to a database master-slave replication request corresponding to the master database to be replicated, perform a cluster operation on the database cluster corresponding to the master database, set the state of the database cluster to the master-slave replication mode, and determine the first delay state of each slave database in the database cluster; If the first delay state of each slave database is not delayed, obtain an identifier for characterizing a database preset transaction corresponding to the database master-slave replication request; the identifier is a global transaction identifier; Send the identifier to each slave database so that each slave database performs a database master-slave replication setting operation according to the identifier; The obtaining of the identifier for characterizing the database preset transaction corresponding to the database master-slave replication request includes: setting each slave database to a maintenance state and setting the master database to a read-only state; comparing the file information of preset files in the master database and each slave database; if it is determined according to the file information that the second delay state of each slave database is not delayed, obtain the global transaction identifier; After comparing the file information of the preset files in the master database and each slave database, it further includes: for each slave database, if there are differences in the file information, compare the file information of the preset files in the master database and the slave database again at a preset comparison time interval, and obtain the current comparison times; if the current comparison times reach a preset number threshold and the file information still has differences, determine that the second delay state of the slave database is delayed, cancel the cluster operation on the database cluster, and restore the state of the database cluster to the cluster mode.

2. The method according to claim 1, characterized in that, the master-slave replication mode is the GTID master-slave replication mode; The sending of the identifier to each slave database so that each slave database performs a database master-slave replication setting operation according to the identifier includes: Sending the global transaction identifier to each slave database so that each slave database performs a database GTID master-slave replication setting operation according to the global transaction identifier.

3. The method according to claim 2, characterized in that, the determining of the first delay state of each slave database in the database cluster includes: Obtaining the node delay time between the slave database and the master database; If the node delay time is greater than a preset time threshold, determine that the first delay state of the slave database is delayed; or If the node delay time is less than or equal to the time threshold, determine that the first delay state of the slave database is not delayed.

4. The method according to claim 3, characterized in that, after determining that the first delay state of the slave database is delayed, it further includes: Canceling the cluster operation on the database cluster and restoring the state of the database cluster to the cluster mode.

5. The method according to claim 2, characterized in that, After sending the global transaction identifier to each slave database so that each slave database performs the database GTID master-slave replication setting operation according to the global transaction identifier, the method further includes: Restoring the master database to a read-write state and lifting the maintenance state of each slave database.

6. The method according to claim 1, wherein, after comparing the file information of the preset files in the master database and each slave database, the method further includes: For each slave database, if there is no difference in the file information, determining that the second delay state of the slave database is not delayed.

7. The method according to claim 1, wherein, before obtaining the global transaction identifier, the method further includes: Determining whether the master database pre-stores a global transaction identifier; If the master database pre-stores a global transaction identifier, resetting the master database.

8. The method according to any one of claims 1 to 7, wherein, after performing a cluster operation on the database cluster corresponding to the master database, the method further includes: Obtaining the replication status of each slave database and determining a delayed replication slave database from each slave database; the delayed replication slave database is a slave database in a delayed replication state; Canceling the delayed replication state of the delayed replication slave database; after sending the identifier to each slave database so that each slave database performs the database master-slave replication setting operation according to the identifier, the method further includes: Restoring the delayed replication slave database to the delayed replication state.

9. A database master-slave replication device, wherein, the device includes: A first delay determination module, configured to, in response to a database master-slave replication request corresponding to a master database to be replicated, perform a cluster operation on the database cluster corresponding to the master database, set the state of the database cluster to the master-slave replication mode, and determine the first delay state of each slave database in the database cluster; An identifier acquisition module, configured to, if the first delay state of each slave database is not delayed, acquire an identifier corresponding to the database master-slave replication request and used to represent a database preset transaction; the identifier is a global transaction identifier; A database replication module, configured to send the identifier to each slave database so that each slave database performs a database master-slave replication setting operation according to the identifier; The device further includes a database state change module, configured to set each slave database to a maintenance state and set the master database to a read-only state; The device further includes a second delay determination module, configured to set each of the slave databases to a maintenance state and set the master database to a read-only state; compare the file information of a preset file in the master database with that in each of the slave databases; if it is determined according to the file information that the second delay status of each of the slave databases has not been delayed, obtain the global transaction identifier; for each slave database, if there are differences in the file information, compare the file information of the preset file in the master database with that in the slave database again at a preset comparison time interval, and obtain the current comparison times; if the current comparison times reach a preset number threshold and there are still differences in the file information, determine that the second delay status of the slave database is delayed, cancel the cluster operation on the database cluster, and restore the status of the database cluster to the cluster mode.

10. A computer device, comprising a memory and a processor, where the memory stores a computer program, characterized in that when the processor executes the computer program, the steps of the method according to any one of claims 1 to 8 are implemented.

11. A computer-readable storage medium, on which a computer program is stored, characterized in that when the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 8 are implemented.

Images