MySQL master-slave replication optimization method and device, medium and equipment

Abstract

The invention provides a MySQL master-slave replication optimization method and device, a medium and equipment, and the method comprises the steps: requesting to obtain binlog event data of a master node through an I / O thread, and generating a RelayLog according to the obtained event data; and reading the SQL statement in the RelayLog through an SQL thread, carrying out SQL statement analysis and SQL merging processing, and carrying out playback based on the SQL subjected to the merging processing. The master-slave copying efficiency can be improved.

Description

Technical Field

[0001] This application relates to the field of database management technology, and in particular to a MySQL master-slave replication optimization method, apparatus, medium and device. Background Technology

[0002] When dealing with large volumes of data, a single database cannot meet the demands of data storage, security, and high concurrency. Therefore, multiple master-slave data servers are required to achieve master-slave replication, increase data reliability, separate read and write operations, and reduce database pressure and table and row locking issues caused by the storage engine. Improving the efficiency of master-slave replication is crucial for database data processing efficiency and has always been a technical problem that those skilled in the art need to consider. Summary of the Invention

[0003] In view of this, this application provides a MySQL master-slave replication optimization method, apparatus, medium and device, the main purpose of which is to improve the efficiency of master-slave replication.

[0004] According to one aspect of this application, a MySQL master-slave replication optimization method is provided, which involves performing the following steps on the slave node:

[0005] The I / O thread requests and retrieves binlog event data from the master node, and generates a RelayLog based on the retrieved event data.

[0006] The SQL thread reads the SQL statements in the RelayLog, performs SQL statement parsing and SQL merging, and replays the merged SQL statements.

[0007] In one implementation, the SQL statement parsing and SQL merging process includes:

[0008] The RelayLog is parsed, and the parsed SQL statements are stored in the SQL pending queue;

[0009] A SQL merging program is started, which reads SQL statements from the SQL pending queue. While reading the SQL statements, it merges the SQL statements according to the merging scenario strategy. After reading for a specified time interval, the current round of SQL reading ends. After the current SQL merging is completed, the merged SQL is stored in the SQL replay queue. At the same time as the current round of SQL reading ends, the next round of reading and merging operations is started. The merged SQL of the previous round can only be stored in the SQL replay queue after the merged SQL of the previous round has been stored in the SQL replay queue.

[0010] One implementation also includes:

[0011] Based on predefined read time parameters, determine the specified time interval from the first SQL statement to the last SQL statement in each round of SQL merging.

[0012] In one implementation, the step of merging SQL statements according to the merging scenario strategy includes:

[0013] The logic for determining the order of SQL statements to be merged is as follows: among the SQL statements that appear before each other, the SQL statement that appears first is executed first; within the same row of data, executing first is equivalent to ending first.

[0014] Based on the logical order of the SQL statements to be merged, operations on the same table or in the same row are merged.

[0015] In one implementation, the merging scenario includes any of the following cases:

[0016] In the scenario of multiple batch insert operations on the same table, multiple batch insert operations on the same table are merged into one insert operation. The constraint is set that the number of values ​​after merging cannot exceed a predetermined number of entries.

[0017] In the scenario of multiple batch deletion operations on the same table, multiple batch deletion operations on the same table are merged into a single deletion operation. The constraint is set that the number of values ​​after merging cannot exceed a predetermined number of entries.

[0018] In the case of different DML operations on the same data, where a single row of data has undergone multiple different DML operations, these operations are merged into a single operation. The constraint is set as follows: a primary key / unique index exists on the table, and the primary key / unique key has not been updated.

[0019] In the scenario of merging DML and DDL, any DML + Truncate = Truncate, and any DML + Drop table = Drop table, the constraint is set as follows: in the scenario of merging DML and DDL in the same table, the DML executed first is executed later.

[0020] In DDL and DDL merging scenarios, where Alter table + drop table = drop table and Alter table + Alter table = Alter table, the constraint is set as follows: for DDL and DDL merging scenarios involving the same table, the Alter table is executed first, and the drop table is executed later.

[0021] According to one aspect of this application, a MySQL master-slave replication optimization device is provided, comprising:

[0022] The I / O thread execution unit is used to request and obtain binlog event data from the master node through the I / O thread, and generate RelayLog based on the obtained event data.

[0023] The SQL thread execution unit is used to read the SQL statements in the RelayLog through the SQL thread, perform SQL statement parsing and SQL merging processing, and replay the merged SQL statements.

[0024] In one implementation,

[0025] The SQL thread execution unit is specifically used to parse the RelayLog, store the parsed SQL statements in the SQL pending queue; start an SQL merging program, read SQL statements from the SQL pending queue, and merge the SQL statements according to the merging scenario strategy while reading them. After reading for a specified time interval, the current round of SQL reading ends. After the current SQL merging is completed, the merged SQL is stored in the SQL replay queue. At the same time as the current round of SQL reading ends, the next round of reading and merging operations is started. The merged SQL in the previous round can only be stored in the SQL replay queue after the merged SQL in the previous round has been stored in the SQL replay queue.

[0026] In one implementation,

[0027] The SQL thread execution unit is also used to determine a specified time interval from the first SQL statement to the last SQL statement in each round of SQL merging, based on a predefined read time parameter.

[0028] In one implementation,

[0029] The SQL thread execution unit is specifically used to determine the order of SQL statements to be merged. The logic is as follows: among the SQL statements that appear one after another, the SQL statement that appears first is executed first; in the same row of data, the first execution is equivalent to the first termination; based on the logic of the order of SQL statements to be merged, the operations on the same table or row are merged.

[0030] In one implementation,

[0031] The merging scenarios include any of the following:

[0032] In the scenario of multiple batch insert operations on the same table, multiple batch insert operations on the same table are merged into one insert operation. The constraint is set that the number of values ​​after merging cannot exceed a predetermined number of entries.

[0033] In the scenario of multiple batch deletion operations on the same table, multiple batch deletion operations on the same table are merged into a single deletion operation. The constraint is set that the number of values ​​after merging cannot exceed a predetermined number of entries.

[0034] In the case of different DML operations on the same data, where a single row of data has undergone multiple different DML operations, these operations are merged into a single operation. The constraint is set as follows: a primary key / unique index exists on the table, and the primary key / unique key has not been updated.

[0035] In the scenario of merging DML and DDL, any DML + Truncate = Truncate, and any DML + Drop table = Drop table, the constraint is set as follows: in the scenario of merging DML and DDL in the same table, the DML executed first is executed later.

[0036] In DDL and DDL merging scenarios, where Alter table + drop table = drop table and Alter table + Alter table = Alter table, the constraint is set as follows: for DDL and DDL merging scenarios involving the same table, the Alter table is executed first, and the drop table is executed later.

[0037] According to one aspect of this application, a storage medium is provided that stores a computer program, wherein the computer program is configured to execute the above-described MySQL master-slave replication optimization method at runtime.

[0038] According to one aspect of this application, an electronic device is provided, including a memory and a processor, wherein the memory stores a computer program, and the processor is configured to run the computer program to perform the above-described MySQL master-slave replication optimization method.

[0039] Using the above technical solutions, this application provides a MySQL master-slave replication optimization method, apparatus, medium, and device. To address the problems in existing solutions where a large number of binlog logs are generated when the master node performs numerous data deletions or update / insert operations, and where poor database disk performance leads to low binlog replay efficiency in the MySQL slave database, resulting in high data synchronization latency and impacting overall system performance, this application provides a MySQL master-slave replication optimization method. After obtaining the binlog event data from the slave node, the binlog event data is transmitted to the RelayLog. SQL statements in the RelayLog are parsed and merged to reduce SQL statement redundancy, thereby alleviating the high data synchronization latency problem and improving overall system performance.

[0040] It is evident that the embodiments of this application have at least the following technical effects: 1. Improved synchronization efficiency: By merging the SQL statements in the RelayLog, the number of SQL statements that need to be replayed is reduced, the total execution time of SQL is reduced, and efficiency is improved; 2. Reduced resource consumption: SQL merging reduces the number of times SQL statements are processed and the host resource consumption (CPU, memory, disk I / O), which is friendly to scenarios with high host resource consumption and SQL merging.

[0041] The above description is only an overview of the technical solution of this application. In order to better understand the technical means of this application and to implement it in accordance with the contents of the specification, and to make the above and other objects, features and advantages of this application more obvious and understandable, the following are specific embodiments of this application. Attached Figure Description

[0042] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments of this application and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings:

[0043] Figure 1 This illustration shows a schematic diagram of a MySQL master-slave replication principle provided in an embodiment of this application;

[0044] Figure 2 The flowchart shown is a MySQL master-slave replication optimization method provided in an embodiment of this application;

[0045] Figure 3 This illustration shows a schematic diagram of an example of a MySQL master-slave replication optimization method provided in an embodiment of this application;

[0046] Figure 4 This illustration shows a schematic diagram of a MySQL master-slave replication optimization device provided in an embodiment of this application. Detailed Implementation

[0047] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of the present application, and not all of them. Based on the embodiments of the present application, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present application. It should be noted that, unless otherwise specified, the embodiments and features in the embodiments of the present application can be combined with each other.

[0048] Master-slave replication refers to using one database instance as the master database and replicating its data to one or more slave databases in real time. The master database handles all write operations (such as insert, update, and delete), while the slave databases typically only handle read operations. Master-slave replication distributes read requests across multiple slave databases, thereby improving system read performance. Types of master-slave replication include asynchronous replication, semi-synchronous replication, and synchronous replication.

[0049] Asynchronous replication: The master database returns immediately after completing the write operation, without waiting for the slave database to complete the replication. This method has lower latency, but may lead to data inconsistency between the master and slave databases.

[0050] Semi-synchronous replication: After a write operation is completed, the master database waits for at least one slave database to confirm that the data has been received. This method ensures a certain degree of data consistency.

[0051] Synchronous replication (parallel replication): After a write operation is completed, the master database must wait for all slave databases to confirm receipt of the data before returning. This method ensures data consistency between master and slave databases, but it increases operation latency.

[0052] In the current master-slave replication architecture of MySQL, parallel replication is an optimization technique introduced to improve the efficiency of the slave database during data synchronization. Traditional MySQL replication is single-threaded, meaning that the slave can only execute the binlog of the master database one record at a time when replaying it, which can become a performance bottleneck under high load. Parallel replication significantly improves the throughput and efficiency of replication by allowing multiple threads to process binlog events simultaneously.

[0053] Parallel replication can be further divided into the following modes.

[0054] 1. Database-Based Parallel Replication

[0055] Principle: Transactions from different databases can be executed in parallel because there is no data dependency between them.

[0056] Limitations: Suitable for multi-database environments, but its effectiveness is limited in single-database or cross-database transaction scenarios.

[0057] 2. Group Commit-Based Parallel Replication

[0058] Principle: Utilizing the group commit feature of transactions on the master database. When the master database commits a transaction, it commits multiple transactions together, and these transactions can be executed in parallel on the slave database.

[0059] Advantages: It is not dependent on the number and structure of databases and is suitable for single-database environments.

[0060] 3Based on WRITESET (WRITESET-Based Parallel Replication)

[0061] Principle: By analyzing the write set of transactions, it is determined which transactions can be executed in parallel.

[0062] Advantages: finer-grained parallelism, suitable for complex transactional environments.

[0063] During the research process, the inventors of this application discovered that although MySQL's parallel replication mechanism significantly improves the efficiency of data synchronization, some problems and challenges may still be encountered in practice.

[0064] 1. Parallel replication relies on the independence between transactions to improve efficiency. If there are dependencies between transactions (e.g., multiple transactions modifying the same row of data), subsequent transactions must wait for the first transaction to complete before they can execute.

[0065] 2. Parallel replication increases the CPU and memory usage of the slave database. If resources are insufficient, this may lead to a decrease in system performance.

[0066] 3. The parallel processing capability of a database has an upper limit. As the amount of data and SQL operations increases, the replay efficiency of the slave database's RelayLog will be affected by insufficient host resources, and the host will also reduce its response efficiency due to excessive concurrency, ultimately affecting the replay efficiency of the slave database.

[0067] Therefore, a new method is needed to effectively improve the replay efficiency of MySQL slave database RelayLog, reduce latency, and improve system performance.

[0068] See Figure 1 The diagram illustrates a MySQL master-slave replication principle provided in an embodiment of this application.

[0069] MySQL uses a log called the binlog. This log records all SQL statements that modify the database (insert, update, delete, create, alter, drop table, grant, etc.). Master-slave replication can be understood as copying the binlog from the master node (master server) to the slave node (slave server) and executing it, so that the data on the slave node is identical to the data on the master node. Through master-slave replication, statements executed on the master node are executed as equivalent statements on the slave node, ensuring that operations on the master node produce the same results on the slave node.

[0070] The basic process of master-slave replication is as follows:

[0071] (1) Enable binary logging on the master node to record any events that modify database data;

[0072] (2) Start a thread (I / O Thread) on the slave node to request events from the master node's binary log file;

[0073] (3) The master node starts a thread (dump thread) to push the binlog required by the slave node to the slave node;

[0074] (4) When a node receives data sent from a master node, it places it in the RelayLog file and records the specific location within which binary log file of the master node the request was sent to (there will be multiple binlogs in the master node).

[0075] (5) Start another thread (sql thread) from the slave node, read the events in the Relay log, and execute them on the slave node.

[0076] During their research, the inventors discovered that when the master node experiences a large number of data deletions or update / insert operations, it generates a large amount of binlog logs. Furthermore, poor database disk performance leads to low RelayLog replay efficiency in the MySQL slave database, resulting in high data synchronization latency and impacting overall system performance. Based on this analysis, this application provides a method to reduce the number of replays by merging SQL statements in the RelayLog, thereby reducing unnecessary I / O operations and improving the RelayLog replay efficiency of the MySQL slave database.

[0077] Normally, the `start slave` command starts both the I / O thread and the SQL thread simultaneously. In this embodiment, the functions of the I / O thread and the SQL thread are as follows:

[0078] 1. Functions of I / O threads

[0079] The slave node's I / O thread requests the binlog events required by the slave node from the master node, and the master node pushes the binlog events to the slave node's I / O thread through the dump thread;

[0080] After receiving these binlog events from the node's I / O thread, a RelayLog is generated.

[0081] 2. Functionality of SQL Threads

[0082] The SQL statements that need to be replayed are parsed from the RelayLog and stored in the SQL pending queue. At this time, the SQL merging program reads the SQL from the SQL pending queue according to the SQL merging rules and merges them.

[0083] The merged SQL is stored in the SQL replay queue, and the SQL statements in the SQL replay queue are executed from it.

[0084] See Figure 2 This document illustrates a flowchart of a MySQL master-slave replication optimization method provided in an embodiment of this application. This MySQL master-slave replication optimization method is used to control parallel master-slave replication based on RelayLog processing. The method includes the following steps S101-S104. In this embodiment, the master node and slave node can be understood as a master server and a slave server, respectively. The master node runs the master database, and the slave node runs the slave database.

[0085] S201: Request the master node's binlog event data through the I / O thread, and generate a RelayLog based on the retrieved events.

[0086] When a slave node requests binlog data from the master node, it may use the file+position method to locate the transaction, or it may specify master_auto_position=1 to automatically locate the transaction position (if GTID is supported). For example, in GTID mode, when requesting incremental data, the node will find the position corresponding to the incremental data and send it; in this case, the request command does not include a position parameter. S202: Through the SQL thread, the SQL statements in the RelayLog are read, parsed, and merged, and then replayed based on the merged SQL.

[0087] In one implementation, compared to the existing slave playback logic, the embodiments of this application may add the following queues or procedures:

[0088] 1. SQL pending queue, used to store SQL generated by RelayLog parsing;

[0089] 2. SQL replay queue: The SQL Thread retrieves SQL from the replay queue and replays it concurrently.

[0090] 3. SQL merging program, which merges SQL statements based on the SQL merging scenario.

[0091] In one implementation, parsing and merging SQL statements in the RelayLog may include the following steps:

[0092] (1) Parse the RelayLog and store the parsed SQL statements in the SQL pending queue;

[0093] (2) Start an SQL merging program, read SQL statements from the SQL pending queue, and merge SQL statements according to the merging scenario strategy while reading SQL statements. After reading for a specified time interval, the current round of SQL reading ends. After the current SQL merging ends, the merged SQL is stored in the SQL replay queue. At the same time as the current round of SQL reading ends, start the next round of reading and merging operation. The merged SQL can only be stored in the SQL replay queue after the previous round has stored the merged SQL in the SQL replay queue.

[0094] Therefore, this process can be understood as including the following steps:

[0095] 1. Continuously read SQL statements from the RelayLog and store them in the SQL pending queue (reading continuously without interruption);

[0096] 2. The SQL merging program retrieves SQL from the SQL pending queue and performs merging processing at specified time intervals (within the same batch). Once the merging of this batch is complete, the merged SQL will be stored in the SQL replay queue. Before storing, it needs to wait for the merged data of the previous batch to be stored in the SQL replay queue (to ensure that the data is ordered).

[0097] 3. Once this batch of SQL retrieval is complete, the next batch of SQL retrieval and merging will begin immediately.

[0098] Therefore, the replay efficiency of the MySQL slave RelayLog is improved by merging SQL statements. Specifically, by merging SQL statements, the number of SQL statements is reduced, thereby reducing the host resources required for SQL execution and reducing SQL lock conflicts, thus speeding up the replay and improving the overall synchronization efficiency.

[0099] As analyzed above, when the master node experiences a large number of data deletions or update / insert operations, it generates a large amount of binlog logs. Furthermore, if the database disk performance is poor, the replay efficiency of the MySQL slave database becomes low, resulting in high data synchronization latency and impacting overall system performance. Therefore, this embodiment of the application effectively reduces SQL statement redundancy by merging SQL statements in the RelayLog, thereby alleviating the problem of high data synchronization latency and improving overall system performance.

[0100] It is evident that the embodiments of this application have at least the following technical effects:

[0101] 1. Improve synchronization efficiency: By merging the SQL statements in the RelayLog, the number of SQL statements that need to be replayed is reduced, the total SQL execution time is reduced, and efficiency is improved;

[0102] 2. Reduced resource consumption: SQL merging reduces the number of SQL statements processed and the consumption of host resources (CPU, memory, disk I / O), making it suitable for scenarios with high host resource consumption and SQL merging capabilities.

[0103] Furthermore, the embodiments of this application are simple, requiring only processing improvements at the slave node.

[0104] See Figure 3 The diagram illustrates an example of a MySQL master-slave replication optimization method provided in an embodiment of this application. Figure 3 In this process, after the slave reads binlog event data from the master and stores it in the RelayLog, the RelayLog data is not directly replayed. Instead, a processing program performs data parsing and SQL merging, and the merged data is stored in the replay queue. The event data in the replay queue is then used for replay.

[0105] In this example, the RelayLog replay steps from the slave are as follows:

[0106] Step 1: After the master database's binlog is synchronized to the slave database, the slave database generates a RelayLog.

[0107] Step 2: The slave database parses the RelayLog, and the parsed SQL will enter the SQL pending processing queue;

[0108] Step 3: The SQL merging program will generate a processing queue and retrieve SQL from the SQL pending queue. While retrieving SQL, it will process the SQL according to the merging scenario and store the processed SQL in the processing queue. After a specified time (configurable, default is 3 seconds), the SQL will be added to a new processing queue and the newly added SQL will continue to be processed. The old processing queue will be converted into an SQL replay queue.

[0109] Step 4: The SQL Thread of the slave database retrieves the SQL from the SQL replay column and replays it.

[0110] In practice, the following parameters can be added to the statement:

[0111] sql_merge_time

[0112] The default value is 3 seconds, which defines the time from the first SQL statement retrieved to the last SQL statement retrieved in each round of SQL merging.

[0113] 1. Determine the logical order of the SQL statements to be merged.

[0114] 1) The SQL statement that appears first is executed first. For example, in insert+delete, if insert appears first and then delete, it means that insert is executed first, and then delete is executed.

[0115] 2) For the same row of data, the first one to be executed is equivalent to the first one to end, because the first one to be executed will be locked first, and only after the execution is completed can other changes be made to that row.

[0116] II. Specific scenario examples are given below.

[0117] Scenario 1: Multiple batch inserts on the same table

[0118] Multiple inserts into the same table can be combined into a single insert.

[0119] Constraint: Each insert value cannot exceed 1000 records (configurable).

[0120] Scenario 2: Multiple batch deletes on the same table

[0121] Multiple deletes on the same table can be combined into a single delete.

[0122] Constraint: Each delete operation can have a maximum of 1000 values ​​(configurable).

[0123] Scenario 3: Different DML operations on peer data

[0124] If a row of data has undergone multiple different DML operations (without changing the primary key / unique key), they can be merged into a single operation.

[0125] insert+delete = These two SQL operations delete the data without affecting the data.

[0126] insert+update = insert the latest value;

[0127] delete + insert = update the latest value;

[0128] The combination delete+update does not exist.

[0129] The combination UPDATE + INSERT does not exist.

[0130] `update+delete` = delete the data in that row.

[0131] Constraints: The table has a primary key / unique index (to locate rows), and the primary key / unique key cannot be updated.

[0132] Scenario 4: Merging DML and DDL

[0133] Any DML + Truncate = Truncate

[0134] Any DML+Drop table=Drop table

[0135] Constraint: In scenarios where DML and DDL operations on the same table are merged, the DML operation executed first will be executed later in the order of DDL operations.

[0136] Scenario 5: DDL+DDL merging

[0137] Alter table+drop table=drop table

[0138] Alter table+Alter table=Alter table

[0139] Constraints: For DDL operations within the same table and DDL merging scenarios, the ALTER TABLE operation executed first will be the DROP TABLE operation executed later.

[0140] III. Specific Operation Examples

[0141] Suppose the following SQL operations are recorded in the binlog within 3 seconds:

[0142] INSERT INTO tableA(id,value)VALUES(1,'A');

[0143] INSERT INTO tableA(id,value)VALUES(2,'B');

[0144] UPDATE tableA SET value='C'WHERE id=1;

[0145] UPDATE tableA SET value='D'WHERE id=1;

[0146] DELETE FROM tableA WHERE id=2;

[0147] INSERT INTO tableA(id,value)VALUES(3,'D');

[0148] The merged binlog will contain the following SQL operations:

[0149] INSERT INTO tableA(id,value)VALUES(1,'A'),(3,'D');

[0150] UPDATE tableA SET value='C'WHERE id=1;

[0151] The insertion and deletion operations for record ID 2 were removed.

[0152] Multiple insert statements are merged into a single batch insert.

[0153] The statement is simplified, and the operation with ID 2 retains the result of the last operation.

[0154] See Figure 4 The diagram shows a schematic of the structure of a MySQL master-slave replication optimization device provided in an embodiment of this application.

[0155] This MySQL master-slave replication optimization device is used to control parallel master-slave replication based on RelayLog processing. The slave nodes can be one or more, and the device is located on the slave node. It includes the following units:

[0156] I / O thread execution unit 401 is used to request and obtain binlog event data of the master node through I / O thread, and generate RelayLog based on the obtained event data;

[0157] SQL thread execution unit 402 is used to read SQL statements in RelayLog through SQL thread, perform SQL statement parsing and SQL merging processing, and replay based on the merged SQL.

[0158] In one implementation,

[0159] The SQL thread execution unit 402 is specifically used to parse the RelayLog, store the parsed SQL statements in the SQL pending queue; start an SQL merging program, read SQL statements from the SQL pending queue, and merge the SQL statements according to the merging scenario strategy while reading the SQL statements. After continuously reading for a specified time interval, the current round of SQL reading ends. After the current SQL merging is completed, the merged SQL is stored in the SQL replay queue. At the same time as the current round of SQL reading ends, the next round of reading and merging operations is started. The merged SQL in the previous round can only be stored in the SQL replay queue after the merged SQL in the previous round has been stored in the SQL replay queue.

[0160] In one implementation,

[0161] The SQL thread execution unit 402 is further configured to determine the time from the first SQL statement to the last SQL statement in each round of SQL merging, based on a predefined read time parameter.

[0162] In one implementation,

[0163] The SQL thread execution unit 402 is specifically used to determine the order logic of the SQL statements to be merged as follows: among the SQL statements that appear one after another, the SQL statement that appears first is executed first; in the same row of data, the first execution is equivalent to the first termination; based on the order logic of the SQL statements to be merged, the operations of the same table or the same row are merged.

[0164] In one implementation,

[0165] The merging scenarios include any of the following:

[0166] In the scenario of multiple batch insert operations on the same table, multiple batch insert operations on the same table are merged into one insert operation. The constraint is set that the number of values ​​after merging cannot exceed a predetermined number of entries.

[0167] In the scenario of multiple batch deletion operations on the same table, multiple batch deletion operations on the same table are merged into a single deletion operation. The constraint is set that the number of values ​​after merging cannot exceed a predetermined number of entries.

[0168] In the case of different DML operations on the same data, where a single row of data has undergone multiple different DML operations, these operations are merged into a single operation. The constraint is set as follows: a primary key / unique index exists on the table, and the primary key / unique key has not been updated.

[0169] In the scenario of merging DML and DDL, any DML + Truncate = Truncate, and any DML + Drop table = Drop table, the constraint is set as follows: in the scenario of merging DML and DDL in the same table, the DML executed first is executed later.

[0170] In DDL and DDL merging scenarios, where Alter table + drop table = drop table and Alter table + Alter table = Alter table, the constraint is set as follows: for DDL and DDL merging scenarios involving the same table, the Alter table is executed first, and the drop table is executed later.

[0171] Embodiments of this application also provide a storage medium storing a computer program, wherein the computer program is configured to execute the steps in any of the above method embodiments when running.

[0172] Optionally, in this embodiment, the storage medium may be configured to store a computer program for performing the following steps:

[0173] The I / O thread requests and retrieves binlog event data from the master node, and generates a RelayLog based on the retrieved event data.

[0174] The SQL thread reads the SQL statements in the RelayLog, performs SQL statement parsing and SQL merging, and replays the merged SQL statements.

[0175] Optionally, in this embodiment, the storage medium may include, but is not limited to, various media capable of storing computer programs, such as USB flash drives, read-only memory (ROM), random access memory (RAM), portable hard drives, magnetic disks, or optical disks.

[0176] Embodiments of this application also provide an electronic device, including a memory and a processor, wherein the memory stores a computer program and the processor is configured to run the computer program to perform the steps in any of the above method embodiments.

[0177] Optionally, the electronic device may further include a transmission device and an input / output device, wherein the transmission device is connected to the processor and the input / output device is connected to the processor.

[0178] Optionally, in this embodiment, the processor can be configured to perform the following steps via a computer program:

[0179] The I / O thread requests and retrieves binlog event data from the master node, and generates a RelayLog based on the retrieved event data.

[0180] The SQL thread reads the SQL statements in the RelayLog, performs SQL statement parsing and SQL merging, and replays the merged SQL statements.

[0181] Optionally, specific examples in this embodiment can refer to the examples described in the above embodiments and optional implementations, and will not be repeated here.

[0182] The serial numbers of the above embodiments of the present application are for description only and do not represent the advantages or disadvantages of the embodiments.

[0183] In the above embodiments of this application, the descriptions of each embodiment have different focuses. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions of other embodiments.

[0184] In the several embodiments provided in this application, it should be understood that the disclosed technical content can be implemented in other ways. The device embodiments described above are merely illustrative; for example, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the displayed or discussed mutual coupling, direct coupling, or communication connection may be through some interfaces; the indirect coupling or communication connection between units or modules may be electrical or other forms.

[0185] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0186] Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.

[0187] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as a USB flash drive, read-only memory (ROM), random access memory (RAM), portable hard drive, magnetic disk, or optical disk.

[0188] The above is only a preferred embodiment of the present application. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the principles of the present application. These improvements and modifications should also be regarded as the scope of protection of the present application.

Claims

1. A MySQL master-slave replication optimization method, characterized in that, Perform the following steps on the slave node: The I / O thread requests and retrieves binlog event data from the master node, and generates a RelayLog based on the retrieved event data. The SQL thread reads the SQL statements in the RelayLog, performs SQL statement parsing and SQL merging, and replays the merged SQL statements.

2. The method according to claim 1, characterized in that, The SQL statement parsing and SQL merging process includes: The RelayLog is parsed, and the parsed SQL statements are stored in the SQL pending queue; A SQL merging program is started, which reads SQL statements from the SQL pending queue. While reading the SQL statements, it merges the SQL statements according to the merging scenario strategy. After reading for a specified time interval, the current round of SQL reading ends. After the current SQL merging is completed, the merged SQL is stored in the SQL replay queue. At the same time as the current round of SQL reading ends, the next round of reading and merging operations is started. The merged SQL of the previous round can only be stored in the SQL replay queue after the merged SQL of the previous round has been stored in the SQL replay queue.

3. The method according to claim 2, characterized in that, Also includes: Based on predefined read time parameters, determine the specified time interval from the first SQL statement to the last SQL statement in each round of SQL merging.

4. The method according to claim 2, characterized in that, The process of merging SQL statements according to the merging scenario strategy includes: The logic for determining the order of SQL statements to be merged is as follows: among the SQL statements that appear before each other, the SQL statement that appears first is executed first; within the same row of data, executing first is equivalent to ending first. Based on the logical order of the SQL statements to be merged, operations on the same table or in the same row are merged.

5. The method according to claim 4, characterized in that, The merging scenarios include any of the following: In the scenario of multiple batch insert operations on the same table, multiple batch insert operations on the same table are merged into one insert operation. The constraint is set that the number of values ​​after merging cannot exceed a predetermined number of entries. In the scenario of multiple batch deletion operations on the same table, multiple batch deletion operations on the same table are merged into a single deletion operation. The constraint is set that the number of values ​​after merging cannot exceed a predetermined number of entries. In the case of different DML operations on the same data, where a single row of data has undergone multiple different DML operations, these operations are merged into a single operation. The constraint is set as follows: a primary key / unique index exists on the table, and the primary key / unique key has not been updated. In the scenario of merging DML and DDL, any DML + Truncate = Truncate, and any DML + Drop table = Droptable. The constraint is set as follows: in the scenario of merging DML and DDL in the same table, the DML that is executed first is executed later. In DDL and DDL merging scenarios, where Alter table + drop table = drop table and Alter table + Alter table = Alter table, the constraint is set as follows: for DDL and DDL merging scenarios involving the same table, the Alter table is executed first, and the drop table is executed later.

6. A MySQL master-slave replication optimization device, characterized in that, Located at the slave node, including: The I / O thread execution unit is used to request and obtain binlog event data from the master node through the I / O thread, and generate RelayLog based on the obtained event data. The SQL thread execution unit is used to read the SQL statements in the RelayLog, perform SQL statement parsing and SQL merging processing, and replay the merged SQL statements.

7. The apparatus according to claim 6, characterized in that, The SQL thread execution unit is specifically used to parse the RelayLog, store the parsed SQL statements in the SQL pending queue; start an SQL merging program, read SQL statements from the SQL pending queue, and merge the SQL statements according to the merging scenario strategy while reading them. After reading for a specified time interval, the current round of SQL reading ends. After the current SQL merging is completed, the merged SQL is stored in the SQL replay queue. At the same time as the current round of SQL reading ends, the next round of reading and merging operations is started. The merged SQL in the previous round can only be stored in the SQL replay queue after the merged SQL in the previous round has been stored in the SQL replay queue.

8. The apparatus according to claim 7, characterized in that, The SQL thread execution unit is also used to determine a specified time interval from the first SQL statement to the last SQL statement in each round of SQL merging, based on a predefined read time parameter.

9. The apparatus according to claim 7, characterized in that, The SQL thread execution unit is specifically used to determine the order of SQL statements to be merged. The logic is as follows: among the SQL statements that appear one after another, the SQL statement that appears first is executed first; in the same row of data, execution first is equivalent to termination first; based on the logic of the order of SQL statements to be merged, operations on the same table or row are merged.

10. The apparatus according to claim 9, characterized in that, The merging scenarios include any of the following: In the scenario of multiple batch insert operations on the same table, multiple batch insert operations on the same table are merged into one insert operation. The constraint is set that the number of values ​​after merging cannot exceed a predetermined number of entries. In the scenario of multiple batch deletion operations on the same table, multiple batch deletion operations on the same table are merged into a single deletion operation. The constraint is set that the number of values ​​after merging cannot exceed a predetermined number of entries. In the case of different DML operations on the same data, where a single row of data has undergone multiple different DML operations, these operations are merged into a single operation. The constraint is set as follows: a primary key / unique index exists on the table, and the primary key / unique key has not been updated. In the scenario of merging DML and DDL, any DML + Truncate = Truncate, and any DML + Drop table = Droptable. The constraint is set as follows: in the scenario of merging DML and DDL in the same table, the DML that is executed first is executed later. In DDL and DDL merging scenarios, where Alter table + drop table = drop table and Alter table + Alter table = Alter table, the constraint is set as follows: for DDL and DDL merging scenarios involving the same table, the Alter table is executed first, and the drop table is executed later.

11. A storage medium, characterized in that, The storage medium stores a computer program, wherein the computer program is configured to execute the method described in any one of claims 1 to 5 when it is run.

12. An electronic device comprising a memory and a processor, characterized in that, The memory stores a computer program, and the processor is configured to run the computer program to perform the method as described in any one of claims 1 to 5.

Images