A method for live migration of a database

Abstract

The application relates to the technical field of database migration, and discloses a method for migrating a database without stopping, and the specific steps comprise the following steps: S40, establishing data bidirectional synchronization of new and old databases; S41, detecting the data synchronization speed and quality of the new and old databases, if the detection is passed, the next step is performed, and if the detection is not passed, the migration is stopped, and the operation is ended. The method for migrating a database without stopping, through which data migration is performed, strictly follows an industry database master-slave synchronization scheme in data synchronization, the correctness and integrity of the data are guaranteed, bidirectional synchronization also provides a convenient channel for subsequent rollback; with the aid of a MySQL Binlog characteristic, a high-efficiency method for inquiring data synchronization progress is derived, millisecond-level switching of a new database is supported, the influence on the running business is very small or even zero, and the user is not aware, so that data can be migrated without stopping.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database migration, in particular to a method for migrating a database without stopping. BACKGROUND

[0002] When the business continues to develop, the data volume and concurrency reach a certain degree, the database may face performance, scalability, availability and other problems, thereby deriving migration requirements such as business splitting, database reselection, cloud migration, and hardware device maintenance to support the development of the business to a higher level.

[0003] Database migration is a big task, and sometimes migration is no less difficult than developing a new system. In fact, it can be said to be more difficult. Data is the core of a system and has the most value. Database migration should ensure the correctness and integrity of the data. In the Internet industry, the access volume of many systems is very high, and there is a certain access volume even at two or three o'clock in the morning. Database migration should ensure that it does not affect user experience. If there are performance and stability problems after migration, database migration should ensure that it can be quickly rolled back.

[0004] In the existing database migration technology, application downtime maintenance is usually faced, the scheme technology is backward and inefficient, which causes very bad user experience for users and certain economic losses for enterprises. Moreover, when problems occur, rollback also faces downtime maintenance. Some technical teams take a double-write scheme, but the cost is high. Due to the lack of technical design problems such as reserving scalability, the implementation is difficult, a lot of manpower and financial resources are needed to restructure the existing business, the implementation complexity is high and the cycle is long, which easily leads to a series of problems such as instability of the existing business.

[0005] Therefore, a method for migrating a database without stopping is proposed to solve the above problems. SUMMARY

[0006] (I) Technical problems solved

[0007] In view of the deficiencies of the prior art, the present application provides a method for migrating a database without stopping, which is a migration scheme that can be implemented from application modification to data synchronization and then to database traffic switching. The database migration capability can be obtained at a low cost, which helps the business to further develop and solves the problem of poor use effect of the existing database migration technology.

[0008] (II) Technical solutions

[0009] The technical solutions of the present application for solving the above technical problems are as follows: a method for migrating a database without stopping, the specific steps comprising:

[0010] S40, establishing data bidirectional synchronization of the new and old databases;

[0011] S41, detect the data synchronization speed and quality of the old and new databases, if passing the detection, proceed to the next step, if not passing the detection, stop the migration, and end the operation;

[0012] S42, submit the SQL stop-write rule of the old data source to the distributed configuration center, and the ORM layer stop-write component is responsible for parsing;

[0013] S43, judge whether the old library transaction in a certain time is added, if not added, proceed to the next step, if added, rollback the SQL stop-write rule, then stop the migration, and end the operation;

[0014] S44, insert a data data2 into the migration auxiliary table of the old library, and then query the inserted data data2 from the migration auxiliary table of the new library, if the data data2 is queried, proceed to the next step, if not queried, rollback the SQL stop-write rule, then stop the migration, and end the operation.

[0015] S45, submit the data source re-routing rule to the distributed configuration center, and the data source re-routing component is responsible for parsing, after the rule takes effect, the SQL of the migration table is executed by the new data source, and the operation of migrating the old library to the new library is completed.

[0016] The beneficial effects of the application are that: through the method for data migration, the data synchronization strictly follows the industry database master-slave synchronization scheme, the correctness and integrity of the data are guaranteed, and the bidirectional synchronization also provides a convenient channel for subsequent rollback; with the help of the MySQL Binlog feature, a high-efficiency method for querying the data synchronization progress is derived, the application engineering supports the millisecond-level switching of the new database, the influence on the running business is very small or even none, and the user is not aware, so that the data is migrated without shutdown.

[0017] On the basis of the above technical scheme, the application can also be improved as follows.

[0018] Further, it further comprises:

[0019] S46, judge whether the data migration process is stable, if stable, proceed to the next step, if not stable, rollback, and end the operation;

[0020] S47, determine that the new database runs stably after migration, and recycle the related resources involved in the above, and end the operation.

[0021] Further, the specific process of establishing the bidirectional synchronization of the data of the new and old databases in the step S40 is as follows:

[0022] The data synchronization tool is used to establish the bidirectional synchronization of the data of the new and old databases;

[0023] Among them, only one database executes the add, delete and modify SQL at the same time.

[0024] Further, the step S41 of detecting the data synchronization speed and quality of the old and new databases is as follows:

[0025] The JDBC tool is used to create the connection between the old and new databases, insert a data data1 into the migration auxiliary table of the old database, and then query the inserted data data1 from the migration auxiliary table of the new database to obtain the time length and compare it with the preset time length;

[0026] If the time length is lower than the preset time length, the next step is performed;

[0027] If the time length is higher than the preset time length, the migration is stopped, and the operation is ended.

[0028] Further, the ORM layer stop-write component responsible for the analysis in the step S42 is responsible for analyzing only the old data source, and after the analysis, no write transaction is submitted to the database.

[0029] Further, the specific process of the ORM layer stop-write component responsible for the analysis in the step S42 is as follows:

[0030] S10, real-time acquisition of the stop-write rule configuration of the distributed configuration center and analysis, splicing the data source identifier and the table name in the rule as a key, and taking the new data source as a value, and caching to the memory Map, with a time complexity of O(1);

[0031] S11, when executing the add-delete-modify SQL, the SQL is intercepted and analyzed in real time in the ORM layer;

[0032] S12, the data source annotation is analyzed through the AOP technology at runtime to obtain the data source identifier;

[0033] S13, the table name in the SQL is efficiently and real-timely analyzed by means of the Alibaba Druid tool;

[0034] S14, after the processing based on S12 and S13, the data source identifier and the data table name are spliced as a key to query whether the stop-write rule is hit in the memory rule;

[0035] S15, if the rule is hit, an exception is directly thrown, execution is not allowed, and the operation is ended, if the rule is not hit, the operation is passed, and the operation is ended.

[0036] Further, the specific process of judging whether the old database transactions within a certain time are in the step S43 is as follows:

[0037] The non-readonly transaction ID list transcation_ids is obtained from the old database as a baseline, and then the current non-readonly transaction list of the old database is periodically queried;

[0038] If transction_ids is not in it, there is a new addition, go to the next step;

[0039] If the periodic query process exceeds the expected duration, there is a new addition, that is, the SQL stop writing rule, terminates this migration, and ends the operation.

[0040] Further, the specific process of completing the old library migration to the new library operation by the data source rerouting component in step S45 is as follows:

[0041] S20, processing application engineering code, data source component adds new data source configuration;

[0042] S21, when executing SQL, real-time interception and analysis of SQL are performed in the ORM layer, and if a transaction has been started, it is directly released;

[0043] S22, data source annotation is parsed by AOP technology at runtime to obtain the data source identifier;

[0044] S23, the table name in the SQL is parsed in real time by the Alibaba Druid tool;

[0045] S24, after the processing based on S22 and S23, the data source identifier and the table name are spliced as a key to query whether the rerouting rule is hit in the memory rule;

[0046] S25, if the rule is hit, the value of the hit rule is taken as the final data source for executing the SQL, and the SQL rerouting effect is achieved.

[0047] Further, it further comprises:

[0048] S30, data source annotation and rerouting annotation are added on the transaction method; wherein the data source annotation and the rerouting annotation are designed separately to facilitate subsequent cleaning work, and the rerouting annotation carries an identifier mark to support multiple batches of migration;

[0049] S31, data source annotation is parsed by AOP technology at runtime to obtain the data source identifier;

[0050] S32, rerouting annotation is parsed by AOP technology at runtime to obtain the identifier; the data source identifier and the identifier are spliced as a key to query whether the rerouting rule is hit in the memory rule;

[0051] S33, the transaction processing mechanism of the Spring framework acquires the connection from the specified data source according to the final data source identifier and binds it in the current thread. BRIEF DESCRIPTION OF DRAWINGS

[0052] Figure 1 The flowchart of the present application;

[0053] Figure 2 This is a flowchart of the ORM layer write stop process of the present invention;

[0054] Figure 3 This is a flowchart illustrating the data rerouting process applied to tables in this invention.

[0055] Figure 4 The data rerouting process of this invention is shown in the transaction flowchart. Detailed Implementation

[0056] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0057] In the embodiments, by Figure 1 This paper presents a method for migrating a database without downtime, including the following steps:

[0058] S40. Establish bidirectional data synchronization between the old and new databases;

[0059] The specific process of establishing bidirectional data synchronization between the old and new databases in step S40 is as follows:

[0060] Establish bidirectional data synchronization between the old and new databases using data synchronization tools (Alibaba Canal or cloud vendor data transfer tools);

[0061] In this way, only one database executes CRUD SQL statements at any given time, ensuring that there are no data conflicts.

[0062] S41. Check the data synchronization speed and quality between the old and new databases. If the check passes, proceed to the next step. If the check fails, stop the migration and end the operation.

[0063] The specific process of detecting the data synchronization speed and quality between the old and new databases in step S41 is as follows:

[0064] Using JDBC tools, a new Connection is created for the old database. A record data1 is inserted into the migration auxiliary table of the old database. Then, the inserted data1 is queried from the migration auxiliary table of the new database. The duration is obtained and compared with the preset duration. The duration can be used as an indicator of synchronization rate and latency. If the indicator does not meet the expectations, the migration will not be performed.

[0065] If the time is less than the preset duration, proceed to the next step;

[0066] If the migration exceeds the preset time, the migration will stop and the operation will end.

[0067] S42. After the data synchronization quality check in step S41, submit the SQL stop-write rules of the old data source to the distributed configuration center, and the stop-write component of the ORM layer is responsible for parsing them.

[0068] In step S42, the ORM layer stop-write component is responsible for resolving the fact that the write only applies to the old data source, and after the write takes effect, no more write transactions will be committed to the database.

[0069] like Figure 2 As shown, the ORM layer stop-write component is used to precisely control the CRUD operations of a specified table. The specific process of parsing by the ORM layer stop-write component in step S42 is as follows:

[0070] S10. Real-time acquisition and parsing of the write stop rule configuration of the distributed configuration center, concatenation of the data source identifier datasource and table name table in the rule as the key, the destination new data source as the value, cached in memory Map, with time complexity O(1);

[0071] S11. When executing CRUD SQL statements, the SQL is intercepted and parsed in real time at the ORM layer (e.g., Mybatis Interceptor).

[0072] S12. At runtime, the data source annotation is parsed using AOP technology to obtain the data source identifier (datasource).

[0073] S13. Use Alibaba Druid tool to efficiently and in real-time parse table names in SQL;

[0074] S14. Based on the processing of S12 and S13, the data source identifier (datasource) and the data table name (table) are concatenated as the key, and the memory rule map is queried to see if the write stop rule is hit.

[0075] S15. If the rule is met, throw an exception directly, do not allow execution, and terminate the operation. If the rule is not met, allow the operation and terminate the operation.

[0076] S43. Determine whether new transactions are being added to the old database within a certain period of time. If no new transactions are added, proceed to the next step. If new transactions are added, roll back the SQL stop-write rule, stop the migration, and end the operation.

[0077] The specific process of determining whether old database transactions within a certain period of time are being added in step S43 is as follows:

[0078] Use the list of non-read-only transaction IDs, transaction_ids, from the old database as a baseline, and then periodically query the current list of non-read-only transactions in the old database.

[0079] If transaction_ids is not included, it means that the old database is no longer executing insert, delete, or update SQL statements and there are no running non-read-only transactions, so no new data will be added to proceed to the next step.

[0080] If the periodic query process exceeds the expected time, a new SQL write stop rule will be added to terminate the migration and end the operation.

[0081] S44. Insert a record `data2` into the migration auxiliary table of the old database. Then, query the new database's migration auxiliary table for the inserted record `data2`. Based on step S43 and taking advantage of the ordered nature of Binlog replay in MySQL master-slave synchronization, if `data2` is found in the new database, it means data synchronization is complete, and proceed to the next step. This is more efficient than the MySQL Checksum tool, significantly reducing time. If the record is not found within the specified time, rollback is performed, terminating the migration. If the record is still not found, the SQL stop-write rule is rolled back, the migration is stopped, and the operation ends.

[0082] S45. Submit the data source rerouting rules to the distributed configuration center, and the data source rerouting component will be responsible for parsing them. After they take effect, the SQL of the table to be migrated will be executed by the new data source. In addition, the SQL stop writing rule in step S42 is only effective for the old data source, so the CRUD SQL rerouting to the new data source can be executed normally. As for the stop writing rule, it can be cleaned up in subsequent steps. At this point, the old database to new database migration operation is completed.

[0083] The data source rerouting component obtains and parses the rerouting rule configuration from the distributed configuration center in real time, and caches the rules in an in-memory Map.

[0084] The key has two dimensions: table and transaction identifier, while the value stores the final target data source. Data source routing is precisely controlled from these two dimensions. The first dimension applies to the table, as shown in the diagram. The specific process of rerouting the old database to the new database in step S45, handled by the data source rerouting component, is as follows:

[0085] S20: Process application project code, add new data source configuration to the data source component;

[0086] S21. When executing SQL, intercept and parse the SQL in real time at the ORM layer (such as Mybatis Interceptor). If a transaction has been started, allow it directly.

[0087] S22. At runtime, the data source annotation is parsed using AOP technology to obtain the data source identifier, datasource.

[0088] S23. Use Alibaba Druid tool to efficiently and in real-time parse table names in SQL;

[0089] S24. Based on the processing of S22 and S23, the data source identifier (datasource) and table name (table) are concatenated as the key, and the rerouting rule is checked in the in-memory rule map to see if it is hit.

[0090] S25. If the rule is hit, the value of the hit rule will be used as the data source for the final execution of the SQL, thus achieving the effect of SQL rerouting.

[0091] like Figure 3 and Figure 4 As shown, the data source rerouting component, the second type of which operates on transactions, cannot use the S2 rerouting process described above because the database connection is determined when Spring transaction annotations are enabled, and the target database source cannot be modified during SQL execution. The transaction level is handled by the following process, with specific steps including:

[0092] S30. Add data source annotation and rerouting annotation to the transaction method; the data source annotation and rerouting annotation are designed separately to facilitate subsequent cleanup work, and the rerouting annotation carries a mark to support multiple batch migrations;

[0093] S31. At runtime, the data source annotation is parsed using AOP technology to obtain the data source identifier (datasource).

[0094] S32. At runtime, the rerouting annotation is parsed using AOP technology to obtain the identifier mark; the data source identifier datasource and the identifier mark are concatenated as the key, and the rerouting rule is checked in the in-memory rule map to see if it is matched.

[0095] S33. Spring's transaction processing mechanism obtains a Connection from a specified data source based on the final data source identifier and binds it to the current thread;

[0096] Also includes:

[0097] S46. Determine if the data migration process is stable. If it is stable, proceed to the next step. If it is unstable, roll back and end the operation.

[0098] Specifically, if a problem occurs after the data migration is completed and a rollback is needed, the above process can be reversed and repeated on the basis of bidirectional synchronization established in S40.

[0099] S47. After confirming that the new database is running stably after migration, reclaim the relevant resources mentioned above (such as cleaning up SQL write stop rules, stopping data synchronization, and reclaiming the old database), and end the operation.

[0100] As described above, the feasible migration solution, which involves application modification, data synchronization, and database traffic switching, allows for low-cost database migration capabilities, facilitating further business development. The one-click cutover provides fine-grained control over the migration process, with close collaboration between bidirectional data synchronization, application ORM layer write-stop components, and data source rerouting components, achieving millisecond-level cutover that is imperceptible to users, truly enabling database migration without downtime.

[0101] It should be noted that, in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0102] Although embodiments of the invention have been shown and described, it will be understood by those skilled in the art that various changes, modifications, substitutions and alterations can be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the appended claims and their equivalents.

Claims

1. A method of live migrating a database, the method comprising: The specific steps include: S40, establishing data bidirectional synchronization of the new and old databases; S41, detecting data synchronization speed and quality of the new and old databases, if passing the detection, proceeding to the next step, if not passing the detection, stopping the migration and ending the operation; The specific process of detecting data synchronization speed and quality of the new and old databases in the step S41 is as follows: With the help of the JDBC tool, a contact between the new and old databases is created, a data data1 is inserted into the migration auxiliary table of the old database, and the inserted data data1 is specified to be queried from the migration auxiliary table of the new database, the time length is obtained and compared with the preset time length; If lower than the preset time length, proceed to the next step; If higher than the preset time length, stop the migration and end the operation; S42, submitting the SQL stop-write rule of the old data source to the distributed configuration center, and the ORM layer stop-write component is responsible for parsing; The specific process of the ORM layer stop-write component responsible for parsing in the step S42 is as follows: S10, real-time acquisition and parsing of the stop-write rule configuration of the distributed configuration center, splicing the data source identifier and table name in the rule as a key and the target new data source as a value, and caching to the memory Map, time complexity O(1); S11, when executing the add-delete-modify SQL, the SQL is intercepted and parsed in real time in the ORM layer; S12, the data source identifier is obtained by parsing the data source annotation through the AOP technology at runtime; S13, the table name in the SQL is parsed in real time with the help of the Alibaba Druid tool; S14, after the processing based on S12 and S13, the data source identifier and the data table name are spliced as a key, and it is queried whether it hits the stop-write rule in the memory rule; S15, if the rule is hit, an exception is directly thrown, execution is not allowed, and the operation is ended, if the rule is not hit, it is passed, and the operation is ended; S43, judging whether the old database transactions within a certain time are added, if not added, proceeding to the next step, if added, rolling back the SQL stop-write rule, stopping the migration, and ending the operation; S44, inserting a data data2 into the migration auxiliary table of the old database, and then specifying to query the inserted data data2 from the migration auxiliary table of the new database, if the data data2 is queried, proceeding to the next step, if the data data2 is not queried, rolling back the SQL stop-write rule, stopping the migration, and ending the operation; S45, submitting the data source re-routing rule to the distributed configuration center, and the data source re-routing component is responsible for parsing, after taking effect, the SQL of the migrated table is executed by the new data source, and the operation of migrating the old database to the new database is completed.

2. The method for live database migration according to claim 1, characterized in that: Further comprising: S46, judging whether the data migration process is stable, if stable, proceeding to the next step, if not stable, rolling back and ending the operation; S47, recycling the related resources involved after the new database runs stably after the migration, and ending the operation.

3. The method for live database migration according to claim 1, wherein: The specific process of establishing data bidirectional synchronization of the new and old databases in the step S40 is as follows: Using a data synchronization tool to establish data bidirectional synchronization of the new and old databases; Among them, only one database executes the add-delete-modify SQL at the same time.

4. The method for live database migration according to claim 1, wherein: The ORM layer stop-write component responsible for parsing in the step S42 only takes effect on the old data source, and after taking effect, no write transaction is submitted to the database.

5. The method for live database migration according to claim 1, wherein: The step S43 in judging whether the old database transactions within a certain time are added in the specific process as follows: Get the non-readonly transaction ID list from the old database as a baseline, and then periodically query the current non-readonly transaction list of the old database; If transcation_ids is not in it, There is no new next step; If the periodic query process exceeds the expected duration, there is a new one, that is, the SQL stop writing rule, terminate this migration, and end the operation.

6. The method for live database migration according to claim 1, wherein: The step S45 in the data source rerouting component is responsible for analyzing the specific process of migrating the old database to the new database operation as follows: S20, process application engineering code, data source component adds new data source configuration; S21, when executing SQL, intercept and parse SQL in real time in the ORM layer, and if a transaction has been started, directly release it; S22, parse data source annotations in real time through AOP technology at runtime to obtain data source identification; S23, use Alibaba Druid tool to efficiently and real-time parse table names in SQL; S24, based on the processing of S22 and S23, splice the data source identification and table name as a key to query whether it hits the rerouting rule in the memory rule; S25, if the rule is hit, the value of the hit rule is used as the final data source to execute the SQL, achieving the effect of SQL rerouting.

7. The method of migrating a database without downtime of claim 6, wherein: Also includes: S30, add data source annotations and rerouting annotations on transaction methods; among them, the data source annotations and rerouting annotations are designed separately to facilitate subsequent cleaning work, and the rerouting annotations carry the identification mark to support multiple batches of migration; S31, parse data source annotations at runtime through AOP technology to obtain data source identification; S32, parse rerouting annotations at runtime through AOP technology to obtain identification; Splice the data source identification and the identification as a key to query whether it hits the rerouting rule in the memory rule; S33, the transaction processing mechanism of Spring framework, according to the final data source identification, contact from the specified data source, and bind in the current thread.

Images