Database replication control method and device, equipment and medium

Abstract

The invention provides a database replication control method and device, equipment and a medium, and the method comprises the steps: obtaining a first time delay database table of a main database; acquiring a second time delay database table of a first slave database in the plurality of slave databases; the second time delay database table comprises a second timestamp corresponding to the first timestamp; and determining the time delay duration of the first slave database according to the data writing state of the master database, the first timestamp and the second timestamp. According to the data writing state of the master database, a first timestamp updated by the master database in the first time delay database table and a second timestamp updated by the first slave database in the plurality of slave databases in the second time delay database table, determining the time delay duration of the first slave database, compared with the prior art that the delay duration between the master database and the slave database is reflected more accurately by using the parameter SecondsBehindMaster, and the problem that when the data delay of the slave database is large, the middleware still distributes the query request to the slave database is avoided.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a method, device, equipment and medium for controlling database replication. Background Art

[0002] In order to solve a large number of concurrent accesses, large websites need to implement distributed load balancing on the website, which is far from enough. At the data business layer and data access layer, if the traditional data structure is still used, or if only one server is used to handle so many database connection operations, the database will inevitably crash, especially if the data is lost, the consequences are even more disastrous. Therefore, now MySQL (My Structured Query Language, relational database management system) clusters usually provide services to users through read-write separation components, among which the delayed replication of the read-only slave database is completed by using MySQL's master-slave replication. However, in the prior art, the parameter Seconds_Behind_Master involved in the delayed replication function cannot accurately reflect the delay time between the master and slave databases, and it is difficult to determine the data lag between the slave database and the master database, which may cause the problem that the middleware still distributes query requests to the slave database when the slave database data delay is large. Summary of the invention

[0003] The purpose of the present invention is to provide a control method, device, equipment and medium for database replication, which is used to solve the problem that the parameter Seconds_Behind_Master involved in database delayed replication in the prior art cannot accurately reflect the delay duration between the master and slave databases, making it difficult to determine the data lag between the slave database and the master database, which may cause the middleware to still distribute query requests to the slave database when the slave database data delay is large.

[0004] To achieve the above object, an embodiment of the present invention provides a method for controlling database replication, which includes:

[0005] Acquire a first delay database table of the main database; wherein the first delay database table includes a first timestamp, and the first timestamp is used to record the current time when the first delay database table is periodically updated when the main database is in a normal state of data writing;

[0006] Obtain a second delay database table of a first slave database in multiple slave databases; wherein, when the first slave database synchronizes data with the master database, the second delay database table is synchronously updated according to the first delay database table; the second delay database table includes a second timestamp corresponding to the first timestamp;

[0007] Determine the delay length of the first slave database according to the data writing status of the master database, the first timestamp, and the second timestamp.

[0008] Optionally, in the control method, wherein the data writing state of the master database includes a normal state and an abnormal state, and determining the delay length of the first slave database according to the data writing state of the master database, the first timestamp, and the second timestamp includes:

[0009] When the data writing state of the master database is normal, determining the delay length of the first slave database according to the second timestamp and the first machine time; wherein the first machine time is the current time of the scheduling node where the first slave database is located;

[0010] When the data writing state of the master database is in an abnormal state, the delay length of the first slave database is determined according to the first timestamp and the second timestamp.

[0011] Optionally, in the control method, the first delay database table and the second delay database respectively include a first flag bit for recording the data writing status of the main database;

[0012] Wherein, the method further comprises:

[0013] The data writing status of the primary database is determined according to the first flag bit; wherein, when the first flag bit is the first information, it is used to indicate that the data writing of the primary database is in a normal state; when the first flag bit is the second information, it is used to indicate that the data writing of the primary database is in an abnormal state.

[0014] Optionally, the control method further comprises:

[0015] In the case where ordinary users can write data normally, setting the first flag bit to the first information on the primary database;

[0016] In the case where ordinary users cannot write data, the first flag bit is set to the second information through a super user.

[0017] Optionally, the control method further comprises:

[0018] Determine the readiness state of a database in a database cluster; the database cluster includes the master database and the multiple slave databases, and the readiness state of the multiple slave databases is determined according to the corresponding delay duration;

[0019] When the ready state is ready, the corresponding database is added to the request backend list of read-write separation;

[0020] When the ready state is not ready, the corresponding database is removed from the request backend list of the read-write separation.

[0021] Optionally, in the control method, the step of determining the readiness status of a database in the database cluster further includes:

[0022] In the case that the connection of the first slave database among the multiple slave databases is abnormal, determining that the ready state of the first slave database is not ready;

[0023] When the first slave database is connected normally and the delay time is greater than a first preset threshold, determining that the ready state of the first slave database is not ready;

[0024] When the first slave database is connected normally and the delay time is less than or equal to the first preset threshold, after setting delayed replication, determining that the ready state of the first slave database is not ready;

[0025] When the connection of the first slave database is normal and the delay time is less than or equal to the first preset threshold, after closing the delayed replication, it is determined that the ready state of the first slave database is ready.

[0026] In order to achieve the above object, an embodiment of the present invention further provides a control device for database replication, which includes:

[0027] A first acquisition module is used to acquire a first delay database table of a main database; wherein the first delay database table includes a first timestamp, and the first timestamp is used to record the current time when the first delay database table is periodically updated when the main database is in a normal state of data writing;

[0028] A second acquisition module is used to acquire a second delay database table of a first slave database among multiple slave databases; wherein, when the first slave database synchronizes data from the master database, the second delay database table is synchronously updated according to the first delay database table; the second delay database table includes a second timestamp corresponding to the first timestamp;

[0029] The first determination module is used to determine the delay length of the first slave database according to the data writing status of the master database, the first timestamp and the second timestamp.

[0030] In order to achieve the above-mentioned purpose, an embodiment of the present invention also provides a network device, including: a processor, a memory, and a program or instruction stored in the memory and executable on the processor; wherein, when the processor executes the program or instruction, the control method of database replication as described above is implemented.

[0031] In order to achieve the above object, an embodiment of the present invention further provides a readable storage medium on which a program or instruction is stored, wherein the program or instruction, when executed by a processor, implements the steps in the control method of database replication as described above.

[0032] In order to achieve the above objective, an embodiment of the present invention further provides a computer program product, which includes computer instructions, and when the computer instructions are executed by a processor, the steps of the control method for database replication as described above are implemented.

[0033] The beneficial effects of the above technical solution of the present invention are as follows:

[0034] The embodiment of the present invention determines the delay length of the first slave database according to the data writing status of the master database, the first timestamp updated by the master database in the first delay database table, and the second timestamp updated by the first slave database in the second delay database table of multiple slave databases. Compared with the prior art that uses the parameter Seconds_Behind_Master to reflect the delay length between the master and slave databases, the embodiment of the present invention is more accurate and accurately determines the data lag between the slave database and the master database, avoiding the problem that the middleware still distributes the query request to the slave database when the data delay of the slave database is large. BRIEF DESCRIPTION OF THE DRAWINGS

[0035] Figure 1 A schematic diagram of a method for controlling database replication according to an embodiment of the present invention;

[0036] Figure 2 A schematic diagram of a control device for database replication according to an embodiment of the present invention. DETAILED DESCRIPTION

[0037] In order to make the technical problems, technical solutions and advantages to be solved by the present invention more clear, a detailed description will be given below with reference to the accompanying drawings and specific embodiments.

[0038] It should be understood that the references to "one embodiment" or "an embodiment" throughout the specification mean that the specific features, structures, or characteristics associated with the embodiment are included in at least one embodiment of the present invention. Therefore, the references to "in one embodiment" or "in an embodiment" appearing throughout the specification do not necessarily refer to the same embodiment. In addition, these specific features, structures, or characteristics may be combined in any suitable manner in one or more embodiments.

[0039] In various embodiments of the present invention, it should be understood that the size of the serial numbers of the following processes does not mean the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present invention.

[0040] Additionally, the terms "system" and "network" are often used interchangeably herein.

[0041] In the embodiments provided in the present application, it should be understood that "B corresponding to A" means that B is associated with A, and B can be determined according to A. However, it should also be understood that determining B according to A does not mean determining B only according to A, and B can also be determined according to A and / or other information.

[0042] For ease of understanding, some contents involved in the embodiments of the present invention are described below:

[0043] like Figure 1 As shown, a method for controlling database replication according to an embodiment of the present invention includes:

[0044] S10, obtaining a first delay database table of the main database; wherein the first delay database table includes a first timestamp, and the first timestamp is used to record the current time when the first delay database table is periodically updated when the main database is in a normal state of data writing;

[0045] It should be noted that the first delay database table is newly created on the main database, and the first timestamp T master The current time when the primary database is in a normal state of data writing when the first delay database table is periodically updated is recorded.

[0046] S20, a second delay database table of a first slave database in the slave database; wherein, when the first slave database synchronizes data from the master database, the second delay database table is synchronously updated according to the first delay database table; the second delay database table includes a second timestamp corresponding to the first timestamp;

[0047] It should be noted that, in the first slave database, the super-management user periodically queries the first delay database table to obtain the second timestamp T in the second delay database table. slave , wherein the second timestamp T slave Corresponding to the first timestamp T master .

[0048] S30, determining a delay length of the first slave database according to the data writing status of the master database, the first timestamp, and the second timestamp;

[0049] It should be noted that the delay time of the first database is T delay is based on the data writing status of the primary database, the first timestamp T master and the second timestamp T slave Yes, it is more accurate than using the parameter Seconds_Behind_Master to reflect the delay between the master and slave libraries.

[0050] In this embodiment, the first delay database table is newly created on the master database, and the delay length of the first slave database is determined according to the data writing status of the master database, the first timestamp updated by the master database in the first delay database table, and the second timestamp updated by the first slave database in the second delay database table among the multiple slave databases. Compared with the prior art that uses the parameter Seconds_Behind_Master to reflect the delay length between the master and slave databases, it is more accurate and can accurately determine the data lag between the slave database and the master database, thus avoiding the problem that the middleware still distributes the query request to the slave database when the data delay of the slave database is large.

[0051] Optionally, in the control method, the data writing state of the master database includes a normal state and an abnormal state, and step S30 includes:

[0052] When the data writing state of the master database is normal, determining the delay length of the first slave database according to the second timestamp and the first machine time; wherein the first machine time is the current time of the scheduling node where the first slave database is located;

[0053] It should be noted that when the data writing state of the master database is normal, the delay time length T of the first slave database is delay The calculation formula is as follows:

[0054] T delay =|T slave -T node |.

[0055] Wherein, the first machine time T node Reflects the current time of the scheduling node where the first slave database is located.

[0056] When the data writing state of the master database is in an abnormal state, determining the delay length of the first slave database according to the first timestamp and the second timestamp;

[0057] It should be noted that when the data writing state of the master database is abnormal, the delay time length T of the first slave database isdelay The calculation formula is as follows:

[0058] T delay =|T master -T slave |.

[0059] At this time, since the master database no longer updates the first timestamp, the delay length of the first slave database is calculated based on the first timestamp and the second timestamp.

[0060] Optionally, in the control method, the first delay database table and the second delay database respectively include a first flag bit for recording the data writing status of the main database;

[0061] Wherein, the method further comprises:

[0062] The data writing status of the primary database is determined according to the first flag bit; wherein, when the first flag bit is the first information, it is used to indicate that the data writing of the primary database is in a normal state; when the first flag bit is the second information, it is used to indicate that the data writing of the primary database is in an abnormal state.

[0063] In this embodiment, the first delay database table and the second delay database respectively include a first flag bit for recording the data writing status of the primary database. When the first flag bit is the first information true, it indicates that the data writing of the primary database is in a normal state; when the first flag bit is the second information false, it indicates that the data writing of the primary database is in an abnormal state.

[0064] Optionally, the control method further comprises:

[0065] In the case where ordinary users can write data normally, setting the first flag bit to the first information on the primary database;

[0066] In the case where ordinary users cannot write data, the first flag bit is set to the second information through a super user.

[0067] In this embodiment, when it is detected that the MySQL cluster currently including the master database and the first slave database initiates a master-slave switch or the master database is abnormal and causes the master-slave switch, the update permission flag is updated according to the following circumstances, that is, the information of the first flag is changed:

[0068] 1) When ordinary users cannot write data, use a super user to set the first flag bit of the first delay database table of the main database to the second information false;

[0069] 2) After the master-slave switch is completed and the updated master database is the master database, when ordinary users can write data normally, the first flag bit of the first delay database table is set to the first information true on the master database.

[0070] Optionally, in the control method, the first delay database table further includes a first identifier corresponding to the first timestamp, wherein different first timestamps correspond to different first identifiers; the second delay database table further includes a second identifier corresponding to the second timestamp, wherein different second timestamps correspond to different second identifiers;

[0071] Wherein, the method further comprises:

[0072] It is determined, according to the first identifier and the second identifier, whether the first timestamp and the second timestamp are corresponding timestamps.

[0073] In this embodiment, the first delay database table also includes the first identifier server_uuid. When the first delay database table is newly created on the main database, the server_uuid value of the first delay database table is updated to the server_uuid field of the first delay database table.

[0074] Optionally, the control method further comprises:

[0075] Determine the readiness state of a database in a database cluster; the database cluster includes the master database and the multiple slave databases, and the readiness state of the multiple slave databases is determined according to the corresponding delay duration;

[0076] When the ready state is ready, the corresponding database is added to the request backend list of read-write separation;

[0077] When the ready state is not ready, the corresponding database is removed from the request backend list of the read-write separation.

[0078] In this embodiment, the delay duration of the multiple slave databases is periodically checked to obtain the ready status of the multiple slave databases, and based on the ready status of the master database, the unready database is removed from the request backend list of the read-write separation, and is added to the request backend list of the read-write separation after the database returns to a normal ready status, thereby ensuring the consistency of data query.

[0079] Optionally, in the control method, the determining the readiness status of a database in the database cluster includes:

[0080] When the primary database is connected normally, determining that the ready state of the primary database is ready;

[0081] In the case that the primary database connection is abnormal, it is determined that the ready state of the primary database is not ready.

[0082] In this embodiment, the ready state of the primary database is determined by the connection state of the primary database.

[0083] Optionally, in the control method, the step of determining the readiness status of a database in the database cluster further includes:

[0084] In the case that the connection of the first slave database among the multiple slave databases is abnormal, determining that the ready state of the first slave database is not ready;

[0085] When the first slave database is connected normally and the delay time is greater than a first preset threshold, determining that the ready state of the first slave database is not ready;

[0086] When the first slave database is connected normally and the delay time is less than or equal to the first preset threshold, after setting delayed replication, determining that the ready state of the first slave database is not ready;

[0087] When the connection of the first slave database is normal and the delay time is less than or equal to the first preset threshold, after closing the delayed replication, it is determined that the ready state of the first slave database is ready.

[0088] In this embodiment, the readiness status of the multiple slave databases is determined by detecting the delay duration and connection status of the multiple slave databases, and the databases in an unready state are removed from the request backend list of read-write separation, and the databases in a normal ready state are added to the request backend database list, so as to ensure that normal database queries are maintained during the master-slave switching process.

[0089] like Figure 2 As shown, in order to achieve the above-mentioned purpose, an embodiment of the present invention further provides a control device for database replication, which includes:

[0090] A first acquisition module 201 is used to acquire a first delay database table of a main database; wherein the first delay database table includes a first timestamp, and the first timestamp is used to record the current time when the first delay database table is periodically updated when the main database is in a normal state of data writing;

[0091] The second acquisition module 202 is used to acquire a second delay database table of a first slave database among multiple slave databases; wherein, when the first slave database synchronizes data from the master database, the second delay database table is synchronously updated according to the first delay database table; the second delay database table includes a second timestamp corresponding to the first timestamp;

[0092] The first determination module 203 is used to determine the delay length of the first slave database according to the data writing status of the master database, the first timestamp and the second timestamp.

[0093] Optionally, in the control device, the first determining module 203 includes:

[0094] a first determining unit, configured to determine, when the data writing state of the master database is in a normal state, a delay length of the first slave database according to the second timestamp and the first machine time; wherein the first machine time is the current time of the scheduling node where the first slave database is located;

[0095] The second determining unit is configured to determine a delay length of the first slave database according to the first timestamp and the second timestamp when the data writing state of the master database is an abnormal state.

[0096] Optionally, in the control device, the first delay database table and the second delay database respectively include a first flag bit for recording a data writing status of the main database;

[0097] Wherein, the device further comprises:

[0098] The second determination module is used to determine the data writing status of the primary database according to the first flag bit; wherein, when the first flag bit is the first information, it is used to indicate that the data writing of the primary database is in a normal state; when the first flag bit is the second information, it is used to indicate that the data writing of the primary database is in an abnormal state.

[0099] Optionally, the control device further includes:

[0100] A first processing module, configured to set the first flag bit to the first information on the primary database when an ordinary user can write data normally;

[0101] The second processing module is configured to set the first flag bit to the second information through a super user when an ordinary user cannot write data.

[0102] Optionally, in the control device, the first delay database table further includes a first identifier corresponding to the first timestamp, wherein different first timestamps correspond to different first identifiers; the second delay database table further includes a second identifier corresponding to the second timestamp, wherein different second timestamps correspond to different second identifiers;

[0103] Wherein, the device further comprises:

[0104] The third determination module is used to determine whether the first timestamp and the second timestamp are corresponding timestamps according to the first identifier and the second identifier.

[0105] Optionally, the control device further includes:

[0106] A fourth determination module is used to determine the readiness state of a database in a database cluster; the database cluster includes the master database and the multiple slave databases, and the readiness state of the multiple slave databases is determined according to the corresponding delay duration;

[0107] A third processing module is used to add the corresponding database to the request backend list of read-write separation when the ready state is ready;

[0108] The fourth processing module is used to remove the corresponding database from the read-write separation request backend list when the ready state is not ready.

[0109] Optionally, in the control device, the fourth determining module includes:

[0110] A third determining unit, configured to determine that the ready state of the primary database is ready when the primary database connection is normal;

[0111] The fourth determining unit is configured to determine that the ready state of the primary database is not ready when the primary database connection is abnormal.

[0112] Optionally, in the control device, the fourth determining module further includes:

[0113] A fifth determining unit, configured to determine that the ready state of the first slave database is not ready when the connection of the first slave database among the multiple slave databases is abnormal;

[0114] a sixth determining unit, configured to determine that the ready state of the first slave database is not ready when the first slave database is connected normally and the delay time is greater than a first preset threshold;

[0115] a seventh determining unit, configured to determine that the ready state of the first slave database is not ready after setting delayed replication when the first slave database is connected normally and the delay time is less than or equal to the first preset threshold;

[0116] An eighth determining unit is used to determine that the ready state of the first slave database is ready after closing delayed replication when the first slave database is connected normally and the delay duration is less than or equal to the first preset threshold.

[0117] It should be noted here that the above-mentioned device provided in the embodiment of the present invention can implement all the method steps implemented in the above-mentioned method embodiment, and can achieve the same technical effect. The parts and beneficial effects that are the same as the method embodiment in this embodiment will not be described in detail here.

[0118] In order to achieve the above-mentioned purpose, an embodiment of the present invention also provides a network device, including: a processor, a memory, and a program or instruction stored in the memory and executable on the processor; wherein, when the processor executes the program or instruction, the control method of database replication as described above is implemented.

[0119] In order to achieve the above object, an embodiment of the present invention further provides a readable storage medium on which a program or instruction is stored, wherein the program or instruction, when executed by a processor, implements the steps in the control method of database replication as described above.

[0120] In order to achieve the above object, an embodiment of the present invention further provides a computer program product, including computer instructions, which, when executed by a processor, implement the above Figure 1 The various processes of the method embodiment shown can achieve the same technical effect, and will not be described again here to avoid repetition.

[0121] The above exemplary embodiments are described with reference to the accompanying drawings, and many different forms and embodiments are feasible without departing from the spirit and teachings of the present invention. Therefore, the present invention should not be constructed as a limitation of the exemplary embodiments proposed herein. More specifically, these exemplary embodiments are provided so that the present invention will be perfect and complete, and the scope of the present invention will be conveyed to those who are familiar with the technology. In these figures, the component sizes and relative sizes may be exaggerated for clarity. The terms used here are only based on the purpose of describing specific exemplary embodiments and are not intended to be limiting. As used herein, unless the text clearly indicates otherwise, the singular forms "one", "an" and "the" are intended to include these multiple forms. It will be further understood that the terms "including" and / or "comprising" when used in this specification indicate the presence of the features, integers, steps, operations, components and / or components, but do not exclude the presence or increase of one or more other features, integers, steps, operations, components, components and / or their groups. Unless otherwise indicated, when stated, a range of values ​​includes the upper and lower limits of that range and any subranges therebetween.

[0122] The above is a preferred embodiment of the present invention. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the principles of the present invention. These improvements and modifications should also be regarded as the scope of protection of the present invention.

Claims

1. A method for controlling database replication, characterized in that: include: Acquire a first delay database table of the main database; wherein the first delay database table includes a first timestamp, and the first timestamp is used to record the current time when the first delay database table is periodically updated when the main database is in a normal state of data writing; Obtain a second delay database table of a first slave database in multiple slave databases; wherein, when the first slave database synchronizes data with the master database, the second delay database table is synchronously updated according to the first delay database table; the second delay database table includes a second timestamp corresponding to the first timestamp; Determine the delay length of the first slave database according to the data writing status of the master database, the first timestamp, and the second timestamp.

2. The control method according to claim 1, characterized in that: The data writing state of the master database includes a normal state and an abnormal state, and determining the delay length of the first slave database according to the data writing state of the master database, the first timestamp, and the second timestamp includes: When the data writing state of the master database is normal, determining the delay length of the first slave database according to the second timestamp and the first machine time; wherein the first machine time is the current time of the scheduling node where the first slave database is located; When the data writing state of the master database is in an abnormal state, the delay length of the first slave database is determined according to the first timestamp and the second timestamp.

3. The control method according to claim 1, characterized in that: The first delay database table and the second delay database table respectively include a first flag bit for recording the data writing status of the main database; Wherein, the method further comprises: The data writing status of the primary database is determined according to the first flag bit; wherein, when the first flag bit is the first information, it is used to indicate that the data writing of the primary database is in a normal state; when the first flag bit is the second information, it is used to indicate that the data writing of the primary database is in an abnormal state.

4. The control method according to claim 3, characterized in that: The method further comprises: In the case where ordinary users can write data normally, setting the first flag bit to the first information on the primary database; In the case where ordinary users cannot write data, the first flag bit is set to the second information through a super user.

5. The control method according to claim 1, characterized in that: The method further comprises: Determine the readiness state of a database in a database cluster; the database cluster includes the master database and the multiple slave databases, and the readiness state of the multiple slave databases is determined according to the corresponding delay duration; When the ready state is ready, the corresponding database is added to the request backend list of read-write separation; When the ready state is not ready, the corresponding database is removed from the request backend list of the read-write separation.

6. The control method according to claim 5, characterized in that: The determining the readiness state of the database in the database cluster further includes: In the case that the connection of the first slave database among the multiple slave databases is abnormal, determining that the ready state of the first slave database is not ready; When the first slave database is connected normally and the delay time is greater than a first preset threshold, determining that the ready state of the first slave database is not ready; When the first slave database is connected normally and the delay time is less than or equal to the first preset threshold, after setting delayed replication, determining that the ready state of the first slave database is not ready; When the connection of the first slave database is normal and the delay time is less than or equal to the first preset threshold, after closing the delayed replication, it is determined that the ready state of the first slave database is ready.

7. A control device for database replication, characterized in that: include: A first acquisition module is used to acquire a first delay database table of a main database; wherein the first delay database table includes a first timestamp, and the first timestamp is used to record the current time when the first delay database table is periodically updated when the main database is in a normal state of data writing; A second acquisition module is used to acquire a second delay database table of a first slave database among multiple slave databases; wherein, when the first slave database synchronizes data from the master database, the second delay database table is synchronously updated according to the first delay database table; the second delay database table includes a second timestamp corresponding to the first timestamp; The first determination module is used to determine the delay length of the first slave database according to the data writing status of the master database, the first timestamp and the second timestamp.

8. A network device comprising: A processor, a memory, and a program or instruction stored in the memory and executable on the processor; characterized in that when the processor executes the program or instruction, the control method for database replication as described in any one of claims 1 to 6 is implemented.

9. A readable storage medium having a program or instruction stored thereon, characterized in that: When the program or instruction is executed by a processor, the steps in the method for controlling database replication according to any one of claims 1 to 6 are implemented.

10. A computer program product, characterized in that The method comprises computer instructions, which, when executed by a processor, implement the steps of the method for controlling database replication according to any one of claims 1 to 6.

Images