Import Database to a Local MySQL Instance
Last updated on: 2021-09-03
Authored by: Alfonso Murillo
Import database to a local MySQL instance
This article provides a basic guide to import a database to a local MySQL instance from text files through the command-line by using the
mysqlimport client, which is a program included in MySQL.
mysqlimport program offers a simplified command-line interface for the
LOAD DATA MySQL statement, which reads rows from a text file to add them to tables in the database.
mysqlimport sends a
LOAD DATA statement to the server.
mysqlimportprogram strips the file’s extension to identify the table’s name, so the file’s name is important. If the target table is called
test_table, the file can be named
test_table.tab, or any other extension.
- The target table should exist in the table. In case you get an error because the table does not exist make sure that it is created before using the
mysqlimporttool and that the file name matches the table name correctly.
Using the mysqlimport client
The syntax for the `mysqlimport´ program is as follows:
mysqlimport [OPTIONS] DB_NAME TEXT_FILE_1 [TEXT_FILE_2 ... ]
mysqlimport command supports multiple options. In this section, we will describe some of the most useful options.
-u is the MySQL username that will be used to connecting to the server.
You can also add the
-p flag to specify the user’s password to connect to the server. Note that using this option requires the password to be written into the command invocation, which is insecure as it saves in the command history log. If you do not add this flag the password will be asked for after executing the command.
In case the user does not need any password to access the server you can avoid the terminal prompting for one by using the
mysqlimport -u USERNAME -password NOT_SO_SECURE_PASSWORD db_name table_name.txt
Read local files
-L indicates that the file will be found on the client. If this flag is not added the file will be searched on the server.
mysqlimport -u USERNAME --local db_name table_name.txt
Compress the information
To compress the information sent between the client and the server (if possible) use the
Specify the columns names
By using the
--columns option and providing a comma-separated list you can define the name for the imported columns.
mysqlimport -u USERNAME --columns column1,column2,column3 --local db_name table_name.txt
Some other common options are listed below:
--delete: empties the target table before importing the new data.
--lock-tables: locks all the tables preventing any writing. This allows that all tables are correctly synchronized.
-h: indicates the host where the data will be imported. The default value is
-P: indicated the TCP/IP port that will be used for the connection.
mysqlimport client is really useful to import information to existing tables in a MySQL instance inside the same server (local) or to another server by using the
--port options in a fast and reliable manner.
The options mentioned in this article will allow you to make an easy import with specific criteria.
For the complete guide of `mysqlimport´ options, you can visit the official mysqlimport documentation.