Database migration method and device, electronic equipment and storage medium

Abstract

The invention provides a database migration method and device, electronic equipment and a storage medium, and relates to the technical field of computers.The method comprises the steps that to-be-migrated data in a source database is determined; generating a directed acyclic graph based on a character string primary key corresponding to the to-be-migrated data; the directed acyclic graph is used for representing the sequence of each character in the main key of the character string; traversing the directed acyclic graph, determining a plurality of character string primary key prefixes, and dividing the to-be-migrated data into a plurality of to-be-migrated data blocks based on the plurality of character string primary key prefixes; and migrating the to-be-migrated data block corresponding to each character string primary key prefix from the source database to a target database. According to the method and the device provided by the invention, efficient migration of the character string primary key database is realized.

Description

Technical Field

[0001] The present application relates to the field of computer technology, and in particular to a database migration method, device, electronic device and storage medium. Background Art

[0002] Database migration refers to exporting data from a source database and then importing it into a target database. When faced with massive amounts of data, related technologies use a method of fixed-length slicing of data to perform database migration. This method is only applicable to databases with integer primary keys and cannot be applied to databases with string primary keys.

[0003] Therefore, how to achieve efficient migration of string primary key database has become a technical problem that needs to be solved urgently in the industry. Summary of the invention

[0004] The present application provides a database migration method, device, electronic device and storage medium, which are used to solve the technical problem of how to achieve efficient migration of a string primary key database.

[0005] This application provides a database migration method, including: Determine the data to be migrated in the source database; Based on the string primary key corresponding to the data to be migrated, a directed acyclic graph is generated; the directed acyclic graph is used to represent the order of each character in the string primary key; Traversing the directed acyclic graph, determining a plurality of string primary key prefixes, and dividing the to-be-migrated data into a plurality of to-be-migrated data blocks based on the plurality of string primary key prefixes; The data blocks to be migrated corresponding to the primary key prefixes of the character strings are migrated from the source database to the target database.

[0006] In some embodiments, generating a directed acyclic graph based on the string primary key corresponding to the data to be migrated includes: The directed acyclic graph is generated by taking the characters in the primary key of each string as nodes and the sequence between the characters as edges.

[0007] In some embodiments, traversing the directed acyclic graph to determine a plurality of string primary key prefixes includes: Traversing the directed acyclic graph based on a depth-first search algorithm to determine a current node; Determine a candidate string primary key prefix based on a node sequence from a root node to the current node in the directed acyclic graph; Based on the candidate character string primary key prefix, generate an execution plan analysis statement corresponding to the primary key query; Based on the analysis result of the execution plan analysis statement, determining the candidate string primary key prefix as the string primary key prefix; The directed acyclic graph is pruned, and the child nodes corresponding to the current node are determined as nodes that do not need to be traversed.

[0008] In some embodiments, determining the candidate string primary key prefix as the string primary key prefix based on the analysis result of the execution plan analysis statement includes: Based on the analysis result of the execution plan analysis statement, determine the number of data records affected by the candidate character string primary key prefix; In a case where the number of data records affected is less than or equal to a preset number, determining the candidate string primary key prefix as the string primary key prefix; The preset number is determined based on a maximum value of data records in a single query of the source database or a maximum value of data records processed per unit time of the source database.

[0009] In some embodiments, migrating the to-be-migrated data blocks corresponding to the prefixes of the primary keys of the respective strings from the source database to the target database includes: Based on the prefixes of the primary keys of the respective character strings, a query is performed in the source database to determine the data blocks to be migrated corresponding to the prefixes of the primary keys of the respective character strings; Determine the amount of data concurrently processed per unit time of the source database; Based on the concurrently processed data volume per unit time, grouping the to-be-migrated data blocks corresponding to the primary key prefixes of the respective character strings; Concurrency control is performed on the migration of data corresponding to each group from the source database to the target database.

[0010] In some embodiments, the concurrent control of migrating the data corresponding to each group from the source database to the target database includes: Determine the preset duration of data processing corresponding to each group; When the actual data processing duration corresponding to any group is less than the preset data processing duration, the sleep duration of the source database when processing the data corresponding to any group is determined based on the difference between the preset data processing duration and the preset data processing duration.

[0011] In some embodiments, after migrating the to-be-migrated data blocks corresponding to the prefixes of the primary keys of the respective strings from the source database to the target database, the method further includes: Determining the amount of new data and updated data in the target database; The amount of migrated data is determined based on the amount of newly added data and the amount of updated data.

[0012] The present application provides a database migration device, comprising: A determination module is used to determine the data to be migrated in the source database; A generating module, used for generating a directed acyclic graph based on the string primary key corresponding to the data to be migrated; the directed acyclic graph is used for representing the order of each character in the string primary key; A partitioning module, configured to traverse the directed acyclic graph, determine a plurality of string primary key prefixes, and divide the to-be-migrated data into a plurality of to-be-migrated data blocks based on the plurality of string primary key prefixes; The migration module is used to migrate the to-be-migrated data blocks corresponding to the primary key prefixes of each string from the source database to the target database.

[0013] The present application provides an electronic device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the database migration method when executing the computer program.

[0014] The present application provides a non-transitory computer-readable storage medium having a computer program stored thereon, and the computer program implements the database migration method when executed by a processor.

[0015] The database migration method, device, electronic device and storage medium provided by the present application determine the data to be migrated in the source database; generate a directed acyclic graph based on the string primary key corresponding to the data to be migrated; the directed acyclic graph is used to represent the order of each character in the string primary key; traverse the directed acyclic graph to determine multiple string primary key prefixes, and divide the data to be migrated into multiple data blocks to be migrated based on the multiple string primary key prefixes; migrate the data blocks to be migrated corresponding to each string primary key prefix from the source database to the target database; because a directed acyclic graph is generated according to the string primary key, the string primary key prefix is ​​determined by graph search, and the data is divided into blocks according to the string primary key prefix, and efficient migration of the string primary key database is achieved through block migration, avoiding overall migration of the database, and improving the efficiency of data migration; in addition, the basis for data segmentation is the string primary key prefix, rather than a fixed data length, and the block result has a smaller impact on data integrity, thereby improving the security of data migration. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0017] In order to more clearly illustrate the technical solutions in the present application or the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0018] Figure 1 It is a flowchart of the database migration method provided by this application.

[0019] Figure 2 It is a schematic diagram of a directed acyclic graph provided in this application.

[0020] Figure 3 It is a schematic diagram of the database migration method based on string primary key partitioning provided by this application.

[0021] Figure 4 It is a flowchart of the sequence division module execution method provided in this application.

[0022] Figure 5 It is a flowchart of the sequence processing module execution method provided in this application.

[0023] Figure 6 It is a structural diagram of the database migration device provided by this application.

[0024] Figure 7 It is a structural schematic diagram of the electronic device provided by this application. DETAILED DESCRIPTION

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

[0026] It should be noted that the terms "first", "second" etc. in the present application are used to distinguish similar objects, and need not be used to describe a specific order or sequential order. It should be understood that the data used in this way can be interchangeable in appropriate circumstances, so that the embodiments of the present application described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions, for example, the process, method, system, product or equipment comprising a series of steps or units or modules need not be limited to those steps or units or modules clearly listed, but may include other steps or units or modules that are not clearly listed or inherent to these processes, methods, products or equipment.

[0027] In the related technology, database migration can be divided into two types: operation and maintenance level migration and business level migration. At the operation and maintenance level, the principle of master-slave synchronization can be used to make the new database a slave database, and the global transaction identifier (GTID) can be used to re-execute the logical log (binlog) of the source database in the new database. After the data migration is completed, the domain name is switched, and the business directly accesses the new database. At the business level, the application system uses a program to scroll the data from the source database through fixed-length slices, and then writes it to the target database. Finally, the application system switches to the new database to achieve database migration.

[0028] When performing migration at the business level, the database migration method in the related art is only applicable to integer primary key databases. String primary key databases cannot be sliced ​​into fixed lengths, and therefore cannot be migrated efficiently.

[0029] In order to solve the shortcomings of related technologies, Figure 1 It is a flowchart of the database migration method provided by this application, such as Figure 1 As shown, the method includes step 110 , step 120 , step 130 and step 140 .

[0030] Step 110: Determine the data to be migrated in the source database.

[0031] Specifically, the execution subject of the database migration method provided in the embodiment of the present application is a database migration device. The device can be implemented by software, such as a database migration program running in a server; or it can be a device for executing the database migration method, such as a mobile terminal, a tablet computer, a desktop computer or a server.

[0032] The data to be migrated refers to the data that needs to be migrated from the source database to the target database. The source database refers to the database from which the data is migrated. The target database refers to the database to which the data is migrated. In the embodiment of the present application, the source database and the target database may be heterogeneous databases with different data storage structures, different data types, different query languages, and different operation modes.

[0033] Step 120: Generate a directed acyclic graph based on the string primary key corresponding to the data to be migrated; the directed acyclic graph is used to represent the order of each character in the string primary key.

[0034] Specifically, the primary key is a field used to uniquely identify each row of data in a table, and it must ensure that each value is unique and non-empty. The primary key can be an integer type, because integers are relatively efficient when compared and stored. The primary key can also be a string type to meet the needs of business scenarios. In the embodiment of the present application, the data to be migrated uses a string as the primary key.

[0035] A directed acyclic graph (DAG) may be generated based on the string primary key corresponding to the data to be migrated, and the graph is used to represent the order of each character in the string primary key. For example, the string primary key corresponding to the data to be migrated may include {"112", "113", "123"}. Figure 2 It is a schematic diagram of a directed acyclic graph provided by this application, such as Figure 2 As shown, the graph may include 1 root node (including character "1"), 2 first-level child nodes (including character "1" and character "2"), and 3 second-level child nodes (including 2 characters "2" and 1 character "3").

[0036] Step 130 , traverse the directed acyclic graph, determine a plurality of string primary key prefixes, and divide the to-be-migrated data into a plurality of to-be-migrated data blocks based on the plurality of string primary key prefixes.

[0037] Specifically, a prefix usually refers to the beginning of a string, that is, a continuous sequence of characters starting from the start position of the string. A string primary key prefix refers to the set of character prefixes of each string primary key in a set of string primary keys. If there are multiple string primary keys, their primary key prefixes can form a set, where each prefix represents a beginning subsequence of different lengths.

[0038] The number of string primary key prefixes can also be determined according to the number of characters contained in the prefix. For example, in the above example, if the number of characters required in the string primary key prefix is ​​2, the string primary key prefix can include {"11", "12"}.

[0039] The directed acyclic graph can be traversed to determine multiple string primary key prefixes. For example, a depth first search (DFS) algorithm can be used to traverse the directed acyclic graph.

[0040] Each string primary key prefix can be used for quick lookup, data indexing, or other operations that require prefix matching of string primary keys.

[0041] The primary key of each data in the data to be migrated can be matched with multiple string primary key prefixes respectively. If the primary key of any data matches any string primary key prefix, it can be determined that the data corresponds to the string primary key prefix. Through the above method, the data to be migrated can be divided into multiple data blocks to be migrated. Each data block to be migrated corresponds to a string primary key prefix.

[0042] Step 140: Migrate the to-be-migrated data blocks corresponding to the primary key prefixes of each string from the source database to the target database.

[0043] Specifically, after the data to be migrated is divided into a plurality of data blocks to be migrated, the string primary key prefixes may be sorted, and the data blocks to be migrated corresponding to the string primary key prefixes may be migrated from the source database to the target database in sequence.

[0044] The database migration method provided by the embodiment of the present application determines the data to be migrated in the source database; generates a directed acyclic graph based on the string primary key corresponding to the data to be migrated; the directed acyclic graph is used to represent the order of each character in the string primary key; traverses the directed acyclic graph to determine multiple string primary key prefixes, and based on the multiple string primary key prefixes, divides the data to be migrated into multiple data blocks to be migrated; migrates the data blocks to be migrated corresponding to each string primary key prefix from the source database to the target database; because a directed acyclic graph is generated according to the string primary key, the string primary key prefix is ​​determined by graph search, and the data is divided into blocks according to the string primary key prefix, and efficient migration of the string primary key database is achieved through block migration, avoiding overall migration of the database, and improving the efficiency of data migration; in addition, the basis for data segmentation is the string primary key prefix, rather than a fixed data length, and the block result has a smaller impact on data integrity, thereby improving the security of data migration.

[0045] It should be noted that each implementation method of the present application can be freely combined, the order can be changed, or it can be executed separately, and does not need to rely on or depend on a fixed execution order.

[0046] In some embodiments, generating a directed acyclic graph based on a string primary key corresponding to the data to be migrated includes: A directed acyclic graph is generated with the characters in each string primary key as nodes and the sequence between the characters as edges.

[0047] Specifically, converting the string primary key into a directed acyclic graph means expressing the order of characters in the string primary key set through directed edges in graph theory and ensuring that there is no loop in the graph.

[0048] Each string primary key can be split, with characters as nodes and the order between characters as edges, with the direction of the edge pointing from the first character to the last character, to generate a directed acyclic graph. The order here is the order of the characters in the string primary key.

[0049] First, the root node can be determined based on the character that is the first in the arrangement order of each string primary key. For example, in the string primary key set {"112", "113", "123"}, the first character is "1", so this character can be used as the root node. Secondly, the level corresponding to the node is determined based on the arrangement order of the characters other than the first character in each string primary key. For example, the arrangement order of the second character "1" in the string primary key "112" is 2, so the node corresponding to this character can be determined as a first-level child node, and the arrangement order of the third character "2" is 3, so the node corresponding to this character can be determined as a second-level child node, and so on. Thirdly, the child nodes other than the root node can be compared at the same level. If the characters corresponding to two child nodes at the same level are the same, the two child nodes at the same level are merged into the same child node. For example, in the above example, the first-level child nodes in the string primary keys "112" and "113" are the same, so they can be merged into one child node. Finally, directed edges are generated according to the order of characters (corresponding to the level of nodes), from nodes with higher levels to nodes with lower levels (characters in the front sequence point to characters in the back sequence), thus generating a directed acyclic graph.

[0050] The database migration method provided in the embodiment of the present application uses the characters in each string primary key as nodes and the order between the characters as edges to generate a directed acyclic graph, which can effectively represent the order of each character in the string primary key, is conducive to traversing the string primary key, accurately determining the string primary key prefix, and improving the accuracy of data segmentation.

[0051] In some embodiments, traversing the directed acyclic graph to determine a plurality of string primary key prefixes includes: Traverse the directed acyclic graph based on the depth-first search algorithm to determine the current node; Based on the node sequence from the root node to the current node in the directed acyclic graph, determine the candidate string primary key prefix; Generate the execution plan analysis statement corresponding to the primary key query based on the candidate string primary key prefix; Based on the analysis result of the execution plan analysis statement, the candidate string primary key prefix is ​​determined as the string primary key prefix; Prune the directed acyclic graph and determine the child nodes corresponding to the current node as nodes that do not need to be traversed.

[0052] Specifically, a depth-first search algorithm can be used to traverse the directed acyclic graph. Taking the current node as an example, the candidate string primary key prefix is ​​determined based on the node sequence from the root node to the current node in the directed acyclic graph. For example, in the above example, when the current node is a first-level child node "1", the candidate string primary key prefix can be the node sequence from the root node "1" to the current node "1", that is, the character sequence corresponding to the node, which is "11".

[0053] Based on the candidate string primary key prefix, you can generate an execution plan analysis statement corresponding to the primary key query. The execution plan analysis statement specifically refers to the Explain statement in the database. The Explain statement is used to analyze the execution plan of the query in the database to help optimize and debug the query performance. For example, the generated execution plan analysis statement can be explainselect * from ${tableName} where id like 'idPrefix%', where explain is the explanation keyword, select is the query keyword, from is the keyword that specifies the query location, tableName indicates the table name in the source database where the data to be migrated is located, where and like are the filter condition keywords, id indicates the string primary key, and idPrefix is ​​the string primary key prefix.

[0054] The analysis result of the execution plan analysis statement refers to the processing result of the execution plan analysis statement, which can be used to analyze and optimize the performance of the query statement, indicating the query execution order, access method, data filtering conditions, execution cost estimation, and result set size estimation. Based on the analysis result of the execution plan analysis statement, it can be determined whether the candidate string primary key prefix can be determined as the string primary key prefix. For example, the analysis result of the execution plan analysis statement can include the number of data records affected. If the number of data records affected is large and may exceed the processing capacity of the source database, the candidate string primary key prefix cannot be used as the string primary key prefix; if the number of data records affected is small and does not exceed the processing capacity of the source database, the candidate string primary key prefix can be used as the string primary key prefix.

[0055] If the candidate string primary key prefix is ​​determined as the string primary key prefix, the directed acyclic graph can be pruned according to the string primary key prefix. The pruning operation refers to treating some nodes as a whole and not continuing to search downward in the directed acyclic graph, that is, determining the child nodes corresponding to the current node as nodes that do not need to be traversed.

[0056] The database migration method provided in the embodiment of the present application determines multiple string primary key prefixes through a depth-first search algorithm (DFS) and an execution plan analysis statement (Explain statement). The newly added and deleted data do not affect the partitioning result, so the partitioning result is relatively stable. In addition, the pruning operation uses the database Explain statement and does not actually query the database. The time complexity is O(1), so it has a high partitioning efficiency.

[0057] In some embodiments, based on the analysis result of the execution plan analysis statement, determining the candidate string primary key prefix as the string primary key prefix includes: Based on the analysis results of the execution plan analysis statement, determine the number of data records affected by the candidate string primary key prefix; When the number of data records affected is less than or equal to the preset number, the candidate string primary key prefix is ​​determined as the string primary key prefix; The preset number is determined based on the maximum value of single query data records of the source database or the maximum value of data records processed per unit time of the source database.

[0058] Specifically, the preset number can be determined based on the maximum value of a single query data record of the source database or the maximum value of data record processing per unit time of the source database. The preset number is used to indicate the maximum processing volume (number of records or rows) of data records of the source database in the data migration process. A data record can be a row of data or a piece of data.

[0059] According to the analysis results of the execution plan analysis statement, determine the number of data records affected by the candidate string primary key prefix. If the number of data records affected is greater than the preset number, the amount of data corresponding to the candidate string primary key prefix is ​​too large and exceeds the maximum processing capacity, and the candidate string primary key prefix cannot be used as the string primary key prefix; if the number of data records affected is less than or equal to the preset number, the amount of data corresponding to the candidate string primary key prefix is ​​less than the maximum processing capacity, and the candidate string primary key prefix can be used as the string primary key prefix.

[0060] The database migration method provided in the embodiment of the present application determines a preset number based on the maximum value of a single query data record of the source database or the maximum value of data record processing per unit time of the source database, and determines a string primary key prefix based on a comparison result between the preset number and the number of data records affected by the candidate string primary key prefix. This does not affect the data processing capability of the source database, and improves the stability and security of data migration.

[0061] In some embodiments, migrating the to-be-migrated data blocks corresponding to the primary key prefixes of each string from the source database to the target database includes: Based on the prefixes of each string primary key, a query is performed in the source database to determine the data blocks to be migrated corresponding to each string primary key prefix; Determine the amount of data that the source database processes concurrently per unit time; Based on the amount of concurrently processed data per unit time, the data blocks to be migrated corresponding to the prefixes of the primary keys of each string are grouped; Perform concurrency control on the migration of data corresponding to each group from the source database to the target database.

[0062] Specifically, the source database is queried based on each string primary key prefix, that is, the primary key of each data in the data to be migrated is matched with multiple string primary key prefixes respectively. If the primary key of any data matches any string primary key prefix, the data is used as the data to be migrated corresponding to the string primary key prefix. After the query is completed in the above manner, the data blocks to be migrated corresponding to each string primary key prefix can be obtained.

[0063] The source database may also be running related data query services or data processing services at the same time. The amount of concurrent data processed per unit time for data migration can be determined based on the hardware resource configuration corresponding to the source database without affecting the processing of other services.

[0064] According to the amount of concurrently processed data per unit time, the data blocks to be migrated corresponding to each string primary key prefix are grouped, and then the data migration tasks corresponding to each group are executed concurrently to migrate the data corresponding to each group from the source database to the target database. For example, if the amount of concurrently processed data per unit time is 100 data records, and the data blocks to be migrated corresponding to any string primary key prefix are 1,000 data records, then the data blocks to be migrated can be divided into 10 groups, each with 100 data records.

[0065] The database migration method provided in the embodiment of the present application migrates the data blocks to be migrated corresponding to the prefix of each string primary key from the source database to the target database through concurrent control, so that the source database can realize online data migration without adversely affecting other running businesses, while also improving the efficiency of data migration.

[0066] In some embodiments, the concurrency control of migrating data corresponding to each group from a source database to a target database includes: Determine the preset duration of data processing corresponding to each group; When the actual data processing duration corresponding to any group is less than the preset data processing duration, the sleep duration of the source database when processing the data corresponding to any group is determined based on the difference between the preset data processing duration and the preset data processing duration.

[0067] Specifically, a preset data processing time length may be determined for the data corresponding to each group, for example, it may be set to 1000 milliseconds (ms).

[0068] Monitor the data processing time corresponding to each group. If the actual data processing time (cost) corresponding to any group is less than the preset data processing time, the sleep time of the source database when processing the data corresponding to the group can be determined based on the difference between the preset data processing time and the preset data processing time (1000-cost). That is, after migrating the group, sleep for (1000-cost) milliseconds.

[0069] The database migration method provided in the embodiment of the present application controls the data migration rate by determining the preset data processing time corresponding to each group and controlling the processing time of each group, without causing adverse effects on other running businesses.

[0070] In some embodiments, after migrating the to-be-migrated data blocks corresponding to the prefixes of the primary keys of the respective strings from the source database to the target database, the method further includes: Determine the amount of new data and updated data in the target database; Determine the amount of data to be migrated based on the amount of new data and updated data.

[0071] Specifically, if the data record in the data block to be migrated does not exist in the target database, then the data record is newly added data, and the number of newly added data records is the amount of newly added data; if the data record in the data block to be migrated exists in the target database but the creation time of the data record is earlier than the creation time of the data record to be migrated, then the data record is updated data, and the number of updated data records is the amount of newly added data.

[0072] By summing the amount of new data and the amount of updated data, we can get the number of migrated data records, that is, the amount of migrated data.

[0073] The database migration method provided in the embodiment of the present application determines the amount of migrated data based on the amount of new data and the amount of updated data, which can effectively count the total amount of migrated data and improve the user experience.

[0074] Figure 3 is a schematic diagram of a database migration method based on string primary key partitioning provided by this application, such as Figure 3 As shown in the figure, this method uses the depth-first search algorithm (DFS) to traverse the string primary key space, and uses the estimated number of affected rows returned by the database Explain statement as the pruning condition to achieve efficient traversal of the string primary key database, and combines sequence scheduling and sequence processing to complete the migration of the string primary key database. The method includes: Step 1: Use the depth-first search algorithm and Explain statement through the sequence partitioning module to partition the character primary keys in the business slave table to obtain data (id sequences) corresponding to multiple string primary key prefixes. Step 2: Schedule the data corresponding to the string primary key prefix through the sequence call module, call the callback method of the sequence processing module through the business callback address, and aggregate and output the sequence migration results. Among them, the sequence processing module queries data from the database according to the string primary key prefix provided by the sequence scheduling module, and finally writes it to the target data source through concurrency control, model conversion, and target data source routing.

[0075] Figure 4 is a flow chart of the sequence division module execution method provided in this application, such as Figure 4 As shown, the method includes: Step 1: Determine the initial conditions, including determining the character set charset of the string primary key, setting the current string primary key prefix idPrefix to empty, setting the pruning condition cutLength (preset number), and setting the string prefix sequence set prefixSet. The size of cutLength cannot exceed the maximum number of rows that the database can query at one time. For example, the database primary key can include {"112", "113", "123"}, and cutLength=2.

[0076] Step 2: Use the search algorithm to perform DFS traversal on the directed acyclic graph, where idPrefix is ​​the sequence from the root node to the current node (candidate string primary key prefix).

[0077] Step 3: Perform pruning judgment on each node traversed by DFS. Use explain select * from ${tableName} where id like 'idPrefix%' to return the rows (number of data records affected) in the result, which is the number of rows that may be affected by the current idPrefix prefix.

[0078] Step 4: If rows>cutLength, it means that the number of records under the current idPrefix prefix is ​​too large and cannot be processed at one time. Jump to step 2.

[0079] Step 5: Record the current idPrefix sequence to prefixSet and perform pruning operations. All child nodes of the current node will no longer be traversed by DFS.

[0080] Step 6: If DFS traversal is not completed, jump to step 2.

[0081] Step 7: Output the divided string primary key prefix set prefixSet. For example, prefixSet may include {"11", "12"}.

[0082] The sequence call module mainly executes the following methods: Step 1: Sequence scheduling is used to control the order in which the idPrefix sequence is executed.

[0083] Control the execution order of the prefixSet set, call the sequence processing modules one by one in ascending lexicographical order, and in case of failure, the current task waits for manual restart of the task.

[0084] Step 2: Business callback is used to call the sequence processing module capabilities through the http interface.

[0085] The specific calling parameters are: {"url":"","userName":"","password":"", "tableName", "startPrefix":"","endPrefix":"","callbackUrl"}, where url is the database jdbc link, username is the database account name, password is the database password, tableName is the database table name to be migrated, startPrefix is ​​the string primary key prefix at the start of migration, endPrefix is ​​the string primary key prefix at the end of migration, and callbackUrl is the callback address of the sequence processing module.

[0086] Step 3: Aggregate the results of step 2 to calculate the migration progress and total migration volume.

[0087] The result format returned by the sequence processing module is: {"saveCount":1,"updateCount":0}, where saveCount is the amount of data added during the migration process, and updateCount is the amount of data updated during the migration process. The results of all sequences are aggregated to obtain the total amount of new data totalSaveCount, the total amount of updated data totalUpdateCount, and the total migration amount totalCount = totalSaveCount+totalUpdateCount.

[0088] Figure 5 is a flow chart of the sequence processing module execution method provided by the present application, such as Figure 5 As shown, the specific steps in the sequence processing module are as follows: Step 1: Sequence query: Based on the passed idPrefix sequence, use the like condition to query the data to be migrated from the source database. The query statement is select * from ${tableName} where id like '${idPrefix}%'.

[0089] Step 2: Concurrency control: Calculate the minimum processing time of the data to be migrated based on the migration rate, group the query data according to the number of concurrent queries per second, and sleep for (1000-cost) milliseconds for each group of data if the processing time cost is less than 1000ms. Step 3: Model conversion: Convert the source data into the target data format according to the specifications.

[0090] The model conversion step needs to be set when migrating data to heterogeneous data sources. In actual business, there are data migration scenarios such as reconstructing existing tables, re-sharding large tables, and synchronizing data to heterogeneous data sources. The structures of the source database and the target data source may be different. In this case, model conversion is required to convert the source data structure into the target data structure.

[0091] The specific conversion rules are as follows: field name conversion, parameter value conversion, and date type conversion. Field name conversion converts the source field name into the target field name, which is applicable to scenarios where field names change during the migration process. Specific technical means include variable name replacement or underscore / camel case type conversion. Parameter value change converts parameter values ​​according to fixed rules, including arithmetic operations, date format conversion, encryption and decryption, etc., which is applicable to scenarios where field values ​​need to be changed simultaneously during the migration process. Date type conversion converts the display format of the date into other easy-to-read types.

[0092] Step 4: Target data source routing: Select the data source to be written according to the configuration.

[0093] The target data source routing supports data sources such as MySql, ElasticSearch, Hive, and Hbase. For scenarios with sharded tables (Mysql) or sharded indexes (ElasticSearch), it is necessary to calculate the sharded tables or indexes for each piece of data.

[0094] Step 5: Data writing: Call the corresponding data source writing method to write the data to the corresponding data source.

[0095] To support repeated execution of migration tasks, query whether the data exists before writing, add new data if it does not exist, and update existing data.

[0096] The sequence processing module uses data conversion and target data source routing. The target write data source can be specified through configuration, and the model can be transformed through data conversion rules, making data migration more flexible and convenient. The concurrent control process is adopted to control the pressure of writing to the target data source, and can support the migration of production databases without stopping services.

[0097] The device provided in the embodiment of the present application is described below. The device described below and the method described above can be referenced to each other.

[0098] Figure 6 It is a schematic diagram of the structure of the database migration device provided by this application, such as Figure 6 As shown, the device comprises: A determination module 610 is used to determine the data to be migrated in the source database; A generating module 620 is used to generate a directed acyclic graph based on the string primary key corresponding to the data to be migrated; the directed acyclic graph is used to represent the order of each character in the string primary key; A partitioning module 630 is used to traverse the directed acyclic graph, determine multiple string primary key prefixes, and divide the to-be-migrated data into multiple to-be-migrated data blocks based on the multiple string primary key prefixes; The migration module 640 is used to migrate the to-be-migrated data blocks corresponding to the primary key prefixes of each string from the source database to the target database.

[0099] The database migration device provided by the embodiment of the present application determines the data to be migrated in the source database; generates a directed acyclic graph based on the string primary key corresponding to the data to be migrated; the directed acyclic graph is used to represent the order of each character in the string primary key; traverses the directed acyclic graph to determine multiple string primary key prefixes, and based on the multiple string primary key prefixes, divides the data to be migrated into multiple data blocks to be migrated; migrates the data blocks to be migrated corresponding to each string primary key prefix from the source database to the target database; because a directed acyclic graph is generated according to the string primary key, the string primary key prefix is ​​determined by graph search, and the data is divided into blocks according to the string primary key prefix, and efficient migration of the string primary key database is achieved through block migration, avoiding overall migration of the database, and improving the efficiency of data migration; in addition, the basis for data segmentation is the string primary key prefix, rather than a fixed data length, and the block result has a smaller impact on data integrity, thereby improving the security of data migration.

[0100] In some embodiments, the generation module is used to: A directed acyclic graph is generated with the characters in each string primary key as nodes and the sequence between the characters as edges.

[0101] In some embodiments, the partitioning module is used to: Traverse the directed acyclic graph based on the depth-first search algorithm to determine the current node; Based on the node sequence from the root node to the current node in the directed acyclic graph, determine the candidate string primary key prefix; Generate the execution plan analysis statement corresponding to the primary key query based on the candidate string primary key prefix; Based on the analysis result of the execution plan analysis statement, the candidate string primary key prefix is ​​determined as the string primary key prefix; Prune the directed acyclic graph and determine the child nodes corresponding to the current node as nodes that do not need to be traversed.

[0102] In some embodiments, the partitioning module is used to: Based on the analysis results of the execution plan analysis statement, determine the number of data records affected by the candidate string primary key prefix; When the number of data records affected is less than or equal to the preset number, the candidate string primary key prefix is ​​determined as the string primary key prefix; The preset number is determined based on the maximum value of single query data records of the source database or the maximum value of data records processed per unit time of the source database.

[0103] In some embodiments, the migration module is used to: Based on the prefixes of each string primary key, a query is performed in the source database to determine the data blocks to be migrated corresponding to each string primary key prefix; Determine the amount of data that the source database processes concurrently per unit time; Based on the amount of concurrently processed data per unit time, the data blocks to be migrated corresponding to the prefixes of the primary keys of each string are grouped; Perform concurrency control on the migration of data corresponding to each group from the source database to the target database.

[0104] In some embodiments, the migration module is used to: Determine the preset duration of data processing corresponding to each group; When the actual data processing duration corresponding to any group is less than the preset data processing duration, the sleep duration of the source database when processing the data corresponding to any group is determined based on the difference between the preset data processing duration and the preset data processing duration.

[0105] In some embodiments, the migration module is further configured to: Determine the amount of new data and updated data in the target database; Determine the amount of data to be migrated based on the amount of new data and updated data.

[0106] Figure 7 is a schematic diagram of the structure of the electronic device provided by this application, such as Figure 7As shown, the electronic device may include: a processor (Processor) 710, a communication interface (Communications Interface) 720, a memory (Memory) 730 and a communication bus (Communications Bus) 740, wherein the processor 710, the communication interface 720, and the memory 730 communicate with each other through the communication bus 740. The processor 710 may call the logic command in the memory 730 to execute the method described in the above embodiment, for example: Determine the data to be migrated in the source database; generate a directed acyclic graph based on the string primary key corresponding to the data to be migrated; the directed acyclic graph is used to represent the order of each character in the string primary key; traverse the directed acyclic graph to determine multiple string primary key prefixes, and divide the data to be migrated into multiple data blocks to be migrated based on the multiple string primary key prefixes; migrate the data blocks to be migrated corresponding to each string primary key prefix from the source database to the target database.

[0107] In addition, the logical commands in the above-mentioned memory can be implemented in the form of software functional units and can be stored in a computer-readable storage medium when sold or used as an independent product. Based on this understanding, the technical solution of the present application can be essentially or partly embodied in the form of a software product that contributes to the prior art. The computer software product is stored in a storage medium and includes several commands to enable a computer device (which can be a personal computer, server, or network device, etc.) to execute all or part of the steps of the method described in each embodiment of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, etc., which can store program code.

[0108] The processor in the electronic device provided in the embodiment of the present application can call the logic instructions in the memory to implement the above method. Its specific implementation method is consistent with the implementation method of the aforementioned method and can achieve the same beneficial effects, which will not be repeated here.

[0109] An embodiment of the present application also provides a computer-readable storage medium on which a computer program is stored. When the computer program is executed by a processor, the methods provided in the above embodiments are implemented.

[0110] Its specific implementation is consistent with the aforementioned method implementation and can achieve the same beneficial effects, so it will not be repeated here.

[0111] An embodiment of the present application provides a computer program product, including a computer program, which implements the above method when executed by a processor.

[0112] The device embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the scheme of this embodiment. Ordinary technicians in this field can understand and implement it without paying creative labor.

[0113] Through the description of the above implementation methods, those skilled in the art can clearly understand that each implementation method can be implemented by means of software plus a necessary general hardware platform, and of course, can also be implemented by hardware. Based on this understanding, the above technical solution is essentially or the part that contributes to the prior art can be embodied in the form of a software product, and the computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, a disk, an optical disk, etc., including a number of instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in each embodiment or some parts of the embodiments.

[0114] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit it. Although the present application has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present application.

Claims

1. A database migration method, characterized in that: include: Determine the data to be migrated in the source database; Generate a directed acyclic graph based on the string primary key corresponding to the data to be migrated; The directed acyclic graph is used to represent the order of each character in the string primary key; Traversing the directed acyclic graph, determining a plurality of string primary key prefixes, and dividing the to-be-migrated data into a plurality of to-be-migrated data blocks based on the plurality of string primary key prefixes; The data blocks to be migrated corresponding to the primary key prefixes of the character strings are migrated from the source database to the target database.

2. The database migration method according to claim 1, characterized in that: The generating a directed acyclic graph based on the string primary key corresponding to the data to be migrated includes: The directed acyclic graph is generated by taking the characters in the primary key of each string as nodes and the sequence between the characters as edges.

3. The database migration method according to claim 1, characterized in that: The traversing the directed acyclic graph to determine multiple string primary key prefixes includes: Traversing the directed acyclic graph based on a depth-first search algorithm to determine a current node; Determine a candidate string primary key prefix based on a node sequence from a root node to the current node in the directed acyclic graph; Based on the candidate character string primary key prefix, generate an execution plan analysis statement corresponding to the primary key query; Based on the analysis result of the execution plan analysis statement, determining the candidate string primary key prefix as the string primary key prefix; The directed acyclic graph is pruned, and the child nodes corresponding to the current node are determined as nodes that do not need to be traversed.

4. The database migration method according to claim 3, characterized in that: The step of determining the candidate string primary key prefix as the string primary key prefix based on the analysis result of the execution plan analysis statement includes: Based on the analysis result of the execution plan analysis statement, determine the number of data records affected by the candidate character string primary key prefix; In a case where the number of data records affected is less than or equal to a preset number, determining the candidate string primary key prefix as the string primary key prefix; The preset number is determined based on a maximum value of data records in a single query of the source database or a maximum value of data records processed per unit time of the source database.

5. The database migration method according to claim 1, characterized in that: The step of migrating the to-be-migrated data blocks corresponding to the primary key prefixes of each string from the source database to the target database includes: Based on the prefixes of the primary keys of the respective character strings, a query is performed in the source database to determine the data blocks to be migrated corresponding to the prefixes of the primary keys of the respective character strings; Determine the amount of data concurrently processed per unit time of the source database; Based on the concurrently processed data volume per unit time, grouping the to-be-migrated data blocks corresponding to the primary key prefixes of the respective character strings; Concurrency control is performed on the migration of data corresponding to each group from the source database to the target database.

6. The database migration method according to claim 5, characterized in that: The concurrent control of migrating the data corresponding to each group from the source database to the target database includes: Determine the preset duration of data processing corresponding to each group; When the actual data processing duration corresponding to any group is less than the preset data processing duration, the sleep duration of the source database when processing the data corresponding to any group is determined based on the difference between the preset data processing duration and the preset data processing duration.

7. The database migration method according to any one of claims 1 to 6, characterized in that: After migrating the to-be-migrated data blocks corresponding to the prefixes of the primary keys of the character strings from the source database to the target database, the method further includes: Determining the amount of new data and updated data in the target database; The amount of migrated data is determined based on the amount of newly added data and the amount of updated data.

8. A database migration device, characterized in that: include: A determination module is used to determine the data to be migrated in the source database; A generating module, used for generating a directed acyclic graph based on the string primary key corresponding to the data to be migrated; The directed acyclic graph is used to represent the order of each character in the string primary key; A partitioning module, configured to traverse the directed acyclic graph, determine a plurality of string primary key prefixes, and divide the to-be-migrated data into a plurality of to-be-migrated data blocks based on the plurality of string primary key prefixes; The migration module is used to migrate the to-be-migrated data blocks corresponding to the primary key prefixes of each string from the source database to the target database.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein: When the processor executes the computer program, the database migration method according to any one of claims 1 to 7 is implemented.

10. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the database migration method according to any one of claims 1 to 7 is implemented.

Images