Database synchronization method and device, equipment and storage medium
During the database migration process, the main thread operates on the first database and captures SQL statements, and the asynchronous thread operates on the second database, solving the problem of data inconsistency during the database migration process and achieving data consistency and accuracy.
Patent Information
- Application Number
- CN202411715683.9
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-11-27
- Publication Date
- 2025-03-18
AI Technical Summary
During the database migration process, when new and old databases are applied at the same time, inconsistent data operations lead to a decrease in data accuracy, affecting the normal operation of the business.
The first database is operated through the main thread, and the target SQL statements related to the write operation are captured and added to the queue to be executed. After the main thread completes operations, the asynchronous thread operates on the second database according to the SQL statement in the queue to ensure data consistency.
The consistency between new and old database data during the database migration process is achieved, the accuracy of database data is improved, and the risk of database instability is reduced.
Smart Images
Figure CN119646090A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the field of big data technology, and in particular to a database synchronization method, device, equipment and storage medium. Background Art
[0002] With the advancement of digital transformation and the enhancement of information security awareness, databases, as the core elements of enterprise system architecture, have become the focus of architecture transformation. However, database migration is a long transition process. In order to ensure the normal use of the business, both the old and new databases need to provide external services to ensure that the database migration process can proceed smoothly.
[0003] However, when two databases are used at the same time, different operations on data in different databases will lead to inconsistent data in the two databases, which will reduce the accuracy of the data in the databases and make the business unable to run normally. Therefore, how to ensure the consistency of data in the database is an urgent problem that we need to solve. Summary of the invention
[0004] Based on the above problems, the present application provides a database synchronization method, apparatus, device and storage medium, the purpose of which is to ensure the consistency of data in the database and improve the accuracy of data in the database.
[0005] The embodiments of the present application disclose the following technical solutions:
[0006] In a first aspect, the present application provides a database synchronization method, comprising:
[0007] In response to the database operation request, the main thread performs a database operation on the first database through the persistence layer; the first database is a new database or an old database;
[0008] Capture the target SQL statement from the persistence layer and add the target SQL statement to the queue to be executed; the target SQL statement is the SQL statement corresponding to the write operation;
[0009] When it is determined that the main thread completes the database operation on the first database, the asynchronous thread performs the database operation on the second database according to the target SQL statement in the queue to be executed; the second database is a database that performs data synchronization with the first database.
[0010] In response to the database operation request, the main thread performs database operations on the first database through the persistence layer, and implements the operation on the database of the current application. In order to achieve database synchronization, the target SQL statement related to the write operation that needs to be executed in the second database is captured from the persistence layer, and the target SQL statement is added to the queue to be executed; when it is determined that the main thread has completed the database operation on the first database, the asynchronous thread performs database operations on the second database according to the target SQL statement in the queue to be executed, so as to ensure the consistency of data in the two databases and improve the accuracy of data in the databases.
[0011] Optionally, in the above method, when it is determined that the main thread completes the database operation on the first database, the asynchronous thread performs the database operation on the second database according to the target SQL statement in the queue to be executed, including:
[0012] When the main thread completes the database operation on the first database, it is determined whether there is an idle asynchronous thread in the asynchronous thread pool;
[0013] When it is determined that there is an idle asynchronous thread, executing the target SQL statement in the to-be-executed queue on the second database in the asynchronous thread to perform the database operation;
[0014] If there are abnormal SQL statements in the asynchronous thread pool, the abnormal SQL statements are stored in the abnormal SQL record file;
[0015] The SQL statements in the abnormal SQL record file are executed on the second database to perform database operations, thereby improving the consistency of data in the second database with data in the first database and reducing the risk of database instability during database migration.
[0016] Optionally, in the above method, if there is an abnormal SQL statement in the asynchronous thread pool, the abnormal SQL statement is saved to an abnormal SQL record file, including:
[0017] Repeat the abnormal SQL statement for a preset number of times on the second database to obtain the execution results of the preset number of times;
[0018] If the execution results of the preset number of times are all failures, the abnormal SQL statements are parsed and spliced to obtain executable SQL statements;
[0019] Storing executable SQL statements in abnormal SQL log files can effectively manage abnormal operations and improve the accuracy of data synchronization.
[0020] Optionally, the method as described above, executing the SQL statement in the abnormal SQL record file on the second database to perform database operations includes:
[0021] The asynchronous thread reads the abnormal SQL log file based on the preset time interval;
[0022] If there are executable SQL statements in the abnormal SQL record file, the executable SQL statements are executed on the second database in sequence according to the order of the executable SQL statements in the abnormal SQL record file to perform database operations, thereby improving the consistency of data in the two databases and reducing the risk of data inconsistency.
[0023] Optionally, in the method described above, before the main thread performs a database operation on the first database through the persistence layer in response to the database operation request, the method further includes:
[0024] Obtain a configuration file; the configuration file includes a switching identifier and a first database identifier and a second database identifier corresponding to the switching identifier; the switching identifier is a stage identifier of the data migration process;
[0025] The switching identifier is loaded from the configuration file, and the first database corresponding to the first database identifier and the first database corresponding to the second database are determined, thereby realizing the switching between the new and old databases in different application scenarios to ensure normal business applications during the database migration process.
[0026] Optionally, in the method described above, when the switching identifier is the first identifier, the first database identifier corresponding to the first identifier is the old database;
[0027] When the switching identifier is the second identifier, the first database identifier corresponding to the second identifier is the old database, and the second database identifier corresponding to the second identifier is the new database;
[0028] When the switching identifier is the third identifier, the first database identifier corresponding to the third identifier is the new database, and the second database identifier corresponding to the third identifier is the old database;
[0029] When the switching identifier is the fourth identifier, the first database identifier corresponding to the fourth identifier is the new database.
[0030] Optionally, in the method described above, in response to the database operation request, the main thread performs a database operation on the first database through the persistence layer, including:
[0031] In response to the database operation request, the main thread executes the business logic method corresponding to the database operation request at the logic layer and obtains the logic parameters corresponding to the business logic method;
[0032] Constructing SQL statements corresponding to the logic parameters in the persistence layer, and executing the SQL statements on the first database to perform database operations, achieves a clear separation of business logic and data access logic, helps to reduce the coupling of the code, and improves the maintainability and scalability of the code.
[0033] In a second aspect, the present application provides a database synchronization device, including:
[0034] A first database operation module, used for responding to a database operation request, the main thread performs a database operation on a first database through a persistence layer; the first database is a new database or an old database;
[0035] The SQL statement capture module is used to capture the target SQL statement from the persistence layer and add the target SQL statement to the queue to be executed; the target SQL statement is the SQL statement corresponding to the write operation;
[0036] The second database operation module is used to, when it is determined that the main thread has completed the database operation on the first database, the asynchronous thread performs database operation on the second database according to the target SQL statement in the queue to be executed; the second database is a database for data synchronization with the first database.
[0037] In a third aspect, the present application provides an electronic device, the device comprising: a processor, and a memory communicatively connected to the processor;
[0038] Memory stores computer-executable instructions;
[0039] The processor executes the computer-executable instructions stored in the memory to implement the database synchronization method described in any one of the above embodiments.
[0040] In a fourth aspect, the present application provides a computer-readable storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are executed by a processor, they are used to implement the database synchronization method described in any of the above embodiments. BRIEF DESCRIPTION OF THE DRAWINGS
[0041] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.
[0042] Figure 1 A flowchart of an embodiment of a database synchronization method provided by the present application;
[0043] Figure 2 This is a schematic diagram of the process of database synchronization in this embodiment;
[0044] Figure 3 A flowchart of another embodiment of a database synchronization method provided by the present application;
[0045] Figure 4 This is a schematic diagram of the different stages in the database migration process;
[0046] Figure 5 Core code for configuring dual databases;
[0047] Figure 6 A schematic diagram of the structure of an embodiment of a database synchronization device provided by the present application;
[0048] Figure 7 A schematic diagram of the structure of an embodiment of an electronic device provided in the present application. DETAILED DESCRIPTION
[0049] As described above, in order to ensure that the database migration process can proceed smoothly, both the old and new databases need to provide external services. How to ensure the consistency of data in the old and new databases is an urgent problem that we need to solve.
[0050] After research, the inventors have proposed a database synchronization method, device, equipment and storage medium to ensure the consistency of data in the database and improve the accuracy of data in the database.
[0051] In order to enable those skilled in the art to better understand the solution of the present application, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.
[0052] Terminology explanation:
[0053] Mybatis: Mybatis is an open source, excellent, and extensible persistence layer framework that supports customized SQL, stored procedures, and advanced mapping. The Mybatis architecture includes an interface layer, whose main function is to accept user data and display the processing results of the request; the logic layer is used to receive data passed from the interface layer, check data, calculate business logic, and call the persistence layer to perform database operations on the database; the persistence layer is used to add, delete, modify, and query data in the database. Mybatis's plug-in mechanism allows interception at a certain point in the execution of the mapping statement, and adds customized logic processing after interception to achieve method enhancement.
[0054] SqlSession: SqlSession is the main interface abstraction of Mybatis, providing operations such as add, delete, and modify. Each transaction will create a new SqlSession, and the Spring framework is responsible for managing the commit / rollback of the transaction.
[0055] SqlSessionTemplate: SqlSessionTemplate is a template class that is mainly responsible for managing Mybatis's SqlSession and calling Mybatis's sql methods.
[0056] See also Figure 1 , which is a flow chart of an embodiment of a database synchronization method provided by the present application. Figure 1 As shown, the method includes:
[0057] S101: In response to a database operation request, the main thread performs a database operation on a first database through a persistence layer.
[0058] The first database is a new database or an old database.
[0059] In this embodiment, when the main thread receives a database operation request, for example, adding a new entry in table A or deleting an entry in table B, the business logic method corresponding to the database operation request is executed in the logic layer, and the logic parameters corresponding to the business logic method are obtained and sent to the persistence layer. The persistence layer constructs a corresponding SQL statement according to the logic parameters, and executes the SQL statement on the first database to perform the database operation.
[0060] S102: Capture the target SQL statement from the persistence layer, and add the target SQL statement to a queue to be executed.
[0061] The target SQL statement is the SQL statement corresponding to the write operation.
[0062] In this embodiment, when the persistence layer is called to perform database operations on the database, the persistence layer will generate corresponding SQL statements. For query SQL statements, only read operations are performed on the first database, and no database synchronization is required. Therefore, only SQL statements corresponding to write operations other than query SQL statements need to be captured. Specifically, write operations may include but are not limited to add, delete, and modify operations.
[0063] S103: When it is determined that the main thread completes the database operation on the first database, the asynchronous thread performs the database operation on the second database according to the target SQL statement in the queue to be executed.
[0064] The second database is a database that performs data synchronization with the first database.
[0065] In this embodiment, when it is determined that the main thread has completed the database operation on the first database, it is determined whether there is an idle asynchronous thread in the asynchronous thread pool. If so, the asynchronous thread is called to execute the target SQL statement in the queue to be executed on the second database and determine whether there is an abnormal SQL statement that cannot be executed in the asynchronous thread pool. If so, the abnormal SQL statement that cannot be executed is stored in the abnormal SQL record file, so as to be used to re-execute the SQL statement in the abnormal SQL record file on the second database to perform the corresponding database operation.
[0066] It is understandable that multiple SQL statements corresponding to the same transaction are executed in the same asynchronous thread.
[0067] For example, refer to Figure 2 , which is a schematic diagram of the data synchronization process in this embodiment. Figure 2 As shown, the solid-line box area is the main thread, and the dotted-line box area is the asynchronous thread. When the main thread receives a transaction request, the transaction request may include multiple database operation requests. Based on the received database operation request, for example, query table A and add, delete and modify table B. Execute the business logic method corresponding to the database operation request in the logic layer, obtain the logic parameters corresponding to the business logic method, and the logic parameters include the specific operations corresponding to the query table A and the add, delete and modify table B, and send the logic parameters to the persistence layer to construct the corresponding SQL statement, and execute the SQL statement on the first database through the persistence layer to perform the database operation.
[0068] The target SQL statement corresponding to the write operation is captured from the persistence layer, that is, the SQL statement corresponding to adding, deleting, and modifying table B, and the target SQL statement is added to the queue to be executed and submitted to the asynchronous thread. When it is determined that the main thread has completed the database operation on the first database, the asynchronous thread performs the database operation on the second database according to the target SQL statement in the queue to be executed.
[0069] In this embodiment, in response to a database operation request, the main thread performs a database operation on the first database through the persistence layer, thereby realizing the operation on the database of the current application. In order to realize database synchronization, the target SQL statement related to the write operation to be executed in the second database is captured from the persistence layer, and the target SQL statement is added to the queue to be executed; when it is determined that the main thread has completed the database operation on the first database, the asynchronous thread performs the database operation on the second database according to the target SQL statement in the queue to be executed, which can ensure the consistency of the data in the two databases without affecting the main thread and improve the accuracy of the data in the database.
[0070] Further, based on the above embodiment, a specific implementation of "in response to the database operation request, the main thread performs a database operation on the first database through the persistence layer" in S101 includes:
[0071] In response to the database operation request, the main thread executes the business logic method corresponding to the database operation request at the logic layer and obtains the logic parameters corresponding to the business logic method.
[0072] In this embodiment, taking adding a table entry in Table A as an example, after the main thread receives an operation request for Table A in the first database at the logic layer, it executes the business logic method corresponding to the database operation request, including rule verification and data recording of the input parameters, to obtain the logic parameters corresponding to the business logic method.
[0073] An SQL statement corresponding to the logic parameter is constructed in the persistence layer, and the SQL statement is executed on the first database to perform database operations.
[0074] In this embodiment, the persistence layer receives the logic parameters passed by the logic layer, and constructs an SQL statement based on the logic parameters, which specifies the column name and corresponding value for adding a table item in Table A. The persistence layer establishes a connection with the first database to execute the SQL statement on Table A, inserting the new table item data into Table A, and the first database creates a new table item in Table A based on the information in the SQL statement.
[0075] In this embodiment, in response to a database operation request, the main thread executes the business logic method corresponding to the database operation request in the logic layer, and obtains the logic parameters corresponding to the business logic method. In the persistence layer, an SQL statement corresponding to the logic parameter is constructed, and the SQL statement is executed on the first database to perform the database operation, thereby achieving a clear separation of business logic and data access logic, helping to reduce the coupling degree of the code and improve the maintainability and scalability of the code.
[0076] See also Figure 3 , which is a flowchart of another embodiment of a database synchronization method provided by the present application; Figure 1 , Figure 3 A specific implementation method of "when it is determined that the main thread completes the database operation on the first database, the asynchronous thread performs the database operation on the second database according to the target SQL statement in the queue to be executed" in S103 is shown, specifically combined with Figure 3 Introduced in S1031-S1034.
[0077] S1031: When the main thread completes the database operation on the first database, it is determined whether there is an idle asynchronous thread in the asynchronous thread pool.
[0078] In this embodiment, after the main thread completes the database operation on the first database, the queue to be executed is submitted to the asynchronous thread pool, and it is determined whether there is an idle asynchronous thread in the asynchronous thread pool that can execute the operation.
[0079] S1032: When it is determined that there is an idle asynchronous thread, the target SQL statement in the to-be-executed queue is executed on the second database in the asynchronous thread to perform database operations.
[0080] In this embodiment, when there are idle asynchronous threads that can be executed in the asynchronous thread pool, the target SQL statement is obtained from the queue to be executed to perform database operations on the second database.
[0081] If there is no idle asynchronous thread in the asynchronous thread pool, it is further determined whether the waiting queue corresponding to the asynchronous thread pool is full. If the waiting queue is not full, the target SQL statement is added to the waiting queue and waits for an idle asynchronous thread to execute the target SQL statement; if the waiting queue is full, the target SQL statement is treated as an abnormal SQL statement.
[0082] S1033: If there is an abnormal SQL statement in the asynchronous thread pool, the abnormal SQL statement is stored in an abnormal SQL record file.
[0083] S1034: Execute the SQL statement in the abnormal SQL record file on the second database to perform database operations.
[0084] In this embodiment, for example, when there are abnormal SQL statements in the asynchronous thread pool, the abnormal SQL statements can be repeatedly executed for a preset number of times. When the execution results corresponding to the preset number of repeated executions are all failures, the abnormal SQL statements with failed execution results can be repaired, spliced into executable SQL statements and stored in the abnormal SQL record file as a basis for judging whether data synchronization is successful, so as to be used for subsequent re-execution of these SQL statements to complete data synchronization.
[0085] Combination Figure 3 As shown, before executing S1031-S1034, referring to the previous embodiment, S101-S102 are executed to implement the operation on the first database and obtain the sequence to be executed.
[0086] In this embodiment, in order to flexibly allocate and manage resources, when the main thread completes the database operation on the first database, it is determined whether there is an idle asynchronous thread in the asynchronous thread pool. When it is determined that there is an idle asynchronous thread, the target SQL statement in the queue to be executed is executed on the second database in the asynchronous thread to perform the database operation, so as to avoid operation delays or failures caused by insufficient threads. If there is an abnormal SQL statement in the asynchronous thread pool, the abnormal SQL statement is stored in an abnormal SQL record file. The SQL statement in the abnormal SQL record file is executed on the second database to perform the database operation, which improves the consistency of the data in the second database with the data in the first database and reduces the risk of database instability during database migration.
[0087] Further, based on the above embodiment, a specific implementation of "if there is an abnormal SQL statement in the asynchronous thread pool, storing the abnormal SQL statement in the abnormal SQL record file" in S1033 includes:
[0088] The abnormal SQL statement is repeatedly executed on the second database for a preset number of times to obtain the execution results of the preset number of times.
[0089] If the execution results of the preset number of times are all failures, the abnormal SQL statements are parsed and spliced to obtain executable SQL statements.
[0090] Store executable SQL statements in abnormal SQL log files.
[0091] In this embodiment, taking the preset number of times as 3 as an example, if there is an abnormal SQL statement in the asynchronous thread pool, the abnormal SQL statement is re-executed 3 times. When the results of the 3 re-executions are all failures, the abnormal SQL statement is parsed, and the node identifier, BoundSQL and node configuration are obtained respectively, and the placeholders and extra spaces are replaced to re-assemble the abnormal SQL statement into an executable SQL statement, and the executable SQL statement is stored in the abnormal SQL record file.
[0092] In this embodiment, in order to avoid SQL execution failure caused by network fluctuations, the abnormal SQL statement is repeatedly executed on the second database for a preset number of times to obtain the execution results of the preset number of times. If the execution results of the preset number of times are all failures, it means that the abnormal SQL statement cannot be executed, and then the abnormal SQL statement is parsed and spliced, and an executable SQL statement is obtained through automated repair attempts; and the executable SQL statement is stored in the abnormal SQL record file to effectively manage abnormal operations and improve the accuracy of data synchronization.
[0093] Further, based on the above embodiment, a specific implementation of "executing the SQL statement in the abnormal SQL record file on the second database to perform database operations" in S1034 includes:
[0094] The asynchronous thread reads the abnormal SQL log file based on the preset time interval.
[0095] If there are executable SQL statements in the abnormal SQL record file, the executable SQL statements are executed on the second database in sequence according to the order of the executable SQL statements in the abnormal SQL record file to perform database operations.
[0096] In this embodiment, if the asynchronous thread fails to execute, it will affect the data of the second database, resulting in inconsistency between the data in the first database and the second database. The asynchronous thread periodically reads the abnormal SQL log file based on a preset time interval. If there is no executable SQL statement in the abnormal SQL log file, it means that there is no failed database operation in the asynchronous thread. If there is an executable SQL statement in the abnormal SQL log file, it means that there is a failed database operation in the asynchronous thread. At this time, according to the order of the executable SQL statements in the abnormal log file, the corresponding executable SQL statements are executed in sequence on the second database to perform database operations.
[0097] In this embodiment, the asynchronous thread reads the abnormal SQL record file based on a preset time interval, and can promptly discover and handle abnormal situations in database operations. If there are executable SQL statements in the abnormal SQL record file, the executable SQL statements are executed on the second database in sequence according to the order of the executable SQL statements in the abnormal SQL record file to perform database operations, thereby improving the consistency of data in the two databases and reducing the risk of data inconsistency.
[0098] Further, based on the above embodiment, in the actual database migration process, the database currently processing the business may fail, and it is necessary to switch to another database to ensure that the business can proceed normally. At this time, before "responding to the database operation request, the main thread performs a database operation on the first database through the persistence layer" in S101, the method may also include:
[0099] Obtain a configuration file, wherein the configuration file includes a switching identifier and a first database identifier and a second database identifier corresponding to the switching identifier; the switching identifier is a stage identifier of the data migration process.
[0100] The switching identifier is loaded from the configuration file, and the first database corresponding to the first database identifier and the first database corresponding to the second database are determined.
[0101] In this embodiment, the first database and the second database are determined from the switching identifier in the read configuration file and the first database identifier and the second database identifier corresponding to the switching identifier. For example, the switching identifier is stage 2, the corresponding first database identifier is the old database, and the second database identifier is the old database. Therefore, the database operation on the old database is executed in the main thread, and the database operation on the new database is executed in the asynchronous thread.
[0102] Furthermore, the switch identifier can be stored in the Redis cache, and the switch identifier can be obtained from the Redis cache based on a preset time interval, avoiding accessing the Redis cache to obtain the switch identifier for each request, thereby reducing the system overhead and response time caused by accessing the Redis cache to obtain the switch identifier.
[0103] Wherein, when the switching identifier is the first identifier, the first database identifier corresponding to the first identifier is the old database;
[0104] When the switching identifier is the second identifier, the first database identifier corresponding to the second identifier is the old database, and the second database identifier corresponding to the second identifier is the new database;
[0105] When the switching identifier is the third identifier, the first database identifier corresponding to the third identifier is the new database, and the second database identifier corresponding to the third identifier is the old database;
[0106] When the switching identifier is the fourth identifier, the first database identifier corresponding to the fourth identifier is the new database.
[0107] For example, see Figure 4 , which is a schematic diagram of the different stages of the database migration process. Figure 4 As shown, when the switching mark is stage 1, the corresponding application scenario is before database migration. At this time, the first database is the old database, and only the corresponding read and write operations are performed on the old database.
[0108] When the switching mark is stage 2, the corresponding application scenario is that during the database migration process, the old database is the first database of the current application, and the new database is the old database that needs to be synchronized. At this time, the old database is read and written accordingly, and the new database is written to achieve data synchronization.
[0109] When the switch mark is stage 3, the corresponding application scenario is that during the database migration process, the new database is the first database of the current application, and the old database is the old database that needs to be synchronized. The new database performs corresponding read and write operations, and writes to the old database to achieve data synchronization. When the database of the current application fails during the database migration process, you can switch to another database to ensure the normal application of the business.
[0110] When the switching mark is stage 4, the corresponding application scenario is that the database migration is completed. At this time, the first database is a new database, and only the corresponding read and write operations are performed on the new database.
[0111] In this embodiment, a configuration file is obtained, a switching identifier is loaded from the configuration file, and the first database and the second database are determined, so as to implement the switching between the old and new databases in different application scenarios, so as to ensure normal business applications during the database migration process.
[0112] Furthermore, based on the above embodiment, the method may also include: rewriting SqlSessionTemplate based on the Mybatis persistence layer framework to perform dual database configuration.
[0113] Specifically, Figure 5 This is the core code for configuring dual databases. Figure 5 As shown, the case Only1 code segment is configured to operate only on the first database. SqlSessionFactory is bound to the database, so you can rewrite SqlSessionTemplate to create two SqlSessionFactory variables to configure two databases. For example, the case Master1Async2 code segment configures the new database as the first database for operation and the old database as the second database for data synchronization; the case Master2Async1 code segment configures the old database as the first database for operation and the new database as the second database for data synchronization.
[0114] See also Figure 6 , which is a schematic diagram of the structure of an embodiment of a database synchronization device provided by the present application. Figure 6 As shown, the device 60 includes a first database operation module 61, a SQL statement capture module 62 and a second database operation module 63.
[0115] The first database operation module 61 is used to respond to the database operation request, and the main thread performs database operation on the first database through the persistence layer; the first database is a new database or an old database;
[0116] The SQL statement capture module 62 is used to capture the target SQL statement from the persistence layer and add the target SQL statement to the queue to be executed; the target SQL statement is a SQL statement other than the query SQL statement;
[0117] The second database operation module 63 is used for, when it is determined that the main thread has completed the database operation on the first database, the asynchronous thread performs database operation on the second database according to the target SQL statement in the queue to be executed; the second database is a database for data synchronization with the first database.
[0118] A database synchronization device provided in an embodiment of the present application can execute the technical solution shown in the above method embodiment, and its implementation principle and beneficial effects are similar, which will not be repeated here.
[0119] Further, based on the above embodiment, the second database operation module 63 is specifically used to determine whether there is an idle asynchronous thread in the asynchronous thread pool when the main thread completes the database operation on the first database; when it is determined that there is an idle asynchronous thread, execute the target SQL statement in the queue to be executed on the second database in the asynchronous thread to perform the database operation; if there is an abnormal SQL statement in the asynchronous thread pool, store the abnormal SQL statement in the abnormal SQL record file; execute the SQL statement in the abnormal SQL record file on the second database to perform the database operation.
[0120] A database synchronization device provided in an embodiment of the present application can execute the technical solution shown in the above method embodiment, and its implementation principle and beneficial effects are similar, which will not be repeated here.
[0121] Further, based on the above embodiment, when there is an abnormal SQL statement in the asynchronous thread pool and the abnormal SQL statement is saved to the abnormal SQL record file, the second database operation module 63 is specifically used to repeatedly execute the abnormal SQL statement on the second database a preset number of times to obtain the execution results of the preset number of times; if the execution results of the preset number of times are all failures, the abnormal SQL statement is parsed and spliced to obtain an executable SQL statement; and the executable SQL statement is stored in the abnormal SQL record file.
[0122] A database synchronization device provided in an embodiment of the present application can execute the technical solution shown in the above method embodiment, and its implementation principle and beneficial effects are similar, which will not be repeated here.
[0123] Further, based on the above embodiment, when the SQL statements in the abnormal SQL record file are executed on the second database to perform database operations, the second database operation module 63 is specifically used for the asynchronous thread to read the abnormal SQL record file based on a preset time interval; if there are executable SQL statements in the abnormal SQL record file, the executable SQL statements are executed on the second database in sequence according to the order of the executable SQL statements in the abnormal SQL record file to perform database operations.
[0124] A database synchronization device provided in an embodiment of the present application can execute the technical solution shown in the above method embodiment, and its implementation principle and beneficial effects are similar, which will not be repeated here.
[0125] Further, based on the above embodiment, before the main thread performs a database operation on the first database through the persistence layer in response to the database operation request, the device 60 may further include a database switching module 64.
[0126] Specifically, the database switching module 64 is used to obtain a configuration file; the configuration file includes a switching identifier and a first database identifier and a second database identifier corresponding to the switching identifier; the switching identifier is a stage identifier of the data migration process; the switching identifier is loaded from the configuration file to determine the first database and the second database.
[0127] A database synchronization device provided in an embodiment of the present application can execute the technical solution shown in the above method embodiment, and its implementation principle and beneficial effects are similar, which will not be repeated here.
[0128] Further, based on the above embodiment, when the switching identifier is the first identifier, the first database identifier corresponding to the first identifier is the old database; when the switching identifier is the second identifier, the first database identifier corresponding to the second identifier is the old database, and the second database identifier corresponding to the second identifier is the new database; when the switching identifier is the third identifier, the first database identifier corresponding to the third identifier is the new database, and the second database identifier corresponding to the third identifier is the old database; when the switching identifier is the fourth identifier, the first database identifier corresponding to the fourth identifier is the new database.
[0129] A database synchronization device provided in an embodiment of the present application can execute the technical solution shown in the above method embodiment, and its implementation principle and beneficial effects are similar, which will not be repeated here.
[0130] Further, based on the above embodiment, the first database operation module 61 is specifically used to respond to database operation requests and parse the database operation requests into database operation instructions; the main thread executes the business logic method corresponding to the database operation instruction in the logic layer to obtain the logic parameters corresponding to the business logic method; constructs SQL statements corresponding to the logic parameters in the persistence layer, and executes the SQL statements on the first database to perform database operations.
[0131] A database synchronization device provided in an embodiment of the present application can execute the technical solution shown in the above method embodiment, and its implementation principle and beneficial effects are similar, which will not be repeated here.
[0132] See also Figure 7, which is a schematic diagram of the structure of an electronic device according to an embodiment of the present application. The electronic device 70 may include: a processor 71 and a memory 72.
[0133] The processor 71 is in communication with the memory 72 , and the memory 72 is used to store computer-executable instructions. The processor 71 is configured to execute the technical solution in any of the aforementioned method embodiments by executing the computer-executable instructions stored in the memory 72 .
[0134] Optionally, the memory 72 may be independent or integrated with the processor 71. Optionally, when the memory 72 is a device independent of the processor 71, the electronic device 70 may further include: a bus for connecting the above devices.
[0135] The electronic device is used to execute the technical solution in any of the aforementioned method embodiments, and its implementation principle and technical effect are similar and will not be repeated here.
[0136] An embodiment of the present application also provides a computer-readable storage medium, which stores computer execution instructions. When the computer execution instructions are executed by a processor, they are used to implement the above-mentioned method. The implementation principle and technical effect are similar and will not be repeated here.
[0137] It should be noted that each embodiment in this specification is described in a progressive manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the device embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment. The device embodiment described above is merely schematic, in which the unit described as a separate component may or may not be physically separated, and the component prompted as a unit may or may not be a physical unit, that is, it may be located in one place, or it may be distributed on multiple network units. Some or all of the modules can be selected according to actual needs to achieve the purpose of the scheme of this embodiment. Ordinary technicians in this field can understand and implement it without paying creative work.
[0138] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any changes or substitutions that can be easily thought of by a person skilled in the art within the technical scope disclosed in the present application should be included in the protection scope of the present application. Therefore, the protection scope of the present application should be based on the protection scope of the claims.
Claims
1. A database synchronization method, characterized in that: include: In response to the database operation request, the main thread performs a database operation on a first database through the persistence layer; the first database is a new database or an old database; Capturing a target SQL statement from the persistence layer, and adding the target SQL statement to a queue to be executed; the target SQL statement is a SQL statement corresponding to a write operation; When it is determined that the main thread completes the database operation on the first database, the asynchronous thread performs a database operation on a second database according to the target SQL statement in the queue to be executed; the second database is a database for data synchronization with the first database.
2. The method according to claim 1, characterized in that When it is determined that the main thread completes the database operation on the first database, the asynchronous thread performs the database operation on the second database according to the target SQL statement in the queue to be executed, including: When the main thread completes the database operation on the first database, determining whether there is an idle asynchronous thread in the asynchronous thread pool; When it is determined that there is an idle asynchronous thread, executing the target SQL statement in the to-be-executed queue on the second database in the asynchronous thread to perform database operations; If there is an abnormal SQL statement in the asynchronous thread pool, the abnormal SQL statement is stored in an abnormal SQL record file; The SQL statement in the abnormal SQL record file is executed on the second database to perform database operations.
3. The method according to claim 2, characterized in that If there is an abnormal SQL statement in the asynchronous thread pool, the abnormal SQL statement is saved to an abnormal SQL record file, including: Repeating the abnormal SQL statement on the second database for a preset number of times to obtain the execution results of the preset number of times; If the execution results of the preset number of times are all failures, the abnormal SQL statement is parsed and spliced to obtain an executable SQL statement; The executable SQL statement is stored in an abnormal SQL record file.
4. The method according to claim 3, characterized in that: The executing the SQL statement in the abnormal SQL record file on the second database to perform database operation includes: The asynchronous thread reads the abnormal SQL record file based on a preset time interval; If there are executable SQL statements in the abnormal SQL record file, the executable SQL statements are executed on the second database in sequence according to the order of the executable SQL statements in the abnormal SQL record file to perform database operations.
5. The method according to claim 1, characterized in that Before the main thread performs a database operation on the first database through the persistence layer in response to the database operation request, the method further includes: Obtain a configuration file; the configuration file includes a switching identifier and a first database identifier and a second database identifier corresponding to the switching identifier; the switching identifier is a stage identifier of the data migration process; The switching identifier is loaded from the configuration file, and a first database corresponding to the first database identifier and a first database corresponding to the second database are determined.
6. The method according to claim 5, characterized in that When the switching identifier is the first identifier, the first database identifier corresponding to the first identifier is the old database; When the switching identifier is the second identifier, the first database identifier corresponding to the second identifier is the old database, and the second database identifier corresponding to the second identifier is the new database; When the switching identifier is the third identifier, the first database identifier corresponding to the third identifier is the new database, and the second database identifier corresponding to the third identifier is the old database; When the switching identifier is the fourth identifier, the first database identifier corresponding to the fourth identifier is a new database.
7. The method according to claim 1, characterized in that In response to the database operation request, the main thread performs a database operation on the first database through the persistence layer, including: In response to the database operation request, the main thread executes the business logic method corresponding to the database operation request at the logic layer and obtains the logic parameters corresponding to the business logic method; Constructing a SQL statement corresponding to the logic parameter in the persistence layer, and executing the SQL statement on the first database to perform database operations.
8. A database synchronization device, characterized in that: include: A first database operation module, configured to respond to a database operation request, and the main thread performs a database operation on a first database through a persistence layer; the first database is a new database or an old database; An SQL statement capture module, used to capture a target SQL statement from the persistence layer and add the target SQL statement to a queue to be executed; the target SQL statement is a SQL statement corresponding to a write operation; The second database operation module is used to, when it is determined that the main thread has completed the database operation on the first database, the asynchronous thread performs database operation on the second database according to the target SQL statement in the queue to be executed; the second database is a database for data synchronization with the first database.
9. An electronic device, characterized in that: The device comprises: a processor, and a memory communicatively connected to the processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory to implement the method according to any one of claims 1 to 7.
10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 7 when executed by a processor.