Method and device for dynamically switching multiple data sources of relational database

Abstract

The invention provides a method for dynamically switching multiple data sources of a relational database. The method comprises the following steps: S1, configuring bidirectional dts synchronization of a source instance and a new instance of a cloud database; s2, configuring full data synchronization and incremental data synchronization, and comparing cloud database source instance data with new instance data; s3, a migration library component is introduced into each application, cloud control configuration is carried out, and gradual amount-increasing migration is carried out; s4, carrying out universal capability adaptation on multiple tenants and multiple service lines by the migration library component; according to the method, dynamic data sources are dynamically switched through cloud control configuration issuing, multi-tenant and multi-service-line cloud database dynamic migration which does not need to be stopped is supported, quasi-real-time comparison is achieved for incremental data in a timed scheduling mode, and basic support is provided for database migration.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and particularly relates to a method and device for dynamically switching multiple data sources of a relational database. Background Art

[0002] In the existing data processing technologies, when different database tables need to be operated, multiple data sources usually need to be configured. However, traditional methods often require manually configuring different data sources in a configuration file and selecting which data source to use through hard coding in the program. This method is not flexible enough to meet the need for dynamically switching data sources. Therefore, a method and device for dynamically switching multiple data sources of a relational database are proposed. Summary of the Invention

[0003] In view of this, embodiments of the present invention hope to provide a method and device for dynamically switching multiple data sources of a relational database to solve or alleviate the technical problems existing in the prior art and at least provide a beneficial option.

[0004] The technical solution of the embodiments of the present invention is implemented as follows: A method for dynamically switching multiple data sources of a relational database includes the following steps:

[0005] S1. Configure bidirectional DTS synchronization between the cloud database source instance and the new instance;

[0006] S2. Configure full - volume data synchronization and incremental data synchronization, and compare the data between the cloud database source instance and the new instance;

[0007] S3. Introduce a database migration component for each application, perform cloud control configuration, and gradually release the migration;

[0008] S4. The database migration component adapts the general capabilities for multiple tenants and multiple business lines.

[0009] In some embodiments, in S1, the new instance refers to a newly added cloud database, that is, the target cloud database to be switched is the new instance.

[0010] In some embodiments, in S2, the cloud database source instance and the new instance are configured simultaneously. During the cloud database switching process, the service cannot be stopped, and all cloud databases need to support reading and writing. For the data consistency of the multi - tenant cloud database, full - volume verification and incremental verification need to be configured simultaneously for the cloud database source instance and the new instance.

[0011] In some embodiments, in S2, the steps of configuring full - volume data synchronization and incremental data synchronization are as follows,

[0012] S21. Perform data synchronization between the source database and the target database through bidirectional DTS;

[0013] S22. Incremental data collection retrieves incremental data from the binlog file, then records it through SSL logging and pulls it every 5 minutes.

[0014] S23. Parse the SSL logs to extract useful information.

[0015] S24. Through the ODPS data platform, migrate the data in the source database table to the target database table.

[0016] S25. Use the incremental data in the binlog file to update the target database table.

[0017] S26. During the migration process, there may be some differences or errors, so it is necessary to compare the incremental data to ensure the accuracy and consistency of the data.

[0018] In some embodiments, in S21, data synchronization includes database table structure synchronization, full - volume data synchronization, and incremental data synchronization.

[0019] In some embodiments, in S4, the steps when the multi - tenant application service requests the cloud database are as follows.

[0020] S41. Developers write SQL statements.

[0021] S42. The SQL statements are intercepted and processed by the Mybatis interceptor.

[0022] S43. The cloud control determines whether the gray - scale is hit.

[0023] If not hit, directly access the old database.

[0024] If hit, access the new database through Alibaba data synchronization.

[0025] In some embodiments, when the multi - tenant application service requests the cloud database, each read - write request will pass through the interceptor extended by Mybatis. The interceptor extended by Mybatis will obtain the table name serial number of the sharded table or the table name of the single table, configure the table name and serial number to be gray - scaled to the cloud control (configuration center), and the interceptor extended by Mybatis determines whether the gray - scale is hit based on whether the cloud control configuration, table name, and serial number match.

[0026] A method and device for dynamically switching multiple data sources of a relational database, including:

[0027] One or more processors;

[0028] A storage device for storing one or more programs.

[0029] When the one or more programs are executed by the one or more processors, the one or more processors implement the method for dynamically switching multiple data sources of a relational database according to any one of claims 1 to 7.

[0030] Due to the above technical solutions adopted in the embodiments of the present invention, it has the following advantages:

[0031] Through this method, the present invention dynamically switches dynamic data sources through cloud control configuration distribution, supports multi-tenants, enables dynamic migration of cloud databases without service interruption for multiple business lines, realizes quasi-real-time comparison of incremental data through a timed scheduling method, and provides basic support for database migration.

[0032] The present invention relates to a method for migrating a cloud database, aiming to provide a solution for migrating a database without service interruption in a project and to solve the problem of flexibly and dynamically switching data sources. The method includes multiple links such as data source configuration, dynamic switching, and data management. Through a preset data source management module, it can quickly and accurately switch to the target data source and complete corresponding database operations.

[0033] The above summary is only for the purpose of the specification and is not intended to be limiting in any way. In addition to the illustrative aspects, embodiments, and features described above, further aspects, embodiments, and features of the present invention will be readily apparent by reference to the drawings and the following detailed description. BRIEF DESCRIPTION OF THE DRAWINGS

[0034] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, the following will briefly introduce the drawings required for use in the description of the embodiments or the prior art. Obviously, the drawings in the following description are only some embodiments of the present application. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative efforts.

[0035] Figure 1 It is a flowchart for configuring full-volume data synchronization and incremental data synchronization of the present invention;

[0036] Figure 2 It is a flowchart for a multi-tenant application service to request a cloud database of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0037] In the following, only some exemplary embodiments are simply described. As those skilled in the art can recognize, the described embodiments can be modified in various different ways without departing from the spirit or scope of the present invention. Therefore, the drawings and the description are considered to be exemplary in nature rather than restrictive.

[0038] It should be noted that terms such as "first", "second", "symmetric", "array", etc. are only used for the purpose of distinguishing descriptions and position descriptions, and should not be construed as indicating or implying relative importance or implicitly specifying the quantity of the indicated technical features. Thus, those defined with features such as "first", "symmetric", etc. may explicitly or implicitly include one or more of such features; similarly, when there is no numerical limitation on certain features in the form of words such as "two", "three", etc., it should be noted that such features also explicitly or implicitly include one or more feature quantities;

[0039] In the present invention, unless otherwise clearly specified and defined, terms such as "install", "connect", "fix", etc. should be understood in a broad sense; for example, it may be a fixed connection, a detachable connection, or an integral molding; it may be a mechanical connection, a direct connection, a welding connection, or an indirect connection through an intermediate medium, and may be the communication inside two components or the interaction relationship between two components. For those of ordinary skill in the art, the specific meanings of the above terms in the present invention can be understood in combination with the specific circumstances according to the accompanying drawings of the specification.

[0040] The embodiments of the present invention will be described in detail below with reference to the accompanying drawings.

[0041] As Figure 1 - Figure 2 shown, the embodiments of the present invention provide a method for dynamically switching multiple data sources of a relational database, including the following steps:

[0042] S1. Configure bidirectional DTS synchronization between the cloud database source instance and the new instance;

[0043] S2. Configure full - volume data synchronization and incremental data synchronization to compare the data between the cloud database source instance and the new instance;

[0044] S3. Introduce a database migration component for each application, perform cloud control configuration, and gradually release the migration volume;

[0045] S4. The database migration component adapts the general capabilities for multiple tenants and multiple business lines.

[0046] In this embodiment, specifically, in S1, the new embodiment refers to the newly added cloud database, that is, the target cloud database to be switched is the new instance.

[0047] In this embodiment, specifically, in S2, the cloud database source instance and the new instance are configured simultaneously, and the service cannot be stopped during the cloud database switching process. All cloud databases need to support reading and writing. For the data consistency of the multi - tenant cloud database, full - volume verification and incremental verification need to be configured for both the cloud database source instance and the new instance simultaneously.

[0048] In this embodiment, specifically, in S2, the steps for configuring full - volume data synchronization and incremental data synchronization are as follows:

[0049] S21. Data synchronization is performed between the source database and the target database through two - way dts;

[0050] S22. Incremental data collection obtains incremental data from the binlog file, and then records it through SSL logs, and pulls it once every 5 minutes;

[0051] S23. Parse the SSL logs to extract useful information;

[0052] S24. Through the ODPS data platform, migrate the data in the source database table to the target database table;

[0053] S25. Use the incremental data in the binlog file to update the target database table;

[0054] S26. During the migration process, there may be some differences or errors, so it is necessary to compare the incremental data to ensure the accuracy and consistency of the data.

[0055] In this embodiment, specifically, in S21, data synchronization includes library - table structure synchronization, full - volume data synchronization, and incremental data synchronization.

[0056] In this embodiment, specifically, in S4, the steps when the multi - tenant application service requests the cloud database are as follows:

[0057] S41. Developers write SQL statements;

[0058] S42. The SQL statements are intercepted and processed by the Mybatis interceptor;

[0059] S43. The cloud control determines whether the gray - scale is hit;

[0060] If not hit, directly access the old database;

[0061] If hit, access the new database through Alibaba data synchronization.

[0062] In this embodiment, specifically, when the multi - tenant application service requests the cloud database, each read - write request will pass through the interceptor extended by Mybatis. In the interceptor extended by Mybatis, the table name serial number of the sharded table or the table name of the single table will be obtained, and the table name and serial number to be gray - scaled will be configured into the cloud control (configuration center). The interceptor extended by Mybatis determines whether the gray - scale is hit based on whether the cloud control configuration, table name, and serial number match.

[0063] In this embodiment, specifically, two-way DTS synchronization refers to a technology that enables data exchange and synchronization between two data sources in two directions. This synchronization mechanism is typically used to ensure data consistency between two databases or data storage systems. The following is a detailed explanation of two-way DTS synchronization:

[0064] Basic Concepts

[0065] Source System: The system where the original data is located, from which data is synchronized to the target system.

[0066] Target System: The system to which data will be synchronized, and it will also synchronize data back to the source system.

[0067] Synchronization Process

[0068] 1. Initial Synchronization:

[0069] Before the two-way synchronization starts, it is usually necessary to perform a full synchronization of the two systems to ensure that their data is consistent.

[0070] 2. Incremental Synchronization:

[0071] After the initial synchronization is completed, two-way DTS will track data changes (such as insert, update, and delete operations) in the two systems.

[0072] When data in the source system changes, these changes will be captured and synchronized to the target system.

[0073] Similarly, when data in the target system changes, these changes will also be captured and synchronized back to the source system.

[0074] 3. Conflict Detection and Resolution:

[0075] During the two-way synchronization process, it is possible that the two systems simultaneously make different changes to the same data item, resulting in conflicts.

[0076] The two-way DTS synchronization mechanism needs to have a set of conflict detection and resolution strategies, such as "Last Write Wins" or more complex conflict resolution algorithms.

[0077] Key Technologies

[0078] Data Change Capture: Use technologies such as triggers and log mining (such as MySQL's binlog, Oracle's Redo Log) to capture data changes.

[0079] Data Transmission: Transmit the changed data to another system through an encrypted connection.

[0080] Data Application: Apply the captured changes on the target system to maintain data consistency.

[0081] Application scenarios

[0082] Multi-data centers: Enterprises may need to have data replicas in multiple geographical locations to provide disaster recovery or load balancing.

[0083] Cloud migration: During the process of migrating data to the cloud platform, it may be necessary to maintain the consistency of data between on-premises and the cloud.

[0084] Hybrid cloud architecture: In a hybrid cloud environment, enterprises may need to synchronize data between on-premises and the cloud.

[0085] In this embodiment, specifically, SSL log parsing generally involves the following steps:

[0086] 1. Collect logs: First, it is necessary to collect log files from the SSL server or client. These log files may contain detailed information about encrypted communications, such as the handshake process, certificate exchange, key negotiation, etc.

[0087] 2. Decrypt logs: Since the communications in the SSL / TLS protocol are encrypted, the logs need to be decrypted for analysis. This generally requires having the corresponding private key or permission to decrypt the data.

[0088] 3. Analyze logs: Use appropriate tools and techniques to deeply analyze the decrypted log data to identify potential security threats, performance issues, or other concerns.

[0089] 4. Report and respond: Based on the analysis results, compile a detailed report and propose corresponding suggestions or measures to address the discovered problems or threats.

[0090] During the parsing process, the following useful information can be extracted for reference:

[0091] Session statistics: Record information such as the number of successful connections, failed attempts, and average response time.

[0092] Security metrics: Evaluate the strength of the encryption algorithms used, the validity and expiration of certificates, etc.

[0093] Anomaly detection: Identify any suspicious activity patterns, such as replay attacks, man-in-the-middle attacks, or other types of network intrusion behaviors.

[0094] Compliance check: Ensure that the SSL configuration complies with relevant security standards and best practices.

[0095] In summary, by carefully parsing SSL logs, network security can be effectively monitored and protected, and potential risks and threats can be prevented.

[0096] In this embodiment, specifically, the Binlog (Binary Log) file is a type of log file in the MySQL database. It records all the change operations of the database, including records of table structure changes (such as CREATE, ALTER, DROP operations) and data changes (such as INSERT, UPDATE, DELETE operations). The Binlog file is very important for scenarios such as master-slave replication, backup and recovery, and auditing of the database.

[0097] The following are the main characteristics and uses of the Binlog file:

[0098] Characteristics

[0099] 1. Binary format: The Binlog file is stored in binary format, so it is called "Binary Log".

[0100] 2. Configurability: MySQL allows users to configure the recording method of Binlog, including whether to enable Binlog, the format of Binlog (STATEMENT, ROW, MIXED), the maximum size and retention time of the Binlog file, etc.

[0101] 3. Position marking: The Binlog file contains position information, which can be used to identify the position of specific operations. This is very useful for replication and recovery operations.

[0102] Uses

[0103] 1. Master-slave replication: In MySQL master-slave replication, the Binlog on the master server is used to record all changes, and then the slave server reads these Binlogs and executes the same operations to maintain data consistency.

[0104] 2. Data recovery: If the database fails, the Binlog can be used to recover the data. By re-executing the operations in the Binlog, the database can be restored to the state before the failure.

[0105] 3. Auditing: The Binlog records all change operations on the database and can be used for security auditing to track the history of data changes.

[0106] 4. Data synchronization: In addition to master-slave replication, the Binlog can also be used in other data synchronization scenarios, such as synchronizing data to non-MySQL databases or other data storage systems.

[0107] Binlog format

[0108] STATEMENT: Record the SQL statement itself, rather than the specific content of the data change.

[0109] ROW: Record the specific content of the data change, that is, the change of the data row.

[0110] MIXED: Use the STATEMENT and ROW formats mixedly. MySQL will automatically select which format to use according to the specific situation of the SQL statement.

[0111] Managing Binlog

[0112] Enabling Binlog: Set the `log-bin` option in the MySQL configuration file (my.cnf or my.ini) to enable Binlog.

[0113] Viewing Binlog: Use the `SHOW BINARY LOGS;` command to view all current Binlog files, and use the `SHOW BINLOG EVENTS;` command to view the events in Binlog.

[0114] Cleaning Binlog: You can use the `PURGE BINARY LOGS` command to delete old Binlog files.

[0115] Binlog files are an important part of MySQL database management. Correctly configuring and managing Binlog is crucial for ensuring the reliability and security of the database.

[0116] In this embodiment, specifically, ODPS (Open Data Processing Service) is a big data processing service provided by Alibaba Cloud. It is a service platform for processing and analyzing large amounts of data. ODPS aims to help users easily process large-scale data sets, support operations such as data storage, computing, analysis, and machine learning, and is commonly used in scenarios such as big data analysis, data warehousing, log analysis, and data mining.

[0117] The following are the main features and functions of the ODPS data platform:

[0118] Features

[0119] 1. Large-scale data processing: ODPS can process data volumes above the PB level, suitable for large-scale data processing requirements.

[0120] 2. Elastic scaling: According to the user's needs, ODPS can automatically adjust resources to achieve elastic computing power.

[0121] 3. High availability: ODPS is designed as a highly available service to ensure the stable operation of data processing tasks.

[0122] 4. Secure and Reliable: Provide multi-level security guarantees, including data encryption, access control, audit logs, etc.

[0123] 5. Multiple Computing Models: Support multiple computing models such as SQL, MapReduce, Graph, Spark, etc.

[0124] Function

[0125] 1. MaxCompute (formerly ODPS SQL):

[0126] Provide a SQL-like query language for processing and analyzing data stored in ODPS.

[0127] Support complex data analysis operations such as JOIN, GROUP BY, window functions, etc.

[0128] 2. MaxCompute MapReduce:

[0129] Allow users to write MapReduce programs to process data, suitable for complex batch processing tasks.

[0130] 3. Graph Processing:

[0131] Used to process graph data and support the implementation of graph algorithms such as shortest path, community discovery, etc.

[0132] 4. Machine Learning:

[0133] Provide machine learning algorithms and services to support model training, prediction, and evaluation.

[0134] 5. Data Warehouse:

[0135] ODPS can be used as a data warehouse to store a large amount of structured data and perform efficient analysis.

[0136] 6. Data Integration:

[0137] Support importing data from different data sources into ODPS and exporting data in ODPS to other data storage systems.

[0138] 7. Resource Management:

[0139] Manage computing resources and storage resources, and provide usage monitoring and optimization suggestions for resources.

[0140] 8. Permission Management:

[0141] Provide fine-grained permission control to ensure data security.

[0142] The ODPS data platform is an important part of Alibaba Cloud's big data ecosystem. It provides users with powerful data processing and analysis capabilities, especially excelling in handling large-scale datasets. Through ODPS, users can easily achieve data storage, computing, analysis, and mining, thus supporting enterprises' data-driven decision-making and business development.

[0143] A method and apparatus for dynamically switching multiple data sources of a relational database, comprising:

[0144] One or more processors;

[0145] A storage device for storing one or more programs,

[0146] When the one or more programs are executed by the one or more processors, the one or more processors implement the method for dynamically switching multiple data sources of a relational database according to any one of claims 1 to 7.

[0147] As described above, it is only the specific implementation manner of the present invention, but the protection scope of the present invention is not limited thereto. Any person skilled in the art within the technical scope disclosed by the present invention can easily think of various changes or substitutions, and these should all be covered within the protection scope of the present invention. Therefore, the protection scope of the present invention should be subject to the protection scope of the claims.

Claims

1. A method for dynamically switching multiple data sources in a relational database, characterized in that: The following steps are involved: S1. Configure bidirectional DTS synchronization between the cloud database source instance and the new instance; S2. Configure full data synchronization and incremental data synchronization to compare the data of the cloud database source instance with the new instance; S3. Introduce migration components for each application, perform cloud control configuration, and gradually migrate in large quantities. S4. The migration component adapts general capabilities to multiple tenants and multiple business lines.

2. The method for dynamically switching multiple data sources of a relational database according to claim 1, characterized in that: In S1, the new implementation example refers to a newly added cloud database, that is, the target cloud database to be switched is a new instance.

3. The method for dynamically switching multiple data sources of a relational database according to claim 1, characterized in that: In S2, the cloud database source instance and the new instance are configured at the same time. The service cannot be stopped during the cloud database switching process. All cloud databases need to support reading and writing. For the data consistency of multi-tenant cloud databases, full verification and incremental verification need to be configured on the cloud database source instance and the new instance at the same time.

4. The method for dynamically switching multiple data sources of a relational database according to claim 1, characterized in that: In S2, the steps for configuring full data synchronization and incremental data synchronization are as follows: S21, data synchronization between source database and target database is performed through bidirectional DTS; S22, incremental data collection is to obtain incremental data from the binlog file, and then pull it every 5 minutes through SSL log records; S23, parsing the SSL log to extract useful information; S24. Use the ODPS data platform to migrate the data in the source database table to the target database table. S25. Use the incremental data in the binlog file to update the target database table; S26. During the migration process, there may be some differences or errors, so incremental data comparison is required to ensure data accuracy and consistency.

5. The method for dynamically switching multiple data sources of a relational database according to claim 4, characterized in that : In S21, data synchronization includes library table structure synchronization, full data synchronization and incremental data synchronization.

6. The method for dynamically switching multiple data sources of a relational database according to claim 1, characterized in that: In S4, the steps when a multi-tenant application service requests a cloud database are as follows: S41. Developers write SQL statements; S42, SQL statements are intercepted and processed through Mybatis interceptor; S43, the cloud control determines whether the grayscale is hit; If it does not hit, the old library is accessed directly; If a match is found, the new database is accessed synchronously through Alibaba Data.

7. The method for dynamically switching multiple data sources of a relational database according to claim 6, characterized in that: When a multi-tenant application service requests a cloud database, each read and write request will pass through the mybatis extended interceptor. The mybatis extended interceptor will obtain the table name and serial number of the sub-table or the table name of the single table, and configure the table name and serial number to be grayed out to the cloud control (configuration center). The mybatis extended interceptor determines whether to hit the grayscale based on whether the cloud control configuration, table name, and serial number match.

8. A method and device for dynamically switching multiple data sources in a relational database, characterized in that: include: one or more processors; a storage device for storing one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the method for dynamic switching of multiple data sources in a relational database according to any one of claims 1 to 7.

Images