MySQL :: MySQL 5.6 Reference Manual :: 4.5.5 mysqlimport

MySQL 5.6 Reference Manual / ... / mysqlimport — A Data Import Program

4.5.5 mysqlimport — A Data Import Program

The mysqlimport client provides a command-line interface to the LOAD DATA INFILE SQL statement. Most options to mysqlimport correspond directly to clauses of LOAD DATA INFILE syntax. See Section 13.2.6, “LOAD DATA INFILE Syntax”.

Invoke mysqlimport like this:

shell> mysqlimport [options] db_name textfile1 [textfile2 ...]

For each text file named on the command line, mysqlimport strips any extension from the file name and uses the result to determine the name of the table into which to import the file's contents. For example, files named patient.txt, patient.text, and patient all would be imported into a table named patient.

mysqlimport supports the following options, which can be specified on the command line or in the [mysqlimport] and [client] groups of an option file. For information about option files used by MySQL programs, see Section 4.2.6, “Using Option Files”.

Table 4.12 mysqlimport Options

Format	Description	Introduced
--bind-address	Use specified network interface to connect to MySQL Server	5.6.1
--columns	This option takes a comma-separated list of column names as its value
--compress	Compress all information sent between client and server
--debug	Write debugging log
--debug-check	Print debugging information when program exits
--debug-info	Print debugging information, memory, and CPU statistics when program exits
--default-auth	Authentication plugin to use	5.6.2
--default-character-set	Specify default character set
--defaults-extra-file	Read named option file in addition to usual option files
--defaults-file	Read only named option file
--defaults-group-suffix	Option group suffix value
--delete	Empty the table before importing the text file
--enable-cleartext-plugin	Enable cleartext authentication plugin	5.6.28
--fields-enclosed-by	This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-escaped-by	This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-optionally-enclosed-by	This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-terminated-by	This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--force	Continue even if an SQL error occurs
--help	Display help message and exit
--host	Connect to MySQL server on given host
--ignore	See the description for the --replace option
--ignore-lines	Ignore the first N lines of the data file
--lines-terminated-by	This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--local	Read input files locally from the client host
--lock-tables	Lock all tables for writing before processing any text files
--login-path	Read login path options from .mylogin.cnf	5.6.6
--low-priority	Use LOW_PRIORITY when loading the table.
--no-defaults	Read no option files
--password	Password to use when connecting to server
--pipe	On Windows, connect to server using named pipe
--plugin-dir	Directory where plugins are installed	5.6.2
--port	TCP/IP port number to use for connection
--print-defaults	Print default options
--protocol	Connection protocol to use
--replace	The --replace and --ignore options control handling of input rows that duplicate existing rows on unique key values
--secure-auth	Do not send passwords to server in old (pre-4.1) format	5.6.17
--shared-memory-base-name	The name of shared memory to use for shared-memory connections
--silent	Produce output only when errors occur
--socket	For connections to localhost, the Unix socket file to use
--ssl	Enable secure connection
--ssl-ca	Path of file that contains list of trusted SSL CAs
--ssl-capath	Path of directory that contains trusted SSL CA certificates in PEM format
--ssl-cert	Path of file that contains X509 certificate in PEM format
--ssl-cipher	List of permitted ciphers to use for connection encryption
--ssl-crl	Path of file that contains certificate revocation lists	5.6.3
--ssl-crlpath	Path of directory that contains certificate revocation list files	5.6.3
--ssl-key	Path of file that contains X509 key in PEM format
--ssl-mode	Security state of connection to server	5.6.30
--ssl-verify-server-cert	Verify server certificate Common Name value against host name used when connecting to server
--use-threads	Number of threads for parallel file-loading
--user	MySQL user name to use when connecting to server
--verbose	Verbose mode
--version	Display version information and exit

--help, -?
Display a help message and exit.
--bind-address=ip_address
On a computer having multiple network interfaces, use this option to select which interface to use for connecting to the MySQL server.
This option is supported beginning with MySQL 5.6.1.
--character-sets-dir=dir_name
The directory where character sets are installed. See Section 10.5, “Character Set Configuration”.
--columns=column_list, -c column_list
This option takes a comma-separated list of column names as its value. The order of the column names indicates how to match data file columns with table columns.
--compress, -C
Compress all information sent between the client and the server if both support compression.
--debug[=debug_options], -# [debug_options]
Write a debugging log. A typical debug_options string is d:t:o,file_name. The default is d:t:o.
--debug-check
Print some debugging information when the program exits.
--debug-info
Print debugging information and memory and CPU usage statistics when the program exits.
--default-character-set=charset_name
Use charset_name as the default character set. See Section 10.5, “Character Set Configuration”.
--default-auth=plugin
A hint about the client-side authentication plugin to use. See Section 6.3.7, “Pluggable Authentication”.
This option was added in MySQL 5.6.2.
--defaults-extra-file=file_name
Read this option file after the global option file but (on Unix) before the user option file. If the file does not exist or is otherwise inaccessible, an error occurs. file_name is interpreted relative to the current directory if given as a relative path name rather than a full path name.
--defaults-file=file_name
Use only the given option file. If the file does not exist or is otherwise inaccessible, an error occurs. file_name is interpreted relative to the current directory if given as a relative path name rather than a full path name.
Exception: Even with --defaults-file, client programs read .mylogin.cnf.
--defaults-group-suffix=str
Read not only the usual option groups, but also groups with the usual names and a suffix of str. For example, mysqlimport normally reads the [client] and [mysqlimport] groups. If the --defaults-group-suffix=_other option is given, mysqlimport also reads the [client_other] and [mysqlimport_other] groups.
--delete, -D
Empty the table before importing the text file.
--enable-cleartext-plugin
Enable the mysql_clear_password cleartext authentication plugin. (See Section 6.5.1.7, “The Cleartext Client-Side Authentication Plugin”.)
This option was added in MySQL 5.6.28.
--fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=...
These options have the same meaning as the corresponding clauses for LOAD DATA INFILE. See Section 13.2.6, “LOAD DATA INFILE Syntax”.
--force, -f
Ignore errors. For example, if a table for a text file does not exist, continue processing any remaining files. Without --force, mysqlimport exits if a table does not exist.
--host=host_name, -h host_name
Import data to the MySQL server on the given host. The default host is localhost.
--ignore, -i
See the description for the --replace option.
--ignore-lines=N
Ignore the first N lines of the data file.
--lines-terminated-by=...
This option has the same meaning as the corresponding clause for LOAD DATA INFILE. For example, to import Windows files that have lines terminated with carriage return/linefeed pairs, use --lines-terminated-by="\r\n". (You might have to double the backslashes, depending on the escaping conventions of your command interpreter.) See Section 13.2.6, “LOAD DATA INFILE Syntax”.
--local, -L
Read input files locally from the client host.
--lock-tables, -l
Lock all tables for writing before processing any text files. This ensures that all tables are synchronized on the server.
--login-path=name
Read options from the named login path in the .mylogin.cnf login path file. A “login path” is an option group containing options that specify which MySQL server to connect to and which account to authenticate as. To create or modify a login path file, use the mysql_config_editor utility. See Section 4.6.6, “mysql_config_editor — MySQL Configuration Utility”. This option was added in MySQL 5.6.6.
--low-priority
Use LOW_PRIORITY when loading the table. This affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE).
--no-defaults
Do not read any option files. If program startup fails due to reading unknown options from an option file, --no-defaults can be used to prevent them from being read.
The exception is that the .mylogin.cnf file, if it exists, is read in all cases. This permits passwords to be specified in a safer way than on the command line even when --no-defaults is used. (.mylogin.cnf is created by the mysql_config_editor utility. See Section 4.6.6, “mysql_config_editor — MySQL Configuration Utility”.)
--password[=password], -p[password]
The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, mysqlimport prompts for one.
Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, “End-User Guidelines for Password Security”. You can use an option file to avoid giving the password on the command line.
--pipe, -W
On Windows, connect to the server using a named pipe. This option applies only if the server supports named-pipe connections.
--plugin-dir=dir_name
The directory in which to look for plugins. Specify this option if the --default-auth option is used to specify an authentication plugin but mysqlimport does not find it. See Section 6.3.7, “Pluggable Authentication”.
This option was added in MySQL 5.6.2.
--port=port_num, -P port_num
The TCP/IP port number to use for the connection.
--print-defaults
Print the program name and all options that it gets from option files.
--protocol={TCP|SOCKET|PIPE|MEMORY}
The connection protocol to use for connecting to the server. It is useful when the other connection parameters normally would cause a protocol to be used other than the one you want. For details on the permissible values, see Section 4.2.2, “Connecting to the MySQL Server”.
--replace, -r
The --replace and --ignore options control handling of input rows that duplicate existing rows on unique key values. If you specify --replace, new rows replace existing rows that have the same unique key value. If you specify --ignore, input rows that duplicate an existing row on a unique key value are skipped. If you do not specify either option, an error occurs when a duplicate key value is found, and the rest of the text file is ignored.
--secure-auth
Do not send passwords to the server in old (pre-4.1) format. This prevents connections except for servers that use the newer password format. This option is enabled by default; use --skip-secure-auth to disable it. This option was added in MySQL 5.6.17.

Note

Passwords that use the pre-4.1 hashing method are less secure than passwords that use the native password hashing method and should be avoided. Pre-4.1 passwords are deprecated and support for them will be removed in a future MySQL release. For account upgrade instructions, see Section 6.5.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.

Note

This option is deprecated and will be removed in a future release. As of MySQL 5.7.5, it is always enabled and attempting to disable it produces an error.
--shared-memory-base-name=name
On Windows, the shared-memory name to use, for connections made using shared memory to a local server. The default value is MYSQL. The shared-memory name is case sensitive.
The server must be started with the --shared-memory option to enable shared-memory connections.
--silent, -s
Silent mode. Produce output only when errors occur.
--socket=path, -S path
For connections to localhost, the Unix socket file to use, or, on Windows, the name of the named pipe to use.
--ssl*
Options that begin with --ssl specify whether to connect to the server using SSL and indicate where to find SSL keys and certificates. See Section 6.4.5, “Command Options for Secure Connections”.
--user=user_name, -u user_name
The MySQL user name to use when connecting to the server.
--use-threads=N
Load files in parallel using N threads.
--verbose, -v
Verbose mode. Print more information about what the program does.
--version, -V
Display version information and exit.

Here is a sample session that demonstrates use of mysqlimport:

shell> mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test
shell> ed
a
100     Max Sydow
101     Count Dracula
.
w imptest.txt
32
q
shell> od -c imptest.txt
0000000   1   0   0  \t   M   a   x       S   y   d   o   w  \n   1   0
0000020   1  \t   C   o   u   n   t       D   r   a   c   u   l   a  \n
0000040
shell> mysqlimport --local test imptest.txt
test.imptest: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
shell> mysql -e 'SELECT * FROM imptest' test
+------+---------------+
| id   | n             |
+------+---------------+
|  100 | Max Sydow     |
|  101 | Count Dracula |
+------+---------------+

PREV HOME UP NEXT

User Comments

Posted by Tom Reinertson on May 17, 2002

Keep in mind that your imported text file should
have some value for empty fields. I regularly
build tables using msqlimport to import
tab-delimited text files. My tables contain
integer fields, some of which are
auto_incremented and some are not. MYSQL will
let you represent empty fields as null text
strings, i.e., two tab characters back-to-back,
but I found this increments the warning count.
To solve this problem you must use some value
for empty fields. Since auto_increment fields
use 0 or NULL, one would think, incorrectly,
that you could use 0 or \N to represent a null
value in the import text file. You must 0 for
an auto_increment field. Using \N increments
the warning count. You should use \N for other
numeric fields where you want a null value.
This problem is especially perplexing because of
MySQL's inability to report the text of a
warning. It only reports a warning count.

Posted by Murali Mohan on November 5, 2002

Before you invoke mysqlimport command with
appropriate options, please check that the 'FILE'
privilege is granted to you.
I wasted time facing the 'Access Denied on
table_name' error
because of the same.

Posted by Subburaj Palanichamy on January 29, 2003

Mysqlimport - access_to_mysql.txt - Usage - reg.

While converting the data from Microsoft Access database to Mysql, I have used the access_to_mysql.txt tool. In my database, some of the tables were were linked with another microsoft access database for which password has been set. Hence while converting the data, it displayed an error.

To over come this, I opened the database which has the linked table and removed the password set for that database.

Once password is removed, all the tables and data was successfully transferred to C:\temp\mysqldump.txt file.

Posted by Bli Bla on June 30, 2004

If you get an "access denied" error message,
you may want to try the --local option to mysqlimport.

Posted by Olivier Müller on November 1, 2004

If your mysqlimport's are not working anymore after the upgrade to mysql 4.0.22, try adding this to your my.cnf:

[mysqlimport]
local = 1

Or add "--local" to your scripts...

Posted by Philippe Jausions on February 4, 2005

If you are one of the many people trying to import a CSV file into MySQL using mysqlimport under MS-Windows command/DOS prompt, try the following:

mysqlimport --fields-optionally-enclosed-by=""" --fields-terminated-by=, --lines-terminated-by="\r\n" --user=YOUR_USERNAME --password YOUR_DATABASE YOUR_TABLE.csv

Between quotes " and backslashes \ it can really give you a hard time finding the proper combination under Windows...

I usually run this command from the folder containing the YOUR_TABLE.csv file.

If you have a header in your .csv file with the name of columns or other "junk" in it, just add a --ignore-lines=X to skip the first X lines (i.e. --ignore-lines=1 to skip 1 line)

If your fields are (optionally) enclosed by double-quotes " and which themselves are doubled inside a value (i.e. a double double-quote "" = 1 double-quote ") then also use --fields-escaped-by=\ (default) and NOT --fields-escaped-by="""

I hope this helps someone,

-Philippe

Posted by David Pearce on February 9, 2006

If your use the --delete or -D commands AND you are importing multiple files to the same database, the screen messages indicate it clears the table before each import file. If this is true, you will only ever have the data from the last imported file.

Posted by Tom Wasiluk on October 20, 2006

If you want to skip columns from the import file
try using something like --columns=col1,@x,col2

This will skip the second column, sending it to
the variable @x instead of the destination table.

Posted by Luke Wendling on November 10, 2006

I was getting a 'field too long' error, but using the --columns flag as below worked for me on Windows.

(for localhost import)
mysqlimport --fields-terminated-by=, --lines-terminated-by="\r\n" DBNAME FILENAME -u USERNAME -p --delete --columns=code,city,state,county

-Luke
http://lukewendling.com

Posted by mick m on August 26, 2008

I found I had to use the **complete path** to the table file - rather than using "./mytable.txt":

./bin/mysqlimport --columns=title,body --fields-terminated-by="||" kb /usr/local/mysql/s_kb_entry.txt

Otherwise mysqlimport went looking for the table.txt file in the data directory.

Posted by Manoj Handapangoda on May 14, 2009

This works fine importing csv file to table called tbl_temp_data
import large csv file in to mysql

LOAD DATA LOCAL INFILE C:\test.csv
INTO TABLE tbl_temp_data
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'

If your csv file is less than 1MB and with default mysql configuration this works good, but
if you have csv file size larger than 1MB then it will not import that file.you have to change max packet size varible value (default is 1Mb)

using mysql-administrator GUI you can easily change this
value
In mysql-administrator GUI --> startup varables --> Advanced Networking tab --> data/memory size group box --> Max packet size = ""

.................. have fun with mysql .........

Posted by A C on June 3, 2009

When I used mysqlimport I found both DOS and Linux were just bombing out with showing me the usage blurb again without any reason. I finally figured out that

--fields-optionally-enclosed-by="""

is incorrect, you need to escape the middle " as shown in this full example:

mysqlimport --fields-optionally-enclosed-by="\"" --fields-terminated-by=, --lines-terminated-by="\r\n" --user=YOUR_USERNAME --password YOUR_DATABASE YOUR_TABLE.csv

the above example also assumes you have copied your YOUR_TABLE.csv file into the data directory for YOUR_DATABASE, though I think I read you can specify an absolute path to YOUR_TABLE.csv also.

Posted by Joe Voirol on March 22, 2010

The --local option is VERY important if you want to import a file from anywhere else other than /var/lib/mysql/table_name directory. If you use --local then you can use the FULL path to the file that you want to import and you do not have to make any changes to the directory permissions for the /var/lib/mysql directory to move files around. It took me a little while to understand what this option meant.

I thought I would share this as it was very frustrating.

Posted by Bill Swartz on February 2, 2011

Running mysqlimport v3.7 under Win32. Contrary to expected behavior, double quoting the textfile parameter does not cause the tool to accept directory or file names that contain whitespace.

Posted by Paul Mars on May 23, 2012

Here's how I got mysqlimport to work on Windows:

--fields-optionally-enclosed-by=\"

Posted by josé ambriz meza on December 27, 2012

En español, explicación de la importación
https://www.youtube.com/watch?v=qh8-iX2wddM