Database migration method and device for heterogeneous database and medium

Abstract

The embodiment of the invention discloses a database migration method and device for a heterogeneous database and a medium, and relates to the technical field of database migration, the method comprises the steps that database migration demand information is acquired, and the database migration demand information comprises current service information, target migration database information and source database information; automatically migrating the data table structure and the business data according to the database migration demand information, extracting SQL statements based on the source database information, and determining a plurality of original SQL statements of the source database; based on the target migration database information, determining current statement rule information, performing rule matching check on the original SQL statement according to the current statement rule information, determining a plurality of risk SQL statements, and generating a migration compatibility evaluation set; and generating a migration compatibility optimization reference file through the migration compatibility evaluation set, and realizing database migration of the source database and the target migration database based on the migration compatibility optimization reference file.

Description

Technical Field

[0001] The present invention relates to the technical field of database migration, and in particular to a database migration method, device and medium for heterogeneous databases. Background Art

[0002] With the rapid development of information technology, the security and stability of databases are extremely high. Many manufacturers have begun to gradually implement the localization migration of core or edge systems as planned. Database localization migration belongs to the very common category of heterogeneous database migration. It is a very challenging task in the industry, with high technical barriers, strong security requirements, and a harsh implementation environment. Heterogeneous database migration work mainly includes three categories: structural migration of database table structure, fields, indexes and other information; data migration of data export, conversion, and import; and application rectification migration of modifying application code and queries.

[0003] The existing database migration methods mainly obtain metadata information such as the storage model and table structure of the source database, and generate the organizational structure of the target database based on the self-built table structure mapping rules, such as data table name, column name, index information, etc.; then, based on the historical storage data, establish data mapping rules that adapt to data migration, and use batch processing methods such as data consistency freezing, multi-tasking, and multi-threading to synchronize the existing data in each table. Database migration is not only a data-level job, but also requires adaptive transformation of applications that rely on the database, including modifying SQL query statements, adjusting database connection configuration, and optimizing application logic. However, application rectification in traditional migration methods often relies on manual operations, which is inefficient and error-prone. At the same time, after the database migration, the execution performance of SQL is not considered, which may lead to poor performance of the migrated database and affect the overall system performance.

[0004] Therefore, in traditional heterogeneous database migration, application-level adaptation usually relies on manual troubleshooting. After database migration, the execution performance of SQL is not considered, resulting in potential compatibility issues, affecting migration efficiency and system performance. Summary of the invention

[0005] One or more embodiments of the present specification provide a database migration method, device and medium for heterogeneous databases, which are used to solve the following technical problems: In traditional heterogeneous database migration, application-level adaptation usually relies on manual troubleshooting. After database migration, the execution performance of SQL is not considered, resulting in potential compatibility issues, affecting migration efficiency and system performance.

[0006] One or more embodiments of this specification adopt the following technical solutions:

[0007] One or more embodiments of the present specification provide a database migration method for heterogeneous databases, the method comprising: obtaining database migration requirement information, wherein the database migration requirement information comprises current business information, target migration database information and source database information; automatically migrating a data table structure and business data in a source database according to the database migration requirement information, and extracting SQL statements based on the source database information to determine a plurality of original SQL statements corresponding to the source database; determining corresponding current statement rule information based on the target migration database information, performing rule matching checks on the plurality of original SQL statements according to the current statement rule information, determining a plurality of risky SQL statements, and generating a migration compatibility assessment set; generating a migration compatibility optimization reference file through the migration compatibility assessment set, and implementing database migration between the source database and the target migration database based on the migration compatibility optimization reference file.

[0008] One or more embodiments of this specification provide a database migration device for a heterogeneous database, including:

[0009] at least one processor; and,

[0010] a memory communicatively connected to the at least one processor; wherein,

[0011] The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the above method.

[0012] One or more embodiments of the present specification provide a non-volatile computer storage medium storing computer executable instructions, wherein the computer executable instructions are configured to execute the above method.

[0013] At least one of the above technical solutions adopted in the embodiments of this specification can achieve the following beneficial effects: through the above technical solution, by automatically extracting the original SQL statements of the source database and performing rule matching checks based on the current statement rule information of the target migration database, risky SQL statements can be automatically identified and determined, significantly reducing the tediousness and error-proneness of relying on manual troubleshooting of application adaptation problems in traditional methods; the generation of a migration compatibility evaluation set can clearly understand which SQL statements have compatibility problems, so as to perform targeted optimization and adjustment, thereby improving the accuracy and efficiency of adaptation; during the migration process, not only the migration of data is focused on, but also the execution performance of SQL statements is deeply considered. By performing rule matching checks on the original SQL statements, problems that may affect performance can be discovered in advance, providing information for subsequent SQL optimization The generated migration compatibility optimization reference file not only contains a list of risky SQL statements, but also provides optimization suggestions for these statements, which helps to quickly adjust and optimize SQL statements after migration to ensure that database performance is not affected. By identifying and resolving potential problems in advance, this technology reduces the number of reworks during the migration process and improves the overall efficiency of migration. Comprehensive compatibility assessments and optimization suggestions enhance the stability of the migration process and reduce the risk of migration failure due to compatibility issues, thereby increasing the success rate of migration. Through an automated and refined migration process, this technology reduces the business interruption time caused by migration and ensures business continuity and smoothness. The optimized database system is not only more stable and reliable, but also better supports business development and meets growing business needs. BRIEF DESCRIPTION OF THE DRAWINGS

[0014] In order to more clearly illustrate the technical solutions in the embodiments of this specification or the prior art, the following briefly introduces the drawings required for use in the embodiments or the prior art description. Obviously, the drawings described below are only some embodiments recorded in this specification. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative labor. In the drawings:

[0015] Figure 1 A schematic diagram of a flow chart of a database migration method for heterogeneous databases provided in an embodiment of this specification;

[0016] Figure 2 A schematic diagram of the structure of a database migration device for heterogeneous databases provided in an embodiment of this specification. DETAILED DESCRIPTION

[0017] In order to enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this specification will be clearly and completely described below in conjunction with the drawings in the embodiments of this specification. Obviously, the described embodiments are only part of the embodiments of this specification, not all of the embodiments. Based on the embodiments of this specification, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of this specification.

[0018] The embodiment of this specification provides a database migration method for a heterogeneous database. It should be noted that the execution subject in the embodiment of this specification can be a server or any device with data processing capabilities. Figure 1 A flowchart of a database migration method for heterogeneous databases provided in an embodiment of this specification is shown as follows: Figure 1 As shown, it mainly includes the following steps:

[0019] Step S101, obtaining database migration requirement information.

[0020] In one embodiment of the present specification, database migration requirement information is obtained, wherein the database migration requirement information includes current business information, target migration database information, and source database information. Current business information refers to business application information that depends on the source database, target migration database information includes the type, specific version, and compatibility requirements of the target migration database, and source database information includes the type and version of the source database, etc., representing the source database. In the embodiment of the present specification, the source database is a MySQL database and the target migration database is an OceanBase database, and the MySQL database is migrated to a domestic OceanBase database.

[0021] Step S102 , according to the database migration requirement information, the data table structure and business data in the source database are automatically migrated, and based on the source database information, SQL statements are extracted to determine a plurality of original SQL statements corresponding to the source database.

[0022] According to the database migration requirement information, the data table structure and business data in the source database are automatically migrated, specifically including: matching the corresponding database driver according to the source database information in the database migration requirement information to establish a connection with the source database based on the database driver; traversing the objects in the source database through the source database connection to determine the metadata of each object to build a source database portrait corresponding to the source database; extracting the data table structure based on the source database portrait to determine the DDL statement corresponding to the data table structure, and executing the DDL statement in a pre-built target migration database to create a data table structure identical to that of the source database to achieve automatic migration of the data table structure; obtaining the current business information in the database migration requirement information, and determining the business data migration time information based on the current business scenario information, wherein the business data migration time information includes at least one migration time node and a migration time window corresponding to the migration time node; and migrating the full data and the incremental data respectively according to the business data migration time information.

[0023] In one embodiment of the present specification, the required database driver is first determined according to the source database information (such as database type, version, etc.). The corresponding database driver is introduced in the application or migration script. For MySQL, the MySQL JDBC driver needs to be introduced. Use the API interface provided by the database driver to establish a connection with the source database. In this process, it is necessary to provide connection information such as database URL, user name, password, etc. Through the database connection, traverse all objects in the source database (such as tables, views, stored procedures, etc.). For each object, obtain its metadata (such as table name, column name, data type, index, etc.). Arrange the obtained metadata into a structure that is easy to understand and operate, such as JSON, XML or a custom object model, to build a portrait of the source database. Extract data table structure information from the source database portrait. Select a suitable target database system according to the target migration database information. Create a new database instance on the target database system, and perform basic configuration and initialization. Configure the connection information of the source database and the target database in the migration tool or script to ensure that the two databases can be successfully connected from the migration tool or script. Based on the metadata of the source database, analyze the objects that need to be migrated (mainly data table structures). According to the data table structure information, generate corresponding Data Definition Language (DDL) statements for each data table that needs to be migrated, such as CREATE TABLE, CREATE INDEX, etc. The statements should include creating tables, defining columns, setting indexes, constraints, etc. According to the current business information in the database migration requirement information, analyze the business operation status and the potential impact of data migration. Based on factors such as peak and off-peak periods of business operation, determine the appropriate migration time node and the migration time window corresponding to each node. Within the selected migration time window, export the full amount of data from the source database (you can use the database's built-in export tool or a custom script), and import the exported data into the target migration database. Set up an incremental data capture mechanism to capture and migrate incremental data that has occurred since the last migration within each migration time window.

[0024] Through the above technical solutions, through automated scripts and tools, manual intervention is reduced, and the efficiency and accuracy of migration are improved; the data table structure extracted based on the source database portrait can ensure that the data table structure created in the target database is completely consistent with the source database, reducing errors caused by manual operations; through reasonable migration time planning, migration can be carried out during the off-peak period of business, reducing the impact on the business; during the migration process, the incremental data capture mechanism ensures real-time synchronization of data and reduces the risk of data inconsistency; by matching different database drivers, it can support multiple types of source databases and target databases, improve the flexibility of migration, and the migration process can be expanded according to business needs, such as adding new data tables, adjusting the migration time window, etc.; automated migration reduces dependence on professionals and reduces labor costs. Through reasonable migration time planning, the utilization of network, storage and other resources can be optimized to reduce resource consumption during the migration process; during the migration process, the incremental data capture mechanism can ensure real-time synchronization of business data and achieve seamless migration; after the migration is completed, if any unexpected situation occurs, the data can be quickly restored through the backup and recovery mechanism to ensure business continuity.

[0025] Based on the current business scenario information, the business data migration time information is determined, specifically including: determining at least one reference continuous time interval based on the historical business peak information and the historical user active time information in the current business scenario information; collecting historical load data of the business system to determine multiple historical load indicator information; analyzing the system load of the business system through the multiple historical load indicator information to determine the periodic load change information of the business system, wherein the periodic load change information includes multiple valley continuous time intervals corresponding to the load valley and multiple peak time intervals corresponding to the load peak; obtaining the amount of data to be migrated in the source database to determine the reference migration duration corresponding to the source database based on the amount of data to be migrated and the reference load information of the business system; according to the periodic load change information, taking the intersection of the at least one reference continuous time interval and the valley continuous time interval to determine at least one load valley interval; determining the business data migration time information through the reference migration duration and the at least one load valley interval.

[0026] In one embodiment of the present specification, historical data analysis is performed based on historical business peak information and historical user active time information in the current business scenario information to identify continuous time periods with relatively stable business activities or low user activity as a reference for potential migration time windows, i.e., reference continuous time intervals. Historical load data of the business system is collected, including CPU usage, memory occupancy, I / O waiting time, network bandwidth usage, etc. Key load indicator information is extracted from the collected data for subsequent system load analysis. Using statistical or machine learning methods, multiple historical load indicator information is analyzed to identify the periodic load change pattern of the business system, and multiple continuous trough time intervals corresponding to the load trough and multiple peak time intervals corresponding to the load peak are determined. The above intervals are crucial for selecting the migration time because the migration process may generate additional load pressure on the business system.

[0027] According to the amount of data to be migrated in the source database and the reference load information of the business system, the estimated migration duration is calculated based on the amount of data to be migrated and the system processing capacity (which can be estimated through historical load data). Perform an intersection operation on at least one reference continuous time interval determined previously and the trough continuous time interval to find a time period that meets the requirements of smooth business operation and is at the trough of the system load. Based on the reference migration duration and the intersection result, determine the final business data migration time information. These time information should include the start time, end time and possible time arrangements of multiple migration batches. It should be noted that when determining the business data migration time information through the reference migration duration and the at least one load trough interval, the reference migration duration and the load trough interval can be compared to divide the migration task into a periodic execution mode, for example, executing the migration task in the daily load trough interval.

[0028] Through the above technical solution, by identifying the periodic load changes of the business system, especially the multiple continuous low valley time intervals corresponding to the load valley, data migration can be performed during these time periods, which can minimize the impact on the normal operation of the business and avoid performance degradation or service interruption caused by migration during the peak period of the business; determining the migration time information based on the load valley interval and the reference migration duration can ensure that the migration process is carried out within the optimal time window, which helps to reduce the total time required for migration and avoid migration delays caused by factors such as network congestion and system resource shortage; performing migration during the load valley period can more effectively utilize system resources, and the system's processing capacity is relatively abundant, which can better cope with the additional load that may be generated during the migration process; avoiding migration during peak hours can significantly reduce the pressure on the system due to migration, maintain the stability and reliability of the system, and prevent system crashes or performance bottlenecks caused by migration operations; through precise time planning, the service interruption time caused by migration can be reduced, thereby improving user experience. Migrating during the load valley period can reduce the risk of data loss or inconsistency caused by system busyness.

[0029] According to the business data migration time information, migration of full data and incremental data is performed respectively, specifically including: obtaining a full data snapshot in the source database and performing full data migration on the business data; obtaining a binary operation file of the source database after the migration time node; parsing the binary operation file to extract multiple DML operation statements located after the migration time node; and replaying the multiple DML operation statements in the target migration database to achieve synchronization of incremental data after the migration time node.

[0030] In one embodiment of the present specification, after the migration time node is determined, the system will obtain the latest full data snapshot from the source database node and insert it into the target database. At the selected migration time node, a full data snapshot is obtained from the source database, and the snapshot contains all the data in the source database at the migration time node. Then, this full data snapshot is migrated to the target migration database to ensure that the target database is consistent with the source database in terms of data at the beginning of the migration.

[0031] After the migration time node, the source database will continue to receive and process new data change operations. After the database completes any DML (insert, update, delete) operation that modifies data, that is, insert, update, and delete operations, these operations will be recorded in the binary log of the source database (such as MySQL's binlog log file). Therefore, it is necessary to obtain these binary operation files generated after the migration time node. Parse the obtained binary operation files to extract the data modification language (DML) operation statements therein. DML operation statements include INSERT, UPDATE, DELETE, etc., which describe the specific content of the data change. Finally, these extracted DML operation statements are replayed in the target migration database to achieve the synchronization of incremental data after the migration time node, ensuring that the target database and the source database maintain real-time consistency in data. It should be noted that if there are multiple migration time nodes, the data before the first migration time node is used as the full data, and the time after the first migration time node is synchronized according to the incremental data.

[0032] The combination of full data migration and incremental data synchronization ensures the integrity of data during the migration process. Both the full data at the beginning of the migration and the incremental data during the migration process can be accurately migrated to the target database. The incremental data synchronization mechanism enables the target database to reflect the data changes in the source database in real time. It is not only suitable for one-time data migration, but also for scenarios that require continuous data synchronization. By regularly obtaining and parsing binary operation files, it can ensure that the target database is always synchronized with the source database. Since full data migration can be performed during business off-peak periods and incremental data synchronization can be performed continuously during the migration process, the downtime of the business system can be minimized. By gradually migrating and synchronizing data, the risks caused by migrating a large amount of data at one time can be reduced. At the same time, the incremental data synchronization mechanism also provides means for data recovery and verification, further reducing the risk of data loss or errors. The parallelization of full data migration and incremental data synchronization can significantly improve the efficiency of data migration. Especially in the case of large amounts of data, this parallel processing method can significantly shorten the migration cycle.

[0033] Based on the source database information, SQL statement extraction is performed to determine multiple original SQL statements corresponding to the source database, specifically including: based on the source database information, a source project corresponding to the source database is determined, and a scan is performed in the source project to obtain a configuration file of a framework corresponding to the source database; key nodes are extracted in the configuration file, and multiple original SQL statements corresponding to the source database are extracted through a pre-set regular parsing method.

[0034] In one embodiment of the present specification, the corresponding source project is determined in the relevant project management system according to the information of the source database (such as database name, connection information, etc.), and the configuration file is usually associated with a specific project. In the determined source project, a file scan is performed to scan all XML configuration files related to the MyBatis framework. Scanning all XML configuration files related to the MyBatis framework in the project usually means searching all .xml files in the project, especially those located in the MyBatis Mapper directory. The scanning process can be performed using automated tools or scripts to improve efficiency and accuracy. According to the rules of MyBatis, key configuration nodes are extracted from these XML files. In the XML configuration file of MyBatis, key nodes usually include <mapper>Root node,

[0035] <select>, ... newSql=dealLineBreak(unFormatSql);

[0037] newSql=DealThePar(newSql);

[0038] newSql=DealBlank(newSql);

[0039] newSql=DealTheVar(newSql);

[0040] newSql=DealWhere(newSql);

[0041] n ewSql=DealTheBracket(newSql);

[0042] newSql=DealLineBlank(newSql);

[0043] newSql=DealKey Words(newSql);

[0044] newSql=DealNonVar(newSql);

[0045] newSql=DealMultiLimit(newSql);[0 046]newSql=DealBracket(newSql);

[0047] newSql=DealIbatisVerySpecial(newSql);

[0048] newSql=DealAbc(newSql);

[0049] The function examples corresponding to the above pseudocodes are as follows: dealLineBreak(unFormatSql), used to process line breaks, unify or remove line breaks in SQL statements for subsequent processing; DealThePar(newSql) is used to process parameter placeholders (such as #{} or ${}) in SQL statements, which are replaced with actual values ​​or other processing; DealBlank(newSql) is used to remove unnecessary spaces or tabs in SQL statements to standardize the format; DealTheVar(newSql) is used to process variables in SQL statements, and the specific processing depends on the context. DealWhere(newSql) is used to optimize or process the WHERE clause in SQL statements, including removing redundant AND / OR, processing conditional logic, etc.

[0050] DealTheBracket(newSql) and DealBracket(newSql) are used to process brackets in SQL statements to ensure their correctness and balance; DealLineBlank(newSql) is used to remove redundant spaces at the end of a line or perform other line-level blank processing. DealKeyWords(newSql) is used to process SQL keywords, including case conversion, reserved word processing, etc.DealNonVar (newSql) is used to process the non-variable part, and the specific behavior depends on the context, which may include formatting, optimization, etc. DealMultiLimit (newSql) is used to process the LIMIT statement supported by multiple databases (such as the paging syntax of MySQL and SQLServer is different). DealIbatisVerySpecial (newSql) is used to process the unique or unconventional SQL syntax or structure of MyBatis. DealAbc (newSql) exemplary function, the specific function depends on the actual code including the processing steps that are not clearly specified.

[0051] Regular expression is a powerful text processing tool that can match and extract text content according to preset patterns. Regular expressions are applied to the extracted key node content to capture and extract all the original SQL statements.

[0052] Through the above technical solution, by directly extracting the original SQL statement from the configuration file of the source project, it can be ensured that the obtained SQL statement is consistent with the actual operation in the source database, thereby improving the accuracy of data migration and synchronization; avoiding the tedious process of manually writing or modifying SQL statements, and by automatically scanning and parsing the configuration file, the process of data migration and synchronization can be greatly simplified; directly extracting SQL statements from the configuration file of the source project can reduce the security risks caused by human errors or malicious tampering. At the same time, by extracting SQL statements through regular parsing methods, the SQL statements can also be further verified and filtered to ensure their security and compliance.

[0053] Step S103, based on the target migration database information, determine the corresponding current statement rule information, so as to perform rule matching checks on multiple original SQL statements according to the current statement rule information, and determine multiple risk SQL statements to generate a migration compatibility assessment set.

[0054] Based on the target migration database information, the corresponding current statement rule information is determined, specifically including: obtaining the current database version information corresponding to the target migration database in the target migration database information; matching the initial SQL statement execution rule corresponding to the target migration database through the database file of the target migration database to determine the corresponding initial SQL statement check rule; determining a new SQL statement check rule according to the current database version information, and customizing and entering the new SQL statement check rule to determine the corresponding database restriction rule of the target migration database; obtaining the current business information corresponding to the target migration database in the target migration database information, and customizing the business restriction rule according to the current business information to determine the business definition rule; determining the current statement rule information through the database restriction rule and the business definition rule.In one embodiment of this specification, determine the current version information of target migration database, query the metadata or system table of database, such as information_schema of MySQL, pg_catalog of PostgreSQL etc., to obtain the version number of database.SQL query tool (such as SQL Server Management Studio, pgAdmin, MySQL Workbench etc.) can be used here or directly write SQL script to inquire.According to the database file (such as .sql script, database dump file etc.) of target migration database, identify and match initial SQL statement execution rule, comprise the specific format or requirement of statement such as creation table, insertion data, definition index, can use the SQL syntax tree parsing tool of ANTLR to analyze and match the structure and content of SQL statement.ANTLR (Another Tool for Language Recognition) is another kind of language recognition tool, is a kind of powerful parser generator, for reading, processing, execution or translating structured text or binary file, ANTLR can automatically generate lexical analyzer and grammar analyzer according to the grammar file defined by user, and input text is processed into grammar analysis tree, greatly simplifies the development process of compiler, makes developer can be more devoted to the realization of business logic. Based on the current database version information, determine the new SQL statement checking rules that may be introduced due to the version update, such as the syntax, function, data type, etc. of the new version. Consult the official documents of the database to understand the changes in the new version, and define new SQL statement checking rules based on these changes. These rules need to be customized and entered into the database migration or audit tool. Combine the current database version information and the new SQL statement checking rules to determine the database restriction rules of the target migration database.

[0057] In one embodiment of the present specification, the pseudo code examples corresponding to the database restriction rules are as follows:

[0058] R1001 (true, RISK.CRITICAL, "Select asterisk is prohibited"),

[0059] R1002 (true, RISK.CRITICAL, "Parameters must use #{}, and ${} is not allowed"),

[0060] R1003 (true, RISK.CRITICAL, "Update and delete without conditions are prohibited"),

[0061] R1004 (true, RISK.CRITICAL, "Update contains subqueries and must use exists, otherwise no match will be found and the field will be assigned a null value"),

[0062] R2001 (true, RISK.MAJOR, "Multiple The number of table joins should not exceed 5"),

[0063] R2002(true,RISK.MAJOR,"Direct comparison of null is prohibited"),

[0064] R2003(true,RISK.MAJOR,"Select without conditions is prohibited"),

[0065] R2004(true,RISK.MAJOR,"Join operation must have on condition to avoid Cartesian product"),

[0066] R2005(true,RISK.MAJOR,"Trigger is prohibited in the system"),

[0067] R2006(true,RISK.MAJOR,"INSERT statement must specify the field list"),

[0068] Get the current business information corresponding to the target migration database from the business team or system documents. Based on this information, define or adjust business restriction rules and convert business rules into SQL statements or database configurations to ensure the correctness and consistency of business logic after data migration. In one embodiment of the present specification, the pseudo code example corresponding to the business restriction rule is as follows:

[0069] B2001 (true, Business.CRITICAL, "insert statement must contain gmt_create, gmt_modify"),

[0070] B2002 (true, Business.CRITICAL, "update statement must update gmt_modify"),

[0071] Combine database restriction rules and business definition rules to determine the rule information of the SQL statement that needs to be executed currently, including which SQL statements are allowed, which need to be modified or optimized, etc. Develop or use existing SQL audit tools to automatically check the compliance of SQL statements according to the defined rules.

[0072] Through the above technical solution, by obtaining the current database version information of the target migration database, it is possible to ensure that subsequent SQL statements and database operations are compatible with the version, thereby reducing migration errors caused by version differences; matching the initial SQL statement execution rules and determining the check rules are helpful to identify and correct potential SQL statement problems in the early stage of migration, thereby improving the accuracy of migration; according to the current database version information, determining and customizing the entry of newly added SQL statement check rules, so that the migration process is more in line with the needs of the specific database environment, thereby improving the pertinence and effectiveness of the migration strategy; by customizing the database restriction rules and business definition rules, a more accurate migration strategy can be formulated according to the characteristics and business needs of the target migration database, thereby ensuring that the migrated database can smoothly support Maintain business operations; through strict SQL statement checking rules and database restriction rules, errors can be discovered and corrected in time during the migration process, reducing migration interruptions and retries caused by errors, thereby improving migration efficiency; customized migration strategies help optimize database performance during the migration process to ensure that the migrated database can run efficiently and meet business needs; through strict database restriction rules and business definition rules, data integrity and security can be ensured during the migration process to prevent data leakage and damage; strict checking of SQL statements and confirmation of database version compatibility can help prevent potential security risks and ensure the security and controllability of the data migration process; by customizing business definition rules based on current business information, it can be ensured that the migrated database can fully meet business needs.

[0073] According to the current statement rule information, the multiple original SQL statements are subjected to rule matching checks to determine multiple risk SQL statements to generate a migration compatibility assessment set, specifically including: performing syntax tree parsing on the multiple original SQL statements through a preset syntax analyzer to extract the original statement information of each original SQL statement; performing matching checks on the original statement information of each SQL statement according to the current statement rule information to identify multiple risk SQL statements and determine the risk rules corresponding to each risk SQL statement; determining the risk level corresponding to each risk SQL statement through the risk rules corresponding to each risk SQL statement and the pre-set risk level information; generating the migration compatibility assessment set based on the multiple risk SQL statements, the risk rules corresponding to each risk SQL statement and the risk level corresponding to each risk SQL statement.

[0074] In one embodiment of the present specification, multiple original SQL statements are subjected to in-depth syntax analysis through a preset syntax analyzer to extract their original statement information, and matching checks are performed on the SQL statements according to the current statement rule information to identify potential risk SQL statements and determine the risk rules and risk levels corresponding to them, and generating the migration compatibility assessment set based on the above information to provide an important reference for database migration.Using the preset syntax analyzer ANTLR, an abstract syntax tree is generated according to the syntax rules of the SQL statement. Each original SQL statement is input into the syntax analyzer, and the SQL statement is converted into an abstract syntax tree through lexical analysis and syntax analysis. In this process, the syntax analyzer will identify the components such as keywords, operators, expressions in the SQL statement, and organize them into a tree structure according to the syntax rules. Traverse the abstract syntax tree to extract the original statement information of the SQL statement, including table name, column name, query condition, operation type (such as SELECT, INSERT, UPDATE, DELETE, etc.). The extracted information is represented in a structured manner to facilitate subsequent processing and analysis.

[0075] According to the current statement rule information (including database restriction rules and business definition rules, etc.), a matching check is performed on the original statement information of each SQL statement. Through the matching check, potential risk SQL statements are identified, and the risk SQL statements may violate the restriction rules of the database, the business definition rules or other provisions in the current statement rule information. For each identified risk SQL statement, its corresponding risk rule is determined. The risk rule may be a restriction rule at the database level, a definition rule at the business level or a specific SQL syntax rule, etc. Determine the risk level of each risk SQL statement based on the risk rules corresponding to it and the pre-set risk level information. The risk level can be determined based on a comprehensive consideration of factors such as the severity of the risk rules and the degree of impact on the business. In the pseudocode corresponding to the database restriction rules and business restriction rules, CRITICAL corresponds to the restriction level, which is the highest risk level, and MAJOR is the warning level, which is lower than the restriction level. Based on multiple risk SQL statements, the risk rules and risk levels corresponding to each risk SQL statement, a migration compatibility assessment set is constructed. The set displays all potential risk SQL statements and their related information in a structured manner. A detailed migration compatibility assessment report can be generated as needed. The report content should include detailed information on the risk SQL statement, the corresponding risk rules and risk levels.

[0076] Through the above technical solution, the syntax tree of SQL statements is parsed by a preset syntax analyzer, the original information of SQL statements can be accurately extracted, and a matching check is performed based on the current statement rule information, so as to accurately identify potential risk SQL statements, which is helpful to find and correct potential problems before migration and reduce errors and failures after migration; identify and determine the risk rules and risk levels corresponding to each risk SQL statement, and provide clear risk prompts for the migration process; the entire evaluation process is highly automated, reducing the workload of manual review of SQL statements, and the automated evaluation can quickly process a large number of SQL statements and improve evaluation efficiency; based on the migration compatibility evaluation set, a migration plan can be formulated more accurately, and SQL statements can be prioritized according to risk levels and risk rules, with high-risk or high-priority statements being processed first Prioritized SQL statements, thereby optimizing the migration process and improving migration efficiency; Through migration compatibility assessment, potential problems can be discovered and resolved before migration, reducing business interruptions caused by SQL statement errors or incompatibility during the migration process. It not only focuses on compatibility issues at the technical level, but also considers business requirements and rules, which helps to ensure that the migrated database can better support business development and meet business needs; The migration compatibility assessment set displays multiple risk SQL statements and their related information (including risk rules, risk levels, etc.) in a structured manner, providing decision makers with a clear risk view. Based on the assessment results, resources (such as manpower, time, funds, etc.) can be allocated more reasonably to cope with the risks and challenges in the migration process. For example, more resources can be allocated for the correction of high-risk SQL statements to ensure that problems are solved in a timely manner.

[0077] Step S104, through the migration compatibility assessment set, a migration compatibility optimization reference file is generated to implement database migration of the source database and the target migration database based on the migration compatibility optimization reference file.

[0078] A migration compatibility optimization reference file is generated through the migration compatibility assessment set, specifically including: determining multiple risk SQL statements in the migration compatibility assessment set and risk rules corresponding to each of the risk SQL statements; generating a recommended SQL statement corresponding to the risk SQL statement according to the risk rule corresponding to each of the risk SQL statements and the current statement rule information; and generating a migration compatibility optimization reference file through multiple recommended SQL statements.

[0079] In one embodiment of the present specification, for each risk SQL statement, according to its corresponding risk rule and current statement rule information, a modification strategy is formulated and a suggested SQL statement is generated, including the following methods: adjusting the structure of the SQL statement to comply with the grammatical rules and logical requirements of the database; if the risk rule indicates that an incorrect or unsupported keyword is used, it is necessary to replace it with a correct keyword or correct its usage; ensuring that the data type used in the SQL statement is consistent with the data type in the database table definition to avoid type mismatch problems; if the risk rule indicates that the query condition may cause performance problems or logical errors, it is necessary to optimize the query condition to improve efficiency and accuracy; if the risk rule involves a violation of the database constraint, it is necessary to add or modify the corresponding constraint condition to ensure the integrity and consistency of the data. After the suggested SQL statement is generated, it can be verified to ensure its correctness and effectiveness, which is completed by executing these SQL statements in a test environment and observing the results. The verification process should ensure that the suggested SQL statement can be executed correctly and will not introduce new risks or problems.

[0080] Finally, multiple suggested SQL statements are organized into a migration compatible optimization reference file. The migration compatible optimization reference file includes a risk SQL statement list, a risk rule description, a suggested SQL statement, a modification description, and may also include a verification result. All original SQL statements identified as risky SQL statements are listed in the risky SQL statement list. For each risky SQL statement, a corresponding risk rule description is provided to explain why the SQL statement is considered a risk. A modified recommended SQL statement is provided for each risky SQL statement, and the modified content of each recommended SQL statement relative to the original SQL statement is explained. In addition, the verification results of the recommended SQL statement in the test environment can also be provided to prove its correctness and effectiveness. For each risky SQL statement generated for the recommended SQL statement, specific optimization directions and solutions are provided, which helps to accurately solve problems in the migration process and improve the success rate of migration; through migration compatibility assessment and optimization, the number of reworks caused by incompatible or incorrect SQL statements is reduced, thereby reducing the manpower, time and financial costs in the migration process; the optimized SQL statements are more in line with the rules and business logic of the database, reduce the occurrence of runtime errors, and improve the stability and reliability of the system.

[0081] Through the above technical solution, by automatically extracting the original SQL statements of the source database and performing rule matching checks based on the current statement rule information of the target migration database, risky SQL statements can be automatically identified and determined, significantly reducing the tediousness and error-proneness of relying on manual troubleshooting of application adaptation problems in traditional methods; the generation of a migration compatibility evaluation set can clearly understand which SQL statements have compatibility problems, so as to perform targeted optimization and adjustment, thereby improving the accuracy and efficiency of adaptation; in the migration process, not only the migration of data is concerned, but also the execution performance of SQL statements is deeply considered. By performing rule matching checks on the original SQL statements, problems that may affect performance can be discovered in advance, providing a basis for subsequent SQL optimization; the generated migration compatibility evaluation set can clearly understand which SQL statements have compatibility problems, so as to perform targeted optimization and adjustment, thereby improving the accuracy and efficiency of adaptation; in the migration process, not only the migration of data is concerned, but also the execution performance of SQL statements is deeply considered. By performing rule matching checks on the original SQL statements, problems that may affect performance can be discovered in advance, thereby providing a basis for subsequent SQL optimization; The optimization reference file not only contains a list of risky SQL statements, but also provides optimization suggestions for these statements, which helps to quickly adjust and optimize SQL statements after migration to ensure that database performance is not affected; by identifying and solving potential problems in advance, this technology reduces the number of reworks during the migration process and improves the overall efficiency of migration. Comprehensive compatibility assessment and optimization suggestions enhance the stability of the migration process and reduce the risk of migration failure caused by compatibility issues, thereby improving the success rate of migration; through the automated and refined migration process, this technology reduces the business interruption time caused by migration, ensures the continuity and smoothness of the business, and the optimized database system is not only more stable and reliable, but also can better support business development and meet the growing business needs.

[0082] The embodiment of this specification also provides another database migration method for heterogeneous databases. On the basis of traditional database migration, through the automatic detection of business code adaptability, system monitoring alarm tuning and other operations, the efficient migration of MySQL database to domestic OceanBase database is realized. Connect to the source database through the driver, scan the database objects in the source database system, obtain the source database system metadata information, and build a source database portrait. Create a target database and establish a connection between the source database and the target database; then, extract the corresponding DDL statements from the source database; finally, re-execute the DDL statements in the target database to complete the automatic migration of the data table structure.

[0083] Determine the migration time node, perform data synchronization, determine the appropriate migration time window to minimize the impact on the business, and ensure that it is performed during a period with low system load. After the migration time node is determined, the system will obtain the latest full data snapshot from the source database node and insert it into the target database. Since the database completes any DML (insert, update, delete) operation that modifies data, the database will record it in real time to the binlog log file; the system parses the binlog operation log for the data generated after the time node, extracts the DML statement, and then replays it in the target database to achieve incremental data synchronization.The process of business SQL compatibility assessment is as follows: scan all XML configuration files relevant to the MyBatis framework in the project. According to the rule of MyBatis, extract key configuration nodes from these XML files. Use specific regular parsing method to extract the SQL statement that needs to be checked from the Mapper XML file. These statements need to further check whether there are grammatical errors or whether they are suitable for the target database. The pseudo code example corresponding to the regular parsing method is the same as the example of the corresponding embodiment of step S102, and will not be repeated here. The extracted SQL statement is parsed by using the ANTLR grammar analyzer to perform syntax tree analysis, verify the correctness of the SQL statement, and extract information such as field name, table name and parameters therefrom. According to a group of predefined optimization rules (shown in the pseudo code example corresponding to the database restriction rules and business restriction rules provided in step S103), the rule supports subsequent manual input, which is used to check the information extracted, to identify any possible risk SQL statement. Finally, the system will create a detailed report, list all potential compatibility issues, and provide risk level assessment according to the rule definition table. This report will be used as a reference for adjusting and optimizing SQL statements. The pseudo code example of the entire compatibility evaluation process is as follows:

[0085] 3-1: Parsing SQL statements in Mapper XML

[0086]

[0087]

[0088] 3-2: Detecting compatibility issues of SQL statements

[0089]

[0090] Main process: Business SQL compatibility evaluation

[0091]

[0092] The above embodiment, based on database architecture migration and data migration, automatically scans, parses and evaluates SQL statements in XML configuration files related to the MyBatis framework through project SQL statement extraction, ANTLR syntax tree parsing, and rule matching checks, thereby saving time and labor costs. An automated detection method is introduced during the migration process to automatically identify the risk level of project SQL statements and their compatibility with the target database, generate a detailed report containing potential compatibility issues and risk levels, discover and resolve potential compatibility issues in advance, improve the success rate of database migration and the stability of the target database, help developers and database administrators improve SQL statements, discover and resolve potential compatibility issues in advance, improve the success rate of database migration and the stability of the target database, and provide a valuable reference for subsequent adjustments and optimizations.

[0093] An embodiment of the present specification also provides a database migration device for a heterogeneous database, as shown in Figure 2, the device includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the above method.

[0094] The embodiments of the present specification also provide a non-volatile computer storage medium, which stores computer executable instructions, and the computer executable instructions are configured to: execute the above method.

[0095] The various embodiments in the present specification are described in a progressive manner, and the same and similar parts between the various embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the embodiments of the device, equipment, and non-volatile computer storage medium, since they are basically similar to the method embodiments, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiments.

[0096] The above describes a specific embodiment of the present specification. Other embodiments are within the scope of the attached claims. In some cases, the actions or steps recorded in the claims can be performed in an order different from that in the embodiments and still achieve the desired results. In addition, the process depicted in the drawings does not necessarily require the specific order or continuous order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0097] The devices and media provided in the embodiments of this specification correspond to the methods one by one, so the devices and media also have beneficial technical effects similar to the corresponding methods. Since the beneficial technical effects of the methods have been described in detail above, the beneficial technical effects of the devices and media will not be repeated here.

[0098] It should be understood by those skilled in the art that the embodiments of this specification can be provided as methods, systems, or computer program products. Therefore, this specification can take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Moreover, this specification can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program codes.

[0099] This specification is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of this specification. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device produce a device for implementing the functions specified in one or more processes in the flowchart and / or one or more blocks in the block diagram.

[0100] These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer-readable memory produce a product including an instruction device, which implements the functions specified in one or more processes of the flowchart and / or one or more blocks of the block diagram.

[0101] These computer program instructions may also be loaded onto a computer or other programmable data processing device, so that a series of operating steps are performed on the computer or other programmable device to produce a computer-implemented process, so that the instructions executed on the computer or other programmable device provide steps for implementing the functions specified in one or more processes of the flowchart and / or one or more blocks of the block diagram.

[0102] In a typical configuration, a computing device includes one or more processors (CPU), an input / output interface, a network interface, and a memory.

[0103] The memory may include non-permanent memory in a computer-readable medium, a random access memory (RAM), and / or a non-volatile memory, such as a read-only memory (ROM) or a flash memory (flash RAM). The memory is an example of a computer-readable medium.

[0104] Computer readable media include permanent and non-permanent, removable and non-removable media that can be used to store information by any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, read-only compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassettes, magnetic tape magnetic disk storage or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device. According to the definition herein, computer readable media does not include temporary computer readable media (transitory media), such as modulated data signals and carrier waves.

[0105] It should also be noted that the terms "comprises", "includes" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, commodity or device comprising a series of elements includes not only those elements, but also includes other elements not explicitly listed, or also includes elements inherent to such process, method, commodity or device. In the absence of further restrictions, an element defined by the sentence "comprises a ..." does not exclude the presence of other identical elements in the process, method, commodity or device comprising the element.

[0106] The above description is only one or more embodiments of the present specification and is not intended to limit the present specification.For those skilled in the art, one or more embodiments of this specification may have various changes and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of one or more embodiments of this specification should be included in the scope of the claims of this specification.< / select> < / mapper>

Claims

1. A database migration method for heterogeneous databases, characterized in that: The method comprises: Obtaining database migration requirement information, wherein the database migration requirement information includes current business information, target migration database information, and source database information; According to the database migration requirement information, the data table structure and business data in the source database are automatically migrated, and based on the source database information, SQL statements are extracted to determine multiple original SQL statements corresponding to the source database; Based on the target migration database information, corresponding current statement rule information is determined, so as to perform rule matching checks on the multiple original SQL statements according to the current statement rule information, determine multiple risky SQL statements, and generate a migration compatibility assessment set; A migration compatibility optimization reference file is generated through the migration compatibility assessment set, so as to implement database migration between the source database and the target migration database based on the migration compatibility optimization reference file.

2. A database migration method for heterogeneous databases according to claim 1, characterized in that: According to the database migration requirement information, the data table structure and business data in the source database are automatically migrated, specifically including: According to the source database information in the database migration requirement information, a corresponding database driver is matched to establish a connection with the source database based on the database driver; Through the connection of the source database, objects in the source database are traversed to determine metadata of each object to construct a source database portrait corresponding to the source database; Based on the source database portrait, extract the data table structure to determine the DDL statement corresponding to the data table structure, and execute the DDL statement in the pre-built target migration database to create the same data table structure as the source database, so as to realize the automatic migration of the data table structure; Acquire current business information in the database migration requirement information, and determine business data migration time information based on the current business scenario information, wherein the business data migration time information includes at least one migration time node and a migration time window corresponding to the migration time node; According to the business data migration time information, full data and incremental data are migrated respectively.

3. A database migration method for heterogeneous databases according to claim 2, characterized in that: Based on the current business scenario information, determining business data migration time information specifically includes: Determine at least one reference continuous time interval based on historical business peak information and historical user active time information in the current business scenario information; Collect historical load data of business systems to determine multiple historical load indicator information; Analyze the system load of the business system through the multiple historical load indicator information to determine the periodic load change information of the business system, wherein the periodic load change information includes multiple continuous valley time intervals corresponding to the load valley and multiple peak time intervals corresponding to the load peak; Acquire information about the amount of data to be migrated in the source database, so as to determine a reference migration duration corresponding to the source database based on the amount of data to be migrated and reference load information of the business system; According to the periodic load change information, taking the intersection of the at least one reference continuous time interval and the valley continuous time interval to determine at least one load valley interval; The service data migration time information is determined by using the reference migration duration and the at least one load valley interval.

4. A database migration method for heterogeneous databases according to claim 2, characterized in that: According to the business data migration time information, the migration of full data and incremental data is performed respectively, specifically including: Obtain a full data snapshot in the source database and perform full data migration on the business data; Obtaining a binary operation file of the source database after the migration time node; Parsing the binary operation file to extract multiple DML operation statements located after the migration time node; The multiple DML operation statements are replayed in the target migration database to achieve synchronization of incremental data after the migration time node.

5. The database migration method for heterogeneous databases according to claim 1, characterized in that: Based on the source database information, SQL statement extraction is performed to determine multiple original SQL statements corresponding to the source database, specifically including: Based on the source database information, determine a source project corresponding to the source database, scan the source project, and obtain a configuration file of a framework corresponding to the source database; Key nodes are extracted from the configuration file, and multiple original SQL statements corresponding to the source database are extracted through a preset regular parsing method.

6. A database migration method for heterogeneous databases according to claim 1, characterized in that: Based on the target migration database information, the corresponding current statement rule information is determined, specifically including: Obtaining current database version information corresponding to the target migration database in the target migration database information; Matching the initial SQL statement execution rule corresponding to the target migration database through the database file of the target migration database to determine the corresponding initial SQL statement checking rule; Determine a new SQL statement check rule according to the current database version information, and enter the new SQL statement check rule by customizing to determine a corresponding database restriction rule of the target migration database; Acquire current business information corresponding to the target migration database in the target migration database information, so as to customize business restriction rules according to the current business information and determine business definition rules; The current statement rule information is determined through the database restriction rule and the business definition rule.

7. The database migration method for heterogeneous databases according to claim 1, characterized in that: According to the current statement rule information, a rule matching check is performed on the multiple original SQL statements to determine multiple risky SQL statements to generate a migration compatibility assessment set, specifically including: By using a preset syntax analyzer, the multiple original SQL statements are parsed into a syntax tree to extract original statement information of each original SQL statement; According to the current statement rule information, a matching check is performed on the original statement information of each of the SQL statements to identify multiple risky SQL statements, and determine the risk rule corresponding to each of the risky SQL statements; Determine the risk level corresponding to each risk SQL statement through the risk rules corresponding to each risk SQL statement and the pre-set risk level information; The migration compatibility assessment set is generated based on the multiple risky SQL statements, the risk rule corresponding to each of the risky SQL statements, and the risk level corresponding to each of the risky SQL statements.

8. The database migration method for heterogeneous databases according to claim 1, characterized in that: Generate a migration compatibility optimization reference file through the migration compatibility assessment set, specifically including: Determine a plurality of risky SQL statements in the migration compatibility assessment set and a risk rule corresponding to each of the risky SQL statements; Generate a suggested SQL statement corresponding to the risky SQL statement according to the risk rule corresponding to each risky SQL statement and the current statement rule information; A migration-compatible optimization reference file is generated through a plurality of the suggested SQL statements.

9. A database migration device for heterogeneous databases, characterized in that: The device comprises: at least one processor; and, a memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can perform the method according to any one of claims 1 to 8.

10. A non-volatile computer storage medium storing computer executable instructions, characterized in that: The computer executable instructions are configured to execute the method according to any one of claims 1 to 8.

Images