Mysql database synchronization method

Abstract

The invention discloses a Mysql database synchronization method, which comprises the following steps that: S1, a DataSync program in a message generation module obtains sql change in a local database, and sends out the obtained change database sql in the form of a Transaction message transaction message; s2, the message receiving module enters a loop, and a'Transaction message 'transaction message is monitored and received; s3, after the transaction message is received, the interrupt unit judges whether the data body is executed locally or not, if yes, interrupt operation is executed, and exiting is carried out; if not, processing the data body; s4, the data cleaning unit checks whether the user configures cleaning rules or not, if the user configures the cleaning rules, the data cleaning unit compares the statements with the rules one by one, and corresponding content replacement and modification are carried out; and S5, establishing stable connection between the execution module and the target database, and accurately executing the cleaned executable sql statement into the target database so as to complete a data synchronization action.

Description

Technical Field

[0001] The invention belongs to the field of computer technology, and in particular relates to a Mysql database synchronization method. Background Art

[0002] Currently, database synchronization is performed using a master-slave replication method. At the same time, most synchronization software uses one of the master and slave servers as the synchronization source, making it difficult to achieve two-way synchronization.

[0003] There is no good solution to problems such as data directional correction, circular synchronization, and primary key conflicts during bidirectional data synchronization. Summary of the invention

[0004] In view of the above problems, the present invention provides a MySQL database synchronization method.

[0005] In order to solve the above technical problems, the present invention adopts the following technical solutions:

[0006] A Mysql database synchronization method is applied to a Mysql database synchronization system, wherein the Mysql database synchronization system includes a master device and a slave device, wherein the master device further includes a local database, a message generating module, a message receiving module, a service processing module and an execution module, wherein the service processing module further includes an interruption unit and a data cleaning unit, and wherein the slave device further includes a target database, including the following steps:

[0007] S1, the DataSync program in the message generation module obtains the SQL changes in the local database, and sends the obtained changed database SQL in the form of a "Transaction_message" transaction message;

[0008] S2, the message receiving module enters a loop to monitor and receive the "Transaction_message" transaction message;

[0009] S3, after receiving the transaction message, the interruption unit determines whether the data body has been executed locally. If it has been executed, it performs the interruption operation and exits; if it has not been executed, it processes the data body;

[0010] S4, the data cleaning unit obtains the processed "Transaction-message" from the interruption unit, disassembles it, and converts it into a specific executable SQL statement set; the data cleaning unit checks whether the user has configured the cleaning rules. If the user has configured the cleaning rules, the data cleaning unit compares these statements with the rules one by one, and replaces and modifies the corresponding content;

[0011] S5, the execution module establishes a stable connection with the target database, accurately executes the cleaned executable SQL statements into the target database, and then completes the data synchronization action.

[0012] In one possible implementation, the DataSync program in the message generation module in S1 obtains the sql changes in the local database, and sends the obtained changed database sql in the form of a "Transaction_message" transaction message. Specifically, it includes: obtaining the implementation based on the open source data synchronization framework Canal and the message queue RabbitMq. After Mysql is configured with master and standby, Canal disguises itself as a slave library MySQLslave, and the local database is used as the master library MySQL master. A dump protocol request is sent to the MySQL master. After receiving the dump protocol request, MySQLmaster starts to push the binary log to Canal. Canal receives and parses the Binlog log to obtain the changed data, and sends the obtained changed database sql in the form of a "Transaction_message" transaction message to the RabbitMg message queue.

[0013] In a possible implementation manner, monitoring and receiving the "Transaction_message" transaction message in S2 includes:

[0014] The message receiving module receives the data change "Transaction_message" transaction message generated in RabbitMq, parses the original message body and converts it into a business-usable SQL statement, and then encapsulates it into a usable data body for processing by the business processing module.

[0015] In a possible implementation, as described in S3, after receiving the transaction message, the interruption unit determines whether the data body has been executed locally. If it has been executed, the interruption operation is performed and the exit is made. If it has not been executed, the data body is processed including: the data body is numbered by means of a table record mark and recorded in the database table sync-sql. If it is found in the local database that the mark of the data body already exists in the table, the interruption operation is performed and the exit is made. On the contrary, if the mark of the data body is not found, the "Transaction-message" is split into specific internal sqls, the id therein is replaced with distributed, and a "sync-sql" record is generated. The generated "sync-sql" record is merged into the header of the "Transaction-message", and then the "sync-sql record" is inserted into the local database.

[0016] In a possible implementation, S5, the execution module establishes a stable connection with the target database, accurately executes the cleaned executable SQL statements into the target database, and then completes the data synchronization action including determining the database type and version.

[0017] In a possible implementation, S5, the execution module establishes a stable connection with the target database, accurately executes the cleaned executable SQL statements into the target database, and then completes the data synchronization action including configuring the connection parameters.

[0018] In a possible implementation, S5, the execution module establishes a stable connection with the target database, accurately executes the cleaned executable SQL statements into the target database, and then completes the data synchronization action including connection pool management.

[0019] The use of the present invention has the following beneficial effects: database data change messages can be effectively received, and the data messages are processed by SQL parsing, data filtering, data correction, etc., and then executed into the target database to complete data synchronization, and the internal business processing module of the system will perform loop interruption judgment to prevent data loop synchronization. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] Figure 1 This is a flowchart of the steps of the Mysql database synchronization method according to an embodiment of the present invention;

[0021] Figure 2 This is a principle block diagram of a MySQL database synchronization system used in a MySQL database synchronization method according to an embodiment of the present invention. DETAILED DESCRIPTION

[0022] 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.

[0023] Reference Figure 1 , which is a flowchart of a Mysql database synchronization method according to an embodiment of the present invention, and is applied to Figure 2 The Mysql database synchronization system shown, wherein the Mysql database synchronization system includes a master device and a slave device, the master device further includes a local database, a message generating module, a message receiving module, a business processing module and an execution module, the business processing module further includes an interrupt unit and a data cleaning unit, and the slave device further includes a target database, including the following steps:

[0024] S1, the DataSync program in the message generation module obtains the SQL changes in the local database and sends the obtained changed database SQL in the form of a "Transaction_message" transaction message;

[0025] S2, the message receiving module enters the loop, monitors and receives the "Transaction_message" transaction message;

[0026] S3, after receiving the transaction message, the interruption unit determines whether the data body has been executed locally. If it has been executed, it performs the interruption operation and exits; if it has not been executed, it processes the data body;

[0027] S4, the data cleaning unit obtains the processed "Transaction-message" from the interrupt unit, disassembles it, and converts it into a specific executable SQL statement set; the data cleaning unit checks whether the user has configured the cleaning rules. If the user has configured the cleaning rules, the data cleaning unit compares these statements with the rules one by one, and replaces and modifies the corresponding content. Before executing the SQL statement, it needs to be cleaned to ensure the legality and security of the statement. The cleaning process may include removing unnecessary spaces and comments, checking syntax errors, preventing SQL injection attacks, etc.; if the SQL statement matches the user-configured cleaning rules, the cleaning unit needs to perform data conversion to adapt to the format and content of the target database; it may include data type conversion, field mapping and other operations to ensure that the parameters are correctly bound to the SQL statement.

[0028] S5, the execution module establishes a stable connection with the target database, accurately executes the cleaned executable SQL statements into the target database, and then completes the data synchronization action.

[0029] In a specific application example, the DataSync program in the message generation module described in S1 obtains the sql changes in the local database, and sends the obtained changed database sql in the form of a "Transaction_message" transaction message. Specifically, it includes: obtaining the implementation based on the open source data synchronization framework Canal and the message queue RabbitMq. After Mysql is configured with master and standby, Canal disguises itself as a slave library MySQLslave, and the local database is used as the master library MySQL master. It sends a dump protocol request to MySQLmaster. After receiving the dump protocol request, MySQLmaster starts to push binary log to Canal. Canal receives and parses the Binlog log to obtain the changed data, and sends the obtained changed database sql in the form of a "Transaction_message" transaction message to the RabbitMg message queue.

[0030] In a specific application example, the monitoring and receiving of the "Transaction_message" transaction message in S2 includes: the message receiving module receives the data change "Transaction_message" transaction message generated in RabbitMq, and parses the original message body into a business-usable SQL statement, and then encapsulates it into a usable data body for processing by the business processing module.

[0031] In a specific application example, as described in S3, after receiving a transaction message, the interruption unit determines whether the data body has been executed locally. If it has been executed, the interruption operation is performed and the system exits. If it has not been executed, the data body is processed, including: the data body is numbered by means of a table record mark and recorded in the database table sync-sql. If the mark of the data body is found to already exist in the table in the local database, the interruption operation is performed and the system exits. On the contrary, if the mark of the data body is not found, the "Transaction-message" is split into specific internal sqls, the id therein is replaced with distributed, and a "sync-sql" record is generated. The generated "sync-sql" record is merged into the header of the "Transaction-message", and then the "sync-sql record" is inserted into the local database.

[0032] In a specific application example, S5 may include determining the database type and version, configuring connection parameters, determining the connection mode, handling connection exceptions, performing connection pool management, performing transaction management, data execution and error handling, etc. The database type and version need to be determined because different databases MySQL have different connection modes and characteristics. The execution module needs to accurately identify the type and version of the target database in order to adopt the corresponding connection driver and protocol. The configuration connection parameters include the address of the database server (IP address or domain name), port number, database name, user name and password, etc. These parameters need to be set correctly to ensure that the connection can be successfully established. Different connection modes can be used when setting the connection mode, such as JDBC. Each connection mode has its specific advantages and applicable scenarios. In the process of establishing a connection, various connection exceptions may be encountered, such as network problems, unavailable database server, incorrect user name or password, etc. The execution module needs to be able to capture these exceptions and provide appropriate error handling mechanisms, such as retrying the connection, recording error logs, etc. In order to improve performance and resource utilization, a connection pool can be used to manage database connections. The connection pool can create a certain number of connections in advance and allocate them to the execution module when needed. After they are used up, the connection will be recycled to avoid frequent creation and destruction of connections. For operations involving multiple SQL statements, transactions may be required to ensure data consistency. The execution module needs to be able to correctly start, commit, and roll back transactions to ensure that it can be restored to the previous state when an error occurs, so transaction management is required. Data execution is to load data into the target database. This can be achieved by executing SQL insert, update, or delete statements. During the execution process, attention should be paid to the integrity and consistency of the data. When executing SQL statements, various errors may be encountered, such as syntax errors, database constraint violations, etc. The execution module needs to be able to capture these errors and provide appropriate error handling mechanisms, such as recording error logs and returning error information to users.

[0033] The Mysql database synchronization method set up above can realize data synchronization from the local database on the master device to the target database on the slave device. If the message generating module, message receiving module, business processing module and execution module described above are also set on the slave device, the message generating module, message receiving module, business processing module and execution module can be implemented, and reverse data synchronization can be realized, thereby realizing two-way data synchronization.

[0034] It should be understood that the exemplary embodiments described herein are illustrative rather than restrictive. Although one or more embodiments of the present invention are described in conjunction with the accompanying drawings, it should be understood by those skilled in the art that various changes in form and detail may be made without departing from the spirit and scope of the present invention as defined by the appended claims.

Claims

1. A Mysql database synchronization method, characterized in that: Applied to a Mysql database synchronization system, the Mysql database synchronization system includes a master device and a slave device, the master device further includes a local database, a message generating module, a message receiving module, a business processing module and an execution module, the business processing module further includes an interrupt unit and a data cleaning unit, the slave device further includes a target database, including the following steps: S1, the DataSync program in the message generation module obtains the SQL changes in the local database, and sends the obtained changed database SQL in the form of a "Transaction_message" transaction message; S2, the message receiving module enters a loop to monitor and receive the "Transaction_message" transaction message; S3, after receiving the transaction message, the interruption unit determines whether the data body has been executed locally. If it has been executed, it performs the interruption operation and exits; if it has not been executed, it processes the data body; S4, the data cleaning unit obtains the "Transaction-message" that has been processed from the interruption unit, disassembles it, and converts it into a specific executable SQL statement set; the data cleaning unit checks whether the user has configured the cleaning rules. If the user has configured the cleaning rules, the data cleaning unit compares these statements with the rules one by one, and replaces and modifies the corresponding content; S5, the execution module establishes a stable connection with the target database, accurately executes the cleaned executable SQL statements into the target database, and then completes the data synchronization action.

2. The Mysql database synchronization method according to claim 1, characterized in that: The DataSync program in the message generation module described in S1 obtains the sql changes in the local database, and sends the obtained changed database sql in the form of a "Transaction_message" transaction message. Specifically, it includes: obtaining the implementation based on the open source data synchronization framework Canal and the message queue RabbitMq. After Mysql is configured with master and standby, Canal disguises itself as a slave library MySQLslave, and the local database is used as the master library MySQL master. It sends a dump protocol request to the MySQL master. After receiving the dump protocol request, MySQLmaster starts to push binary logs to Canal. Canal receives and parses the Binlog log to obtain the changed data, and sends the obtained changed database sql in the form of a "Transaction_message" transaction message to the RabbitMg message queue.

3. The Mysql database synchronization method according to claim 2, characterized in that: The monitoring and receiving of the "Transaction_message" transaction message in S2 includes: The message receiving module receives the data change "Transaction_message" transaction message generated in RabbitMq, parses the original message body and converts it into a business-usable SQL statement, and then encapsulates it into a usable data body for processing by the business processing module.

4. The Mysql database synchronization method according to claim 1, characterized in that: As described in S3, after receiving the transaction message, the interruption unit determines whether the data body has been executed locally. If it has been executed, the interruption operation is performed and exits; If not executed, the data body is processed including: marking the data body by table record marking and recording it in the database table sync-sql; if in the local database, it is found that the mark of the data body already exists in the table, the interrupt operation is executed and the exit is performed; otherwise, if the mark of the data body is not found, the "Transaction-message" is split into specific internal sqls, the id therein is replaced with distributed, and a "sync-sql" record is generated, the generated "sync-sql" record is merged into the header of the "Transaction-message", and then the "sync-sql record" is inserted into the local database.

5. The Mysql database synchronization method according to claim 1, characterized in that: S5, the execution module establishes a stable connection with the target database, accurately executes the cleaned executable SQL statements into the target database, and then completes the data synchronization action including determining the database type and version.

6. The Mysql database synchronization method according to claim 1, characterized in that: S5, the execution module establishes a stable connection with the target database, accurately executes the cleaned executable SQL statements into the target database, and then completes the data synchronization action including configuring the connection parameters.

7. The Mysql database synchronization method according to claim 1, characterized in that: S5, the execution module establishes a stable connection with the target database, accurately executes the cleaned executable SQL statements into the target database, and then completes the data synchronization action including connection pool management.

Images