A method for collecting point position mapping processing when a MYSQL slave library switches with each other

Abstract

The application belongs to the real-time data collection technology in the big data field, and discloses a point position mapping processing method when MYSQL slave databases are switched with each other. The method abstracts manual experience into algorithm analysis, searches from the latest content of the latest BINLOG file by sorting the slave BINLOG file and inversely analyzing the BINLOG content, queries the corresponding collection point position, so as to realize the algorithm with the least BINLOG file searching and the least BINLOG log analysis, find the collection point position corresponding to the switched slave database, realize the seamless switching of the slave database from exception to other slave database, and guarantee the business continuity.

Description

TECHNICAL FIELD

[0001] The present application belongs to the field of real-time data collection technology in big data, and particularly relates to a method for collecting point position mapping when MYSQL slave databases are switched. BACKGROUND

[0002] The existing MYSQL database architecture of the bank is one master, one backup and two slaves, and the real-time collection architecture is to collect the BINLOG log of the slave database of the MYSQL architecture, because collecting the BINLOG log of the slave database will not cause pressure to the master database, thereby not affecting the business load. The current scheme for collecting the MYSQL in real time is to obtain data by analyzing the MYSQL BINLOG log, but any good architecture has abnormal problems, so we often encounter the situation that the slave database being collected is down or stops serving, in which case, how to ensure that the collection and the business are not interrupted? How to find the collection point position of the corresponding BINLOG file of another MYSQL slave database from the current abnormal collection slave BINLOG point position? This is the pain point of the bank and the industry, and there is currently no mature scheme in the industry.

[0003] In view of the above problems, the current response scheme in the industry is to find the BINLOG file of the corresponding slave database by using the event time based on the current collected point position and the artificial experience estimation method, which has many disadvantages, consumes a lot of manpower and is not intelligent enough, and the found collection point position often deviates from the actual abnormal point collection point position, resulting in loss or repeated consumption when consuming, affecting the business data. Moreover, when abnormal, the operation and maintenance need to take a long time to find the corresponding slave database and the corresponding collection point position, during which the business is discontinuous, which will cause the business to stagnate and have a great impact.

[0004] The above information disclosed in the background section is only used to strengthen the understanding of the background of the present application, and therefore it can include information that does not constitute prior art known to those of ordinary skill in the art. SUMMARY

[0005] In order to solve the defects of the prior art, the purpose of the present application is to provide a method for collecting point position mapping when MYSQL slave databases are switched, which abstracts artificial experience into algorithm analysis, sorts the slave BINLOG file and analyzes the BINLOG content in reverse order, starts searching from the latest content of the latest BINLOG file, queries the corresponding collection point position, thereby realizing the algorithm of finding the BINLOG file and analyzing the BINLOG log as little as possible, finding the corresponding collection point position of the switched slave database, realizing seamless switching of the slave database from the abnormal switching to other slave databases, and guaranteeing the business continuity.

[0006] The technical scheme adopted by the present application is as follows:

[0007] 1. A point mapping processing method for switching between MYSQL slave databases, comprising the following steps:

[0008] Step 1: According to the error report of the collection program, the slave database exception is found, the current consumption BINLOG file and the current consumption log position of the slave database are located, and the user's action record on the data is found.

[0009] Step 2: According to the user's action record, the corresponding slave database BINLOG log file is searched in reverse order, the corresponding BINLOG file and point information are found, and then the corresponding operation record point information is found.

[0010] Step 3: When the corresponding switching slave database matching point is found, the BINLOG file where the point is located, the event time and the log point are recorded as the input parameters for restarting real-time collection, and the collection program is restarted.

[0011] Step 4: The real-time collection program obtains the latest slave database BINLOG file, event time and log point information, and can resume collection from the point breakpoint, ensuring data collection continuity.

[0012] With the above technical scheme, the system is based on the point of the BINLOG log abnormal point of the current collection slave database, and the BINLOG file corresponding to the switching slave database is manually analyzed in reverse order according to the point of the abnormal point, and the BINLOG file content is analyzed in reverse order to obtain the collection point of the slave database (manual analysis is difficult, often backward, downstream from, ensuring business RTO and RPO), the collection point is used as the initial collection point of the slave database after switching, thereby realizing smooth switching and ensuring business continuity.

[0013] Specifically, the step 1 is specifically: when the collection program appears an exception, the log outputs the abnormal BINLOG file and the last collection point, the system finds the slave database server exception according to the collected log data, finds the current consumption BINLOG file of the slave database server, locates the current consumption log position of the slave database, analyzes the abnormal BINLOG file, and finds the user's action record on the data according to the log position and event

[0014] Specifically, in the step 2, the system finds the corresponding data action and data file number according to the reverse search method, finds the user's action data record, finds the corresponding BINLOG file, locates the BINLOG file, and obtains the corresponding mapping point information and the corresponding operation record.

[0015] The beneficial effects of the present application are:

[0016] The present application overcomes the foregoing prior art in which the collection point needs to be collected manually and cannot accurately find the corresponding collection point to achieve real-time collection and rapid switching to other slave libraries, and restore business collection. Moreover, the method is time-consuming, has the disadvantages of less consumption or repeated consumption of data, and has a long interruption time in real-time collection, affecting business continuity. By mapping the collection point exception slave library and the normal slave library into a tool, the tool can be configured and scheduled through monitoring means, and when an exception is triggered, the tool automatically finds the mapping point of the corresponding slave library, thereby achieving rapid switching and real-time collection, and ensuring business continuity. The entire process is accurate to the second, and has less impact on the business. The high reliability and high availability of real-time collection of the bank are ensured. BRIEF DESCRIPTION OF DRAWINGS

[0017] The present application will be described by way of example and with reference to the accompanying drawings, in which:

[0018] Figure 1 is a flowchart of a collection point mapping method when the MYSQL slave library is switched in the present application;

[0019] Figure 2 is a normal MYSQL slave library BINLOG data point position mapping diagram in the present application;

[0020] Figure 3 is a last user behavior operation log diagram of an exception MYSQL slave library BINLOG log in the present application;

[0021] Figure 4 is a current file list diagram of an exception MYSQL slave library BINLOG in the present application;

[0022] Figure 5 is a same user behavior operation log diagram corresponding to the architecture of a normal MYSQL database mapping BINLOG file of an exception MYSQL slave library in the present application;

[0023] Figure 6 is an integrated tool execution effect diagram of a collection point mapping method when the MYSQL slave library is switched in the present application. DETAILED DESCRIPTION

[0024] In order to make the purposes, technical solutions and advantages of the embodiments of the present application clearer, the technical solutions in the embodiments of the present application will be described clearly and completely below with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, rather than all the embodiments. The components of the embodiments of the present application described and shown in the drawings can be arranged and designed in various different configurations. Therefore, the following detailed description of the embodiments of the present application provided in the drawings is not intended to limit the scope of the claimed present application, but only represents selected embodiments of the present application. Based on the embodiments of the present application, all other embodiments obtained by those skilled in the art without creative work belong to the scope of protection of the present application.

[0025] The embodiments of the present application will be described below in conjunction with the drawings. Figures 1-6 The present application will be described in detail.

[0026] The existing MYSQL database architecture of the bank is one master and one backup and two slaves, and the real-time collection architecture is to collect the BINLOG log of the slave database of the MYSQL architecture. The reason is that collecting the BINLOG log of the slave database will not cause pressure to the master database, so as to not affect the business load. The current scheme for collecting the MYSQL in real time is to obtain data by analyzing the MYSQL BINLOG log. However, even the best architecture will be abnormal, and therefore we often encounter that the slave database being collected is down or stops serving. Then, how do we guarantee that the collection is not interrupted and the business is not interrupted? How do we find another MYSQL slave database corresponding to the collection point from the current abnormal collection slave database BINLOG point? This is the pain point of the bank and the industry, and there is currently no corresponding mature scheme in the industry.

[0027] Financial industry has strict requirements on RTO and RPO, so when the slave library appears an exception, if it cannot be switched to other corresponding slave library to realize continuous collection in time, it is unacceptable loss to the business. In order to solve this problem, the industry solutions are investigated, and after MYSQL 5.7, there is a Git ID (transaction ID) architecture, which guarantees the data consistency of master-slave through transaction ID, realizes seamless switching, but the current situation of our bank not only exists in the version above MYSQL 5.7, but also exists in the following version, the reconstruction of the overall architecture is difficult and costly, and it cannot meet the current risk solution, so another solution must be found. Through the analysis of BINLOG principle, it is found that BINLOG records the operation records of data such as insert, update, delete and truncate. Each operation record of data can be found in BINLOG, so we can imagine whether the last operation record read when the collection exception occurs can be used as the condition for searching in the corresponding normal slave BINLOG. Then the problem comes, how to find the least BINLOG file, parse the least BINLOG file content, and find the corresponding collection BINLOG file and collection point of the last record as soon as possible?

[0028] In order to solve the defects existing in the prior art, the purpose of the present application is to provide a collection point mapping processing method when the MYSQL slave libraries are switched to each other, referring to Figure 1 , the specific method is as follows:

[0029] According to the error of the collection program, the slave library exception is found, the current consumption BINLOG file and the current consumption log position of the slave library are located, and the user's action record on data is found.

[0030] 1) When the collection program appears an exception, the exception BINLOG file and the last collection point of the exception are output in the log, according to the point, the NEXT_POSITION:993954435 position operation record of BINLOG_FILE: mysql-bin.000072 is parsed as the comparison condition for parsing the corresponding slave library. According to the log of the collection program, it is found that the slave server appears an exception, the current consumption BINLOG file of the slave server appearing an exception is found, the current consumption log position of the slave library is located, the exception BINLOG file is parsed, and the user's action record on data is found according to the log position and event

[0031] The BINLOG file information and the collection point of the exception point output in the exception collection node log, referring to Figure 2 ;

[0032] Abnormal slave current consumption BINLOG file: mysql-bin.000032

[0033] Abnormal slave current consumption log position: 273

[0034] Abnormal BINLOG file parsing: mysql-bin.000032, find user action record according to log point and event, see Figure 3 ;

[0035] 2) According to the abnormal collection slave BINLOG parsing user action (SQL statement), go to the corresponding slave BINLOG log file in reverse order (the purpose of reverse order search method is to find the corresponding data action from the latest BINLOG file, the last written BINLOG log, find the user action data record, find the corresponding BINLOG file, so as to locate the BINLOG file, find the corresponding operation record point information)

[0036] According to the BINLOG list, it can be seen that the latest BINLOG file is, see Figure 4 ;

[0037] mysql-bin.000052

[0038] Query the BINLOG file and point information of the slave with the same operation record:

[0039] BINLOG file: mysql-bin.000052

[0040] See Figure 5 , corresponding operation record point information: 382

[0041] 3) When the corresponding switching slave matching point is found, record the BINLOG file where the point is located, the event time and the log point as the input parameter of the real-time collection restart, run the collection program again, see Figure 6 .

[0042] 4) Real-time collection program can recover collection from the point breakpoint by getting the latest slave BINLOG file, event time and log point information, which guarantees the continuity of data collection.

[0043] 5) The above process is realized by code, and the tool collection slave abnormality is automatically monitored and automatically switched to the corresponding normal slave. It also has the ability to be embedded into the real-time development platform for integrated management. It has the value of expansion and the potential to form a product.

[0044] The one master and one backup two-slave MYSQL architecture, the backup cluster synchronizes the master cluster data, the real-time application program, and the BINLOG data of one slave cluster is collected, there is no perfect architecture, when the exception comes, it is particularly important to guarantee the business continuity, therefore, the above method forms the tooling ability, can quickly realize that the collection node is abnormal, automatically and seamlessly switches to other normal slave library, thereby guaranteeing the business continuity, improving the high availability and high reliability of the architecture.

[0045] In the present application, by arranging the adjacent slice placing grooves in parallel and staggered, it is obvious for those skilled in the art that the present application is not limited to the details of the above exemplary embodiments, and can be implemented in other specific forms without departing from the spirit or essential characteristics of the present application. Therefore, the embodiments should be regarded as exemplary and non-limiting from any point of view, and the scope of the present application is defined by the appended claims rather than the above description, and therefore all changes falling within the meaning and scope of the essential elements of the claims are intended to be included in the present application. Any reference signs in the claims should not be regarded as limiting the claims involved.

Claims

1. A point position mapping processing method for collecting when MYSQL slave databases switch to each other, characterized in that, The method comprises the following steps: Step 1: according to the error of the collection program, find the slave exception, locate the current consumption BINLOG file and the current consumption log position of the slave, find the user's action record on the data; Step 2: according to the user's action record, find the corresponding BINLOG log file in the slave in reverse order, find the corresponding BINLOG file and point position information, and then find the corresponding operation record point position information; Step 3: when the corresponding switching slave matching point is found, record the BINLOG file where the point is located, the event time and the log point, as the input parameter of restarting real-time collection, and re-run the collection program; Step 4: the real-time collection program obtains the latest slave BINLOG file, event time and log point position information, and can recover the collection from the point breakpoint, ensuring the continuity of data collection. 2.The method of claim 1, wherein, The step 1 is specifically: when the collection program appears an exception, the log will output the exception BINLOG file and its last collection point, the system finds the slave server exception according to the collected log data, finds the current consumption BINLOG file of the slave server where the exception occurs, locates the current consumption log position of the slave, parses the exception BINLOG file, and finds the user's action record on the data according to the log position and event. 3.The method of claim 1, wherein, The step 2 is specifically: the system finds the user's action according to the exception collection slave BINLOG, finds the corresponding data action and data file number according to the reverse search method, finds the user's action record on the data, finds the corresponding BINLOG file, and then locates the BINLOG file to obtain the corresponding mapping point information and the corresponding operation record.

Images