CN113742319A - Data migration method of different database management systems based on expect - Google Patents
Data migration method of different database management systems based on expect Download PDFInfo
- Publication number
- CN113742319A CN113742319A CN202111074371.0A CN202111074371A CN113742319A CN 113742319 A CN113742319 A CN 113742319A CN 202111074371 A CN202111074371 A CN 202111074371A CN 113742319 A CN113742319 A CN 113742319A
- Authority
- CN
- China
- Prior art keywords
- data
- database management
- expect
- management system
- instruction
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 230000005012 migration Effects 0.000 title claims abstract description 33
- 238000013508 migration Methods 0.000 title claims abstract description 33
- 238000000034 method Methods 0.000 title claims abstract description 17
- 238000013515 script Methods 0.000 claims abstract description 25
- 230000008676 import Effects 0.000 claims abstract description 10
- 238000009434 installation Methods 0.000 claims description 18
- 238000000926 separation method Methods 0.000 claims description 8
- 238000009795 derivation Methods 0.000 claims description 3
- 230000008569 process Effects 0.000 description 2
- 230000004075 alteration Effects 0.000 description 1
- 230000009286 beneficial effect Effects 0.000 description 1
- 230000008859 change Effects 0.000 description 1
- 230000007547 defect Effects 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/214—Database migration support
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/25—Integrating or interfacing systems involving database management systems
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention relates to a data migration method of different database management systems based on expect, which executes data migration under a Linux operating system, and compiles an export shell script A.sh of a database management system A and an import shell script B.sh of a database management system B based on expect language; and sequentially executing export shell script A.sh and import shell script B.sh, realizing rapid automatic migration of data by adopting the technical scheme, and eliminating data type differences of source data, targets and data in the migration of different database systems.
Description
Technical Field
The invention relates to the field of big data migration, in particular to a data migration method of different database management systems based on expect.
Background
At present, the total updating of different database data is mostly performed manually, and for a large amount of data (hundred million levels), because data of a plurality of objects is often migrated simultaneously in the data migration process, and information of all migrated objects is read in the data migration process, because the content of the query required is very much, the operation of the database migration tool disclosed in patent No. CN 201410318745-data migration apparatus for a database and data migration method is time consuming, and as the number of migrated objects increases, the speed of reading information becomes slower and slower, and if each object is executed in sequence, the efficiency of data migration and the migration experience of the user are seriously affected. In addition, there is also a difference in the data types of the source data and the target and data in the migration of different database systems.
Disclosure of Invention
Aiming at the defects of the prior art, the invention provides the database table migration method of different database management systems based on expect, which realizes the rapid and automatic migration of full data in different database management systems.
The invention discloses a data migration method of different database management systems based on expect, which comprises the following technical scheme: the method executes data migration under a Linux operating system and comprises the following steps:
s1, installing an Expect language in the Linux operating system;
s2, compiling an export shell script A.sh of the database management system A based on the expect language;
s3, compiling an import shell script B.sh of the B database management system based on the expect language;
s4, executing export shell script A.sh, exporting data of A database management system, and generating data text file;
s5: and executing an import shell script B.sh, and importing the generated data file into a B database management system.
Further, the step S4 and the step S5 are put into the Linux timer to run.
Further, the step S1 includes:
s1-1, uploading the Expect installation package to a linux server;
s1-2, executing a 'tar-xzvf Expect installation package' command, and decompressing the installation package;
s1-3, executing a command of 'cd Expect installation package' and entering a directory of the Expect installation package;
s1-4, executing a "/configure & & make & & make instruction" command, and installing an Expect language;
and S1-5, executing an 'expect-v' command, checking the version number, and if the version number can be checked, finishing the installation.
Further, the export shell script a.sh in step S2 includes database table instructions of an export a database management system, where the instructions include:
s2-1, A is a connection information instruction of the database management system;
s2-2, a login information instruction;
s2-3, a statement derivation instruction of the database table, wherein the statement defines a derived table name, a derived data sequence, a data separation symbol and a data ending symbol of each row;
s2-4, defining the generated data type and the generated data file;
and S2-5, ending information instruction.
Further, the importing shell script b.sh in step S3 includes a database table instruction for importing a B database management system, where the instruction includes the following:
s3-1, B is a connection information instruction of the database management system;
s3-2, a login information instruction;
s3-3, creating a blank database instruction in the B database management system;
s3-4, importing a statement instruction of a database table in a blank database, wherein the statement defines a file name of an imported data text, an order of the imported data, a table field name corresponding to the order data, an imported table name, a data separation symbol and an end symbol of each row of data;
and S3-5, ending information instruction.
Further, the A database management system is MySQL; the B database management system is PostgreSQL.
Compared with the prior art, the invention has the following beneficial effects:
1. shell scripts are compiled by adopting an Expect language to export a data text file, so that rapid and automatic migration of data is realized, and the data type differences of source data, targets and data in migration of different database systems are eliminated;
2. and executing the shell script by adopting a Linux timer to realize the periodical execution of the database migration, or executing the database migration at a specified time to realize the full-automatic processing.
Drawings
The accompanying drawings, which are described herein to provide a further understanding of the application, are included in the following description:
FIG. 1 is a schematic flow chart of an embodiment of the present invention.
Detailed Description
Referring to fig. 1, the embodiment of the present invention takes MySQL and PostgreSQL databases as examples, and implements data migration of different database management systems based on expect, where the data migration is performed under a Linux operating system, and includes the following steps:
s1, installing an Expect language in the Linux operating system;
s2, compiling export shell script A.sh of the MySQL database management system based on the expect language;
s3, compiling an import shell script B.sh of the PostgreSQL database management system based on the expect language;
s4, executing export shell script A.sh, exporting data of the MySQL database management system, and generating a data text file;
s5: and executing an import shell script B.sh, and importing the generated data file into a PostgreSQL database management system.
Of course, the generated data text file is stored as a document with a corresponding format according to the storage type set by the script, and is stored as a txt document in the embodiment.
the txt document content is:
data 11^ data 12^ data 13^ data 14^
Data 21^ data 22^ data 23^ data 24^
Data n1^ data n2^ data n3^ data n4^ data nm;
wherein n and m are natural numbers, the data separation symbol is ^ and the ending symbol of each row of data is carriage return and line change;
and during import, separating data from rows and columns corresponding to the table according to the data row and column number according to txt document data, the imported table name and the separation symbol ^ to realize data number matching and entering.
Further, the step S4 and the step S5 are put into the Linux timer to run.
Further, the step S1 includes:
s1-1, uploading the Expect installation package to a linux server;
s1-2, executing a 'tar-xzvf Expect installation package' command, and decompressing the installation package;
s1-3, executing a command of 'cd Expect installation package' and entering a directory of the Expect installation package;
s1-4, executing a "/configure & & make & & make instruction" command, and installing an Expect language;
and S1-5, executing an 'expect-v' command, checking the version number, and if the version number can be checked, finishing the installation.
Further, the export shell script a.sh in step S2 includes a database table instruction for exporting a MySQL database management system, where the instruction includes:
s2-1, a connection information instruction of the MySQL database management system;
s2-2, a login information instruction;
s2-3, a statement derivation instruction of the database table, wherein the statement defines a derived table name, a derived data sequence, a data separation symbol and a data ending symbol of each row;
s2-4, defining the generated data type and the generated data text file;
and S2-5, ending information instruction.
Further, the importing shell script b.sh in step S3 includes a database table instruction of importing a PostgreSQL database management system, where the instruction includes the following:
s3-1, a connection information instruction of a PostgreSQL database management system;
s3-2, a login information instruction;
s3-3, creating a blank database instruction in the PostgreSQL database management system;
s3-4, importing a statement instruction of a database table in a blank database, wherein the statement defines a file name of an imported data text, an order of the imported data, a table field name corresponding to the order data, an imported table name, a data separation symbol and an end symbol of each row of data;
and S3-5, ending information instruction.
Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that changes, modifications, substitutions and alterations can be made in these embodiments without departing from the principles and spirit of the invention, the scope of which is defined in the appended claims and their equivalents.
Claims (6)
1. A data migration method of different database management systems based on expect, which executes data migration under Linux operating system, is characterized in that: the method comprises the following steps:
s1, installing an Expect language in the Linux operating system;
s2, compiling an export shell script A.sh of the database management system A based on the expect language;
s3, compiling an import shell script B.sh of the B database management system based on the expect language;
s4, executing export shell script A.sh, exporting data of A database management system, and generating data text file;
s5: and executing an import shell script B.sh, and importing the generated data text file into a B database management system.
2. A data migration method of different database management systems based on expect is characterized in that: the step S4 and the step S5 are put into the Linux timer to run.
3. A data migration method of different database management systems based on expect is characterized in that: the step S1 includes:
s1-1, uploading the Expect installation package to a linux server;
s1-2, executing a 'tar-xzvf Expect installation package' command, and decompressing the installation package;
s1-3, executing a command of 'cd Expect installation package' and entering a directory of the Expect installation package;
s1-4, executing a "/configure & & make & & make instruction" command, and installing an Expect language;
and S1-5, executing an 'expect-v' command, checking the version number, and if the version number can be checked, finishing the installation.
4. A data migration method of different database management systems based on expect is characterized in that: the export shell script a.sh in step S2 includes database table instructions to export a database management system, the instructions including:
s2-1, A is a connection information instruction of the database management system;
s2-2, a login information instruction;
s2-3, a statement derivation instruction of the database table, wherein the statement defines a derived table name, a derived data sequence, a data separation symbol and a data ending symbol of each row;
s2-4, defining the generated data type and the generated data text file;
and S2-5, ending information instruction.
5. A data migration method of different database management systems based on expect is characterized in that: the import shell script b.sh in step S3 includes a database table instruction for importing a B database management system, where the instruction includes the following:
s3-1, B is a connection information instruction of the database management system;
s3-2, a login information instruction;
s3-3, creating a blank database instruction in the B database management system;
s3-4, importing a statement instruction of a database table in a blank database, wherein the statement defines a file name of an imported data text, an order of the imported data, a table field name corresponding to the order data, an imported table name, a data separation symbol and an end symbol of each row of data;
and S3-5, ending information instruction.
6. A data migration method of different database management systems based on expect is characterized in that: the A database management system is MySQL; the B database management system is PostgreSQL.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111074371.0A CN113742319A (en) | 2021-09-14 | 2021-09-14 | Data migration method of different database management systems based on expect |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111074371.0A CN113742319A (en) | 2021-09-14 | 2021-09-14 | Data migration method of different database management systems based on expect |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN113742319A true CN113742319A (en) | 2021-12-03 |
Family
ID=78738642
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202111074371.0A Pending CN113742319A (en) | 2021-09-14 | 2021-09-14 | Data migration method of different database management systems based on expect |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113742319A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN114138714A (en) * | 2022-02-08 | 2022-03-04 | 上海金仕达软件科技有限公司 | Data migration method and device, storage medium and electronic equipment |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20150019488A1 (en) * | 2013-07-09 | 2015-01-15 | Oracle International Corporation | Automated database migration architecture |
| CN109460439A (en) * | 2018-11-14 | 2019-03-12 | 泰康保险集团股份有限公司 | A kind of data processing method, device, medium and electronic equipment |
| CN110162517A (en) * | 2019-05-30 | 2019-08-23 | 深圳前海微众银行股份有限公司 | Data migration method, device, equipment and computer readable storage medium |
| CN113297182A (en) * | 2021-06-16 | 2021-08-24 | 中国农业银行股份有限公司 | Data migration method, device, storage medium and program product |
| CN113312336A (en) * | 2021-06-22 | 2021-08-27 | 中国农业银行股份有限公司 | Data processing method, data processing apparatus, electronic device, storage medium, and program product |
-
2021
- 2021-09-14 CN CN202111074371.0A patent/CN113742319A/en active Pending
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20150019488A1 (en) * | 2013-07-09 | 2015-01-15 | Oracle International Corporation | Automated database migration architecture |
| CN109460439A (en) * | 2018-11-14 | 2019-03-12 | 泰康保险集团股份有限公司 | A kind of data processing method, device, medium and electronic equipment |
| CN110162517A (en) * | 2019-05-30 | 2019-08-23 | 深圳前海微众银行股份有限公司 | Data migration method, device, equipment and computer readable storage medium |
| CN113297182A (en) * | 2021-06-16 | 2021-08-24 | 中国农业银行股份有限公司 | Data migration method, device, storage medium and program product |
| CN113312336A (en) * | 2021-06-22 | 2021-08-27 | 中国农业银行股份有限公司 | Data processing method, data processing apparatus, electronic device, storage medium, and program product |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN114138714A (en) * | 2022-02-08 | 2022-03-04 | 上海金仕达软件科技有限公司 | Data migration method and device, storage medium and electronic equipment |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111324647A (en) | Method and device for generating ETL code | |
| CN102541521B (en) | Automatic operating instruction generating device based on structured query language and method | |
| US8417998B2 (en) | Targeted black box fuzzing of input data | |
| CN104133772A (en) | Automatic test data generation method | |
| CN110209650A (en) | The regular moving method of data, device, computer equipment and storage medium | |
| CN108241632B (en) | Data verification method oriented to database data migration | |
| CN103226485A (en) | Code publishing method, machine and system | |
| CN110955661B (en) | Data fusion method and device, readable storage medium and electronic equipment | |
| CN104102511A (en) | SQL (structured query language) Server based automatic script upgrading system and method | |
| CN113742319A (en) | Data migration method of different database management systems based on expect | |
| CN103810099A (en) | Code tracing method and code tracing system | |
| CN109684304A (en) | Data migration method and device | |
| CN106445618A (en) | Automatic database updating method | |
| CN111966760B (en) | Test data generation method and device based on Hive data warehouse | |
| CN104991810A (en) | Method and processing system for automatically adding APK into Android system | |
| EP2904520B1 (en) | Reference data segmentation from single to multiple tables | |
| CN104598382A (en) | Test control method and device | |
| JP2009223851A (en) | Method for migrating database system | |
| JP7131119B2 (en) | Systems and methods for merging source data from a source application with target data in a target application | |
| CN112181951B (en) | Heterogeneous database data migration method, device and equipment | |
| TW201732648A (en) | Source code error detection device and method thereof | |
| CN115437639A (en) | Automatic generation method and device of compiling configuration file, electronic equipment and storage medium | |
| CN113433885A (en) | Method for converting ladder diagram of PLC programming system into structured text | |
| CN114090547A (en) | Data migration method and device, computer equipment and storage medium | |
| EP2781977A1 (en) | Method and system for managing distributed computing in automation systems |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20211203 |