A CDC-based DDL change and key table data change monitoring method and system

Abstract

The present invention discloses a DDL change and key table data change monitoring method and system based on CDC. The present invention is constructed based on FlinkCDC, and monitors and configures the database structure and key configuration tables that need to be monitored through a system interface. After the flink process monitors that the target library table has changed, the change is recorded in the platform database, and the platform system derives the library table change SQL within a period of time according to the change record.

Description

Technical Field

[0001] The present invention relates to the field of databases, and in particular to a CDC-based DDL change and key table data change monitoring method and system. Background Art

[0002] The existing relational database monitoring technologies mainly include Canal, FlinkCDC, Seatunnel CDC, etc. They only implement basic database monitoring capabilities and have not been applied to the DevOps field. The existing solutions also have the problem of cumbersome and easy omissions in database script organization when releasing from the test environment to the production environment, the success rate of version release is low, and the pressure on development and operation and maintenance personnel is doubled. Summary of the invention

[0003] The purpose of the present invention is to provide a CDC-based DDL change and key table data change monitoring method and system thereof. The present invention proposes a more specific design method and application system for one-click derivation of database change scripts, cross-database management system support, automated monitoring and upgrade process capabilities.

[0004] To achieve the above purpose, the present invention provides the following technical solutions and operation steps:

[0005] Provide a CDC-based DDL change and key table data change monitoring method. This proposal is built based on FlinkCDC and includes the following steps:

[0006] S1. Create monitoring configuration;

[0007] S1-1 Login to the system: The user logs in to the system through a personal account and enters the user name and password;

[0008] S1-2 Enter the monitoring configuration page: After logging in, find and click the "Monitoring Configuration" option in the user homepage or navigation bar to enter the monitoring configuration page;

[0009] S1-3 Create a new monitoring configuration: In the monitoring configuration page, the user clicks the "New Configuration" button and the monitoring configuration form pops up;

[0010] S1-4 Configure monitoring objects: In the configuration form, users can select the database structure and key configuration tables to be monitored. In addition, users can set a time window to specify the time range for monitoring; they can also choose to specify specific DDL statements or data change types;

[0011] S1-5 Save Configuration: After the user fills in the configuration information, click the "Save Configuration" button, the system will save the user's monitoring configuration and prepare to start monitoring database changes;

[0012] S2, real-time monitoring status;

[0013] S2-1 Check monitoring status: Return to the user homepage or navigation bar, click the "Real-time Monitoring" option to enter the real-time monitoring status page;

[0014] S2-2 Visual display: On the real-time monitoring page, the system uses visual charts and other methods to intuitively display the real-time status of the current monitoring; users can clearly understand the changes of the monitored objects, including DDL changes and data changes of key tables;

[0015] S2-3 Customized monitoring strategy: Users can flexibly configure monitoring objects and frequencies according to actual needs, and customize monitoring strategies through the setting options provided on the page;

[0016] S3. Management change records;

[0017] S3-1 View change records: Enter the "Change Record Management" page, where users can view the change records stored in the system;

[0018] S3-2 Parsing and Analysis: The system regularly parses and analyzes change records. Users can learn more about each change on this page, including the change type, target table, and specific change content.

[0019] Step 4: Derive and implement changes;

[0020] S4-1 Enter the change derivation page: Click the "Derive Change" option to enter the change derivation page;

[0021] S4-2 Intelligent deduction: Based on the change information obtained through analysis, the system intelligently deduces the corresponding changes that need to be made to the test environment within a period of time;

[0022] S4-3 Review derivation results: Users can review the derivation results to ensure that the changes are applicable to the test environment; if necessary, users can return to make changes;

[0023] S5, data synchronization;

[0024] 5-1 Perform data synchronization: When the user is ready to upgrade the system, the system performs data synchronization based on the change information derived intelligently;

[0025] 5-2 Monitor the synchronization process: Users can monitor the synchronization process in real time on the "Data Synchronization Monitoring" page. The system provides detailed log records and audit information to ensure the transparency of the synchronization process;

[0026] 5-3 Handling synchronization exceptions: If an exception occurs during the synchronization process, the system will promptly notify the user, who can view the log to troubleshoot the problem and perform necessary rollback operations.

[0027] A CDC-based DDL change and key table data change monitoring system, characterized by including setting a monitoring time window, specifying specific DDL statements or data change types, providing real-time monitoring status display, providing a user-friendly visual interface, supporting multiple database management systems, providing a change record parsing module, providing script execution functions, and providing detailed logging and auditing functions.

[0028] Furthermore, the monitoring configuration capability mentioned in this system allows users to perform more detailed monitoring configuration, including setting the monitoring time window, specifying specific DDL statements or data change types, etc.; providing real-time monitoring status display, providing a user-friendly visual interface, allowing users to understand the real-time status of the system's current monitoring, and customizing monitoring strategies, allowing users to flexibly configure monitoring objects and frequencies according to actual needs.

[0029] Furthermore, the various database management systems mentioned in this system are specifically designed to automatically adapt to the syntax and structural changes of different database systems, reduce the need for manual intervention, and ensure the versatility and compatibility of the system; the performance construction of the FlinkCDC module is optimized to ensure that change records can be captured and transmitted efficiently and stably in scenarios with high concurrency and large data volumes.

[0030] Furthermore, the system supports storing monitored database change records in the platform database, including DDL changes and data changes of key tables. This ensures the integrity and accuracy of the records and provides storage policy configuration options, including data retention time, storage capacity management, etc., to adapt to different business scenarios and storage requirements.

[0031] Furthermore, the system provides a change record parsing module to parse and analyze the stored change records, taking into account the syntax and structural differences of different database management systems to ensure the accuracy and applicability of the parsing; based on the change information obtained from the parsing, the system automatically generates an upgrade script, which includes DDL statements and data change statements for synchronizing the test environment to ensure the consistency of the test environment and the production environment after the upgrade, and introduces a verification mechanism to ensure that the generated upgrade script does not cause irreversible data problems after being executed in the test environment.

[0032] Furthermore, the script execution function mentioned in this system ensures the successful execution of the upgrade script in the test environment; establishes a monitoring mechanism to track the script execution status in real time, and promptly detects and handles abnormal situations; provides a script execution rollback function, which can quickly roll back to the previous stable state when an execution exception occurs, thereby reducing the impact of the upgrade operation on the system.

[0033] Furthermore, the system provides detailed logging and auditing functions to record key information of each change, script generation and execution, which is convenient for tracing and troubleshooting. It introduces event tracing function and log encryption mechanism, so that users can trace the detailed process of each change, script generation and execution event, including the operator, timestamp, etc., while ensuring that sensitive information will not be leaked.

[0034] Compared with the prior art, the present invention has the following beneficial effects:

[0035] The present invention solves the problem of cumbersome and easy omissions in database script organization when releasing from a test environment to a production environment, improves the success rate of version release, and reduces the mental burden of development and operation and maintenance personnel by monitoring changes in the database over a period of time, automatically generating upgrade scripts, and automating monitoring and upgrade process capabilities. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] Figure 1 The present invention is a flowchart of a method for monitoring DDL changes and key table data changes based on CDC. DETAILED DESCRIPTION

[0037] In order to enable those skilled in the art to better understand the technical solution of the present invention, the present invention is described in detail below in conjunction with the accompanying drawings. The description in this part is only exemplary and explanatory and should not have any limiting effect on the protection scope of the present invention.

[0038] It should be noted that similar reference numerals and letters denote similar items in the following drawings, and therefore, once an item is defined in one drawing, further definition and explanation thereof is not required in subsequent drawings.

[0039] It should be noted that the terms "center", "upper", "lower", "left", "right", "vertical", "horizontal", "inside", "outside", etc., indicating the orientation or positional relationship, are based on the orientation or positional relationship shown in the drawings, or are the orientation or positional relationship in which the invented product is usually placed when in use. They are only for the convenience of describing the present invention and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and therefore cannot be understood as limiting the present invention. In addition, the terms "first", "second", "third", etc. are only used to distinguish the description, and cannot be understood as indicating or implying relative importance.

[0040] In addition, the terms "horizontal", "vertical", "overhanging" and the like do not mean that the components are required to be absolutely horizontal or overhanging, but can be slightly tilted. For example, "horizontal" only means that its direction is more horizontal than "vertical", and does not mean that the structure must be completely horizontal, but can be slightly tilted.

[0041] In the description of the present invention, it is also necessary to explain that, unless otherwise clearly specified and limited, the terms "set", "install", "connect", and "connect" should be understood in a broad sense, for example, it can be a fixed connection, a detachable connection, or an integral connection; it can be a mechanical connection or an electrical connection; it can be a direct connection, or it can be indirectly connected through an intermediate medium, or it can be the internal communication of two elements. For ordinary technicians in this field, the specific meanings of the above terms in the present invention can be understood according to specific circumstances.

[0042] See also Figure 1 , providing a CDC-based DDL change and key table data change monitoring method. This proposal is built based on FlinkCDC and includes the following steps:

[0043] S1. Create monitoring configuration;

[0044] S1-1 Login to the system: The user logs in to the system through a personal account and enters the user name and password;

[0045] S1-2 Enter the monitoring configuration page: After logging in, find and click the "Monitoring Configuration" option in the user homepage or navigation bar to enter the monitoring configuration page;

[0046] S1-3 Create a new monitoring configuration: In the monitoring configuration page, the user clicks the "New Configuration" button and the monitoring configuration form pops up;

[0047] S1-4 Configure monitoring objects: In the configuration form, users can select the database structure and key configuration tables to be monitored. In addition, users can set a time window to specify the time range for monitoring; they can also choose to specify specific DDL statements or data change types;

[0048] S1-5 Save Configuration: After the user fills in the configuration information, click the "Save Configuration" button, the system will save the user's monitoring configuration and prepare to start monitoring database changes;

[0049] S2, real-time monitoring status;

[0050] S2-1 Check monitoring status: Return to the user homepage or navigation bar, click the "Real-time Monitoring" option to enter the real-time monitoring status page;

[0051] S2-2 Visual display: On the real-time monitoring page, the system uses visual charts and other methods to intuitively display the real-time status of the current monitoring; users can clearly understand the changes of the monitored objects, including DDL changes and data changes of key tables;

[0052] S2-3 Customized monitoring strategy: Users can flexibly configure monitoring objects and frequencies according to actual needs, and customize monitoring strategies through the setting options provided on the page;

[0053] S3. Management change records;

[0054] S3-1 View change records: Enter the "Change Record Management" page, where users can view the change records stored in the system;

[0055] S3-2 Parsing and Analysis: The system regularly parses and analyzes change records. Users can learn more about each change on this page, including the change type, target table, and specific change content.

[0056] Step 4: Derive and implement changes;

[0057] S4-1 Enter the change derivation page: Click the "Derive Change" option to enter the change derivation page;

[0058] S4-2 Intelligent deduction: Based on the change information obtained through analysis, the system intelligently deduces the corresponding changes that need to be made to the test environment within a period of time;

[0059] S4-3 Review derivation results: Users can review the derivation results to ensure that the changes are applicable to the test environment; if necessary, users can return to make changes;

[0060] S5, data synchronization;

[0061] 5-1 Perform data synchronization: When the user is ready to upgrade the system, the system performs data synchronization based on the change information derived intelligently;

[0062] 5-2 Monitor the synchronization process: Users can monitor the synchronization process in real time on the "Data Synchronization Monitoring" page. The system provides detailed log records and audit information to ensure the transparency of the synchronization process;

[0063] 5-3 Handling synchronization exceptions: If an exception occurs during the synchronization process, the system will promptly notify the user, who can view the log to troubleshoot the problem and perform necessary rollback operations.

[0064] A CDC-based DDL change and key table data change monitoring system, characterized by including setting a monitoring time window, specifying specific DDL statements or data change types, providing real-time monitoring status display, providing a user-friendly visual interface, supporting multiple database management systems, providing a change record parsing module, providing script execution functions, and providing detailed logging and auditing functions.

[0065] Furthermore, the monitoring configuration capability mentioned in this system allows users to perform more detailed monitoring configuration, including setting the monitoring time window, specifying specific DDL statements or data change types, etc.; providing real-time monitoring status display, providing a user-friendly visual interface, allowing users to understand the real-time status of the system's current monitoring, and customizing monitoring strategies, allowing users to flexibly configure monitoring objects and frequencies according to actual needs.

[0066] Furthermore, the various database management systems mentioned in this system are specifically designed to automatically adapt to the syntax and structural changes of different database systems, reduce the need for manual intervention, and ensure the versatility and compatibility of the system; the performance construction of the FlinkCDC module is optimized to ensure that change records can be captured and transmitted efficiently and stably in scenarios with high concurrency and large data volumes.

[0067] Furthermore, the system supports storing monitored database change records in the platform database, including DDL changes and data changes of key tables. This ensures the integrity and accuracy of the records and provides storage policy configuration options, including data retention time, storage capacity management, etc., to adapt to different business scenarios and storage requirements.

[0068] Furthermore, the system provides a change record parsing module to parse and analyze the stored change records, taking into account the syntax and structural differences of different database management systems to ensure the accuracy and applicability of the parsing; based on the change information obtained from the parsing, the system automatically generates an upgrade script, which includes DDL statements and data change statements for synchronizing the test environment to ensure the consistency of the test environment and the production environment after the upgrade, and introduces a verification mechanism to ensure that the generated upgrade script does not cause irreversible data problems after being executed in the test environment.

[0069] Furthermore, the script execution function mentioned in this system ensures the successful execution of the upgrade script in the test environment; establishes a monitoring mechanism to track the script execution status in real time, and promptly detects and handles abnormal situations; provides a script execution rollback function, which can quickly roll back to the previous stable state when an execution exception occurs, thereby reducing the impact of the upgrade operation on the system.

[0070] Furthermore, the system provides detailed logging and auditing functions to record key information of each change, script generation and execution, which is convenient for tracing and troubleshooting. It introduces event tracing function and log encryption mechanism, so that users can trace the detailed process of each change, script generation and execution event, including the operator, timestamp, etc., while ensuring that sensitive information will not be leaked.

[0071] The standard parts used in this application document can all be purchased from the market and can be customized according to the description in the specification and drawings. The specific connection methods of each part adopt conventional means such as mature bolts, rivets, welding, etc. in the prior art. The machinery, parts and equipment all adopt conventional models in the prior art. The control method is automatically controlled by a controller. The control circuit of the controller can be implemented by simple programming by technicians in this field, which is common knowledge in this field. The present invention is mainly used to protect mechanical devices, so the present invention will no longer explain the control method and circuit connection in detail. The peripheral controller mentioned in the specification can control the electrical components mentioned in this article, and the peripheral controller is a conventional known device.

[0072] It should be noted that, in this article, the terms "comprises", "includes" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or apparatus that includes a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or apparatus.

[0073] This article uses specific examples to illustrate the principles and implementation methods of the present invention. The above examples are only used to help understand the method and core ideas of the present invention. The above are only preferred implementation methods of the present invention. It should be pointed out that due to the limitations of textual expression, there are objectively infinite specific structures. For ordinary technicians in this technical field, without departing from the principles of the present invention, they can make several improvements, modifications or changes, and can also combine the above technical features in an appropriate manner; these improvements, modifications, changes or combinations, or the direct application of the inventive concept and technical solution to other occasions without improvement, should be regarded as the protection scope of the present invention.

Claims

1. A CDC-based DDL change and key table data change monitoring method. This proposal is built based on FlinkCDC and is characterized by: The following steps are included: S1. Create monitoring configuration; S1-1 Login to the system: The user logs in to the system through a personal account and enters the user name and password; S1-2 Enter the monitoring configuration page: After logging in, find and click the "Monitoring Configuration" option in the user homepage or navigation bar to enter the monitoring configuration page; S1-3 Create a new monitoring configuration: In the monitoring configuration page, the user clicks the "Create a new configuration" button and the monitoring configuration form pops up; S1-4 Configure monitoring objects: In the configuration form, users can select the database structure and key configuration tables to be monitored; in addition, users can set a time window to specify the time range for monitoring; they can also choose to specify specific DDL statements or data change types; S1-5 Save Configuration: After the user fills in the configuration information, click the "Save Configuration" button, and the system will save the user's monitoring configuration and prepare to start monitoring database changes; S2, real-time monitoring status; S2-1 Check monitoring status: Return to the user homepage or navigation bar, click the "Real-time Monitoring" option to enter the real-time monitoring status page; S2-2 Visual display: On the real-time monitoring page, the system directly displays the real-time status of the current monitoring in the form of visual charts; Users can clearly understand the changes of monitored objects, including DDL changes and data changes of key tables; S2-3 Customized monitoring strategy: Users can flexibly configure monitoring objects and frequencies according to actual needs, and customize monitoring strategies through the setting options provided on the page; S3. Management change records; S3-1 View change records: Enter the "Change Record Management" page, where users can view the change records stored in the system; S3-2 Parsing and Analysis: The system regularly parses and analyzes change records. Users can learn more about each change on this page, including the change type, target table, and specific change content. Step 4: Derive and implement changes; S4-1 Enter the change derivation page: Click the "Derive Change" option to enter the change derivation page; S4-2 Intelligent deduction: Based on the change information obtained through analysis, the system intelligently deduces the corresponding changes that need to be made to the test environment within a period of time; S4-3 Review derivation results: Users can review the derivation results to ensure that the changes are applicable to the test environment; if necessary, users can return to make changes; S5, data synchronization; 5-1 Perform data synchronization: When the user is ready to upgrade the system, the system performs data synchronization based on the change information derived intelligently; 5-2 Monitor the synchronization process: Users can monitor the synchronization process in real time on the "Data Synchronization Monitoring" page. The system provides detailed log records and audit information to ensure the transparency of the synchronization process; 5-3 Handling synchronization exceptions: If an exception occurs during the synchronization process, the system will promptly notify the user, who can view the log to troubleshoot the problem and perform necessary rollback operations.

2. A CDC-based DDL change and key table data change monitoring system, which is used to implement a CDC-based DDL change and key table data change monitoring method, characterized in that: It includes setting the monitoring time window, specifying specific DDL statements or data change types, providing real-time monitoring status display, providing a user-friendly visual interface, supporting multiple database management systems, providing the establishment of change record parsing modules, providing script execution functions, and providing detailed logging and auditing functions.

3. The CDC-based DDL change and key table data change monitoring system according to claim 2, characterized in that: The change record parsing module parses and analyzes the stored change records, taking into account the differences in syntax and structure of different database management systems to ensure the accuracy and applicability of the parsing; Based on the change information obtained through analysis, the system automatically generates an upgrade script, which includes DDL statements and data change statements for synchronizing the test environment to ensure the consistency between the test environment and the production environment after the upgrade. A verification mechanism is also introduced to ensure that the generated upgrade script does not cause irreversible data problems after being executed in the test environment.

Images