A method for bi-directional real-time data synchronization between MySQL and openGauss

Abstract

The application relates to a MySQL and openGauss bidirectional data real-time synchronization method. The method comprises the following steps: collecting a changed transaction of MySQL through binlog on the MySQL side, judging whether the transaction contains a mark or not, discarding the transaction containing the mark, sending the transaction not containing the mark to an openGauss application side, splicing the mark, and writing the mark into the openGauss; collecting a changed transaction of openGauss through decoderbufs on the openGauss side, judging whether the transaction contains a mark or not, discarding the transaction containing the mark, sending the transaction not containing the mark to a MySQL application side, splicing the mark, and writing the mark into the MySQL. The method can ensure that application data on the MySQL and the openGauss can be synchronized in a new library verification stage, and the data safety of users is effectively guaranteed.

Description

Technical Field

[0001] This invention belongs to the technical field of real-time data synchronization methods, and particularly relates to a method for bidirectional real-time data synchronization between MySQL and openGauss. Background Technology

[0002] When users replace a MySQL database with an openGauss database, the application will continue to run on the openGauss database after data migration. Until the user verifies that the new database fully meets application requirements, the backup application will continue to run on MySQL. Therefore, a method is needed to ensure that the application data on MySQL and openGauss can be synchronized. Summary of the Invention

[0003] In order to ensure that application data on MySQL and openGauss can be synchronized during the new database verification phase, this invention provides a new solution for bidirectional real-time data synchronization between MySQL and openGauss databases based on MySQL Binlog and PostgreSQL Decoderbufs.

[0004] The overall concept of the method of this invention is as follows:

[0005] The MySQL side collects change transactions from the binlog, determines whether the transaction contains a tag, discards change transactions containing tags, and sends change transactions without tags to the openGauss application side, concatenates the tag and writes it into openGauss.

[0006] The openGauss client collects change transactions from openGauss via decoderbufs, determines whether the transaction contains a tag, discards change transactions containing tags, and sends change transactions without tags to the MySQL application, which then appends the tag and writes them into MySQL.

[0007] Specifically, this invention provides a method for real-time bidirectional data synchronization between MySQL and openGauss, the method comprising:

[0008] Step 1: The MySQL side collects the MySQL change transactions through binlog, determines whether the change transaction contains a tag, discards the change transaction containing a tag, and sends the change transaction without a tag to the openGauss application side, concatenates the tag and writes it into openGauss.

[0009] Step 2: The openGauss client collects the change transactions of openGauss through decoderbufs, determines whether the change transaction contains a tag, discards the change transaction containing a tag, and sends the change transaction without a tag to the MySQL application client, concatenates the tag and writes it into MySQL.

[0010] Furthermore, step one of the method for real-time bidirectional data synchronization between MySQL and openGauss in this invention includes:

[0011] S1. Configure MySQL Binlog service;

[0012] S2. Connect to the MySQL database and create a mark_down table under the schema that needs to be synchronized;

[0013] The S3.java program connects to Binlog to collect change transaction SQL from the MySQL database;

[0014] S4. Determine whether the change transaction SQL contains the mark_down flag. If it does, discard the collected record. If not, send the collected record to the openGauss application.

[0015] After receiving the change record, the S5.openGauss application appends a mark_down flag to each record and then writes it to the openGauss library.

[0016] Furthermore, the configuration of the MySQL Binlog service described in step S1 of the above method for real-time bidirectional data synchronization between MySQL and openGauss includes:

[0017] S11. Adjust MySQL database permissions;

[0018] S12. Configure the parameters in the MySQL configuration file my.cnf;

[0019] S13. Restart the database using the command `service MySQL restart`.

[0020] Furthermore, in step S11 of the above method for real-time bidirectional data synchronization between MySQL and openGauss, the database permissions of MySQL are adjusted. These MySQL database permissions include: REPLICATION SLAVE, REPLICATIONCLIENT, SELECT, INSERT, UPDATE, and DELETE.

[0021] Furthermore, in step S12 of the above method for real-time bidirectional data synchronization between MySQL and openGauss, the configuration parameters in the MySQL configuration file my.cnf include configuring the log-bin option, that is, setting the binlog log directory; the log-bin can be directly set to mysql-bin, which means that its location is in the / var / lib / mysql / directory, or it can be set to an absolute path.

[0022] Furthermore, step two of the method for real-time bidirectional data synchronization between MySQL and openGauss in this invention includes:

[0023] S101.openGauss configures the decoderbufs service;

[0024] S102. Connect to the openGauss database and create a mark_down table under the schema that needs to be synchronized;

[0025] The S103.java program connects to decoderbufs to collect change transaction SQL from the openGauss database;

[0026] S104. Determine whether the change transaction SQL contains the mark_down flag. If it does, discard the collected record; otherwise, send the collected record to the MySQL application.

[0027] S105. After receiving the change record, the MySQL application appends a mark_down flag to the beginning of each record and then writes it to the MySQL database.

[0028] Furthermore, the openGauss configuration decoderbufs service described in step S101 of the above method for real-time bidirectional data synchronization between MySQL and openGauss includes:

[0029] S1011. Modify the configuration file postgresql.conf, change wal_level to logical, and add decoderbufs to the shared_preload_libraries option;

[0030] S1012. Adjust the number of max_wal_senders and max_replication_slots as needed;

[0031] S1013. Restart the openGauss database.

[0032] In addition, the present invention also provides a computer-readable storage medium storing a computer program, which, when executed by a processor, implements the steps of the above-described method for real-time bidirectional data synchronization between MySQL and openGauss.

[0033] In summary, the method for real-time bidirectional data synchronization between MySQL and openGauss in this invention can ensure that application data on MySQL and openGauss can be synchronized during the new database verification phase, effectively guaranteeing user data security. Attached Figure Description

[0034] To more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings used in the embodiments of the present invention will be briefly introduced below. Obviously, the following drawings are only some embodiments recorded in the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0035] Figure 1 This is a flowchart illustrating the overall implementation process of the method of the present invention.

[0036] Figure 2 This is a flowchart illustrating the process of real-time data synchronization from the MySQL side according to an embodiment of the present invention.

[0037] Figure 3 This is a flowchart illustrating the process of real-time data synchronization from the openGauss endpoint according to an embodiment of the present invention. Detailed Implementation

[0038] To make the objectives, technical solutions, and advantages of this invention clearer, the technical solutions of this invention will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this invention, and not all of them. This invention can also be implemented or applied through other different specific embodiments, and the details in this specification can also be modified or changed based on different viewpoints and applications without departing from the spirit of this invention.

[0039] At the same time, it should be understood that the scope of protection of the present invention is not limited to the specific embodiments described below; it should also be understood that the terminology used in the embodiments of the present invention is for describing specific embodiments and not for limiting the scope of protection of the present invention.

[0040] Example: A method for real-time bidirectional data synchronization between MySQL and openGauss

[0041] like Figure 1 As shown, this method includes the following two processes:

[0042] The MySQL side collects changes from the MySQL database through binlog, determines whether the transaction contains a tag, discards the change transaction containing a tag, and sends the change transaction without a tag to the openGauss application side, concatenates the tag and writes it into openGauss;

[0043] The openGauss client collects openGauss changes through decoderbufs, determines whether the transaction contains a tag, discards the change transaction containing a tag, and sends the change transaction without a tag to the MySQL application client, which then concatenates the tag and writes it into MySQL.

[0044] The two processes described above are combined to form a complete two-way real-time data synchronization process, which will be explained separately below:

[0045] Figure 2 The diagram shows the real-time data synchronization process on the MySQL side, which includes the following steps:

[0046] Step 1: Configure MySQL Binlog service:

[0047] (1) Implementing this technical solution requires configuring Binlog on the MySQL side. Enabling Binlog requires adjusting the MySQL database permissions, as detailed below:

[0048]

[0049] The authorization statement is as follows:

[0050] grant select,replication slave,replication client on*.*to'MySQL user'@'%';

[0051] grant select on mysql.user to'MySQL user'@'%';

[0052] grant select,insert,update,delete on 'MySQLuser'.incremental_offsetto'MySQLuser'@'%';

[0053] grant select,insert,update,delete on 'MySQLuser'.tb_mysql_logcontextto 'MySQLuser'@'%';

[0054] Grant select, insert, update, delete on 'MySQL user'.tb_mysql_tableddl to 'MySQL user'@'%';

[0055] Grant select on 'To be synchronized library'.'To be synchronized table' to 'MySQL user'@'%';

[0056] FLUSH PRIVILEGES;

[0057]

Explanation

[0058] In the authorization statement:

[0059] <MySQL user> refers to the user name when connecting to the MySQL data source, which should be modified according to the actual situation.

[0060] (2) Configure parameters in the MySQL configuration file my.cnf.

[0061] log-bin=mysql-bin # Enable binlog

[0062] binlog-format=ROW # Select the ROW mode for binlog

[0063] server-id=1 # Configure the server id, which doesn't have to be 1, and the machines in the cluster should not be the same as those of other machines.

[0064]

Explanation

[0065] The log-bin option configures the binlog log directory. Setting this directory means enabling the binlog log.

[0066] log-bin can directly take the value of mysql-bin, which means its location is in the / var / lib / mysql / directory by default; it can also be set to an absolute path.

[0067] (3) Use the service MySQL restart command to restart the database.

[0068] [root@Cent0S76-53 etc]#service mysql restart

[0069] Shutting down MySQL..SUCCESS!

[0070] Starting MySQL..SUCCESS!

[0071] [root@Cent0S76-53 etc]#

[0072] Step 2: Connect to the MySQL database, create the mark_down table under the schema that needs to be synchronized, and execute the following commands to create the mark_down table and grant permissions:

[0073] create table mark_down(update_time TIMESTAMP);

[0074] grant insert,update on 'MySQL user'.mark_down to 'MySQL user'@'%';

[0075] Step 3: The Java program connects to the Binlog and retrieves the change transaction SQL of the MySQL database;

[0076] Step 4: Determine if the SQL statement contains the flag operation: update mark_down set update_time... If it does, discard the collected record; otherwise, send the record to the application.

[0077] Step 5: After receiving the change record, the application adds a marker SQL statement before each record: update mark_down set update_time = current_timpstamp; and then writes it to the openGauss database.

[0078] Figure 3 The diagram shows the real-time data synchronization process on the openGauss end, which includes the following steps:

[0079] Step 1: Configure decoderbufs in openGauss

[0080] The default configuration of openGauss includes decoderbufs, and the installation process will not be described in detail.

[0081] (1) Modify the configuration file postgresql.conf

[0082] wal_level changed to logical

[0083] Add decoderbufs to the shared_preload_libraries option

[0084] Adjust the number of max_wal_senders and max_replication_slots as needed. The default value is 10, which means that a maximum of 10 replication slots can be created.

[0085] (2) Restart the openGauss database.

[0086] Step 2: Connect to the openGauss database, create the mark_down table under the schema that needs to be synchronized, and execute the following commands to create the mark_down table and grant permissions:

[0087] create table mark_down(update_time TIMESTAMP);

[0088] grant insert,update on table mark_down to openGauss user;

[0089] Step 3: The Java program connects to decoderbufs and obtains the change transaction SQL of the openGauss database;

[0090] Step 4: Determine if the SQL statement contains the flag operation: update mark_down set update_time... If it does, discard the collected record; otherwise, send the record to the application.

[0091] Step 5: After receiving the change records, the application adds a marker SQL statement before each record: update mark_down set update_time = current_timpstamp; and then writes it to the MySQL database.

[0092] The above description is merely a preferred embodiment of the present invention and is not intended to limit the present invention in any way. Any person skilled in the art can make some modifications or alterations to the above-disclosed technical content to obtain equivalent embodiments without departing from the scope of the technical solution of the present invention. Any simple modifications, equivalent changes and alterations made to the above embodiments based on the technical essence of the present invention without departing from the scope of the technical solution of the present invention should be included within the scope of protection of the claims of the present invention.

Claims

1. A method for real-time bidirectional data synchronization between MySQL and OpenGauss, characterized in that, The method includes: Step 1: The MySQL side collects change transactions from the binlog, determines whether the change transaction contains a flag, discards the flagged change transaction, and sends the unflagged change transaction to the openGauss application, where a flag is appended and written to openGauss; including: S1. Configure MySQL Binlog service; S2. Connect to the MySQL database and create a mark_down table under the schema that needs to be synchronized; S3. The Java program connects to the Binlog to collect change transaction SQL statements from the MySQL database; S4. Determine whether the change transaction SQL contains the mark_down flag. If it does, discard the collected records. If not, send the collected records to the openGauss application. S5. After receiving the change record, the openGauss application appends a mark_down flag to the beginning of each record and then writes it to the openGauss library; Step two: The openGauss client collects openGauss change transactions through decoderbufs, determines whether the change transaction contains a tag, discards the transaction with a tag, and sends the transaction without a tag to the MySQL application, where the tag is appended and written to MySQL; including: S101. Configure the decoderbufs service in openGauss; S102. Connect to the openGauss database and create a mark_down table under the schema that needs to be synchronized; S103. The Java program connects to decoderbufs to collect change transaction SQL from the openGauss database; S104. Determine whether the change transaction SQL contains the mark_down flag. If it does, discard the collected records; otherwise, send the collected records to the MySQL application. S105. After receiving the change record, the MySQL application appends a mark_down flag to the beginning of each record and then writes it to the MySQL database.

2. The method for real-time bidirectional data synchronization between MySQL and openGauss according to claim 1, characterized in that, The configuration of the MySQL Binlog service described in step S1 includes: S11. Adjust MySQL database permissions; S12. Configure the parameters in the MySQL configuration file my.cnf; S13. Restart the database using the command `service MySQL restart`.

3. The method for real-time bidirectional data synchronization between MySQL and openGauss according to claim 2, characterized in that, The adjustment of MySQL database permissions in step S11 includes the following MySQL database permissions: REPLICATIONSLAVE, REPLICATION CLIENT, SELECT, INSERT, UPDATE, and DELETE.

4. The method for real-time bidirectional data synchronization between MySQL and openGauss according to claim 2, characterized in that, The configuration parameters in the MySQL configuration file my.cnf mentioned in step S12 include configuring the log-bin option, that is, setting the binlog log directory; the log-bin can be directly set to mysql-bin, which means that its location is in the / var / lib / mysql / directory, or it can be set to an absolute path.

5. The method for real-time bidirectional data synchronization between MySQL and openGauss according to claim 1, characterized in that, The openGauss configuration decoderbufs service described in step S101 includes: S1011. Modify the configuration file postgresql.conf, change wal_level to logical, and add decoderbufs to the shared_preload_libraries option; S1012. Adjust the number of max_wal_senders and max_replication_slots as needed; S1013. Restart the openGauss database.

6. A computer-readable storage medium storing a computer program that, when executed by a processor, implements the steps of the method for real-time bidirectional data synchronization between MySQL and openGauss as described in any one of claims 1-5.

Images