Data migration method and device, computer readable storage medium and computer device

Abstract

The application relates to a data migration method and device, a computer readable storage medium and computer equipment. The method comprises the following steps: obtaining corresponding original full data in a source database; inserting the identifier of the source data table corresponding to the data according to the data position in the original full data to obtain target full data; modifying the original primary key corresponding to the data in the target full data into an updated primary key, wherein the updated primary key is determined by the original primary key corresponding to the data and the identifier of the source data table corresponding to the data; migrating the target full data to a target data table in a target database; obtaining incremental data of the source database, migrating the incremental data to the target data table, and inserting the identifier of the source data table corresponding to the data according to the data position in the incremental data. The scheme provided by the application can reduce the influence of the database migration process on normal access operations of the database.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a data migration method, apparatus, computer-readable storage medium, and computer device. Background Technology

[0002] In practical applications, it's often necessary to merge and split database data, such as in game server splitting and merging. During rapid expansion phases, excessive database pressure necessitates continuous splitting; conversely, during stable operation or decline phases, service merging is performed to reduce operating costs. Merging services requires migrating data from multiple tables in the source database and merging it into tables in another database.

[0003] In traditional techniques, migrating a source database typically requires shutting down all services accessing the database, exporting the data, manually or using tools to organize the data, and then importing it into a centralized database. When business read and write traffic is high, the service shutdown can be very long, during which time the database is essentially unavailable, unable to perform any write operations, or even read operations.

[0004] Therefore, minimizing the impact on normal database access operations during database migration has become one of the urgent technical problems to be solved. Summary of the Invention

[0005] Therefore, it is necessary to provide a data migration method, apparatus, computer-readable storage medium, and computer device to address the technical problems raised in the background art, so as to reduce the impact of the database migration process on normal database access operations.

[0006] A data migration method, comprising:

[0007] The data from each source data table in the source database are used to compose the original full data corresponding to the source database.

[0008] The target full dataset is obtained by inserting the identifier of the source data table corresponding to the data based on the data position in the original full dataset;

[0009] The original primary key corresponding to the data in the target full data is modified to the updated primary key, and the updated primary key is determined by the combination of the original primary key corresponding to the data and the identifier of the source data table corresponding to the data;

[0010] Migrate the full target data to the target data table in the target database;

[0011] obtaining incremental data of the source database relative to the original full data, migrating the incremental data into the target data table, and inserting an identifier of a source data table corresponding to data according to a data position in the incremental data.

[0012] A data migration apparatus, the apparatus comprising:

[0013] a full data obtaining module configured to obtain original full data of a source database, the original full data being composed of data of each source data table in the source database;

[0014] an inserting module configured to insert an identifier of a source data table corresponding to data according to a data position in the original full data, to obtain target full data;

[0015] a primary key updating module configured to modify an original primary key corresponding to data in the target full data into an updated primary key, the updated primary key being determined by the original primary key corresponding to the data and the identifier of the source data table corresponding to the data;

[0016] a migrating module configured to migrate the target full data into a target data table of a target database;

[0017] an incremental data obtaining module configured to obtain incremental data of the source database relative to the original full data, migrate the incremental data into the target data table, and insert an identifier of a source data table corresponding to data according to a data position in the incremental data.

[0018] A computer readable storage medium storing a computer program, the computer program being executed by a processor to cause the processor to perform the steps of the data migration method.

[0019] A computer device comprising a memory and a processor, the memory storing a computer program, the computer program being executed by the processor to cause the processor to perform the steps of the data migration method.

[0020] The data migration method, device, computer readable storage medium and computer equipment ensure that the data of the source database can be migrated to the target database without omission under the condition that service access is not stopped, and meanwhile, in the process of data migration, the identification of the source data table is inserted in the migrated data, and the original primary key of the original full data is modified, and the original primary key and the identification of the data table are set as joint primary keys, so that primary key conflict can be avoided when migration to the target database, and data can be smoothly migrated to the target database. The scheme provided in the application can smoothly migrate all data of the source database to the target database without omission, thereby reducing the influence of the database migration process on normal access operation of the database. BRIEF DESCRIPTION OF DRAWINGS

[0021] Figure 1 An application environment diagram of a data migration method in an embodiment;

[0022] Figure 2 A flowchart of a data migration method in an embodiment;

[0023] Figure 3 A flowchart of a data migration method in another embodiment;

[0024] Figure 4 A flowchart of step S312 in an embodiment;

[0025] Figure 5 A flowchart of a log stream in an embodiment;

[0026] Figure 6 A flowchart of a data migration method in another embodiment;

[0027] Figure 7 A structural block diagram of a data migration device in an embodiment;

[0028] Figure 8 A structural block diagram of an incremental data acquisition module in an embodiment;

[0029] Figure 9 A structural block diagram of a data migration device in another embodiment;

[0030] Figure 9A A structural block diagram of a log analysis module in an embodiment;

[0031] Figure 9BA structural block diagram of the log analysis module in another embodiment;

[0032] Figure 10 A structural block diagram of the computer device in an embodiment. DETAILED DESCRIPTION

[0033] For the purposes of the present application, the technical solutions and advantages thereof are more clearly apparent, the following will be further described in detail in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application, and are not intended to limit the present application.

[0034] Figure 1 An application environment diagram of the data migration method in an embodiment. Refer to Figure 1 The method is applied in a data migration system, which includes a first server 110 and a second server 120, wherein the first server 110 is a server corresponding to a source database, and the second server 120 is a server corresponding to a target database. The first server 110 and the second server 120 are connected through a network. The first server 110 and the second server 120 can be implemented by an independent server or a server cluster composed of multiple servers.

[0035] As shown in Figure 2 , in an embodiment, a data migration method is provided. The present embodiment mainly takes the method applied in the first server 110 in the above Figure 1 as an example for illustration. Refer to Figure 2 , the data migration method specifically includes the following steps:

[0036] S202, obtaining all data of each source data table in the source database to form original full data corresponding to the source database.

[0037] Among them, the source database refers to a database that needs to be migrated. The source database includes multiple source data tables. These source data tables can be obtained based on horizontal partitioning of a same data table. The structures of each source data table are the same.

[0038] Specifically, the first server obtains all data of each source data table in the source database at a specific time point and before the specific time point, and forms the original full data from the data. Among them, the specific time point can be set according to needs.

[0039] In an embodiment, the first server obtains the table creation statement corresponding to the source data table while obtaining each source data table in the source database.

[0040] In one embodiment, the specific time point can be a current time point. The first server obtains all data of each source data table in the source database at the current time point and before the current time point, and assembles the data into original full data.

[0041] In one embodiment, the first server can export data from each source data table in the source database by using an open source tool mydumper or myqldump.

[0042] In one embodiment, before assembling the data of each source data table into original full data, the first server checks the configuration information of each source data table, including IP address, port number and data table name, and checks whether the structures of each source data table are consistent.

[0043] S204, inserting an identifier of a source data table corresponding to the data according to a data position in the original full data, to obtain target full data.

[0044] The data position refers to a position of the data in the source database, and according to the position, it can be determined which source data table the data belongs to. Here, the data refers to a data record in the original full data, and one data record may, for example, be one row of data in a data table.

[0045] Specifically, after obtaining the original full data, the first server inserts an identifier of a source data table to which each data record belongs in each data record according to a data position in the original full data, to obtain target full data.

[0046] In one embodiment, the identifier of the source data table can be obtained by combining a database name and a data table name corresponding to the source data table.

[0047] S206, modifying an original primary key corresponding to the data in the target full data into an updated primary key, the updated primary key being determined by combining the original primary key corresponding to the data and an identifier of a source data table corresponding to the data.

[0048] The original primary key refers to a primary key of the data in the source database. The updated primary key is determined by combining the original primary key corresponding to the data and an identifier of a source data table corresponding to the data.

[0049] Specifically, the first server sets the original primary key field and the identifier field of the source data table in the target full data as a joint primary key, thereby re-determining the primary key field corresponding to the target full data.

[0050] In one embodiment, when exporting the original full data, the first server also exports a table creation statement corresponding to the source database, and the first server can modify the original primary key in the exported table creation statement into the updated primary key, thereby implementing the modification of the primary key of the target full data.

[0051] S208, migrating the target full data to a target data table of the target database.

[0052] Specifically, the first server can send the target full data to the second server, and the second server imports the target full data into the target data table.

[0053] S210, obtaining incremental data of the source database relative to the original full data, migrating the incremental data to the target data table, and inserting an identifier of a source data table corresponding to the data according to a data position in the incremental data.

[0054] The incremental data is relative to the original full data, that is, data corresponding to a change operation on the source data after a specific time point corresponding to the original full data. The data position in the incremental data refers to the position of the data in the incremental data in the source database, according to which it can be determined which source data table the data in the incremental data belongs to.

[0055] Specifically, after obtaining the incremental data of each source data table, the first server can determine the identifier of the source data table corresponding to each data record in the incremental data according to the data position in the incremental data, insert the identifier into the data record, and migrate the incremental data to the target data table of the second server.

[0056] In an embodiment, the first server can obtain a log file corresponding to the source database, which records all data change operations of the source data, and according to the log file, the incremental data can be determined and migrated to the target data table. The log file can be a transaction log.

[0057] In an embodiment, when the difference between the source database and the target database does not exceed a preset threshold, the data of the source database and the target database is kept synchronized.

[0058] The difference can be a data size difference or a time difference. The data size difference refers to the difference between the data size corresponding to the source database and the data size corresponding to the target database; the time difference refers to the difference between the time corresponding to the latest change operation of the source database and the time corresponding to the latest change operation of the target database.

[0059] Specifically, since the access service of the source database is not stopped, there are continuous data write operations in the source database, therefore, the first server continuously obtains the incremental data of the source database, and each time the incremental data is migrated to the target database, it is judged whether the difference between the source data and the target database exceeds the preset threshold, if not, it means that the data in the source database and the target database is basically consistent at this time, then it is in the synchronization stage, the first server keeps the data of the source database and the target database synchronized, if yes, it continues to obtain the incremental data of the source database until the difference does not exceed the preset threshold.

[0060] The above data migration method, when migrating the data of the source database, first obtains the original full data, migrates the original full data, then obtains the incremental data relative to the original full data, and migrates the incremental data, so that the data of the source database can be migrated to the target database without missing under the condition that the service access is not stopped, and in the process of data migration, by inserting the identifier of the source data table in the migrated data and modifying the original primary key of the original full data, the original primary key and the identifier of the data table are set as the joint primary key, the primary key conflict can be avoided when migrating to the target database, so that the data can be smoothly migrated to the target database. The scheme provided in the application can smoothly migrate all data of the source database to the target database without missing, thereby reducing the influence of the database migration process on the normal access operation of the database.

[0061] In one embodiment, obtaining the incremental data of the source database relative to the original full data and migrating the incremental data to the target data table comprises: obtaining a transaction log corresponding to the source database; parsing the transaction log to obtain a change statement corresponding to the source database according to the parsing result, and replaying the change statement to the target data table.

[0062] Wherein, the transaction log refers to a binglog log, the binglog log is a binary log of the database, the transaction log records all data changes of the database and is stored in binary form on the disk. Replay refers to executing the operation on the source database again in the target database.

[0063] In this embodiment, after the first server obtains the transaction log corresponding to each source data table, the transaction log is parsed, and the change statement corresponding to the source database can be obtained according to the parsing result.

[0064] The change statement refers to a statement of a data change operation performed on the source database after a specific time point corresponding to the original full data, and includes at least one of a DDL (data definition language) statement and a DML (data manipulation language) statement. The DDL statement is used to define or change the structure of a data table, a data type, a link between data tables, and a constraint, and includes operations such as CREATE, ALTER, and DROP. The DML statement is used to operate on data of a database, and includes operations such as SELECT, UPDATE, INSERT, and DELETE.

[0065] The first server replays the change statement to the target database after obtaining the change statement. Specifically, the change statement can be sent to the second server, and the second server performs a corresponding change operation on the target data according to the received change statement.

[0066] In an embodiment, the transaction log can be parsed through "--base64-output=decode-rows–v", where base64-output can control the output statement to output a BINLOG statement encoded in base64; decode-rows: decode a row-based event into a SQL statement.

[0067] In this embodiment, the migration of the incremental data is implemented by obtaining the transaction log, parsing the transaction log to obtain the change statement, and replaying the change statement to the target database, which can improve the migration efficiency of the incremental data.

[0068] In an embodiment, as shown in Figure 3 a data migration method is provided, including:

[0069] S302, obtaining data of each source data table in the source database to form original full data corresponding to the source database, and recording a target position corresponding to the original full data.

[0070] The target position refers to a position of the transaction log corresponding to the original full data. The position, that is, the position point of the transaction log, can be composed of a file name and a corresponding position offset of the transaction log, for example, mysql-bin.001, 154.

[0071] S304, inserting an identifier of a source data table corresponding to data in the original full data according to a data position to obtain target full data.

[0072] S306, modify the original primary key corresponding to the data in the target full data to an update primary key, and the update primary key is determined by the original primary key corresponding to the data and the identifier of the source data table corresponding to the data.

[0073] S308, migrate the target full data to the target data table of the target database.

[0074] S310, obtain the transaction log corresponding to the source database.

[0075] S312, start parsing the transaction log from the target site, obtain the change statement corresponding to the source database according to the parsing result, replay the change statement to the target data table, and insert the identifier of the source data table corresponding to the data according to the data position of the incremental data corresponding to the change statement.

[0076] Specifically, since the target site corresponding to the original full data is recorded, the first server can accurately position to the corresponding log position in the transaction log according to the file name and offset of the target site after obtaining the transaction log. Since the log data starting from the target site is the log data corresponding to the incremental data, the parsing result obtained by starting parsing from the target site can be directly determined as the change statement corresponding to each source data table.

[0077] In one embodiment, the first server replays the change statement corresponding to each transaction to the target data table of the target database after parsing the log data corresponding to the transaction, realizes the migration of the incremental data corresponding to the transaction, and judges whether the difference between the source database and the target database at this time exceeds a preset threshold. If not, it means that the source database and the target database are basically consistent, and the data synchronization between the source database and the target database is maintained. Otherwise, the first server continues to parse the log corresponding to the next transaction until the difference between the source database and the target database does not exceed the preset threshold.

[0078] In this embodiment, by recording the target site corresponding to the original full data, the target site can be directly parsed when parsing the transaction log, avoiding parsing all transaction logs, and improving the parsing efficiency.

[0079] In one embodiment, the method further comprises: when the difference between the source database and the target database does not exceed a preset threshold, detecting the update state of the transaction log; when detecting that the transaction log is updated, parsing the current update log data corresponding to the transaction log to obtain the current update change statement; and replaying the current update change statement to the target data table.

[0080] The current update log data refers to log data currently updated in the transaction log after the difference between the source database and the target database does not exceed a preset threshold. The current update change statement refers to a change statement obtained by parsing the transaction log according to the current update.

[0081] In this embodiment, the first server can detect the update state of the transaction log in real time. When it is detected that there is an update, the current update log data is obtained for parsing, the current update change statement is obtained, the current update change statement is replayed to the target data table, and thus the data synchronization between the source database and the target database is realized.

[0082] In this embodiment, the first server can detect the update state of the transaction log in real time. When it is detected that there is an update, the current update log data is obtained for parsing, the current update change statement is obtained, the current update change statement is replayed to the target data table, and thus the data synchronization between the source database and the target database is realized.

[0083] In one embodiment, after the current update change statement is replayed to the target data table, the method further includes: when the write operation of each source data table in the source database is stopped and the replay of the current update change statement in the target data table is completed, obtaining an access address of the target database; and switching the current service access address from the access address corresponding to the source database to the access address of the target database.

[0084] The current service access address refers to the address of the user when accessing the current service. The current service here refers to the service provided by the source data.

[0085] In this embodiment, the first server can first stop the write operation of each source data table in the source database, replay the current update change statement in the target data table, obtain the access address of the target database when the replay is completed, and switch the current service access address from the access address corresponding to the source database to the access address corresponding to the target database, so as to realize the switching of the service access.

[0086] In this embodiment, since the data in the source database and the target database is always kept synchronized, only a short service stop is needed when switching the service access, compared with the long service pause in the traditional technology, and thus the influence on the service access is greatly reduced.

[0087] In one embodiment, the parsing the transaction log from the target site, obtaining the change statement corresponding to the source database according to the parsing result, and replaying the change statement into the target data table comprises: parsing the transaction log from the target site, determining the first source data table as the current source data table when a structure change statement corresponding to the first source data table is parsed, recording the structure change statement corresponding to the current source data table, continuing to parse the transaction log, and replaying into the target data table when a first data change statement is parsed from the parsed change statement; the first data change statement is a data change statement corresponding to a source data table after the current source data table; when a structure change statement corresponding to a next source data table is parsed from the parsed data change statement, the next source data table is determined as the current source data table, and the step of recording the structure change statement corresponding to the current source data table and the corresponding site is entered until the current source data table is the last source data table; and replaying the recorded structure change statement into the target data table.

[0088] In one embodiment, as shown in the figure, the transaction log is parsed from the target site, the change statement corresponding to the source database is obtained according to the parsing result, and the change statement is replayed into the target data table, which comprises: Figure 4

[0089] S402, the transaction log is parsed from the target site, and the first source data table is determined as the current source data table when a structure change statement corresponding to the first source data table is parsed.

[0090] Since each source data table in the source database is obtained by horizontal table splitting, each source data table can be sorted from top to bottom according to the position relationship, and the first source data table is arranged first, the second source data table is arranged second, and so on. The structure change statement corresponding to the first source data table refers to the DDL statement for changing the structure of the first source data table. It can be understood that since the plurality of source data tables in the source database are data tables with the same structure, after the structure of the first source data table is changed, the structures of the other source data tables will inevitably be changed, only the time points of the changes are different.

[0091] Specifically, the changes are made according to the position sorting order of each source data table, that is, the first source data table is changed first, the second source data table is changed second, the third source data table is changed third, and so on, until the last source data table. The last source data table here refers to the last source data table, that is, the source data table at the bottom of the source database.

[0092] S404, the structure change statement corresponding to the current source data table and the corresponding site are recorded.

[0093] ​S406, determining whether the current source data table is the last source data table, if not, entering step S408; if yes, entering step S416.

[0094] S408, continuing to parse the transaction log.

[0095] Since the number of source data tables in the source database is known, the number of structure change statements that can be finally parsed is also known, and the first server can determine whether the number of structure change statements currently received is the same as the number of source data tables, if yes, it means that the current source data table is the last source data table, otherwise, the current source database is not the last source data table, then starting from the position corresponding to the current source data table, the transaction log is continued to be parsed.

[0096] S410, when the parsed change statement includes a first data change statement, replaying to the target data table, and repeating S408 to continue parsing the transaction log. The first data change statement is a data change statement corresponding to a source data table after the current source data table.

[0097] S412, when the parsed change statement includes a second data change statement, discarding the second data change statement, and repeating S408 to continue parsing the transaction log. The second data change statement is a data change statement corresponding to a source data table before or at the current source data table.

[0098] Among them, the source data table after the current source data table refers to the data table below the current source data table. Since the structure change of each source data table is performed in order according to the position, when the structure change statement corresponding to the current source data table is parsed, it means that the current source data table and the source data table before the current source data table have been structurally changed, while the source data table after the current source data table has not been structurally changed. Since the structure change statements parsed before only record saving is performed and are not replayed to the target database, that is, the structure of the target database at this time is also not changed. Therefore, when the parsed change statement includes the first data change statement, that is, the data change statement corresponding to the source data table after the current source data table, the first server can normally replay to the target database; and when the parsed change statement includes the second data change statement, that is, the data change statement corresponding to the source data table before or at the current source data table, since the current source data table or the source data table before the current source data table has been structurally changed, the second data change statement cannot be replayed to the target database, therefore, the second data change statement is discarded.

[0099] S414, when the parsed data change statement includes a structure change statement corresponding to a next source data table, determining the next source data table as the current source data table, and entering step S404.

[0100] The next source data table is the first source data table that is ordered after the current source data table according to the position relationship. For example, if the current source data table is the first source data table, the next source data table is the second source data table, and so on.

[0101] When the parsed data change statement includes the structure change statement corresponding to the next source data table, the next source data table is determined as the current source data table, and step S404 is entered.

[0102] S416, replay the recorded structure change statement to the target data table.

[0103] In an embodiment, since the structures of the source data tables are the same, the structure change statements for changing the structures of the source data are also the same. The first server can select one of all the recorded structure change statements to replay to the target database to change the structure of the target database.

[0104] S418, reparse the transaction log from the position corresponding to the first source data table to the position corresponding to the last source data table.

[0105] Specifically, since in the previous parsing process, from the parsing of a structure change statement to the last structure change statement, part of the data change statements are discarded because they cannot be replayed to the target database. Therefore, the first server starts to reparse from the position corresponding to the first structure change statement to the position corresponding to the last structure change statement, and replays all the third data change statements parsed in this process to the target database. The third data change statements are the same as the second data change statements discarded before. At present, the structure change statements recorded before are parsed again in this process. Since the structure change statements have been recorded, they can be discarded in this round of parsing process.

[0106] S420, continue to parse the transaction log, and replay the parsed data change statement to the target database.

[0107] Specifically, since all the structure change statements have been parsed and replayed to the target database, the structure of the target database has also been changed. At this time, the target database can start to normally parse the transaction log from the position corresponding to the current source data table, and replay the parsed data change statement to the target database.

[0108] It can be understood that if the structure change statement corresponding to the first source data table is parsed again in the subsequent parsing process, the above steps are repeated.

[0109] In the embodiment, when the source data table in the source database is changed in structure during the data migration process, the transaction log is parsed twice through the above steps, so that all the change statements corresponding to the source database can be successfully replayed to the target database, and errors in the replay process are avoided.

[0110] To make the log parsing process clearer, the following will be illustrated by taking a binlog stream as an example. Figure 5 In the embodiment, the source database MySQL Instance includes two source data tables table1 and table2, wherein the first server parses the DDL statement of table1 at t1, and parses the DDL statement of table2 at t3. The whole parsing process includes the following steps.

[0111] 1. The DDL statement of table1 is parsed at t1, and the DDL statement and the corresponding binlog position are recorded.

[0112] At this time, table1 is the current source data table. The first server determines that the current source data table is not the last source data table according to the number of source data tables in the source database, and thus step 2 is entered.

[0113] 2. The binlog from t2 to t3 is continuously parsed.

[0114] 3. The schema V2 DML statement belonging to table1 is discarded, and the schema V1 DML statement belonging to table2 is replayed to the target database. Schema V2 indicates that table1 is changed in structure, and the schema V2 DML statement is the DML statement after the structure is changed.

[0115] 4. The parsing is continuously performed, and the DDL statement of table2 is parsed at t3, and the DDL statement and the corresponding binlog position are recorded.

[0116] At this time, table2 is the current source data table. The first server determines that all the DDL statements on the source database have been parsed according to the number of source data tables in the source database, i.e., table2 is the last source data table.

[0117] 5. The recorded DDL statement is replayed to the target database.

[0118] 6. The parsing start position of the new binlog stream is set to the position saved in step 1.

[0119] 7. The binlog from t2 to t3 is parsed again.

[0120] 8. For schema V2 DML statements belonging to table1, replay them normally to the target database; for schema V1 DML statements belonging to table2, discard them.

[0121] 9. By analyzing the binlog position saved at step 4, it can be seen that all DML statements discarded in step 3 have been replayed to the target database;

[0122] 10. Continue parsing the binlog site corresponding to time t4 normally.

[0123] Since all the structure change statements have been replayed to the target database, the target database has undergone structure changes, and the data change statements parsed subsequently are all data change statements after the structure changes, they can be directly replayed to the target database.

[0124] In one embodiment, when the parsed change statement includes a second data change statement, the second change statement can be cached. After the recorded structure change statement is replayed to the target data table, the cached second change statement can be retrieved and replayed into the target data. Because the second change statement is cached, secondary parsing of the transaction log can be avoided.

[0125] In one embodiment, such as Figure 6 As shown, a data migration method is provided, including the following steps:

[0126] S602, obtain the data from each source data table in the source database to form the original full data corresponding to the source database, and record the target position corresponding to the original full data.

[0127] S604: Insert the identifier of the source data table corresponding to the data based on the data position in the original full data to obtain the target full data.

[0128] S606, modify the original primary key corresponding to the data in the target full data to the updated primary key. The updated primary key is determined by the combination of the original primary key corresponding to the data and the identifier of the source data table corresponding to the data.

[0129] S608 migrates the full target data to the target data table in the target database.

[0130] S610, retrieve the transaction log corresponding to the source database.

[0131] S612, starting from the target site, parsing the transaction log, obtaining the change statement corresponding to the source database according to the parsing result, replaying the change statement to the target data table, and inserting the identifier of the source data table corresponding to the data according to the data position of the incremental data corresponding to the change statement.

[0132] S614, when the difference between the source database and the target database does not exceed the preset threshold, detecting the update state of the transaction log.

[0133] S616, when the transaction log is detected to be updated, parsing the current update log data corresponding to the transaction log to obtain the current update change statement.

[0134] S618, replaying the current update change statement to the target data table.

[0135] S620, when the write operation of each source data table in the source database is stopped and the current update change statement is replayed in the target data table, obtaining the access address of the target database.

[0136] S622, switching the current service access address from the access address corresponding to the source database to the access address of the target database.

[0137] In the embodiment, the data of multiple source data tables in the source database can be migrated to the target database and merged into the same data table without stopping the service access, and the service can be switched to the target database instantaneously after the data migration, reducing the impact on normal access operations of the database during the database migration.

[0138] It can be understood that the data migration method of the application can also be executed on the second server, and when executed on the second server, the steps are the same as when executed on the first server, which will not be described herein.

[0139] It should be understood that, although Figure 2-4 and Figure 6 the flowcharts in each step are displayed in sequence according to the arrow, these steps are not necessarily executed in sequence according to the arrow. Unless otherwise stated herein, the execution of these steps has no strict order limitation, and these steps can be executed in other order. Moreover, Figure 2-4 and Figure 6 at least part of the steps in can include multiple sub-steps or multiple stages, which are not necessarily executed at the same time, but can be executed at different times, and the execution order of these sub-steps or stages is not necessarily sequential, but can be alternately executed with other steps or sub-steps or stages of other steps.

[0140] In one embodiment, as shown in Figure 7 A data migration apparatus 700 is provided, comprising:

[0141] A full data acquisition module 702 is configured to acquire data of each source data table in the source database to form original full data corresponding to the source database;

[0142] An insertion module 704 is configured to insert an identifier of the source data table corresponding to the data according to a data position in the original full data to obtain target full data;

[0143] A primary key updating module 706 is configured to modify an original primary key corresponding to the data in the target full data into an updated primary key, the updated primary key being determined by the original primary key corresponding to the data and the identifier of the source data table corresponding to the data;

[0144] A migration module 708 is configured to migrate the target full data to a target data table in the target database;

[0145] An incremental data acquisition module 710 is configured to acquire incremental data of the source database relative to the original full data, migrate the incremental data to the target data table, and insert an identifier of the source data table corresponding to the data according to a data position in the incremental data.

[0146] The above data migration apparatus, when migrating data of the source database, first acquires original full data, migrates the original full data, then acquires incremental data relative to the original full data, and migrates the incremental data, thereby ensuring that the data of the source database can be migrated to the target database without omission under the condition that service access is not stopped, and meanwhile, in the process of migrating the data, the identifier of the source data table is inserted in the migrated data, and the original primary key of the original full data is modified, and the original primary key and the identifier of the data table are set as a joint primary key, thereby avoiding primary key conflict when migrating to the target database, and smoothly migrating the data to the target database. The scheme provided in the present application can smoothly migrate all data of the source database to the target database without omission in the entire migration process without stopping service access of the source data, thereby reducing the influence of the database migration process on normal access operation of the database.

[0147] In one embodiment, as shown in Figure 8 The incremental data acquisition module 710 comprises:

[0148] A log acquisition module 710A is configured to acquire a transaction log corresponding to the source database;

[0149] A log analysis module 710B is configured to analyze the transaction log, obtain a change statement corresponding to the source database according to an analysis result, and replay the change statement to the target data table.

[0150] In one embodiment, as shown in Figure 9 the device further comprises a target site recording module 712 configured to record a target site corresponding to the original full data; the log analysis module 710B is further configured to analyze the transaction log from the target site, to obtain a change statement corresponding to the source database according to the analysis result, and to replay the change statement into the target data table.

[0151] In one embodiment, the device further comprises a data synchronization module configured to, when detecting an update of the transaction log, analyze current update log data corresponding to the transaction log to obtain a current update change statement, and to replay the current update change statement into the target data table.

[0152] In one embodiment, the device further comprises a switching module configured to, when the write operation of each source data table in the source database is stopped and the replay of the current update change statement into the target data table is completed, obtain an access address of the target database; and to switch the current service access address from the access address corresponding to the source database to the access address of the target database.

[0153] In one embodiment, as shown in Figure 9A the log analysis module 710B comprises:

[0154] a first analysis module 902 configured to analyze the transaction log from the target site, and to determine a first source data table as a current source data table when a structure change statement corresponding to the first source data table is obtained;

[0155] a recording module 904 configured to record the structure change statement corresponding to the current source data table;

[0156] a second analysis module 906 configured to continue analyzing the transaction log;

[0157] a first replay module 908 configured to, when the obtained change statement includes a first data change statement, replay into the target data table; the first data change statement is a data change statement corresponding to a source data table after the current source data table;

[0158] a loop module 910 configured to, when the obtained data change statement includes a structure change statement corresponding to a next source data table, determine the next source data table as the current source data table, and enter the second analysis module, until the current source data table is a last source data table;

[0159] a second replay module 912 configured to replay the recorded structure change statement into the target data table.

[0160] In one embodiment, the recording module 904 is used to record the structure change statements and corresponding positions corresponding to the current source data table; such as Figure 9B As shown, the log parsing module 710B further includes: a discarding module 914, used to discard the second data change statement when the parsed change statement includes a second data change statement; the second data change statement is a data change statement corresponding to the current source data table or a source data table preceding the current source data table; a third parsing module 916, used to re-parse the transaction log starting from the position corresponding to the first source data table until the position corresponding to the last source data table, and when the parsed data change statement includes a third data change statement, to replay the third data change statement to the target data table; the third data change statement is the same as the discarded second data change statement.

[0161] Figure 10 An internal structural diagram of a computer device in one embodiment is shown. Specifically, this computer device may be... Figure 1 The first server is 110, and the second server is 120. For example... Figure 10 As shown, the computer device includes a processor, memory, network interface, input device, and display screen connected via a system bus. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system and may also store computer programs. When executed by the processor, these computer programs enable the processor to perform data migration methods. The internal memory may also store computer programs, which, when executed by the processor, enable the processor to perform data migration methods. The display screen can be an LCD screen or an e-ink screen. The input device can be a touch layer covering the display screen, buttons, a trackball, or a touchpad mounted on the computer device's casing, or an external keyboard, touchpad, or mouse.

[0162] Those skilled in the art will understand that Figure 10 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0163] In one embodiment, the data migration apparatus provided in this application can be implemented as a computer program, and the computer program can be implemented as follows: Figure 10 It runs on the computer device shown. The computer device's memory can store the various program modules that make up the data migration device, for example, Figure 7The full data acquisition module, the insertion module, the primary key update module, the migration module and the incremental data acquisition module are shown. The computer program composed of various program modules enables the processor to execute the steps in the data migration method of various embodiments of the present application described in the specification.

[0164] For example, Figure 10 The computer device shown can execute step S202 through the full data acquisition module in the data migration apparatus as shown. Figure 7 The computer device can execute step S204 through the insertion module. The computer device can execute step S206 through the primary key update module. The computer device can execute step S208 through the migration module. The computer device can execute step S210 through the incremental data acquisition module.

[0165] In one embodiment, a computer device is provided, comprising a memory and a processor, the memory storing a computer program, the computer program being executed by the processor to enable the processor to execute the steps of the data migration method described above. The steps of the data migration method can be the steps in the data migration method of various embodiments described above.

[0166] In one embodiment, a computer readable storage medium is provided, storing a computer program, the computer program being executed by the processor to enable the processor to execute the steps of the data migration method described above. The steps of the data migration method can be the steps in the data migration method of various embodiments described above.

[0167] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program. The program can be stored in a non-volatile computer readable storage medium, and when the program is executed, the processes of the above-mentioned embodiment methods can be included. Any reference to memory, storage, database, or other medium used in the embodiments provided in the present application can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. As an illustration but not limitation, RAM is available in various forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDR SDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), etc.

[0168] Any combination of the technical features of the above embodiments can be made. In order to make the description simple, all possible combinations of the technical features in the above embodiments are not described, however, as long as the combination of the technical features does not exist, it should be considered as the scope of the present application.

[0169] The above embodiments only express several implementation manners of the present application, and the description is more specific and detailed, but it should not be understood as a limitation on the scope of the patent of the present application. It should be pointed out that for ordinary skilled in the art, without departing from the concept of the present application, a number of modifications and improvements can be made, which are all within the protection scope of the present application. Therefore, the protection scope of the patent of the present application should be subject to the appended claims.

Claims

1. A data migration method, comprising: obtaining original full data corresponding to a source database by acquiring data compositions of a plurality of source data tables in the source database without stopping access services of the source database, and recording target sites corresponding to the original full data, wherein structures of the source data tables are the same; obtaining target full data by inserting, according to data positions in the original full data, identifications of source data tables corresponding to the data; modifying table creation statements corresponding to the source database to modify original primary keys in the table creation statements into updated primary keys to modify primary keys of the target full data, wherein the updated primary keys are obtained by combining the original primary keys corresponding to the data and the identifications of the source data tables corresponding to the data; migrating the target full data to target data tables in a target database; parsing transaction logs corresponding to the source database from the target sites, determining a first source data table as a current source data table when a structure change statement corresponding to the first source data table is parsed according to a position relationship of the source data tables; recording structure change statements corresponding to the current source data table and corresponding sites; replaying to the target data tables when a first data change statement is included in parsed change statements; the first data change statement is a data change statement corresponding to a source data table after the current source data table; discarding a second data change statement when the second data change statement is included in the parsed change statements; the second data change statement is a data change statement corresponding to a source data table before or at the current source data table; determining a next source data table as the current source data table according to the position relationship of the source data tables when a structure change statement corresponding to the next source data table is included in parsed data change statements, and entering the step of recording the structure change statements corresponding to the current source data table and the corresponding sites until the current source data table is a last source data table; replaying the recorded structure change statements to the target data tables to migrate incremental data of the source database relative to the original full data to the target data tables, and inserting, according to data positions in the incremental data, identifications of source data tables corresponding to the data; re-parsing the transaction logs from a site corresponding to the first source data table to a site corresponding to the last source data table; replaying a third data change statement to the target data tables when the third data change statement is included in parsed data change statements; the third data change statement is a data change statement identical to the discarded second data change statement; replaying a current update change statement obtained by parsing current update log data of the transaction logs to the target data tables when a difference between the source database and the target database does not exceed a preset threshold; stopping write operations of the plurality of source data tables in the source database when the current update change statement is completed in the target data tables, and switching a current service access address from an access address corresponding to the source database to an access address of the target database.

2. The method of claim 1, wherein, The current update change statement obtained by analyzing the current update log data of the transaction log is replayed to the target data table when the difference between the source database and the target database does not exceed a preset threshold, including: When the difference between the source database and the target database does not exceed a preset threshold, detecting an update state of a transaction log; When the transaction log update is detected, analyzing current update log data corresponding to the transaction log to obtain a current update change statement; The current update change statement is replayed to the target data table.

3. The method of claim 1, wherein, The first data change statement is a DDL statement.

4. The method of claim 1, wherein, The second data change statement is a DML statement.

5. A data migration apparatus, characterized by comprising: The device comprises: A full data acquisition module configured to acquire original full data corresponding to a source database by acquiring data of a plurality of source data tables in the source database without stopping access services of the source database, and record target sites corresponding to the original full data; the structures of the source data tables are all the same; An insertion module configured to insert an identifier of a source data table corresponding to data according to a data position in the original full data to obtain target full data; A primary key update module configured to modify a table creation statement corresponding to the source database, modify an original primary key in the table creation statement into an updated primary key, and perform primary key modification on the target full data; the updated primary key is obtained by combining the original primary key corresponding to the data and the identifier of the source data table corresponding to the data; A migration module configured to migrate the target full data to target data tables in a target database. The log analysis module is configured to parse transaction logs corresponding to the source database from a target site, determine a first source data table as a current source data table when a structure change statement corresponding to the first source data table sorted according to a position relationship is obtained, record the structure change statement corresponding to the current source data table and a corresponding site, replay the target data table when a first data change statement is included in the obtained change statement, the first data change statement being a data change statement corresponding to a source data table after the current source data table, discard a second data change statement when the second data change statement is included in the obtained change statement, the second data change statement being a data change statement corresponding to the current source data table or a source data table before the current source data table, determine a next source data table as the current source data table when a structure change statement corresponding to the next source data table is included in the obtained data change statement, and perform the step of recording the structure change statement corresponding to the current source data table and the corresponding site until the current source data table is a last source data table, replay the recorded structure change statement to the target data table to migrate incremental data of the source database relative to the original full data to the target data table, and insert an identifier of a source data table corresponding to data according to a data position in the incremental data, reparse the transaction logs from the site corresponding to the first source data table to a site corresponding to the last source data table, and replay a third data change statement to the target data table when the third data change statement is included in the obtained data change statement, the third data change statement being the same data change statement as the discarded second data change statement. The data synchronization module is configured to replay a current update change statement obtained by parsing current update log data of the transaction logs to the target data table when a difference between the source database and the target database does not exceed a preset threshold. The switching module is configured to stop write operations of a plurality of source data tables in the source database when the current update change statement is replayed in the target data table, and switch a current service access address from an access address corresponding to the source database to an access address of the target database.

6. The data migration apparatus of claim 5, wherein, The data synchronization module is further configured to detect an update state of the transaction logs when a difference between the source database and the target database does not exceed a preset threshold, parse current update log data corresponding to the transaction logs to obtain a current update change statement when the transaction logs are detected to be updated, and replay the current update change statement to the target data table.

7. The data migration apparatus of claim 5, wherein, The first data change statement is a DDL statement.

8. The data migration apparatus of claim 5, wherein, The second data change statement is a DML statement.

9. A computer readable storage medium storing a computer program, characterized in that, The computer program is executed by the processor to enable the processor to perform the steps of the method of any one of claims 1 to 4. 10.A computer device, comprising a memory and a processor, wherein the memory stores a computer program, and the computer device is configured to perform the method according to any one of claims 1-9. The computer program is executed by the processor to enable the processor to perform the steps of the method of any one of claims 1 to 4.

11. A computer program product comprising computer instructions, characterized in that, The computer instructions, when executed by a processor, implement the method of any one of claims 1 to 4.

Images