1MYSQLIMPORT(1)               MySQL Database System              MYSQLIMPORT(1)
2
3
4

NAME

6       mysqlimport - a data import program
7

SYNOPSIS

9       mysqlimport [options] db_name textfile1 ...
10

DESCRIPTION

12       The mysqlimport client provides a command-line interface to the LOAD
13       DATA SQL statement. Most options to mysqlimport correspond directly to
14       clauses of LOAD DATA syntax. See Section 13.2.7, “LOAD DATA Statement”.
15
16       Invoke mysqlimport like this:
17
18           mysqlimport [options] db_name textfile1 [textfile2 ...]
19
20       For each text file named on the command line, mysqlimport strips any
21       extension from the file name and uses the result to determine the name
22       of the table into which to import the file's contents. For example,
23       files named patient.txt, patient.text, and patient all would be
24       imported into a table named patient.
25
26       mysqlimport supports the following options, which can be specified on
27       the command line or in the [mysqlimport] and [client] groups of an
28       option file. For information about option files used by MySQL programs,
29       see Section 4.2.2.2, “Using Option Files”.
30
31--help, -?  Display a help message and exit.
32
33--bind-address=ip_address On a computer having multiple network
34           interfaces, use this option to select which interface to use for
35           connecting to the MySQL server.
36
37--character-sets-dir=dir_name The directory where character sets
38           are installed. See Section 10.15, “Character Set Configuration”.
39
40--columns=column_list, -c column_list This option takes a list of
41           comma-separated column names as its value. The order of the column
42           names indicates how to match data file columns with table columns.
43
44--compress, -C Compress all information sent between the client and
45           the server if possible. See Section 4.2.8, “Connection Compression
46           Control”.
47
48           As of MySQL 8.0.18, this option is deprecated. Expect it to be
49           removed in a future version of MySQL. See the section called
50           “Configuring Legacy Connection Compression”.
51
52--compression-algorithms=value The permitted compression algorithms
53           for connections to the server. The available algorithms are the
54           same as for the protocol_compression_algorithms system variable.
55           The default value is uncompressed.
56
57           For more information, see Section 4.2.8, “Connection Compression
58           Control”.
59
60           This option was added in MySQL 8.0.18.
61
62--debug[=debug_options], -# [debug_options] Write a debugging log.
63           A typical debug_options string is d:t:o,file_name. The default is
64           d:t:o.
65
66           This option is available only if MySQL was built using WITH_DEBUG.
67           MySQL release binaries provided by Oracle are not built using this
68           option.
69
70--debug-check Print some debugging information when the program
71           exits.
72
73           This option is available only if MySQL was built using WITH_DEBUG.
74           MySQL release binaries provided by Oracle are not built using this
75           option.
76
77--debug-info Print debugging information and memory and CPU usage
78           statistics when the program exits.
79
80           This option is available only if MySQL was built using WITH_DEBUG.
81           MySQL release binaries provided by Oracle are not built using this
82           option.
83
84--default-character-set=charset_name Use charset_name as the
85           default character set. See Section 10.15, “Character Set
86           Configuration”.
87
88--default-auth=plugin A hint about which client-side authentication
89           plugin to use. See Section 6.2.17, “Pluggable Authentication”.
90
91--defaults-extra-file=file_name Read this option file after the
92           global option file but (on Unix) before the user option file. If
93           the file does not exist or is otherwise inaccessible, an error
94           occurs. If file_name is not an absolute path name, it is
95           interpreted relative to the current directory.
96
97           For additional information about this and other option-file
98           options, see Section 4.2.2.3, “Command-Line Options that Affect
99           Option-File Handling”.
100
101--defaults-file=file_name Use only the given option file. If the
102           file does not exist or is otherwise inaccessible, an error occurs.
103           If file_name is not an absolute path name, it is interpreted
104           relative to the current directory.
105
106           Exception: Even with --defaults-file, client programs read
107           .mylogin.cnf.
108
109           For additional information about this and other option-file
110           options, see Section 4.2.2.3, “Command-Line Options that Affect
111           Option-File Handling”.
112
113--defaults-group-suffix=str Read not only the usual option groups,
114           but also groups with the usual names and a suffix of str. For
115           example, mysqlimport normally reads the [client] and [mysqlimport]
116           groups. If this option is given as --defaults-group-suffix=_other,
117           mysqlimport also reads the [client_other] and [mysqlimport_other]
118           groups.
119
120           For additional information about this and other option-file
121           options, see Section 4.2.2.3, “Command-Line Options that Affect
122           Option-File Handling”.
123
124--delete, -D Empty the table before importing the text file.
125
126--enable-cleartext-plugin Enable the mysql_clear_password cleartext
127           authentication plugin. (See Section 6.4.1.4, “Client-Side Cleartext
128           Pluggable Authentication”.)
129
130--fields-terminated-by=..., --fields-enclosed-by=...,
131           --fields-optionally-enclosed-by=..., --fields-escaped-by=...  These
132           options have the same meaning as the corresponding clauses for LOAD
133           DATA. See Section 13.2.7, “LOAD DATA Statement”.
134
135--force, -f Ignore errors. For example, if a table for a text file
136           does not exist, continue processing any remaining files. Without
137           --force, mysqlimport exits if a table does not exist.
138
139--get-server-public-key Request from the server the public key
140           required for RSA key pair-based password exchange. This option
141           applies to clients that authenticate with the caching_sha2_password
142           authentication plugin. For that plugin, the server does not send
143           the public key unless requested. This option is ignored for
144           accounts that do not authenticate with that plugin. It is also
145           ignored if RSA-based password exchange is not used, as is the case
146           when the client connects to the server using a secure connection.
147
148           If --server-public-key-path=file_name is given and specifies a
149           valid public key file, it takes precedence over
150           --get-server-public-key.
151
152           For information about the caching_sha2_password plugin, see
153           Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
154
155--host=host_name, -h host_name Import data to the MySQL server on
156           the given host. The default host is localhost.
157
158--ignore, -i See the description for the --replace option.
159
160--ignore-lines=N Ignore the first N lines of the data file.
161
162--lines-terminated-by=...  This option has the same meaning as the
163           corresponding clause for LOAD DATA. For example, to import Windows
164           files that have lines terminated with carriage return/linefeed
165           pairs, use --lines-terminated-by="\r\n". (You might have to double
166           the backslashes, depending on the escaping conventions of your
167           command interpreter.) See Section 13.2.7, “LOAD DATA Statement”.
168
169--local, -L By default, files are read by the server on the server
170           host. With this option, mysqlimport reads input files locally on
171           the client host.
172
173           Successful use of LOCAL load operations within mysqlimport also
174           requires that the server permits local loading; see Section 6.1.6,
175           “Security Considerations for LOAD DATA LOCAL”
176
177--lock-tables, -l Lock all tables for writing before processing any
178           text files. This ensures that all tables are synchronized on the
179           server.
180
181--login-path=name Read options from the named login path in the
182           .mylogin.cnf login path file. A “login path” is an option group
183           containing options that specify which MySQL server to connect to
184           and which account to authenticate as. To create or modify a login
185           path file, use the mysql_config_editor utility. See
186           mysql_config_editor(1).
187
188           For additional information about this and other option-file
189           options, see Section 4.2.2.3, “Command-Line Options that Affect
190           Option-File Handling”.
191
192--low-priority Use LOW_PRIORITY when loading the table. This
193           affects only storage engines that use only table-level locking
194           (such as MyISAM, MEMORY, and MERGE).
195
196--no-defaults Do not read any option files. If program startup
197           fails due to reading unknown options from an option file,
198           --no-defaults can be used to prevent them from being read.
199
200           The exception is that the .mylogin.cnf file is read in all cases,
201           if it exists. This permits passwords to be specified in a safer way
202           than on the command line even when --no-defaults is used. To create
203           .mylogin.cnf, use the mysql_config_editor utility. See
204           mysql_config_editor(1).
205
206           For additional information about this and other option-file
207           options, see Section 4.2.2.3, “Command-Line Options that Affect
208           Option-File Handling”.
209
210--password[=password], -p[password] The password of the MySQL
211           account used for connecting to the server. The password value is
212           optional. If not given, mysqlimport prompts for one. If given,
213           there must be no space between --password= or -p and the password
214           following it. If no password option is specified, the default is to
215           send no password.
216
217           Specifying a password on the command line should be considered
218           insecure. To avoid giving the password on the command line, use an
219           option file. See Section 6.1.2.1, “End-User Guidelines for Password
220           Security”.
221
222           To explicitly specify that there is no password and that
223           mysqlimport should not prompt for one, use the --skip-password
224           option.
225
226--pipe, -W On Windows, connect to the server using a named pipe.
227           This option applies only if the server was started with the
228           named_pipe system variable enabled to support named-pipe
229           connections. In addition, the user making the connection must be a
230           member of the Windows group specified by the
231           named_pipe_full_access_group system variable.
232
233--plugin-dir=dir_name The directory in which to look for plugins.
234           Specify this option if the --default-auth option is used to specify
235           an authentication plugin but mysqlimport does not find it. See
236           Section 6.2.17, “Pluggable Authentication”.
237
238--port=port_num, -P port_num For TCP/IP connections, the port
239           number to use.
240
241--print-defaults Print the program name and all options that it
242           gets from option files.
243
244           For additional information about this and other option-file
245           options, see Section 4.2.2.3, “Command-Line Options that Affect
246           Option-File Handling”.
247
248--protocol={TCP|SOCKET|PIPE|MEMORY} The transport protocol to use
249           for connecting to the server. It is useful when the other
250           connection parameters normally result in use of a protocol other
251           than the one you want. For details on the permissible values, see
252           Section 4.2.7, “Connection Transport Protocols”.
253
254--replace, -r The --replace and --ignore options control handling
255           of input rows that duplicate existing rows on unique key values. If
256           you specify --replace, new rows replace existing rows that have the
257           same unique key value. If you specify --ignore, input rows that
258           duplicate an existing row on a unique key value are skipped. If you
259           do not specify either option, an error occurs when a duplicate key
260           value is found, and the rest of the text file is ignored.
261
262--server-public-key-path=file_name The path name to a file in PEM
263           format containing a client-side copy of the public key required by
264           the server for RSA key pair-based password exchange. This option
265           applies to clients that authenticate with the sha256_password or
266           caching_sha2_password authentication plugin. This option is ignored
267           for accounts that do not authenticate with one of those plugins. It
268           is also ignored if RSA-based password exchange is not used, as is
269           the case when the client connects to the server using a secure
270           connection.
271
272           If --server-public-key-path=file_name is given and specifies a
273           valid public key file, it takes precedence over
274           --get-server-public-key.
275
276           For sha256_password, this option applies only if MySQL was built
277           using OpenSSL.
278
279           For information about the sha256_password and caching_sha2_password
280           plugins, see Section 6.4.1.3, “SHA-256 Pluggable Authentication”,
281           and Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
282
283--shared-memory-base-name=name On Windows, the shared-memory name
284           to use for connections made using shared memory to a local server.
285           The default value is MYSQL. The shared-memory name is
286           case-sensitive.
287
288           This option applies only if the server was started with the
289           shared_memory system variable enabled to support shared-memory
290           connections.
291
292--silent, -s Silent mode. Produce output only when errors occur.
293
294--socket=path, -S path For connections to localhost, the Unix
295           socket file to use, or, on Windows, the name of the named pipe to
296           use.
297
298           On Windows, this option applies only if the server was started with
299           the named_pipe system variable enabled to support named-pipe
300           connections. In addition, the user making the connection must be a
301           member of the Windows group specified by the
302           named_pipe_full_access_group system variable.
303
304--ssl* Options that begin with --ssl specify whether to connect to
305           the server using encryption and indicate where to find SSL keys and
306           certificates. See the section called “Command Options for Encrypted
307           Connections”.
308
309--ssl-fips-mode={OFF|ON|STRICT} Controls whether to enable FIPS
310           mode on the client side. The --ssl-fips-mode option differs from
311           other --ssl-xxx options in that it is not used to establish
312           encrypted connections, but rather to affect which cryptographic
313           operations to permit. See Section 6.8, “FIPS Support”.
314
315           These --ssl-fips-mode values are permitted:
316
317           •   OFF: Disable FIPS mode.
318
319           •   ON: Enable FIPS mode.
320
321           •   STRICT: Enable “strict” FIPS mode.
322
323
324               Note
325               If the OpenSSL FIPS Object Module is not available, the only
326               permitted value for --ssl-fips-mode is OFF. In this case,
327               setting --ssl-fips-mode to ON or STRICT causes the client to
328               produce a warning at startup and to operate in non-FIPS mode.
329
330--tls-ciphersuites=ciphersuite_list The permissible ciphersuites
331           for encrypted connections that use TLSv1.3. The value is a list of
332           one or more colon-separated ciphersuite names. The ciphersuites
333           that can be named for this option depend on the SSL library used to
334           compile MySQL. For details, see Section 6.3.2, “Encrypted
335           Connection TLS Protocols and Ciphers”.
336
337           This option was added in MySQL 8.0.16.
338
339--tls-version=protocol_list The permissible TLS protocols for
340           encrypted connections. The value is a list of one or more
341           comma-separated protocol names. The protocols that can be named for
342           this option depend on the SSL library used to compile MySQL. For
343           details, see Section 6.3.2, “Encrypted Connection TLS Protocols and
344           Ciphers”.
345
346--user=user_name, -u user_name The user name of the MySQL account
347           to use for connecting to the server.
348
349--use-threads=N Load files in parallel using N threads.
350
351--verbose, -v Verbose mode. Print more information about what the
352           program does.
353
354--version, -V Display version information and exit.
355
356--zstd-compression-level=level The compression level to use for
357           connections to the server that use the zstd compression algorithm.
358           The permitted levels are from 1 to 22, with larger values
359           indicating increasing levels of compression. The default zstd
360           compression level is 3. The compression level setting has no effect
361           on connections that do not use zstd compression.
362
363           For more information, see Section 4.2.8, “Connection Compression
364           Control”.
365
366           This option was added in MySQL 8.0.18.
367
368       Here is a sample session that demonstrates use of mysqlimport:
369
370           shell> mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test
371           shell> ed
372           a
373           100     Max Sydow
374           101     Count Dracula
375           .
376           w imptest.txt
377           32
378           q
379           shell> od -c imptest.txt
380           0000000   1   0   0  \t   M   a   x       S   y   d   o   w  \n   1   0
381           0000020   1  \t   C   o   u   n   t       D   r   a   c   u   l   a  \n
382           0000040
383           shell> mysqlimport --local test imptest.txt
384           test.imptest: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
385           shell> mysql -e 'SELECT * FROM imptest' test
386           +------+---------------+
387           | id   | n             |
388           +------+---------------+
389           |  100 | Max Sydow     |
390           |  101 | Count Dracula |
391           +------+---------------+
392
394       Copyright © 1997, 2021, Oracle and/or its affiliates.
395
396       This documentation is free software; you can redistribute it and/or
397       modify it only under the terms of the GNU General Public License as
398       published by the Free Software Foundation; version 2 of the License.
399
400       This documentation is distributed in the hope that it will be useful,
401       but WITHOUT ANY WARRANTY; without even the implied warranty of
402       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
403       General Public License for more details.
404
405       You should have received a copy of the GNU General Public License along
406       with the program; if not, write to the Free Software Foundation, Inc.,
407       51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
408       http://www.gnu.org/licenses/.
409
410

SEE ALSO

412       For more information, please refer to the MySQL Reference Manual, which
413       may already be installed locally and which is also available online at
414       http://dev.mysql.com/doc/.
415

AUTHOR

417       Oracle Corporation (http://dev.mysql.com/).
418
419
420
421MySQL 8.0                         09/04/2021                    MYSQLIMPORT(1)
Impressum