Write in front: the blogger is a real combat development after training into the cause of the “hill pig”, nickname from the cartoon “Lion King” in “Peng Peng”, always optimistic, positive attitude towards things around. My technical path from Java full stack engineer all the way to big data development, data mining field, now there are small achievements, I would like to share with you what I have learned in the past, I hope to help you on the way of learning. At the same time, the blogger also wants to build a perfect technical library through this attempt. Any anomalies, errors and matters needing attention related to the technical points of the article will be listed at the end, and everyone is welcome to provide materials in various ways.

  • Please criticize any mistakes in the article and revise them in time.
  • If you have any questions you would like to discuss or learn, please contact me at [email protected].
  • The style of the published article varies from column to column, and all are self-contained. Please correct the deficiencies.

Several ways of data migration – MySQL database

Key words: MySQL, database, data migration, import, export \

The article directories

Once we start playing with databases, we’ll be working with SQL and data all the time. In the daily operation, we only need to operate the specified database, the implementation of the increase, deletion, change and check, authority management, etc. But sometimes due to project upgrades, or server changes, we have to move data from one place to another, specifically from one database service to another database service, because we continue to use the data.

I. Data migration

The operation of data migration is very common in the process of application operation or data use. Here, in order to let you have a better understanding of data, we first give you a popular knowledge of the life cycle of data.

1. Data lifecycle

In the process of data use, there is also a life cycle running through it:

  • Create: Create is the starting point of data, corresponding to service logic, such as user registration, card opening, and registration. At this time, data flows from the objective world to the database.
  • Storage: Storage is the process of data survival. It is managed as needed and has strict access control in complex systems.
  • Usage: Usage is the meaning of the existence of data. Data will be changed and queried under the condition of ensuring data security.
  • Sharing: Data sharing is often involved in the use of data to connect platforms or subsystems. Sharing data properly helps to better support and develop services.
  • Archiving: Once the data is archived, it means that the data is used less frequently. Archived data is generally stored in a medium suitable for long-term storage and encrypted if necessary.
  • Destruction: Data destruction represents the end of the data life cycle. Data is completely deleted, but in the era of big data, data is the treasure, and usually only archived.

2. Data migration occurs

To quote wikipedia’s explanation of data migration, we can say that it is user-friendly and comprehensive:

Data migration is the process of selecting, preparing, extracting, and transforming data and transferring it permanently from one computer storage system to another. In addition, verifying the integrity of migrated data and decommissioning old data stores are considered part of the overall data migration process. Data migration is a key consideration for any system implementation, upgrade, or integration, and is usually performed as automatically as possible, freeing human resources from tedious tasks. There are many reasons for data migration, including server or storage device replacement, maintenance or upgrade, application migration, website integration, disaster recovery, and data center migration.

Data migration can be divided into data export and data import according to the direction of data flow. In this way, there is usually an intermediate file, which may be a FILE in SQL format or a data file in various formats. Save these files on disk and import them to another database when needed. This method generates files, but data can be recovered at any time. Another way is to directly transfer data when both the original database service and the target database service are enabled. Because there are various formats of data files and various methods of data migration, this article only introduces the common dump SQL, running SQL, data transfer, and data synchronization.

Second, data export

As a universal database language, SQL can convert the structure and data in the database into complete SQL statements, including table building sentences and data insertion statements, and automatically generate SQL in a reasonable order according to the relationship between tables. Typically, if we migrate data by exporting data files, the SQL format will be preferred, even across libraries (since SQL syntax is universal, data can be migrated between different DBMSS after processing).

1. Command mode

  • Export single table data

If you want to export data from a single table, you must have configured it in advancesecure-file-privOption to specify an export directory. If not configured, the export cannot be performed and a new file will be generated when exported.



If this is the first configuration, restart the MySQL service after the configuration. (For Windows, use Windows path.)

SELECT * FROM` ` table nameINTO OUTFILE 'File path';
Copy the code



The export of single table data exists in the form of data files. Each row of data is separated by delimiters. You can directly open it with Notepad or directly use it during data import.



At the same time, we can also usemysqldumpThe tool directly exports to SQL files, which is actually a backup tool, support data table and database export operations.

mysqldump -U a username-P Database name Indicates the name of the data table> 'File path'
Copy the code



Table building sentences and data insertion statements are generated.

  • Database dump
mysqldump -U a username-P Database name> 'File path' 
Copy the code



All tables in the database will be automatically generated table building sentences and data insert statements.

2. Software

If you haven’t used Navicat before, you can check out this article to see the basic operation: MySQL database interface – Navicat common features

  • Export single table data

Using interface tools to export data is very simple, take Navicat as an example. Select one of the tables and right click:Export wizard.



Select the corresponding data file format as required:











The SQL file exported in this way contains only the data, not the table structure, as follows:

  • Database dump

Open the database connection, select the database you want to export, right-click:Dump SQL -> Structure + data -> Choose the path.

Data import

After the data file or SQL file is prepared, you can import data. If the SQL file does not contain table construction clauses, you need to create a table structure to ensure that the field names, field types (length), and constraints are compatible with data.

1. Command mode

  • Single table data import

For exported DATA files, use the LOAD DATA INFILE statement to insert DATA:

load data local infile 'File path' into tableThe name of the table.Copy the code



After the import is complete, the execution status is displayed. If there are primary key constraints or uniqueness constraints in the table, the same data will be skipped.

  • SQL import

If an executable SQL file is generated directly during data export, you can use the source command to execute the file, which is applicable to both single-table data import and the entire database import.

Source File path;Copy the code



When the SQL file is loaded, the execution result of each SQL statement is displayed, with Query OK representing success and Rows affected representing the number of rows affected by the current statement on the database. (0 ROWS affected corresponds to the statement and various instructions, and the execution is successful if Query OK is displayed.)

2. Software

  • Single table data import

Select the table from which you want to import data and right-click:The import wizard.

















Select the import mode as required and click Start.

  • SQL import

If you use an existing SQL file to import data, you can directly open a database, create a query window, and then paste the content in the SQL file into the window to execute the data import. This method applies to both single-table data import and database import.

In addition, if we want to import the entire database, we can also right-click the database:Run the SQL.



4. Data transmission and data synchronization

1. Data transmission

The effect of using the data transfer function is similar to that of importing data through SQL statements.The existing tables are usually deleted first, this point must be noted, so that the end result is the same as the data source, complete data migration.

Click on the Tools menu in data Transfer to start using:









2. Synchronize data

The procedure for data synchronization is similar to that for data transmission. You need to specify the data source and target database first. The difference is that data synchronization compares the differences between the two databases first and users can decide whether to synchronize the data.