A storage process-oriented online database migration method

Abstract

The application relates to the technical field of databases and discloses a stored procedure-oriented online database migration method and system, which comprises the following steps: S1, performing deduplication filtering on stored procedures in a source file; S2, assigning an identifier to each stored procedure and initializing a triple dictionary; S3, serializing stored procedure request, forwarding the stored procedure to a source database based on the triple dictionary, and recording the frequency of each stored procedure; S4, translating the stored procedure with the highest frequency; S5, forwarding the translated stored procedure from the source database to a target database based on the triple dictionary, and executing the translated stored procedure by the target database; S6, checking the execution result of the stored procedure and performing data synchronization between the target database and the source database; and S7, repeating S3-S6 until the migration task is completed. The application can identify and classify stored procedures, and realizes the target of database migration through progressive translation reform.

Description

An Online Database Migration Method Oriented to Stored Procedures Technical Field

[0001] This invention belongs to the technical field of databases, specifically relating to an online database migration method and system oriented towards stored procedures. Background Technology

[0002] The storage cost and performance per unit of data vary across different database categories, leading to data migration needs from business users. Some business logic is encapsulated within numerous stored procedures (stored procedures are sets of SQL statements in large database systems designed to perform specific functions). This results in a strong binding between the source code and the database category, making database migration dependent on modifications to the stored procedures. While tools exist for translating and converting stored procedures between different databases, direct translation is not always feasible, requiring significant manpower and time for verification. Furthermore, the migration itself must consider its impact on business operations.

[0003] For example, Chinese Patent No. CN107958010B discloses a method and system for online data migration. The method includes: monitoring the binary logs of incremental data to be migrated in the application's data to be migrated; generating incremental data migration messages based on the binary logs; and sending the incremental data migration messages to the application's incremental data message queue according to a message distribution strategy. This invention achieves online data migration based on a message mechanism.

[0004] For example, patent application publication number CN110059072A discloses a data migration monitoring method, system, device, and medium based on stored procedures. The method includes: monitoring and detecting that a migration switch in the main stored procedure is triggered; determining the target basic data table that the current migration depends on from at least one basic data table; storing the table name of the target basic data table in a monitoring record table; triggering the corresponding secondary stored procedure; obtaining at least one data table to be migrated associated with the target basic data table; and migrating the at least one data table to be migrated, wherein the target basic data table is used to store basic data associated with the data to be migrated in each of the at least one data table to be migrated; determining the migration result of the at least one data table to be migrated; and recording the migration result in the monitoring record table at a position associated with the table name.

[0005] All of the above patents suffer from the problems raised in this background technology: online database migration does not involve source code modification, and direct translation and conversion are not always feasible, requiring manpower and time costs for verification.

[0006] The information disclosed in this background section is intended only to enhance the understanding of the overall background of the invention and should not be construed as an admission or in any way implying that the information constitutes prior art known to those skilled in the art. Summary of the Invention

[0007] The technical problem to be solved by this invention is to overcome the shortcomings of the prior art and provide an online database migration method oriented towards stored procedures. By analyzing the source code, the stored procedures are identified and classified. An intermediate layer is added between the server and the database to automatically count and transparently forward requests. While ensuring normal business operations, high-frequency stored procedures are identified and gradually translated and synchronized with the data to ultimately achieve the goal of database migration while minimizing the impact on business operations.

[0008] To solve the above-mentioned technical problems, the present invention provides the following technical solution:

[0009] On one hand, the present invention provides an online database migration method for stored procedures, comprising the following steps:

[0010] S1: Perform deduplication filtering on the stored procedures in the source file;

[0011] S2: Assign an identifier to each stored procedure and initialize the triplet dictionary;

[0012] S3: Serialize the stored procedure request, forward the stored procedure to the source database based on the triple dictionary, and record the frequency of each stored procedure;

[0013] S4: Translate the most frequently used stored procedure;

[0014] S5: Based on the triple dictionary, the translated stored procedure is forwarded from the source database to the target database, and the target database executes the translated stored procedure;

[0015] S6: Verify the execution result of the stored procedure and synchronize data between the target database and the source database;

[0016] S7: Repeat S3-S6 until the migration task is completed.

[0017] As a preferred embodiment of the online database migration method for stored procedures described in this invention, the method for deduplication filtering is as follows: using an SQL syntax parser to parse the source code file, identify the stored procedures, and perform deduplication filtering on the stored procedures.

[0018] In a preferred embodiment of the online database migration method for stored procedures described in this invention, the triplet dictionary takes the following form:

[0019] (key, value1, value2);

[0020] Wherein, key is the ID of the identifier; value1 is the stored procedure; value2 is the translated stored procedure, with an initial value of empty.

[0021] As a preferred embodiment of the online database migration method for stored procedures described in this invention, the method for forwarding the stored procedure to the source database is as follows: read the triple dictionary, forward the stored procedure corresponding to value1 to the source database, and count the frequency of the stored procedure corresponding to value1.

[0022] As a preferred embodiment of the online database migration method for stored procedures described in this invention, the method for translating the most frequently used stored procedure is as follows: periodically collect the frequency of stored procedures and perform statistical analysis to find the most frequently used stored procedure and display it back to the business party; the business party modifies and translates the stored procedure based on its own business and updates the value2 in the triplet.

[0023] As a preferred embodiment of the online database migration method for stored procedures described in this invention, the method for forwarding the translated stored procedure from the source database to the target database is to read a triplet dictionary and send the stored procedure corresponding to value2 to the target database.

[0024] As a preferred embodiment of the online database migration method for stored procedures described in this invention, the method for determining the completion of the migration task is that the triple dictionary value2 has been completely replaced or the business party decides to stop the migration.

[0025] Secondly, this invention provides an online database migration system for stored procedures, comprising a syntax parsing module, an intermediate layer forwarding module, a database synchronization module, and a frequency analysis and translation module; wherein:

[0026] The syntax parsing module is used to parse source code files, identify stored procedures, and perform deduplication filtering and assign identifiers to the stored procedures;

[0027] The intermediate layer forwarding module is used to connect the database and the server; identify the identifier of each stored procedure; route and forward the stored procedures based on the identifier; and send the frequency to the frequency analysis and translation module.

[0028] The frequency analysis and translation module is used to statistically analyze the frequency of the stored procedures, identify the most frequent stored procedure, and translate it; then, the translated stored procedure is sent to the intermediate layer forwarding module.

[0029] The database synchronization module is used for data synchronization between the source database and the target database.

[0030] Thirdly, the present invention provides an electronic device, including a memory for storing instructions and a processor for executing the instructions, causing the device to perform operations implementing the online database migration method for stored procedures described in the present invention.

[0031] Fourthly, the present invention provides a computer-readable storage medium having a computer program stored thereon, characterized in that, when the computer program is executed by a processor, it implements the online database migration method oriented towards stored procedures described in the present invention.

[0032] Compared with the prior art, the beneficial effects achieved by the present invention are as follows:

[0033] Most online database migrations do not involve source code modification. The solution proposed in this patent allows modification and migration to be carried out simultaneously and iteratively. The modification process can be determined by the migration effect, and the effect can be visualized in real time, which is more business-friendly.

[0034] This invention adopts a gradual migration approach and simultaneously runs dual database synchronization tests to maximize business continuity and avoid business interruption or impact caused by database migration.

[0035] Each step of the migration in this invention can be verified for correctness after synchronous testing, controlling migration risks and achieving the effect of incremental migration; it eliminates the need for manual full translation of the stored process before testing, thus avoiding the time and risk costs brought about by a huge translation workload. Attached Figure Description

[0036] To more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings used in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. Wherein:

[0037] Figure 1 is a flowchart of the online database migration method for stored procedures provided by the present invention;

[0038] Figure 2 is a schematic diagram of the structure of the online database migration system for stored procedures provided by the present invention. Detailed Implementation

[0039] The technical solution of the present invention will be described in detail below with reference to the accompanying drawings and specific embodiments. It should be understood that the embodiments of the present invention and the specific features in the embodiments are detailed descriptions of the technical solution of the present invention, rather than limitations thereof. In the absence of conflict, the embodiments of the present invention and the technical features in the embodiments can be combined with each other.

[0040] Example 1

[0041] This embodiment describes an online database migration method oriented towards stored procedures. Referring to Figure 1, the method includes the following steps:

[0042] S1: Perform deduplication filtering on the stored procedures in the source file; the method is as follows:

[0043] An SQL syntax parser is used to parse the source code file, identify stored procedures, and perform deduplication filtering on the stored procedures.

[0044] S2: Assign an identifier to each stored procedure and initialize the triple dictionary; export the triple dictionary for later use.

[0045] The dictionary of triples takes the following form:

[0046] (key, value1, value2);

[0047] Wherein, key is the ID of the identifier; value1 is the stored procedure; value2 is the translated stored procedure, with an initial value of empty.

[0048] S3: Serialize stored procedure requests, forward stored procedures to the source database based on the triple dictionary, and record the frequency of each stored procedure; the method is as follows:

[0049] The serialized request is checked for a unique identifier to determine if it is a stored procedure. If it is not a stored procedure, the request is forwarded to the target database. If it is a stored procedure, the triple dictionary is read, the stored procedure corresponding to value1 is forwarded to the source database, and the frequency of the stored procedure corresponding to value1 is counted.

[0050] When the business service receives a user request, it schedules and sends the specific SQL command. If the request involves a stored procedure, the SQL command will include a unique identifier.

[0051] S4: Translate the most frequently used stored procedure; the method is as follows:

[0052] The frequency of stored procedures is periodically collected and statistically analyzed to identify the most frequent stored procedures and then displayed to the business side. The business side then modifies and translates the stored procedures based on its own business needs and updates the value2 in the triplet.

[0053] S5: Based on the triple dictionary, forward the translated stored procedure from the source database to the target database, and have the target database execute the translated stored procedure; the method is as follows: read the triple dictionary and send the stored procedure corresponding to value2 to the target database.

[0054] S6: Verify the execution result of the stored procedure and synchronize data between the target database and the source database;

[0055] S7: Repeat S3-S6 until the migration task is completed.

[0056] The migration task is considered complete when the value2 of the triple dictionary is completely replaced or the business side decides to stop the migration.

[0057] Example 2

[0058] This embodiment is the second embodiment of the present invention; based on the same inventive concept as Embodiment 1, and referring to Figure 2, this embodiment introduces an online database migration system for stored procedures. Referring to Figure 2, the system includes a syntax parsing module, an intermediate layer forwarding module, a database synchronization module, and a frequency analysis and translation module; wherein:

[0059] The syntax parsing module uses a standard, efficient SQL syntax parser to parse the source code file, identify its stored procedures, perform deduplication filtering on the stored procedures, and assign a unique identifier to each stored procedure.

[0060] The intermediate layer forwarding module is used to connect the database and the server; identify the unique identifier of each stored procedure; route the stored procedure to the database for execution based on the identifier; and send the frequency to the frequency analysis and translation module.

[0061] The frequency analysis and translation module is used to statistically analyze the frequency of the stored procedures, identify the most frequent stored procedure, and translate it; then, the translated stored procedure is sent to the intermediate layer forwarding module.

[0062] The database synchronization module is used for data synchronization between the source database and the target database.

[0063] The specific functional implementation of each of the above modules is described in the relevant content of Embodiment 1, "An Online Database Migration Method Oriented to Stored Procedures," and will not be repeated here.

[0064] Example 3

[0065] Based on the same inventive concept as other embodiments, this embodiment introduces an electronic device including a memory and a processor. The memory is used to store instructions, and the processor is used to execute the instructions, causing the computer device to perform the online database migration method for stored procedures provided by the methods described above.

[0066] Since the electronic device described in this embodiment is the electronic device used to implement the online database migration method for stored procedures in the embodiments of this application, those skilled in the art can understand the specific implementation methods and various variations of the electronic device in this embodiment based on the online database migration method for stored procedures described in the embodiments of this application. Therefore, how the electronic device implements the method in the embodiments of this application will not be described in detail here. Any electronic device used by those skilled in the art to implement the online database migration method for stored procedures in the embodiments of this application falls within the scope of protection of this application.

[0067] Example 4

[0068] Based on the same inventive concept as other embodiments, this embodiment introduces a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the online database migration method for stored procedures provided by the methods described above.

[0069] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0070] The embodiments of the present invention have been described above with reference to the accompanying drawings. However, the present invention is not limited to the specific embodiments described above. The specific embodiments described above are merely illustrative and not restrictive. Those skilled in the art can make many other forms under the guidance of the present invention without departing from the spirit and scope of the claims. All of these forms are within the protection scope of the present invention.

Claims

1. A stored procedure-oriented online database migration method, characterized in that: Includes the following steps: S1: Deduplicate the stored procedures in the source file; S2: Assign an identifier to each stored procedure and initialize a triple dictionary; the triple dictionary is in the form of (key, value1, value2); where key is the ID of the identifier; value1 is the stored procedure; value2 is the translated stored procedure, initially empty; S3: Serialize the stored procedure requests, forward the stored procedures to the source database based on the triple dictionary, and record the frequency of each stored procedure; S4: Translate the most frequent stored procedure; the translation of the stored procedure... The method for identifying the most frequently used stored procedure is as follows: Periodically collect the frequency of stored procedures and perform statistical analysis to find the most frequently used stored procedure and display it back to the business side; the business side modifies and translates the stored procedure based on its own business needs and updates the value2 in the triplet; S5: Based on the triplet dictionary, forward the translated stored procedure from the source database to the target database, and execute the translated stored procedure in the target database; S6: Verify the execution result of the stored procedure and synchronize the data between the target database and the source database; S7: Repeat S3-S6 until the migration task is completed.

2. The online database migration method for stored procedures as described in claim 1, characterized in that: The method for deduplication filtering is as follows: use an SQL syntax parser to parse the source code file, identify the stored procedures, and perform deduplication filtering on the stored procedures.

3. The online database migration method for stored procedures as described in claim 2, characterized in that: The method for forwarding the stored procedure to the source database is as follows: read the triple dictionary, forward the stored procedure corresponding to value1 to the source database, and count the frequency of the stored procedure corresponding to value1.

4. The online database migration method for stored procedures as described in claim 3, characterized in that: The method for forwarding the translated stored procedure from the source database to the target database is to read the triple dictionary and send the stored procedure corresponding to value2 to the target database.

5. The online database migration method for stored procedures as described in claim 4, characterized in that: The method for determining whether the migration task is complete is that the triple dictionary value2 has been completely replaced or the business side decides to stop the migration.

6. A stored procedure-oriented online database migration system, implemented based on any one of claims 1-5, characterized in that: It includes a syntax parsing module, an intermediate layer forwarding module, a database synchronization module, and a frequency analysis and translation module. Specifically: the syntax parsing module parses source code files, identifies stored procedures, and performs deduplication filtering and assigns identifiers to the stored procedures; the intermediate layer forwarding module connects the database and the server; identifies the identifier of each stored procedure; routes and forwards the stored procedures based on the identifier; and sends the frequency to the frequency analysis and translation module; the frequency analysis and translation module statistically analyzes the frequency of the stored procedures, identifies the most frequent stored procedure, translates it, and sends the translated stored procedure to the intermediate layer forwarding module; the database synchronization module synchronizes data between the source database and the target database.

7. An electronic device, characterized in that, include: Memory, used to store instructions; A processor for executing the instructions, causing the device to perform operations implementing an online database migration method oriented towards stored procedures as described in any one of claims 1-5.

8. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements an online database migration method oriented towards stored procedures as described in any one of claims 1-5.

Images