A MySQL data synchronization method, apparatus, database terminal, medium, and system
By using the latest transaction identifier of the storage module to complete transaction rollback in MySQL master-slave replication, the problem of data inconsistency between master and slave databases is solved, achieving lossless switching and efficient data synchronization.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2022-10-12
- Publication Date
- 2026-03-13
Smart Images
Figure CN115617908B_ABST
Abstract
Description
Technical Field
[0001] The embodiments of the present invention relate to the field of database technology, and in particular to a MySQL data synchronization method, apparatus, database terminal, medium and system. Background Technology
[0002] MySQL clustering typically refers to a MySQL master-slave replication architecture. MySQL master-slave replication is commonly used to address the single point of failure issue in MySQL, synchronizing changes from the master database to the slave database through logical replication. However, because a strictly consistent model cannot be guaranteed between the master and slave databases, MySQL master-slave replication introduces data consistency problems.
[0003] Figure 1 This is a diagram illustrating the AfterSync mode of the MySQL semi-synchronous plugin, as shown below. Figure 1 As shown, after the master database calls binlogsync, it needs to wait for the slave database's response before committing. This way, the transaction will only be committed after the slave database confirms that it has received the transaction. Therefore, this can solve the problem of data inconsistency between master and slave databases caused by MySQL master-slave replication.
[0004] In the aforementioned prior art, if a failure occurs after the master database synchronizes the binary log to the slave database but before the master database completes disk flushing, the transactions in the binary log will not be successfully committed on the master database. However, since the slave database has already received the binary log and executed it successfully, it is equivalent to having extra data on the slave database, which will cause inconsistency between the master and slave databases. Summary of the Invention
[0005] This invention provides a MySQL data synchronization method, apparatus, database terminal, medium, and system to solve the problem of data inconsistency between a first database and a second database, achieving a lossless MySQL switching effect.
[0006] According to one aspect of the present invention, a MySQL data synchronization method is provided, characterized in that it is applied to a first database, the method comprising:
[0007] After receiving the submission request, a binary log carrying an acknowledgment request is sent to the second database, and the binary log records multiple transactions.
[0008] After receiving the confirmation information returned by the second database, the transaction identifier is sent to the storage module;
[0009] In the event of a failure, a transaction rollback is performed based on the latest transaction identifier sent by the storage module, so as to achieve data synchronization after the second database completes the transaction rollback based on the latest transaction identifier.
[0010] According to another aspect of the present invention, a MySQL data synchronization method is provided, characterized in that it is applied to a second database, the method comprising:
[0011] After receiving the binary log carrying an acknowledgment request sent by the first database, the binary log is stored in the relay log;
[0012] A confirmation message is returned to the first database;
[0013] In the event of a failure, a transaction rollback is performed based on the latest transaction identifier sent by the storage module, so as to achieve data synchronization after the first database completes the transaction rollback based on the latest transaction identifier.
[0014] According to another aspect of the present invention, a MySQL data synchronization device is provided, characterized in that it is applied to a first database, the device comprising:
[0015] The first sending module is used to send a binary log carrying an acknowledgment request to the second database after receiving a submission request;
[0016] The second sending module is used to send the transaction identifier to the storage module after receiving the confirmation information returned by the second database;
[0017] The data synchronization module is used to complete the transaction rollback based on the latest transaction identifier sent by the storage module when a failure occurs, so as to achieve data synchronization after the second database completes the transaction rollback based on the latest transaction identifier.
[0018] According to another aspect of the present invention, a MySQL data synchronization device is provided, characterized in that it is applied to a second database, the device comprising:
[0019] The receiving module is used to receive the binary log carrying the confirmation request sent by the first database and store the binary log in the relay log.
[0020] The return module is used to return confirmation information to the first database;
[0021] The data synchronization module is used to complete the transaction rollback based on the latest transaction identifier sent by the storage module when a failure occurs, so as to achieve data synchronization after the first database completes the transaction rollback based on the latest transaction identifier.
[0022] According to another aspect of the present invention, a first database is provided, characterized in that the first database comprises:
[0023] At least one processor; and
[0024] A memory communicatively connected to the at least one processor; wherein,
[0025] The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to execute a MySQL data synchronization method applied to a first database.
[0026] According to another aspect of the present invention, a second database is provided, characterized in that the second database comprises:
[0027] At least one processor; and
[0028] A memory communicatively connected to the at least one processor; wherein,
[0029] The memory stores a computer program that can be executed by the at least one processor to enable the at least one processor to execute a MySQL data synchronization method applied to a second database.
[0030] According to another aspect of the present invention, a computer-readable storage medium is provided, characterized in that the computer-readable storage medium stores computer instructions for causing a processor to execute the MySQL data synchronization method described in any embodiment of the present invention.
[0031] According to another aspect of the present invention, a MySQL data synchronization system is provided, characterized in that the system includes a first database, a second database, and a storage module, wherein the first database is connected to the second database and the storage module respectively;
[0032] The first database is used to execute the MySQL data synchronization method provided in one aspect of the present invention;
[0033] The second database is used to execute the MySQL data synchronization method provided in another aspect of the present invention;
[0034] The storage module is used to store transaction identifiers.
[0035] The technical solution of this invention completes data synchronization by sending the latest transaction identifier through the storage module, which solves the problem of data inconsistency between the first database and the second database and achieves the effect of lossless MySQL switching.
[0036] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of the present invention, nor is it intended to limit the scope of the invention. Other features of the invention will become readily apparent from the following description. Attached Figure Description
[0037] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.
[0038] Figure 1 A schematic diagram of the AfterSync mode of the MySQL semi-synchronous plugin;
[0039] Figure 2 This is a flowchart illustrating a MySQL data synchronization method provided in Embodiment 1 of the present invention;
[0040] Figure 3 This is a flowchart illustrating a MySQL data synchronization method provided in Embodiment 2 of the present invention;
[0041] Figure 4 This is a schematic diagram of a MySQL execution submission request process provided in Embodiment 3 of the present invention;
[0042] Figure 5 This is a flowchart illustrating a MySQL data synchronization method provided in Embodiment 3 of the present invention;
[0043] Figure 6 This is a schematic diagram of another process for a MySQL data synchronization method provided in Embodiment 3 of the present invention;
[0044] Figure 7 This is a schematic diagram of the structure of a MySQL data synchronization device provided in Embodiment 4 of the present invention;
[0045] Figure 8 This is a schematic diagram of the structure of a MySQL data synchronization device provided in Embodiment 5 of the present invention;
[0046] Figure 9 This is a schematic diagram of the structure of a MySQL data synchronization system provided in Embodiment Six of the present invention;
[0047] Figure 10 This is a schematic diagram of an example structure of a MySQL data synchronization system provided in Embodiment Six of the present invention;
[0048] Figure 11 A schematic diagram of the structure of a database terminal that can be used to implement an embodiment of the present invention is shown. Detailed Implementation
[0049] To enable those skilled in the art to better understand the present invention, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are merely some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention. It should be understood that the various steps described in the method embodiments of the present invention can be performed in different orders and / or in parallel. Furthermore, the method embodiments may include additional steps and / or omit the steps shown. The scope of the present invention is not limited in this respect.
[0050] The term "comprising" and its variations as used herein are open-ended inclusions, meaning "including but not limited to". The term "based on" means "at least partially based on". The term "one embodiment" means "at least one embodiment"; the term "another embodiment" means "at least one additional embodiment"; the term "some embodiments" means "at least some embodiments". Definitions of other terms will be given in the description below.
[0051] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.
[0052] It should be noted that the terms "a" and "a plurality of" used in this invention are illustrative rather than restrictive. Those skilled in the art should understand that, unless otherwise expressly indicated in the context, they should be understood as "one or more".
[0053] The names of the messages or information exchanged between the multiple devices in the embodiments of the present invention are for illustrative purposes only and are not intended to limit the scope of these messages or information.
[0054] Example 1
[0055] Figure 2This is a flowchart illustrating a MySQL data synchronization method provided in Embodiment 1 of the present invention. This method is applicable to data synchronization between primary and backup databases in a MySQL cluster. The method can be executed by a MySQL data synchronization device, which can be implemented by software and / or hardware and is generally integrated into the first database terminal. In this embodiment, the first database can be the primary database, i.e., the master database.
[0056] like Figure 2 As shown in Embodiment 1 of the present invention, a MySQL data synchronization method includes the following steps:
[0057] S110. After receiving the submission request, send a binary log carrying the confirmation request to the second database. The binary log records multiple transactions.
[0058] The submission request can be a request for the first database to synchronize data to the second database. The data to be synchronized can include newly added or modified data in the first database. The second database can be a slave database, and there is no specific limit to the number of slave databases.
[0059] The confirmation request can be a request with confirmation information, and there are no restrictions on the specific form of the confirmation request.
[0060] A binary log can be a record of statements related to data modifications in a primary database, i.e., a binlog. The basic data structure of a binary log consists of a header and data. The header may include the binary log type event, creation time, server information, etc. The data can contain all data modifications specific to this transaction. A transaction can be a sequence of database operations that access and potentially manipulate various data items. For example, in MySQL, a transaction can be a single SQL statement. In this embodiment, the binary log can carry confirmation requests.
[0061] In this embodiment, there are no specific restrictions on where or how submission requests are received. For example, submission requests sent by users can be received. Submission requests can be received through a thread, and binary logs carrying confirmation requests can be sent to the second database through a thread. Specifically, the first database includes a user thread UserTHD and an identifier processing thread Dump THD. The user thread receives submission requests sent by users, writes binary logs to the identifier processing thread, and the identifier processing thread sends binary logs carrying confirmation requests to the read / write thread IO THD in the second database.
[0062] S120. After receiving the confirmation information returned by the second database, the transaction identifier is sent to the storage module.
[0063] The acknowledgment message can be sent by the second database after receiving the binary log. This acknowledgment message indicates that the second database has successfully received the binary log containing the acknowledgment request sent by the first database. Each transaction has a corresponding transaction identifier, GTID. This transaction identifier can be a unique identifier generated on the first database and bound to the transaction. This unique identifier is not only unique within the first database but also unique within the MySQL cluster. The transaction identifier can be sent to the storage module through an identifier processing thread. This storage module can be a consistent storage based on the Raft protocol, responsible for storing the current MySQL transaction identifier.
[0064] Furthermore, the transaction identifier consists of the unique identifier of the current MySQL instance and the number of transactions that have been committed on the current MySQL instance, and the number of transactions increases monotonically with each transaction commit.
[0065] Wherein, GTID = server_uuid:transaction_id; server_uuid represents a unique identifier for a MySQL instance, and the server that generated the binlog can be determined based on server_uuid, and transaction_id represents the number of transactions that have been committed on this instance.
[0066] For example, the transaction identifier 3E11FA47-71CA-11E1-9E33-C80AA9429562:1 consists of the unique identifier of the current MySQL instance 3E11FA47-71CA-11E1-9E33-C80AA9429562 and the number of transactions that have been committed on the current MySQL instance, which is 1.
[0067] Understandably, a committed transaction can correspond to a transaction identifier. The first database only sends the transaction identifier to the storage module. A transaction identifier is generally no more than 64 bytes, which can greatly reduce the storage pressure on the storage module.
[0068] S130. In the event of a failure, a transaction rollback is performed based on the latest transaction identifier sent by the storage module, so as to achieve data synchronization after the second database completes the transaction rollback based on the latest transaction identifier.
[0069] In this embodiment, the specific content of the fault is not limited. For example, the fault may include network fault, power outage fault, and system crash fault. The fault may occur when the binary log is sent to the second database and the second database successfully writes the data to disk, but the confirmation information is not successfully sent to the first database, or when the data modification in the first database has not been written to disk.
[0070] The latest transaction identifier can be the last transaction identifier stored by the storage module before the failure occurred.
[0071] In this embodiment, when a fault occurs, both the first database and the second database can obtain the latest transaction identifier from the storage module. By comparing the maximum number of transactions with the latest transaction identifier with the maximum number of transactions with the transaction identifier in the local disk, the excess transactions are rolled back to achieve data synchronization between the first database and the second database.
[0072] In this embodiment, the transaction identifier is sent to the storage module for storage after the first database receives the confirmation information from the second database, but before the second database writes the data to disk. This ensures that the number of transactions in the latest transaction identifier will never exceed the maximum number of transactions stored locally in the second database, nor will it be less than the maximum number of transactions stored locally in the first database.
[0073] Furthermore, completing the transaction rollback based on the latest transaction identifier sent by the storage module includes: obtaining the latest transaction identifier from the storage module, and taking the maximum number of transactions in the latest transaction identifier as the first maximum number of transactions; obtaining the maximum number of transactions in the transaction identifiers stored in the local disk as the second maximum number of transactions; if the second maximum number of transactions is greater than the first maximum number of transactions, then rolling back the transactions corresponding to the excess part in the transaction identifier, wherein the excess part is the portion where the second maximum number of transactions exceeds the first maximum number of transactions; and downgrading the first database to the second database.
[0074] For example, if the latest transaction identifier of the storage module is da0cd243-116a-11ed-b46d-525400bdfd18:1-699061, then the maximum number of transactions in the latest transaction identifier, i.e., the first maximum number of transactions, is 699061. If the transaction identifier stored in the local disk is da0cd243-116a-11ed-b46d-525400bdfd18:1-699063, then the maximum number of transactions in the transaction identifier stored in the local disk, i.e., the second maximum number of transactions, is 699063. At this time, the first maximum number of transactions is less than the second maximum number of transactions. Therefore, the redundant transactions in the local disk can be rolled back. The redundant transactions in the local disk can be transactions with transaction number identifiers of 699062 and 699063.
[0075] Understandably, after rolling back redundant transactions, the first database needs to be downgraded to the second database. Simultaneously, the second database can also roll back redundant transactions based on the latest transaction identifier. Once both the first and second databases have completed their transaction rollback operations, data synchronization can be achieved.
[0076] Furthermore, in the absence of a fault, upon receiving the response from the storage module, the binary log is written to the local disk, the transaction identifier is written to the binary log, and a commit command is returned to the user after the writing is completed.
[0077] The storage module can respond in various ways. For example, after receiving the transaction identifier, the storage module responds by sending storage confirmation information to the first database.
[0078] In this embodiment, the transaction identifier is also written to the MySQL binary log. Only after all the data has been written to disk will the commit command be returned to the user. Only after the user receives the command will the data be truly committed successfully.
[0079] This invention provides a MySQL data synchronization method. First, after receiving a commit request, a binary log carrying an acknowledgment request is sent to a second database. This binary log records multiple transactions. Then, after receiving acknowledgment information from the second database, a transaction identifier is sent to a storage module. Finally, in the event of a failure, a transaction rollback is performed based on the latest transaction identifier sent by the storage module, thus achieving data synchronization after the second database completes its rollback based on the latest transaction identifier. This method compares the number of transactions with the latest transaction identifier in the storage module with the maximum number of transactions in the first database, rolling back transactions corresponding to excess transaction identifiers in the first database. After the second database completes its rollback based on the latest transaction identifier, data synchronization between the first and second databases is achieved. This solution solves the problem of data inconsistency between the first and second databases. Furthermore, this method only synchronizes data carrying transaction identifiers, achieving a lossless MySQL failover effect. Synchronizing only data carrying transaction identifiers results in a smaller data volume, avoiding the need for other solutions such as MySQL Replication Groups or similar schemes to synchronize all MySQL data through a consistency protocol, thus preventing the low synchronization efficiency caused by large MySQL transactions.
[0080] Example 2
[0081] Figure 3 This is a flowchart illustrating a MySQL data synchronization method according to Embodiment 2 of the present invention. This method is applicable to data synchronization between primary and backup databases in a MySQL cluster. The method can be executed by a MySQL data synchronization device, which can be implemented in software and / or hardware and is generally integrated into a second database terminal. In this embodiment, the second database can be a slave database.
[0082] like Figure 3As shown in Embodiment 2 of the present invention, a MySQL data synchronization method includes the following steps:
[0083] S210. After receiving the binary log carrying the acknowledgment request sent by the first database, store the binary log in the relay log.
[0084] The relay log can be an intermediate file in the second database, and it can store transactions from the binary log of the first data transmission.
[0085] In this embodiment, there are no specific restrictions on the method of receiving binary logs and storing them in the relay log. For example, a thread can receive binary logs carrying acknowledgment requests sent by the first database and store them in the relay log. Specifically, in the second database, an I / O thread receives binary logs carrying acknowledgment requests sent by the first database and stores them in the relay log.
[0086] S220, Return confirmation information to the first database.
[0087] The confirmation message can be sent by the second database after receiving the binary log. The confirmation message is used to indicate that the second database has successfully received the binary log carrying the confirmation request sent by the first database.
[0088] S230. In the event of a failure, a transaction rollback is performed based on the latest transaction identifier sent by the storage module, so as to achieve data synchronization after the first database completes the transaction rollback based on the latest transaction identifier.
[0089] In this embodiment, the specific content of the fault is not limited. For example, the fault may include network fault, power outage fault, and system crash fault. The fault may occur when the binary log is sent to the second database and the second database successfully writes the data to disk, but the confirmation information is not successfully sent to the first database, or when the data modification in the first database has not been written to disk.
[0090] The latest transaction identifier can be the last transaction identifier stored by the storage module before the failure occurred.
[0091] In this embodiment, when a failure occurs, both the first database and the second database can obtain the latest transaction identifier from the storage module. By comparing the maximum number of transactions with the latest transaction identifier with the maximum number of transactions with the transaction identifier in the local disk, the excess transactions are rolled back to achieve data synchronization between the first database and the second database.
[0092] In this embodiment, the transaction identifier is sent to the storage module for storage after the first database receives the confirmation information from the second database, but before the second database writes the data to disk. This ensures that the number of transactions in the latest transaction identifier will never exceed the maximum number of transactions stored locally in the second database, nor will it be less than the maximum number of transactions stored locally in the first database.
[0093] Furthermore, the step of completing the transaction rollback based on the latest transaction identifier sent by the storage module to achieve data synchronization after the first database completes the transaction rollback based on the latest transaction identifier includes: obtaining the latest transaction identifier from the storage module; determining whether the number of transactions stored in the relay log is greater than the maximum number of transactions in the latest transaction identifier; if so, rolling back the redundant transactions stored in the relay log; and upgrading to a new first database to complete data synchronization. If not, upgrading the second database to a new first database to complete data synchronization.
[0094] For example, if the latest transaction identifier of the storage module is da0cd243-116a-11ed-b46d-525400bdfd18:1-699061, then the maximum number of transactions in the latest transaction identifier is 699061; if the transaction identifier stored in the relay log is da0cd243-116a-11ed-b46d-525400bdfd18:1-699063, then the maximum number of transactions in the relay log is 699063. At this time, the number of transactions stored in the relay log is greater than the maximum number of transactions in the latest transaction identifier. Therefore, the redundant transactions in the relay log can be rolled back. The redundant transactions in the relay log can be transactions with transaction number identifiers of 699062 and 699063.
[0095] In this embodiment, when the number of transactions stored in the relay log exceeds the maximum number of transactions in the latest transaction identifier, the excess transactions in the relay log are rolled back. Simultaneously, the first database can also roll back the excess transactions based on the latest transaction identifier. Once both the first and second databases have completed their transaction rollback operations, data synchronization is achieved.
[0096] Understandably, after a failure, the second database needs to be upgraded to the first database in order to continue data synchronization.
[0097] This invention provides a MySQL data synchronization method in Embodiment 2. First, it receives a binary log containing an acknowledgment request from a first database and stores the binary log in a relay log. Then, it returns acknowledgment information to the first database. Finally, in the event of a failure, it rolls back the transaction based on the latest transaction identifier sent by the storage module, achieving data synchronization after the first database completes its rollback based on the latest transaction identifier. This method compares the number of transactions with the latest transaction identifier in the storage module with the maximum number of transactions in the second database's transaction identifiers, rolling back transactions corresponding to excess transaction identifiers in the second database. Data synchronization between the second and first databases is achieved after the first database completes its rollback based on the latest transaction identifier. This solution solves the problem of data inconsistency between the second and first databases, achieving a lossless MySQL failover effect by having the storage module synchronize only the data with transaction identifiers in the database.
[0098] Example 3
[0099] Based on the technical solutions of the above embodiments, Embodiment 3 of the present invention provides a specific implementation method. As a specific implementation method of this embodiment, Figure 4 This is a schematic diagram illustrating a MySQL submission request execution process provided in Embodiment 3 of the present invention. Figure 4 As shown, the specific process includes the following:
[0100] The MySQL Master, or first database, receives commit requests (sent by users) through the User THD (user thread) and sends binary logs carrying acknowledgment requests to the MySQL Slave, or second database, through the Dump THD (Send Events With Ack Request).
[0101] After receiving the Events With Ack Request from the MySQL Master, the MySQL Slave returns an ACK to the MySQL Master via the read / write thread IO THD.
[0102] After the Dump THD in the MySQL Master receives the ACK returned by the MySQL Slave, it writes GTID to KV, which means sending the transaction identifier to the Raft KV module.
[0103] The Raft KV module sends a Write GTID to KV Done command to the Dump THD, which means the storage module sends a transaction completion command to the notification processing thread. Only after all data is written to disk will the user's Commit command be returned to the user; that is, the Dump THD sends a Commit Done command to the User THD, and only after the user receives this command is the data truly committed successfully.
[0104] Figure 5 This is a flowchart illustrating a MySQL data synchronization method provided in Embodiment 3 of the present invention. Figure 5 As shown, the specific process includes the following:
[0105] After receiving the commit request, the MySQL Master inserts into the binary log (Write Binlog (Prepare)) and then sends the binary log containing the acknowledgment request to the second database via the "Send Binglog Events to SlaveWith Ack Request" command.
[0106] The MySQL Slave Receive Binlog Events & Save To Relaylog means that after the second database receives the binary log containing the acknowledgment request sent by the first database, it stores the binary log in the relay log and then returns an ACK (acknowledgment message) to the MySQL Master.
[0107] The MySQL master Receive Slave Ack means that after the first database receives the confirmation information returned by the second database, the Send Current GTID To KV Store means sending the current transaction identifier to the storage module.
[0108] KV GTID Store Receive GTID & Save To Disk means that the storage module receives the transaction identifier and saves it to the local disk of the storage module.
[0109] After the MySQL Master Receive KV Store Ack command (the first database receives the confirmation command from the storage module), Sync Binlog To Disk synchronizes the binary log to the local disk, and Commit Done completes the commit request.
[0110] Figure 6 This is another flowchart illustrating a MySQL data synchronization method provided in Embodiment 3 of the present invention. Figure 6As shown, the specific process includes the following:
[0111] The MySQL Master Insert Into…Commit command means that the first database receives the commit request, prepares to write the binary log (Write Binlog (Prepare)) based on the commit request, and sends the binary log events to the second database with the acknowledgment request (Send Binglog Events to Slave With Ack Request).
[0112] The MySQL Slave Receive Binlog Events & Save to Relaylog process involves the second database receiving binary logs carrying acknowledgment requests from the first database and storing these binary logs in the relay log. After storing the binary logs in the relay log, it returns an ACK acknowledgment message to the first MySQL Master database.
[0113] MySQL Master Receive Slave Ack means that after the first database receives the confirmation information returned by the second database, Send Current GTID To KV Store means sending the current transaction identifier to the storage module.
[0114] MySQL Master Crash occurs when the first database fails to send the current transaction identifier to the storage module. The Promote Master Recover process is executed, and the specific steps are as follows: Get Newest Gtid (obtain the latest transaction identifier); the Gtid stored in the current KV Store is recorded as the remote gtid, and the latest transaction identifier stored in the current storage module is recorded as the remote transaction identifier; the maximum gtid record received by the current local relay log is recorded as the local gtid, and the current maximum transaction identifier received in the local relay log is recorded as the local maximum transaction identifier; determine if the remote gtid contains the local gtid, i.e., determine if the remote transaction identifier contains the local maximum transaction identifier; if the remote transaction identifier contains the local maximum transaction identifier, then Set Readonly = False (set the data to be readable and writable), and change to new master (promote the original second database to the new first database); if the remote transaction identifier does not contain the local maximum transaction identifier, then roll back the redundant parts of the local gtid based on the remote gtid, i.e., roll back the transactions corresponding to the redundant parts of the local maximum transaction identifier based on the remote transaction identifier; Set Readonly = False (set the data to be readable and writable); change to new master. The master database is the original second database that has been downgraded to the new first database to provide master services.
[0115] A MySQL Master Crash occurs when the first database fails to send its current transaction identifier to the storage module. The Old Master Recover process is executed, and the specific steps are as follows: `Set Readonly=True` sets the data to read-only; `Get Newest Gtid` retrieves the latest transaction identifier; the success of retrieving the remote gtid (determining if the remote transaction identifier was successfully retrieved; if it fails, `Get Newest Gtid` is returned again; if successful, the Newest Gtid is recorded as the remote gtid; the maximum local gtid is retrieved; the maximum local transaction identifier is compared with the maximum portion of the remote transaction identifier (truncation of the local server); the maximum local transaction identifier is compared with the maximum local transaction identifier; the maximum local gtid is checked against the maximum remote gtid; if so, the excess portion of the local gtid is rolled back; the original first database is demoted to the second database; otherwise, the process switches to the new master and receives Binglog Events & saves data. Relaylog receives transactions from the binary log and stores them in the relay log.
[0116] In this embodiment, when a fault occurs, both the first database and the second database can obtain the latest transaction identifier from the storage module. By comparing the maximum number of transactions with the latest transaction identifier with the maximum number of transactions with the transaction identifier in the local disk, the excess transactions are rolled back to achieve data synchronization between the first database and the second database.
[0117] Example 4
[0118] Figure 7 This is a schematic diagram of a MySQL data synchronization device provided in Embodiment 4 of the present invention. This embodiment is applicable to data synchronization between primary and backup databases in a MySQL cluster. The device can be implemented using software and / or hardware. This device is applied to the first database, such as… Figure 7 As shown, the MySQL data synchronization device specifically includes:
[0119] The first sending module 710 is used to send a binary log carrying an acknowledgment request to the second database after receiving a submission request;
[0120] The second sending module 720 is used to send the transaction identifier to the storage module after receiving the confirmation information returned by the second database;
[0121] The data synchronization module 730 is used to complete the transaction rollback based on the latest transaction identifier sent by the storage module when a failure occurs, so as to achieve data synchronization after the second database completes the transaction rollback based on the latest transaction identifier.
[0122] Accordingly, the transaction identifier consists of the unique identifier of the current MySQL instance and the number of transactions that have been committed on the current MySQL instance, and the number of transactions increases monotonically with each transaction commit.
[0123] Optionally, the device further includes:
[0124] The command submission module, when no fault occurs, after receiving the response from the storage module, writes the binary log to the local disk, writes the transaction identifier to the binary log, and returns the submission command to the user after the write is completed.
[0125] Optionally, the data synchronization module 730 includes:
[0126] The first maximum transaction number acquisition unit is used to obtain the latest transaction identifier from the storage module and take the maximum transaction number in the latest transaction identifier as the first maximum transaction number.
[0127] The second maximum transaction number acquisition unit is used to acquire the maximum transaction number among the transaction identifiers stored in the local disk as the second maximum transaction number.
[0128] The rollback unit is used to roll back the transactions corresponding to the excess part in the transaction identifier if the second maximum number of transactions is greater than the first maximum number of transactions, wherein the excess part is the part where the second maximum number of transactions exceeds the first maximum number of transactions;
[0129] The downgrade unit is used to downgrade the first database to the second database.
[0130] The above-mentioned product can perform the method provided in Embodiment 1 of the present invention, and has the corresponding functional modules and beneficial effects of performing the method.
[0131] Example 5
[0132] Figure 8 This is a schematic diagram of a MySQL data synchronization device provided in Embodiment 5 of the present invention. This embodiment is applicable to data synchronization between primary and backup databases in a MySQL cluster. The device can be implemented using software and / or hardware. This device is applied to a second database, such as… Figure 8As shown, the MySQL data synchronization device specifically includes: a receiving module 810, which is used to receive a binary log carrying an acknowledgment request sent by the first database and then store the binary log in a relay log;
[0133] Return module 820 is used to return confirmation information to the first database;
[0134] The data synchronization module 830 is used to complete the transaction rollback based on the latest transaction identifier sent by the storage module when a failure occurs, so as to achieve data synchronization after the second database completes the transaction rollback based on the latest transaction identifier.
[0135] Optionally, the data synchronization module includes:
[0136] The latest transaction identifier acquisition unit is used to acquire the latest transaction identifier from the storage module;
[0137] A transaction count comparison unit is used to determine whether the number of transactions stored in the relay log is greater than the maximum number of transactions in the latest transaction identifier;
[0138] The rollback unit is used to roll back the redundant transactions stored in the relay log if it is determined that the number of transactions stored in the relay log is greater than the maximum number of transactions in the latest transaction identifier; and to upgrade the second database to a new first database.
[0139] The upgrade unit is used to upgrade the second database to a new first database to complete data synchronization if it is determined that the number of transactions stored in the relay log is not greater than the maximum number of transactions in the latest transaction identifier.
[0140] The above-mentioned product can perform the method provided in Embodiment 2 of the present invention, and has the corresponding functional modules and beneficial effects of performing the method.
[0141] Example 6
[0142] Figure 9 This is a schematic diagram of the structure of a MySQL data synchronization system provided in Embodiment Six of the present invention. Figure 9 As shown, the system includes: a first database 910, a second database 920, and a storage module 930, with the first database 910 connected to the second database 920 and the storage module 930 respectively.
[0143] The first database 910 is used to execute the MySQL data synchronization method provided in Example 1;
[0144] The second database 920 is used to execute the MySQL data synchronization method provided in Example 2;
[0145] Storage module 930 is used to store transaction identifiers.
[0146] The transaction identifier can be a unique identifier generated on the first database and bound to the transaction. This unique identifier is not only unique in the first database but also unique within the MySQL cluster. The first database, MySQL Master, can execute the MySQL data synchronization method provided in Example 1 to send data to the second database, i.e., MySQL Slave, and send the transaction identifier to the storage module, i.e., KV Store, to maintain data consistency between the first and second databases.
[0147] The technical solution of this invention involves deploying a MySQL data synchronization system. After receiving a commit request, the first database 910 sends a binary log carrying a confirmation request to the second database 920. Upon receiving the binary log from the first database 910, the second database 920 stores the binary log in a relay log and returns confirmation information to the first database 910 after storage. Upon receiving the confirmation information from the second database 920, the first database 910 sends a transaction identifier to the storage module 930. In the event of a failure, both the first database 910 and the second database 920 can obtain the latest transaction identifier from the storage module 930. By comparing the maximum number of transactions with the latest transaction identifier with the maximum number of transactions with the transaction identifier on the local disk, excess transactions are rolled back, thereby achieving data synchronization between the first database 910 and the second database 920.
[0148] For example, Figure 10 This is a schematic diagram of an example structure of a MySQL data synchronization system provided in Embodiment Six of the present invention, as shown below. Figure 10 As shown, the MySQL Master (first database) is connected to both the MySQL Slave (second database) and the KV Store (storage module). The MySQL Master sends the GTID to the KV Store (first database) and the transaction identifier to the storage module. The MySQL Master also sends data to the Slave (first database) and the second database.
[0149] Example 7
[0150] Figure 11 A schematic diagram of a database terminal 10, which can be used to implement embodiments of the present invention, is shown. The database terminal 10 may include a first database terminal and a second database terminal. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the invention described and / or claimed herein.
[0151] like Figure 11 As shown, the database terminal 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12 or a random access memory (RAM) 13, communicatively connected to the at least one processor 11. The memory stores computer programs executable by the at least one processor. The processor 11 can perform various appropriate actions and processes based on the computer program stored in the ROM 12 or loaded into the RAM 13 from storage unit 18. The RAM 13 can also store various programs and data required for the operation of the database terminal 10. The processor 11, ROM 12, and RAM 13 are interconnected via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.
[0152] Multiple components in the database terminal 10 are connected to the I / O interface 15, including: an input unit 16, such as a keyboard, mouse, etc.; an output unit 17, such as various types of displays, speakers, etc.; a storage unit 18, such as a disk, optical disk, etc.; and a communication unit 19, such as a network card, modem, wireless transceiver, etc. The communication unit 19 allows the database terminal 10 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.
[0153] Processor 11 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. Processor 11 performs the various methods and processes described above, such as MySQL data synchronization methods, including MySQL data synchronization methods applied to a first database and MySQL data synchronization methods applied to a second database.
[0154] In some embodiments, the MySQL data synchronization method described above can be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program can be loaded and / or installed on the database terminal 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the MySQL data synchronization method described above can be performed. Alternatively, in other embodiments, processor 11 can be configured to perform the MySQL data synchronization method by any other suitable means (e.g., by means of firmware).
[0155] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field-programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard products (ASSPs), systems-on-a-chip (SoCs), payload-programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.
[0156] Computer programs used to implement the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs may be executed entirely on a machine, partially on a machine, or as a standalone software package, partially on a machine and partially on a remote machine, or entirely on a remote machine or server.
[0157] In the context of this invention, a computer-readable storage medium can be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, apparatus, or device. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination thereof. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.
[0158] To provide user interaction, the systems and techniques described herein can be implemented on a database terminal having: a display device for displaying information to the user (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor); and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the database terminal. Other types of devices can also be used to provide user interaction; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).
[0159] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or computing systems that include middleware components (e.g., application servers), or computing systems that include frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.
[0160] A computing system can include clients and servers. Clients and servers are generally located far apart and typically interact through communication networks. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system to address the shortcomings of traditional physical hosts and VPS services, such as high management difficulty and weak business scalability.
[0161] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.
[0162] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.
Claims
1. A MySQL data synchronization method, characterized in that, Applied to a first database, the method includes: After receiving the submission request, a binary log carrying an acknowledgment request is sent to the second database, and the binary log records multiple transactions. After receiving the confirmation information returned by the second database, the transaction identifier is sent to the storage module; In the event of a failure, a transaction rollback is performed based on the latest transaction identifier sent by the storage module, so as to achieve data synchronization after the second database completes the transaction rollback based on the latest transaction identifier; The step of completing the transaction rollback based on the latest transaction identifier sent by the storage module includes: The latest transaction identifier is obtained from the storage module, and the maximum number of transactions in the latest transaction identifier is taken as the first maximum number of transactions. The maximum number of transactions stored in the local disk is used as the second maximum number of transactions. If the second maximum number of transactions is greater than the first maximum number of transactions, then the transactions corresponding to the excess part in the transaction identifier are rolled back, where the excess part is the portion where the second maximum number of transactions exceeds the first maximum number of transactions. Downgrade the first database to the second database.
2. The method according to claim 1, characterized in that, The transaction identifier consists of the unique identifier of the current MySQL instance and the number of transactions that have been committed on the current MySQL instance. The number of transactions increases monotonically with each transaction commit.
3. The method according to claim 1, characterized in that, When no fault occurs, after receiving the response from the storage module, the binary log is written to the local disk, the transaction identifier is written to the binary log, and the commit command is returned to the user after the write is completed.
4. A MySQL data synchronization method, characterized in that, Applied to a second database, the method includes: After receiving the binary log carrying an acknowledgment request sent by the first database, the binary log is stored in the relay log; A confirmation message is returned to the first database; In the event of a failure, a transaction rollback is performed based on the latest transaction identifier sent by the storage module, so as to achieve data synchronization after the first database completes the transaction rollback based on the latest transaction identifier; The process of rolling back a transaction based on the latest transaction identifier sent by the storage module includes: Obtain the latest transaction identifier from the storage module; Determine whether the number of transactions stored in the relay log is greater than the maximum number of transactions in the latest transaction identifier; If so, roll back the redundant transactions stored in the relay log; Upgrade the second database to a new first database; If not, the second database will be upgraded to the new first database to complete data synchronization.
5. A MySQL data synchronization device, characterized in that, Applied to a first database, the apparatus includes: The first sending module is used to send a binary log carrying an acknowledgment request to the second database after receiving a submission request; The second sending module is used to send the transaction identifier to the storage module after receiving the confirmation information returned by the second database; The data synchronization module is used to complete the transaction rollback based on the latest transaction identifier sent by the storage module when a failure occurs, so as to achieve data synchronization after the second database completes the transaction rollback based on the latest transaction identifier; The data synchronization module includes: The first maximum transaction number acquisition unit is used to obtain the latest transaction identifier from the storage module and take the maximum transaction number in the latest transaction identifier as the first maximum transaction number. The second maximum transaction number acquisition unit is used to acquire the maximum transaction number among the transaction identifiers stored in the local disk as the second maximum transaction number. The rollback unit is used to roll back the transactions corresponding to the excess part in the transaction identifier if the second maximum number of transactions is greater than the first maximum number of transactions, wherein the excess part is the part where the second maximum number of transactions exceeds the first maximum number of transactions; The downgrade unit is used to downgrade the first database to the second database.
6. A MySQL data synchronization device, characterized in that, Applied to a second database, the device includes: The receiving module is used to receive the binary log carrying the confirmation request sent by the first database and store the binary log in the relay log. The return module is used to return confirmation information to the first database; The data synchronization module is used to complete the transaction rollback based on the latest transaction identifier sent by the storage module when a failure occurs, so as to achieve data synchronization after the second database completes the transaction rollback based on the latest transaction identifier; The data synchronization module includes: The latest transaction identifier acquisition unit is used to acquire the latest transaction identifier from the storage module; A transaction count comparison unit is used to determine whether the number of transactions stored in the relay log is greater than the maximum number of transactions in the latest transaction identifier; The rollback unit is used to roll back the redundant transactions stored in the relay log if it is determined that the number of transactions stored in the relay log is greater than the maximum number of transactions in the latest transaction identifier; and to upgrade the second database to a new first database. The upgrade unit is used to upgrade the second database to a new first database to complete data synchronization if it is determined that the number of transactions stored in the relay log is not greater than the maximum number of transactions in the latest transaction identifier.
7. A first database terminal, characterized in that, The first database terminal includes: At least one processor; and A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the MySQL data synchronization method according to any one of claims 1-3.
8. A second database terminal, characterized in that, The second database terminal includes: At least one processor; and A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the MySQL data synchronization method of claim 4.
9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that cause a processor to execute the MySQL data synchronization method according to any one of claims 1-4.
10. A MySQL data synchronization system, characterized in that, The system includes a first database, a second database, and a storage module, wherein the first database is connected to the second database and the storage module. The first database is used to execute the MySQL data synchronization method as described in any one of claims 1-3; The second database is used to execute the MySQL data synchronization method as described in claim 4; The storage module is used to store transaction identifiers.
Citation Information
Patent Citations
Database management method and database system
CN107368485A
Safe distributed database interaction method suitable for mobile positioning system
CN109995530A