Method and apparatus for generating database snapshot, electronic device and medium

Abstract

Embodiments of the application disclose a database snapshot generation method and device, electronic equipment and a medium. The method comprises: when a database snapshot generation instruction is received, detecting the shard state and transaction state of each database shard and prohibiting cross-shard transactions; when the shard state and transaction state of each database shard are normal, adding a read lock to each database; starting a transaction consistency snapshot operation to obtain transaction consistency snapshot information; taking the binlog position in the transaction consistency snapshot information as a consistency point, and globally releasing the read lock, wherein the binlog position records a database modification operation; and exporting a logical database table structure, a physical database table structure and a logical-physical topology relationship in combination with the information of the consistency point. The method can ensure the consistency of data while keeping the read-write availability of the database when the database snapshot is obtained.

Description

Technical Field

[0001] This application relates to the field of databases, and more specifically to a method, apparatus, electronic device, and medium for generating database snapshots. Background Technology

[0002] With the rapid increase in data volume, when the data becomes too large for a single-machine database to handle, a distributed database is needed. When using a distributed database, if the storage device experiences application failure or file corruption, database snapshots are required for online data recovery. Current methods for obtaining consistent snapshots have some drawbacks. For example, they require that no further updates be made to the database during the data export process, or that the application be shut down entirely. These methods cannot maintain the read and write availability of the database while obtaining a consistent snapshot. Summary of the Invention

[0003] To address the aforementioned issues, embodiments of this application provide a method, apparatus, electronic device, and medium for generating database snapshots. This allows for the generation of a database snapshot without compromising data consistency, and the export of logical and physical database table structures, as well as logical and physical topology relationships. This is crucial for database backup, restoration, and disaster recovery. Furthermore, it maintains database read / write availability while ensuring data consistency when the database snapshot is obtained.

[0004] This application discloses a method for generating a database snapshot in some embodiments, the method comprising:

[0005] Upon receiving a database snapshot generation instruction, the shard status and transaction status of each database shard are checked, and cross-shard transactions are prohibited.

[0006] When the sharding status and transaction status of each database shard are both normal, a read lock is added to each database.

[0007] Initiate a consistent snapshot operation for the transaction and obtain the consistent snapshot information of the transaction;

[0008] The binlog position in the consistency snapshot information is used as the consistency point, and the read lock is released globally. The binlog position records the database modification operations.

[0009] Based on the information of the consistency sites, the logical library table structure, physical library table structure, and logical-physical topology are derived.

[0010] Optionally, upon receiving a database snapshot generation instruction, detecting the shard status and transaction status of each database shard and prohibiting cross-shard transactions includes:

[0011] When a database snapshot generation instruction is received, a distributed lock or a distributed transaction coordinator is used to prevent cross-shard transactions.

[0012] The distributed lock coordinates transactions in the distributed system by using a lock server in the distributed system, and the distributed transaction coordinator manages transactions in the distributed system by using a transaction coordinator.

[0013] Optionally, upon receiving a database snapshot generation instruction, detecting the shard status and transaction status of each database shard and prohibiting cross-shard transactions includes:

[0014] Upon receiving a database snapshot generation instruction, an identifier variable is set within the database as a cross-shard transaction execution flag; wherein, a value of true indicates that cross-shard transaction execution is allowed, and a value of false indicates that cross-shard transaction execution is prohibited;

[0015] Set the flag variable to false to prevent cross-shard transactions from being executed.

[0016] Optionally, the step of initiating a consistent snapshot operation for a transaction, and obtaining the consistent snapshot information of the transaction, includes:

[0017] Change the isolation level of the current transaction to repeatable read;

[0018] After receiving the success message for the change, the database internal statement is executed to initiate a consistency snapshot operation for the transaction and obtain the consistency snapshot information of the transaction.

[0019] Optionally, before changing the isolation level of the current transaction to repeatable read, the method further includes:

[0020] All read operations of the database are cached in system memory to avoid dirty reads; wherein, a dirty read refers to reading uncommitted modifications of another transaction within a transaction.

[0021] Optionally, the step of using the binlog position in the consistency snapshot information as the consistency point and globally releasing the read lock, wherein the binlog position records the database modification operations, includes:

[0022] Before releasing the global read lock, unlock all tables in the database;

[0023] After receiving successful unlock messages for all tables, release the global read lock.

[0024] Optionally, after deriving the logical library table structure, physical library table structure, and logical-physical topology by combining the information of the consistency sites, the method includes:

[0025] Disconnect from the database and resume execution of cross-shard transactions.

[0026] Optionally, after deriving the logical library table structure, physical library table structure, and logical-physical topology by combining the information from the consistency sites, the method includes:

[0027] The database image is restored using the logical table structure, physical table structure, and logical-physical topology.

[0028] Optionally, the step of initiating a consistent snapshot operation for a transaction, and obtaining the consistent snapshot information of the transaction, includes:

[0029] Use version-based concurrency control technology to enable a consistent snapshot of the transaction and obtain the consistent snapshot information of the transaction;

[0030] The version-based concurrency control technology is a database technology that achieves transaction consistency by maintaining snapshots of multiple versions of the data during transaction execution.

[0031] Optionally, after deriving the logical library table structure, physical library table structure, and logical-physical topology by combining the information from the consistency sites, the method includes:

[0032] A distributed storage engine is used to store database data across multiple servers.

[0033] Some embodiments of this application provide a database client, including:

[0034] The first transaction unit and the cache management unit are used to send a database snapshot generation instruction to the database server, receive the shard status and transaction status information of each database shard returned by the database server, send an add read lock request to the database server, send the query transaction consistency snapshot information and consistency site information to the cache management unit, send the first request information for exporting the logical database table structure, physical database table structure and logical physical topology relationship, and receive the target query result returned by the database server.

[0035] Some embodiments of this application provide a database server, including:

[0036] The receiving unit is used to receive the database snapshot generation instruction and read lock request sent by the first transaction unit of the database client, and to receive the first request information sent by the cache management unit of the database client;

[0037] The processing unit is used to determine whether the query result cache corresponding to the database snapshot generation instruction is valid. If so, it sends an indication message to the first transaction unit of the database client to indicate that the query result cache corresponding to the database snapshot generation instruction is valid. This causes the first transaction unit of the database client to send the consistency snapshot information and consistency site information of the query transaction to the cache management unit of the database client to obtain the first query result. The first query result, the logical database table structure, the physical database table structure, and the logical-physical topology relationship are then merged into the target query result and returned to the cache management unit.

[0038] Some embodiments of this application provide a database snapshot generation apparatus, the apparatus comprising:

[0039] The receiving module is used to detect the shard status and transaction status of each database shard when it receives the database snapshot generation instruction, and to prohibit cross-shard transactions.

[0040] The processing module is used to add a read lock to each database when the sharding status and transaction status of each database shard are normal; to initiate a consistent snapshot operation for the transaction and obtain the consistent snapshot information of the transaction; to use the binlog position in the consistent snapshot information as the consistency point and to globally release the read lock, wherein the binlog position records the modification operation of the database;

[0041] The output module is used to derive the logical library table structure, physical library table structure, and logical-physical topology relationship by combining the information of the consistency sites.

[0042] Optionally, the receiving module is further configured to:

[0043] When a database snapshot generation instruction is received, a distributed lock or a distributed transaction coordinator is used to prevent cross-shard transactions.

[0044] The distributed lock coordinates transactions in the distributed system by using a lock server in the distributed system, and the distributed transaction coordinator manages transactions in the distributed system by using a transaction coordinator.

[0045] Optionally, the receiving module is further configured to:

[0046] Upon receiving a database snapshot generation instruction, an identifier variable is set within the database as a cross-shard transaction execution flag; wherein, a value of true indicates that cross-shard transaction execution is allowed, and a value of false indicates that cross-shard transaction execution is prohibited;

[0047] Set the flag variable to false to prevent cross-shard transactions from being executed.

[0048] Optionally, the processing module is further configured to:

[0049] Change the isolation level of the current transaction to repeatable read;

[0050] After receiving the success message for the change, the database internal statement is executed to initiate a consistency snapshot operation for the transaction and obtain the consistency snapshot information for the transaction.

[0051] Optionally, the processing module is further configured to:

[0052] All read operations of the database are cached in system memory to avoid dirty reads; wherein, a dirty read refers to reading uncommitted modifications of another transaction within a transaction.

[0053] Optionally, the processing module is further configured to:

[0054] Before releasing the global read lock, unlock all tables in the database;

[0055] After receiving successful unlock messages for all tables, the global read lock is released.

[0056] Optionally, the output module is further configured to:

[0057] Disconnect from the database and resume execution of cross-shard transactions.

[0058] Optionally, the output module is further configured to:

[0059] The database image is restored using the logical table structure, physical table structure, and logical-physical topology.

[0060] Optionally, the receiving module is further configured to:

[0061] Use version-based concurrency control technology to enable a consistent snapshot of the transaction and obtain the consistent snapshot information of the transaction;

[0062] The version-based concurrency control technology is a database technology that achieves transaction consistency by maintaining snapshots of multiple versions of the data during transaction execution.

[0063] Optionally, the output module is further configured to:

[0064] A distributed storage engine is used to store database data across multiple servers.

[0065] This application also provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, it implements the steps of any of the above-described methods for generating database snapshots.

[0066] This application also provides a computer-readable storage medium storing a computer program thereon, which, when executed by a processor, implements the steps of the database snapshot generation method described above.

[0067] The database snapshot generation method, system, electronic device, and medium provided in this application can generate a database snapshot without compromising database data consistency, and export the logical table structure, physical table structure, and logical-physical topology. This is of great significance for database backup, restoration, and disaster recovery. Furthermore, it maintains database read / write availability while ensuring data consistency when obtaining the database snapshot.

[0068] The above description is only an overview of the technical solution of this application. In order to better understand the technical means of this application and to implement it in accordance with the contents of the specification, and to make the above and other objects, features and advantages of this application more obvious and understandable, the following are specific embodiments of this application. Attached Figure Description

[0069] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0070] Figure 1 The schematic diagram illustrates a flowchart of a method for generating a database snapshot provided in some embodiments of this application;

[0071] Figure 2 This illustration schematically shows one of the flowcharts of another method for generating a database snapshot provided in some embodiments of this application;

[0072] Figure 3 The second schematic diagram illustrates another method for generating a database snapshot provided in some embodiments of this application;

[0073] Figure 4 The diagram illustrates a database client structure for another method of generating a database snapshot provided in some embodiments of this application.

[0074] Figure 5The diagram illustrates the structure of a database client for a database snapshot generation method provided in some embodiments of this application.

[0075] Figure 6 The diagram illustrates the structure of a database server for a database snapshot generation method provided in some embodiments of this application.

[0076] Figure 7 The schematic diagram illustrates the structure of a database snapshot generation apparatus provided in some embodiments of this application;

[0077] Figure 8 A block diagram schematically illustrates a computing processing apparatus for performing methods according to some embodiments of this application;

[0078] Figure 9 A storage unit for holding or carrying program code implementing methods according to some embodiments of this application is illustrated schematically. Detailed Implementation

[0079] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0080] Figure 1 The illustration schematically shows a flowchart of a method for generating a database snapshot according to some embodiments of this application, the method including:

[0081] Step 101: Upon receiving the database snapshot generation instruction, check the sharding status and transaction status of each database shard, and prohibit cross-shard transactions.

[0082] It's important to note the following: Distributed Database: A distributed database is a database system that stores data across multiple nodes. It's typically used to handle large datasets, offering higher scalability and availability. Snapshot: A snapshot is a copy of the database, which can be used for data recovery, data analysis, and other purposes. Sharding: Sharding is a concept in distributed databases, referring to dividing a database table or index into several parts according to certain rules, with each part stored on a different node. Transaction: A transaction is a database concept referring to a set of modification operations on data that either all execute or all roll back. Transactions ensure data consistency and integrity.

[0083] In this embodiment, it is necessary to detect the shard status and transaction status of each database shard. Shard status refers to whether the database shard is currently available. Transaction status refers to whether the current transaction is normal, such as whether it is locked or has timed out. If an abnormal database shard or transaction status is detected, then the execution of cross-shard transactions should be prohibited to avoid affecting database inconsistency. For example, suppose we have a distributed database containing two database shards, A and B. When a database snapshot generation instruction is received, the shard status and transaction status of database shards A and B are detected. If the shard status of shard A is "normal," but the shard status of shard B is "faulty," then snapshot generation will be prohibited because it cannot be guaranteed that all shards are in the same state. If the shard status of all shards is normal, but cross-shard transactions exist, then snapshot generation will also be prohibited because these transactions may still be in progress when the snapshot is generated, leading to data inconsistency.

[0084] Step 102: When the sharding status and transaction status of each database shard are both normal, add a read lock to each database.

[0085] It's important to note that in distributed databases, database sharding refers to the process of dividing data in a database into multiple parts according to certain rules and storing them across different database instances. Shard status refers to the operational status of each shard, such as whether it's functioning normally or in an abnormal state. Transaction status refers to the execution status of transactions in the current database, such as whether it's in a committed state or a rollback state. In databases, locks are a mechanism used to protect data integrity and consistency during concurrent access to the database. There are various types of locks, among which a read lock allows multiple transactions to read data simultaneously but does not allow any transaction to modify the data. In this step, we add a read lock to the database, meaning that during the process of generating a database snapshot, other transactions can only read the data and cannot modify it.

[0086] In this embodiment, upon receiving the database snapshot generation instruction and ensuring that the shard status and transaction status of all database shards are normal, a read lock can be added to each database shard to prevent data updates during snapshot generation. The read lock guarantees that the database shard can only be read, not modified, during snapshot generation, thus ensuring the consistency of the snapshot data. For example, suppose we have a distributed database containing three database shards A, B, and C. Upon receiving the snapshot generation instruction, if the status and transaction status of A, B, and C are normal, a read lock can be added to each database shard to ensure that the data in A, B, and C will not be modified during snapshot generation.

[0087] Step 103: Initiate a consistent snapshot operation for the transaction and obtain the consistent snapshot information of the transaction.

[0088] It should be noted that a transaction is a set of operations used to guarantee the atomicity, consistency, isolation, and durability of database operations.

[0089] In this embodiment, a consistent snapshot operation is initiated on all database shards. A consistent snapshot refers to a complete snapshot of all data that can be queried within a single transaction. This means that after enabling the consistent snapshot operation, no modification operations can be performed on any database shard until the transaction ends. By obtaining the consistent snapshot information of all database shards, we can obtain a complete snapshot containing all data after the transaction ends. This ensures data consistency during the transaction; that is, the data state in the database remains fixed before the transaction is committed.

[0090] Step 104: Use the binlog position in the consistency snapshot information as the consistency point and globally release the read lock. The binlog position records the database modification operations.

[0091] It's important to note that Binlog is the binary log of a MySQL database, recording all database modifications, including insert, delete, and update operations. Binlog helps restore the database to a specific point in time. It greatly improves database availability and fault tolerance. By obtaining the binlog position of the current transaction, we can know all the modifications performed on the database before the current transaction, thus obtaining a consistent snapshot of the database.

[0092] In this embodiment, for example, after performing a series of modification operations, we can extract the binlog position of the current transaction to obtain all the modification operations performed on the database before the current transaction. In this way, we can use these modification operations to restore database consistency and obtain a consistent snapshot of the database without affecting its read / write availability. Next, we need to globally release all read locks. This ensures the read / write availability of the database and avoids unnecessary impact on the database during the snapshot acquisition process.

[0093] Step 105: Derive the logical library table structure, physical library table structure, and logical-physical topology relationship by combining the information of the consistency sites.

[0094] It should be noted that the embodiments in this application require the use of so-called consistency points. A consistency point refers to the location (usually a binlog position) where the last modification operation of the database is recorded during the generation of a database snapshot. This location is called a consistency point because it records the consistency state of the database at the time of snapshot generation. When using consistency points, the database snapshot can be obtained by exporting the logical database table structure, physical database table structure, and logical-physical topology. The logical database table structure refers to the structure of tables in the logical database of the distributed database, including information such as table field names, types, and lengths. The physical database table structure refers to the structure of tables in the physical database of the distributed database, also including information such as table field names, types, and lengths. The logical-physical topology refers to the relationship between the logical database and the physical database in the distributed database, including which tables belong to the logical database, which tables belong to the physical database, and the association relationships between these tables.

[0095] In this embodiment of the application, for example, we assume that our distributed database has a logical database named "customer_database", which contains two tables: "customers" and "orders". The "customers" table contains all customer information, including customer name, address, and phone number. The "orders" table contains all order information, including order number, product name, and quantity. When we export the information of this logical database, we obtain the table structure of these two tables, as well as the logical and physical topology between them. That is, we know which fields the "customers" table contains and their data types. We also know which fields the "orders" table contains and their data types. Furthermore, we know that the "customer_id" field in the "orders" table and the "id" field in the "customers" table have a foreign key relationship; that is, the "customer_id" field in the "orders" table can only contain "id" values ​​that already exist in the "customers" table. This information is obtained by exporting the logical database table structure, physical database table structure, and logical and physical topology.

[0096] In this embodiment, the above-described method allows us to generate a database snapshot without compromising data consistency, and to export the logical table structure, physical table structure, and logical-physical topology. This is crucial for database backup, restoration, and disaster recovery. Furthermore, it maintains database read / write availability while ensuring data consistency when obtaining the snapshot.

[0097] Optionally, step 101 includes:

[0098] When a database snapshot generation instruction is received, a distributed lock or a distributed transaction coordinator is used to prevent cross-shard transactions.

[0099] The distributed lock coordinates transactions in the distributed system by using a lock server in the distributed system, and the distributed transaction coordinator manages transactions in the distributed system by using a transaction coordinator.

[0100] In this embodiment, upon receiving a database snapshot generation instruction, it is necessary to check the shard status and transaction status of each database shard and prohibit cross-shard transactions. This can be achieved using distributed locks or a distributed transaction coordinator. A distributed lock is a locking mechanism used in distributed systems, where a lock server coordinates transactions. When a process attempts to acquire a lock, the lock server checks its availability; if available, it grants the lock to the process; otherwise, it rejects the lock acquisition request. A distributed transaction coordinator is a transaction management mechanism used in distributed systems, managing transactions within the system. The transaction coordinator tracks the status of each transaction and coordinates dependencies between transactions as needed. Thus, when a transaction executes, the transaction coordinator checks whether other transactions have completed.

[0101] In this embodiment of the application, cross-shard transactions are prohibited by using distributed locks or distributed transaction coordinators, which can ensure the consistency of data during the database snapshot generation process, thereby facilitating subsequent data recovery.

[0102] Optionally, step 101 includes:

[0103] Step A1: Upon receiving the database snapshot generation instruction, an identifier variable is set within the database as a cross-shard transaction execution flag; wherein, a value of true indicates that cross-shard transaction execution is allowed, and a value of false indicates that cross-shard transaction execution is prohibited.

[0104] In this embodiment of the application, for example, the variable setting can be implemented by creating a table in the database. The table contains a boolean field named "cross_shard_transaction_flag" to record whether cross-shard transaction execution is allowed. This table can be created using the following SQL statement: `CREATE TABLE cross_shard_transaction_flag(cross_shard_transaction_flag BOOLEAN NOT NULL);` Then, when a database snapshot generation instruction is received, the value of the flag variable can be set using the following SQL statement: `UPDATE cross_shard_transaction_flag SET cross_shard_transaction_flag = false;` In this way, when the cross-shard transaction execution flag is false, cross-shard transactions are prohibited from execution.

[0105] Step A2: Set the flag variable to false to prevent cross-shard transactions from being executed.

[0106] In this embodiment, a storage method (such as a database table, cache server, file system, etc.) is used to store the flag variables. The values ​​of the flag variables are updated using database queries or relevant APIs of programming languages. For example, if we use a database table to store the flag variables, we can execute the following SQL statement to set the flag variable to false: `UPDATE flags SET value=false WHERE name='allow_cross_shard_transactions'`. If we use a cache server to store the flag variables, we can use the relevant APIs of the cache server to update the values ​​of the flag variables. For example, if we use Redis (Remote Dictionary Server) as a cache server, we can use the following code to set the flag variable to false: `redis.set('allow_cross_shard_transactions','false')`. Redis, short for Remote Dictionary Server, is an open-source in-memory database that provides a data structure for storing key-value pairs. It can be used to store various types of data, including strings, lists, sets, sorted sets, hash tables, bitmaps, and counters. Its characteristics include high speed, the ability to store data in memory, and support for persisting data to disk. It is commonly used as a cache server, but can also be used as a message queue, counter, distributed lock, publish-subscribe system, etc.

[0107] In this embodiment of the application, by setting a cross-shard transaction execution flag inside the database, we can prohibit the execution of cross-shard transactions when generating database snapshots, thereby ensuring that the generated snapshots are consistent.

[0108] Optionally, such as Figure 2 As shown, step 103 includes:

[0109] Step 201: Change the isolation level of the current transaction to repeatable read;

[0110] In this embodiment, the isolation level of the current transaction is set to repeatable read at the start of the transaction. Exemplarily, this can be achieved by using "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ" in the SQL statement. During the execution of the transaction, it is necessary to ensure that the isolation level of the current transaction remains repeatable read at all times. This means that all queries executed within the current transaction will obtain consistent data results, and no other transaction will modify the data before the current transaction is committed. The isolation level setting of the current transaction is cleared when the transaction is committed or rolled back.

[0111] Step 202: After receiving the success message of the change, execute the internal statement of the database to start the consistency snapshot operation of the transaction and obtain the consistency snapshot information of the transaction.

[0112] In this embodiment, an internal database statement is required to initiate a consistent snapshot operation for the transaction. Specifically, the SQL statement `START TRANSACTION WITH CONSISTENT SNAPSHOT` can be used to initiate the consistent snapshot operation. After executing this statement, the consistent snapshot information of the current transaction can be obtained. The obtained consistent snapshot information includes the start position of the transaction, the isolation level of the transaction, and log information of all operations performed within the transaction. This information will be used to roll back or commit the transaction during subsequent data recovery.

[0113] In this embodiment, by performing the above steps, it can be guaranteed that consistent data results are obtained in the current transaction, and no other transaction will modify the data before commit. This helps to avoid dirty reads, non-repeatable reads, and phantom reads.

[0114] Optionally, before step 201, the method further includes:

[0115] All read operations of the database are cached in system memory to avoid dirty reads; wherein, a dirty read refers to reading uncommitted modifications of another transaction within a transaction.

[0116] In this embodiment, it is also typically necessary to cache all database read operations in system memory to avoid dirty reads. This means that within the current transaction, the data read comes from the system memory cache, rather than being read directly from the database. This avoids reading uncommitted modifications from another transaction within the same transaction.

[0117] In this embodiment, the above-described method creates a cache in system memory containing all read operations. This allows subsequent transaction operations to directly use the cached data without needing to read from the database again. This significantly improves transaction processing efficiency.

[0118] Optionally, step 104 includes:

[0119] Step S1: Unlock all tables in the database before releasing the global read lock;

[0120] In this embodiment, it is necessary to first check which tables in the database are locked, and then perform unlocking operations on these tables. The specific unlocking operation may vary depending on the database type and version, but it is generally achieved by executing a specific SQL statement. For example, in a MySQL database, the "UNLOCKTABLES" statement can be used to unlock tables. After executing this statement, MySQL will release all currently used table locks so that these tables can be locked again in subsequent operations. After this step is completed, the global read lock can be released, allowing other transactions to operate on this database. Unlocking tables is crucial for ensuring the normal operation of the database, because if table locks are held indefinitely, other transactions cannot access these tables, which may lead to database performance degradation or database malfunction.

[0121] Step S2: After receiving successful unlock information for all tables, release the global read lock.

[0122] In this embodiment, for example, the "RELEASE LOCK" statement can be used to release a global read lock in a MySQL database. After executing this statement, MySQL will release the global read lock so that the database can be locked again in subsequent operations. During the process of releasing the global read lock, a lock server or distributed transaction coordinator in a distributed system can be used to maintain a lock list that records the locks currently in use. When releasing the global read lock, the global read lock is removed from the lock list. This ensures that all locked tables are unlocked after the global read lock is released. In addition, before releasing the global read lock, some necessary cleanup operations can be performed internally in the database, such as clearing temporary tables and closing cursors, to ensure that the database state is consistent after the global read lock is released.

[0123] In this embodiment of the application, the global read lock can be released through the above steps so that other transactions can operate on the database.

[0124] Optionally, after step 105, the method includes:

[0125] Disconnect from the database and resume execution of cross-shard transactions.

[0126] It's important to note that disconnecting the connection is to prevent consistent snapshots from consuming excessive system resources. For large databases, exporting a consistent snapshot can consume significant memory and CPU resources; without disconnecting, other read and write operations might be affected. Furthermore, resuming cross-shard transactions ensures the database continues to function normally after the snapshot is generated. Cross-shard transactions play a crucial role in databases; failing to resume their execution could disrupt read / write operations and transaction execution.

[0127] In this embodiment, before disconnecting from the database, a statement needs to be executed to restore the ability to execute cross-shard transactions. For example, in a MySQL database, the "SET SESSION TRANSACTIONISOLATION LEVEL READ COMMITTED" statement can be used to restore the ability to execute cross-shard transactions. After executing this statement, MySQL sets the isolation level of the current transaction to repeatable read, thereby allowing the execution of cross-shard transactions. Then, the "DISCONNECT" statement can be used to disconnect from the database and clear all cached data after disconnection, so that the database can be reconnected in subsequent operations.

[0128] Optionally, after step 105, the method includes:

[0129] The database image is restored using the logical table structure, physical table structure, and logical-physical topology.

[0130] It's worth noting that restoring a database mirror is also essential in horizontal scaling scenarios. This is because as the amount of data or the volume of access to the database increases, its performance may degrade. In such cases, it's necessary to distribute the database data across multiple servers to improve performance. Restoring a database mirror can be used to migrate database data to a new server, thereby achieving horizontal scaling of the database.

[0131] In this embodiment, tables are created in the database based on the logical table structure, physical table structure, and logical-physical topology. This may require using internal database statements such as "CREATE TABLE" or "ALTER TABLE". Appropriate constraints, such as foreign key constraints and unique constraints, are established in the database based on the logical table structure and logical-physical topology. Next, table data recorded in the physical tables can be inserted into the corresponding logical tables one by one. During the data insertion process, it is necessary to adhere to the logical-physical topology to ensure data consistency and correctness.

[0132] During database mirror restoration, multi-threading can be used to accelerate data insertion and improve recovery efficiency. Additionally, batch insert functionality can be used to insert multiple records at once, further enhancing efficiency. After all data insertion is complete, checkpointing can be used to record database modifications to a log file for later use.

[0133] In this embodiment, the database mirror is restored through the above process, ensuring consistency between the restored database and the original database. This ensures that no important data is lost and no data inconsistencies occur during the database recovery process.

[0134] Optionally, step 202 includes:

[0135] Use version-based concurrency control technology to enable a consistent snapshot of the transaction and obtain the consistent snapshot information of the transaction;

[0136] The version-based concurrency control technology is a database technology that achieves transaction consistency by maintaining snapshots of multiple versions of the data during transaction execution.

[0137] It's important to note that Version Concurrency Control (VCC) is a database technology that achieves transaction consistency by maintaining multiple snapshots of data during transaction execution. VCC means that when two transactions modify the same record simultaneously, to avoid conflicts, the system generates a version number for each transaction and only writes the changes back to the database when the transactions are committed. Thus, when a transaction reads a record, the system determines which version of the record to return based on the transaction's version number.

[0138] In this embodiment of the application, for example, in a MySQL database, the "START TRANSACTIONWITH CONSISTENT SNAPSHOT" statement can be used to initiate a consistent snapshot of a transaction. After executing this statement, MySQL will obtain the consistent snapshot information of the current transaction and ensure that the data read in subsequent operations is consistent.

[0139] Optionally, after step 105, the following is included:

[0140] A distributed storage engine is used to store database data across multiple servers.

[0141] In this embodiment, for example, the NDB storage engine is used to store database data across multiple servers. NDB (Network Database) is a storage engine in the MySQL database management system that can store data across multiple machines and access this data using the MySQL protocol. The NDB storage engine can be integrated with MySQL Server or deployed independently. It is a distributed storage engine capable of supporting large-scale concurrent access and providing high availability, scalability, and data consistency. The NDB storage engine can be used to store various types of data, including numerical values, strings, and binary data. It also provides some special storage types, such as date, time, and precise numerical values.

[0142] In this embodiment, by using a distributed storage engine, data can be distributed across multiple machines and accessed using the MySQL protocol. This is used to store large amounts of data and support data access in high-concurrency scenarios.

[0143] like Figure 3As shown, Apache ZooKeeper is an open-source distributed coordination service. It provides an efficient mechanism to coordinate many processes in a distributed system. ZooKeeper provides a simple interface to implement functions such as distributed locks, distributed queues, and distributed naming services. ZooKeeper also provides highly reliable data consistency services, enabling data synchronization between multiple processes. Distributed database node 1 and distributed database node 2 both include dn1, dn2, and dn3, meaning that both distributed database node 1 and distributed database node 2 have three data nodes, namely dn1, dn2, and dn3. These three data nodes play the same role in distributed database node 1 and distributed database node 2, and are responsible for storing and maintaining the data in the database.

[0144] like Figure 4 As shown in this embodiment, for example, verification is performed using the Tianyi Cloud distributed database udal. Two instances, udal1 and udal2, need to be deployed. Each instance has two nodes and is associated with two Tianyi Cloud databases (RDS). ZooKeeper is used as the message center to store the topological relationship between logical and physical database tables. Before implementation, it is necessary to ensure smooth network connectivity between instance nodes and that the nodes are running normally. UDAL is a distributed database system that provides users with a way to manage data in a distributed environment. UDAL can improve data availability and performance by distributing data across multiple servers. A key feature of UDAL is its ability to horizontally shard data, providing high-performance read and write capabilities in a distributed environment. UDAL also features high availability and automatic failover capabilities, ensuring data protection in the event of system failure. RDS stands for Amazon Relational Database Service. RDS is a relational database service provided by Amazon that helps users manage and run MySQL, MariaDB, PostgreSQL, Oracle, and Microsoft SQL Server databases in the cloud. RDS can automatically back up, restore, expand, and scale databases, and provides free built-in availability and security features, thus simplifying the process of database management for users.

[0145] Specifically, check the status of all shards and transactions, and prohibit cross-shard transactions; add read locks to all databases; you can use the `FLUSH TABLES WITH READ LOCK` statement, which will release the global lock quickly after a consistent snapshot of the transaction is enabled. Change the repeatable read transaction isolation level and enable consistent snapshots of transactions; you can use `SETSESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ` to set the RR isolation level, and then use `START TRANSACTION WITH CONSISTENT SNAPSHOT` to enable a consistent snapshot of the MySQL database transactions.

[0146] Furthermore, the binlog position is acquired as the consistency position, and the global read lock is released. The binlog position information for each shard can be obtained using `UDAL SHOWMASTER STATUS`, mainly including the binlog file name, gtid, and position. Based on the consistency position, the logical database table structure, physical database table structure, and logical-physical topology are derived, and the data for each shard is exported sequentially. The logical-physical topology stored in ZooKeeper is obtained, converted into a topology object, and exported. The table structure is exported using `show create table`, and the table data is exported using `SELECT * FROM tab`. Finally, after the export is complete, the connection is closed, and cross-shard transactions are allowed again.

[0147] Figure 5 The schematic diagram illustrates the structure of a database client 1 for a database snapshot generation method provided in some embodiments of this application, including:

[0148] The first transaction unit 11 and the cache management unit 12 are used to send a database snapshot generation instruction to the database server, receive the shard status and transaction status information of each database shard returned by the database server, send an add read lock request to the database server, send the consistency snapshot information and consistency site information of the query transaction to the cache management unit, send the first request information for exporting the logical database table structure, physical database table structure and logical physical topology relationship, and receive the target query result returned by the database server.

[0149] Figure 6 The diagram illustrates the structure of a database server 2 for a database snapshot generation method provided in some embodiments of this application. The database server 2 includes:

[0150] Receiving unit 21 is used to receive a database snapshot generation instruction and a read lock request sent by the first transaction unit of the database client, and to receive the first request information sent by the cache management unit of the database client;

[0151] Processing unit 22 is used to determine whether the query result cache corresponding to the database snapshot generation instruction is valid. If so, it sends an indication message to the first transaction unit 11 of the database client to indicate that the query result cache corresponding to the database snapshot generation instruction is valid. This causes the first transaction unit 11 of the database client to send the consistency snapshot information and consistency site information of the query transaction to the cache management unit 12 of the database client to obtain the first query result. The first query result, the logical database table structure, the physical database table structure, and the logical-physical topology relationship are merged into the target query result and returned to the cache management unit 12.

[0152] Figure 7 The schematic diagram illustrates the structure of a database snapshot generation apparatus 30 provided in some embodiments of this application. The apparatus 30 includes:

[0153] The receiving module 301 is used to detect the sharding status and transaction status of each database shard when it receives the database snapshot generation instruction, and to prohibit cross-shard transactions.

[0154] The processing module 302 is used to add a read lock to each database when the sharding status and transaction status of each database shard are normal; to enable the consistency snapshot operation of the transaction and obtain the consistency snapshot information of the transaction; to use the binlog position in the consistency snapshot information as the consistency point and to globally release the read lock, wherein the binlog position records the modification operation of the database;

[0155] The output module 303 is used to derive the logical library table structure, physical library table structure, and logical-physical topology relationship by combining the information of the consistency site.

[0156] Optionally, the receiving module 301 is further configured to:

[0157] When a database snapshot generation instruction is received, a distributed lock or a distributed transaction coordinator is used to prevent cross-shard transactions.

[0158] The distributed lock coordinates transactions in the distributed system by using a lock server in the distributed system, and the distributed transaction coordinator manages transactions in the distributed system by using a transaction coordinator.

[0159] Optionally, the receiving module 301 is further configured to:

[0160] Upon receiving a database snapshot generation instruction, an identifier variable is set within the database as a cross-shard transaction execution flag; wherein, a value of true indicates that cross-shard transaction execution is allowed, and a value of false indicates that cross-shard transaction execution is prohibited;

[0161] Set the flag variable to false to prevent cross-shard transactions from being executed.

[0162] Optionally, the processing module 302 is further configured to:

[0163] Change the isolation level of the current transaction to repeatable read;

[0164] After receiving the success message for the change, the database internal statement is executed to initiate a consistency snapshot operation for the transaction and obtain the consistency snapshot information of the transaction.

[0165] Optionally, the processing module 302 is further configured to:

[0166] All read operations of the database are cached in system memory to avoid dirty reads; wherein, a dirty read refers to reading uncommitted modifications of another transaction within a transaction.

[0167] Optionally, the processing module 302 is further configured to:

[0168] Before releasing the global read lock, unlock all tables in the database;

[0169] After receiving successful unlock messages for all tables, release the global read lock.

[0170] Optionally, the output module 303 is further configured to:

[0171] Disconnect from the database and resume execution of cross-shard transactions.

[0172] Optionally, the output module 303 is further configured to:

[0173] The database image is restored using the logical table structure, physical table structure, and logical-physical topology.

[0174] Optionally, the receiving module 301 is further configured to:

[0175] Use version-based concurrency control technology to enable a consistent snapshot of the transaction and obtain the consistent snapshot information of the transaction;

[0176] The version-based concurrency control technology is a database technology that achieves transaction consistency by maintaining snapshots of multiple versions of the data during transaction execution.

[0177] In this embodiment, the above-described method allows us to generate a database snapshot without compromising data consistency, and to export the logical table structure, physical table structure, and logical-physical topology. This is crucial for database backup, restoration, and disaster recovery. Furthermore, it maintains database read / write availability while ensuring data consistency when obtaining the snapshot.

[0178] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. Those skilled in the art can understand and implement this without any creative effort.

[0179] The various component embodiments of this application can be implemented in hardware, or as software modules running on one or more processors, or a combination thereof. Those skilled in the art will understand that microprocessors or digital signal processors (DSPs) can be used in practice to implement some or all of the functions of some or all of the components in the computing processing device according to the embodiments of this application. This application can also be implemented as a device or apparatus program (e.g., a computer program and computer program product) for performing part or all of the methods described herein. Such an implementation of this application can be stored on a non-transient computer-readable medium, or can be in the form of one or more signals. Such signals can be downloaded from an Internet website, provided on a carrier signal, or provided in any other form.

[0180] For example, Figure 8 A computing processing apparatus is shown that can implement the methods according to this application. This computing processing apparatus conventionally includes a processor 410 and a computer program product or non-transitory computer-readable medium in the form of a memory 420. The memory 420 may be an electronic memory such as flash memory, EEPROM (Electrically Erasable Programmable Read-Only Memory), EPROM, hard disk, or ROM. The memory 420 has a storage space 430 for program code 431 for performing any of the method steps described above. For example, the storage space 430 for program code may include various program codes 431 respectively for implementing the various steps in the methods described above. These program codes can be read from or written to one or more computer program products. These computer program products include program code carriers such as hard disks, CDs, memory cards, or floppy disks. Such computer program products are typically as shown in the reference. Figure 9 The portable or fixed storage unit. This storage unit may have the same characteristics as... Figure 8 The memory 420 in the computing processing device is similarly arranged as storage segments, storage spaces, etc. Program code can be compressed, for example, in an appropriate form. Typically, the storage unit includes computer-readable code 431', that is, code that can be read by a processor such as 410, which, when run by the computing processing device, causes the computing processing device to perform the various steps in the methods described above.

[0181] It should be understood that although the steps in the flowcharts of the accompanying figures are shown sequentially as indicated by the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the accompanying figures may include multiple sub-steps or multiple stages. These sub-steps or stages are not necessarily completed at the same time, but can be executed at different times, and their execution order is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the sub-steps or stages of other steps.

[0182] The terms "an embodiment," "embodiment," or "one or more embodiments" as used herein mean that a particular feature, structure, or characteristic described in connection with an embodiment is included in at least one embodiment of this application. Furthermore, please note that the examples of the phrase "in one embodiment" do not necessarily all refer to the same embodiment.

[0183] Numerous specific details are set forth in the specification provided herein. However, it will be understood that embodiments of this application may be practiced without these specific details. In some instances, well-known methods, structures, and techniques have not been shown in detail so as not to obscure the understanding of this specification.

[0184] In the claims, any reference signs placed between parentheses should not be construed as limiting the claims. The word "comprising" does not exclude the presence of elements or steps not listed in the claims. The word "a" or "an" preceding an element does not exclude the presence of a plurality of such elements. This application can be implemented by means of hardware comprising several different elements and by means of a suitably programmed computer. In a unit claim enumerating several means, several of these means may be embodied by the same item of hardware. The use of the words first, second, and third, etc., does not indicate any order. These words can be interpreted as names.

[0185] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of this application, and are not intended to limit them. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application.

Claims

1. A method for generating a database snapshot, characterized in that, The method includes: Upon receiving a database snapshot generation instruction, the shard status and transaction status of each database shard are checked, and cross-shard transactions are prohibited. When the sharding status and transaction status of each database shard are both normal, a read lock is added to each database. Initiate a consistent snapshot operation for the transaction and obtain the consistent snapshot information of the transaction; The binlog position in the consistency snapshot information is used as the consistency point, and the read lock is released globally. The binlog position records the database modification operations. Based on the information of the consistency sites, the logical library table structure, physical library table structure, and logical-physical topology are derived.

2. The method according to claim 1, characterized in that, Upon receiving a database snapshot generation instruction, the step of detecting the shard status and transaction status of each database shard and prohibiting cross-shard transactions includes: When a database snapshot generation instruction is received, a distributed lock or a distributed transaction coordinator is used to prevent cross-shard transactions. The distributed lock coordinates transactions in the distributed system by using a lock server in the distributed system, and the distributed transaction coordinator manages transactions in the distributed system by using a transaction coordinator.

3. The method according to claim 1, characterized in that, Upon receiving a database snapshot generation instruction, the step of detecting the shard status and transaction status of each database shard and prohibiting cross-shard transactions includes: Upon receiving a database snapshot generation instruction, an identifier variable is set within the database as a cross-shard transaction execution flag; wherein, a value of true indicates that cross-shard transaction execution is allowed, and a value of false indicates that cross-shard transaction execution is prohibited; Set the flag variable to false to prevent cross-shard transactions from being executed.

4. The method according to claim 1, characterized in that, The operation of initiating a consistent snapshot of a transaction, which obtains the consistent snapshot information of the transaction, includes: Change the isolation level of the current transaction to repeatable read; After receiving the success message for the change, the database internal statement is executed to initiate a consistency snapshot operation for the transaction and obtain the consistency snapshot information of the transaction.

5. The method according to claim 4, characterized in that, Before changing the isolation level of the current transaction to repeatable read, the method further includes: All read operations of the database are cached in system memory to avoid dirty reads; wherein, a dirty read refers to reading uncommitted modifications of another transaction within a transaction.

6. The method according to claim 1, characterized in that, The step involves using the binlog position in the consistent snapshot information as the consistency point and globally releasing the read lock. The binlog position records the database modification operations, including: Before releasing the global read lock, unlock all tables in the database; After receiving successful unlock messages for all tables, release the global read lock.

7. The method according to claim 1, characterized in that, After deriving the logical library table structure, physical library table structure, and logical-physical topology by combining the information of the consistency sites, the method includes: Disconnect from the database and resume execution of cross-shard transactions.

8. The method according to claim 1, characterized in that, After deriving the logical library table structure, physical library table structure, and logical-physical topology by combining the information of the consistency sites, the method includes: The database image is restored using the logical table structure, physical table structure, and logical-physical topology.

9. The method according to claim 1, characterized in that, The operation of initiating a consistent snapshot of a transaction, which obtains the consistent snapshot information of the transaction, includes: Use version-based concurrency control technology to enable a consistent snapshot of the transaction and obtain the consistent snapshot information of the transaction; The version-based concurrency control technology is a database technology that achieves transaction consistency by maintaining snapshots of multiple versions of the data during transaction execution.

10. The method according to claim 1, characterized in that, After deriving the logical library table structure, physical library table structure, and logical-physical topology by combining the information of the consistency sites, the method includes: A distributed storage engine is used to store database data across multiple servers.

11. A database client, characterized in that, include: First transaction unit and cache management unit; The first transaction unit is used to send a database snapshot generation instruction to the database server, receive the shard status and transaction status information of each database shard returned by the database server, and send an add read lock request to the database server. Send the consistency snapshot information and consistency site information of the query transaction to the cache management unit, send the first request information to export the logical database table structure, physical database table structure and logical physical topology relationship, and receive the target query result returned by the database server. The consistency site information is the binlog position in the consistency snapshot information as the consistency site, and the read lock is released globally. The binlog position records the database modification operation.

12. A database server, characterized in that, include: The receiving unit is used to receive the database snapshot generation instruction and read lock request sent by the first transaction unit of the database client, and to receive the first request information sent by the cache management unit of the database client; The processing unit is used to determine whether the query result cache corresponding to the database snapshot generation instruction is valid. If so, it sends an indication message to the first transaction unit of the database client to indicate that the query result cache corresponding to the database snapshot generation instruction is valid. This causes the first transaction unit of the database client to send the consistency snapshot information and consistency site information of the query transaction to the cache management unit of the database client to obtain the first query result. The first query result, the logical database table structure, the physical database table structure, and the logical-physical topology relationship are merged into the target query result and returned to the cache management unit. The consistency site information uses the binlog position in the consistency snapshot information as the consistency site and globally releases the read lock. The binlog position records the database modification operations.

13. A database snapshot generation apparatus, characterized in that, The device includes: The receiving module is used to detect the shard status and transaction status of each database shard when it receives the database snapshot generation instruction, and to prohibit cross-shard transactions. The processing module is used to add a read lock to each database when the sharding status and transaction status of each database shard are normal; to initiate a consistent snapshot operation for the transaction and obtain the consistent snapshot information of the transaction; to use the binlog position in the consistent snapshot information as the consistency point and to globally release the read lock, wherein the binlog position records the modification operation of the database; The output module is used to derive the logical library table structure, physical library table structure, and logical-physical topology relationship by combining the information of the consistency sites.

14. An electronic device, characterized in that, The device includes a processor and a memory: The memory is used to store computer programs; The processor is configured to execute the method for generating a database snapshot according to any one of claims 1-10, based on the computer program.

15. A computer-readable storage medium, characterized in that, The computer-readable storage medium is used to store a computer program for performing the method for generating a database snapshot according to any one of claims 1-10.

Images