Method and device for realizing quick synchronization of DDL of database standby machine

Abstract

The application discloses a method, device and equipment for realizing quick synchronization of DDL of a database backup machine and a storage medium, and relates to the technical field of databases, which comprises the following steps: setting a DDL synchronization thread on a host and setting a DDL playback thread on a backup machine; the host executes DDL through a DDL execution thread on the host, synchronizes the executed DDL to the DDL synchronization thread through the DDL execution thread, and synchronizes the executed DDL to the DDL playback thread on the backup machine through the DDL synchronization thread; the backup machine sends the received DDL to the DDL execution thread on the backup machine through the DDL playback thread, and executes the DDL through the DDL execution thread. The application executes ddl on the host and the backup machine simultaneously, effectively improves the playback speed of the DDL of the database backup machine, reduces the playback delay, and effectively guarantees the RTO and RPO of the database.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of database technology, in particular to a method and device for realizing fast synchronization of DDL by database standby. BACKGROUND

[0002] In the current database master-slave high-availability architecture, when a database needs to execute DDL, the DDL must be executed on the master to be synchronized to the standby for playback. The playback of DDL and the playback of DML cannot be executed in parallel. At this time, the standby playback delay is basically determined by the execution time of DDL. Meanwhile, the master-slave switching time of the database is also affected by the standby playback delay. Therefore, in the case of long DDL execution time, the RTO of the database may not be guaranteed. SUMMARY

[0003] The present application solves the technical problem of overcoming the shortcomings of the prior art and providing a method and device for realizing fast synchronization of DDL by database standby.

[0004] To solve the above technical problems, the technical solutions of the present application are as follows:

[0005] The present application provides a method for realizing fast synchronization of DDL by database standby, comprising:

[0006] setting a DDL synchronization thread on the master and setting a DDL playback thread on the standby;

[0007] the master executes DDL through a DDL execution thread thereon, synchronizes the executed DDL to the DDL synchronization thread through the DDL execution thread, and synchronizes the executed DDL to the DDL playback thread on the standby through the DDL synchronization thread;

[0008] the standby sends the received DDL to the DDL execution thread thereon through the DDL playback thread, and executes the DDL through the DDL execution thread.

[0009] The present application also provides a device for realizing fast synchronization of DDL by database standby, comprising:

[0010] a thread setting module for setting a DDL synchronization thread on the master and setting a DDL playback thread on the standby;

[0011] a master control module for controlling the master to execute DDL through a DDL execution thread thereon, synchronizing the executed DDL to the DDL synchronization thread through the DDL execution thread, and synchronizing the executed DDL to the DDL playback thread on the standby through the DDL synchronization thread;

[0012] The standby control module is configured to control the standby machine to send the received DDL to the DDL execution thread thereon through the DDL playback thread, and execute the DDL through the DDL execution thread.

[0013] The application further provides a computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the method for realizing fast synchronization of DDL of a database standby machine when executing the program.

[0014] The application further provides a computer readable storage medium, wherein a computer program is stored in the computer readable storage medium, and the program is executable on a processor to implement the method for realizing fast synchronization of DDL of a database standby machine.

[0015] The method and device for realizing fast synchronization of DDL of a database standby machine are characterized in that: a DDL synchronization thread is set on the master machine, and a DDL playback thread is set on the standby machine; the master machine executes the DDL through the DDL execution thread thereon, synchronizes the executed DDL to the DDL synchronization thread through the DDL execution thread, and synchronizes the executed DDL to the DDL playback thread on the standby machine through the DDL synchronization thread; the standby machine sends the received DDL to the DDL execution thread thereon through the DDL playback thread, and executes the DDL through the DDL execution thread; thus, the ddl is executed simultaneously by the master machine and the standby machine, the playback speed of the DDL of the database standby machine is effectively improved, the playback delay is reduced, and the RTO and RPO of the database are effectively ensured. BRIEF DESCRIPTION OF DRAWINGS

[0016] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the following will briefly introduce the drawings needed to be used in the embodiment description. Obviously, the drawings in the following description are only some embodiments of the present application, and other drawings can be obtained by those skilled in the art without any creative labor.

[0017] Figure 1 The flowchart of the method for realizing fast synchronization of DDL of a database standby machine is provided.

[0018] Figure 2 The flowchart of the method for realizing fast synchronization of DDL of a database standby machine is provided.

[0019] Figure 3 The structural diagram of the device for realizing fast synchronization of DDL of a database standby machine is provided.

[0020] Figure 4 The schematic diagram of the computer device is provided. DETAILED DESCRIPTION

[0021] In order to make the content of the present application more easily understood, the present application is further described in detail below according to the specific embodiments and in conjunction with the drawings.

[0022] The present application provides a method and device for realizing fast synchronization of DDL of a database standby machine. The ddl can be executed simultaneously by the master and standby machines, which effectively improves the playback speed of the database standby machine DDL, reduces the playback delay, and effectively guarantees the database RTO and RPO.

[0023] The present application is further described below in conjunction with the drawings.

[0024] Referring to Figure 1 The present application provides a method for realizing fast synchronization of DDL of a database standby machine. The method comprises steps S101-S105, and the specific steps are described as follows.

[0025] Step S101: A DDL synchronization thread is set on the master machine, and a DDL playback thread is set on the standby machine.

[0026] Specifically, referring to Figure 2 The flowchart of the method for realizing fast synchronization of DDL of a database standby machine provided by the present application is shown in the figure. In the figure, Master is the master machine, and Slave is the standby machine. An independent DDL synchronization thread (i.e., Sync ddl thread in Figure 2 ) is additionally added on the master machine. An independent DDL playback thread (i.e., Replay ddl thread in Figure 2 ) is additionally added on each standby machine.

[0027] It can be understood that the DDL execution threads are set on the master and standby machines, referring to Figure 2 . The DDL execution thread on the master machine is used to execute DDL and is also used to synchronize the DDL executed on the master machine to the DDL playback thread on the standby machine. The DDL execution thread on the standby machine is also used to execute DDL and is also used to synchronize the DDL execution result to the DDL playback thread.

[0028] The DDL synchronization thread added on the master machine can synchronize the DDL executed on the master machine to the DDL playback thread on the standby machine and receive the DDL execution result of the standby machine. The DDL playback thread added on the standby machine can receive the DDL synchronized from the master machine and send it to the DDL execution thread on the standby machine to execute the DDL.

[0029] Step S102: The master machine executes DDL through the DDL execution thread thereon, synchronizes the executed DDL to the DDL synchronization thread through the DDL execution thread, and synchronizes the executed DDL to the DDL playback thread on the standby machine through the DDL synchronization thread.

[0030] Specifically, the host executes the DDL received from the client through a DDL execution thread in the host. Meanwhile, the DDL execution thread in the host also synchronizes the DDL executed in the host to a newly added DDL synchronization thread in the host. The DDL synchronization thread sends the DDL executed in the host to the newly added DDL playback thread in all the standbys.

[0031] Step S103: The standby sends the received DDL to the DDL execution thread in the standby through the DDL playback thread, and executes the DDL through the DDL execution thread.

[0032] Specifically, each standby sends the received DDL to the DDL execution thread in the standby through the newly added DDL playback thread in the standby, and executes the DDL through the DDL execution thread. After the DDL is executed in the standby, the standby synchronizes the DDL execution result to the DDL playback thread through the DDL execution thread in the standby, and the DDL playback thread feeds back the execution result to the DDL synchronization thread in the host.

[0033] It should be noted that when the standby executes the DDL, the executed DDL is not written into the binary log file of the standby, and other DMLs and DCLs are normally synchronized and played back.

[0034] Step S104: After the DDL is executed in the host, the DDL synchronization thread informs the standby to suspend the playback of other data except the DDL.

[0035] Specifically, after the DDL is executed in the host, the host informs all the standbys through the DDL synchronization thread to suspend the synchronization and playback of other data except the DDL, and only execute the DDL.

[0036] Step S105: After the DDL is executed in the standby, the DDL playback thread fills in the information of the executed DDL in the current database cache and the binary log.

[0037] Specifically, when the DDL is executed in the standby, the executed DDL is not written into the binary log file. After the DDL is executed, the DDL playback thread fills in the information of the executed DDL.

[0038] Step S106: Start the playback of other data in the standby.

[0039] Specifically, after the standby fills in the information of the DDL, the standby normally synchronizes and plays back other DMLs and DCLs.

[0040] It should be noted that generally the host executes the DDL first, but because the host and the standby execute the DDL asynchronously, the standby may execute the DDL first. If the standby DDL is executed first, the DDL execution thread on the standby suspends the service of the library table involved in the executed DDL, and after the host DDL is executed, the host informs the standby to start the DDL execution thread on the standby.

[0041] Therefore, the above method effectively improves the playback speed of the database standby DDL, reduces the playback delay, and effectively guarantees the database RTO and RPO.

[0042] Referring to Figure 3 The embodiment of the application also provides a device for realizing fast synchronization of DDL of a database standby.

[0043] The thread setting module 201 is configured to set a DDL synchronization thread on the host and a DDL playback thread on the standby.

[0044] It should be noted that the thread setting module 201 is configured to additionally set an independent DDL synchronization thread on the host and an independent DDL playback thread on each standby.

[0045] It can be understood that the DDL execution threads are set on the host and the standby. The DDL execution thread on the host is configured to execute the DDL and synchronize the executed DDL to the DDL playback thread on the standby. The DDL execution thread on the standby is also configured to execute the DDL and synchronize the DDL execution result to the DDL playback thread.

[0046] The DDL synchronization thread additionally set on the host is configured to synchronize the executed DDL on the host to the DDL playback thread on the standby and receive the DDL execution result of the standby. The DDL playback thread additionally set on the standby is configured to receive the DDL synchronized from the host and send the DDL to the DDL execution thread on the standby to execute the DDL.

[0047] The host control module 202 is configured to control the host to execute the DDL through the DDL execution thread on the host, synchronize the executed DDL to the DDL synchronization thread through the DDL execution thread, and synchronize the executed DDL to the DDL playback thread on the standby through the DDL synchronization thread.

[0048] Meanwhile, the host control module 202 is also configured to inform the standby to suspend the playback of data other than the DDL through the DDL synchronization thread after the host DDL is executed.

[0049] The standby control module 203 is configured to control the standby to send the received DDL to the DDL execution thread thereon through the DDL playback thread, and execute the DDL through the DDL execution thread.

[0050] It should be noted that when the standby executes the DDL, the executed DDL is not written into the binary log file of the standby, and other DML and DCL are normally synchronized and played back during the period.

[0051] Meanwhile, the standby control module 203 is further configured to, after the DDL of the standby is executed, fill in the information of the executed DDL in the current database cache and the binary log through the DDL playback thread.

[0052] In addition, if the DDL of the standby is executed before the DDL of the host is executed, the standby control module 203 is further configured to control the DDL execution thread on the standby to suspend the service of the library table involved in the executed DDL on the standby. The host control module 202 is further configured to, after the DDL of the host is executed, instruct the standby to start the DDL execution thread thereon.

[0053] Referring to Figure 4 The embodiment also provides a computer device, and components of the computer device can include but are not limited to one or more processors or processing units, system memory, and a bus connecting different system components including the system memory and the processing units.

[0054] The bus represents one or more of several types of bus structures, including a memory bus or memory controller, a peripheral bus, a graphics acceleration port, a processor or local bus using any of a variety of bus architectures. Examples of such architectures include, but are not limited to, industry standard architecture (ISA) bus, microchannel architecture (MAC) bus, enhanced ISA bus, video electronics standards association (VESA) local bus, and peripheral component interconnect (PCI) bus.

[0055] The computer system / server typically includes a variety of computer system readable media. Such media can be any available media that is accessible by the computer system / server and includes both volatile and non-volatile media, removable and non-removable media.

[0056] The system memory can include computer system readable media in the form of volatile memory, such as random access memory (RAM) and / or cache memory. The computer device can further include other removable / non-removable, volatile / non-volatile computer system storage media. By way of example only, a storage system suitable for storing physical memory can be used for reading from and writing to non-removable, non-volatile magnetic media (e.g., a "hard drive"). A magnetic disk drive can also be provided for reading from and writing to a removable, non-volatile magnetic disk (e.g., a "floppy disk"), and an optical disk drive can be provided for reading from or writing to a removable, non-volatile optical disk (e.g., a CD-ROM, DVD-ROM or other optical media). In these instances, each can be connected to the bus by one or more data media interfaces. The memory can include at least one program product having a set (e.g., at least one) of program modules that are configured to carry out the functions of embodiments of the application.

[0057] Program / utility, having a set (at least one) of program modules, can be stored in, for example, memory by way of example, such program modules include an operating system, one or more application programs, other program modules, and program data, each of or some combination of which can include an implementation of a network environment. Program modules are generally executed by processing unit and / or provided data to other program modules in memory, as desired.

[0058] The computer device can also communicate with one or more external devices such as a keyboard, a pointing device, a display, etc. through input / output (I / O) interface(s). Additionally, the computer device can communicate with one or more networks such as a local area network (LAN), a wide area network (WAN), and / or a public network such as the Internet, through network adapter.

[0059] The processing unit executes the program modules stored in the system memory to perform the functions and / or methods of embodiments of the application.

[0060] The computer program mentioned above can be set in a computer storage medium, that is, the computer storage medium is encoded with the computer program, which makes one or more computers execute the method flow and / or device operation shown in the above embodiments of the application when the computer program is executed by the one or more computers.

[0061] With the development of time and technology, the medium meaning is more and more extensive, and the computer program propagation path is no longer limited to a tangible medium, but can also be directly downloaded from a network, etc. Any combination of one or more computer-readable media can be used. The computer-readable medium can be a computer-readable signal medium or a computer-readable storage medium. The computer-readable storage medium may, for example, but is not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or apparatus, or any combination thereof. More specific examples (non-exhaustive list) of the computer-readable storage medium include: an electrical connection having one or more wires, a portable computer diskette, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In this document, the computer-readable storage medium can be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, device or apparatus.

[0062] The computer-readable signal medium can include a data signal propagated in a baseband or as part of a carrier wave, in which the computer-readable program code is carried. Such propagated data signals can take a variety of forms, including, but not limited to, electromagnetic signals, optical signals, or any suitable combination thereof. The computer-readable signal medium can also be any computer-readable medium that is not a storage medium and that can be used to carry or store program code for use by or in connection with an instruction execution system, apparatus, or device.

[0063] The program code contained on the computer-readable medium can be transmitted using any suitable medium, including, but not limited to, wireless, wireline, optical fiber, RF, etc., or any suitable combination thereof.

[0064] Computer program code for carrying out operations of the present application can be written in any combination of one or more programming languages, including an object-oriented programming language such as Java, Smalltalk, C++, or conventional procedural programming languages, such as the "C" programming language or similar programming languages. The program code can execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer, or entirely on the remote computer or server. In the latter scenario, the remote computer can be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection can be made to an external computer (for example, through the Internet using an Internet Service Provider).

[0065] Besides the above-mentioned embodiments, the present application can have other embodiments; any technical solution formed by equivalent replacement or equivalent transformation falls within the protection scope of the present application.

Claims

1. A method for realizing fast synchronization of DDL for database standby, characterized in that: The method comprises the following steps: setting a DDL synchronization thread on the host and a DDL playback thread on the standby machine; the host executes DDL through a DDL execution thread thereon, and synchronizes the executed DDL to the DDL synchronization thread through the DDL execution thread, and the DDL synchronization thread synchronizes the executed DDL to the DDL playback thread on the standby machine; the standby machine sends the received DDL to the DDL execution thread thereon through the DDL playback thread, and executes the DDL through the DDL execution thread, and the executed DDL is not written into the binary log file of the standby machine; after the host finishes executing the DDL, the standby machine is informed to suspend other data playback except the DDL through the DDL synchronization thread; after the standby machine finishes executing the DDL, the DDL information is filled in the current database cache and the binary log through the DDL playback thread; other data playback on the standby machine is started.

2. The method for realizing fast synchronization of DDL of a database standby machine according to claim 1, characterized in that: After the standby machine sends the received DDL to the DDL execution thread thereon through the DDL playback thread, and executes the DDL through the DDL execution thread, the method further comprises the following steps: the standby machine synchronizes the DDL execution result to the DDL playback thread through the DDL execution thread thereon, and feeds back the DDL execution result to the DDL synchronization thread through the DDL playback thread.

3. The method for realizing fast synchronization of DDL of a database standby machine according to claim 1, characterized in that: The method further comprises the following steps: if the standby machine finishes executing the DDL before the host finishes executing the DDL, the DDL execution thread on the standby machine suspends the service of the library table involved in the executed DDL, and after the host finishes executing the DDL, the host informs the standby machine to start the DDL execution thread thereon.

4. An apparatus for realizing quick synchronization of DDL for a database standby, characterized in that: The method comprises the following steps: a thread setting module, configured to set a DDL synchronization thread on the host and a DDL playback thread on the standby machine; a host control module, configured to control the host to execute DDL through a DDL execution thread thereon, and to synchronize the executed DDL to the DDL synchronization thread through the DDL execution thread, and to synchronize the executed DDL to the DDL playback thread on the standby machine through the DDL synchronization thread; a standby machine control module, configured to control the standby machine to send the received DDL to the DDL execution thread thereon through the DDL playback thread, and to execute the DDL through the DDL execution thread, and to not write the executed DDL into the binary log file of the standby machine; the host control module is further configured to inform the standby machine to suspend other data playback except the DDL through the DDL synchronization thread after the host finishes executing the DDL; the standby machine control module is further configured to fill the DDL information in the current database cache and the binary log through the DDL playback thread after the standby machine finishes executing the DDL, and to start other data playback on the standby machine.

5. A computer device comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, characterized in that: The processor executes the program to implement the method in any one of claims 1-3.

6. A computer readable storage medium having stored thereon a computer program, characterized in that: The program is executed by the processor to implement the method in any one of claims 1-3.

Images