A method for quickly locating master-slave delay

Abstract

The present invention discloses a method for quickly locating master-slave delay. By running a script, the script will query the file directory and point executed by the slave library SQL thread in real time. If the point has not changed for many times, the recorded directory position and point are the positions of the operations that may cause the master-slave delay. By finding the relevant position of the log file and parsing the executed operation, the specific cause of the problem is found. The method of the present invention can accurately record the file directory and point when the master-slave delay occurs, and can realize the operation of quickly and accurately locating the problem, which is convenient for subsequent SQL optimization, etc., and the operation is simple and convenient.

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and in particular to a method for quickly locating master-slave delay. Background Art

[0002] The master-slave synchronization of MySQL database is that the master database writes the write operations sequentially into the binlog, and the slave IO thread of the slave database reads the binlog generated by the master database, and then executes it locally with the slave SQL thread to ensure the logical consistency of the master and slave data. The reason for the delay may be that the TPS concurrency of the master database is high, and the large number of DDL operations generated exceeds the range that the slave database thread can bear. It may also be caused by transactions, locks, time-consuming DDL, etc., and network transmission delays may also cause master-slave delays.

[0003] The current database monitoring platform can generally monitor the parameter value of the time that the database slave database lags behind the master database, record the time when the delay occurs and how long the delay lasts, but cannot directly obtain the cause of the problem.

[0004] To find out the specific reasons for the master-slave delay caused by transactions, locks, time-consuming DDL, etc., the current method is to find the specific location in the archive log of the database through the monitored delay information, find out the specific operations being performed at that location, and finally perform subsequent optimization; but even if the operation at the relevant location of the log file can be found through the time when the problem occurred recorded by the monitoring platform, it will take a lot of time due to the large number of log file records. At the same time, there may be a delay in the time when the monitoring platform records the occurrence of the problem, and there is also the possibility that the problem cannot be accurately located. Summary of the invention

[0005] In order to accurately and quickly locate the cause of the delay, it is critical to query and record the specific location of the problem in real time. When a master-slave delay operation caused by a transaction, lock, time-consuming DDL, etc. occurs, the file directory and point executed by the slave SQL thread will remain unchanged for a period of time. The present invention runs a script, and the script will query the file directory and point executed by the slave SQL thread in real time. If the point has not changed for many times, the recorded directory location and point are the locations of the operation that may cause the master-slave delay. By searching the relevant locations of the log file and analyzing the executed operations, the specific cause of the problem can be found.

[0006] The present invention achieves the above-mentioned purpose through the following technical solutions:

[0007] A method for quickly locating master-slave delay includes the following steps:

[0008] Step 1: Query the master-slave status and obtain the file directory and location executed by the slave SQL thread;

[0009] Step 2: Continuously query the file directory and points where the SQL thread is executed;

[0010] Step 3, determine whether it is necessary to record the queried file directory and location;

[0011] Step 4: Parse the database archive binary log (i.e. binlog file) and determine the specific location.

[0012] A further solution is that in step 1, the slave database of the database records the status information between the master and the slave, including the name of the binary log file of the master server being read by the current I / O thread, the position of the binary log being read by the current I / O thread, the file name of the relay log being read and executed by the current slave SQL thread, the position of the relay log file being read and executed by the current slave SQL thread, the most recent events in the relay log file read and executed by the slave SQL thread, the current execution position of the slave SQL thread, and other parameters. When a master-slave delay occurs, the file directory and point executed by the SQL thread are determined by finding the two parameters of the most recent events in the relay log file read and executed by the slave SQL thread and the current execution position of the slave SQL thread.

[0013] A further solution is that in step 2, once a master-slave delay caused by transactions, locks, time-consuming DDL, etc. occurs, the file directory and points in the database slave status information will stop updating and remain unchanged; by writing the query of the slave status information into a script, the slave status information is queried continuously and the query results are recorded.

[0014] A further solution is that in step 3, the degree of acceptance of master-slave delay is related to the actual project situation. A threshold representing the degree of acceptance of master-slave delay is set. The threshold is the number of times the above-mentioned file directory and point remain unchanged. If the number of times remains unchanged is greater than the threshold, it means that a certain delay has occurred in the master-slave relationship. The query results are recorded in a relevant text file for later use by database administrators.

[0015] A further solution is that in step 4, according to the specific location of the text file record, the archived binary log (ie, binlog file) of the database is extracted to form a readable database operation log, which is the specific operation that causes the master-slave delay.

[0016] The beneficial effects of the present invention are:

[0017] A method for quickly locating master-slave delay of the present invention, when the operation of master-slave delay caused by transaction, lock, time-consuming DDL, etc. occurs, the file directory and point executed by the slave library SQL thread will remain unchanged for a period of time, so the present invention runs the script, and the script will query the file directory and point executed by the slave library SQL thread in real time, and judge that if the multiple points have not changed, the recorded directory position and point are the positions of the operations that may cause master-slave delay, and the specific cause of the problem is found by searching the relevant positions of the log file and parsing the executed operations. The method of the present invention can accurately record the file directory and point when the master-slave delay occurs, and can realize fast and accurate positioning of the problem operation, facilitate subsequent SQL optimization, etc., and the operation is simple and convenient. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0019] Figure 1 It is a logic flow chart of the script of the present invention. DETAILED DESCRIPTION

[0020] To make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be described in detail below. Obviously, the described embodiments are only part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments of the present invention, all other implementation methods obtained by ordinary technicians in this field without creative work belong to the scope of protection of the present invention.

[0021] In any embodiment, if Figure 1 As shown, a method for quickly locating master-slave delay of the present invention includes:

[0022] Step 1: Query the master-slave status and obtain the file directory and location executed by the slave SQL thread;

[0023] The slave database of the database will record the status information between the master and the slave, including the name of the binary log file of the master server that the current I / O thread is reading, the position of the binary log that the current I / O thread is reading, the file name of the relay log that the current slaveSQL thread is reading and executing, the position of the relay log file that the current slave SQL thread is reading and executing, the most recent events in the relay log file that the slave SQL thread is reading and executing, the current position of the slave SQL thread, and other parameters. When a master-slave delay occurs, the file directory and point executed by the SQL thread are determined by finding the most recent events in the relay log file that the slaveSQL thread is reading and executing and the current position of the slave SQL thread.

[0024] Step 2: Continuously query the file directory and points where the SQL thread is executed;

[0025] Once a master-slave delay occurs due to transactions, locks, time-consuming DDL, etc., the file directory and points in the database slave status information will stop updating and remain unchanged. Therefore, by writing the query of the slave status information into a script, the slave status information is queried continuously and the query results are recorded.

[0026] Step 3: Determine whether the file directory and location to be queried need to be recorded;

[0027] The degree of acceptance of master-slave delays is related to the actual project situation. Therefore, it is necessary to set a threshold representing the acceptance of master-slave delays. The threshold is the number of times the above-mentioned file directory and point remain unchanged. If it remains unchanged for more than the threshold number of times, it means that there is a certain delay between the master and the slave. The query results can be recorded in relevant text files for later use by database administrators.

[0028] Step 4: Parse the database archive binary log (i.e. binlog file) and determine the specific location;

[0029] According to the specific location recorded in the above text file, the archived binary log (ie binlog file) of the database is extracted to form a readable database operation log, which is the specific operation that causes the master-slave delay.

[0030] In a specific embodiment, Figure 1 As shown, a method for quickly locating master-slave delay of the present invention comprises the following steps:

[0031] Step 1: Build the implementation environment and initialize the test table;

[0032] In the present invention, the version of MySQL master and slave databases is 5.7.23, the system environment is centos7, and the running version of the python program is 3.6.7; MySQL needs to open binlog and set binlog_format to row mode; create a test table t_test on the MySQL master database, and then insert 3 million data into the test table, waiting for the MySQL slave database to complete the synchronization of the database from the master database.

[0033] Step 2: Prepare scripts to execute large transactions or time-consuming DDL statements in the master database;

[0034] Select to use the SQL statement to delete 10,000 data items and the SQL statement to update 100,000 data items into a transaction script; this transaction will be executed in the MySQL master database later.

[0035] Step 3: Prepare a query script to continuously query the file directory and points executed by the SQL thread to determine whether records are needed. If records are needed, they are saved in the text. See the logical flow chart of the script for details. Figure 1 .

[0036] The main contents of the script include:

[0037] 1. Set a threshold n = 100, which represents the acceptable master-slave delay;

[0038] 2. Set the query time interval t, which will be used to query the master-slave status of the slave library. It can be adjusted according to the actual situation. The present invention uses t = 0.1s;

[0039] 3. Set a counter c with an initial value of 0. The value of the counter will be compared with the threshold to determine whether to record the file directory and point under the current query;

[0040] 4. Execute show slave status\G in the MySQL slave database to query all master-slave status information, then filter the results and save the most recent events in the relay log file read and executed by the SQL thread and the position currently executed by the SQL thread in a temporary file;

[0041] 5. After waiting for the query time interval t, determine whether the counter c is greater than the threshold 100. If the value of counter c is not greater than the threshold 100, continue to loop and execute show slave status\G, record the most recent events in the relay log file read and executed by the SQL thread, and the position parameter results currently executed by the SQL thread. Compare this result with the saved result. If the value of counter c is greater than the threshold 100, execute step 8;

[0042] 6. If the SQL thread's current execution position changes, the result of the previous query is discarded, the result of this query is retained, the value of the counter c is set to 0, and the process returns to step 5;

[0043] 7. If the SQL thread reads and executes the relay log file, and the SQL thread's current execution position has not changed, then the counter c value is increased by 1, and the process returns to step 5.

[0044] 8. Save the most recent events in the relay log file read and executed by the SQL thread of this query and the two parameter results of the position currently executed by the SQL thread in a text file.

[0045] Step 4: Run the script and record the results;

[0046] Run the query script in step 3 on the MySQL slave server, and then execute the transaction script in step 2 in the database of the MySQL master server.

[0047] Step 5: Parse the MySQL binlog file and compare the parsed operation records to see if they are the transaction operations in step 2.

[0048] In the present invention, the native mysqlbinlog client tool is used to parse the MySQL binlog file into a readable SQL text according to the file directory and point position recorded in the text file. By comparing this SQL text with the text of step 2, it can be found that they are the same operation.

[0049] The above is only a specific embodiment of the present invention, but the protection scope of the present invention is not limited thereto. Any technician familiar with the technical field can easily think of changes or replacements within the technical scope disclosed by the present invention, which should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention should be based on the protection scope of the claims. It should also be noted that the various specific technical features described in the above specific embodiments can be combined in any suitable manner without contradiction. In order to avoid unnecessary repetition, the present invention will not further explain various possible combinations. In addition, the various different embodiments of the present invention can also be combined arbitrarily, as long as they do not violate the concept of the present invention, they should also be regarded as the contents disclosed by the present invention.

Claims

1. A method for quickly locating master-slave delay, characterized in that: The following steps are involved: Step 1: Query the master-slave status and obtain the file directory and location executed by the slave SQL thread; Step 2: Continuously query the file directory and location where the SQL thread is executed; Step 3, determine whether it is necessary to record the queried file directory and location; in step 3, the acceptance degree of the master-slave delay is related to the actual project situation, and a threshold representing the master-slave delay acceptance is set, which is the number of times the above-mentioned file directory and location remain unchanged. If it remains unchanged for more than the threshold number of times, it means that a certain delay has occurred in the master-slave, and the query results are recorded in a related text file for later use by database managers; Step 4, parse the database archive binary log and determine the specific location; in step 4, according to the specific location recorded in the above text file, extract the database archive binary log to form a readable database operation log, which is the specific operation that causes the master-slave delay.

2. A method for quickly locating master-slave delay as claimed in claim 1, characterized in that: In the step 1, the slave database of the database records the status information between the master and the slave, including the name of the binary log file of the master server being read by the current I / O thread, the position of the binary log being read by the current I / O thread, the file name of the relay log being read and executed by the current slave SQL thread, the position in the relay log file being read and executed by the current slave SQL thread, the most recent events in the relay log file read and executed by the slave SQL thread, and the position parameter currently executed by the slave SQL thread; when a master-slave delay occurs, the file directory and point executed by the SQL thread are determined by finding the two parameters of the most recent events in the relay log file read and executed by the slave SQL thread and the position currently executed by the slave SQL thread.

3. A method for quickly locating master-slave delay as claimed in claim 1, characterized in that: In step 2, once a master-slave delay caused by a transaction, lock, and / or time-consuming DDL occurs, the file directory and point in the database slave status information will stop updating and remain unchanged; By writing the query of the slave database status information into a script, the slave database status information is queried continuously and the query results are recorded.

Images