CN110750594B

CN110750594B - Real-time cross-network database synchronization method based on mysql incremental log

Info

Publication number: CN110750594B
Application number: CN201910938282.2A
Authority: CN
Inventors: 李蔚
Original assignee: Shanghai Ismartv Network Technologies Co ltd
Current assignee: Shanghai Ismartv Network Technologies Co ltd
Priority date: 2019-09-30
Filing date: 2019-09-30
Publication date: 2023-05-30
Anticipated expiration: 2039-09-30
Also published as: CN110750594A

Abstract

The invention relates to a mysql incremental log-based real-time cross-network database synchronization method, which comprises the following steps: analyzing the increment log generated by mysql, and serially persistence the analyzed increment log as an event into a database; the server side of the machine room where the database server is located transmits the persistent data to the client side in the remote machine room by utilizing a socket long connection mode; and the client plays back the received persistent data sequentially and writes the persistent data into a local database, so that database synchronization across networks is realized. The invention can solve the fundamental problem of mysql public network synchronization, thereby realizing the data foundation for the targets of multiple data centers.

Description

Real-time cross-network database synchronization method based on mysql incremental log

Technical Field

The invention relates to the technical field of information, in particular to a mysql incremental log-based real-time cross-network database synchronization method.

Background

Currently, with the continuous development of internet technology, for an internet application, the carrying capacity of the internet application is continuously refreshed and improved along with the increase of the user quantity. The corresponding application is gradually expanded to single-machine room multi-server application, or even to multi-machine room multi-data center application from the original single-server application. Based on such changes, synchronization across machine room databases is becoming one of the important fundamental links to be considered. As a paradigm for open source databases, it is important to address cross-machine room synchronization of mysql. The traditional mysql master-slave synchronization is only suitable for a single network with stable network environment and no fluctuation, and cannot be realized when the network environment is faced with complex public network transmission.

At present, the synchronization of mysql itself has the following problems in a cross-network scenario:

1. problems caused by network delay instability: because of the departure from the lan, network transmission on the public network is inevitable, network delay and instability are necessarily present, and synchronization of mysql itself is not optimized for the cross-network scenario, thus various failures may occur.

2. Problems caused by inconsistent device loads in master-slave synchronization: the loads of the master device and the slave devices are different, so that the master device and the slave devices can be mutually influenced.

3. Synchronization failure problems caused by various conflicts: when the master and slave are synchronized, if the slave database is changed, the master and slave failure can be immediately caused because the mysql synchronization mechanism is a strong consistency principle.

4. Problem of synchronization policy: there are conventionally 3 strategies for master-slave synchronization, (1) synchronous replication: the synchronization is performed in a transaction manner, that is, any transaction executed by the master library includes the step of synchronizing data of all slave nodes. The method has the advantages that the data synchronization consistency can be ensured, and the disadvantage is that the performance of the master-slave db is sharply reduced when the db load is large. (2) asynchronous replication: the master library asynchronously synchronizes data to the slave library regardless of whether the slave library receives and processes. The method has the advantage that the synchronization abnormality does not affect the master-slave db performance. A disadvantage is that when the master fails, there may be situations where the data is not synchronized successfully. (3) semi-synchronous replication: similar to synchronous replication, the difference is that any transaction executed by the master includes at least one step of receiving binlog from the slave (the slave does not need to wait until execution is completed and then feeds back to the master). The method has the advantages that the performance of asynchronous replication and the accuracy of synchronous replication are considered to a certain extent. The disadvantage is that the performance is not completely asynchronous and therefore a load is placed on db. The master cannot know the actual execution condition of the slave node, so that the execution failure is still the case.

5. Problem of synchronization efficiency and synchronization delay: the playback strategy adopted by the slave node is single-thread sequential operation, which has the advantages of ensuring the sequential nature of data playback and ensuring the synchronous consistency of data, but has the disadvantages of insufficient synchronous efficiency and a certain synchronous delay caused by single-thread processing.

Disclosure of Invention

The invention provides a real-time cross-network database synchronization method based on mysql incremental logs, which can solve the fundamental problem of mysql public network synchronization, thereby realizing a data foundation for targets of multiple data centers.

The technical scheme adopted for solving the technical problems is as follows: the utility model provides a real-time cross-network database synchronization method based on mysql increment logs, which comprises the following steps:

(1) Analyzing the increment log generated by mysql, and serially persistence the analyzed increment log as an event into a database;

(2) The server side of the machine room where the database server is located transmits the persistent data to the client side in the remote machine room by utilizing a socket long connection mode;

(3) And the client plays back the received persistent data sequentially and writes the persistent data into a local database, so that database synchronization across networks is realized.

The step (1) specifically comprises the following steps: analyzing the increment logs in each database in batches, then persisting analysis results into a table in batches, after persistence is successful, storing the position of the increment log which is persisted currently, and if the program is restarted, starting processing by taking the position as an analysis point.

The fields of the table include: incremental log identification, incremental log location, database name, type, parsed raw data, and update time.

If any abnormal condition occurs in the persistence process, the retry is repeated, and if the number of times of occurrence of the abnormal condition reaches a retry threshold, an alarm is given.

The step (2) specifically comprises the following steps: loading remote client ip/port configuration, starting a socket long connection for each client, infinitely circularly loading the persistent data in batches, and transmitting the persistent data to the corresponding client through the socket long connection; after successful transmission, the current synchronized increment log position of the client is written into a data table for the initial loading position when starting each time.

The data table field includes: id. Client identification, processing delta log location, and update time.

The socket long connection realizes disconnection reconnection processing by monitoring whether the connection is effective or not, and realizes package sticking and unpacking processing by detecting the head and the tail of a protocol; when the persistent data is transmitted, the transmission data is subjected to serialization processing, encryption processing and compression processing.

And (3) the client in the step (3) establishes a socket monitor for receiving the connection of the server, judging whether the connection request ip is in the white list, if so, receiving the data transmitted by the server, and decoding the data, otherwise, rejecting the data.

The step (3) specifically comprises the following steps: the client performs data grouping on each database, converts the persistent data related to each database into sql, sequentially stores the sql into a list, and stores the sql in each database object; processing and importing playback work aiming at each database, waiting for the completion of the execution of all threads through a future mechanism, and feeding back a processing result to a server; when the processing procedure is imported, the server synchronizes the change to the client in real time after the presynchronization database structure changes; and when the batch import is carried out, a batch import mechanism of jdbc is adopted, and if the batch import fails, the import operation is carried out piece by piece.

And (3) the method further comprises the steps of retrying the batch synchronization each time in a failover mode and playing back the batch synchronization based on the incremental log sequence.

Advantageous effects

Due to the adoption of the technical scheme, compared with the prior art, the invention has the following advantages and positive effects: the incremental log generated by mysql is analyzed and persisted, then transmitted to the cross-machine-room client through socket, and the data is replayed and put in storage through a processing mechanism, so that the function of database synchronization is finally realized, the problems of instability and unsafe in the public network transmission of mysql master-slave synchronization are solved, and the cross-machine-room multi-data center is possible to be established.

Drawings

Fig. 1 is a diagram of an application architecture of the present invention.

Detailed Description

The invention will be further illustrated with reference to specific examples. It is to be understood that these examples are illustrative of the present invention and are not intended to limit the scope of the present invention. Further, it is understood that various changes and modifications may be made by those skilled in the art after reading the teachings of the present invention, and such equivalents are intended to fall within the scope of the claims appended hereto.

The embodiment of the invention relates to a mysql incremental log-based real-time cross-network database synchronization method, an application architecture of which is shown in fig. 1, and which adopts a high-availability strategy (a server is zk-based and a client is nginx-based) to ensure that no single-point risk exists, and the method comprises the following steps: analyzing the increment log generated by mysql, and serially persistence the analyzed increment log as an event into a database; the server side of the machine room where the database server is located transmits the persistent data to the client side in the remote machine room by utilizing a socket long connection mode; and the client plays back the received persistent data sequentially and writes the persistent data into a local database, so that database synchronization across networks is realized.

In the server program, there is an infinite loop program, after the mysql log is resolved, the mysql log is used as an event to be serially and permanently stored in db for subsequent positioning and synchronous use, and the method comprises the following steps: the binlog in each db is parsed in batches, and then the parsing results are persisted into a specific table in batches. Table field: the method comprises the steps of identifying binlog, identifying binlog position, identifying db, analyzing the original data by the type (dml (addition, deletion, modification)/ddl (data structure modification), and updating time, wherein in order to prevent the data volume from being too large, the lasting data before 30 days are cleaned through a timing program.

After the server program is started, the method comprises the following steps: loading remote client ip/port configuration, and starting a socket long connection for each client. And data is transmitted through the long connection, so that the transmission performance is improved, and the resource consumption caused by repeatedly opening and closing the connection is reduced. The socket long connection is used for processing the conditions of disconnection, reconnection, package sticking, package unpacking and the like. The disconnect reconnection handling mechanism is implemented based on monitoring whether the connection is active (heartbeat). The sticky package unpacking processing mechanism is realized based on a custom transmission protocol and a detection protocol head and tail. To facilitate binary transmission, the transmission data is serialized in a manner of a Protostuff mechanism. To ensure transmission security, AES encryption is performed on transmission data in the following specific manner: AES/CBC/PKCS5 packing. To reduce the size of the transmission data, the transmission data is compressed in a manner of a snpey mechanism. Each client starts a thread, loads the persistent event log data in an infinite loop and transmits the event log data to the corresponding client through a socket. When abnormal, it will retry indefinitely until successful. After success, the binlog position of the current synchronization of the client is written into a data table for the initial loading position at each start. Data table field definition: id, client identification, processing binlog location, update time.

After the client program is started, the method comprises the following steps: after starting, a socket monitor is established for receiving the connection of the server. And judging whether the connected request ip is in a white list or not, and rejecting the connection request ip if not. And carrying out decoding verification on the received binary data. And decoding the received batch of synchronous data.

The received synchronous data is replayed and put in storage to realize database synchronization, and the method comprises the following steps: because each db is to realize data playback, the execution sequence of sql of each db is required to be completely executed according to the original sequence, so that the basic requirements of performance and playback are considered, data grouping is carried out on each db, the db related persistent data are converted into sql, and the sql is stored in a list according to the sequence and is stored in each db object. At this time, a thread can be started for each db to process the import playback work. And then waiting for the completion of the execution of all threads through a future mechanism, and feeding back the processing result to the server. The client performs import preprocessing on the received sql data to enable the sql data to meet the execution requirement. For sql preprocessing, it is necessary to know the properties of certain fields, such as whether a key is primary, whether a field is time, etc. These will be synchronized to the client in real time by the server after the pre-synchronization db structure changes. Considering the import performance, batch import is implemented by adopting the batch mechanism of jdbc. If the batch import fails, the import operation is executed piece by piece. The import per each import ignores conflicts for importation (the probability of successful importation is higher than that of batch importation), and otherwise fails, the whole task fails. Aiming at the scene that dml execution speed is high and ddl execution speed is possibly low, different import timeout time is set, and balance is achieved on two points of ensuring successful import and finding problems in time.

In the embodiment, a mechanism is required to be adopted to ensure that the data of the database is subjected to binlog analysis, persistence, server sending, client receiving and client warehousing, so that the sequence of data synchronization is ensured, and the consistency of the data synchronization is ensured. And the retry processing is carried out on each batch synchronization in a failover mode, namely, one failure in the batch synchronization is considered to be failure. Playback is performed based on binlog order, ensuring data consistency. And aiming at partial conflict, neglecting processing is carried out, so that the probability of synchronization failure is reduced.

Aiming at the problem of mysql synchronization in the prior art, the following measures are adopted to treat the problems in the embodiment:

1. the problem caused by unstable network delay is solved.

The method and the system optimize transmission across networks, ensure high-efficiency and stable transmission, and ensure that accuracy and order of data synchronization are not affected even if the network fluctuates through a series of strategies.

A. The use of the tpc long connection transmission allows data synchronization, feedback to be implemented on a single channel, preventing a series of problems due to multiple handshakes.

B. Aiming at the situations of unstable network, instantaneous interruption and the like, a heartbeat mechanism is adopted. If no data is input and output within 30 seconds through one thread detection, a heartbeat packet is initiated, and if feedback is received within a specified time. The connection is considered normal. If no separation is received, the connection is considered abnormal. At this time, reconnection is initiated, and the data transmission tcp connection is reinitialized.

C. For the scenes of restarting, abnormality, overtime and the like, after each node successfully transmits each time, the last successful synchronous data event identification is subjected to persistence. This will be the initial value of the synchronized data event. Under any condition, the data transmission omission is avoided, and the final consistency of the synchronous data of the database is ensured.

2. The method solves the problem caused by inconsistent loads of the devices in the master-slave synchronization.

A. The server side adopts a mode of asynchronously analyzing the binlog of the master node. Then using a queue peak elimination synchronization mode. I.e. at any one time, only a quantitative batch of data is synchronized for a single client. The method can ensure that the synchronous quantity of the server is always in a controllable range, and does not cause burden to the main node, thereby influencing the external service quality.

B. The client adopts a single db to perform data playback by adopting a single thread, and is based on the controllable synchronous data volume of the server, so that the data playback of the client cannot burden the slave node, and the external service quality is affected.

3. And the problem of synchronization failure caused by various conflicts is solved.

A. Aiming at special conflict scenes such as main key conflict, insertion column conflict, insertion key conflict, deletion column/deletion key absence and the like when data are inserted, evading processing is carried out.

B. For the conflict scene, an avoidance process is adopted. Because the conflicts arising in the above-described operations are considered negligible from a business perspective. No forced verification is required.

4. The problem of the synchronization strategy is solved.

A. The strategy adopted by the embodiment is different from the existing synchronous copy, asynchronous copy and semi-synchronous copy. In the process of data synchronization, the master node and the slave node are not subjected to too large load (the data quantity for batch execution can be adjusted at any time), and normal external service is ensured. In addition, the final consistency of the data can be ensured, and the synchronization position is fed back to the master node for persistence after the slave node is successfully synchronized. The master node uses the data as an initial basis for synchronization.

B. Firstly, the server asynchronously analyzes the binlog of the master node and then drops the binlog into a queue. And the consumers of the service end send the service ends to the clients in batches through tcp long connection. After receiving the data to be synchronized, the client re-analyzes the data to be synchronized into executable sql, and then stores the data in batches. And feeding back the data synchronization result of the batch to the server. If the synchronization is successful, the server side will take the data of the next batch to synchronize, and will perform the value until the synchronization is successful. If the failure occurs, the server can retry indefinitely, and if the error reaches the threshold, the server alarms. In the whole process, the master node and the slave node normally provide services to the outside, and the synchronization occupies only a small part of resources.

5. The problems of synchronization efficiency and synchronization delay are solved.

A. The binlog is converted to the sql that is actually executed.

B. Wherein, insert divides 2 cases, if it includes the primary key field, then adopt the replay into to carry out, prevent the situation that appears the primary key conflict subsequently. Otherwise, the method is directly executed by insert.

C. The update is divided into 2 cases, and if the main key field is included in the update, the main key is used as a query condition to update. Otherwise, the full field is used as the query condition. The use of the former can accelerate update execution efficiency without full field matching.

D. Wherein delete is divided into 2 cases, if the main key field is included, then the main key is used as the query condition to delete. Otherwise, the full field is used as the query condition. The use of the former can speed up delete execution efficiency without full field matching.

E. The data is replayed and put in storage, and the method of executing the sql by the batch is adopted, so that the execution efficiency can be remarkably improved.

F. In addition to the dml operations described above, the ddl operations also cover that the structural changes of the data tables therein can be synchronized together. The difference is that the time-out time set for ddl operation is greater than that for dml operation. The rest of the processing mechanisms are consistent.

G. And the transaction consistency is not relied on for synchronization, so that the synchronization efficiency is improved.

H. During synchronization, batch synchronous data are grouped according to dbs, and each db is subjected to multi-thread batch playback and warehousing, so that the synchronization efficiency is improved.

It is easy to find that the incremental log generated by mysql is used, is analyzed and persistent, is transmitted in the public network through a socket, is sent to a cross-machine-room client, and is replayed and put in storage through a processing mechanism, so that the function of database synchronization is finally realized, the problems of instability and unsafe existing in the public network transmission of mysql master-slave synchronization are solved, and the cross-machine-room multi-data center is possible to be established.

Claims

1. A mysql incremental log-based real-time cross-network database synchronization method is characterized by comprising the following steps:

(1) Analyzing the increment log generated by mysql, and serially persistence the analyzed increment log as an event into a database; the method comprises the following steps: analyzing the increment logs in each database in batches, then persisting analysis results into a table in batches, after persistence is successful, storing the position of the increment log which is persisted currently, and if the program is restarted, starting processing by taking the position as an analysis point;

(2) The server side of the machine room where the database server is located transmits the persistent data to the client side in the remote machine room by utilizing a socket long connection mode; the method comprises the following steps: loading remote client ip/port configuration, starting a socket long connection for each client, infinitely circularly loading the persistent data in batches, and transmitting the persistent data to the corresponding client through the socket long connection; after successful transmission, the current synchronous increment log position of the client is written into a data table for the initial loading position when the client is started each time;

(3) The client plays back the received persistent data sequentially and writes the persistent data into a local database, so that database synchronization across networks is realized; the method comprises the following steps: the client performs data grouping on each database, converts the persistent data related to each database into sql, sequentially stores the sql into a list, and stores the sql in each database object; processing and importing playback work aiming at each database, waiting for the completion of the execution of all threads through a future mechanism, and feeding back a processing result to a server; when the processing procedure is imported, the server synchronizes the change to the client in real time after the presynchronization database structure changes; and when the batch import is carried out, a batch import mechanism of jdbc is adopted, and if the batch import fails, the import operation is carried out piece by piece.

2. The mysql delta log-based real-time cross-network database synchronization method of claim 1, wherein the fields of the table comprise: incremental log identification, incremental log location, database name, type, parsed raw data, and update time.

3. The mysql incremental log-based real-time cross-network database synchronization method of claim 1, wherein if any abnormal condition occurs during persistence, retry is repeated, and if the number of times of occurrence of the abnormal condition reaches a retry threshold, an alarm is given.

4. The mysql delta log real-time cross-network database synchronization method of claim 1, wherein the fields of the data table comprise: id. Client identification, processing delta log location, and update time.

5. The mysql incremental log-based real-time cross-network database synchronization method of claim 1, wherein the socket long connection realizes disconnection reconnection processing by monitoring whether the connection is valid or not, and realizes sticky package unpacking processing by detecting the head and the tail of a protocol; when the persistent data is transmitted, the transmission data is subjected to serialization processing, encryption processing and compression processing.

6. The synchronization method of the real-time cross-network database based on mysql incremental journals according to claim 1, wherein the client in the step (3) establishes a socket monitor for receiving the connection of the server and judging whether the request ip for connection is in a white list, if so, receiving the data transmitted by the server, and decoding the data, otherwise, rejecting the data.

7. The mysql incremental log-based real-time cross-network database synchronization method of claim 1, further comprising the step of performing retry processing and playback based on the incremental log sequence for each batch synchronization in a failover manner after step (3).