Data migration method and device, equipment and medium

Abstract

The invention relates to the technical field of data processing, in particular to a data migration method and device, equipment and a medium, which are used for efficiently and accurately migrating data of a database. In the method, for each to-be-migrated data table in a source end database, format conversion is performed on the to-be-migrated data table to obtain a target data table; for each type of target data table, determining a target number of machines for migrating the type of target data table according to a data size of the type of target data table, a pre-configured migration rate and migration time corresponding to the type of target data table and a number of concurrent migration queues of each machine; migrating the type of target data table to a target end database based on the target number of machines; wherein different types of target data tables correspond to different migration rates, and machines for migrating the different types of target data tables are not the same machine, so that the purpose of efficiently and accurately migrating the data of the database is achieved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data processing, and in particular to a data migration method, device, equipment and medium. BACKGROUND

[0002] With the deepening of enterprise digital transformation and the rapid iteration of information technology, database upgrading, migration or replacement is a common technical requirement. For example, as the data scale of public enterprises continues to grow, and the demand for data processing speed and distributed computing capacity continues to increase, traditional centralized databases such as Oracle databases have been difficult to meet the demand for large-scale data processing, and gradually migrating their data to new distributed databases such as TiDB databases has become a trend.

[0003] However, there is currently no good solution to how to efficiently and accurately migrate database data. SUMMARY

[0004] The present application provides a data migration method, device, equipment and medium to efficiently and accurately migrate database data.

[0005] In a first aspect, the present application provides a data migration method, which comprises: For each to-be-migrated data table in a source database, if the to-be-migrated data table contains a preset target format sub-data, the target format sub-data is format-converted to obtain a target data table corresponding to the to-be-migrated data table; For each type of target data table, according to the data size of each target data table of the type, the preconfigured migration rate of the target data table of the type corresponding to the target data table of the type, the migration time, and the number of concurrent migration queues of each machine, the target number of machines for migrating each target data table of the type is determined; each target data table of the type is assigned to the target number of machines, and each target data table of the type is migrated to a target database based on the target number of machines; wherein the migration rates of different types of target data tables are different, and the machines for migrating different types of target data tables are not the same machine.

[0006] In one possible implementation, the assignment of each target data table of the type to the target number of machines comprises: The target data tables of the type are sorted according to the data size of the target data tables; and the following operations are respectively performed when each queue in the target number of machines is assigned a target data table in each round of loop until each target data table of the type is assigned: Based on the current to-be-migrated data size of each queue in the target number of machines, a target queue set for pre-assigning target data tables in the current round is determined; According to the sorting order, a set number of target data tables are assigned to each queue in the target queue set.

[0007] In one possible implementation, determining the target queue set for the current pre-allocated target data based on the current amount of data to be migrated in each queue of the target number of machines includes: For each queue in the target number of machines, determine whether the amount of data to be migrated in the queue currently exceeds a set proportion of the amount of data to be migrated in any other queue; if so, exclude the queue from the target queue set; otherwise, add the queue to the target queue set.

[0008] In one possible implementation, migrating the target data tables of this type to the target database includes: For each target data table, if the data in the target data table is static, the target data table is migrated to the target database using a full synchronization method; if the data in the target data table is dynamic, the target data table is migrated to the target database using an incremental synchronization method.

[0009] In one possible implementation, the method further includes: For each target data table, if the consistency check result of the data in the target database and the data in the source database is inconsistent, then the inconsistent sub-data is identified, the corresponding sub-data in the source database is migrated to the target database, and the sub-data is spliced ​​and merged into the target data table in the target database.

[0010] In one possible implementation, if the consistency check result of the data in the target data table in the target database is inconsistent with the data in the source database, before identifying the inconsistent sub-data, the method further includes: Determine whether the data volume of the target data table exceeds a set threshold. If so, proceed to the next step of identifying inconsistent sub-data. If not, the target data table will be removed from the target database and re-identified as the data table to be migrated.

[0011] In one possible implementation, the target data table may be of the following types: a first data type with a CLOB field and a second data type without a CLOB field.

[0012] In one possible implementation, the source database is an Oracle database; the target database is a TiDB database.

[0013] Secondly, this application provides a data migration apparatus, the apparatus comprising: The conversion module is used to convert the target format sub-data for each data table to be migrated in the source database if the data table to be migrated contains a preset target format sub-data to obtain the target data table corresponding to the data table to be migrated. The migration module is used to determine the target number of machines to migrate each type of target data table based on the data volume of each target data table of that type, the pre-configured migration rate corresponding to the target data table of that type, the migration time, and the number of concurrent migration queues per machine; allocate each type of target data table to the target number of machines; and migrate each type of target data table to the target database based on the target number of machines; wherein, the migration rate corresponding to different types of target data tables is different, and the machines used to migrate different types of target data tables are not the same machines.

[0014] In one possible implementation, the migration module is specifically used for: Sort the target data tables of this type according to their data volume; and perform the following operations in each round of the loop when allocating target data tables to the queues of the target number of machines, until all target data tables of this type have been allocated: Based on the amount of data to be migrated in each queue of the target number of machines, determine the target queue set of the pre-allocated target data table for this round; According to the sorting order, a set number of target data tables are assigned to each queue in the target queue set.

[0015] In one possible implementation, the migration module is specifically used for: For each queue in the target number of machines, determine whether the amount of data to be migrated in the queue currently exceeds a set proportion of the amount of data to be migrated in any other queue; if so, exclude the queue from the target queue set; otherwise, add the queue to the target queue set.

[0016] In one possible implementation, the migration module is specifically used for: For each target data table, if the data in the target data table is static, the target data table is migrated to the target database using a full synchronization method; if the data in the target data table is dynamic, the target data table is migrated to the target database using an incremental synchronization method.

[0017] In one possible implementation, the device further includes: The verification and compensation module is used to identify inconsistent sub-data for each target data table if the consistency verification result between the data in the target database and the data in the source database is inconsistent. The module then migrates the corresponding sub-data from the source database to the target database and merges the sub-data into the target data table in the target database.

[0018] In one possible implementation, the verification compensation module is further configured to: Determine whether the data volume of the target data table exceeds a set threshold. If so, proceed to the next step of identifying inconsistent sub-data. If not, the target data table will be removed from the target database and re-identified as the data table to be migrated.

[0019] In one possible implementation, the target data table may be of the following types: a first data type with a CLOB field and a second data type without a CLOB field.

[0020] In one possible implementation, the source database is an Oracle database; the target database is a TiDB database.

[0021] Thirdly, this application provides an electronic device, comprising: Memory, used to store program instructions; A processor is configured to invoke program instructions stored in the memory and execute the steps of the method described in any one of the first aspects according to the obtained program instructions.

[0022] Fourthly, this application provides a computer-readable storage medium storing a computer program, the computer program including program instructions that, when executed by a computer, cause the computer to perform the method as described in any one of the first aspects.

[0023] Fifthly, this application provides a computer program product comprising: computer program code, which, when executed on a computer, causes the computer to perform the method described in any one of the first aspects.

[0024] In this embodiment, for each data table to be migrated in the source database, the target format sub-data contained in the data table to be migrated can be converted to obtain the target data table corresponding to the data table to be migrated. For each type of target data table, the target number of machines used to migrate each type of target data table is determined based on the data size of each target data table of that type, the pre-configured migration rate corresponding to the target data table of that type, the migration time, and the number of concurrent migration queues on each machine. Each target data table of that type is assigned to the target number of machines, and based on the target number of machines, each target data table of that type is migrated to the target database. Here, the migration rate corresponding to different types of target data tables is different, and the machines used to migrate different types of target data tables are not the same machine. Based on this, the purpose of efficiently and accurately migrating the database data can be achieved. Attached Figure Description

[0025] To more clearly illustrate the implementation methods in the embodiments of this application or related technologies, the accompanying drawings used in the description of the embodiments or related technologies will be briefly introduced below. Obviously, the accompanying drawings described below are some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings.

[0026] Figure 1 A schematic diagram of a first data migration process provided by some embodiments is shown; Figure 2 A schematic diagram of a second data migration process provided by some embodiments is shown; Figure 3 A schematic diagram of a data migration apparatus provided in some embodiments is shown; Figure 4 A schematic diagram of an electronic device structure provided by some embodiments is shown. Detailed Implementation

[0027] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application. Unless otherwise specified, the embodiments and features in the embodiments of this application can be arbitrarily combined with each other. Furthermore, although a logical order is shown in the flowchart, in some cases, the steps shown or described may be performed in a different order than that shown here.

[0028] The terms "first" and "second" in the specification, claims, and accompanying drawings of this application are used to distinguish different objects, not to describe a specific order. Furthermore, the term "comprising" and any variations thereof are intended to cover non-exclusive protection. For example, a process, method, system, product, or device that includes a series of steps or units is not limited to the listed steps or units, but may optionally include steps or units not listed, or may optionally include other steps or units inherent to these processes, methods, products, or devices. The term "multiple" in this application can mean at least two, for example, two, three, or more, and the embodiments of this application do not impose limitations.

[0029] The following description, in conjunction with the accompanying drawings, illustrates exemplary embodiments of this application, including various details to aid understanding. These embodiments should be considered merely exemplary. Therefore, those skilled in the art should recognize that various changes and modifications can be made to the embodiments described herein without departing from the scope of this application. Similarly, for clarity and brevity, descriptions of well-known functions and structures are omitted in the following description. It should be noted that in the embodiments of this application, certain existing industry solutions such as software, components, and models may be mentioned. These should be considered exemplary, intended only to illustrate the feasibility of implementing the technical solutions of this application, and do not imply that the applicant has already used or necessarily used such solutions.

[0030] The acquisition, transmission, storage, and use of data in this application all comply with the requirements of relevant national laws and regulations.

[0031] To efficiently and accurately migrate data from a database, this application provides a data migration method, apparatus, device, and medium. Some preferred embodiments of this application are described below with reference to the accompanying drawings.

[0032] Example 1: Figure 1 The diagram illustrates a first-type data migration process provided by some embodiments, such as... Figure 1 As shown, the process includes the following steps: S101: For each data table to be migrated in the source database, if the data table to be migrated contains preset target format sub-data, then the target format sub-data is converted to obtain the target data table corresponding to the data table to be migrated.

[0033] The data migration method provided in this application is applied to electronic devices, such as personal computers (PCs), mobile terminals, servers, etc., and this application does not specifically limit them.

[0034] In one possible implementation, in order to efficiently and accurately migrate data from the database, the electronic device can perform a series of preparatory tasks before data migration, such as source database environment analysis, target database construction preparation, and data table structure conversion.

[0035] In the process of analyzing the source database environment, a detailed analysis can be conducted on the structure, views, stored procedures, indexes, and data types of the data tables in the source database (such as an Oracle database). Factors such as the amount of data in the data tables and business dependencies (distinguishing between static and dynamic data) can be evaluated to provide a basis for the formulation of subsequent migration plans.

[0036] TiDB, a distributed database compatible with the MySQL protocol, boasts advantages such as horizontal scalability, high availability, and support for large-scale data processing. Taking TiDB as the target database as an example, during the preparation for building the target database, a TiDB cluster can be deployed on the target system, configuring three main components: Placement Driver (PD), TiKV, and TiDB nodes. Reasonable performance and storage planning can be performed according to business needs to ensure the scalability and fault tolerance of the target cluster, which will not be elaborated further here.

[0037] When performing data table structure conversion, the pre-configured data structure automatic conversion module in the electronic device can identify the pre-configured field mapping rules, connect to the source database to read the data list in the database, and automatically and intelligently convert the table structure, field types, indexes and other information of the data tables in the source database (such as Oracle database) into Structured Query Language (SQL) statements compatible with the target database (TiDB database).

[0038] Specifically, corresponding field mapping rules can be configured to adapt to the characteristics of different database kernels. Based on these field mapping rules, when the source database's data table to be migrated contains sub-data in a preset format (referred to as target format sub-data for ease of description), the target format sub-data can be converted into data that is compatible with the target database, thereby obtaining the target data table corresponding to the data table to be migrated.

[0039] For example, taking Oracle as the source database and TiDB as the target database, data (sub-data) of variable-length string (2) type (VARCHAR2) in Oracle can be converted into sub-data of variable-length string (VARCHAR) type format adapted by TiDB. Similarly, numeric (NUMBER) type sub-data in Oracle can be converted into sub-data of decimal and fractional (DECIMAL) type format adapted by TiDB. Furthermore, sequence (SEQUENCE) data in Oracle can be converted into auto-increment column (AUTO_INCREMENT) data adapted by TiDB or globally unique ID data generated by TiDB's globally unique identity document (ID) generation mechanism; these details will not be elaborated upon here.

[0040] Additionally, primary keys, foreign keys, unique constraints, and index structures in an Oracle database can be converted to a TiDB-compatible format to migrate these structures to a TiDB database. This will not be elaborated upon further here.

[0041] S102: For each type of target data table, determine the target number of machines to migrate each type of target data table based on the data volume of each target data table of that type, the pre-configured migration rate corresponding to the target data table of that type, the migration time, and the number of concurrent migration queues per machine; allocate each type of target data table to the target number of machines, and migrate each type of target data table to the target database based on the target number of machines; wherein, the migration rate corresponding to different types of target data tables is different, and the machines used to migrate different types of target data tables are not the same machine.

[0042] After completing the preparatory work, the next step is to develop a migration plan. When developing a migration plan, it's important to consider that different types of data tables may have different migration rates. For example, the migration rate of a data table with a Character Large Object (CLOB) field may differ by a factor of ten compared to a data table without a CLOB field. CLOB is a built-in data type in the database used to store large character data, with a maximum capacity of up to 2GB. To ensure efficient and accurate data migration, the machines used to migrate different types of target data tables can be different; that is, the machines used to migrate different types of target data tables are not the same machine.

[0043] For example, when migrating data tables of different data types, the migration rate for tables of the first data type with CLOB fields is typically below 1 MB / s, while the migration rate for tables of the second data type without CLOB fields can reach 10-20 MB / s. To migrate database data efficiently and accurately, tables of the first data type and tables of the second data type can be migrated separately on different machines. That is, the first machine used to migrate the first data type and the second machine used to migrate the second data type are not the same machine.

[0044] Specifically, when formulating a migration plan, for each type of target data table, the target number of machines to be used for migrating each type of target data table can be determined based on the data volume of each target data table of that type, the pre-configured migration rate corresponding to the target data table of that type, the migration time, and the number of concurrent migration queues per machine. Then, each target data table of that type can be assigned to the target number of machines, and based on the target number of machines, each target data table of that type can be migrated to the target database.

[0045] For example, taking the migration of a second data type table without a CLOB field as an example, assuming that the machine's Central Processing Unit (CPU), memory, and Network Attached Storage (NAS) capacity are considered, and considering a machine with 16GB of memory and a memory utilization rate below 80%, the machine can stably run 4 concurrent queues, with each queue (single queue) having a migration rate of approximately 10-20 MB / s. Assuming that the migration of this type of data table needs to be completed within 5 days (migration time), we can first calculate the product of the migration rate of the target data table of this type (10-20 MB / s), the migration time (5 days, the time unit can be converted to seconds), and the number of concurrent migration queues per machine (e.g., 4). This gives the amount of data that can be migrated by each machine within the specified migration time. Then, we can divide the total amount of data in all target data tables of this type by the amount of data that can be migrated by each machine within the specified migration time to obtain the number of machines used to migrate this type of target data table (for ease of description, this is called the target number).

[0046] Subsequently, in order to improve the smoothness of the migration process and ensure that the migration task can be completed within the specified (pre-configured) migration time, for each type of target data table, the target data table of that type can be evenly distributed to the corresponding number of target machines, so that the target number of machines can migrate the target data table to the target database evenly and smoothly within the specified migration time.

[0047] In one possible implementation, when evenly distributing target data tables of any type to a target number of machines, the target data tables of that type can first be sorted in descending order of data volume. Then, based on this sorting, several rounds of operations can be performed to evenly distribute the target data tables to each queue of the target number of machines until all target data tables of that type have been distributed. Specifically, in any round of distributing target data tables to each queue of the target number of machines, the following operations can be performed: The target queue set for pre-allocating target data in this round can be determined based on the current amount of data to be migrated in each queue of the target number of machines. For example, queues with a current amount of data to be migrated that is less than a set data volume threshold can be identified as queues in the target queue set for pre-allocating target data in this round. As another example, to accurately determine the target queue set, for each queue in the target number of machines, it can be determined whether the current amount of data to be migrated in that queue exceeds a set percentage of the current amount of data to be migrated in any other queue. If so, the current amount of data to be migrated in that queue is considered large, and a target data table can be temporarily excluded from the target queue set in this round, to be re-evaluated in the next round. Conversely, if the current amount of data to be migrated in that queue does not exceed a set percentage of the current amount of data to be migrated in any other queue, the current amount of data to be migrated in that queue is considered not large, and a target data table can be allocated to it in this round, thus adding that queue to the target queue set. The set percentage can be 30%, 40%, etc., and this application does not impose a specific limitation on it; it can be flexibly set according to requirements.

[0048] After determining the target queue set for this round of pre-allocation of target data tables, a set number (e.g., one) of target data tables can be allocated to each queue in the target queue set according to the sorting order described above. The set number can be one or more; this application does not impose a specific limitation and can flexibly set according to requirements. Based on this, it is possible to evenly distribute data tables with similar data volumes to each queue, thereby maximizing the stability and controllability of the migration process for each machine.

[0049] In one possible implementation, when migrating data tables, the business attributes of the data in the target data table can be used to determine whether the data is static or dynamic. Static data is a collection of data that maintains fixed attributes within a specific time and space range, possessing immutability and stability. Dynamic data is data generated during program execution and can change over time; it will not be elaborated further here. For each target data table to be migrated, if the data in the target data table is static, it can be migrated to the target database using a full synchronization method; if the data in the target data table is dynamic, it can be migrated to the target database using an incremental synchronization method.

[0050] The full synchronization method involves periodically reading all data from the source database and writing it to the target database. During a full synchronization migration, the corresponding data can be exported from the database using the DSG migration tool, or the relevant data tables can be extracted from the source database (such as an Oracle database) using a custom extraction tool to generate the target data tables to be migrated. Then, the target data table files can be imported into the destination database (such as a TiDB database) using the DSG migration tool. This process can handle error retries and breakpoint resumption during large-scale data transfers, ensuring data integrity, which will not be elaborated further here.

[0051] Incremental synchronization only transmits data that has changed (added / updated / deleted) in the source database since the last synchronization. For example, during incremental synchronization, the redo logs of the source database (such as an Oracle database) can be analyzed to identify incremental data after the time point of the last synchronization start. This data is then parsed into SQL statements compatible with the target database to obtain the target data tables to be migrated. The incremental data is then synchronized (migrated) to the target database (such as a TiDB database), ensuring data consistency between the source and target databases.

[0052] The efficiency of full synchronization is significantly higher than that of incremental synchronization. By migrating static data to the target database using full synchronization and dynamic data to the target database using incremental synchronization, both data migration efficiency and accuracy can be guaranteed.

[0053] In one possible implementation, to ensure the accuracy of data migration and the consistency of data between the source and target databases, a consistency check can be performed on the data in both databases. During this consistency check (data consistency check), a full (all) check can be performed on the migrated data tables, or only a portion of the data tables can be sampled for check. For example, this process can be performed using a data consistency check tool developed by the applicant. When performing sample check, the sample check can be selected in the data consistency check tool's graphical interface, and the corresponding data time can be entered to perform sample check on the data at that time; this will not be elaborated further. Alternatively, multi-point, multi-time-period detection and comparison (consistency check) can be performed during the migration phase. For example, without affecting migration efficiency, multiple consistency checks can be performed at different stages, such as after full synchronization of static data and incremental synchronization of dynamic data to achieve real-time data catching up, ensuring that problems encountered during the migration process are detected early and appropriate compensation measures are implemented promptly.

[0054] In addition, when performing data consistency verification, the data consistency verification tool's interface allows you to compare the table structure and indexes of the same data table between the source and target databases, or to compare the data itself. When comparing the data of the same data table, you can compare the total number of records in the table or perform a one-to-one comparison of all field values ​​using the primary key (or unique index) to ensure complete data consistency between the source and target databases. Besides field-level consistency verification, row-level verification and other methods can also be used to ensure consistency across different business data. Row-level verification is a technique that verifies data integrity by calculating checksums or hash values ​​at the row level, which will not be elaborated upon here.

[0055] In one possible implementation, for each target data table, if the consistency check result of the target data table is inconsistent, a compensation mechanism can be used to achieve data consistency between the source database and the target database. Specifically, if the consistency check result of the target data table is inconsistent, the missing sub-data can be further located and identified, that is, the inconsistent sub-data in the target data table can be located and identified. Then, the corresponding sub-data in the source database can be migrated to the target database, and the sub-data can be concatenated and merged into the target data table in the target database. For example, suppose a data table in the source database has 100 rows, but the consistency check result shows that the data table has only 99 rows in the target database. By comparison, it can be found that the 20th row is missing. This row can be migrated to the target database separately, and the row can be concatenated and merged into the 20th row of the target data table to achieve data consistency between the source database and the target database.

[0056] Furthermore, considering that locating and identifying missing sub-data takes time, and if the total data volume of the target data table is not large, the time spent locating and identifying missing sub-data may exceed the time spent re-migrating the target data table. In this case, the target data table can be directly removed (cleared) from the target database, and the target data table in the source database can be re-identified as the data table to be migrated. Based on the data migration method described in the above embodiment, the target data table is then re-migrated to the target database (reloaded). Specifically, for each target data table, if the consistency check result of the target data table is inconsistent, it can be first determined whether the data volume of the target data table is greater than a set threshold (e.g., 1,000,000M). If so, the missing sub-data can be located and identified, the corresponding sub-data in the source database can be migrated to the target database, and the sub-data can be concatenated and merged into the target data table in the target database. If the data volume of the target data table is not greater than the set threshold, the target data table can be removed from the target database, and the target data table can be re-identified as the data table to be migrated, and then re-migrated to the target database.

[0057] In one possible implementation, the aforementioned compensation mechanism can be performed at any time during the migration process, or during the system downtime window for switching between the old and new databases; this application does not specifically limit this. Additionally, a backup machine can be pre-configured, and the consistency verification and compensation mechanisms described above can be executed on the backup machine.

[0058] To facilitate understanding, the data migration process provided in this application will be explained and illustrated below through a specific embodiment. (See reference...) Figure 2 , Figure 2The diagram illustrates a second data migration process provided by some embodiments, which includes the following steps: S201: For each table to be migrated in the source Oracle database, if the table contains sub-data in a preset target format, the target format sub-data is converted to obtain the target table corresponding to the table to be migrated.

[0059] Among these features, automated data structure conversion tools can be used to automate the conversion of table structures and data formats between Oracle databases and TiDB databases, reducing the complexity and error rate of manual operations.

[0060] S202: For each type of target data table, determine the target number of machines to migrate each type of target data table based on the data size of each target data table of that type, the pre-configured migration rate corresponding to the target data table of that type, the migration time, and the number of concurrent migration queues per machine. Note that different types of target data tables have different migration rates, and the machines used to migrate different types of target data tables are not the same machine.

[0061] This application can migrate not only existing data but also real-time generated data. Compared to related technologies where migration and verification must be performed within the system downtime window during the switch between old and new databases, which can lead to prolonged system downtime and impact user experience if the amount of data to be migrated is large, the migration time for each type of data table in this application can be any time before the system downtime window during the switch between old and new databases. This avoids the risks and inconveniences associated with performing migration and verification within the system downtime window. Of course, some dynamically generated data tables with small amounts of data (such as data tables involved in information reports) can also be migrated within the downtime window. This application does not impose specific limitations on this and can flexibly set it according to needs.

[0062] S203: For each type of target data table, sort the target data tables of that type according to their data volume; and perform the following operations in each round of the loop when allocating target data tables to the queues of the target number of machines, until all target data tables of that type have been allocated: For each queue in the target number of machines, determine whether the amount of data to be migrated in that queue exceeds a set proportion of the amount of data to be migrated in any other queue; if so, exclude that queue from the target queue set; otherwise, add that queue to the target queue set. Following the sorting order described above, a target data table is assigned to each queue in the target queue set in turn.

[0063] S204: Based on the target number of machines, migrate each target data table of this type to the target TiDB database. Specifically, for each target data table, if the data in the target data table is static, migrate the target data table to the TiDB database using a full synchronization method; if the data in the target data table is dynamic, migrate the target data table to the TiDB database using an incremental synchronization method.

[0064] The electronic device's display interface can show information such as the number of data tables to be migrated, the total size of the data, and the estimated time, which is convenient for users to view and will not be elaborated further here.

[0065] S205: For each target data table, if the consistency check result of the data in the target database and the data in the source database is inconsistent, then determine whether the data size of the target data table exceeds a set threshold. If so, identify the inconsistent sub-data, migrate the corresponding sub-data from the source Oracle database to the target TiDB database using the standby machine, and merge the sub-data into the target data table in the TiDB database. If not, remove the target data table from the target TiDB database, re-identify the target data table as the data table to be migrated, and migrate the target data table back to the target TiDB database using the standby machine.

[0066] Specifically, for target data tables and their sub-data that have inconsistent consistency verification results, the target data tables or sub-data can be migrated back to the target TiDB database using a dedicated backup machine. This avoids affecting the migration rate of the aforementioned number of target machines and ensures the stability and controllability of the migration process to the greatest extent.

[0067] In this embodiment, for each data table to be migrated in the source database, the format of the data table to be migrated is converted to obtain the target data table corresponding to the data table to be migrated; for each type of target data table, the target number of machines used to migrate each type of target data table is determined based on the data size of each target data table of that type, the pre-configured migration rate corresponding to the target data table of that type, the migration time, and the number of concurrent migration queues on each machine; based on the target number of machines, each type of target data table is migrated to the target database; wherein, the migration rate corresponding to different types of target data tables is different, and the machines used to migrate different types of target data tables are not the same machine, based on this, the purpose of efficiently and accurately migrating database data can be achieved.

[0068] The data migration method provided in this application has extensive practical experience in domestic IT innovation transformation systems. It has successfully tackled and solved problems such as large data volume, complex data distribution, and large incremental data in multiple domestically produced systems within the industry. It has good scalability, improves the efficiency and accuracy of database migration, and is particularly suitable for large-scale data migration and data migration needs of complex business systems. It is especially adaptable to domestic IT innovation transformation systems.

[0069] Example 2: Based on the same technical concept, this application provides a data migration device, see reference. Figure 3 , Figure 3 The diagram illustrates a data migration apparatus according to some embodiments, the apparatus comprising: The conversion module 301 is used to convert the target format sub-data for each data table to be migrated in the source database if the data table to be migrated contains a preset target format sub-data to obtain the target data table corresponding to the data table to be migrated. The migration module 302 is used to determine the target number of machines to migrate each type of target data table based on the data size of each target data table of that type, the pre-configured migration rate corresponding to the target data table of that type, the migration time, and the number of concurrent migration queues per machine; allocate each type of target data table to the target number of machines; and migrate each type of target data table to the target database based on the target number of machines; wherein, the migration rate corresponding to different types of target data tables is different, and the machines used to migrate different types of target data tables are not the same machine.

[0070] In one possible implementation, the migration module 302 is specifically used for: Sort the target data tables of this type according to their data volume; and perform the following operations in each round of the loop when allocating target data tables to the queues of the target number of machines, until all target data tables of this type have been allocated: Based on the amount of data to be migrated in each queue of the target number of machines, determine the target queue set of the pre-allocated target data table for this round; According to the sorting order, a set number of target data tables are assigned to each queue in the target queue set.

[0071] In one possible implementation, the migration module 302 is specifically used for: For each queue in the target number of machines, determine whether the amount of data to be migrated in the queue currently exceeds a set proportion of the amount of data to be migrated in any other queue; if so, exclude the queue from the target queue set; otherwise, add the queue to the target queue set.

[0072] In one possible implementation, the migration module 302 is specifically used for: For each target data table, if the data in the target data table is static, the target data table is migrated to the target database using a full synchronization method; if the data in the target data table is dynamic, the target data table is migrated to the target database using an incremental synchronization method.

[0073] In one possible implementation, the device further includes: The verification and compensation module 303 is used to identify inconsistent sub-data for each target data table if the consistency verification result of the data in the target database and the data in the source database is inconsistent. The module then migrates the corresponding sub-data from the source database to the target database and merges the sub-data into the target data table in the target database.

[0074] In one possible implementation, the verification compensation module 303 is further configured to: Determine whether the data volume of the target data table exceeds a set threshold. If so, proceed to the next step of identifying inconsistent sub-data. If not, the target data table will be removed from the target database and re-identified as the data table to be migrated.

[0075] In one possible implementation, the target data table may be of the following types: a first data type with a CLOB field and a second data type without a CLOB field.

[0076] In one possible implementation, the source database is an Oracle database; the target database is a TiDB database.

[0077] Example 3: Based on the same inventive concept, and building upon the above embodiments, this application also provides an electronic device that can realize the functions of the data migration device discussed above. Please refer to [link to relevant documentation]. Figure 4 , Figure 4The diagram illustrates the structure of an electronic device according to some embodiments. The electronic device includes a processor 401, a communication interface 402, a memory 403, and a communication bus 404, wherein the processor 401, the communication interface 402, and the memory 403 communicate with each other through the communication bus 404. The memory 403 stores a computer program, which, when executed by the processor 401, causes the processor 401 to perform the following steps: For each data table to be migrated in the source database, if the data table to be migrated contains a preset target format sub-data, then the target format sub-data is converted to obtain the target data table corresponding to the data table to be migrated. For each type of target data table, based on the data size of each target data table of that type, the pre-configured migration rate corresponding to the target data table of that type, the migration time, and the number of concurrent migration queues per machine, the target number of machines for migrating each target data table of that type is determined; each target data table of that type is allocated to the target number of machines, and based on the target number of machines, each target data table of that type is migrated to the target database; wherein, the migration rate corresponding to different types of target data tables is different, and the machines used to migrate different types of target data tables are not the same machines.

[0078] In one possible implementation, the processor 401 is specifically used for: Sort the target data tables of this type according to their data volume; and perform the following operations in each round of the loop when allocating target data tables to the queues of the target number of machines, until all target data tables of this type have been allocated: Based on the amount of data to be migrated in each queue of the target number of machines, determine the target queue set of the pre-allocated target data table for this round; According to the sorting order, a set number of target data tables are assigned to each queue in the target queue set.

[0079] In one possible implementation, the processor 401 is specifically used for: For each queue in the target number of machines, determine whether the amount of data to be migrated in the queue currently exceeds a set proportion of the amount of data to be migrated in any other queue; if so, exclude the queue from the target queue set; otherwise, add the queue to the target queue set.

[0080] In one possible implementation, the processor 401 is specifically used for: For each target data table, if the data in the target data table is static, the target data table is migrated to the target database using a full synchronization method; if the data in the target data table is dynamic, the target data table is migrated to the target database using an incremental synchronization method.

[0081] In one possible implementation, the processor 401 is further configured to: For each target data table, if the consistency check result of the data in the target database and the data in the source database is inconsistent, then the inconsistent sub-data is identified, the corresponding sub-data in the source database is migrated to the target database, and the sub-data is spliced ​​and merged into the target data table in the target database.

[0082] In one possible implementation, the processor 401 is further configured to: Determine whether the data volume of the target data table exceeds a set threshold. If so, proceed to the next step of identifying inconsistent sub-data. If not, the target data table will be removed from the target database and re-identified as the data table to be migrated.

[0083] In one possible implementation, the target data table may be of the following types: a first data type with a CLOB field and a second data type without a CLOB field.

[0084] In one possible implementation, the source database is an Oracle database; the target database is a TiDB database.

[0085] The communication bus mentioned in the above electronic devices can be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, etc. This communication bus can be divided into address bus, data bus, control bus, etc. For ease of illustration, only one thick line is used to represent it in the diagram, but this does not mean that there is only one bus or one type of bus.

[0086] Communication interface 402 is used for communication between the above-mentioned electronic device and other devices.

[0087] The memory may include random access memory (RAM) or non-volatile memory (NVM), such as at least one disk storage device. Optionally, the memory may also be at least one storage device located remotely from the aforementioned processor.

[0088] The processors mentioned above can be general-purpose processors, including central processing units, network processors (NPs), etc.; they can also be digital signal processors (DSPs), application-specific integrated circuits, field-programmable gate arrays or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc.

[0089] Based on the same inventive concept, embodiments of this application provide a computer-readable storage medium, the computer program product including: computer program code, which, when run on a computer, causes the computer to perform the following steps: For each data table to be migrated in the source database, if the data table to be migrated contains a preset target format sub-data, then the target format sub-data is converted to obtain the target data table corresponding to the data table to be migrated. For each type of target data table, based on the data size of each target data table of that type, the pre-configured migration rate corresponding to the target data table of that type, the migration time, and the number of concurrent migration queues per machine, the target number of machines for migrating each target data table of that type is determined; each target data table of that type is allocated to the target number of machines, and based on the target number of machines, each target data table of that type is migrated to the target database; wherein, the migration rate corresponding to different types of target data tables is different, and the machines used to migrate different types of target data tables are not the same machines.

[0090] In one possible implementation, assigning each target data table of this type to the target number of machines includes: Sort the target data tables of this type according to their data volume; and perform the following operations in each round of the loop when allocating target data tables to the queues of the target number of machines, until all target data tables of this type have been allocated: Based on the amount of data to be migrated in each queue of the target number of machines, determine the target queue set of the pre-allocated target data table for this round; According to the sorting order, a set number of target data tables are assigned to each queue in the target queue set.

[0091] In one possible implementation, determining the target queue set for the current pre-allocated target data based on the current amount of data to be migrated in each queue of the target number of machines includes: For each queue in the target number of machines, determine whether the amount of data to be migrated in the queue currently exceeds a set proportion of the amount of data to be migrated in any other queue; if so, exclude the queue from the target queue set; otherwise, add the queue to the target queue set.

[0092] In one possible implementation, migrating the target data tables of this type to the target database includes: For each target data table, if the data in the target data table is static, the target data table is migrated to the target database using a full synchronization method; if the data in the target data table is dynamic, the target data table is migrated to the target database using an incremental synchronization method.

[0093] In one possible implementation, the method further includes: For each target data table, if the consistency check result of the data in the target database and the data in the source database is inconsistent, then the inconsistent sub-data is identified, the corresponding sub-data in the source database is migrated to the target database, and the sub-data is spliced ​​and merged into the target data table in the target database.

[0094] In one possible implementation, if the consistency check result of the data in the target data table in the target database is inconsistent with the data in the source database, before identifying the inconsistent sub-data, the method further includes: Determine whether the data volume of the target data table exceeds a set threshold. If so, proceed to the next step of identifying inconsistent sub-data. If not, the target data table will be removed from the target database and re-identified as the data table to be migrated.

[0095] In one possible implementation, the target data table may be of the following types: a first data type with a CLOB field and a second data type without a CLOB field.

[0096] In one possible implementation, the source database is an Oracle database; the target database is a TiDB database.

[0097] The aforementioned computer-readable storage medium can be any available medium or data storage device that can be accessed by the processor in an electronic device, including but not limited to magnetic storage such as floppy disks, hard disks, magnetic tapes, magneto-optical disks (MO), optical storage such as CDs, DVDs, BDs, HVDs, etc., and semiconductor storage such as ROMs, EPROMs, EEPROMs, non-volatile memory (NAND flash), solid-state drives (SSDs), etc.

[0098] Based on the same inventive concept, this application also provides a computer program product, which includes computer program code that, when run on a computer, causes the computer to execute any of the methods described above. Since the principles and methods by which the above computer program products solve problems are similar, the implementation of the above computer program products can be referred to the implementation of the methods, and repeated details will not be elaborated further.

[0099] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application 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.

[0100] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to this application. It should be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0101] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0102] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0103] Obviously, those skilled in the art can make various modifications and variations to this application without departing from the spirit and scope of this application. Therefore, if such modifications and variations fall within the scope of the claims of this application and their equivalents, this application also intends to include such modifications and variations.

Claims

1. A data migration method, characterized in that, The method includes: For each data table to be migrated in the source database, if the data table to be migrated contains a preset target format sub-data, then the target format sub-data is converted to obtain the target data table corresponding to the data table to be migrated. For each type of target data table, based on the data size of each target data table of that type, the pre-configured migration rate corresponding to the target data table of that type, the migration time, and the number of concurrent migration queues per machine, the target number of machines for migrating each target data table of that type is determined; each target data table of that type is allocated to the target number of machines, and based on the target number of machines, each target data table of that type is migrated to the target database; wherein, the migration rate corresponding to different types of target data tables is different, and the machines used to migrate different types of target data tables are not the same machines.

2. The method according to claim 1, characterized in that, The process of assigning each target data table of this type to the target number of machines includes: Sort the target data tables of this type according to their data volume; and perform the following operations in each round of the loop when allocating target data tables to the queues of the target number of machines, until all target data tables of this type have been allocated: Based on the amount of data to be migrated in each queue of the target number of machines, determine the target queue set of the pre-allocated target data table for this round; According to the sorting order, a set number of target data tables are assigned to each queue in the target queue set.

3. The method according to claim 2, characterized in that, The step of determining the target queue set for pre-allocated target data in this round based on the current amount of data to be migrated in each queue of the target number of machines includes: For each queue in the target number of machines, determine whether the amount of data to be migrated in the queue currently exceeds a set proportion of the amount of data to be migrated in any other queue; if so, exclude the queue from the target queue set; otherwise, add the queue to the target queue set.

4. The method according to claim 1, characterized in that, The migration of each target data table of this type to the target database includes: For each target data table, if the data in the target data table is static, the target data table is migrated to the target database using a full synchronization method; if the data in the target data table is dynamic, the target data table is migrated to the target database using an incremental synchronization method.

5. The method according to claim 1, characterized in that, The method further includes: For each target data table, if the consistency check result of the data in the target database and the data in the source database is inconsistent, then the inconsistent sub-data is identified, the corresponding sub-data in the source database is migrated to the target database, and the sub-data is spliced ​​and merged into the target data table in the target database.

6. The method according to claim 5, characterized in that, If the consistency check result of the data in the target data table in the target database is inconsistent with the data in the source database, before identifying the inconsistent sub-data, the method further includes: Determine whether the data volume of the target data table exceeds a set threshold. If so, proceed to the next step of identifying inconsistent sub-data. If not, the target data table will be removed from the target database and re-identified as the data table to be migrated.

7. The method according to claim 1, characterized in that, The target data table includes two types: a first data type with a CLOB field and a second data type without a CLOB field.

8. The method according to any one of claims 1-7, characterized in that, The source database is an Oracle database; the target database is a TiDB database.

9. A data migration device, characterized in that, The device includes: The conversion module is used to convert the target format sub-data for each data table to be migrated in the source database if the data table to be migrated contains a preset target format sub-data to obtain the target data table corresponding to the data table to be migrated. The migration module is used to determine the target number of machines to migrate each type of target data table based on the data volume of each target data table of that type, the pre-configured migration rate corresponding to the target data table of that type, the migration time, and the number of concurrent migration queues per machine; allocate each type of target data table to the target number of machines; and migrate each type of target data table to the target database based on the target number of machines; wherein, the migration rate corresponding to different types of target data tables is different, and the machines used to migrate different types of target data tables are not the same machines.

10. The apparatus according to claim 9, characterized in that, The migration module is specifically used for: Sort the target data tables of this type according to their data volume; and perform the following operations in each round of the loop when allocating target data tables to the queues of the target number of machines, until all target data tables of this type have been allocated: Based on the amount of data to be migrated in each queue of the target number of machines, determine the target queue set of the pre-allocated target data table for this round; According to the sorting order, a set number of target data tables are assigned to each queue in the target queue set.

11. The apparatus according to claim 10, characterized in that, The migration module is specifically used for: For each queue in the target number of machines, determine whether the amount of data to be migrated in that queue exceeds a set proportion of the amount of data to be migrated in any other queue; if so, exclude that queue from the target queue set. Otherwise, the queue is assigned to the target queue set.

12. The apparatus according to claim 9, characterized in that, The migration module is specifically used for: For each target data table, if the data in the target data table is static, the target data table is migrated to the target database using a full synchronization method; if the data in the target data table is dynamic, the target data table is migrated to the target database using an incremental synchronization method.

13. The apparatus according to claim 9, characterized in that, The device further includes: The verification and compensation module is used to identify inconsistent sub-data for each target data table if the consistency verification result between the data in the target database and the data in the source database is inconsistent. The module then migrates the corresponding sub-data from the source database to the target database and merges the sub-data into the target data table in the target database.

14. The apparatus according to claim 13, characterized in that, The verification compensation module is also used for: Determine whether the data volume of the target data table exceeds a set threshold. If so, proceed to the next step of identifying inconsistent sub-data. If not, the target data table will be removed from the target database and re-identified as the data table to be migrated.

15. The apparatus according to claim 9, characterized in that, The target data table includes two types: a first data type with a CLOB field and a second data type without a CLOB field.

16. The apparatus according to any one of claims 9-15, characterized in that, The source database is an Oracle database; the target database is a TiDB database.

17. An electronic device, characterized in that, include: Memory, used to store program instructions; A processor is configured to invoke program instructions stored in the memory and execute the steps of the method according to any one of claims 1-8.

18. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program, the computer program including program instructions that, when executed by a computer, cause the computer to perform the method as described in any one of claims 1-8.

19. A computer program product, characterized in that, The computer program product includes: computer program code, which, when run on a computer, causes the computer to perform the method described in any one of claims 1-8.

Images