mysqlimport loads tables from text files in various formats. The base name of the text file must be the name of the table that should be used. If one uses sockets to connect to the MariaDB server, the server will open and read the text file directly. In other cases the client will open the text file. The SQL command LOAD DATA INFILE is used to import the rows.

Using mysqlimport

The command to use mysqlimport and the general syntax is:

mysqlimport [OPTIONS] database textfile1 [textfile2 ...]


mysqlimport supports the following options:

--character-sets-dir=nameDirectory for character set files.
-c cols, --columns=colsUse only these columns to import the data to. Give the column names in a comma separated list. This is same as giving columns to LOAD DATA INFILE.
-C, --compressUse compression in server/client protocol.
-# [options] , --debug[=options]Output debug log. Often this is d:t:o,filename. The default is d:t:o.
--debug-checkCheck memory and open file usage at exit.
--debug-infoPrint some debug info at exit.
--default-auth=pluginDefault authentication client-side plugin to use.
--default-character-set=nameSet the default character set.
--defaults-extra-file=nameRead this file after the global files are read. Must be given as the first option.
--defaults-file=nameOnly read default options from the given file name Must be given as the first option.
--defaults-group-suffix=nameIn addition to the given groups, also read groups with this suffix.
-d, --deleteFirst delete all rows from table.
--fields-terminated-by=nameFields in the input file are terminated by the given string.
--fields-enclosed-by=nameFields in the import file are enclosed by the given character.
--fields-optionally-enclosed-by=nameFields in the input file are optionally enclosed by the given character.
--fields-escaped-by=nameFields in the input file are escaped by the given character.
-f, --forceContinue even if we get an SQL error.
-?, --helpDisplays this help and exits.
-h name, --host=nameConnect to host.
-i, --ignoreIf duplicate unique key was found, keep old row.
--ignore-lines=nIgnore first n lines of data infile.
--lines-terminated-by=nameLines in the input file are terminated by the given string.
-L, --localRead all files through the client.
-l, --lock-tablesLock all tables for write (this disables threads).
--low-priorityUse LOW_PRIORITY when updating the table.
--no-defaultsDon't read default options from any option file. Must be given as the first option.
-p[passwd], --password[=passwd]Password to use when connecting to server. If password is not given it's asked from the terminal. Specifying a password on the command line should be considered insecure. You can use an option file to avoid giving the password on the command line.
--pipe, -WOn Windows, connect to the server via a named pipe. This option applies only if the server supports named-pipe connections.
--plugin-dirDirectory for client-side plugins.
-P num, --port=numPort number to use for connection or 0 for default to, in order of preference, my.cnf, the MYSQL_TCP_PORT environment variable, /etc/services, built-in default (3306).
--print-defaultsPrint the program argument list and exit. Must be given as the first option.
--protocol=nameThe protocol to use for connection (tcp, socket, pipe, memory).
-r, --replaceIf duplicate unique key was found, replace old row.
--shared-memory-base-nameShared-memory name to use for Windows connections using shared memory to a local server (started with the --shared-memory option). Case-sensitive.
-s, --silentSilent mode. Produce output only when errors occur.
-S, --socket=nameFor connections to localhost, the Unix socket file to use, or, on Windows, the name of the named pipe to use.
--sslEnable TLS for connection (automatically enabled with other flags).Disable with '--skip-ssl'.
--ssl-ca=nameCA file in PEM format (check OpenSSL docs, implies --ssl).
--ssl-capath=nameCA directory (check OpenSSL docs, implies --ssl).
--ssl-cert=nameX509 cert in PEM format (implies --ssl).
--ssl-cipher=nameSSL cipher to use (implies --ssl).
--ssl-key=nameX509 key in PEM format (implies --ssl).
--ssl-crl=nameCertificate revocation list (implies --ssl).
--ssl-crlpath=nameCertificate revocation list path (implies --ssl).
--ssl-verify-server-certVerify server's "Common Name" in its cert against hostname used when connecting. This option is disabled by default.
--use-threads=numLoad files in parallel. The argument is the number of threads to use for loading data.
-u name, --user=nameUser for login if not current user.
-v, --verbosePrint info about the various stages.
-V, --versionOutput version information and exit.

Option Files

In addition to reading options from the command-line, mysqlimport can also read options from option files. If an unknown option is provided to mysqlimport in an option file, then it is ignored.

The following options relate to how MariaDB command-line tools handles option files. They must be given as the first argument on the command-line:

--print-defaultsPrint the program argument list and exit.
--no-defaultsDon't read default options from any option file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.

In MariaDB 10.2 and later, mysqlimport is linked with MariaDB Connector/C. Therefore, it may be helpful to see Configuring MariaDB Connector/C with Option Files for more information on how MariaDB Connector/C handles option files.

Option Groups

mysqlimport reads options from the following option groups from option files:

[mysqlimport] Options read by mysqlimport, which includes both MariaDB Server and MySQL Server.
[client] Options read by all MariaDB and MySQL client programs, which includes both MariaDB and MySQL clients. For example, mysqldump.
[client-server]Options read by all MariaDB client programs and the MariaDB Server. This is useful for options like socket and port, which is common between the server and the clients.
[client-mariadb]Options read by all MariaDB client programs.

Default values

Variables (--variable-name=value) and boolean options {FALSE|TRUE}Value (after reading options)
character-sets-dir(No default value)
columns(No default value)
fields-terminated-by(No default value)
fields-enclosed-by(No default value)
fields-optionally-enclosed-by(No default value)
fields-escaped-by(No default value)
host(No default value)
lines-terminated-by(No default value)
ssl-ca(No default value)
ssl-capath(No default value)
ssl-cert(No default value)
ssl-cipher(No default value)
ssl-key(No default value)
user(No default value)


Comments loading...