MySQL database migration method and system based on block file

Abstract

The invention discloses a MySQL database migration method and system based on a block file, and the method comprises the steps: enabling a snapshot file generation service to generate a snapshot for a source database directory, storing the snapshot in a designated local disk directory, and recording a timestamp generated by the current snapshot; the snapshot file transmission service transmits the snapshot to a target library server; the incremental data extraction service obtains a snapshot generation timestamp and analyzes an incremental log after the timestamp, binary data in the incremental log is converted into structured data, and the structured data is stored in a local disk directory; the incremental data conversion service is used for converting incremental structured data into standard DML statements of a database by identifying data operation content and analyzing to adapt to all field types; and reading and sequentially executing the DML statements through the incremental data writing service, and restoring the data of the source database to the target database. On the basis of block file migration, the thermal migration of the database is realized in combination with incremental logs.

Description

Technical Field

[0001] The present invention relates to the technical field of database migration and database backup and recovery, and in particular to a block file-based MySQL database migration method and system. Background Art

[0002] MySQL database migration technology is the process of migrating all the data in a MySQL database system from one storage medium or platform to another storage medium or platform. This involves data extraction, transformation, loading (ETL) and ensuring the consistency and integrity of data during the migration process. At present, there are several common methods: 1. Query the entire table through the database driver connection, such as: JAVA language through the JDBC driver, use the SELECT statement to query the entire table data to the target library to perform the INSERT operation; 2. Parse the database log file, then convert it into a data model, and finally splice it into an INSERT statement to perform insertion in the target library, such as MySQL reads the Binlog log and parses it and then performs data restoration in the target library. 3. Snapshot file migration, stop the source database service, then copy the entire source server file to the target server, and then perform the restore operation on the target server.

[0003] The disadvantages of the existing technology are: (1) Querying the entire table through a database driver connection: all records in the table will be returned. If the amount of data in the table is large, this will cause a large amount of data to be transmitted on the network, increasing the network load and response time. If the query result set is very large, it may cause the application to run out of memory, especially in an environment with limited memory resources. Full table scans will cause a large load on the database server, especially in a high-concurrency environment, which may cause database performance to degrade. Full table queries will return all fields and records in the table, which may lead to the exposure of sensitive data, especially without proper security measures. (2) Parsing database log files: When the amount of data is large, all Binlog files need to be parsed, which is relatively inefficient. (3) Stopping the entire server to migrate: The source database service needs to be stopped, affecting the execution of production business. Summary of the invention

[0004] The object of the present invention is to provide a MySQL database migration method and system based on block files, which realizes hot migration of database by combining incremental logs on the basis of block file migration.

[0005] The technical solution adopted by the present invention is: A block file-based MySQL database migration system includes the following modules: Snapshot file migration module: provides snapshot file generation service and snapshot file transfer service; snapshot file generation service is used to create a snapshot file of a complete copy of the database at a certain point in time and store the generated snapshot file in a specified location; snapshot file transfer service transfers the generated snapshot file from the source server to the target server Incremental data module: provides incremental data extraction service, incremental data conversion service and incremental data writing service; the incremental data extraction service captures and transmits the newly generated data changes in the snapshot file migration to ensure the data consistency between the source database and the target database; the incremental data conversion service performs necessary conversion on the incremental data captured from the source database to ensure that the data can be correctly applied in the target database; the incremental data writing service writes the converted incremental data to the target database to ensure the integrity and consistency of the data while minimizing the impact on the target database.

[0006] Furthermore, the snapshot file transfer service encrypts the snapshot file to ensure data transmission security.

[0007] Furthermore, the incremental data extraction service captures the addition, modification and deletion operations in the source database in real time or periodically based on the database log, and caches the captured data changes to the local disk directory.

[0008] Specifically, as a preferred implementation, the disk cache of the incremental data extraction service is changed to a memory cache, which can reduce the reading and writing of disk IO and improve the efficiency of incremental data parsing.

[0009] Furthermore, the incremental data conversion service performs necessary conversions on the captured data, including data mapping, format conversion, etc., to adapt to the structure and requirements of the target database; and then uses data consistency algorithms (such as digest algorithms) to verify the data to achieve consistency and integrity of data files.

[0010] Furthermore, the incremental data writing service writes the converted incremental data to the target database and uses database transaction technology to achieve the integrity and consistency of DML operations; at the same time, it records detailed information during the writing process to facilitate subsequent auditing and troubleshooting.

[0011] A MySQL database migration method based on block files comprises the following steps: Step 1: The snapshot file generation service generates a snapshot of the source database directory and stores it in the specified local disk directory, and records the current snapshot generation time as the timestamp for subsequent service calls; the snapshot file transfer service then transfers the snapshot file to the target database server; Specifically, the snapshot in step 1 records all data in the database at the current moment, including business data, configuration data, system data, etc.

[0012] Furthermore, step 1 specifically includes the following steps: Step 1-1, the snapshot file generation service calls the underlying interface of the operating system to generate a snapshot of the source database directory and stores it in the specified local disk directory, and records the current snapshot generation time as the timestamp for subsequent service calls; In step 1-2, the snapshot file transfer service generates a file verification code (such as MD5, SHA-256) before transferring the snapshot, and compresses the snapshot file; then it uses an efficient and secure data transmission protocol (such as TCP / IP protocol, HTTPS protocol) to transfer the snapshot file and the file verification code to the target library server.

[0013] Step 1-3: After the snapshot transfer is completed, the snapshot file transfer service of the target library server decompresses the compressed snapshot file and performs a file integrity check after decompression, which not only improves data transmission efficiency but also ensures data consistency and integrity.

[0014] Step 2: The incremental data extraction service obtains the snapshot generation timestamp of the snapshot file generation service, parses the incremental log after the timestamp, converts the binary data in the incremental log into structured data and saves it to the local disk directory; Specifically, the service uses real-time monitoring to capture incremental changes in the database in real time and record timestamps. When the incremental data extraction service is abnormally shut down, the incremental data generated subsequently can be parsed based on the timestamp after the next startup.

[0015] Furthermore, step 2 specifically includes the following steps: Step 2-1, the incremental data extraction service obtains the snapshot generation timestamp of the snapshot file generation service; Step 2-2, determine whether the incremental log after the timestamp is enabled; if yes, execute step 2-3; otherwise, execute step 2-2; Step 2-3, capturing incremental data based on timestamps and recording corresponding timestamps to form incremental logs; Step 2-4: parse the binary data in the incremental log and convert it into structured data and save it to the local disk directory.

[0016] Step 3: The incremental data conversion service converts the structured data of the incremental data extraction service by identifying the data operation content and parsing and adapting all field types so as to convert them into database standard DML statements. Specifically, it automatically identifies the addition, modification, and deletion operations, and performs field-level analysis to adapt to all field types such as character, integer, floating point, time, and binary, and converts the data into database-standard DML statements.

[0017] Special type adaptation rules: Time type: Identify various time formats, such as yyyy-MM-dd, yyyy, yyyy-MM, HH:mm:ss, etc., use different parsing rules, and convert them into data that can be executed by DML statements Floating point type: Identify single-precision, double-precision, and no-precision data, and convert them into data executable by DML statements Binary: Identify binary data and use data mapping to map the binary data into DML statements to complete binary data writing.

[0018] Step 4: Read the DML statements converted by the incremental data conversion service through the incremental data writing service, execute the DML statements sequentially, restore the data of the source database to the target database, and use database transaction management technology to ensure data consistency.

[0019] The present invention adopts the above technical solution, uses snapshot files to restore data, realizes database inventory migration from the underlying file dimension, and improves the inventory migration rate. At the same time, in order to solve the consistency problem of source and target databases under high concurrency during hot migration, the underlying database log parsing technology is adopted, combined with the data consistency algorithm, to migrate the incremental data generated during hot migration to the target database.

[0020] Compared with the prior art, the present invention has the following beneficial effects: 1. It does not need to rely on database drivers for additional full data query and analysis, which reduces time overhead; 2. It adopts a file mode, and does not need to load the full data into the memory, which reduces the use of memory resources; 3. It has little impact on database performance, and only uses the disk space of the database server; 4. It does not perform structured query on the data, avoiding the problem of data exposure; 5. Compared with parsing all Binlogs, the Binlog logs are not parsed for existing data, which improves the efficiency of existing migration. Incremental migration adopts the method of reading Binlog logs to realize hot migration without interrupting production business. BRIEF DESCRIPTION OF THE DRAWINGS

[0021] The present invention is further described in detail below with reference to the accompanying drawings and specific embodiments; Figure 1 A schematic diagram of the principle architecture of a MySQL database migration method based on block files of the present invention; Figure 2 It is a schematic diagram of the data conversion process of the present invention. DETAILED DESCRIPTION

[0022] In order to make the purpose, technical solutions and advantages of the embodiments of the present application clearer, the technical solutions in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application.

[0023] like Figure 1 As shown in Figure 2, the present invention discloses a MySQL database migration method based on block files, which uses snapshot files to restore data, realizes database inventory migration from the underlying file dimension, and improves the inventory migration rate. At the same time, in order to solve the consistency problem of the source and target databases under high concurrency during hot migration, the underlying database log parsing technology is used, and combined with the data consistency algorithm, the incremental data generated during the hot migration process is migrated to the target database. The MySQL database migration system based on block files adopted by the present invention includes the following modules: Snapshot file migration module: provides snapshot file generation service and snapshot file transfer service; Specifically, (1) the snapshot file generation service is an important component of the present invention, which is responsible for generating the snapshot file of the database. The following is a detailed description of the snapshot file generation module, including its functions, implementation methods and precautions. It mainly includes the following two main functions: Generates a snapshot file to create a complete copy of the database at a certain point in time, including data files, configuration files, and log files.

[0024] Store the snapshot file and store the generated snapshot file in the specified location.

[0025] (2) The snapshot file transfer service is another key part of the present invention, which is responsible for transferring the generated snapshot files from the source server to the target server. This module needs to ensure the efficiency, reliability and security of the transmission process. The following is a detailed description of the snapshot file transfer module, including its functions, implementation methods and precautions. It mainly includes the following two main functions: File transfer: Using efficient transfer tools and protocols, snapshot files can be quickly transferred to the target server. And using consistency algorithms to ensure data consistency.

[0026] File encryption: Encrypt snapshot files to ensure data transmission security.

[0027] Incremental data module: provides incremental data extraction service, incremental data conversion service and incremental data writing service; Specifically, (1) the incremental data extraction service is an important component of the present invention, which is responsible for capturing and transmitting newly generated data changes in snapshot file migration to ensure data consistency between the source database and the target database. The following is a detailed description of the incremental data extraction service, including its functions, implementation methods and precautions.

[0028] Incremental capture: Based on database logs, capture the addition, modification, and deletion operations in the source database in real time or periodically.

[0029] Data caching: Cache captured data changes to a local disk directory. In addition, if memory resources are sufficient or higher incremental data parsing efficiency is required, the incremental data extraction service's disk cache can be changed to a memory cache. This can reduce disk IO reads and writes and improve incremental data parsing efficiency.

[0030] (2) The incremental data conversion service is an important part of the present invention. It is responsible for performing necessary conversions on the incremental data captured from the source database to ensure that the data can be correctly applied in the target database. The following is a detailed description of the incremental data conversion service, including its functions, implementation methods, and precautions.

[0031] Data conversion: Perform necessary conversions on the captured data, including data mapping, format conversion, etc., to adapt to the structure and requirements of the target database.

[0032] Data verification: Use data consistency algorithms (such as digest algorithms) to achieve consistency and integrity of data files.

[0033] (3) The incremental data writing service is the last key link of the present invention, which is responsible for writing the converted incremental data to the target database. This service needs to ensure the integrity and consistency of the data while minimizing the impact on the target database. The following is a detailed description of the incremental data writing service, including its functions, implementation methods and precautions.

[0034] Data writing: Write the converted incremental data to the target database and use database transaction technology to ensure the integrity and consistency of DML operations.

[0035] Logging: Record detailed information during the writing process to facilitate subsequent auditing and troubleshooting.

[0036] The specific process steps of a block file-based MySQL database migration method are as follows: After starting the program, the snapshot file generation service calls the underlying interface of the operating system (such as LVM) to generate a snapshot of the source database directory and records the current snapshot generation time as the timestamp for subsequent service calls. The snapshot records all the data in the database at the current moment, including business data, configuration data, system data, etc. The snapshot file is then stored in the specified local disk directory.

[0037] Before transferring a snapshot, the snapshot file transfer service first generates a file verification code (such as MD5, SHA-256), and then compresses the snapshot file. Then, it uses an efficient and secure data transmission protocol (such as TCP / IP protocol, HTTPS protocol) to transfer the snapshot file and the file verification code to the target library server.

[0038] After the snapshot transfer is completed, the snapshot file transfer service decompresses the compressed snapshot file and performs a file integrity check after decompression, which not only improves data transmission efficiency but also ensures data consistency and integrity.

[0039] After starting the program, the incremental data extraction service obtains the snapshot generation timestamp of the snapshot file generation service, parses the incremental log after the timestamp, converts the binary data in the incremental log into structured data and saves it to the local disk directory. The service uses real-time monitoring to capture the incremental changes of the database in real time and record the timestamp. When the incremental data extraction service is abnormally shut down, the incremental data generated subsequently can be parsed based on the timestamp after the next startup.

[0040] After starting the program, the incremental data conversion service converts the structured data of the incremental data extraction service, automatically identifying the addition, modification, and deletion operations. It also performs field-level analysis, adapts to all field types such as character, integer, floating point, time, and binary, and converts the data into database standard DML statements.

[0041] Special type adaptation rules: Time type: Identify various time formats, such as yyyy-MM-dd, yyyy, yyyy-MM, HH:mm:ss, etc., use different parsing rules, and convert them into data that can be executed by DML statements Floating point type: Identify single-precision, double-precision, and no-precision data, and convert them into data executable by DML statements Binary: Identify binary data and use data mapping to map the binary data into DML statements to complete binary data writing.

[0042] After the snapshot file transfer service is completed, the incremental data write service is automatically started to read the DML statements converted by the incremental data conversion service, execute the DML statements sequentially, and restore the data to the target database. At the same time, database transaction management technology is used to ensure data consistency.

[0043] The present invention adopts the above technical solution, uses snapshot files to restore data, realizes database inventory migration from the underlying file dimension, and improves the inventory migration rate. At the same time, in order to solve the consistency problem of source and target databases under high concurrency during hot migration, the underlying database log parsing technology is adopted, combined with the data consistency algorithm, to migrate the incremental data generated during hot migration to the target database.

[0044] Compared with the prior art, the present invention has the following beneficial effects: 1. It does not need to rely on database drivers for additional full data query and analysis, which reduces time overhead; 2. It adopts a file mode, and does not need to load the full data into the memory, which reduces the use of memory resources; 3. It has little impact on database performance, and only uses the disk space of the database server; 4. It does not perform structured query on the data, avoiding the problem of data exposure; 5. Compared with parsing all Binlogs, the Binlog logs are not parsed for existing data, which improves the efficiency of existing migration. Incremental migration adopts the method of reading Binlog logs to realize hot migration without interrupting production business.

[0045] Obviously, the described embodiments are part of the embodiments of the present application, rather than all of the embodiments. In the absence of conflict, the embodiments in the present application and the features in the embodiments can be combined with each other. The components of the embodiments of the present application generally described and shown in the drawings here can be arranged and designed in various different configurations. Therefore, the detailed description of the embodiments of the present application is not intended to limit the scope of the application claimed for protection, but merely represents the selected embodiments of the present application. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians of the art without making creative work are within the scope of protection of the present application.

Claims

1. A block file-based MySQL database migration system, characterized by: It includes the following modules: Snapshot file migration module: provides snapshot file generation service and snapshot file transfer service; snapshot file generation service is used to create a snapshot file of a complete copy of the database at a certain point in time and store the generated snapshot file in a specified location; snapshot file transfer service transfers the generated snapshot file from the source server to the target server Incremental data module: provides incremental data extraction service, incremental data conversion service and incremental data writing service. In the snapshot file migration, the incremental data extraction service captures and transmits the newly generated data changes to ensure the data consistency between the source database and the target database. The incremental data conversion service will perform necessary conversions on the incremental data captured from the source database to ensure that the data can be correctly applied in the target database; the incremental data writing service will write the converted incremental data to the target database to ensure data integrity and consistency while minimizing the impact on the target database.

2. The MySQL database migration system based on block files according to claim 1, characterized in that: The snapshot file transfer service encrypts snapshot files to ensure data transmission security.

3. The MySQL database migration system based on block files according to claim 1, characterized in that: The incremental data extraction service is based on database logs, capturing addition, modification, and deletion operations in the source database in real time or periodically, and caching the captured data changes to the local disk or memory.

4. The MySQL database migration system based on block files according to claim 1, characterized in that: The incremental data conversion service includes data mapping and format conversion. At the same time, the incremental data conversion service uses a data consistency algorithm to verify the data to achieve consistency and integrity of data files.

5. The MySQL database migration system based on block files according to claim 1, characterized in that: The incremental data writing service writes the converted incremental data to the target database and uses database transaction technology to achieve the integrity and consistency of DML operations. It also records detailed information during the writing process to facilitate subsequent auditing and troubleshooting.

6. A block file-based MySQL database migration method, using a block file-based MySQL database migration system according to any one of claims 1 to 5, characterized in that: The method comprises the following steps: Step 1: The snapshot file generation service generates a snapshot of the source database directory and stores it in the specified local disk directory, and records the current snapshot generation time as the timestamp for subsequent service calls; The snapshot file transfer service then transfers the snapshot file to the target library server; Step 2: The incremental data extraction service obtains the snapshot generation timestamp of the snapshot file generation service, parses the incremental log after the timestamp, converts the binary data in the incremental log into structured data and saves it to the local disk directory; Step 3: The incremental data conversion service converts the structured data of the incremental data extraction service by identifying the data operation content and parsing and adapting all field types so as to convert them into database standard DML statements. Step 4: Read the DML statements converted by the incremental data conversion service through the incremental data writing service, execute the DML statements sequentially, restore the data of the source database to the target database, and use database transaction management technology to ensure data consistency.

7. The MySQL database migration method based on block files according to claim 6 is characterized in that: Step 1 specifically includes the following steps: Step 1-1, the snapshot file generation service calls the underlying interface of the operating system to generate a snapshot of the source database directory and stores it in the specified local disk directory, and records the current snapshot generation time as the timestamp for subsequent service calls; Step 1-2: The snapshot file transfer service generates a file verification code before transferring the snapshot and compresses the snapshot file; then it uses an efficient and secure data transfer protocol to transfer the snapshot file and the file verification code to the target library server; Step 1-3: After the snapshot transfer is completed, the snapshot file transfer service of the target library server decompresses the compressed snapshot file and performs a file integrity check after decompression.

8. The MySQL database migration method based on block files according to claim 6, characterized in that: Step 2 specifically includes the following steps: Step 2-1, the incremental data extraction service obtains the snapshot generation timestamp of the snapshot file generation service; Step 2-2, determine whether the incremental log after the timestamp is enabled; if yes, execute step 2-3; otherwise, execute step 2-2; Step 2-3, capturing incremental data based on timestamps and recording corresponding timestamps to form incremental logs; Step 2-4: parse the binary data in the incremental log and convert it into structured data and save it to the local disk directory.

9. The MySQL database migration method based on block files according to claim 6, characterized in that: The data operations in step 3 include adding, modifying, and deleting operations; the adaptable field types include character type, integer type, floating point type, time type, and binary type.

Images