Master-slave delay determination method and related equipment

By recording the target update statement and the current system time in the binary log of the master-slave database, and updating the timestamp using the target column attribute of the heartbeat table, the problem of insufficient master-slave delay detection accuracy in the existing technology is solved, and a higher Time accuracy and data synchronization accuracy.

CN119088881APending Publication Date: 2024-12-06XINGIN INFORMATION TECH (SHANGHAI) CO LTD
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202411135529.4
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-08-19
Publication Date
2024-12-06

AI Technical Summary

Technical Problem

The existing master-slave delay detection methods can only be accurate to the second level, resulting in a decrease in the accuracy of master-slave delay and cannot be accurate to less than the second level, such as milliseconds or microseconds, affecting the accuracy of data synchronization.

Method used

By recording the target update statement and the current system time in the binary log between the master and slave servers, the time stamp is updated using the target column attribute of the heartbeat table, and the time accuracy is lower than the second level, thereby improving the time accuracy of the master and slave delay .

Benefits of technology

Improves the time accuracy of master-slave delays, can be accurate to the millisecond or microsecond level, and enhances the accuracy and reliability of data synchronization.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN119088881A_ABST
    Figure CN119088881A_ABST
Patent Text Reader

Abstract

The invention discloses a master-slave delay determination method and related device.The method is applied to a slave server and comprises the steps that first current system time and a target update statement are obtained from a binary log, and the target update statement and the first current system time are obtained when a master server executes the target update statement on a master database; writing the target update statement and the first current system time into a binary log; the value of the first field of the heartbeat table of the slave database is updated to the second current system time of the slave server by executing the target update statement, the value of the second field of the heartbeat table of the slave database is updated to the first current system time, and the time precision of the value of the first field and the time precision of the value of the second field are both lower than the second level; and the master-slave delay is determined based on the second current system time and the first current system time, so that the accuracy of the master-slave delay is improved.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present application relates to the technical field of database synchronization, and in particular to a method for determining a master-slave delay and related equipment. Background Art

[0002] In order to achieve server load balancing and enhance the database system's ability to resist disasters, a master-slave synchronization architecture is adopted, that is, the data in the database on the master server (referred to as the "master database") is copied to the database on the slave server (referred to as the "slave database") to achieve data consistency between the master database and the slave database.

[0003] However, due to factors such as network and distance, there is a delay when the slave database copies the data of the master database. At present, the method for detecting the master-slave delay is generally to execute the show slave status command on the slave database to obtain the value of the Seconds_Behind_Master parameter. This value represents the master-slave delay, but the time accuracy of the value obtained in the above way can only be accurate to the second level, which reduces the accuracy of the master-slave delay. Summary of the invention

[0004] The present application provides a method for determining a master-slave delay and related equipment, so that the time precision of the master-slave delay is lower than the second level, thereby improving the accuracy of the master-slave delay.

[0005] In a first aspect, the present application provides a master-slave delay determination method, which is applied to a slave server, and the method includes:

[0006] Obtaining a first current system time and a target update statement from the binary log of the primary server, wherein the target update statement and the first current system time are written into the binary log by the primary server when the primary server executes the target update statement on the primary database;

[0007] By executing the target update statement, the value of the first field of the heartbeat table of the slave database is updated to the second current system time of the slave server, and the value of the second field of the heartbeat table of the slave database is updated to the first current system time, wherein the time precision of the value of the first field and the value of the second field are both lower than the second level;

[0008] A master-slave delay is determined based on the second current system time and the first current system time.

[0009] In a second aspect, the present application provides a method for determining a master-slave delay, which is applied to a master server, and the method includes:

[0010] Get the target update statement for the primary database;

[0011] By executing the target update statement, the value of the second field of the heartbeat table of the primary database is updated to the first current system time of the primary server;

[0012] The first current system time is obtained from the second field of the heartbeat table of the master database to write the target update statement and the first current system time into the binary log, so that the slave server updates the value of the first field of the heartbeat table of the slave database to the second current system time of the slave server by executing the target update statement in the binary log, and updates the value of the second field of the heartbeat table of the slave database to the first current system time, so that the slave server determines the master-slave delay based on the second current system time and the first current system time, wherein the time accuracy of the value of the first field and the value of the second field are both lower than the second level.

[0013] In a third aspect, the present application provides a slave server, the slave server comprising: a first acquisition unit and a first processing unit;

[0014] A first acquisition unit is used to acquire a first current system time and a target update statement from a binary log of a primary server, wherein the target update statement and the first current system time are written into the binary log by the primary server when the primary server executes the target update statement on the primary database;

[0015] A first processing unit is used to update the value of the first field of the heartbeat table of the slave database to the second current system time of the slave server, and to update the value of the second field of the heartbeat table of the slave database to the first current system time by executing a target update statement, wherein the time precision of the value of the first field and the value of the second field are both lower than the second level;

[0016] The first processing unit is further used to determine the master-slave delay based on the second current system time and the first current system time.

[0017] In a fourth aspect, the present application provides a main server, the main server comprising: a second acquisition unit and a second processing unit;

[0018] A second acquisition unit is used to acquire a target update statement for the primary database;

[0019] A second processing unit is used to update the value of the second field of the heartbeat table of the primary database to the first current system time of the primary server by executing the target update statement;

[0020] The second processing unit is further used to obtain the first current system time from the second field of the heartbeat table of the master database, so as to write the target update statement and the first current system time into the binary log, so that the slave server updates the value of the first field of the heartbeat table of the slave database to the second current system time of the slave server by executing the target update statement in the binary log, and updates the value of the second field of the heartbeat table of the slave database to the first current system time, so that the slave server determines the master-slave delay based on the second current system time and the first current system time, wherein the time accuracy of the value of the first field and the value of the second field are both lower than the second level.

[0021] In a fifth aspect, the present application provides an electronic device, comprising: a processor and a memory, the processor being connected to the memory, the memory being used to store a computer program, and the processor being used to execute the computer program stored in the memory, so that the electronic device executes the method of the first aspect or the second aspect.

[0022] In a sixth aspect, the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and the computer program enables a computer to execute the method of the first aspect or the second aspect.

[0023] In a seventh aspect, the present application provides a computer program product, which includes a non-transitory computer-readable storage medium storing a computer program, and is computer-operable to cause the computer to execute the method of the first aspect or the second aspect.

[0024] Implementing this application has the following beneficial effects:

[0025] After the master server obtains the target update statement for the master database, the master server executes the target update statement on the master database, and updates the value of the second field in the heartbeat table of the master database to the first current system time of the master server, that is, the first current system time is the current system time of the master server when the master server executes the target update statement to update the master database; then the master server writes the target update statement and the first current system time to the binary log; then the slave server obtains the first current system time and the target update statement from the binary log of the master server; then the slave server updates the value of the first field in the heartbeat table of the slave database to the second current system time of the slave server by executing the target update statement, and The value of the second field is updated to the first current system time, wherein the time precision of the value of the first field and the value of the second field are both lower than the second level, that is, the second current system time can be understood as the target event recorded by the slave server in the playback binary log, that is, the current system time of the slave server when executing the target update statement, and the first current system time is the current system time corresponding to the target event in the binary log, that is, the current system time of the master server when executing the target time, and the precision of the first current system time and the second current system time are both lower than the second level, then finally based on the second current system time and the first current system time, the time precision of determining the master-slave delay can also be lower than the second level, such as millisecond level, microsecond level, etc., thereby improving the accuracy of the master-slave delay. BRIEF DESCRIPTION OF THE DRAWINGS

[0026] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0027] Figure 1 A schematic diagram of a master-slave replication provided in an embodiment of the present application;

[0028] Figure 2 A flowchart of a method for determining a master-slave delay provided in an embodiment of the present application;

[0029] Figure 3 A flowchart of another method for determining master-slave delay provided in an embodiment of the present application;

[0030] Figure 4 A schematic diagram of master-slave delay determination provided in an embodiment of the present application;

[0031] Figure 5 A schematic diagram of a master-slave delay determination system is provided for an embodiment of the present application;

[0032] Figure 6 A schematic diagram of another master-slave delay determination system provided in an embodiment of the present application;

[0033] Figure 7 A block diagram of the functional units of a slave server provided in an embodiment of the present application;

[0034] Figure 8 A block diagram of the functional units of a main server provided in an embodiment of the present application;

[0035] Fig. 9 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0036] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0037] The terms "first", "second", "third" and "fourth" etc. in the specification, claims and drawings of the present application are used to distinguish different objects, rather than to describe a specific order. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions. For example, a process, method, system, product or device that includes a series of steps or units is not limited to the listed steps or units, but optionally includes steps or units that are not listed, or optionally includes other steps or units inherent to these processes, methods, products or devices.

[0038] Reference to "embodiments" herein means that a particular feature, result, or characteristic described in conjunction with the embodiments may be included in at least one embodiment of the present application. The appearance of the phrase in various locations in the specification does not necessarily refer to the same embodiment, nor is it an independent or alternative embodiment that is mutually exclusive with other embodiments. It is explicitly and implicitly understood by those skilled in the art that the embodiments described herein may be combined with other embodiments.

[0039] First, the relevant terms and related technologies involved in this application are explained:

[0040] Binary log (Binary log, Binlog): It is a binary file in the database system that is used to record operational events that change the database. It is usually used for database backup, replication, and recovery.

[0041] Statement replication mode (Statement): is a database replication technology that is used to write SQL statements that cause data changes executed in the master database into the binary log of the master database, and then re-execute or replay the SQL statements on the slave database to keep the data in the slave database synchronized with the master database.

[0042] Relay log: A type of log used in the database replication process to transmit and store replication events between the master database and the slave database to achieve data replication and synchronization.

[0043] Seconds_Behind_Master: is a method for determining the master-slave delay, which mainly refers to the difference between the current time of the slave server and the time recorded by the event A currently being played back by the slave server when the slave server is playing back the event A read from the binary log.

[0044] like Figure 1 As shown, Figure 1 A schematic diagram of master-slave replication provided in an embodiment of the present application.

[0045] For the master-slave replication of databases, such as the master-slave replication of MySQL, it generally includes a master server and one or more slave servers. Figure 1 Only one slave server is used as an example. Figure 1 As shown, when a write operation (write) event is executed on the primary database, that is, an SQL statement is executed on the primary database. If the execution of the SQL statement performs a change operation on the primary database (such as insert, update, delete, etc.), the write operation event is written to the binary log (Binar ylog). It should be noted that the writing is mainly carried out in accordance with the format of the binary log. For example, if it is in statement replication mode, the executed SQL statement is written to the binary log, that is, the execution statement is recorded. If it is in row replication mode (Row), the specific content of the data change referenced by the execution of the SQL statement is written to the binary log. Each record in the binary log can be called an "event". Then, the I / O thread of the slave server establishes a connection with the binary log, reads events from the binary log, and then the I / O thread writes the read events to the local relay log of the slave server. In short, the events recorded in the binary log are copied to the relay log; then, the SQL thread of the slave server reads these events from the relay log and replays or re-executes these events (for example, if the event corresponds to a SQL statement, the SQL thread will also re-execute the SQL statement), and synchronizes the update of the slave database to synchronize the data changes with the master database.

[0046] However, as can be seen from the above background technology, the traditional method for detecting the master-slave delay is generally to execute the show slave status command on the slave database to obtain the value of the Seconds_Behind_Master parameter, which represents the master-slave delay. However, the time accuracy of the value obtained in the above manner can only be accurate to the second level, and cannot be accurate to a smaller level (such as microsecond level, millisecond level, etc.), thereby reducing the accuracy of the master-slave delay. That is to say, if the actual delay between the master database and the slave database is less than one second, if the traditional method is used, the detected master-slave delay will be zero, making the detected master-slave delay value inaccurate.

[0047] Therefore, in an embodiment of the present application, after the master server obtains the target update statement for the master database, the master server executes the target update statement on the master database, and updates the value of the second field in the heartbeat table of the master database to the first current system time of the master server, that is, the first current system time is the current system time of the master server when the master server executes the target update statement to update the master database; then the master server writes the target update statement and the value of the second field, that is, the first current system time, to the binary log; then the slave server obtains the value of the second field, that is, the first current system time, and the target update statement from the binary log of the master server; then the slave server updates the value of the first field of the heartbeat table of the slave database to the second current system time of the slave server by executing the target update statement. , and updates the value of the second field of the heartbeat table of the slave database to the first current system time, wherein the time precision of the value of the first field and the value of the second field are both lower than the second level, that is, the second current system time can be understood as the target event recorded by the slave server in the playback binary log, that is, the current system time of the slave server when executing the target update statement, and the first current system time is the current system time corresponding to the target event in the binary log, that is, the current system time of the master server when executing the target time, and the precision of the first current system time and the second current system time are both lower than the second level, then finally based on the second current system time and the first current system time, the time precision of determining the master-slave delay can also be lower than the second level, such as millisecond level, microsecond level, etc., thereby improving the accuracy of the master-slave delay.

[0048] First of all, it is explained that the master server and the slave server in the embodiment of the present application are mainly explained by taking the MySQL server as an example.

[0049] See also Figure 2 , Figure 2 A flowchart of a method for determining a master-slave delay provided in an embodiment of the present application is provided. The method is applied to a master server, and the method includes but is not limited to steps S201-S203:

[0050] S201. The primary server obtains a target update statement for the primary database.

[0051] In an embodiment of the present application, the primary server can obtain a target update statement from the client, that is, before the primary server obtains the target update statement from the client, the client first establishes a connection with the primary server, and then the primary server can start a session (Session), and then the client can send the target update statement to the primary server based on the session, so that the primary server executes the target update statement, that is, performs operations on the primary database.

[0052] S202: The primary server updates the value of the second field of the heartbeat table of the primary database to the first current system time of the primary server by executing a target update statement.

[0053] In an embodiment of the present application, before executing step S202, the primary server may set the transaction isolation level of the primary database to a repeatable read isolation level. For example, the primary server may also obtain a first statement from the client through an opened session, and then the primary server sets the transaction isolation level of the primary database to a repeatable read isolation level by executing the first statement. For example, the first statement may be: SET@@session.tx_isolation='Repeatable Read', we can know that under the repeatable read isolation level, a transaction can get consistent results by reading the same row of data multiple times during the execution process. Even if other transactions are executed to modify the data, it will not affect the reading result of the current transaction. For example, the target update statement is executed to update the value of the second field of the heartbeat table of the master database to the first current system time of the master server. Then, for the transaction of executing the target update statement, the value of the second field read from the heartbeat table of the master database, that is, the first current system time, will be written into the binary log. Even in the process of writing the value of the second field read from the heartbeat table of the master database, that is, the first current system time, into the binary log, if the master server executes other transactions to modify the value of the second field of the heartbeat table of the master database, that is, the first current system time, then for the current transaction of executing the target update statement, the value of the second field read is still the first current system time, and then the master-slave delay is calculated based on the first current system time, thereby ensuring the accuracy of the master-slave delay.

[0054] Optionally, before executing step S202, the master server may also set the format of the binary log to statement replication mode, so that after executing the target update statement, the master server writes the target update statement into the binary log. Exemplarily, the master server may also obtain a second statement from the client through an opened session, and then the master server sets the format of the binary log on the master server to statement replication mode by executing the second statement. For example, the second statement may be: SET Session Binlog_format = 'Statement', and then after the master server executes the target update statement to perform an update operation on the master database, the specific statement for executing the update operation may be written into the binary log, that is, the target update statement is written into the binary log.

[0055] Of course, before executing step S202, the master server first creates a heartbeat table on the master database and the slave database (the name of the heartbeat table is "heartbeat_ms"), and the heartbeat table includes a first field, a second field, etc.; and configures a target column attribute for the second field in the heartbeat table of the master database, wherein the target column attribute is used to indicate that an update operation is performed on the fields other than the second field in the heartbeat table of the master database, and the value of the second field in the heartbeat table of the master database is updated to the current system time of the master server, that is, for the row of the second field, when other rows in the heartbeat table are updated, the second field will also be automatically updated, that is, the value of the second field is automatically updated to the current system time of the master server. Exemplarily, the structure of the heartbeat table of the master database can be as follows:

[0056] CREATE TABLE `heartbeat_ms`(

[0057] `ts_master`datetime(6)NOT NULL default CURRENT_TIMESTAMP(6)ON UPDAT ECURRENT_TIMESTAMP(6),

[0058] `ts_self`datetime(6)NOT NULL,

[0059] `server_id`int(10)unsigned NOT NULL,

[0060] `file`varchar(255)DEFAULT NULL,

[0061] `position`bigint(20)unsigned DEFAULT NULL,

[0062] `relay_master_log_file`varchar(255)DEFAULT NULL,

[0063] `exec_master_log_pos`bigint(20)unsigned DEFAULT NULL,

[0064] PRIMARY KEY (`server_id`),

[0065] )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

[0066] Among them, for the first field, namely, `ts_master`datetime(6)NOT NULL default CURRENT_TIMESTAMP(6)ON UPDATE CURRENT_TIMESTAMP(6)comment: ts_master can represent the name of the second field; datetime(6) specifies that the data type of the second field is used to store date and time values, and the time precision is at the microsecond level. Of course, datetime(3) can also be used to specify the time precision as millisecond level, and other time precisions lower than the second level can be set, which are not specifically limited in this application; NOT NULL specifies that the value of the second field cannot be empty; default CURRENT_TIMESTAMP(6) specifies that the default value of the second field is the current system time of the current operation node (for example, when the master server executes an operation event for the master database, the current operation node is the master server, and when the slave server executes an operation event for the slave database, the current operation node is the slave server), and the time precision is at the microsecond level. Similarly, it can also be specified as millisecond level, etc.; ON UPDATE CURRENT_TIMESTAMP(6) means that when other rows are updated, that is, when the data of fields other than the second field in the heartbeat table is modified, the value of the second field will be automatically updated to the current system time of the primary server. In other words, ON UPDATE CURRENT_TIMESTAMP(6) is the target column attribute configured for the second field.

[0067] For the second field, `ts_self`datetime(6)NOT NULL: ts_self can represent the name of the first field; datetime(6) specifies that the data type of the first field is used to store date and time values, and the time precision is at the microsecond level. Similarly, it can also be set to milliseconds or other time precisions lower than seconds; NOT NULL specifies that the value of the first field cannot be empty.

[0068] The third field, `server_id` int(10) unsigned NOT NULL, indicates the field used to store the unique identifier of the server. The data type is a non-negative integer, the length is 10 characters, and the value cannot be empty. For the fourth field, `file` varchar(255) DEFAULT NULL, it indicates the file name of the log of the master database, that is, the binary log. The data type is a variable-length string. The maximum string length is 255, and the default value is NULL. For the fifth field, `position` bigint(20) unsigned DEFAULT NULL, it indicates the position of the current time in the binary log. The data type is a non-negative integer, the length is 20 characters, and the default value is NULL. For the sixth field, `relay_master_log_file` varchar(255) DEFAULT NULL, it indicates the file name of the log of the slave database, that is, the relay log. The data type is a variable-length string. The maximum string length is 255, and the default value is NULL. For the seventh field, `exec_master_log_pos` bigint(20) unsigned DEFAULT NULL, indicating the position of the events in the binary log in the relay log to be re-executed or replayed from the database. The data type is a non-negative integer with a length of 20 characters and a default value of NULL. For the eighth field PRIMARY KEY (`server_id`), it indicates that the primary key is server_id. ENGINE = InnoDB DEFAULT CHARSET = utf8mb4, indicating the storage engine of the heartbeat table.

[0069] It should be noted that the structure of the above-mentioned heartbeat table is only an example. The present application does not specifically limit the number of fields and the setting of attributes in the heartbeat table. In subsequent applications, the embodiments of the present application can adaptively adjust the heartbeat table in the embodiments of the present application based on the specific actual needs of the application.

[0070] Furthermore, the master server can update the value of the second field of the heartbeat table of the master database to the first current system time of the master server by executing a target update statement. Specifically, the master server updates the value of the first field of the heartbeat table of the master database to the first current system time by executing a target update statement. At this time, the first current system time is the current system time of the master server when the update operation is performed on the master database. The target update statement includes a target function, and the target function is used to obtain the current date and time information of the operating system. For example, when the operating system is the master server when the target update statement is executed, the target function is used to obtain the current system time of the master server, and the time accuracy is less than the second level. Then, when the master server executes the target update statement, based on the target function in the target update statement, the value of the first field of the heartbeat table of the master database is updated to the first current system time of the master server. For example, the target update statement can be: REPLACE INTOheartbeat_ms(ts_self,server_id)VALUE(sysdate(6),1), where the target function is sysdate(6), indicating that the value of the first field (i.e., ts_self) in the heartbeat table is updated to the first current system time of the primary server, and the time precision is lower than the second level, where the time precision is the microsecond level, and based on the target update statement, the value of server_id in the heartbeat table can also be updated to 1 (i.e., the identifier of the primary server); at the same time, when it is detected that the primary server updates the value of the first field in the heartbeat table of the primary database to the first current system time by executing the target update statement, that is, when the primary server updates the value of the first field in the heartbeat table to the first current system time, the target column attribute of the second field in the heartbeat table of the primary database can also be identified, and based on the target column attribute, i.e., in response to the target column attribute, the value of the second field in the heartbeat table is updated to the current system time of the primary server, i.e., the first current system time. That is to say, in an embodiment of the present application, by configuring the target column attribute for the second field, when the main server updates the value of the first field to the first current system time by executing the target update statement, the value of the second field is also indirectly and automatically updated to the first current system time based on the target column attribute, that is, the update of the value of the second field is not directly based on the execution of the target update statement.

[0071] S203. The master server obtains the first current system time from the second field of the heartbeat table of the master database to write the target update statement and the first current system time into the binary log, so that the slave server updates the value of the first field of the heartbeat table of the slave database to the second current system time of the slave server by executing the target update statement in the binary log, and updates the value of the second field of the heartbeat table of the slave database to the first current system time, so that the slave server determines the master-slave delay based on the second current system time and the first current system time.

[0072] In the embodiment of the present application, the time precision of the value of the first field and the value of the second field are both lower than the second level, such as microseconds, milliseconds, etc., which is not specifically limited in the present application. From the above embodiment, it can be seen that each operation event of the master database should be recorded in the binary log according to the corresponding format. For example, if the format of the binary log is statement replication mode, the execution statement corresponding to each operation event is recorded in the binary log. If the format of the binary log is row replication mode, the specific content of the data change caused by each operation event is recorded in the binary log.

[0073] Therefore, the master server obtains the first current system time from the second field of the heartbeat table of the master database, and writes the target update statement and the first current system time into the binary log. In the embodiment of the present application, the format of the binary log can be set to the statement replication format. When the master server executes the target update statement, the value of the first field in the heartbeat table of the master database is updated to the first current system time. At the same time, based on the target column attribute of the second field, the value of the second field is updated to the first current system time. That is to say, at this time, the target operation event for the master database includes (1) updating the value of the first field to the first current system time, and (2) updating the value of the second field to the first current system time, wherein, The execution statement corresponding to the (1)th event is a target update statement, while the (2)th event is triggered based on the target column attribute and there is no corresponding execution statement. The master server then writes the target update statement corresponding to the target operation event and the update content corresponding to the value of the second field, i.e., the first current system time, into the binary log. Accordingly, the binary log records the target update statement and the first current system time corresponding to the target operation event. It should be noted that the binary log may record the target update statement and the first current system time in a preset format (e.g., binary format) when recording the target update statement and the first current system time, and the record corresponding to the target update statement and the first current system time in the binary log may be referred to as a log event in the binary log.

[0074] Furthermore, since the binary log records the target update statement and the first current system time in a preset format, the slave server can obtain the log event corresponding to the target update statement and the first current system time from the binary log. For example, the slave server sends a read request for the binary log to the master server through the above-mentioned I / O thread, and then the master server responds to the read request and sends the corresponding log event to the slave server. At this time, the log event records the target update statement and the first current system time, and the first current system time is used to indicate the updated value of the second field; then the slave server writes the log event to the local log of the slave server, that is, the relay log, through the I / 0 thread, that is, the log event is completely copied to the relay log; then the slave server obtains the log event from the relay log through the SQL thread; then the slave server parses the log time through the SQL thread to obtain the corresponding target update statement and the value of the second field, that is, the first current system time, that is, the slave server obtains the target update statement and the first current system time.

[0075] Then, the slave server executes the target update statement through the SQL thread, updates the value of the first field in the heartbeat table of the slave database to the second current system time of the slave server, and updates the value of the second field in the heartbeat table of the slave database to the first current system time, wherein the structure of the heartbeat table of the slave database is similar to that of the heartbeat table of the master database, which will not be repeated here. It should be noted that the same heartbeat table can be created in the master database and the slave database in advance, or the heartbeat table can be created on the master database first, and then the heartbeat table of the master database is synchronized to the slave database to obtain the heartbeat table of the slave database, which is not specifically limited in this application. Since the target update statement includes a target function, the target function is used to obtain the current system time of the operating system. The slave server executes the target update statement through the SQL thread, indicating that the operating system is a slave server at this time. Then, the current system time of the slave server, i.e., the second current system time, can be obtained through the target function, and then the value of the first field in the heartbeat table of the slave database is updated to the second current system time; since the first current system time is indicated as the value of the second field in the heartbeat table, the slave server updates the value of the second field in the heartbeat table of the slave database to the first current system time. It should be explained that the second field in the heartbeat table of the slave database may also include a target column attribute. When the value of the first field in the heartbeat table of the slave database is updated to the second current system time, in principle, the value of the second field in the heartbeat table of the slave database should also be automatically updated to the current system time of the slave server, i.e., the second current system time. However, since the slave server obtains the first current system time for indicating the value of the second field from the binary log, when the slave server detects the update value corresponding to the second field of the heartbeat table of the slave database, i.e., the first current system time, the target column attribute corresponding to the second field will not be triggered, but the first current system time will be directly used as the value of the second field in the heartbeat table of the slave database. That is to say, the master server executes the target update statement to update the value of the first field in the heartbeat table of the master database, and based on the target column attribute of the second field in the heartbeat table of the master database, updates the value of the second field in the heartbeat table of the master database. Accordingly, if the slave server needs to synchronize the heartbeat table of the master database, it is necessary to re-execute or replay the corresponding log event from the slave server, that is, the slave server re-executes the target update statement to update the value of the first field in the heartbeat table of the slave database, and updates the value of the second field in the heartbeat table of the slave database to the first current system time. At this time, the heartbeat table of the slave database includes the current system time of the master server when the master server executes the update operation event on the heartbeat table of the master database, that is, the first current system time, and the current system time of the slave server when the slave server replays the update operation event to achieve synchronization of the heartbeat tables of the master database and the slave database, that is, the second current system time.

[0076] Furthermore, the slave server can directly determine the master-slave delay based on the second current system time and the first current system data. For example, the slave server can obtain the value of the first field, i.e., the second current system time, and the value of the second field, i.e., the first current system time, from the heartbeat table of the slave database through the target thread, and then determine the master-slave delay as the difference between the second current system time and the first current system data; of course, the slave server can obtain the value of the first field and the value of the second field from the heartbeat table of the slave database through the target thread every preset time period, that is, no matter how many times the heartbeat table of the master database and the heartbeat table of the slave database have been updated before, at this time, the slave server can obtain the value of the first field and the value of the second field from the heartbeat table of the database through the target thread every preset time period, and the values ​​are all the latest values, and then the slave server determines the corresponding master-slave delay based on the obtained value of the first field and the value of the second field. It should be noted that the present application does not specifically limit the preset time period.

[0077] Optionally, the slave server may also receive a query request from the monitoring service; then the slave server responds to the query request, and obtains the second current system time and the first current system time from the heartbeat table of the slave database through the target thread; then the slave server determines the master-slave delay based on the second current system time and the first current system time, for example, determines the master-slave delay as the difference between the second current system time and the first current system time; then the slave server may also send the master-slave delay to the monitoring service. That is to say, the monitoring service can also send a query request to the slave server every preset time period. The query request can be a query SQL statement, such as SELECT abs((ts_self-ts_master)*1000000)as relagFROM heartbeat_ms. Then, the slave server responds to the query request, that is, executes the query SQL statement, obtains the value of the first field (i.e., ts_self) and the value of the second field (i.e., ts_master) from the heartbeat table of the slave database, and then determines the master-slave delay from the server based on the value of ts_self and the value of ts_master, for example, the master-slave delay is abs((ts_self-ts_master)*1000000), where abs represents the absolute value function; then the slave server can send the master-slave delay to the monitoring service.

[0078] It should be explained that if only the first field or the second field is set in the master database and the slave database, for example, only the first field is set, then when the master server updates the master database, the value of the first field in the heartbeat table of the master database is correspondingly updated to the current system time of the master server, and then the master server writes the update event into the binary log, that is, at this time, the binary log includes the updated value of the first field, that is, the current system time of the master server; at this time, the slave server replays the update event of the master server, that is, the slave server updates the value of the first field in the heartbeat table of the slave database to the current system time of the master server in the binary log, and then obtains the current system time of the slave server through the first thread, and then determines the master-slave delay based on the current system time of the slave server and the updated value of the first field in the heartbeat table of the slave database. In this way, there will be a problem that after the first field in the heartbeat table of the slave database is synchronously updated, if you want to calculate the master-slave delay, you also need to use the first thread to obtain the current system time of the slave server at this time, and the speed of the first thread will affect the accuracy of the current system time of the slave server finally obtained, and thus affect the accuracy of the master-slave delay.

[0079] Therefore, in an embodiment of the present application, by setting the first field and the second field in the heartbeat tables of the master database and the slave database, the master server executes a target update statement, updates the first field in the heartbeat table of the master database to the first current system time of the master server, and in response to the target column attribute of the second field in the heartbeat table of the master data, updates the value of the second field to the first current system time; then sets the write mode of the binary log to the statement replication mode, the master server writes the target update statement to the binary log, and writes the value of the second field, i.e., the first current system time, to the binary log; then the slave server replays the update event of the master server recorded in the binary log, i.e., the slave server executes the target update statement, updates the value of the first field in the heartbeat table of the slave database to the value of the first field in the heartbeat table of the slave server The second current system time of the slave server is obtained, and the value of the second field in the heartbeat table of the slave database is updated to the first current system time. That is to say, the second current system time now represents the current system time of the slave server when replaying the update event, and the first current system time is the actual time when the update event replayed by the slave server is executed on the master server. Since the slave server assigns the second current system time of the slave server to the first field in the heartbeat table of the slave database while replaying the update event, the value of the first field, i.e. the second current system time, and the value of the second field, i.e. the first current system time, can be directly obtained from the heartbeat table of the slave database through the target thread. The obtained second current system time will not change due to the speed of the target thread, thereby improving the accuracy of the master-slave delay.

[0080] Optionally, after the monitoring service obtains the master-slave delay from the slave server, the monitoring service may also perform other subsequent processing based on the master-slave delay. For example, the monitoring service determines whether the data synchronization between the master server and the slave server is abnormal based on the master-slave delay and a preset delay threshold. For example, if the master-slave delay is greater than the preset delay threshold, then the data synchronization between the master server and the slave server is determined to be abnormal, and vice versa. Optionally, the monitoring server may also adaptively and dynamically adjust the preset delay threshold to generate a target delay threshold that matches the master-slave delay (the principle of determining the target delay threshold will be specifically explained below), and then determine whether the data synchronization between the master server and the slave server is abnormal based on the target delay threshold and the master-slave delay. The principle of determining whether it is abnormal will not be repeated here. If abnormal, the monitoring service may trigger an alarm notification to notify the administrator to check the data synchronization link between the master server and the slave server. Optionally, the monitoring service can also determine whether to trigger the fault switching mechanism based on the master-slave delay and the target delay threshold. For example, if the master-slave delay is greater than the target delay threshold, the fault switching mechanism is triggered. Accordingly, the monitoring service sends a first indication message to the slave server, and then the slave server switches to the new master server in response to the first indication message, that is, switches the slave server to the new master server to ensure the availability and continuity of the system.

[0081] Of course, optionally, after determining the master-slave delay, the slave server may also determine whether there is an abnormality in the data synchronization between the master server and the slave server based on the master-slave delay. Exemplarily, the slave server determines whether the data synchronization between the slave server and the master server is abnormal based on the master-slave delay and a preset delay threshold. For example, if the master-slave delay is greater than the preset delay threshold, the data synchronization is determined to be abnormal. If the master-slave delay is less than or equal to the preset delay threshold, the data synchronization is determined to be normal. If it is abnormal, the slave server may also send an abnormal prompt message to the monitoring service to prompt that the data synchronization between the master server and the slave server is abnormal.

[0082] Of course, in an optional embodiment, the preset delay threshold does not necessarily match the current data synchronization status of the master and slave servers. Therefore, when executing the step of determining whether the data synchronization between the slave server and the master server is abnormal based on the master-slave delay and the preset delay threshold, the preset delay threshold can also be adaptively and dynamically adjusted. Specifically:

[0083] Acquire multiple historical master-slave delays between the master server and the slave server from the server, wherein the multiple historical master-slave delays are determined at preset time periods, that is, each historical master-slave delay is determined before the above-mentioned master-slave delay, and the determination time of adjacent historical master-slave delays is a preset time period, such as every second, every minute, etc., which is not specifically limited in the present application; then acquire the load data and network data of the master server and the slave server in the preset time period corresponding to each historical master-slave delay from the server, that is, acquire the load data and network data of the master server and the slave server in the time period for determining each historical master-slave delay, wherein the load data of the master server and the slave server may include memory usage, etc., and the network data of the master server and the slave server may include network bandwidth, etc.

[0084] Then, the slave server determines the threshold change range based on multiple historical master-slave delays, the load data and network data of the master server and the slave server under each historical master-slave delay, wherein the threshold change range is in the range of [-1,1]. Exemplarily: first, the slave server determines the first threshold change range based on the load data, for example, the slave server obtains the standard memory usage corresponding to the second delay threshold; then the slave server determines the first difference between the memory usage and the standard memory usage; then the slave server determines the first threshold corresponding to the memory usage based on the first difference and the first mapping table. The first mapping table represents the mapping relationship between the memory usage rate and the threshold change range. The specific principle embodied in the first mapping table is that when the first difference is equal to zero, the first threshold change range is the first preset value (for example, zero); when the first difference is greater than zero, if the first difference is larger, the first threshold change range is larger, and at this time the value range of the first threshold change range is (0,1], and vice versa; when the first difference is less than zero, if the first difference is larger, the first threshold change range is larger, and at this time the value range of the first threshold change range is [-1,0), and vice versa.

[0085] Then, the second threshold change range is determined from the server based on the network data, for example, the standard network bandwidth corresponding to the second delay threshold is obtained from the server; then the second difference between the network bandwidth in the network data and the standard network bandwidth is determined from the server; then the second threshold change range corresponding to the network bandwidth is determined from the server based on the second difference and the second mapping table, wherein the second mapping table represents the mapping relationship between the network bandwidth and the threshold change range, and the specific principle embodied in the second mapping table is that when the second difference is equal to zero, the second threshold change range is the first preset value; when the second difference is greater than zero, if the second difference is larger, the second threshold change range is smaller, and at this time the value range of the second threshold change range is [-1,0), and vice versa; when the second difference is less than zero, if the second difference is larger, the second threshold change range is smaller, and at this time the value range of the second threshold change range is (0,1], and vice versa.

[0086] Then, the slave server determines the third threshold change amplitude based on multiple historical master-slave delays, for example, the slave server determines the delay average value based on multiple historical master-slave delays; then the slave server determines the third difference between the delay average value and the second delay threshold; then the slave server determines the third threshold change amplitude corresponding to the delay average value based on the third difference and the third mapping table, wherein the third mapping table represents the mapping relationship between the delay average value and the threshold change amplitude, and the specific principle embodied in the third mapping table is that when the third difference is equal to zero, the third threshold change amplitude is the first preset value (for example, zero); when the third difference is greater than zero, if the third difference is larger, the third threshold change amplitude is larger, and at this time the value range of the third threshold change amplitude is (0,1], and vice versa; when the third difference is less than zero, if the third difference is larger, the third threshold change amplitude is larger, and at this time the value range of the first threshold change amplitude is [-1,0), and vice versa.

[0087] Then, the server determines the above threshold change range based on the first threshold change range, the second threshold change range, and the third threshold change range. For example, the first threshold change range, the second threshold change range, and the third threshold change range can be weighted and summed according to a preset weight coefficient to obtain the final threshold change range.

[0088] Then, the slave server determines the target delay threshold based on the threshold change amplitude and the preset delay threshold. For example, the slave server obtains the threshold change amount based on the product of the threshold change amplitude and the preset delay threshold. Then, the slave server determines the target delay threshold based on the threshold change amount and the preset delay threshold, for example, the sum of the threshold change amount and the preset delay threshold. Finally, the slave server determines whether the data synchronization between the slave server and the master server is abnormal based on the master-slave delay and the target delay threshold. If abnormal, the slave server can generate an abnormal prompt message, and then the slave server can also send the abnormal prompt message to the monitoring service to prompt that the current data synchronization status between the slave server and the master server is abnormal, and then the administrator can be notified to perform maintenance.

[0089] It can be seen that in the embodiment of the present application, since the determined threshold change range is [-1,1], if the threshold change range is zero, it means that the second delay threshold does not need to be adjusted; if the threshold change range is greater than zero, it means that the second delay threshold needs to be increased; if the threshold change range is less than zero, it means that the second delay threshold needs to be reduced. Therefore, the embodiment of the present application can optimize the stability and performance of the database system by adaptively and dynamically adjusting the preset second delay threshold.

[0090] See also Figure 3 , Figure 3 A flowchart of another master-slave delay determination method provided in an embodiment of the present application. The method is applied to a slave server, and the method includes but is not limited to steps S301-S303:

[0091] S301. The slave server obtains the first current system time and the target update statement from the binary log of the master server.

[0092] In an embodiment of the present application, the target update statement and the first current system time are written to the binary log when the master server executes the target update statement on the master database, or in other words, when the value of the first field and the value of the second field of the heartbeat table of the master database are updated to the first current system time of the master server, the target update statement and the first current system time are written to the binary log. That is, the master server writes the target update statement and the first current system time to the binary log by executing the above steps S201-S203 (not repeated here); then the slave server obtains the first current system time and the target update statement from the binary log through the I / 0 thread, and writes the first current system time and the target update statement to the local log of the slave server, i.e., the relay log. The specific principle is referred to the existing description of the above embodiment, which is not repeated here.

[0093] S302: The slave server updates the value of the first field of the heartbeat table of the slave database to the second current system time of the slave server, and updates the value of the second field of the heartbeat table of the slave database to the first current system time by executing a target update statement.

[0094] In an embodiment of the present application, the time accuracy of the value of the first field and the value of the second field are both lower than the second level, such as the millisecond level, microsecond level, etc., and the present application does not make specific limitations. After the target update statement and the first current system time are written into the relay log of the slave server, the slave server can obtain the target update statement and the first current system time from the relay log through the SQL thread. The specific principle refers to the existing explanation of the above embodiment, which will not be repeated here. Then the slave server executes the target update statement through the SQL thread, and updates the value of the first field in the heartbeat table of the slave database to the current system time of the slave server, that is, the second current system time. Since the target update statement includes a target function, the target function is used to obtain the current system time of the operating system. The slave server executes the target update statement through the SQL thread, which indicates that the operating system is the slave server at this time. Then, the current system time of the slave server, that is, the second current system time, can be obtained through the target function, and then the value of the first field in the heartbeat table of the slave database is updated to the second current system time. At the same time, since the first current system time is indicated as the value of the second field in the heartbeat table, the slave server updates the value of the second field in the heartbeat table of the slave database to the first current system time. The explanation principle of updating the value of the second field in the heartbeat table of the database to the first current system time here can also refer to the above existing explanation, which will not be repeated here. In addition, it is noted that the structure of the heartbeat table of the slave database is similar to that of the master database, which will not be repeated here. The same heartbeat table can be created in the master database and the slave database in advance, or the heartbeat table can be created in the master database first, and then the heartbeat table of the master database can be synchronized to the slave database to obtain the heartbeat table of the slave database. This application does not make specific limitations.

[0095] S303. The slave server determines a master-slave delay based on the second current system time and the first current system time.

[0096] In an embodiment of the present application, the slave server can directly obtain the value of the first field, which is the second current system time, and the value of the second field, which is the first current system time, from the heartbeat table of the slave database, and then obtain the above-mentioned master-slave delay based on the difference between the second current system time and the first current system time from the slave server; further, the slave server can also determine whether there is an abnormality in the data synchronization between the master server and the slave server based on the master-slave delay, which will not be repeated here.

[0097] Optionally, when executing step S303, the slave server may also receive a query request from the monitoring service; then the slave server responds to the query request, obtains the second current system time and the first current system time from the heartbeat table of the slave database through the target thread; then the slave server determines the master-slave delay based on the second current system time and the first current system time, which is not repeated here; then the slave server may also send the master-slave delay to the monitoring service, and further, after obtaining the master-slave delay, the monitoring service may also perform other subsequent processing based on the master-slave delay, which is not repeated here.

[0098] It should be noted that after determining the master-slave delay between the master server and the slave server, both the slave server and the monitoring service can be used for other purposes based on the master-slave delay, which will not be listed one by one in this application.

[0099] For easier understanding, see Figure 4 , Figure 4 A schematic diagram of master-slave delay determination provided in an embodiment of the present application.

[0100] like Figure 4As shown, it is assumed that before the master server executes the target update statement, that is, before the update, the value of the first field (i.e., ts_self) and the value of the second field (i.e., ts_master) in the heartbeat table in the master database of the master server are both A1, and the value of the first field and the value of the second field in the heartbeat table in the slave database of the slave server have been synchronized with the master database, that is, the value of the first field (i.e., ts_self) and the value of the second field (i.e., ts_master) in the heartbeat table of the slave database are also both A1. Of course, the value of the first field and the value of the second field in the heartbeat table of the master database and the slave database may also be different, which is not specifically limited in this application; then the master server executes the target update statement, and updates the value of the first field and the value of the second field in the heartbeat table of the master database to the current system time of the master server, i.e., the first current system time (i.e., A2); then the master server writes the target update statement and A2 into the binary log; then the slave server reads the binary log from the Binary log through the I / O thread log, and write the target update statement and A2 into the relay log Relaylog; then the slave server obtains the target update statement and A2 through the SQL thread; then the slave server executes the target update statement through the SQL thread, updates the value of the first field in the heartbeat table of the slave database to the current system time of the slave server, that is, the second current system time (that is, A3), and updates the value of the second field in the heartbeat table of the slave data to A2; further, the slave server can also receive a query request from the monitoring service; then the slave server responds to the query request, and obtains A2 and A3 from the updated heartbeat table of the slave database through the target thread; then the slave server determines the master-slave delay based on A2 and A3; then the slave server sends the master-slave delay to the monitoring service.

[0101] See also Figure 5 , Figure 5 A schematic diagram of a master-slave delay determination system is provided for an embodiment of the present application.

[0102] Figure 5 The master-slave delay determination system shown includes a master server and a slave server; the master server and the slave server can be independent physical servers, or a server cluster or distributed system composed of multiple physical servers, or a cloud server that provides cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, as well as basic cloud computing services such as big data and artificial intelligence platforms, which are not specifically limited in this application; in addition, the number of master servers and slave servers can be one or more, and the embodiments of this application are mainly illustrated by taking one as an example.

[0103] First, the master server obtains a target update statement for the master database from the client; then the master server updates the value of the second field of the heartbeat table of the master database to the first current system time of the master server by executing the target update statement; then the master server obtains the first current system time from the second field of the heartbeat table of the master database to write the target update statement and the first current system time into the binary log; then the slave server obtains the first current system time and the target update statement from the binary log of the master server; then the slave server updates the value of the first field of the heartbeat table of the slave database to the second current system time of the slave server by executing the target update statement, and updates the value of the second field of the heartbeat table of the slave database to the first current system time; then the slave server determines the master-slave delay based on the second current system time and the first current system time. It should be noted that the principles of the steps executed by the master server and the slave server in this embodiment can correspond to the relevant description of the above embodiment, and will not be repeated here.

[0104] See also Figure 6 , Figure 6 A schematic diagram of another master-slave delay determination system provided in an embodiment of the present application.

[0105] Figure 6 The master-slave delay determination system shown includes a master server, a slave server, and a monitoring service; the monitoring service can be used to monitor the delay between the master server and the slave server; the master server, the slave server, and the monitoring service can all be independent physical servers, or they can be a server cluster or distributed system composed of multiple physical servers, or they can be cloud servers that provide cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, as well as basic cloud computing services such as big data and artificial intelligence platforms, which are not specifically limited in this application; of course, the monitoring service can also be a client, such as a smart phone, tablet computer, laptop computer, desktop computer, smart TV, desktop computer, etc., which is not specifically limited in this application; in addition, the number of master servers and slave servers can be one or more, and the embodiments of this application are mainly illustrated by taking one as an example.

[0106] based on Figure 5In the embodiment, in terms of determining the master-slave delay based on the second current system time and the first current system time from the server, after the slave server obtains the master-slave delay based on the difference between the second current system time and the first current system time, the slave server sends the master-slave delay to the monitoring service; or optionally, the slave server receives a query request from the monitoring service; then the slave server responds to the query request and obtains the second current system time and the first current system time from the heartbeat table of the slave database through the target thread; then the slave server determines the master-slave delay based on the second current system time and the first current system time; then the slave server sends the master-slave delay to the monitoring service. It should be noted that the principles of the steps performed by the master server, the slave server, and the monitoring service in this embodiment can correspond to the relevant explanations of the above-mentioned embodiment, and will not be repeated here.

[0107] For further understanding, some application scenarios involved in the embodiments of the present application are introduced below.

[0108] First, for multi-active scenarios, computer rooms are deployed locally and remotely respectively. At this time, the delay of the local computer room and the remote computer room is determined based on the master-slave delay determination method of the embodiment of the present application; if the business side can accept the delay, choose to write data in the local computer room and read data in the remote computer room, that is, choose the "cross-region write, local area read" strategy to achieve data synchronization; if the business side does not accept the delay, choose to write data in the local computer room and read data in the local computer room, that is, choose the "cross-region write, cross-region read" strategy to achieve data synchronization.

[0109] Second, in order to speed up the data synchronization between regions, corresponding dedicated lines are generally used. For example, the data synchronization between the computer room at site A and the computer room at site B uses dedicated line C, and the delay for data synchronization using dedicated line C is a preset value. At this time, the delay between the computer room at site A and the computer room at site B can be determined based on the master-slave delay determination method of the embodiment of the present application. Based on the delay and the preset value, it can be determined whether there is an abnormality in dedicated line C.

[0110] It can be seen that in an embodiment of the present application, after the master server obtains the target update statement for the master database, the master server executes the target update statement on the master database, and updates the value of the first field and the value of the second field in the heartbeat table of the master database to the first current system time of the master server, that is, the first current system time is the current system time of the master server when the master server executes the target update statement to update the master database; then the master server writes the target update statement and the first current system time to the binary log; then the slave server obtains the first current system time and the target update statement from the binary log of the master server; then the slave server updates the value of the first field of the heartbeat table of the slave database to the second current system time of the slave server by executing the target update statement. And the value of the second field of the heartbeat table of the slave database is updated to the first current system time, wherein the time precision of the value of the first field and the value of the second field are both lower than the second level, that is, the second current system time can be understood as the target event recorded by the slave server in the playback binary log, that is, the current system time of the slave server when executing the target update statement, and the first current system time is the current system time corresponding to the target event in the binary log, that is, the current system time of the master server when executing the target time, and the precision of the first current system time and the second current system time are both lower than the second level, then finally based on the second current system time and the first current system time, the time precision of determining the master-slave delay can also be lower than the second level, such as millisecond level, microsecond level, etc., thereby improving the accuracy of the master-slave delay.

[0111] See also Figure 7 , Figure 7 A block diagram of the functional units of a slave server provided in an embodiment of the present application.

[0112] The slave server 700 includes: a first acquisition unit 701 and a first processing unit 702;

[0113] The first acquisition unit 701 is used to acquire a first current system time and a target update statement from a binary log of the primary server, wherein the target update statement and the first current system time are written into the binary log by the primary server when executing the target update statement on the primary database;

[0114] The first processing unit 702 is used to update the value of the first field of the heartbeat table of the slave database to the second current system time of the slave server, and to update the value of the second field of the heartbeat table of the slave database to the first current system time by executing the target update statement, wherein the time precision of the value of the first field and the value of the second field are both lower than the second level;

[0115] The first processing unit 702 is further configured to determine a master-slave delay based on the second current system time and the first current system time.

[0116] In one embodiment of the present application, in terms of determining the master-slave delay based on the second current system time and the first current system time, the first acquisition unit is configured to receive a query request from a monitoring service;

[0117] The first processing unit 702 is specifically configured to:

[0118] In response to the query request, obtaining, through the target thread, a second current system time and a first current system time from a heartbeat table of a slave database;

[0119] A master-slave delay is determined based on the second current system time and the first current system time.

[0120] In one embodiment of the present application, after determining the master-slave delay, the first processing unit 702 is specifically configured to:

[0121] Based on the master-slave delay and the preset delay threshold, determine whether the data synchronization between the slave server and the master server is abnormal;

[0122] If there is an abnormality, an abnormal prompt message is sent to the monitoring service to prompt the abnormality of data synchronization between the slave server and the master server.

[0123] In a specific implementation, the first acquisition unit 701 and the first processing unit 702 described in the embodiment of the present invention may execute other implementations described in the embodiment of the master-slave delay determination method provided in the embodiment of the present invention, which will not be described in detail here.

[0124] See also Figure 8 , Figure 8 A block diagram of the functional units of a main server provided in an embodiment of the present application.

[0125] The main server 800 includes: a second acquisition unit 801 and a second processing unit 802;

[0126] The second acquisition unit 801 is used to acquire a target update statement for the primary database;

[0127] The second processing unit 802 is used to update the value of the second field of the heartbeat table of the primary database to the first current system time of the primary server by executing the target update statement;

[0128] The second processing unit 802 is further used to obtain the first current system time from the second field of the heartbeat table of the master database, so as to write the target update statement and the first current system time into the binary log, so that the slave server updates the value of the first field of the heartbeat table of the slave database to the second current system time of the slave server by executing the target update statement in the binary log, and updates the value of the second field of the heartbeat table of the slave database to the first current system time, so that the slave server determines the master-slave delay based on the second current system time and the first current system time, wherein the time accuracy of the value of the first field and the value of the second field are both lower than the second level.

[0129] In one embodiment of the present application, before updating the value of the second field of the heartbeat table of the primary database to the first current system time of the primary server by executing the target update statement, the second processing unit 802 is specifically configured to:

[0130] Configuring a target column attribute for the second field in the heartbeat table of the primary database, wherein the target column attribute is used to indicate that when an update operation is performed on a field other than the second field in the heartbeat table of the primary database, the value of the second field in the heartbeat table of the primary database is updated to the current system time of the primary server;

[0131] In terms of updating the value of the second field of the heartbeat table of the primary database to the first current system time of the primary server by executing the target update statement, the second processing unit 802 is specifically configured to:

[0132] When the value of the first field of the heartbeat table of the primary database is updated to the first current system time by executing the target update statement, the value of the second field of the heartbeat table of the primary database is updated to the first current system time of the primary server based on the target column attribute.

[0133] In one embodiment of the present application, before updating the value of the second field of the heartbeat table of the primary database to the first current system time of the primary server by executing the target update statement, the second processing unit 802 is specifically configured to:

[0134] Set the binary log format to statement replication mode so that the master server writes the target update statement to the binary log after executing the target update statement.

[0135] In a specific implementation, the second acquisition unit 801 and the second processing unit 802 described in the embodiment of the present invention may execute other implementations described in the embodiment of the master-slave delay determination method provided in the embodiment of the present invention, which will not be described in detail here.

[0136] See also Fig. 9 , Fig. 9 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application.

[0137] like Fig. 9 As shown, the electronic device 900 includes a transceiver 901, a processor 902 and a memory 903. They are connected via a bus 904. The memory 903 is used to store computer programs and data, and can transmit the data stored in the memory 903 to the processor 902.

[0138] Wherein, the electronic device may be a slave server or a master server;

[0139] When the electronic device is a slave server, the processor 902 is used to read the computer program in the memory 903 to perform the following operations:

[0140] The control transceiver 901 obtains the first current system time and the target update statement from the binary log of the master server, wherein the target update statement and the first current system time are written into the binary log by the master server when executing the target update statement on the master database;

[0141] By executing the target update statement, the value of the first field of the heartbeat table of the slave database is updated to the second current system time of the slave server, and the value of the second field of the heartbeat table of the slave database is updated to the first current system time, wherein the time precision of the value of the first field and the value of the second field are both lower than the second level;

[0142] A master-slave delay is determined based on the second current system time and the first current system time.

[0143] In one embodiment of the present application, in determining the master-slave delay based on the second current system time and the first current system time, the processor 902 is specifically configured to perform the following steps:

[0144] Control transceiver 901 to receive a query request from a monitoring service;

[0145] In response to the query request, obtaining, through the target thread, a second current system time and a first current system time from a heartbeat table of a slave database;

[0146] A master-slave delay is determined based on the second current system time and the first current system time.

[0147] In one embodiment of the present application, after determining the master-slave delay, the processor 902 is specifically configured to perform the following steps:

[0148] Based on the master-slave delay and the preset delay threshold, determine whether the data synchronization between the slave server and the master server is abnormal;

[0149] If there is an abnormality, an abnormal prompt message is sent to the monitoring service to prompt the abnormality of data synchronization between the slave server and the master server.

[0150] When the electronic device is a main server, the processor 902 is used to read the computer program in the memory 903 to perform the following operations:

[0151] Control transceiver 901 to obtain a target update statement for the main database;

[0152] By executing the target update statement, the value of the second field of the heartbeat table of the primary database is updated to the first current system time of the primary server;

[0153] The first current system time is obtained from the second field of the heartbeat table of the master database to write the target update statement and the first current system time into the binary log, so that the slave server updates the value of the first field of the heartbeat table of the slave database to the second current system time of the slave server by executing the target update statement in the binary log, and updates the value of the second field of the heartbeat table of the slave database to the first current system time, so that the slave server determines the master-slave delay based on the second current system time and the first current system time, wherein the time accuracy of the value of the first field and the value of the second field are both lower than the second level.

[0154] In one embodiment of the present application, before updating the value of the second field of the heartbeat table of the primary database to the first current system time of the primary server by executing the target update statement, the processor 902 is specifically configured to perform the following steps:

[0155] Configuring a target column attribute for the second field in the heartbeat table of the primary database, wherein the target column attribute is used to indicate that when an update operation is performed on a field other than the second field in the heartbeat table of the primary database, the value of the second field in the heartbeat table of the primary database is updated to the current system time of the primary server;

[0156] In one embodiment of the present application, in updating the value of the second field of the heartbeat table of the primary database to the first current system time of the primary server by executing the target update statement, the processor 902 is specifically configured to perform the following steps:

[0157] When the value of the first field of the heartbeat table of the primary database is updated to the first current system time by executing the target update statement, the value of the second field of the heartbeat table of the primary database is updated to the first current system time of the primary server based on the target column attribute.

[0158] In one embodiment of the present application, before updating the value of the second field of the heartbeat table of the primary database to the first current system time of the primary server by executing the target update statement, the processor 902 is specifically configured to perform the following steps:

[0159] Set the binary log format to statement replication mode so that the master server writes the target update statement to the binary log after executing the target update statement.

[0160] Specifically, the transceiver 901 may be Figure 7 The first acquisition unit 701 or the first acquisition unit 701 of the server 700 of the embodiment Figure 8 The second acquisition unit 801 of the main server 800 of the embodiment, the processor 902 may be Figure 7 The first processing unit 702 of the server 700 or Figure 8 The second processing unit 802 of the main server 800 of the embodiment.

[0161] In a specific implementation, the transceiver 901 and the processor 902 described in the embodiment of the present invention may also execute other implementations described in the embodiment of the master-slave delay determination method provided in the embodiment of the present invention, which will not be described in detail here.

[0162] It should be understood that the electronic device in this application can be a master server or a slave server. The master server or the slave server can be, for example, an independent physical server, or a server cluster or distributed system composed of multiple physical servers, or a cloud server that provides cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, and basic cloud computing services such as big data and artificial intelligence platforms. This application does not make specific limitations. The above electronic devices are only examples, not exhaustive, and include but are not limited to the above electronic devices.

[0163] It should be understood that an embodiment of the present application also provides a computer-readable storage medium, which stores a computer program, and the computer program is executed by a processor to implement part or all of the steps of any master-slave delay determination method recorded in the above method embodiments.

[0164] An embodiment of the present application also provides a computer program product, which includes a non-transitory computer-readable storage medium storing a computer program, and the computer program is operable to cause a computer to execute part or all of the steps of any master-slave delay determination method recorded in the above method embodiments.

[0165] It should be noted that, for the aforementioned method embodiments, for the sake of simplicity, they are all expressed as a series of action combinations, but those skilled in the art should be aware that the present application is not limited by the described order of actions, because according to the present application, certain steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also be aware that the embodiments described in the specification are all optional embodiments, and the actions and modules involved are not necessarily required by the present application.

[0166] In the above embodiments, the description of each embodiment has its own emphasis. For parts that are not described in detail in a certain embodiment, reference can be made to the relevant descriptions of other embodiments.

[0167] In the several embodiments provided in the present application, it should be understood that the disclosed devices can be implemented in other ways. For example, the device embodiments described above are only schematic, such as the division of units, which is only a logical function division. There may be other division methods in actual implementation, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, and the indirect coupling or communication connection of devices or units can be electrical or other forms.

[0168] The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0169] In addition, each functional unit in each embodiment of the present application may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above integrated unit may be implemented in the form of hardware or in the form of a software program module.

[0170] If the integrated unit is implemented in the form of a software program module and sold or used as an independent product, it can be stored in a computer-readable memory. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art, or all or part of the technical solution can be embodied in the form of a software product, which is stored in a memory and includes several instructions for a computer device (which can be a personal computer, server or network device, etc.) to perform all or part of the steps of each embodiment method of the present application. The aforementioned memory includes: U disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), mobile hard disk, disk or optical disk and other media that can store program codes.

[0171] A person skilled in the art can understand that all or part of the steps in the various methods of the above embodiments can be completed by instructing related hardware through a program, and the program can be stored in a computer-readable memory, and the memory can include: a flash drive, a read-only memory (English: Read-Only Memory, abbreviated as: ROM), a random access memory (English: Random Access Memory, abbreviated as: RAM), a magnetic disk or an optical disk, etc.

[0172] The embodiments of the present application are introduced in detail above. Specific examples are used in this article to illustrate the principles and implementation methods of the present application. The description of the above embodiments is only used to help understand the method of the present application and its core idea. At the same time, for those skilled in the art, according to the idea of ​​the present application, there will be changes in the specific implementation methods and application scope. In summary, the content of this specification should not be understood as a limitation on the present application.

Claims

1. A method for determining master-slave delay, characterized in that: Applied to a slave server, the method comprises: Acquire a first current system time and a target update statement from a binary log of the primary server, wherein the target update statement and the first current system time are written into the binary log by the primary server when the primary server executes the target update statement on the primary database; By executing the target update statement, the value of the first field of the heartbeat table of the slave database is updated to the second current system time of the slave server, and the value of the second field of the heartbeat table of the slave database is updated to the first current system time, wherein the time precision of the value of the first field and the value of the second field are both lower than the second level; The master-slave delay is determined based on the second current system time and the first current system time.

2. The method according to claim 1, characterized in that: The determining the master-slave delay based on the second current system time and the first current system time includes: receiving a query request from a monitoring service; In response to the query request, acquiring the second current system time and the first current system time from the heartbeat table of the slave database through the target thread; The master-slave delay is determined based on the second current system time and the first current system time.

3. The method according to claim 1 or 2, characterized in that: After determining the master-slave delay, the method further includes: Based on the master-slave delay and a preset delay threshold, determining whether data synchronization between the slave server and the master server is abnormal; If there is an abnormality, an abnormal prompt message is sent to the monitoring service to prompt that the data synchronization between the slave server and the master server is abnormal.

4. A method for determining master-slave delay, characterized in that: Applied to the main server, the method comprises: Get the target update statement for the primary database; By executing the target update statement, the value of the second field of the heartbeat table of the primary database is updated to the first current system time of the primary server; The first current system time is obtained from the second field of the heartbeat table of the master database, so as to write the target update statement and the first current system time into a binary log, so that the slave server updates the value of the first field of the heartbeat table of the slave database to the second current system time of the slave server by executing the target update statement in the binary log, and updates the value of the second field of the heartbeat table of the slave database to the first current system time, so that the slave server determines the master-slave delay based on the second current system time and the first current system time, wherein the time precision of the value of the first field and the value of the second field are both lower than the second level.

5. The method according to claim 4, characterized in that Before updating the value of the second field of the heartbeat table of the primary database to the first current system time of the primary server by executing the target update statement, the method further includes: Configuring a target column attribute for the second field in the heartbeat table of the master database, wherein the target column attribute is used to indicate that when an update operation is performed on a field other than the second field in the heartbeat table of the master database, the value of the second field in the heartbeat table of the master database is updated to the current system time of the master server; The updating of the value of the second field of the heartbeat table of the primary database to the first current system time of the primary server by executing the target update statement includes: When the value of the first field of the heartbeat table of the primary database is updated to the first current system time by executing the target update statement, the value of the second field of the heartbeat table of the primary database is updated to the first current system time based on the target column attribute.

6. The method according to claim 4 or 5, characterized in that: Before updating the value of the second field of the heartbeat table of the primary database to the first current system time of the primary server by executing the target update statement, the method further includes: The format of the binary log is set to a statement replication mode, so that the master server writes the target update statement into the binary log after executing the target update statement.

7. A slave server, characterized in that: include: a first acquisition unit and a first processing unit; The first acquisition unit is used to acquire a first current system time and a target update statement from a binary log of the master server, wherein the target update statement and the first current system time are written into the binary log by the master server when executing the target update statement on the master database; The first processing unit is configured to update the value of the first field of the heartbeat table of the slave database to the second current system time of the slave server, and update the value of the second field of the heartbeat table of the slave database to the first current system time by executing the target update statement, wherein the time precision of the value of the first field and the value of the second field are both lower than the second level; The first processing unit is further configured to determine the master-slave delay based on the second current system time and the first current system time.

8. A main server, characterized in that: include: a second acquisition unit and a second processing unit; The second acquisition unit is used to acquire a target update statement for the primary database; The second processing unit is used to update the value of the second field of the heartbeat table of the primary database to the first current system time of the primary server by executing the target update statement; The second processing unit is further used to obtain the first current system time from the second field of the heartbeat table of the master database, so as to write the target update statement and the first current system time into a binary log, so that the slave server updates the value of the first field of the heartbeat table of the slave database to the second current system time of the slave server by executing the target update statement in the binary log, and updates the value of the second field of the heartbeat table of the slave database to the first current system time, so that the slave server determines the master-slave delay based on the second current system time and the first current system time, wherein the time accuracy of the value of the first field and the value of the second field are both lower than the second level.

9. An electronic device, characterized in that: include: A processor and a memory, the processor is connected to the memory, the memory is used to store a computer program, and the processor is used to execute the computer program stored in the memory, so that the electronic device executes the method as described in any one of claims 1 to 6.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and the computer program is executed by a processor to implement the method according to any one of claims 1 to 6.