Script and command-based database migration method

Abstract

The invention provides a database migration method based on scripts and commands. The method comprises the following steps that A, first data table information and first view information in a Sybase database and second data table information and second view information in a MySQL database are obtained respectively; b, exporting data from the Sybase database by adopting a bcp command; c, performing code conversion on the exported data; d, processing the data subjected to code conversion; e, finally, the processed data are imported into the MySQL database through a load command, the three commands of bcp, conv and load are adopted for completing data export, character coding mode conversion and data import respectively, and the data can be migrated safely and completely; due to the fact that no three-party software is involved, the leakage risk and the compliance risk of the data in the migration process are further reduced, and a favorable reference is provided for colleagues needing database migration in enterprises.

Description

Technical Field

[0001] The present application relates to the technical field of data migration, and in particular to a database migration method based on scripts and commands. Background Art

[0002] Database migration involves selecting, preparing, extracting, converting, and permanently transferring data from one computer storage system to another. In addition, verifying the integrity of the migrated data and decommissioning the old data storage are also considered part of the overall data migration process. Database migration refers to the process of migrating data and objects in a database from one platform or environment to another. This process may involve migrating from one database management system (such as MySQL, Oracle, SQLServer, etc.) to another database management system, or migrating between different servers or database instances within the same database management system.

[0003] In scenarios such as database version updates, capacity expansion, distributed storage or hardware updates, there is a common demand for database migration. Currently, the commonly used database migration solutions are mainly divided into manual migration and fully automatic migration. Among them, manual migration requires developers to manually write SQL statements to migrate the database. The migration success rate and efficiency are low, and it is easy to make mistakes and omissions. The current fully automatic migration solutions are strongly dependent on and limited by the built-in functions of the database framework, and have poor generalization capabilities. For example, in the database version update scenario, if the database framework or programming language is replaced, it will have a great impact on the above-mentioned fully automatic migration solution. At the same time, because it is limited by the functions provided by the database framework, it is easy to cause migration failure or incomplete data migration; in addition, the migration process is completely black box, and sensitive or dangerous operations cannot be controlled and managed, which is easy to cause data loss.

[0004] In order to ensure the security of customer data, historical customer information originally stored in the Sybase database needs to be migrated to the MySQL database. The current data migration process is prone to garbled original data and poor character set compatibility. In addition, the migration process involves third-party software, which is prone to data leakage risks and compliance risks during the migration process. Therefore, it is necessary to design a database migration method based on scripts and commands. Summary of the invention

[0005] The present application provides a database migration method based on scripts and commands to solve the current problems of garbled original data and poor character set compatibility, thereby achieving safe and complete data migration; and solving the problems of garbled original data swallowing separators and escape character recognition errors.

[0006] The present application provides a database migration method based on scripts and commands, comprising the following steps:

[0007] A. Obtain first data table information and first view information from the Sybase database and second data table information and second view information from the MySQL database respectively;

[0008] B. Use the bcp command to export data from the Sybase database;

[0009] C. Convert the exported data into different codes;

[0010] D. Process the data after encoding conversion;

[0011] E. Finally, use the load command to import the processed data into the MySQL database.

[0012] Preferably, the present application provides a script and command-based database migration method, wherein the step A specifically comprises:

[0013] Generate a table creation statement for a first data table corresponding to the Sybase database according to the first data table information and the first view information;

[0014] Generate a table creation statement for a second data table corresponding to the MySQL database according to the second data table information and the second view information;

[0015] A string comparison is performed on the table creation statement of the first data table and the table creation statement of the second data table to obtain a table structure comparison result.

[0016] Preferably, the present application provides a script and command-based database migration method, wherein the bcp utility tool that generates the bcp command in step B copies data between Microsoft SQL Server 2000 and Sybase instances and data files in a user-specified format.

[0017] Preferably, the present application provides a script and command-based database migration method, wherein in step B, before using the bcp command to export data from the Sybase database, the method further includes:

[0018] Manually specify a delimiter with a high degree of recognition to avoid the content in the Sybase database being the same as the default delimiter.

[0019] Preferably, the present application provides a script and command-based database migration method, wherein, in step C, before the data is imported into the MySQL database, the iconv command is used to perform encoding conversion.

[0020] Preferably, the present application provides a script and command-based database migration method, wherein in step C, after encoding the exported data, the following steps are further included:

[0021] When executing the iconv command, you need to use parameters to skip illegal characters.

[0022] Preferably, the present application provides a script and command-based database migration method, wherein the step D specifically comprises: using Python's regular expression to match missing delimiters and complete them.

[0023] Preferably, the present application provides a script and command-based database migration method, in which Python regular expressions are used to match missing delimiters and complete them. The specific steps are as follows:

[0024] First, use a regular expression to match the first missing delimiter;

[0025] Then complete the first separator.

[0026] Preferably, the present application provides a script-and-command-based database migration method, which also includes batch generation of migration instructions, specifically using Python scripts to batch generate the required commands. When the commands need to be adjusted, it is only necessary to simply adjust the script and regenerate the commands to complete the adjustment of all commands.

[0027] Preferably, a database migration device based on scripts and commands comprises a data export module, a code conversion module, a data processing module, a data import module and a processor module, wherein the data export module, the code conversion module, the data processing module and the data import module are respectively connected to the processor module;

[0028] Wherein, the data export module is used to export data from the Sybase database;

[0029] The encoding conversion module is used to perform encoding conversion on the data before importing into the MySQL database;

[0030] The data processing module is used to process the data, using Python regular expressions to match missing delimiters and complete them;

[0031] The data import module is used to import the processed data into the MySQL database using the load command.

[0032] Beneficial effects:

[0033] (1) The present invention uses the three commands bcp, iconv, and load to respectively complete the export of data, the conversion of character encoding methods, and the import of data, so that the data can be migrated safely and completely; it solves the problems of garbled delimiters in the original data and escape character recognition errors, and provides an effective method for achieving the integrity of database migration.

[0034] (2) The present invention does not design any third-party software, and uses database commands and Linux operating system commands throughout the process to complete the complete data migration. Since the present invention does not involve any third-party software, the risk of data leakage and compliance risks during the migration process are further reduced, providing a useful reference for colleagues within the enterprise who need to do database migration.

[0035] (3) In this method, a Python script is used to batch generate the required commands. When the commands need to be adjusted, the script needs to be simply adjusted and the commands are regenerated to complete the adjustment of all commands.

[0036] The above description is only an overview of the technical solution of the embodiment of the present application. In order to more clearly understand the technical means of the embodiment of the present application, it can be implemented in accordance with the contents of the specification. In order to make the above and other purposes, features and advantages of the embodiment of the present application more obvious and easy to understand, the specific implementation methods of the present application are listed below. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] In order to more clearly illustrate the technical solutions of the embodiments of the present application, a brief introduction will be given below to the drawings required for use in the description of the embodiments. Obviously, the drawings described below are some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0038] Figure 1 It is the work flow chart of the present invention;

[0039] Figure 2 This is a block diagram of the working principle of the present invention;

[0040] Figure 3 To generate the script diagram used for bcp and iconv commands;

[0041] Figure 4 Script diagram for fixing missing separators;

[0042] Figure 5 To generate the script diagram used for the load command.

[0043] Explanation of the accompanying drawings: data export module 1, encoding conversion module 2, data processing module 3, data import module 4, processor module 5. DETAILED DESCRIPTION

[0044] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0045] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by technicians in the technical field to which this application belongs; the terms used in the specification of the application are only for the purpose of describing specific embodiments and are not intended to limit this application; the terms "including" and "having" and any variations thereof in the specification, claims and drawings of this application are intended to cover non-exclusive inclusions.

[0046] Reference to "embodiments" herein means that a particular feature, structure, or characteristic described in conjunction with the embodiments may be included in at least one embodiment of the present application. The appearance of the phrase "embodiments" in various locations in the specification does not necessarily refer to the same embodiment, nor is it an independent or alternative embodiment that is mutually exclusive with other embodiments. It is explicitly and implicitly understood by those skilled in the art that the embodiments described herein may be combined with other embodiments.

[0047] The directional words appearing in the following description are all directions shown in the drawings, and do not limit the specific structure of the present application. For example, in the description of the present application, the directions or positional relationships indicated by the terms "center", "longitudinal", "lateral", "length", "width", "thickness", "up", "down", "front", "back", "left", "right", "vertical", "horizontal", "top", "bottom", "inside", "outside", "clockwise", "counterclockwise", "axial", "radial", "circumferential", etc. are based on the directions or positional relationships shown in the drawings, and are only for the convenience of describing the present application and simplifying the description, rather than indicating or implying that the device or element referred to must have a specific direction, be constructed and operated in a specific direction, and therefore cannot be understood as limiting the present application.

[0048] In addition, the expressions of indicated directions such as the X direction, the Y direction, and the Z direction used to illustrate the operation and construction of the components of the present embodiment are not absolute but relative, and although these indications are appropriate when the components are in the positions shown in the figures, when these positions change, these directions should be interpreted differently to correspond to the changes.

[0049] In addition, the terms "first", "second", etc. in the specification and claims of this application or the above-mentioned drawings are used to distinguish different objects rather than to describe a specific order, and may explicitly or implicitly include one or more of the features.

[0050] In the description of the present application, it should be noted that, unless otherwise clearly specified and limited, the terms "installed", "connected" and "connected" should be understood in a broad sense. For example, the "connection" or "connection" of a mechanical structure may refer to a physical connection. For example, the physical connection may be a fixed connection, such as a fixed connection through a fixing member, such as a fixed connection through a screw, bolt or other fixing member; the physical connection may also be a detachable connection, such as a mutual snap-on or snap-fit ​​connection; the physical connection may also be an integral connection, such as a connection formed by welding, bonding or integral molding. For those of ordinary skill in the art, the specific meanings of the above terms in the present application can be understood according to specific circumstances.

[0051] In order to enable those skilled in the art to better understand the solution of the present application, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the accompanying drawings.

[0052] See also Figure 1-Figure 5 The present application discloses a database migration method based on scripts and commands, comprising the following steps:

[0053] A. Obtain first data table information and first view information from the Sybase database and second data table information and second view information from the MySQL database respectively;

[0054] B. Use the bcp command to export data from the Sybase database;

[0055] C. Convert the exported data into different codes;

[0056] D. Process the data after encoding conversion;

[0057] E. Finally, use the load command to import the processed data into the MySQL database.

[0058] In the present invention, step A specifically comprises:

[0059] Generate a table creation statement for a first data table corresponding to the Sybase database according to the first data table information and the first view information;

[0060] Generate a table creation statement for a second data table corresponding to the MySQL database according to the second data table information and the second view information;

[0061] A string comparison is performed on the table creation statement of the first data table and the table creation statement of the second data table to obtain a table structure comparison result.

[0062] In the present invention, the bcp utility tool that generates the bcp command in step B copies data between Microsoft SQL Server 2000 and Sybase instances and data files in a format specified by the user.

[0063] In the present invention, in step B, before using the bcp command to export data from the Sybase database, the following steps are also included:

[0064] Manually specify a delimiter with high recognition accuracy to avoid the content in the Sybase database being the same as the default delimiter, thereby improving data export efficiency.

[0065] In the present invention, since Sybase and MySQL use different character encoding methods, before data is imported into the MySQL database, the iconv command is used to perform encoding conversion.

[0066] In addition, in step C of the present invention, after the exported data is converted into code, the following steps are also included:

[0067] Because the original data may contain garbled or illegal characters, you need to use parameters to skip illegal characters when executing the iconv command.

[0068] In addition, the converted data file may also have missing delimiters caused by control characters, which may cause errors when importing into the database. Therefore, Python regular expressions are used to match the missing delimiters and complete them.

[0069] The specific steps are as follows:

[0070] First, use a regular expression to match the first missing delimiter;

[0071] Then complete the first separator.

[0072] The above process is all the operations required for migrating a table. When multiple tables need to be migrated, the above process needs to be repeatedly operated in batches. The present invention also includes batch generation of migration instructions, specifically using Python scripts to batch generate the required commands. When the commands need to be adjusted, simply adjust the script and regenerate the commands to complete the adjustment of all commands.

[0073] The present invention also discloses a database migration device based on scripts and commands, comprising a data export module 1, a code conversion module 2, a data processing module 3, a data import module 4 and a processor module 5, wherein the data export module 1, the code conversion module 2, the data processing module 3 and the data import module 4 are respectively connected to the processor module 5;

[0074] Wherein, the data export module 1 is used to export data from the Sybase database;

[0075] The encoding conversion module 2 is used to perform encoding conversion on the data before importing into the MySQL database;

[0076] The data processing module 3 is used to process the data, using Python regular expressions to match missing delimiters and complete them;

[0077] The data import module 4 is used to import the processed data into the MySQL database using the load command.

[0078] The present invention does not design any third-party software, and uses database commands and Linux operating system commands throughout the process to complete the complete data migration. Since the present invention does not involve any third-party software, the risk of data leakage and compliance risks during the migration process are further reduced, providing a useful reference for colleagues within the enterprise who need to do database migration.

[0079] In summary, the present invention uses the three commands of bcp, iconv and load to respectively complete the export of data, the conversion of character encoding mode and the import of data, so that the data can be migrated safely and completely; it solves the problems of garbled delimiters in the original data and errors in the recognition of escape characters, and provides an effective method for achieving the integrity of database migration.

[0080] It should be noted that the above-mentioned sequence of the embodiments of the present application is for description only and does not represent the advantages and disadvantages of the embodiments. The above-mentioned specific embodiments of the present application are described. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recorded in the claims can be performed in an order different from that in the embodiments and still achieve the desired results. In addition, the processes depicted in the drawings do not necessarily require the specific order or continuous order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0081] A person skilled in the art will understand that all or part of the steps to implement the above embodiments may be accomplished by hardware, or may be accomplished by instructing the relevant hardware through a program, and the program may be stored in a computer-readable storage medium, and the above-mentioned storage medium may be a read-only memory, a disk or an optical disk, etc.

[0082] The above are only preferred embodiments of the present application and are not intended to limit the present application. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present application should be included in the protection scope of the present application.

Claims

1. A database migration method based on scripts and commands, characterized in that: The following steps are involved: A. Obtain first data table information and first view information from the Sybase database and second data table information and second view information from the MySQL database respectively; B. Use the bcp command to export data from the Sybase database; C. Convert the exported data into different codes; D. Process the data after encoding conversion; E. Finally, use the load command to import the processed data into the MySQL database.

2. According to claim 1, a database migration method based on scripts and commands is characterized in that: The specific steps in step A are: Generate a table creation statement for a first data table corresponding to the Sybase database according to the first data table information and the first view information; Generate a table creation statement for a second data table corresponding to the MySQL database according to the second data table information and the second view information; A string comparison is performed on the table creation statement of the first data table and the table creation statement of the second data table to obtain a table structure comparison result.

3. According to the script and command-based database migration method of claim 1, it is characterized in that: The bcp utility that generates the bcp command in step B copies data between Microsoft SQL Server 2000 and Sybase instances and data files in a user-specified format.

4. The script and command-based database migration method according to claim 1, characterized in that: In the step B, before exporting data from the Sybase database using the bcp command, the following steps are also included: Manually specify a delimiter with a high degree of recognition to avoid the content in the Sybase database being the same as the default delimiter.

5. The script and command-based database migration method according to claim 1, characterized in that: In the step C, before the data is imported into the MySQL database, the iconv command is used to perform encoding conversion.

6. The script and command-based database migration method according to claim 1, characterized in that: In the step C, after the exported data is converted into code, the following steps are also included: When executing the iconv command, you need to use parameters to skip illegal characters.

7. The script and command-based database migration method according to claim 1, characterized in that: The step D specifically includes: using Python's regular expression to match the missing delimiters and complete them.

8. The script and command-based database migration method according to claim 7, characterized in that: Use Python's regular expressions to match missing delimiters and complete them. The specific steps are as follows: First, use a regular expression to match the first missing delimiter; Then complete the first separator.

9. The script and command-based database migration method according to claim 1, characterized in that: It also includes batch generation of migration instructions, which uses Python scripts to batch generate the required commands. When the commands need to be adjusted, you only need to simply adjust the script and regenerate the commands to complete the adjustment of all commands.

10. A database migration device based on scripts and commands, characterized in that: It comprises a data export module (1), a code conversion module (2), a data processing module (3), a data import module (4) and a processor module (5), wherein the data export module (1), the code conversion module (2), the data processing module (3) and the data import module (4) are respectively connected to the processor module (5); Wherein, the data export module (1) is used to export data from a Sybase database; The encoding conversion module (2) is used to perform encoding conversion on the data before importing into the MySQL database; The data processing module (3) is used to process the data, using Python regular expressions to match missing delimiters and complete them; The data import module (4) is used to import the processed data into the MySQL database using the load command.

Images