Multi-database compatible online DDL change method and system

Abstract

The invention discloses a multi-database compatible online DDL change method and system.The method comprises the steps that before DDL change of an original table is executed, a temporary table with the same table structure as the original table is created firstly, and then the DDL change of the original table is transferred to the temporary table; in the data synchronization stage, DML changes of an original table are captured in real time in a log changing mode, changed log records are analyzed, incremental data are recognized, and then the incremental data are synchronized into a temporary table; meanwhile, synchronizing the existing data in the original table to the temporary table in batches by adopting a shared lock mode; once data synchronization is completely completed, the temporary table is renamed as the name of the original table, and smooth transition of DDL change is achieved. According to the method and the system, efficient DDL change is realized by creating a temporary table, capturing and synchronizing incremental data by using a change log and changing a final table name, meanwhile, the consistency and the integrity of the data are ensured, and the influence on service operation is reduced.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of online DDL change, and in particular to a multi-database compatible online DDL change method and system. BACKGROUND

[0002] There are several major pain points when a relational database processes DDL (Data Definition Language) changes for large tables, as follows:

[0003] (1) Long table locking: During the execution of DDL changes, the database applies read or write locks on the entire table, preventing other DML changes from being performed. For large tables, DDL changes can take a long time, causing the table to be locked for a long time and seriously disrupting the normal operation of the business process.

[0004] (2) Blocking other transactions: Due to table locking, any other transaction attempting to access the table will be blocked, and even a deadlock may be triggered. This not only affects the ongoing DDL change, but also hinders the normal operation of other businesses.

[0005] (3) Single point of failure risk: During the DDL change process, if the master database fails, the entire change process will fail. In a master-slave replication environment, this can cause the table structure of the slave to be inconsistent with the master, increasing the risk of operation.

[0006] (4) Difficulty in rolling back: Once the DDL change is complete, it is difficult to roll it back to the state before the change. This poses a risk to operations, as it becomes very difficult to quickly recover if the change goes wrong.

[0007] For specific databases such as MySQL, the pt-online-schema-change tool can be used to implement online DDL changes for large tables. This tool creates a shadow table and triggers to synchronize data, thus completing the operation. However, for most databases, such a tool is lacking, so a general method is needed to support online DDL operations for multiple databases. SUMMARY

[0008] To solve the performance bottleneck, lock table, transaction blocking, single point failure risk and rollback difficulty of a relational database in processing large table DDL operation, the application provides a multi-database compatible online DDL change method and system, which realizes efficient DDL change by creating a temporary table, capturing and synchronizing incremental data by using a change log, and finally changing the table name, while ensuring data consistency and integrity, and reducing the impact on business operation.

[0009] To achieve the above object, the application adopts the following technical scheme:

[0010] In an embodiment of the application, a multi-database compatible online DDL change method is provided, which comprises:

[0011] Before performing the DDL change of the original table, a temporary table with the same table structure as the original table is created, and then the DDL change of the original table is transferred to the temporary table;

[0012] In the data synchronization stage, the DML change of the original table is captured in real time by using a change log, the changed log record is parsed, the incremental data is identified, and then the incremental data is synchronized to the temporary table; at the same time, the existing data in the original table is batch synchronized to the temporary table by using a shared lock mode, so as to ensure the consistency and integrity of the data in the synchronization process;

[0013] Once the data synchronization is completed, the temporary table is renamed as the name of the original table, and the smooth transition of the DDL change is realized;

[0014] The original table is deleted, and the process of real-time synchronization of incremental data is stopped.

[0015] Further, the batch synchronization step is as follows:

[0016] (1) According to the primary key field sorting, the number of returned records is limited to 1, the value of the primary key field is returned as the starting boundary of the original data and as the starting value of the first cycle;

[0017] (2) The data record block size of each cycle is specified, the value of the primary key field is specified in the filtering condition to be greater than the starting value of the current cycle, and the return of two records is limited, the first record is the cut-off value of the current cycle, and the second record is the starting value of the next cycle;

[0018] (3) According to the starting value and the cut-off value of the current cycle, a data synchronization SQL statement is generated, and a shared lock mode is held to synchronize the data;

[0019] (4) The steps (2) and (3) are cycled to obtain the starting value and the cut-off value, and the synchronization of the data is performed.

[0020] Further, for the SQL statement of obtaining the start value and the end value of the loop, it is judged according to the returned record number:

[0021] If two data are returned, it is considered that the current loop cannot synchronize all the unsynchronized data in the original table, and the next round of loop needs to continue to synchronize the data;

[0022] If only one data is returned, it is considered that the current loop can synchronize all the unsynchronized data in the original table;

[0023] If the returned data is empty, it is considered that the current loop can synchronize all the unsynchronized data in the original table.

[0024] Further, in the data synchronization stage, the distributed CDC tool is used to capture change events from multiple databases and convert them into consumable message formats, and write them into a message queue; the topic of Kafka is listened to, the change events are parsed, the increase, deletion, modification and query operations of the target table are converted and executed, so that the incremental data is synchronized.

[0025] In an embodiment of the present application, a multi-database compatible online DDL change system is also proposed, which comprises:

[0026] A temporary table creation module is used to create a temporary table with the same table structure as the original table before performing DDL change of the original table, and then transfer the DDL change of the original table to the temporary table;

[0027] A DDL change processing module is used to capture the DML change of the original table in real time through the change log in the data synchronization stage, parse the log record of the change, identify the incremental data, and then synchronize the incremental data to the temporary table; at the same time, the existing data in the original table is batch synchronized to the temporary table by using the shared lock mode, so as to ensure the consistency and integrity of the data in the synchronization process; once the data synchronization is completed, the temporary table is renamed as the name of the original table, the smooth transition of the DDL change is realized; the original table is deleted, and the process of real-time synchronization of incremental data is stopped.

[0028] Further, the steps of batch synchronization are as follows:

[0029] (1) According to the primary key field sorting, the returned record number is limited to 1, the value of the primary key field is returned as the starting boundary of the original data, and as the starting value of the first loop;

[0030] (2) The data record block size of each loop is specified, the value of the primary key field is specified in the filtering condition, which is greater than the starting value of the current loop, and the returned record number is limited to two, the first record is the end value of the current loop, and the second record is the starting value of the next loop;

[0031] (3) According to the start value and the end value of the current cycle, a SQL statement for data synchronization is generated, and a shared lock mode is held, and data is synchronized.

[0032] (4) The step (2) and the step (3) are cycled, the start value and the end value are acquired, and the synchronization of data is performed.

[0033] Further, for the SQL statement for acquiring the start value and the end value of the cycle, it is judged according to the returned record number:

[0034] If two data are returned, it is considered that the current cycle cannot synchronize all the unsynchronized data in the original table, and the next cycle needs to continue to synchronize the data;

[0035] If only one data is returned, it is considered that the current cycle can synchronize all the unsynchronized data in the original table;

[0036] If the returned data is empty, it is considered that the current cycle can synchronize all the unsynchronized data in the original table.

[0037] Further, in the data synchronization stage, a distributed CDC tool is used to capture change events from multiple databases, and the change events are converted into a consumable message format and written into a message queue; a topic of Kafka is listened to, change events are parsed, and increment data synchronization is realized by converting the change events into add, delete, modify and query operations of a target table and executing the add, delete, modify and query operations.

[0038] In an embodiment of the application, a computer device is also provided, which includes a memory, a processor, and a computer program stored in the memory and executable on the processor, and the processor implements the online DDL change compatible with multiple databases when executing the computer program.

[0039] In an embodiment of the application, a computer readable storage medium is also provided, and the computer readable storage medium stores a computer program for performing the online DDL change compatible with multiple databases.

[0040] Advantages:

[0041] 1. Cross-database compatibility: The online DDL change method provided by the application supports multiple database systems, including MySQL, Oracle, PostgreSQL and the like, and realizes wide compatibility with different database platforms.

[0042] 2. Reduce the table locking time: The application executes the DDL operation on the temporary table, avoids long-time locking of the original table, significantly reduces the interference to the business process, and improves the efficiency of the large table DDL operation.

[0043] 3. Real-time incremental data synchronization: The present application uses CDC tools such as Debezium to capture data change events and synchronizes them to temporary tables in real time, ensuring data consistency and integrity while avoiding the overhead of full data migration.

[0044] 4. Reduce single point failure risk: In a master-slave replication environment, the present application reduces the risk of single point failure caused by master failure by ensuring the consistency of the table structure of the slave database with the master database.

[0045] 5. Simplify rollback operation: Once the DDL change fails, the present application allows quick rollback to the state before the change, simplifying the operation and maintenance and reducing the risk of change failure.

[0046] 6. Improve business continuity: The present application reduces the impact of DDL operations on business, improves business continuity, and ensures the stability and availability of business during DDL changes. BRIEF DESCRIPTION OF DRAWINGS

[0047] Figure 1 is a flow chart of the online DDL change method compatible with multiple databases of the present application;

[0048] Figure 2 is a schematic diagram of incremental data synchronization of an embodiment of the present application;

[0049] Figure 3 is a schematic diagram of the online DDL change system structure compatible with multiple databases of the present application;

[0050] Figure 4 is a schematic diagram of the computer device structure of the present application. DETAILED DESCRIPTION

[0051] The principles and spirits of the present application will be described below with reference to several exemplary embodiments. It should be understood that these embodiments are given only to enable those skilled in the art to better understand and implement the present application, and do not limit the scope of the present application in any way. On the contrary, these embodiments are provided to make the present disclosure more thorough and complete, and to fully convey the scope of the present disclosure to those skilled in the art.

[0052] Those skilled in the art know that the embodiments of the present application can be implemented as a system, a system, a device, a method or a computer program product. Therefore, the present disclosure can be specifically implemented in the following forms: complete hardware, complete software (including firmware, resident software, microcode, etc.), or a combination of hardware and software.

[0053] According to an embodiment of the present application, a multi-database compatible online DDL change method is provided, which realizes efficient DDL change by creating a temporary table, capturing and synchronizing incremental data by using a change log, and finally changing the table name, while ensuring data consistency and integrity and reducing the impact on business operation.

[0054] The principles and spirits of the present application will be explained in detail below with reference to several representative embodiments of the present application.

[0055] Figure 1 is a flow chart of the multi-database compatible online DDL change method of the present application. As shown in the figure, the method comprises: Figure 1

[0056] Before performing the DDL change of the original table, a temporary table with the same table structure as the original table is first created, and then all DDL changes of the original table are transferred to the new temporary table. Since the temporary table does not contain any data initially, the DDL change can be quickly completed.

[0057] In the data synchronization stage, the DML changes (including insertion, update and deletion operations) of the original table are captured in real time by using the change log method, the log records of the changes are parsed, the incremental data is identified, and then the incremental changes are synchronized to the temporary table. At the same time, the existing data in the original table is safely batch-synchronized to the temporary table in batches by using the shared lock mode, so as to ensure the consistency and integrity of the data during the synchronization process.

[0058] Once the data synchronization is completely completed, the temporary table is renamed as the name of the original table, so as to realize the smooth transition of the DDL operation.

[0059] The original table is deleted, and the process of real-time synchronization of incremental data is stopped.

[0060] It should be noted that although the operations of the method of the present application are described in a specific order in the above embodiments and the accompanying drawings, this does not require or imply that the operations must be performed in the specific order, or that all the shown operations must be performed to achieve the desired results. Additionally or alternatively, certain steps can be omitted, multiple steps can be combined into one step, and / or one step can be divided into multiple steps.

[0061] In order to more clearly explain the above multi-database compatible online DDL change method, a specific embodiment will be described below, however, it should be noted that the embodiment is only used to better illustrate the present application and does not constitute an improper limitation on the present application.

[0062] Detailed implementation

[0063] 1. Create a temporary table ​

[0064] Copy the table structure of the original table to generate a temporary table. The temporary table has no data.

[0065] 2. DDL change

[0066] Convert the DDL change for the original table to a DDL change statement for the temporary table, and perform the DDL change operation on the temporary table. Since the temporary table has no data, the DDL change is very fast.

[0067] 3. Incremental data synchronization

[0068] As shown in the figure, use the distributed CDC (Change Data Capture) tool Debezium (existing tool) to capture change events from multiple databases and convert them into consumable message formats, and write them to message queues. Listen to the topic of Kafka, parse the change event, convert it to the insert, delete, update and query operation of the target table and execute it, so as to realize the synchronization of incremental data. Figure 2

[0069] Data change events include: insertion, deletion and update. Each data change event has its own data processing logic.

[0070] (1) Data insertion

[0071] Debezium will listen to this insertion operation and convert it into an event, which will be sent to a message queue such as Kafka.

[0072] Insertion operation statement for the original table:

[0073] INSERT INTO customers(first_name,last_name,email)VALUES('John','Doe','john.doe@example.com');

[0074] The following is the JSON representation of this insertion event:

[0075]

[0076]

[0077] The JSON data of the insertion event, payload contains the actual data, which is the data of the inserted row. id: automatically generated primary key. first_name, last_name, email: values provided when inserting.

[0078] ​The listener parses the Kafka insert event data and, for each newly inserted record of the original table, the temporary table can insert the corresponding new record in time. For the newly inserted record, there are two actions, the goal is to avoid the primary key conflict problem and ensure data consistency.

[0079] 1) If the temporary table already has a record with the same primary key, delete the old record first, then insert the new record. Two SQL statements are generated: a delete statement and an insert statement. The SQL statements are executed in order.

[0080] 2) If the temporary table does not have a record with the same primary key, directly insert the new record into the temporary table. One SQL statement is generated: an insert statement.

[0081] (2) Data deletion

[0082] Debezium will listen to this delete operation and convert it into a delete event, which will be sent to a message queue such as Kafka.

[0083] The delete operation statement for the original table:

[0084] DELETE FROM customers WHERE id=1004

[0085] JSON representation of the delete event:

[0086]

[0087]

[0088]

[0089] The JSON data of the delete event, the payload contains the actual deleted data, if multiple records are deleted, multiple JSON data of the deleted records will be generated.

[0090] The listener parses the Kafka delete event data and, for each deleted record of the original table, generates a delete SQL statement.

[0091] (3) Data update

[0092] For update operations, Debezium generates two events for each update: one representing the state before the update and the other representing the state after the update (carrying op as u, indicating that the event is an update operation). The following example JSON objects represent the state at two different stages of an update operation on the same record. The before object under payload (a field in Debezium event messages that carries the details of the database change) identifies the data before the update, and the after object under payload identifies the data after the update.

[0093] The update SQL statement for the original table:

[0094] The operation data for the update event:

[0095]

[0096]

[0097] The actions triggered by the data update event are as follows:

[0098] 1) If the primary key field of the update table is updated, and the value of the primary key has changed, delete the data of the temporary table with this primary key first, and then insert the data into the temporary table according to the updated data.

[0099] 2) If the non-primary key field of the update table is updated, update the data of the temporary table according to the primary key, and generate the update SQL statement.

[0100] 4. Batch data synchronization

[0101] Loop to copy data from the original table to the temporary table according to the data upper and lower boundaries, and execute the synchronization of the data in the shared lock mode. At this time, the data being synchronized cannot perform any DML operations.

[0102] The steps of batch synchronization are as follows:

[0103] (1) Before starting the first data synchronization, get the starting boundary of the original data.

[0104] Sort according to the primary key field, limit the number of returned records to 1, and return the value of the primary key field as the starting boundary of the original data, as the starting value of the first loop.

[0105] For example, for a MySQL database, the segment is an auto-incremented primary key id character column. The starting boundary of the original data can be obtained using the following SQL.

[0106] SELECT id FROM t1 ORDER BY id LIMIT 1.

[0107] Here, it is assumed that the returned data is 1, which is the start value of the first loop.

[0108] (2) Obtain the end value of the current loop and the start value of the next loop.

[0109] Specify the data record block size for each loop, i.e., the number of records synchronized for each loop, and specify in the filter condition that the value of the primary key field is greater than the start value of the current loop, limiting the return of two records.

[0110] For example, for a MySQL database, use the following SQL to return two records. The first record is the end value of the current loop, and the second record is the start value of the next loop.

[0111] SELECT id FROM t1(id>'1')) ORDER BY id LIMIT 999,2.

[0112] In the above example, id>'1' indicates that the value of the primary key field in the filter condition is greater than the start value of the current loop 1, and 999 in "LIMIT 999,2" is the data record block size for each loop minus 1, and 2 indicates that two records are returned. Assuming the return values are 1000 and 1001, 1000 is the end value of the current loop, and 1001 is the start value of the next loop.

[0113] (3) Generate a synchronized SQL statement based on the start value and end value of the current loop, and add a shared lock mode to synchronize data. For example, the synchronization example for MySQL is as follows:

[0114] insert into t_tmp(id,name,area) SELECT id,name,area

[0115] FROM t1 WHERE((id>='1')) AND((`id`<='1000')) LOCK IN SHARE MODE;

[0116] (4) Loop steps (2) and (3) to obtain the start value and end value and execute data synchronization.

[0117] SELECT id FROM t1(id>'1000')) ORDER BY id LIMIT 999,2.

[0118] 1000 is the end value of the last cycle. Similarly, the first returned record is the end value of the current cycle, and the second record is the start value of the next cycle.

[0119] Suppose the returned values are 2000 and 2001.

[0120] The generated SQL example of data synchronization is as follows:

[0121] insert into t_tmp(id,name,area)SELECT id,name,area

[0122] FROM t1 WHERE((id>='1001'))AND((`id`<='2000'))LOCK IN SHARE MODE;

[0123] (5) For the SQL statement of obtaining the start value and the end value of the cycle, it is judged according to the number of returned records:

[0124] 1) If two data can be returned, the current cycle cannot synchronize all the unsynchronized data in the t1 table, and the next cycle needs to continue to synchronize the data.

[0125] 2) If only one data is returned, the current cycle can synchronize all the unsynchronized data.

[0126] 3) If the returned data is empty, the current cycle can also synchronize all the unsynchronized data.

[0127] 5、Change the table name

[0128] The SQL statement of performing renaming is used to change the table name of the temporary table to the table name of the original table. For example, for the MySQL database, the following SQL can be executed:

[0129] RENAME TABLE t1 TO_t1_old,_t1_new TO t1;

[0130] 6、Delete the original table

[0131] The original table is deleted, and the process of incremental real-time synchronization is stopped.

[0132] Based on the same inventive concept, the application further provides a multi-database compatible online DDL changing system. The implementation of the system can refer to the implementation of the above method, and the repeated parts will not be described again. The term "module" used below can be a combination of software and / or hardware that realizes a predetermined function. Although the system described in the following embodiments is preferably realized in software, the realization of hardware, or a combination of software and hardware is also possible and is conceived.

[0133] Figure 3 Figure 1 is a schematic diagram of a multi-database compatible online DDL change system structure according to the present application. As shown in the figure, the system comprises: Figure 3

[0134] A temporary table creation module 101, which is used to create a temporary table with the same table structure as the original table before performing DDL change on the original table, and then transfer the DDL change of the original table to the temporary table.

[0135] A DDL change processing module 102, which is used to capture the DML change of the original table in real time through change log mode in the data synchronization stage, parse the log records of the change, identify the incremental data, and then synchronize the incremental data to the temporary table; at the same time, the existing data in the original table is batch synchronized to the temporary table in shared lock mode, to ensure the consistency and integrity of the data in the synchronization process; once the data synchronization is completed, the temporary table is renamed as the name of the original table, to realize the smooth transition of the DDL change; the original table is deleted, and the process of real-time synchronization of incremental data is stopped.

[0136] In the data synchronization stage, the distributed CDC tool is used to capture the change events from multiple databases, and convert them into consumable message format and write them into the message queue; the topic of Kafka is listened to, the change events are parsed, converted into the insert, delete, update and query operations of the target table and executed, to realize the synchronization of the incremental data.

[0137] The steps of batch synchronization are as follows:

[0138] (1) According to the primary key field sorting, the returned record number is limited to 1, and the value of the primary key field is returned as the starting boundary of the original data and as the starting value of the first cycle;

[0139] (2) The data record block size of each cycle is specified, and the value of the primary key field is specified in the filtering condition to be greater than the starting value of the current cycle, and the returned record number is limited to two, the first record is the cut-off value of the current cycle, and the second record is the starting value of the next cycle;

[0140] (3) According to the starting value and the cut-off value of the current cycle, the SQL statement for data synchronization is generated, and the shared lock mode is held, to synchronize the data;

[0141] (4) The steps (2) and (3) are cycled to obtain the starting value and the cut-off value, and execute the synchronization of the data.

[0142] (5) For the SQL statement for obtaining the starting value and the cut-off value of the cycle, the returned record number is judged according to the returned record number:

[0143] ​If two pieces of data are returned, it is considered that this loop cannot synchronize all the unsynchronized data in the original table, and the next loop needs to continue to synchronize the data;

[0144] If only one piece of data is returned, it is considered that this loop can synchronize all the unsynchronized data in the original table.

[0145] If the returned data is empty, it is considered that this loop can synchronize all the unsynchronized data in the original table.

[0146] It should be noted that although several modules of the multi-database compatible online DDL change system are mentioned in the foregoing detailed description, such division is merely exemplary and not mandatory. In fact, according to the embodiments of the present application, the features and functions of two or more modules described above can be embodied in one module. Conversely, the features and functions of one module described above can be further divided into several modules.

[0147] Based on the foregoing inventive concept, as shown in Figure 4 The present application also proposes a computer device 200, which comprises a memory 210, a processor 220, and a computer program 230 stored in the memory 210 and executable on the processor 220, wherein the processor 220 implements the foregoing multi-database compatible online DDL change method when executing the computer program 230.

[0148] Based on the foregoing inventive concept, the present application also proposes a computer readable storage medium, which stores a computer program for executing the foregoing multi-database compatible online DDL change method.

[0149] The multi-database compatible online DDL change method and system proposed by the present application have the following highlights:

[0150] 1. Cross-database compatibility: The online DDL change method proposed by the present application supports multi-database systems, including MySQL, Oracle, and PostgreSQL, etc., and realizes wide compatibility with different database platforms.

[0151] 2. Reduce table locking time: The present application performs DDL operations on a temporary table, avoiding long-time locking of the original table, significantly reducing the interference with business processes, and improving the efficiency of large table DDL operations.

[0152] 3. Real-time incremental data synchronization: The present application uses CDC tools such as Debezium to capture data change events and synchronizes them to a temporary table in real time, ensuring data consistency and integrity, while avoiding the overhead of full data migration.

[0153] 4. Reduce single point failure risk: In a master-slave replication environment, the present application reduces the risk of single point failure caused by master database failure by ensuring the consistency of the table structure of the slave database with the master database.

[0154] 5. Simplify rollback operation: Once the DDL change has a problem, the present application allows quick rollback to the state before the change, simplifying the operation and maintenance, and reducing the risk of change failure.

[0155] 6. Improve business continuity: The present application reduces the impact of DDL operation on business, improves the continuity of business, and ensures the stability and availability of business during DDL change.

[0156] Although the spirit and principles of the present application have been described with reference to several specific embodiments, it should be understood that the present application is not limited to the disclosed specific embodiments, and the division of aspects does not mean that the features in these aspects cannot be combined for benefit, but only for the convenience of expression. The present application is intended to cover various modifications and equivalent arrangements contained in the spirit and scope of the appended claims.

[0157] The scope of protection of the present application should be understood by those skilled in the art that various modifications or changes made on the basis of the technical solutions of the present application without creative labor are still within the scope of protection of the present application.

Claims

1. A multi-database compatible online DDL change method, characterized by, The method comprises: Before performing the DDL change of the original table, a temporary table with the same table structure as the original table is created, and the DDL change of the original table is transferred to the temporary table; In the data synchronization stage, the DML change of the original table is captured in real time through the change log, the log record of the change is parsed, the incremental data is identified, and then the incremental data is synchronized to the temporary table; at the same time, the existing data in the original table is batch synchronized to the temporary table in the shared lock mode, so as to ensure the consistency and integrity of the data in the synchronization process; Once the data synchronization is completed, the temporary table is renamed as the name of the original table, and the smooth transition of the DDL change is realized; The original table is deleted, and the process of real-time synchronization of incremental data is stopped.

2. The multi-database compatible online DDL change method according to claim 1, characterized in that, The steps of batch synchronization are as follows: (1) According to the primary key field sorting, the number of returned records is limited to 1, the value of the primary key field is returned as the starting boundary of the original data, and the starting value of the first cycle is returned; (2) The data record block size of each cycle is specified, the value of the primary key field is specified in the filtering condition, the return of two records is limited, the first record is the cut-off value of this cycle, and the second record is the starting value of the next cycle; (3) According to the starting value and the cut-off value of this cycle, the SQL statement of data synchronization is generated, and the shared lock mode is held to synchronize the data; (4) The starting value and the cut-off value are obtained by repeating steps (2) and (3), and the synchronization of the data is performed.

3. The multi-database compatible online DDL change method according to claim 2, wherein, For the SQL statement of obtaining the starting value and the cut-off value of the cycle, it is judged according to the number of returned records: If two data are returned, it is considered that this cycle cannot synchronize all the unsynchronized data in the original table, and the next cycle needs to continue to synchronize the data; If only one data is returned, it is considered that this cycle can exactly synchronize all the unsynchronized data in the original table; If the returned data is empty, it is considered that this cycle can synchronize all the unsynchronized data in the original table.

4. The multi-database compatible on-line DDL change method according to claim 1, wherein, In the data synchronization stage, the distributed CDC tool is used to capture the change event from multiple databases, and the change event is converted into a consumable message format and written into a message queue; the topic of Kafka is listened to, the change event is parsed, the incremental data is converted into the add, delete, modify and query operation of the target table and executed, so that the incremental data is synchronized.

5. A multi-database compatible online DDL change system, characterized by, The system comprises: A temporary table creation module is configured to create a temporary table having the same table structure as the original table before performing the DDL change of the original table, and to transfer the DDL change of the original table to the temporary table; A DDL change processing module is configured to capture the DML change of the original table in real time through the change log in the data synchronization stage, parse the log record of the change, identify the incremental data, and then synchronize the incremental data to the temporary table; at the same time, the existing data in the original table is batch synchronized to the temporary table in the shared lock mode, so as to ensure the consistency and integrity of the data in the synchronization process; once the data synchronization is completed, the temporary table is renamed as the name of the original table, and the smooth transition of the DDL change is realized; the original table is deleted, and the process of real-time synchronization of incremental data is stopped.

6. The multi-database compatible online DDL change system according to claim 5, wherein, The steps of batch synchronization are as follows: (1) According to the primary key field sorting, limit the returned record number to 1, return the value of the primary key field as the starting boundary of the original data, and as the starting value of the first loop; (2) Specify the data record block size of each loop, specify the value of the primary key field in the filter condition to be greater than the starting value of this loop, limit the return of two records, the first record is the end value of this loop, and the second record is the starting value of the next loop; (3) According to the starting value and the end value of this loop, generate a SQL statement for data synchronization, and hold a shared lock mode to synchronize data; (4) Loop steps (2) and (3) to obtain the starting value and the end value, and execute the synchronization of data.

7. The multi-database compatible online DDL change system according to claim 6, wherein, For the SQL statement for obtaining the starting value and the end value of the loop, according to the number of returned records: If two records are returned, it is considered that this loop cannot synchronize all the unsynchronized data in the original table, and the next round of loop needs to continue to synchronize the data; If only one record is returned, it is considered that this loop can synchronize all the unsynchronized data in the original table; If the returned data is empty, it is considered that this loop can synchronize all the unsynchronized data in the original table.

8. The multi-database compatible online DDL change system according to claim 5, wherein, In the data synchronization stage, the distributed CDC tool is used to capture change events from multiple databases and convert them into consumable message formats and write them to a message queue; listen to the topic of Kafka, parse the change events, convert them into insert, delete, update and query operations of the target table and execute them, thereby realizing the synchronization of incremental data.

9. A computer device comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, characterized in that, The processor executes the computer program to realize the method of any one of claims 1-4.

10. A computer-readable storage medium, characterized in that, The computer readable storage medium stores a computer program for executing the method of any one of claims 1-4.

Images