Database data migration method, device and storage medium
The database data migration method addresses limitations of existing tools by providing seamless, real-time data migration between heterogeneous databases with asynchronous verification, ensuring data integrity and flexibility without service interruption.
Patent Information
- Application Number
- CN202011247564.7
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2020-11-10
- Publication Date
- 2024-12-06
- Estimated Expiration
- 2040-11-10
AI Technical Summary
Existing data migration tools are limited in scope, expensive, and require service interruption, failing to support seamless migration between heterogeneous databases and lacking efficient asynchronous verification.
A database data migration method that configures data mapping and link information, synchronizes data while services are running, verifies data integrity, and supports reverse synchronization to ensure seamless migration without downtime, using connection pools and asynchronous verification.
Enables real-time, non-interruptive data migration between heterogeneous databases with efficient asynchronous verification, ensuring data consistency and flexibility across various database systems.
Smart Images

Figure CN112306994B_ABST
Abstract
Description
Technical Field
[0001] The present disclosure relates to the field of database technology, and in particular to a database data migration method, device and storage medium. Background Art
[0002] Existing data migration usually relies on the data migration tools provided by the database vendors themselves. These tools are highly professional and fully functional, and are suitable for large-scale data migration between specific databases. However, they have a narrow range of uses and are expensive. Moreover, most of the existing migration tools do not support data migration and switching without interrupting services, and are not applicable to core business systems. Summary of the invention
[0003] In view of this, a technical problem to be solved by the present invention is to provide a database data migration method, device and storage medium.
[0004] According to a first aspect of the present disclosure, a database data migration method is provided, including: configuring data mapping information between a source database table and a target database table; synchronizing the data in the source database table into the target database table based on the data mapping information when the source database and the target database provide services; after performing a data synchronization operation between the source database table and the target database table, performing verification processing on the data in the source database table and the target database table, judging whether the data synchronization operation is correct based on the verification result and performing corresponding processing; after judging that the data synchronization operation is correct, synchronizing the newly added data in the target database table into the source database table based on the data mapping information.
[0005] Optionally, data link configuration information of the source database and the target database is configured; and a source database connection pool and a target database connection pool are configured based on the data link configuration information to store source database links and target database links respectively.
[0006] Optionally, the data link configuration information includes: database type, link address, connection string, and database user name and password; configuring the source database connection pool and the target database connection pool based on the data link configuration information includes: creating the source database connection pool and the target database connection pool, and determining the maximum number of database connections; based on the data link information and the maximum number of database connections, generating the source database link and the target database link, and placing them in the source database connection pool and the target database connection pool, respectively.
[0007] Optionally, the data mapping information includes: a correspondence between data tables and a mapping relationship between data table fields; synchronizing the data in the source database table in the target database table based on the data mapping information includes: using a source database link in the source database connection pool and a target database link in the target database link pool, and synchronizing the data in the source database table in the target database table based on the data mapping information.
[0008] Optionally, the using the source database link in the source database connection pool and the target database link in the target database link pool to synchronize the data in the source database table into the target database table based on the data mapping information includes: configuring data synchronization frequency information corresponding to the source database table; generating a first task corresponding to the source database table, and setting the execution frequency of the first task based on the data synchronization frequency information; wherein the first task is executed using a thread corresponding to the source data table; obtaining a target database link from the target database link pool based on the first task, and obtaining data information to be synchronized using the obtained target database link; obtaining a source database link from the source database connection pool based on the first task, and obtaining data to be synchronized from the source database table using the obtained source database link and based on the data information to be synchronized; and inserting the data to be synchronized into the target database table using the obtained target database link and based on the data mapping information.
[0009] Optionally, the using the acquired target database link to obtain the data information that needs to be synchronized includes: using the acquired target database link to query the maximum ID value corresponding to the data record in the target database table; the using the acquired source database link and obtaining the data that needs to be synchronized from the source database table based on the data information that needs to be synchronized includes: using the acquired source database link to obtain data whose data record ID value is greater than the maximum ID value from the source database table as the data that needs to be synchronized.
[0010] Optionally, field type conversion information between the source database and the target database is obtained; when the data to be synchronized is inserted into the target database table, type conversion processing is performed on the data to be synchronized based on the field type conversion information.
[0011] Optionally, after the data to be synchronized is inserted into the target database table, a second task corresponding to the source database table is generated; wherein the second task is executed using a thread corresponding to the source data table; based on the second task, a source database link is obtained from the source database connection pool, and the data of the source database table modified during the data synchronization operation is obtained using the obtained source database link; based on the second task, a target database link is obtained from the target database link pool, and the data corresponding to the modified data is updated in the target database table using the obtained target database link.
[0012] Optionally, the verifying the data in the source database table and the target database table, judging whether the data synchronization operation is correct based on the verification result and performing corresponding processing comprises: generating a third task corresponding to the source database table; wherein the third task is executed using a thread corresponding to the source data table; obtaining a source database link from the source database connection pool based on the third task, using the obtained source database link to query the data corresponding to the data synchronization operation stored in the source database table, converting all field values contained in the data into character types and performing splicing processing, encrypting the spliced string to obtain a first encrypted value; obtaining a target database link from the target database connection pool based on the third task, using the obtained target database link to query the data corresponding to the data synchronization operation stored in the target database table, converting all field values contained in the data into character types and performing splicing processing, encrypting the spliced string to obtain a second encrypted value; if it is determined that the first encrypted value is the same as the corresponding second encrypted value, then it is determined that the data synchronization is correct; if it is determined that the first encrypted value is not the same as the corresponding second encrypted value, then it is determined that the data synchronization is abnormal, and corresponding abnormal processing is performed.
[0013] Optionally, synchronizing the newly added data in the target database table with the source database table based on the data mapping information includes: generating a fourth task corresponding to the target database table; wherein the fourth task is executed using a thread corresponding to the target data table; obtaining a target database link from the target database connection pool based on the fourth task, and using the obtained target database link to query the target data table for newly added data after the data synchronization operation; obtaining a source database link from the source database connection pool based on the fourth task, and synchronizing the newly added data in the source data table using the obtained source database link and based on the data mapping information.
[0014] Optionally, the source database and the target database are of different database types; and at least one field of the source database table and the target database table is different.
[0015] According to a second aspect of the present disclosure, a database data migration device is provided, comprising: a mapping configuration module, configured to configure data mapping information between a source database table and a target database table; wherein the data mapping information comprises: a corresponding relationship between data tables and a mapping relationship between data table fields; a data synchronization module, configured to synchronize the data in the source database table to the target database table based on the data mapping information when the source database and the target database provide services; a data verification module, configured to verify the data in the source database table and the target database table after performing a data synchronization operation between the source database table and the target database table, determine whether the data synchronization operation is correct based on the verification result and perform corresponding processing; and a reverse synchronization module, configured to synchronize the newly added data in the target database table to the source database table based on the data mapping information after determining that the data synchronization operation is correct.
[0016] In one embodiment, the link configuration module is used to configure the data link configuration information of the source database and the target database; the link generation module is used to configure the source database connection pool and the target database connection pool respectively based on the data link configuration information to store the source database link and the target database link respectively.
[0017] According to a third aspect of the present disclosure, a database data migration device is provided, comprising: a memory; and a processor coupled to the memory, wherein the processor is configured to execute the above method based on instructions stored in the memory.
[0018] According to a fourth aspect of the present disclosure, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores computer instructions, and the instructions are executed by a processor to implement the above database data migration method.
[0019] The database data migration method, device and storage medium disclosed in the present invention can realize data migration without stopping the system, thus solving the problem of data migration downtime in the online system; adopt a two-way data append strategy, thus realizing that no matter data is written to any database, it can be synchronized to another database in real time, thus being suitable for data migration of online non-interruptible services; can shield the differences between database and data table field attributes, thus realizing data migration between heterogeneous databases and heterogeneous table structures, thus making it more flexible to use; can asynchronously perform data integrity verification, and independently run data synchronization and data verification, thus improving the efficiency of synchronization operations. BRIEF DESCRIPTION OF THE DRAWINGS
[0020] In order to more clearly illustrate the embodiments of the present disclosure or the technical solutions in 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 only some embodiments of the present disclosure. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.
[0021] Figure 1 A flowchart of an embodiment of a database data migration method according to the present disclosure;
[0022] Figure 2 A schematic diagram of a process of configuring a database connection pool in an embodiment of a database data migration method according to the present disclosure;
[0023] Figure 3 A schematic diagram of a process of performing data synchronization in an embodiment of a database data migration method according to the present disclosure;
[0024] Figure 4 This is a schematic diagram of a process of updating data in one embodiment of a database data migration method according to the present disclosure;
[0025] Figure 5 A schematic diagram of a flow chart of verification processing in an embodiment of a database data migration method according to the present disclosure;
[0026] Figure 6 This is a schematic diagram of a flow chart of verification processing in another embodiment of the database data migration method according to the present disclosure;
[0027] Figure 7 A schematic diagram of a process of performing data reverse synchronization in an embodiment of a database data migration method according to the present disclosure;
[0028] Figure 8 A schematic diagram of modules of an embodiment of a database data migration device according to the present disclosure;
[0029] Fig. 9 is a module diagram of another embodiment of a database data migration device according to the present disclosure;
[0030] Fig.10 is a module schematic diagram of a data synchronization module in one embodiment of a database data migration device according to the present disclosure;
[0031] Fig.11 is a module schematic diagram of a data verification module in one embodiment of a database data migration device according to the present disclosure;
[0032] Fig.12A schematic diagram of a reverse synchronization module in an embodiment of a database data migration device according to the present disclosure;
[0033] Fig.13 It is a module diagram of another embodiment of the database data migration device according to the present disclosure. DETAILED DESCRIPTION
[0034] The present disclosure is described more fully below with reference to the accompanying drawings, in which exemplary embodiments of the present disclosure are described. The technical solutions in the embodiments of the present disclosure will be clearly and completely described below in conjunction with the drawings in the embodiments of the present disclosure. Obviously, the described embodiments are only part of the embodiments of the present disclosure, rather than all of the embodiments. Based on the embodiments in the present disclosure, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present disclosure. The technical solutions of the present disclosure are described in many aspects below in conjunction with various figures and embodiments.
[0035] The terms "first" and "second" below are only used to describe the difference and have no other special meanings.
[0036] At present, data migration usually relies on the data migration tools provided by the database vendor. For example, when the source database and the target database are both MySQL and the database table structure is exactly the same, you can use MySQL binlog for data synchronization. Binlog is used to record all updated data or potentially updated data; when the source database is a MySQL database but the target database is another database, you can use a plug-in for data synchronization. The plug-in can be Alibaba's canal plug-in; when the source database and the target database are both Oracle, you can use dblink for data synchronization; when the source database and the target database are both SQL Server databases, you can use the database publishing and subscription method for data synchronization.
[0037] Existing data migration technology solutions rely on data migration tools provided by database vendors, and the source database and target database must be of the same type, and the source database and target database table structures must be completely consistent, and the field types must also be of the same type. Most existing data migration technology solutions perform data migration when the application stops service, which is not applicable to core business systems where service cannot be interrupted; most existing data migration technology solutions use synchronous data verification, that is, verification is performed when a piece of data is synchronized, which is relatively inefficient, and when a migration fails during the migration process, it usually directly reports an error and exits, which brings a lot of repetitive work to the migration of large amounts of data.
[0038] Figure 1 FIG. 1 is a flow chart of an embodiment of a method for migrating database data according to the present disclosure. Figure 1 As shown:
[0039] Step 101: configure data mapping information between a source database table and a target database table.
[0040] In one embodiment, the source database is a source database for data synchronization, the source database may be a variety of databases that conform to standard SQL, and the source database table is a data table in the source database. The target database is a target database for data synchronization, the target database may be a variety of databases that conform to standard SQL, and the target database table is a data table in the target database.
[0041] The data mapping information includes the corresponding relationship between data tables and the mapping relationship between data table fields. The source database and the target database may be of different database types, for example, the source database is Oracle and the target database is MySQL. The source database table and the target database table may have one or more different fields.
[0042] Step 102: When the source database and the target database are providing services, the data in the source database table is synchronized in the target database table based on the data mapping information.
[0043] In one embodiment, data synchronization between the source database and the target database is achieved while the source database and the target database are still providing services such as query and data insertion.
[0044] Step 103: After performing data synchronization between the source database table and the target database table, verify the data in the source database table and the target database table, determine whether the data synchronization operation is correct based on the verification result, and perform corresponding processing.
[0045] Step 104: after determining that the data synchronization operation is correct, synchronize the newly added data in the target database table with the source database table based on the data mapping information.
[0046] In one embodiment, data link configuration information of the source database and the target database is configured, and the data link configuration information includes database type, link address, connection string, database user name and password, etc. Based on the data link configuration information, a source database link pool and a target database link pool are configured to store source database links and target database links respectively.
[0047] Figure 2 FIG. 1 is a flow chart of configuring a database connection pool in an embodiment of a database data migration method according to the present disclosure, as shown in FIG. Figure 2 As shown:
[0048] Step 201: Create a source database connection pool and a target database connection pool, and determine the maximum number of database connections.
[0049] Step 202: Based on the data link information and the maximum number of database connections, a source database link and a target database link are generated respectively, and are placed in a source database connection pool and a target database connection pool respectively.
[0050] The data mapping information is obtained, and the data mapping information includes the correspondence between data tables and the mapping relationship between data table fields. The source database link in the source database connection pool and the target database link in the target database connection pool are used to synchronize the data in the source database table with the target database table based on the data mapping information.
[0051] In one embodiment, multiple database data source connection methods can be configured through the management end program to seamlessly perform data migration. The management end program embeds common database connection jar packages, and configures the database connection method of the corresponding jar package in the configuration file. For example, the source database is Oracle, the target database is MySQL library; the source database table is the t_user table in the Oracle library, and its structure is shown in Table 1 below:
[0052] Field Name Field properties id Number(12) user_name Varchar2(64) age Number(2) create_date Date
[0053] Table 1 Structure of t_user The target database table is the t_person table in the MySQL database, and its structure is shown in Table 2 below:
[0054]
[0055]
[0056] Table 2 Structure table of t_person
[0057] The data link configuration information can be stored in the configuration file (config.properties). The management end can provide a web management interface for configuring the database link address, connection string, database username and password of the source database and the target database. The source database link is an oracle database link, and the target database link is a mysql database link.
[0058] Get the type and version of the source database and the target database, and configure the field type conversion information between the source database and the target database. For example, if the target database is MySQL, the date field needs to use the str_to_date function to convert the character type to the date type. The field type conversion information can be stored in the config.properties configuration file.
[0059] Configure data mapping information, which includes mapping between data tables in the source database and the target database, and mapping between data table fields. For example, the t_user table in the source database corresponds to the t_person table in the target database, and the user_name field in the t_user table corresponds to the person_name field in the t_person table. Data mapping information can be stored in a configuration file (mapping.properties file).
[0060] Configure the number of threads, that is, set the number of threads started by the server. The number of threads configured corresponds to the source database table. The number of threads can be flexibly configured according to the amount of data in the source database table, so that all table data can be migrated at the same time as much as possible. The number of threads is stored in the config.properties file.
[0061] Configure the frequency of scheduled data capture (data synchronization frequency). This frequency is best corresponding to the execution time of a single thread. For example, the execution time of a single thread is about 2 seconds, and the data synchronization frequency is set to once every 2.5 seconds. The data synchronization frequency is stored in confing.properties. You can configure the number of source database table data to be captured each time for source database query paging. This configuration item is stored in confing.properties.
[0062] Figure 3 FIG. 1 is a flow chart of data synchronization in one embodiment of the database data migration method according to the present disclosure, as shown in FIG. Figure 3 As shown:
[0063] Step 301: configure data synchronization frequency information corresponding to a source database table.
[0064] Step 302: Generate a first task corresponding to the source database table, and set the execution frequency of the first task based on the data synchronization frequency information; wherein, the first task is executed using a thread corresponding to the source database table.
[0065] In one embodiment, a task refers to an activity completed by software, and a task is executed by a process or thread. A thread corresponding to the source data table can be obtained or started from a thread pool, the execution frequency of the first task is set based on the data synchronization frequency information, and the thread corresponding to the source data table is used to execute the first task (timed execution).
[0066] Step 303: acquiring a target database link from a target database link pool based on the first task, and using the acquired target database link to acquire data information to be synchronized.
[0067] In one embodiment, the obtained target database link is used to query the maximum ID value corresponding to the data record in the target database table.
[0068] Step 304: obtain a source database link from the source database connection pool based on the first task, and obtain data to be synchronized from a source database table using the obtained source database link and based on the data information to be synchronized.
[0069] In one embodiment, the acquired source database link is used to acquire data whose data record ID value is greater than the maximum ID value in the target database table from the source database table as data that needs to be synchronized.
[0070] Step 305: Use the acquired target database link and based on the data mapping information to insert the data to be synchronized into the target database table.
[0071] The field type conversion information between the source database and the target database is obtained, and when the data to be synchronized is inserted into the target database table, the data to be synchronized is converted based on the field type conversion information.
[0072] In one embodiment, database link pools for the source database and the target database are created respectively according to the data link configuration information. Quartz is an open source project of the OpenSymphony open source organization in the field of job scheduling. It can be combined with J2EE and J2SE applications or used alone. Quartz can be used to create jobs. According to the number of database tables in the configuration file, use Quartz to dynamically create jobs (tasks). Each job_name corresponds to a database table, and the execution frequency of each job is set equal to the preset data synchronization frequency information.
[0073] Dynamically create the first task corresponding to the source database table. The name of the first task is job_user. This first task is a scheduled task. The first task needs to complete the following work:
[0074] 1. Get a target database link from the target database link pool. For example, get a database link from the Oracle database link pool.
[0075] 2. Query the maximum ID value in the target database table (the data before the maximum value must already exist in the target database table, so you only need to synchronize the data after the maximum value), and store it in the variable maxId. For example, if you need to query the maximum ID of the t_person table in the MySQL database, this ID is the ID that will be used to start the next execution of the first task. The query statement is:
[0076] select max(id) maxId from t_person。
[0077] 3. Obtain a source database connection from the source database connection pool. For example, obtain a database connection from the mysql database connection pool.
[0078] 4. Query the data that needs to be synchronized from the source database table. For example, query in the oracle database, and the sql statement is as follows:
[0079] select a1.* from (select t_user.*, rownum rn from t_user where id > maxId) t1 where rn >= limitNum;
[0080] Store the query result set into a List object.
[0081] 5. Obtain the field type conversion information between the source database and the target database, and check whether there are fields that need to be translated. For example, if the target database is the mysql database and the Create_date field is of date type, the sql statement needs to be concatenated as str_to_date(Create_date, 'yyyy-MM-dd').
[0082] 6. Use the batch insert method in the target database table to batch insert data. For example, if the target database is the mysql database, the sql statement for batch insert is as follows:
[0083] insert into t_person(id, person_name, age, create_date) VALUES(1, '张三
[0084] ', 20, str_to_date('2017-09-09', 'yyyy-MM-dd')), (2, '李四
[0085] ', 21, str_to_date('2017-09-09', 'yyyy-MM-dd')), (3, ’王五
[0086] ', 22, str_to_date('2017-09-09', 'yyyy-MM-dd'))。
[0087] Repeat the above steps 1-6 until the data synchronization is completed. After the data synchronization is completed, before the data source is switched to the target database, if there is new data in the source database, it will be synchronized to the target database in real time.
[0088] Figure 4 FIG. 1 is a flow chart of data updating in one embodiment of the database data migration method according to the present disclosure, as shown in FIG. Figure 4 As shown:
[0089] Step 401: after inserting the data to be synchronized into the target database table, generate a second task corresponding to the source database table; wherein the second task is executed using a thread corresponding to the source database table.
[0090] Step 402: acquiring a source database link from a source database connection pool based on the second task, and using the acquired source database link to acquire data modified in a source database table during the data synchronization operation.
[0091] Step 403: acquiring a target database link from the target database link pool based on the second task, and using the acquired target database link to update data corresponding to the modified data in the target database table.
[0092] In one embodiment, since the data source is not switched during the synchronization process, the production data is still written into the source database. It is very likely that the data previously synchronized to the target database has been modified during the synchronization process, so an incremental data modification is required.
[0093] Get a source database link from the source database link pool, for example, get a database link from the MySQL database link pool. Query the source database table for records of modified data since the data synchronization time point. All modified data after this synchronization time point will be presented through modified. As long as the modified time is after the current time point, it means that there is a modification. The corresponding SQL statement is as follows:
[0094] select*from t_user where modified>=data synchronization time point.
[0095] Store the query result set in the List object. Update the corresponding records in the target database table. The corresponding SQL statement is as follows:
[0096] update t_person set person_name=#t_user.user_name#where id=#t_user.id#.
[0097] Before the data source is switched to the target database, the second task will continue to run to ensure that the data between the two databases remains consistent.
[0098] Figure 5FIG. 1 is a flow chart of verification processing in an embodiment of the database data migration method according to the present disclosure, as shown in FIG. Figure 5 As shown:
[0099] Step 501: Generate a third task corresponding to a source database table; wherein the third task is executed using a thread corresponding to the source database table.
[0100] Step 502, based on the third task, obtain the source database link from the source database connection pool, use the obtained source database link to query the data stored in the source database table and corresponding to the data synchronization operation, convert all field values contained in this data into character type and perform splicing processing, encrypt the spliced character string, and obtain a first encrypted value.
[0101] Step 503, based on the third task, obtain the target database link from the target database connection pool, use the obtained target database link to query the data stored in the target database table and corresponding to the data synchronization operation, convert all field values contained in this data into character types and perform splicing processing, encrypt the spliced character string, and obtain a second encrypted value.
[0102] Step 504: If it is determined that the first encrypted value is the same as the corresponding second encrypted value, it is determined that the data synchronization is correct.
[0103] Step 504: If it is determined that the first encrypted value is different from the corresponding second encrypted value, then it is determined that the data synchronization is abnormal, and corresponding abnormal processing is performed. There are many types of abnormal processing, such as notifying the user of the abnormality, re-synchronizing the data, etc.
[0104] Figure 6 FIG. 1 is a flow chart of verification processing in another embodiment of the database data migration method according to the present disclosure, as shown in FIG. Figure 6 As shown:
[0105] Step 601, obtain the source database and target database links.
[0106] Step 602, read mapping.properties to obtain all field information of the data table.
[0107] Step 603, start multithreading.
[0108] In one embodiment, according to the number of source database tables, a verification job, i.e., the third task, is dynamically created using Quartz. Each job_name corresponds to a source database table, and a job is responsible for verifying a source database table and a corresponding target database table. Database links are obtained from the source database link pool and the target database link pool, respectively. The configuration information in the mapping.properties file is read to obtain all field information of all data tables in the database.
[0109] Steps 604-608, query the source database table and the data stored in the target database corresponding to the data synchronization operation, and save them in the list. Each time a record in the list is read, all fields of each record are converted to character types for character concatenation, and then the concatenated string is subjected to md5 operation to obtain the md5 value. Compare the two corresponding md5 values to see if they are consistent. If they are consistent, it means that the data synchronization is correct. If they are inconsistent, the inconsistent id is recorded.
[0110] Step 609, prompting the user that the data synchronization is abnormal.
[0111] In step 610, the user may resynchronize the table.
[0112] Step 611, prompting the user that the data migration is successful.
[0113] Figure 7 FIG. 1 is a flow chart of performing data reverse synchronization in one embodiment of the database data migration method according to the present disclosure, as shown in FIG. Figure 7 As shown:
[0114] Step 701, generating a fourth task corresponding to a target database table; wherein the fourth task is executed using a thread corresponding to the target database table.
[0115] Step 702: Based on the fourth task, a target database link is obtained from the target database connection pool, and the obtained target database link is used to query the target data table for newly added data after the data synchronization operation.
[0116] In one embodiment, after the data synchronization operation, the target database connection pool may be switched to the primary database, production data is written into the target database, and the acquired target database link is used to query the target data table for newly added data after the data synchronization operation.
[0117] Step 703: based on the fourth task, a source database link is obtained from the source database connection pool, and the newly added data is synchronized in the source data table using the obtained source database link and based on the data mapping information.
[0118] In one embodiment, when the data has been synchronized and verified, the reverse data synchronization process needs to be started. The reverse data synchronization process refers to appending data in reverse on the basis of the forward data migration, that is, synchronizing data from the target database to the source database. The purpose is to ensure that the data in the two databases are always consistent regardless of whether the data is written to the source database or the target database, so that the data source can be smoothly migrated to achieve the purpose of database migration without shutting down the online system.
[0119] A new set of reverse jobs, namely the fourth task, is added. The data synchronization process reuses the forward data synchronization process. The data source of the reverse job program is opposite to that of the forward job, that is, the reverse job program uses the target database as the master database and the source database as the slave database. Perform reverse synchronization from the target database to the source database. After the data of the source database and the target database are completely consistent, the online instance database link can be migrated to the target database in batches.
[0120] In one embodiment, Figure 8 As shown, the present disclosure provides a database data migration device 80, including: a mapping configuration module 81, a data synchronization module 82, a data verification module 83 and a reverse synchronization module 84. The mapping configuration module 81 configures data mapping information between a source database table and a target database table; wherein the data mapping information includes: a correspondence between data tables and a mapping relationship between data table fields. The data synchronization module 82 synchronizes the data in the source database table in the target database table based on the data mapping information when the source database and the target database provide services.
[0121] After performing data synchronization between the source database table and the target database table, the data verification module 83 verifies the data in the source database table and the target database table, determines whether the data synchronization operation is correct based on the verification result, and performs corresponding processing. After determining that the data synchronization operation is correct, the reverse synchronization module 84 synchronizes the newly added data in the target database table with the source database table based on the data mapping information.
[0122] In one embodiment, Fig. 9 As shown, the database data migration device 80 also includes a link configuration module 85 and a link generation module 86. The link configuration module 85 configures the data link configuration information of the source database and the target database. The link generation module 86 configures the source database connection pool and the target database connection pool based on the data link configuration information to store the source database link and the target database link respectively.
[0123] The data link configuration information includes information such as database type, link address, connection string, and database username and password. The link generation module 86 creates a source database connection pool and a target database connection pool, and determines the maximum number of database connections; the link generation module 86 generates a source database link and a target database link based on the data link information and the maximum number of database connections, and puts them into the source database connection pool and the target database connection pool, respectively.
[0124] In one embodiment, the data synchronization module 82 uses the source database link in the source database connection pool and the target database link in the target database link pool to synchronize the data in the source database table with the target database table based on the data mapping information.
[0125] like Fig.10 As shown, the data synchronization module 82 includes: a synchronization task starting unit 821, an information acquisition unit 822, a data query unit 823 and a data insertion unit 824. The synchronization task starting unit 821 configures the data synchronization frequency information corresponding to the source database table; the synchronization task starting unit 821 generates a first task corresponding to the source database table, and sets the execution frequency of the first task based on the data synchronization frequency information; wherein the first task is executed using a thread corresponding to the source data table. The information acquisition unit 822 acquires the target database link from the target database link pool based on the first task, and uses the acquired target database link to acquire the data information to be synchronized.
[0126] The data query unit 823 obtains the source database link from the source database connection pool based on the first task, and uses the obtained source database link and the data information to be synchronized to obtain the data to be synchronized from the source database table. The data insertion unit 824 uses the obtained target database link and the data mapping information to insert the data to be synchronized into the target database table.
[0127] In one embodiment, the information acquisition unit 822 uses the acquired target database link to query the maximum ID value corresponding to the data record in the target database table. The data query unit 823 uses the acquired source database link to obtain data with a data record ID value greater than the maximum ID value from the source database table as data to be synchronized.
[0128] The data inserting unit 824 obtains the field type conversion information between the source database and the target database, and when inserting the data to be synchronized into the target database table, performs type conversion processing on the data to be synchronized based on the field type conversion information.
[0129] In one embodiment, after inserting the data to be synchronized into the target database table, the data update module 87 generates a second task corresponding to the source database table; wherein the second task is executed using a thread corresponding to the source database table.
[0130] The data update module 87 obtains the source database link from the source database connection pool based on the second task, and uses the obtained source database link to obtain the data modified in the source database table during the data synchronization operation. The data update module 87 obtains the target database link from the target database link pool based on the second task, and uses the obtained target database link to update the data corresponding to the modified data in the target database table.
[0131] like Fig.11 As shown, the data verification module 83 includes: a verification task starting unit 831, a data encryption processing unit 832 and an encryption result verification unit 833. The verification task starting unit 831 generates a third task corresponding to the source database table; wherein the third task is executed using a thread corresponding to the source data table.
[0132] The data encryption processing unit 832 obtains a source database link from the source database connection pool based on the third task, uses the obtained source database link to query the data corresponding to the data synchronization operation stored in the source database table, converts all field values contained in the data into character types and performs concatenation processing, encrypts the concatenated character string, and obtains a first encrypted value.
[0133] The data encryption processing unit 832 obtains the target database link from the target database connection pool based on the third task, uses the obtained target database link to query the data corresponding to the data synchronization operation stored in the target database table, converts all field values contained in the data into character types and performs splicing processing, encrypts the spliced character string, and obtains a second encrypted value.
[0134] If the encryption result verification unit 833 determines that the first encryption value is the same as the corresponding second encryption value, it determines that the data synchronization is correct; if the encryption result verification unit 833 determines that the first encryption value is not the same as the corresponding second encryption value, it determines that the data synchronization is abnormal and performs corresponding exception processing.
[0135] like Fig.12 As shown, the reverse synchronization module 84 includes: a reverse task starting unit 841 and a reverse synchronization data unit 842. The reverse task starting unit 841 generates a fourth task corresponding to the target database table; wherein the fourth task is executed using a thread corresponding to the target data table.
[0136] The reverse synchronization data unit 842 obtains the target database link from the target database connection pool based on the fourth task, and uses the obtained target database link to query the target data table for the newly added data after the data synchronization operation. The reverse synchronization data unit 842 obtains the source database link from the source database connection pool based on the fourth task, and uses the obtained source database link and based on the data mapping information to synchronize the newly added data in the source data table.
[0137] In one embodiment, Fig.13 FIG. 1 is a module diagram of another embodiment of the database data migration device according to the present disclosure. Fig.13 As shown, the apparatus may include a memory 1301, a processor 1302, a communication interface 1303 and a bus 1304. The memory 1301 is used to store instructions, the processor 1302 is coupled to the memory 1301, and the processor 1302 is configured to execute the above database data migration method based on the instructions stored in the memory 1301.
[0138] The memory 1301 may be a high-speed RAM memory, a non-volatile memory, etc. The memory 1301 may also be a memory array. The memory 1301 may also be divided into blocks, and the blocks may be combined into virtual volumes according to certain rules. The processor 1302 may be a central processing unit CPU, or an application-specific integrated circuit ASIC (Application Specific Integrated Circuit), or one or more integrated circuits configured to implement the database data migration method disclosed in the present invention.
[0139] In one embodiment, the present disclosure provides a computer-readable storage medium storing computer instructions, which implement the database data migration method in any of the above embodiments when executed by a processor.
[0140] The database data migration method, device and storage medium provided in the above embodiments synchronize the data in the source database table with the target database table based on data mapping information when the source database and the target database provide services; perform verification processing on the data in the source database table and the target database table, determine whether the data synchronization operation is correct based on the verification result and perform corresponding processing; synchronize the newly added data in the target database table with the source database table based on the data mapping information; enable data migration without downtime, and solve the problem of downtime of online system data migration; adopt a bidirectional data append strategy to achieve real-time synchronization of data written to any database to another database, which is suitable for online data migration of non-interruptible services; by configuring the relationship between the mapping table fields, the differences between the database and data table field attributes can be shielded, and data migration between heterogeneous databases and heterogeneous table structures can be achieved, which can be applicable to different versions of databases of various manufacturers and has greater flexibility in use; data integrity verification can be performed asynchronously, and data synchronization and data verification can be independently operated to improve the efficiency of synchronization operations and improve user experience.
[0141] The method and system of the present disclosure may be implemented in many ways. For example, the method and system of the present disclosure may be implemented by software, hardware, firmware, or any combination of software, hardware, and firmware. The above order of steps for the method is for illustration only, and the steps of the method of the present disclosure are not limited to the order specifically described above, unless otherwise specifically stated. In addition, in some embodiments, the present disclosure may also be implemented as a program recorded in a recording medium, which includes machine-readable instructions for implementing the method according to the present disclosure. Therefore, the present disclosure also covers a recording medium storing a program for executing the method according to the present disclosure.
[0142] The description of the present disclosure is given for the purpose of illustration and description, and is not intended to be exhaustive or to limit the present disclosure to the disclosed form. Many modifications and variations will be apparent to those of ordinary skill in the art. The embodiments are selected and described in order to better illustrate the principles and practical applications of the present disclosure, and to enable those of ordinary skill in the art to understand the present disclosure and thereby design various embodiments with various modifications suitable for specific uses.
Claims
1. A database data migration method, comprising: Configure data mapping information between source database tables and target database tables; In a state where the source database and the target database provide services, synchronizing the data in the source database table in the target database table based on the data mapping information; After performing a data synchronization operation between the source database table and the target database table, verifying the data in the source database table and the target database table, determining whether the data synchronization operation is correct based on the verification result and performing corresponding processing; The data corresponding to the data synchronization operation stored in the source database table are queried, all field values contained in the data are converted into character types and concatenated, and the concatenated string is encrypted to obtain a first encrypted value; the data corresponding to the data synchronization operation stored in the target database table are queried, all field values contained in the data are converted into character types and concatenated, and the concatenated string is encrypted to obtain a second encrypted value; if the first encrypted value is the same as the corresponding second encrypted value, it is determined that the data synchronization is correct; if the first encrypted value is different from the corresponding second encrypted value, it is determined that the data synchronization is abnormal, and corresponding abnormal processing is performed; After determining that the data synchronization operation is correct, the newly added data in the target database table is synchronized with the source database table based on the data mapping information.
2. The method of claim 1, further comprising: Configure data link configuration information of the source database and the target database; A source database connection pool and a target database connection pool are respectively configured based on the data link configuration information to store source database links and target database links respectively.
3. The method of claim 2, wherein: The data link configuration information includes: database type, link address, connection string, and database user name and password; configuring the source database connection pool and the target database connection pool based on the data link configuration information includes: Creating the source database connection pool and the target database connection pool, and determining the maximum number of database connections; Based on the data link information and the maximum number of database connections, the source database link and the target database link are generated respectively, and are put into the source database connection pool and the target database connection pool respectively.
4. The method of claim 2, wherein: The data mapping information includes: a correspondence relationship between data tables and a mapping relationship between data table fields; and synchronizing the data in the source database table in the target database table based on the data mapping information includes: The data in the source database table is synchronized in the target database table based on the data mapping information by using the source database link in the source database connection pool and the target database link in the target database link pool.
5. The method according to claim 4, wherein the using the source database link in the source database connection pool and the target database link in the target database link pool to synchronize the data in the source database table into the target database table based on the data mapping information comprises: Configuring data synchronization frequency information corresponding to the source database table; Generate a first task corresponding to the source database table, and set the execution frequency of the first task based on the data synchronization frequency information; wherein the first task is executed using a thread corresponding to the source database table; Acquire a target database link from the target database link pool based on the first task, and use the acquired target database link to acquire data information to be synchronized; Acquire a source database link from the source database connection pool based on the first task, and acquire data to be synchronized from the source database table using the acquired source database link and based on the data information to be synchronized; The data to be synchronized is inserted into the target database table using the acquired target database link and based on the data mapping information.
6. The method according to claim 5, wherein the step of using the acquired target database link to acquire data information to be synchronized comprises: Use the acquired target database link to query the maximum ID value corresponding to the data record in the target database table; The step of using the acquired source database link and acquiring the data to be synchronized from the source database table based on the data information to be synchronized includes: The obtained source database link is used to obtain data whose data record ID value is greater than the maximum ID value from the source database table as the data that needs to be synchronized.
7. The method of claim 5, further comprising: Acquire field type conversion information between the source database and the target database; When the data to be synchronized is inserted into the target database table, type conversion processing is performed on the data to be synchronized based on the field type conversion information.
8. The method of claim 5, further comprising: After inserting the data to be synchronized into the target database table, generating a second task corresponding to the source database table; wherein the second task is executed using a thread corresponding to the source database table; Acquire a source database link from the source database connection pool based on the second task, and use the acquired source database link to acquire data of the source database table that is modified during the data synchronization operation; A target database link is obtained from the target database link pool based on the second task, and data corresponding to the modified data is updated in the target database table using the obtained target database link.
9. The method of claim 2, further comprising: Generate a third task corresponding to the source database table; wherein the third task is executed using a thread corresponding to the source database table; Acquire a source database link from the source database connection pool based on the third task, and use the acquired source database link to query data corresponding to the data synchronization operation stored in the source database table; A target database link is obtained from the target database connection pool based on the third task, and the obtained target database link is used to query the data corresponding to the data synchronization operation stored in the target database table.
10. The method according to claim 2, wherein synchronizing the newly added data in the target database table with the source database table based on the data mapping information comprises: Generate a fourth task corresponding to the target database table; wherein the fourth task is executed using a thread corresponding to the target database table; Based on the fourth task, a target database link is obtained from the target database connection pool, and the obtained target database link is used to query the target database table for newly added data after the data synchronization operation; A source database link is obtained from the source database connection pool based on the fourth task, and the newly added data is synchronized in the source database table using the obtained source database link and based on the data mapping information.
11. The method according to any one of claims 1 to 10, wherein: The source database and the target database are of different database types; and at least one field of the source database table and the target database table is different.
12. A database data migration device, comprising: A mapping configuration module, used to configure data mapping information between a source database table and a target database table; wherein the data mapping information includes: a corresponding relationship between data tables and a mapping relationship between data table fields; A data synchronization module, configured to synchronize the data in the source database table with the data in the target database table based on the data mapping information when the source database and the target database provide services; A data verification module, used to verify the data in the source database table and the target database table after performing a data synchronization operation between the source database table and the target database table, and determine whether the data synchronization operation is correct based on the verification result and perform corresponding processing; Wherein, the data verification module includes: a data encryption processing unit, configured to query the data corresponding to the data synchronization operation stored in the source database table, convert all field values contained in the data into character types and perform concatenation processing, encrypt the concatenated character string to obtain a first encrypted value; query the data corresponding to the data synchronization operation stored in the target database table, convert all field values contained in the data into character types and perform concatenation processing, encrypt the concatenated character string to obtain a second encrypted value; an encryption result verification unit, configured to determine that data synchronization is correct if the first encryption value is the same as the corresponding second encryption value; and to determine that data synchronization is abnormal if the first encryption value is different from the corresponding second encryption value, and perform corresponding abnormal processing; A reverse synchronization module is used to synchronize the newly added data in the target database table with the source database table based on the data mapping information after determining that the data synchronization operation is correct.
13. The apparatus of claim 12, further comprising: A link configuration module, used to configure data link configuration information of the source database and the target database; The link generation module is used to configure the source database connection pool and the target database connection pool respectively based on the data link configuration information to store the source database link and the target database link respectively.
14. A database data migration device, comprising: Memory; and a processor coupled to the memory, wherein the processor is configured to execute the method according to any one of claims 1 to 11 based on instructions stored in the memory. 15 . A computer-readable storage medium, wherein the computer-readable storage medium non-transitorily stores computer instructions, wherein the instructions are executed by a processor to perform the method according to claim 1 .
Citation Information
Patent Citations
Cross-database data synchronizing system and method
CN104317921A
A data migration method for upgrading a water company's operating charging system
CN109284293A
