mysqlimport – Import data from the command line for the MySQL server

The mysqlimport command is used to import data from a file into a MySQL database. This command is a client-side utility that can be used to import data from a file in various formats such as CSV, TSV, and XML. This command is available on Linux, macOS, and Windows operating systems.

Overview

The basic syntax of the mysqlimport command is as follows:

mysqlimport [options] database file_name

Here, database is the name of the database where the data is to be imported, and file_name is the name of the file containing the data to be imported.

Examples

• Import data from a CSV file into a MySQL database:

mysqlimport --fields-terminated-by=, --columns=id,name,age testdb data.csv

In this example, the --fields-terminated-by option specifies that the fields in the CSV file are separated by commas. The --columns option specifies the columns in the table where the data is to be imported.

• Import data from a TSV file into a MySQL database:

mysqlimport --fields-terminated-by='\t' --columns=id,name,age testdb data.tsv

In this example, the --fields-terminated-by option specifies that the fields in the TSV file are separated by tabs.

• Import data from an XML file into a MySQL database:

mysqlimport --xml --columns=id,name,age testdb data.xml

In this example, the --xml option specifies that the input file is in XML format.

Specific use cases

• Importing large amounts of data from a file into a MySQL database.
• Automating the import process using scripts or cron jobs.
• Importing data from different file formats into a MySQL database.

Options

The following table lists the available options for the mysqlimport command:

Option	Description
-h, –host=host_name	Connect to the MySQL server on the specified host.
-P, –port=port_number	Connect to the MySQL server on the specified port number.
-u, –user=user_name	Use the specified MySQL user name to connect to the server.
-p, –password=password	Use the specified password to connect to the MySQL server.
–compress	Use compression when transferring data between the client and server.
–fields-terminated-by=separator	Specify the separator character used in the input file.
–fields-enclosed-by=delimiter	Specify the delimiter character used to enclose fields in the input file.
–fields-optionally-enclosed-by=delimiter	Specify the delimiter character used to optionally enclose fields in the input file.
–lines-terminated-by=separator	Specify the separator character used to terminate lines in the input file.
–ignore-lines=number	Ignore the specified number of lines at the beginning of the input file.
–columns=column_list	Specify the columns in the table where the data is to be imported.
–local	Read the input file from the client host rather than the server host.
–xml	Specify that the input file is in XML format.
–replace	Replace existing rows in the table with the imported data.
–ignore	Ignore rows in the input file that duplicate existing rows in the table.

Troubleshooting tips

• If the mysqlimport command is not recognized, make sure that the MySQL client utilities are installed on your system.
• Make sure that you have the necessary permissions to access the MySQL server and the database where the data is to be imported.
• Check the input file for errors or missing data.
• Check the syntax of the mysqlimport command and make sure that all options are specified correctly.

Notes

• The mysqlimport command is a client-side utility and cannot be used to import data directly into a remote MySQL server.
• The input file must be in a format that is supported by the mysqlimport command.
• The mysqlimport command can be used with other MySQL client utilities such as mysql and mysqldump to automate the import process.