Method, system, equipment and medium for MySQL to realize real-time synchronization of library data through Binlog

Abstract

The invention discloses a method, a system, equipment and a medium for MySQL to realize library data real-time synchronization through Binlog, and the method comprises the following steps: creating a MysqlBinLogListener instance, and transmitting configuration information conf to initialize the MysqlBinLogListener instance; for a database table which needs to be synchronized, calling a regListener method to register a corresponding monitor; and calling a parse method to start a monitoring and consumption process. The method, the system, the equipment and the medium can realize real-time synchronization of data between the two databases.

Description

Technical Field

[0001] The present invention belongs to the technical field of databases, and relates to a method, system, device and medium for MySQL to achieve real-time synchronization of database data through Binlog. Background Art

[0002] In today's enterprise-level applications and data processing scenarios, it is often necessary to maintain data consistency and real-time performance between different databases. For example, in a distributed system, there may be a master database for processing major business operations, while a slave database is used for backup, data analysis, or other specific purposes. Traditional data synchronization methods often have problems such as high latency, low efficiency, and complex configuration, and cannot meet business scenarios with high real-time requirements. MySQL's Binlog records all changes to the database, making it possible to achieve efficient and real-time data synchronization. However, how to accurately parse and use Binlog to achieve real-time data synchronization between two databases is still a technical problem that needs to be solved. Summary of the invention

[0003] The purpose of the present invention is to overcome the shortcomings of the above-mentioned prior art and provide a method, system, device and medium for realizing real-time synchronization of database data in MySQL through Binlog. The method, system, device and medium can realize real-time synchronization of data between two databases.

[0004] To achieve the above object, the present invention discloses a method for implementing real-time synchronization of database data in MySQL through Binlog, comprising:

[0005] Create a MysqlBinLogListener instance and pass in the configuration information conf for initialization;

[0006] For the database table that needs to be synchronized, call the regListener method to register the corresponding listener;

[0007] Call the parse method to start the monitoring and consumption process.

[0008] The method for implementing real-time synchronization of database data in MySQL through Binlog described in the present invention is further improved in that:

[0009] Furthermore, in the process of calling the regListener method to register the corresponding listener, the field information of the database table is obtained and saved, and the listener is associated with the database table.

[0010] Furthermore, the process of calling the parse method to start the monitoring and consumption process is:

[0011] Connect the BinaryLogClient instance to the MySQL server and start receiving Binlog events;

[0012] When a TABLE_MAP event is received, the relevant information of the database table is recorded;

[0013] For WRITE events, UPDATE events, and DELETE events, the relevant data of the WRITE events, UPDATE events, and DELETE events are converted into BinLogItem and put into the blocking queue queue;

[0014] The consumer thread takes BinLogItem from the blocking queue, finds the corresponding listener list according to the dbTable string, and calls the onEvent method of the listener in the listener list for processing. The listener converts the received insert operation, update operation or delete operation into the corresponding SQL statement and executes it in the target database to achieve real-time data synchronization.

[0015] Furthermore, the initial variables in the process of creating a MysqlBinLogListener instance and passing in the configuration information conf for initialization include but are not limited to: the blocking queue queue, the Multimap and Map of the listener.

[0016] The present invention discloses a system for implementing real-time synchronization of database data in MySQL through Binlog, comprising:

[0017] Create a module to create a MysqlBinLogListener instance and pass in the configuration information conf for initialization;

[0018] The first calling module is used to call the regListener method to register the corresponding listener for the database table that needs to be synchronized;

[0019] The second calling module is used to call the parse method to start the monitoring and consumption process.

[0020] The further improvement of the system for implementing real-time synchronization of database data by MySQL through Binlog of the present invention is:

[0021] Furthermore, in the process of calling the regListener method to register the corresponding listener, the field information of the database table is obtained and saved, and the listener is associated with the database table.

[0022] Furthermore, the second calling module includes:

[0023] The receiving module is used to connect the BinaryLogClient instance to the MySQL server and start receiving Binlog events;

[0024] The recording module is used to record the relevant information of the database table when receiving the TABLE_MAP event;

[0025] The writing module is used to convert the relevant data of the WRITE event, UPDATE event and DELETE event into BinLogItem and put it into the blocking queue queue;

[0026] The synchronization module is used for the consumer thread to take out the BinLogItem from the blocking queue, find the corresponding listener list according to the dbTable string, and call the onEvent method of the listener in the listener list for processing. The listener converts the received insert operation, update operation or delete operation into the corresponding SQL statement and executes it in the target database to achieve real-time synchronization of data.

[0027] Furthermore, the initial variables in the process of creating a MysqlBinLogListener instance and passing in the configuration information conf for initialization include but are not limited to: the blocking queue queue, the Multimap and Map of the listener.

[0028] The present invention discloses a computer device, comprising a memory, a processor and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, the steps of the method for MySQL to achieve real-time synchronization of library data through Binlog are implemented.

[0029] The present invention discloses 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 method for implementing real-time synchronization of database data by MySQL through Binlog are implemented.

[0030] The present invention has the following beneficial effects:

[0031] The method, system, device and medium for MySQL to achieve real-time synchronization of library data through Binlog of the present invention can capture the change operation of the database in real time by directly monitoring the Binlog of MySQL during specific operation, and synchronize the data to the target database almost without delay, so as to meet the business scenarios with high requirements for data real-time performance. In addition, the present invention adopts a multi-threaded approach to process the consumption of events, improves the efficiency of data synchronization, can quickly process a large number of Binlog events, and avoids data backlog. In addition, in actual operation, only the connection information of the source database and the target database and the table information to be synchronized need to be provided, and data synchronization can be achieved through a simple registration listener operation, which reduces the complexity of configuration.

[0032] Furthermore, the present invention utilizes mechanisms such as blocking queues and thread pools to ensure orderly data processing and system stability, and can reliably synchronize data even in high concurrency situations, reducing the possibility of data loss and errors. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] The accompanying drawings constituting a part of the present invention are used to provide a further understanding of the present invention. The exemplary embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute an improper limitation of the present invention. In the accompanying drawings:

[0034] Figure 1 The present invention is a flow chart of the method. DETAILED DESCRIPTION

[0035] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0036] In the description of the present invention, it should be understood that the terms “include” and “comprises” indicate the presence of described features, wholes, steps, operations, elements and / or components, but do not exclude the presence or addition of one or more other features, wholes, steps, operations, elements, components and / or collections thereof.

[0037] It should also be understood that the terms used in the present specification are only for the purpose of describing specific embodiments and are not intended to limit the present invention. As used in the present specification and the appended claims, unless the context clearly indicates otherwise, the singular forms "a", "an" and "the" are intended to include plural forms.

[0038] It should be further understood that the term "and / or" used in the present specification and the appended claims refers to any combination of one or more of the associated listed items and all possible combinations, and includes these combinations. For example, A and / or B can represent: A exists alone, A and B exist at the same time, and B exists alone. In addition, the character " / " in the present invention generally indicates that the associated objects are in an "or" relationship.

[0039] It should be understood that, although the terms first, second, third, etc. may be used to describe preset ranges, etc. in the embodiments of the present invention, these preset ranges should not be limited to these terms. These terms are only used to distinguish preset ranges from each other. For example, without departing from the scope of the embodiments of the present invention, the first preset range may also be referred to as the second preset range, and similarly, the second preset range may also be referred to as the first preset range.

[0040] The word "if" as used herein may be interpreted as "at the time of" or "when" or "in response to determining" or "in response to detecting", depending on the context. Similarly, the phrases "if it is determined" or "if (stated condition or event) is detected" may be interpreted as "when it is determined" or "in response to determining" or "when detecting (stated condition or event)" or "in response to detecting (stated condition or event)", depending on the context.

[0041] In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, rather than all of the embodiments. The components of the embodiments of the present invention described and shown in the drawings here can usually be arranged and designed in various different configurations. Therefore, the following detailed description of the embodiments of the present invention provided in the drawings is not intended to limit the scope of the claimed invention, but merely represents selected embodiments of the present invention. Based on the embodiments in the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention.

[0042] Various structural schematic diagrams of the embodiments disclosed in the present invention are shown in the accompanying drawings. These figures are not drawn to scale, and some details are magnified and some details may be omitted for the purpose of clear expression. The shapes of various regions and layers shown in the figures and the relative sizes and positional relationships therebetween are only exemplary, and may deviate in practice due to manufacturing tolerances or technical limitations, and those skilled in the art may additionally design regions / layers with different shapes, sizes, and relative positions according to actual needs.

[0043] Embodiment 1

[0044] refer to Figure 1 The method for implementing real-time synchronization of database data in MySQL through Binlog of the present invention comprises the following steps:

[0045] MysqlBinLogListener class: Responsibilities as a database listener: Responsible for listening to MySQL Binlog events, handling different types of events accordingly, and implementing the BinaryLogClient.EventListener interface to receive and process Binlog events.

[0046] Initialization part:

[0047] In the constructor:

[0048] Connect the BinaryLogClient instance to the MySQL server and set the relevant connection parameters, such as the host address (conf.getHost()), port number (conf.getPort()), username (conf.getUsername()), and password (conf.getPasswd()).

[0049] Configure EventDeserializer to parse Binlog event data.

[0050] Initialize important member variables, including:

[0051] A blocking queue queue (capacity 100,000) used to store Binlog event items.

[0052] A Multimap (listeners) used to store listeners corresponding to the data table.

[0053] A Map (dbTableCols) used to store database table column information.

[0054] Thread pool consumer, the number of threads is consumerThreads, and the default value is obtained from BinLogConstants.consumerThreads.

[0055] Monitoring processing part (onEvent method):

[0056] This method is the core processing logic of the listener and will be called when a Binlog event is received.

[0057] First, handle the event differently according to its type:

[0058] For the TABLE_MAP event, obtain the database and table names and combine them into a dbTable string for subsequent operations.

[0059] For WRITE (insert operation), UPDATE (update operation) and DELETE (delete operation) events, the corresponding processing is performed respectively. For example, for insert operation, the inserted row data is obtained from WriteRowsEventData, and then a BinLogItem object is created based on the table column information stored in dbTableCols and placed in the queue. There is a similar processing logic for update and delete operations, except that data is obtained from different event data types and BinLogItem is created.

[0060] Registration listening part (regListener method):

[0061] Used to register a listener for a specific database table. Receive the database name db, table name table and listener instance listener as parameters.

[0062] First, get the combined dbTable string through the getdbTable method, then get the field set cols of the database table (obtained through the getColMap method), and save the field information to dbTableCols, and save the listener to listeners, so that the corresponding listener can be notified when receiving related events.

[0063] Parsing and consumption part (parse method):

[0064] First, register the current listener with BinaryLogClient so that it can receive Binlog events.

[0065] Then, multiple threads (the number is consumerThreads) are started to consume the BinLogItem in the queue. After each thread takes the BinLogItem from the queue, it finds the corresponding listener list according to the dbTable of the item and calls the onEvent method of each listener to handle the event. Finally, it connects to the MySQL server to start listening to the Binlog event.

[0066] Based on the above, the present invention specifically comprises the following steps:

[0067] 1) Create a MysqlBinLogListener instance and pass in the configuration information conf for initialization.

[0068] 2) For the database table that needs to be synchronized, call the regListener method to register the corresponding listener. During the registration process, the field information of the database table is obtained and saved, and the listener is associated with the database table.

[0069] 3) Call the parse method to start the monitoring and consumption process;

[0070] The specific process of step 3) is:

[0071] 31) Connect the BinaryLogClient instance to the MySQL server and start receiving Binlog events.

[0072] 32) When a TABLE_MAP event is received, the relevant information of the database table is recorded.

[0073] 33) For WRITE events, UPDATE events, and DELETE events, the relevant data of the WRITE events, UPDATE events, and DELETE events are converted into BinLogItem and put into the blocking queue queue.

[0074] 34) The consumer thread takes the BinLogItem from the blocking queue, finds the corresponding listener list according to the dbTable string, and calls the onEvent method of the listener in the listener list for processing. The listener can synchronize the data to another database according to specific business needs. For example, the listener can convert the received insert, update or delete operation into the corresponding SQL statement and execute it in the target database to achieve real-time data synchronization.

[0075] Embodiment 2

[0076] The system for implementing real-time synchronization of database data in MySQL through Binlog of the present invention comprises:

[0077] Create a module to create a MysqlBinLogListener instance and pass in the configuration information conf for initialization;

[0078] The first calling module is used to call the regListener method to register the corresponding listener for the database table that needs to be synchronized;

[0079] The second calling module is used to call the parse method to start the monitoring and consumption process.

[0080] The further improvement of the system for implementing real-time synchronization of database data by MySQL through Binlog of the present invention is:

[0081] Furthermore, in the process of calling the regListener method to register the corresponding listener, the field information of the database table is obtained and saved, and the listener is associated with the database table.

[0082] Furthermore, the second calling module includes:

[0083] The receiving module is used to connect the BinaryLogClient instance to the MySQL server and start receiving Binlog events;

[0084] The recording module is used to record the relevant information of the database table when receiving the TABLE_MAP event;

[0085] The writing module is used to convert the relevant data of the WRITE event, UPDATE event and DELETE event into BinLogItem and put it into the blocking queue queue;

[0086] The synchronization module is used for the consumer thread to take out the BinLogItem from the blocking queue, find the corresponding listener list according to the dbTable string, and call the onEvent method of the listener in the listener list for processing. The listener converts the received insert operation, update operation or delete operation into the corresponding SQL statement and executes it in the target database to achieve real-time synchronization of data.

[0087] Furthermore, the initial variables in the process of creating a MysqlBinLogListener instance and passing in the configuration information conf for initialization include but are not limited to: the blocking queue queue, the Multimap and Map of the listener.

[0088] The division of modules in the embodiments of the present application is schematic and is only a logical function division. There may be other division methods in actual implementation. In addition, each functional module in each embodiment of the present application may be integrated into a processor, or may exist physically separately, or two or more modules may be integrated into one module. The above-mentioned integrated modules may be implemented in the form of hardware or in the form of software functional modules.

[0089] Embodiment 3

[0090] A computer device includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, the steps of the method for implementing real-time synchronization of database data by MySQL through Binlog are implemented, for example, including: creating a MysqlBinLogListener instance, and passing in configuration information conf for initialization; for the database table that needs to be synchronized, calling the regListener method to register the corresponding listener; calling the parse method to start the listening and consumption process. Among them, the memory may include a memory, such as a high-speed random access memory, and may also include a non-volatile memory, such as at least one disk memory, etc.; the processor, the network interface, and the memory are interconnected through an internal bus, and the internal bus may be an industrial standard architecture bus, a peripheral component interconnection standard bus, an extended industrial standard structure bus, etc. The bus can be divided into an address bus, a data bus, a control bus, etc. The memory is used to store programs. Specifically, the program may include a program code, and the program code includes computer operation instructions. The memory may include a memory and a non-volatile memory, and provide instructions and data to the processor.

[0091] Embodiment 4

[0092] A computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the method for implementing real-time synchronization of library data in MySQL through Binlog are implemented, including: creating a MysqlBinLogListener instance, and passing in configuration information conf for initialization; for database tables that need to be synchronized, calling the regListener method to register the corresponding listener; calling the parse method to start the listening and consumption process. Specifically, the computer-readable storage medium includes, but is not limited to, for example, volatile memory and / or non-volatile memory. The volatile memory may include random access memory (RAM) and / or cache memory (cache), etc. The non-volatile memory may include a read-only memory (ROM), a hard disk, a flash memory, an optical disk, a magnetic disk, etc.

[0093] Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, systems, or computer program products. Therefore, the present application may adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application may adopt the form of a computer program product implemented in one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) that include computer-usable program code.

[0094] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0095] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0096] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0097] Those skilled in the art will readily appreciate other embodiments of the present invention after considering the specification and disclosure of the invention. This application is intended to cover any variations, uses or adaptations of the present invention that follow the general principles of the present invention and include common knowledge or customary techniques in the art that are not disclosed by the present invention. The specification and examples are to be considered exemplary only, and the true scope and spirit of the present invention are indicated by the following claims.

[0098] It should be understood that the present invention is not limited to the exact construction that has been described above and shown in the drawings and that various modifications and changes may be made without departing from the scope thereof. The scope of the present invention is limited only by the appended claims.

[0099] The above description is only a preferred embodiment of the present invention and does not limit the present invention in any way. Any simple modification, change and equivalent structural change made to the above embodiment based on the technical essence of the present invention still falls within the protection scope of the technical solution of the present invention.

Claims

1. A method for implementing real-time synchronization of database data in MySQL through Binlog, characterized in that: include: Create a MysqlBinLogListener instance and pass in the configuration information conf for initialization; For the database table that needs to be synchronized, call the regListener method to register the corresponding listener; Call the parse method to start the monitoring and consumption process.

2. The method for implementing real-time synchronization of database data in MySQL through Binlog according to claim 1, characterized in that: In the process of calling the regListener method to register the corresponding listener, the field information of the database table is obtained and saved, and the listener is associated with the database table.

3. The method for implementing real-time synchronization of database data in MySQL through Binlog according to claim 1, characterized in that: The process of calling the parse method to start the monitoring and consumption process is: Connect the BinaryLogClient instance to the MySQL server and start receiving Binlog events; When a TABLE_MAP event is received, the relevant information of the database table is recorded; For WRITE events, UPDATE events, and DELETE events, the relevant data of the WRITE events, UPDATE events, and DELETE events are converted into BinLogItem and put into the blocking queue queue; The consumer thread takes BinLogItem from the blocking queue, finds the corresponding listener list according to the dbTable string, and calls the onEvent method of the listener in the listener list for processing. The listener converts the received insert operation, update operation or delete operation into the corresponding SQL statement and executes it in the target database to achieve real-time data synchronization.

4. The method for implementing real-time synchronization of database data in MySQL through Binlog according to claim 1, characterized in that: The initial variables in the process of creating the MysqlBinLogListener instance and passing in the configuration information conf for initialization include but are not limited to: the blocking queue queue, the Multimap and Map of the listener.

5. A system for implementing real-time synchronization of database data in MySQL through Binlog, characterized in that: include: Create a module to create a MysqlBinLogListener instance and pass in the configuration information conf for initialization; The first calling module is used to call the regListener method to register the corresponding listener for the database table that needs to be synchronized; The second calling module is used to call the parse method to start the monitoring and consumption process.

6. The system for implementing real-time synchronization of database data using Binlog in MySQL according to claim 5, characterized in that: In the process of calling the regListener method to register the corresponding listener, the field information of the database table is obtained and saved, and the listener is associated with the database table.

7. The system for implementing real-time synchronization of database data using Binlog in MySQL according to claim 5, characterized in that: The second calling module includes: The receiving module is used to connect the BinaryLogClient instance to the MySQL server and start receiving Binlog events; The recording module is used to record the relevant information of the database table when receiving the TABLE_MAP event; The writing module is used to convert the relevant data of the WRITE event, UPDATE event and DELETE event into BinLogItem and put it into the blocking queue queue; The synchronization module is used for the consumer thread to take out the BinLogItem from the blocking queue, find the corresponding listener list according to the dbTable string, and call the onEvent method of the listener in the listener list for processing. The listener converts the received insert operation, update operation or delete operation into the corresponding SQL statement and executes it in the target database to achieve real-time synchronization of data.

8. The system for implementing real-time synchronization of database data using Binlog in MySQL according to claim 5, characterized in that: The initial variables in the process of creating the MysqlBinLogListener instance and passing in the configuration information conf for initialization include but are not limited to: the blocking queue queue, the Multimap and Map of the listener.

9. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the steps of the method for implementing real-time synchronization of database data by MySQL through Binlog as described in any one of claims 1 to 4 are implemented.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by the processor, the steps of the method for implementing real-time synchronization of database data by MySQL through Binlog as described in any one of claims 1 to 4 are implemented.

Images