A MYSQL data synchronization method and related device

Abstract

The invention belongs to the technical field of data storage, and particularly relates to a MYSQL data synchronization method and a related device, which are used in an open source middleware Canal, and the open source middleware Canal supports the MySQL Replication protocol; the MYSQL data synchronization method comprises: monitoring the MySQL Binlog log of the MYSQL relational database; when the MySQL Binlog log is monitored, parsing and verifying the MySQL Binlog log to obtain a data change message; sending the data change message to a preset user processing program; wherein the data change message is used to trigger the preset user processing program to synchronize the data in the MYSQL relational database to a preset target data source; the invention realizes data synchronization between the MySQL database and the target data source, and ensures the real-time and high reliability of the data.

Description

Technical Field

[0001] The present invention belongs to the technical field of data synchronization, and in particular relates to a MYSQL data synchronization method and related devices. Background Art

[0002] With the increasing installed capacity of renewable energy in power generation systems, the data of renewable energy site monitoring systems has also increased, and the amount of data stored has also increased exponentially. How to realize data sharing, business sharing and automatic data backup among various systems in real time and accurately is particularly important for improving the reliability, ease of use and rapid response capabilities of power generation monitoring systems.

[0003] At present, the existing new energy station monitoring system usually includes multiple subsystems. The data synchronization, sharing and data backup between the subsystems are usually achieved by means of interfaces, scheduled tasks or manual import. It has the defect of poor real-time performance and can only meet non-real-time scenarios. Secondly, there are problems with data errors and omissions. Summary of the invention

[0004] In view of the technical problems existing in the prior art, the present invention provides a MYSQL data synchronization method and related devices to solve the technical problems of data synchronization, sharing and data backup among subsystems in the existing new energy station monitoring system, and the poor real-time performance and data errors.

[0005] In order to achieve the above object, the technical solution adopted by the present invention is: The present invention provides a MYSQL data synchronization method, which is used in an open source middleware Canal, and the open source middleware Canal supports the MySQL Replication protocol; The MYSQL data synchronization method comprises: Monitor the MySQL Binlog log of the MYSQL relational database; When the MySQL Binlog log is monitored, the MySQL Binlog log is parsed and verified to obtain the data change message; Send a data change message to a preset user processing program; wherein the data change message is used to trigger the preset user processing program to synchronize the data in the MYSQL relational database to the preset target data source.

[0006] Furthermore, the process of monitoring the MySQL Binlog log of the MYSQL relational database is as follows: Send a Binlog dump command to a MYSQL relational database; wherein the Binlog dump command is used to trigger the MYSQL relational database to push the MySQL Binlog log to the open source middleware Canal.

[0007] Furthermore, the process of parsing and verifying the Mysql Binlog log to obtain the data change message is as follows: Parse the Mysql Binlog log to obtain data change content and record the current binlog position information; Perform consistency check on the current binlog location information and the binlog location information of the MYSQL relational database to obtain a check result; If the verification result is consistent, the data change content is packaged according to a preset data format to obtain a data change message.

[0008] Furthermore, the preset user processing program is a data processing program written in a preset programming language; wherein the data processing program is used to parse data change messages, convert data formats, and establish a communication connection with a preset target data source.

[0009] Furthermore, the data change message is used to trigger a preset user processing program to synchronize the data in the MYSQL relational database to a preset target data source, as follows: Parse the data change message through the preset user processing program, convert the data format, and obtain the data to be synchronized; The target data source engine is called through a preset user processing program to write the data to be synchronized into the preset target data source.

[0010] Furthermore, the preset target data sources include Mysql database, real-time database, Redis cache, message middleware and ES.

[0011] The present invention also provides a MYSQL data synchronization system, which is used in an open source middleware Canal, and the open source middleware Canal supports the MySQL Replication protocol; The MYSQL data synchronization system includes: The monitoring module is used to monitor the Mysql Binlog log of the MYSQL relational database; The verification module is used to parse and verify the Mysql Binlog log of the MYSQL relational database when monitoring it, and obtain the data change message; The synchronization module is used to send a data change message to a preset user processing program; wherein the data change message is used to trigger the preset user processing program to synchronize the data in the MYSQL relational database to the preset target data source.

[0012] The present invention also provides a MYSQL data synchronization device, comprising: a processor suitable for executing a computer program; A computer-readable storage medium, wherein a computer program is stored in the computer-readable storage medium, and when the computer program is executed by the processor, the MYSQL data synchronization method is executed.

[0013] The present invention also provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the MYSQL data synchronization method is implemented.

[0014] The present invention also provides a computer program product, which includes a computer program, and when the computer program is executed by a processor, the MYSQL data synchronization method is implemented.

[0015] Compared with the prior art, the present invention has the following beneficial effects: The MYSQL data synchronization method provided by the present invention parses and verifies the MysqlBinlog log of the MYSQL relational database monitored, obtains data change messages, and accurately and completely captures the data change messages, thereby helping to maintain the consistency between the MySQL database and the target data source during the data synchronization process; and synchronizes the data in the MYSQL relational database to the preset target data source based on the data change message, thereby realizing data synchronization between the MySQL database and various types of target data sources, ensuring the real-time and high reliability of the data, effectively reducing the risk of data loss or erroneous synchronization, thereby realizing secure synchronization and sharing of data between systems; the present invention adopts the open source middleware Canal to realize data synchronization between the MySQL database and the target data source in a non-invasive manner, without any modification or configuration of the MySQL database; at the same time, it can meet the synchronization requirements of full data or incremental data.

[0016] The MYSQL data synchronization system, MYSQL data synchronization device, computer-readable storage medium and computer program product provided by the present invention have all the advantages of the above-mentioned MYSQL data synchronization method. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, 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 application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0018] Figure 1 A flowchart of the MYSQL data synchronization method provided in Example 1; Figure 2 A structural block diagram of the MYSQL data synchronization system provided in Example 2; Figure 3 This is a structural block diagram of the MYSQL data synchronization device provided in Example 3. DETAILED DESCRIPTION

[0019] In order to make the technical problems, technical solutions and beneficial effects solved by this application clearer, the technical solutions in the embodiments of this application will be described clearly and completely in combination with the drawings in the embodiments of this application; obviously, the described embodiments are only part of the embodiments of this application, not all of them. Based on the embodiments in this application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0020] Example 1 This embodiment 1 provides a MYSQL data synchronization method, which is used in an open source middleware Canal. The open source middleware Canal supports the MySQL Replication protocol, that is, it can simulate the Replication protocol based on the MySQL Binlog log to synchronize data.

[0021] As attached Figure 1 As shown, the MYSQL data synchronization method comprises the following steps: Step 1: Establish a communication connection between the open source middleware Canal and the MYSQL relational database. Specifically, based on the address, port or username and password information of the MYSQL relational database, establish a communication connection between the open source middleware Canal and the MYSQL relational database.

[0022] Step 2: Use the open source middleware Canal to monitor the Mysql Binlog log of the MYSQL relational database. The Mysql Binlog log is a log file in the MySQL relational database, which is used to record all operations that modify data on the database server, including data definition language (DDL) operations and data manipulation language (DML) operations, such as INSERT, UPDATE, and DELETE; the Mysql Binlog log is stored in binary format.

[0023] Specifically, the process of monitoring the MySQL Binlog log of the MYSQL relational database is as follows: Step 21. The open source middleware Canal simulates the behavior of MySQL slave and sends the Binlog dump command to the MYSQL relational database.

[0024] Step 22, the MYSQL relational database receives and responds to the Binlog dump command, and pushes the MySQL Binlog log to the open source middleware Canal.

[0025] Step 3: When the open source middleware Canal monitors the MySQL Binlog log, it parses and verifies the MySQL Binlog log to obtain the data change message. It should be noted that by introducing the open source middleware Canal, the open source middleware Canal will enable monitoring of the MYSQL Binlog log by default. Once the data changes, it will immediately read the data and send it to the user processing program. The user processing program will immediately process and write it to the target source, ensuring the real-time nature of the data.

[0026] Specifically, the steps for parsing and verifying the Mysql Binlog log to obtain data change messages are as follows: Step 31, parse the Mysql Binlog log to obtain data change content, and record the current binlog position information; wherein, parse the Mysql Binlog log to parse the MysqlBinlog log in binary format into text information, and then read the header information, event type and specific data change content of the Mysql Binlog log, and record the currently parsed binlog position information; specifically, the binlog position information includes the binlog file name and the synchronization position offset.

[0027] Step 32, the current binlog position information is checked for consistency with the Binlog position information of the MYSQL relational database to ensure the integrity and accuracy of the data and obtain a verification result; it should be noted that the consistency check can also be performed by checking whether the format of the binlog event is correct, verifying whether the data in the event conforms to the data type and constraints of MySQL, or verifying that the logical relationship between the binlog events is correct to ensure data consistency.

[0028] Step 33: If the verification result is consistent, encapsulate the data change content according to the preset data format to obtain a data change message; preferably, the preset data format is JSON; if the verification result is inconsistent, discard the Mysql Binlog log and return to step 2.

[0029] Step 4: Use the open source middleware Canal to send a data change message to a preset user processing program, wherein the data change message is used to trigger the preset user processing program to synchronize the data in the MYSQL relational database to the preset target data source.

[0030] It should be noted that the preset user processing program is a data processing program written in a preset programming language; preferably, the preset programming language is JAVA; wherein the data processing program is used to parse data change messages, convert data formats, and establish a communication connection with a preset target data source; the preset user processing program is triggered by the data change message to synchronize the data in the MYSQL relational database to the preset target data source, so that the data flow is based on the generation of messages, and the messages themselves can guarantee high reliability, thereby ensuring high reliability of the data.

[0031] Step 5: parse the data change message through the preset user processing program, convert the data format, and obtain the data to be synchronized. Specifically, the process of obtaining the data to be synchronized is as follows: Step 51: Use rowChage.getEventType() to obtain the type of data change message through the preset user processing program, and read the changed data content; wherein, the type of data change message includes INSERT, UPDATE or DELETE, and the read changed data content is the MYSQL SQL statement.

[0032] Step 52: Obtain the data content to be synchronized according to the type of the acquired data change message and the read changed data content; perform data format conversion on the data content to be synchronized to obtain the data to be synchronized.

[0033] Step 6: Call the target data source engine through the preset user processing program to write the data to be synchronized to the preset target data source. The preset target data source includes Mysql database, real-time database, Redis cache, message middleware and ES.

[0034] The MYSQL data synchronization method described in this embodiment 1 implements the MySQLReplication protocol through the open source middleware Canal, disguises itself as a slave library Slave, and sends a Binlog dump command to the master library Master. After receiving the Binlog dump command, the master library Master starts to push the MySQL Binlog log to the open source middleware Canal; secondly, after the open source middleware Canal receives the MySQL Binlog log, it parses the MySQL Binlog log, and then synchronizes the data in the MYSQL relational database to various preset target data sources; the method can realize the synchronization of the full data and real-time incremental data of the MYSQL relational database, ensuring the quasi-real-time and high reliability of the data.

[0035] In this embodiment 1, the progress of synchronization is determined by comparing the current binlog position information obtained by analysis with the Binlog position information of the MYSQL relational database, thereby ensuring data consistency; when the open source middleware Canal parses and verifies the MysqlBinlog log, the binlog event of the MYSQL relational database is used to verify the content to ensure data integrity and accuracy; specifically, the open source middleware Canal is connected to the MYSQL relational database, and the binlog log is pulled; the open source middleware Canal parses the binlog event and records the current synchronization position information; the open source middleware Canal sends the parsed data changes to the consumer; after the consumer confirms that the data processing is successful, Canal updates the synchronization position, indicating that the data has been safely processed and does not need to be processed again.

[0036] Example 2 As attached Figure 2 As shown, this embodiment 2 provides a MYSQL data synchronization system, including a communication connection module, a monitoring module, a parsing and verification module and a synchronization module; The communication connection module is used to establish a communication connection between the open source middleware Canal and the MYSQL relational database; the monitoring module is used to monitor the Mysql Binlog log of the MYSQL relational database; the parsing and verification module is used to parse and verify the Mysql Binlog log when the open source middleware Canal monitors the Mysql Binlog log to obtain the data change message; the synchronization module is used to send the data change message to the preset user processing program; the data change message is parsed by the preset user processing program, and the data format is converted to obtain the data to be synchronized; the target data source engine is called by the preset user processing program, and the data to be synchronized is written to the preset target data source.

[0037] Example 3 As attached Figure 3 As shown, this embodiment 3 provides a MYSQL data synchronization device, including: a memory for storing a computer program; a processor for implementing the steps of the MYSQL data synchronization method when executing the computer program.

[0038] When the processor executes the computer program, the steps of the above-mentioned MYSQL data synchronization method are implemented, for example: Establish a communication connection between the open source middleware Canal and the MYSQL relational database; monitor the Mysql Binlog log of the MYSQL relational database; when the open source middleware Canal monitors the Mysql Binlog log, parse and verify the MysqlBinlog log to obtain a data change message; send the data change message to a preset user processing program; parse the data change message through the preset user processing program, and convert the data format to obtain the data to be synchronized; call the target data source engine through the preset user processing program, and write the data to be synchronized to the preset target data source.

[0039] Alternatively, when the processor executes the computer program, the functions of each module in the above-mentioned MYSQL data synchronization system are realized, for example: The communication connection module is used to establish a communication connection between the open source middleware Canal and the MYSQL relational database; the monitoring module is used to monitor the Mysql Binlog log of the MYSQL relational database; the parsing and verification module is used to parse and verify the Mysql Binlog log when the open source middleware Canal monitors the Mysql Binlog log to obtain the data change message; the synchronization module is used to send the data change message to the preset user processing program; the data change message is parsed by the preset user processing program, and the data format is converted to obtain the data to be synchronized; the target data source engine is called by the preset user processing program, and the data to be synchronized is written to the preset target data source.

[0040] Exemplarily, the computer program may be divided into one or more modules / units, which are stored in the memory and executed by the processor to complete the present invention. The one or more modules / units may be a series of computer program instruction segments capable of completing preset functions, and the instruction segments are used to describe the execution process of the computer program in the MYSQL data synchronization device.

[0041] For example, the computer program can be divided into a communication connection module, a monitoring module, a parsing and verification module and a synchronization module, and the specific functions of each module are as follows: a communication connection module is used to establish a communication connection between the open source middleware Canal and the MYSQL relational database; a monitoring module is used to monitor the Mysql Binlog log of the MYSQL relational database; a parsing and verification module is used to parse and verify the Mysql Binlog log when the open source middleware Canal monitors the Mysql Binlog log to obtain a data change message; a synchronization module is used to send a data change message to a preset user processing program; the data change message is parsed by the preset user processing program, and the data format is converted to obtain the data to be synchronized; the target data source engine is called by the preset user processing program to write the data to be synchronized to the preset target data source.

[0042] The MYSQL data synchronization device may be a computing device such as a desktop computer, a notebook, a PDA, and a cloud server. The MYSQL data synchronization device may include, but is not limited to, a processor and a memory. Those skilled in the art will appreciate that the above are examples of MYSQL data synchronization devices and do not constitute a limitation on the MYSQL data synchronization device. The MYSQL data synchronization device may include more components than the above, or a combination of certain components, or different components. For example, the MYSQL data synchronization device may also include input and output devices, network access devices, buses, etc.

[0043] The processor may be a central processing unit (CPU), other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The general-purpose processor may be a microprocessor or any conventional processor, etc. The processor is the control center of the MYSQL data synchronization device, and uses various interfaces and lines to connect various parts of the entire MYSQL data synchronization device.

[0044] The memory may be used to store the computer programs and / or modules, and the processor implements various functions of the MYSQL data synchronization device by running or executing the computer programs and / or modules stored in the memory, and calling the data stored in the memory.

[0045] The memory may mainly include a program storage area and a data storage area, wherein the program storage area may store an operating system, an application required for at least one function (such as a sound playback function, an image playback function, etc.), etc.; the data storage area may store data created according to the use of the mobile phone (such as audio data, a phone book, etc.), etc. In addition, the memory may include a high-speed random access memory, and may also include a non-volatile memory, such as a hard disk, a memory, a plug-in hard disk, a smart memory card (SmartMediaCard, SMC), a secure digital (SecureDigital, SD) card, a flash card (FlashCard), at least one disk storage device, a flash memory device, or other volatile solid-state storage devices.

[0046] Example 4 This embodiment 4 also provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the MYSQL data synchronization method are implemented, for example: Establish a communication connection between the open source middleware Canal and the MYSQL relational database; monitor the Mysql Binlog log of the MYSQL relational database; when the open source middleware Canal monitors the Mysql Binlog log, parse and verify the MysqlBinlog log to obtain a data change message; send the data change message to a preset user processing program; parse the data change message through the preset user processing program, and convert the data format to obtain the data to be synchronized; call the target data source engine through the preset user processing program, and write the data to be synchronized to the preset target data source.

[0047] If the modules / units integrated in the MYSQL data synchronization system are implemented in the form of software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium.

[0048] Based on such understanding, the present invention implements all or part of the processes in the above-mentioned MYSQL data synchronization method, and can also be completed by instructing related hardware through a computer program, and the computer program can be stored in a computer-readable storage medium, and when the computer program is executed by a processor, the steps of the above-mentioned MYSQL data synchronization method can be implemented. Among them, the computer program includes computer program code, and the computer program code can be in source code form, object code form, executable file or preset intermediate form, etc.

[0049] The computer-readable storage medium may include: any entity or device capable of carrying the computer program code, recording medium, USB flash drive, mobile hard disk, magnetic disk, optical disk, computer memory, read-only memory (ROM), random access memory (RAM), electrical carrier signal, telecommunication signal and software distribution medium, etc.

[0050] It should be noted that the content contained in the computer-readable storage medium can be appropriately increased or decreased according to the requirements of legislation and patent practice in the jurisdiction. For example, in some jurisdictions, according to legislation and patent practice, computer-readable storage media do not include electrical carrier signals and telecommunication signals.

[0051] Example 5 This embodiment 5 provides a computer product, which includes a computer program, and the computer program is stored in a computer-readable storage medium; the processor of the MYSQL data synchronization device reads the computer program from the computer-readable storage medium, and the processor executes the computer program, so that the MYSQL data synchronization device can execute the MYSQL data synchronization method described in Embodiment 1, which will not be repeated here.

[0052] It should be noted that a person of ordinary skill in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be implemented by instructing related hardware through a computer program. The program can be stored in a computer-readable storage medium. When the program is executed, it can include the processes of the embodiments of the above-mentioned methods.

[0053] The Mysql data synchronization method of the present invention realizes data synchronization based on an open source middleware Canal supporting the MySQL Replication protocol, and has the advantages of quasi-real-time performance and high reliability. Specifically, the MYSQL relational database stores all database operations in the Mysql Binlog log in a multi-threaded asynchronous manner by default. The open source middleware Canal uses an asynchronous network model based on a netty framework, and its performance is very high. The open source middleware Canal monitors the Mysql Binlog log by simulating the Mysql slave library to start a thread, and when the change of the Mysql Binlog log is monitored, a read operation is immediately triggered to read the file content and process it, thereby ensuring the real-time performance of data synchronization. Specifically, the operation of reading a data stream is performed through a stream object; the read file content is the content in the MySQL binlog log file, which is binary data; the open source middleware Canal reads the Mysql Binlog log of the MYSQL relational database, verifies the Mysql Binlog log during the reading process, parses the file content, and converts it into an add, modify, and query operation on a target data source, thereby updating the data to a preset target data source, so that the preset target data source and the MYSQL relational database are always guaranteed to be completely consistent.

[0054] In the present invention, when the target data source and the MYSQL relational database are synchronized for data update, the full synchronization method is adopted for the first data update; the subsequent synchronization operations adopt the incremental synchronization method; it should be noted that the process of implementing the full data synchronization is: reading all the contents of the mysql binlog log file to be synchronized once, parsing and processing it, and writing it to the target data source, thereby realizing the full synchronization; the process of realizing the incremental data synchronization is: only reading the newly added content in the mysql binlog log, parsing and processing it, and writing it to the target data source, realizing the incremental synchronization from the page.

[0055] The above embodiment is only one of the implementation methods that can realize the technical solution of the present invention. The scope of protection claimed by the present invention is not limited only to this embodiment, but also includes changes, replacements and other implementation methods that can be easily thought of by any technician familiar with the technical field within the technical scope disclosed by the present invention.

Claims

1. A MYSQL data synchronization method, characterized in that: Used in the open source middleware Canal, which supports the MySQL Replication protocol; The MYSQL data synchronization method comprises: Monitor the MySQL Binlog log of the MYSQL relational database; When the MySQL Binlog log is monitored, the MySQL Binlog log is parsed and verified to obtain the data change message; Send a data change message to a preset user processing program; wherein the data change message is used to trigger the preset user processing program to synchronize the data in the MYSQL relational database to the preset target data source.

2. A MYSQL data synchronization method according to claim 1, characterized in that: The process of monitoring the MySQL Binlog log of the MYSQL relational database is as follows: Send a Binlog dump command to a MYSQL relational database; wherein the Binlog dump command is used to trigger the MYSQL relational database to push the MySQL Binlog log to the open source middleware Canal.

3. A MYSQL data synchronization method according to claim 1, characterized in that: The process of parsing and verifying the Mysql Binlog log to obtain data change messages is as follows: Parse the Mysql Binlog log to obtain data change content and record the current binlog position information; Perform consistency check on the current binlog location information and the binlog location information of the MYSQL relational database to obtain a check result; If the verification result is consistent, the data change content is packaged according to a preset data format to obtain a data change message.

4. A MYSQL data synchronization method according to claim 1, characterized in that: The preset user processing program is a data processing program written in a preset programming language; wherein the data processing program is used to parse data change messages, convert data formats, and establish a communication connection with a preset target data source.

5. A MYSQL data synchronization method according to claim 1, characterized in that: The data change message is used to trigger the preset user processing program to synchronize the data in the MYSQL relational database to the preset target data source, which is as follows: Parse the data change message through the preset user processing program, convert the data format, and obtain the data to be synchronized; The target data source engine is called through a preset user processing program to write the data to be synchronized into the preset target data source.

6. A MYSQL data synchronization method according to claim 1, characterized in that: The preset target data sources include Mysql database, real-time database, Redis cache, message middleware and ES.

7. A MYSQL data synchronization system, characterized in that: Used in the open source middleware Canal, which supports the MySQL Replication protocol; The MYSQL data synchronization system includes: The monitoring module is used to monitor the Mysql Binlog log of the MYSQL relational database; The verification module is used to parse and verify the Mysql Binlog log of the MYSQL relational database when monitoring it, and obtain the data change message; The synchronization module is used to send a data change message to a preset user processing program; wherein the data change message is used to trigger the preset user processing program to synchronize the data in the MYSQL relational database to the preset target data source.

8. A MYSQL data synchronization device, characterized in that: include: a processor suitable for executing a computer program; A computer-readable storage medium, wherein a computer program is stored in the computer-readable storage medium, and when the computer program is executed by the processor, the MYSQL data synchronization method according to any one of claims 1 to 6 is executed.

9. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the MYSQL data synchronization method as described in any one of claims 1 to 6 is implemented.

10. A computer program product, characterized in that The computer program product comprises a computer program, and when the computer program is executed by a processor, the MYSQL data synchronization method according to any one of claims 1 to 6 is implemented.

Images