1MYSQLIMPORT(1) MySQL Database System MYSQLIMPORT(1)
2
6 mysqlimport - a data import program
7
9 mysqlimport [options] db_name textfile1 ...
10
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 Invoke mysqlimport like this:
17 shell> mysqlimport [options] db_name textfile1 [textfile2 ...]
19 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 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 · --help, -?
32 Display a help message and exit.
34 · --bind-address=ip_address
36 On a computer having multiple network interfaces, use this option
38 to select which interface to use for connecting to the MySQL
39 server.
40 · --character-sets-dir=dir_name
42 The directory where character sets are installed. See
44 Section 10.15, “Character Set Configuration”.
45 · --columns=column_list, -c column_list
47 This option takes a list of comma-separated column names as its
49 value. The order of the column names indicates how to match data
50 file columns with table columns.
51 · --compress, -C
53 Compress all information sent between the client and the server if
55 possible. See Section 4.2.6, “Connection Compression Control”.
56 As of MySQL 8.0.18, this option is deprecated. It will be removed
58 in a future MySQL version. See the section called “Legacy
59 Connection Compression Configuration”.
60 · --compression-algorithms=value The permitted compression algorithms
62 for connections to the server. The available algorithms are the
63 same as for the protocol_compression_algorithms system variable.
64 The default value is uncompressed.
65 For more information, see Section 4.2.6, “Connection Compression
67 Control”.
68 This option was added in MySQL 8.0.18.
70 · --debug[=debug_options], -# [debug_options]
72 Write a debugging log. A typical debug_options string is
74 d:t:o,file_name. The default is d:t:o.
75 This option is available only if MySQL was built using WITH_DEBUG.
77 MySQL release binaries provided by Oracle are not built using this
78 option.
79 · --debug-check
81 Print some debugging information when the program exits.
83 This option is available only if MySQL was built using WITH_DEBUG.
85 MySQL release binaries provided by Oracle are not built using this
86 option.
87 · --debug-info
89 Print debugging information and memory and CPU usage statistics
91 when the program exits.
92 This option is available only if MySQL was built using WITH_DEBUG.
94 MySQL release binaries provided by Oracle are not built using this
95 option.
96 · --default-character-set=charset_name
98 Use charset_name as the default character set. See Section 10.15,
100 “Character Set Configuration”.
101 · --default-auth=plugin
103 A hint about which client-side authentication plugin to use. See
105 Section 6.2.17, “Pluggable Authentication”.
106 · --defaults-extra-file=file_name
108 Read this option file after the global option file but (on Unix)
110 before the user option file. If the file does not exist or is
111 otherwise inaccessible, an error occurs. file_name is interpreted
112 relative to the current directory if given as a relative path name
113 rather than a full path name.
114 For additional information about this and other option-file
116 options, see Section 4.2.2.3, “Command-Line Options that Affect
117 Option-File Handling”.
118 · --defaults-file=file_name
120 Use only the given option file. If the file does not exist or is
122 otherwise inaccessible, an error occurs. file_name is interpreted
123 relative to the current directory if given as a relative path name
124 rather than a full path name.
125 Exception: Even with --defaults-file, client programs read
127 .mylogin.cnf.
128 For additional information about this and other option-file
130 options, see Section 4.2.2.3, “Command-Line Options that Affect
131 Option-File Handling”.
132 · --defaults-group-suffix=str
134 Read not only the usual option groups, but also groups with the
136 usual names and a suffix of str. For example, mysqlimport normally
137 reads the [client] and [mysqlimport] groups. If the
138 --defaults-group-suffix=_other option is given, mysqlimport also
139 reads the [client_other] and [mysqlimport_other] groups.
140 For additional information about this and other option-file
142 options, see Section 4.2.2.3, “Command-Line Options that Affect
143 Option-File Handling”.
144 · --delete, -D
146 Empty the table before importing the text file.
148 · --enable-cleartext-plugin
150 Enable the mysql_clear_password cleartext authentication plugin.
152 (See Section 6.4.1.4, “Client-Side Cleartext Pluggable
153 Authentication”.)
154 · --fields-terminated-by=..., --fields-enclosed-by=...,
156 --fields-optionally-enclosed-by=..., --fields-escaped-by=...
157 These options have the same meaning as the corresponding clauses
159 for LOAD DATA. See Section 13.2.7, “LOAD DATA Statement”.
160 · --force, -f
162 Ignore errors. For example, if a table for a text file does not
164 exist, continue processing any remaining files. Without --force,
165 mysqlimport exits if a table does not exist.
166 · --get-server-public-key
168 Request from the server the public key required for RSA key
170 pair-based password exchange. This option applies to clients that
171 authenticate with the caching_sha2_password authentication plugin.
172 For that plugin, the server does not send the public key unless
173 requested. This option is ignored for accounts that do not
174 authenticate with that plugin. It is also ignored if RSA-based
175 password exchange is not used, as is the case when the client
176 connects to the server using a secure connection.
177 If --server-public-key-path=file_name is given and specifies a
179 valid public key file, it takes precedence over
180 --get-server-public-key.
181 For information about the caching_sha2_password plugin, see
183 Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
184 · --host=host_name, -h host_name
186 Import data to the MySQL server on the given host. The default host
188 is localhost.
189 · --ignore, -i
191 See the description for the --replace option.
193 · --ignore-lines=N
195 Ignore the first N lines of the data file.
197 · --lines-terminated-by=...
199 This option has the same meaning as the corresponding clause for
201 LOAD DATA. For example, to import Windows files that have lines
202 terminated with carriage return/linefeed pairs, use
203 --lines-terminated-by="\r\n". (You might have to double the
204 backslashes, depending on the escaping conventions of your command
205 interpreter.) See Section 13.2.7, “LOAD DATA Statement”.
206 · --local, -L
208 By default, files are read by the server on the server host. With
210 this option, mysqlimport reads input files locally on the client
211 host.
212 Successful use of LOCAL load operations within mysqlimport also
214 requires that the server permits local loading; see Section 6.1.6,
215 “Security Issues with LOAD DATA LOCAL”
216 · --lock-tables, -l
218 Lock all tables for writing before processing any text files. This
220 ensures that all tables are synchronized on the server.
221 · --login-path=name
223 Read options from the named login path in the .mylogin.cnf login
225 path file. A “login path” is an option group containing options
226 that specify which MySQL server to connect to and which account to
227 authenticate as. To create or modify a login path file, use the
228 mysql_config_editor utility. See mysql_config_editor(1).
229 For additional information about this and other option-file
231 options, see Section 4.2.2.3, “Command-Line Options that Affect
232 Option-File Handling”.
233 · --low-priority
235 Use LOW_PRIORITY when loading the table. This affects only storage
237 engines that use only table-level locking (such as MyISAM, MEMORY,
238 and MERGE).
239 · --no-defaults
241 Do not read any option files. If program startup fails due to
243 reading unknown options from an option file, --no-defaults can be
244 used to prevent them from being read.
245 The exception is that the .mylogin.cnf file, if it exists, is read
247 in all cases. This permits passwords to be specified in a safer way
248 than on the command line even when --no-defaults is used.
249 (.mylogin.cnf is created by the mysql_config_editor utility. See
250 mysql_config_editor(1).)
251 For additional information about this and other option-file
253 options, see Section 4.2.2.3, “Command-Line Options that Affect
254 Option-File Handling”.
255 · --password[=password], -p[password]
257 The password of the MySQL account used for connecting to the
259 server. The password value is optional. If not given, mysqlimport
260 prompts for one. If given, there must be no space between
261 --password= or -p and the password following it. If no password
262 option is specified, the default is to send no password.
263 Specifying a password on the command line should be considered
265 insecure. To avoid giving the password on the command line, use an
266 option file. See Section 6.1.2.1, “End-User Guidelines for Password
267 Security”.
268 To explicitly specify that there is no password and that
270 mysqlimport should not prompt for one, use the --skip-password
271 option.
272 · --pipe, -W
274 On Windows, connect to the server using a named pipe. This option
276 applies only if the server was started with the named_pipe system
277 variable enabled to support named-pipe connections. In addition,
278 the user making the connection must be a member of the Windows
279 group specified by the named_pipe_full_access_group system
280 variable.
281 · --plugin-dir=dir_name
283 The directory in which to look for plugins. Specify this option if
285 the --default-auth option is used to specify an authentication
286 plugin but mysqlimport does not find it. See Section 6.2.17,
287 “Pluggable Authentication”.
288 · --port=port_num, -P port_num
290 For TCP/IP connections, the port number to use.
292 · --print-defaults
294 Print the program name and all options that it gets from option
296 files.
297 For additional information about this and other option-file
299 options, see Section 4.2.2.3, “Command-Line Options that Affect
300 Option-File Handling”.
301 · --protocol={TCP|SOCKET|PIPE|MEMORY}
303 The connection protocol to use for connecting to the server. It is
305 useful when the other connection parameters normally result in use
306 of a protocol other than the one you want. For details on the
307 permissible values, see Section 4.2.4, “Connecting to the MySQL
308 Server Using Command Options”.
309 · --replace, -r
311 The --replace and --ignore options control handling of input rows
313 that duplicate existing rows on unique key values. If you specify
314 --replace, new rows replace existing rows that have the same unique
315 key value. If you specify --ignore, input rows that duplicate an
316 existing row on a unique key value are skipped. If you do not
317 specify either option, an error occurs when a duplicate key value
318 is found, and the rest of the text file is ignored.
319 · --server-public-key-path=file_name
321 The path name to a file containing a client-side copy of the public
323 key required by the server for RSA key pair-based password
324 exchange. The file must be in PEM format. This option applies to
325 clients that authenticate with the sha256_password or
326 caching_sha2_password authentication plugin. This option is ignored
327 for accounts that do not authenticate with one of those plugins. It
328 is also ignored if RSA-based password exchange is not used, as is
329 the case when the client connects to the server using a secure
330 connection.
331 If --server-public-key-path=file_name is given and specifies a
333 valid public key file, it takes precedence over
334 --get-server-public-key.
335 For sha256_password, this option applies only if MySQL was built
337 using OpenSSL.
338 For information about the sha256_password and caching_sha2_password
340 plugins, see Section 6.4.1.3, “SHA-256 Pluggable Authentication”,
341 and Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
342 · --shared-memory-base-name=name
344 On Windows, the shared-memory name to use for connections made
346 using shared memory to a local server. The default value is MYSQL.
347 The shared-memory name is case-sensitive.
348 This option applies only if the server was started with the
350 shared_memory system variable enabled to support shared-memory
351 connections.
352 · --silent, -s
354 Silent mode. Produce output only when errors occur.
356 · --socket=path, -S path
358 For connections to localhost, the Unix socket file to use, or, on
360 Windows, the name of the named pipe to use.
361 On Windows, this option applies only if the server was started with
363 the named_pipe system variable enabled to support named-pipe
364 connections. In addition, the user making the connection must be a
365 member of the Windows group specified by the
366 named_pipe_full_access_group system variable.
367 · --ssl*
369 Options that begin with --ssl specify whether to connect to the
371 server using SSL and indicate where to find SSL keys and
372 certificates. See the section called “Command Options for Encrypted
373 Connections”.
374 · --ssl-fips-mode={OFF|ON|STRICT} Controls whether to enable FIPS
376 mode on the client side. The --ssl-fips-mode option differs from
377 other --ssl-xxx options in that it is not used to establish
378 encrypted connections, but rather to affect which cryptographic
379 operations are permitted. See Section 6.5, “FIPS Support”.
380 These --ssl-fips-mode values are permitted:
382 · OFF: Disable FIPS mode.
384 · ON: Enable FIPS mode.
386 · STRICT: Enable “strict” FIPS mode.
388 Note
391 If the OpenSSL FIPS Object Module is not available, the only
392 permitted value for --ssl-fips-mode is OFF. In this case,
393 setting --ssl-fips-mode to ON or STRICT causes the client to
394 produce a warning at startup and to operate in non-FIPS mode.
395 · --tls-ciphersuites=ciphersuite_list
397 The permissible ciphersuites for encrypted connections that use
399 TLSv1.3. The value is a list of one or more colon-separated
400 ciphersuite names. The ciphersuites that can be named for this
401 option depend on the SSL library used to compile MySQL. For
402 details, see Section 6.3.2, “Encrypted Connection TLS Protocols and
403 Ciphers”.
404 This option was added in MySQL 8.0.16.
406 · --tls-version=protocol_list
408 The permissible TLS protocols for encrypted connections. The value
410 is a list of one or more comma-separated protocol names. The
411 protocols that can be named for this option depend on the SSL
412 library used to compile MySQL. For details, see Section 6.3.2,
413 “Encrypted Connection TLS Protocols and Ciphers”.
414 · --user=user_name, -u user_name
416 The user name of the MySQL account to use for connecting to the
418 server.
419 · --use-threads=N
421 Load files in parallel using N threads.
423 · --verbose, -v
425 Verbose mode. Print more information about what the program does.
427 · --version, -V
429 Display version information and exit.
431 · --zstd-compression-level=level The compression level to use for
433 connections to the server that use the zstd compression algorithm.
434 The permitted levels are from 1 to 22, with larger values
435 indicating increasing levels of compression. The default zstd
436 compression level is 3. The compression level setting has no effect
437 on connections that do not use zstd compression.
438 For more information, see Section 4.2.6, “Connection Compression
440 Control”.
441 This option was added in MySQL 8.0.18.
443 Here is a sample session that demonstrates use of mysqlimport:
445 shell> mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test
447 shell> ed
448 a
449 100 Max Sydow
450 101 Count Dracula
451 .
452 w imptest.txt
453 32
454 q
455 shell> od -c imptest.txt
456 0000000 1 0 0 \t M a x S y d o w \n 1 0
457 0000020 1 \t C o u n t D r a c u l a \n
458 0000040
459 shell> mysqlimport --local test imptest.txt
460 test.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
461 shell> mysql -e 'SELECT * FROM imptest' test
462 +------+---------------+
463 | id | n |
464 +------+---------------+
465 | 100 | Max Sydow |
466 | 101 | Count Dracula |
467 +------+---------------+
468
470 Copyright © 1997, 2020, Oracle and/or its affiliates. All rights
471 reserved.
472 This documentation is free software; you can redistribute it and/or
474 modify it only under the terms of the GNU General Public License as
475 published by the Free Software Foundation; version 2 of the License.
476 This documentation is distributed in the hope that it will be useful,
478 but WITHOUT ANY WARRANTY; without even the implied warranty of
479 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
480 General Public License for more details.
481 You should have received a copy of the GNU General Public License along
483 with the program; if not, write to the Free Software Foundation, Inc.,
484 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
485 http://www.gnu.org/licenses/.
486
489 For more information, please refer to the MySQL Reference Manual, which
490 may already be installed locally and which is also available online at
491 http://dev.mysql.com/doc/.
492
494 Oracle Corporation (http://dev.mysql.com/).
495MySQL 8.0 03/06/2020 MYSQLIMPORT(1)