Uploading Data using MySQLImport
To upload data via MySQLIMport Command,
you can follow the syntax given below:
mysqlimport [options] db_name textfile1 [textfile2 ...]
Here, db_name refers to the name of the database that contains the tables in which you want to upload the data from files textfile1, textfile2, etc. From all the files mentioned in the command line above, MySQL will take each file individually and strip the extension from the file name. The remaining filename is used to determine the name of the table into which the data needs to be uploaded. For instance, data from files such as Employee.txt, Employee.text, and Employee will be imported into a table named Employee.
Below is the list of the options that MySQL Import supports. You can mention them on the command line or in the [mysqlimport] and [client] groups of an option file.
Option Name Usage
–bind-address – – bind-address=ip_address
Use this to specify a network interface to connect to MySQL Server.
–columns –columns=column_list, -c column_list
It takes a comma-separated list of column names as its input.
–compress –compress, -C
Compress all information sent between client and server. Note that this option has been deprecated as of MySQL 8.0.18.
–compression-algorithms –compression-algorithms=value
Use to configure permitted compression algorithms for connections to server.
–debug –debug[=debug_options], -# [debug_options]Write Debugging Log. A typical debug_options string is d:t:o,file_name where the default is d:t:o.
–debug-check –debug-check
Print debugging information when a program exits
–debug-info –debug-info
Print debugging information, memory, and CPU statistics when program exits
–default-auth –default-auth=plugin
Authentication plugin to use
–default-character-set –default-character-set=charset_name
Specify default character set
–defaults-extra-file –defaults-extra-file=file_name
Read named option file in addition to usual file options.
–defaults-file –defaults-file=file_name
Read-only named option file
–defaults-group-suffix –defaults-group-suffix=str
Option group suffix value
–delete –delete, -D
Empty the table before importing the text file
–enable-cleartext-plugin –enable-cleartext-plugin
Enable cleartext authentication plugin
–fields-enclosed-by,–fields-escaped-by, –fields-optionally-enclosed-by,–fields-terminated-by These options have the same meaning as the corresponding clause for LOAD DATA.
–force –force, -f
Continue even if an SQL error occurs
–get-server-public-key –get-server-public-key
Request RSA public key from the server
–help Display help message and exit
–host –host=host_name, -h host_name
Host on which MySQL server is located
–ignore –ignore, -i
See the description for the –replace option
–ignore-lines –ignore-lines=N
Ignore the first N lines of the data file
–lines-terminated-by –lines-terminated-by=…
This option has the same meaning as the corresponding clause for LOAD DATA
–local –local, -L
Read input files locally from the client host
–lock-tables –lock-tables, -l
Lock all tables for writing before processing any text files
–login-path –login-path=name
Read login path options from .mylogin.cnf
–low-priority –low-priority
Use LOW_PRIORITY when loading the table
–no-defaults –no-defaults
Read no option files
–password –password[=password], -p
[password]Password to use when connecting to server
–password1 –password1[=pass_val]
First multifactor authentication password to use when connecting to server
–password2 –password2[=pass_val]
Second multifactor authentication password to use when connecting to server
–password3 –password3[=pass_val]
Third multifactor authentication password to use when connecting to server
–pipe –pipe, -W
Connect to the server using named pipe (Windows only)
–plugin-dir –plugin-dir=dir_name
Directory where plugins are installed
–port –port=port_num, -P port_num
TCP/IP port number for the connection
–print-defaults –print-defaults
Print default options
–protocol –protocol={TCP|SOCKET|PIPE|MEMORY}
Transport protocol to use
–replace –replace, -r
The –replace and –ignore options control handling of input rows that duplicate existing rows on unique key values
–server-public-key-path –server-public-key-path=file_name
Pathname to file containing RSA public key
–shared-memory-base-name –shared-memory-base-name=name
Shared-memory name for shared-memory connections (Windows only)
–silent –silent, -s
Produce output only when errors occur
–socket –socket=path, -S path
Unix socket file or Windows named pipe to use
–ssl-ca The file that contains the list of trusted SSL Certificate Authorities
–ssl-capath The directory that contains trusted SSL Certificate Authority certificate files
–ssl-cert The file that contains X.509 certificate
–ssl-cipher Permissible ciphers for connection encryption
–ssl-crl The file that contains certificate revocation lists
–ssl-crlpath The directory that contains certificate revocation-list files
–ssl-fips-mode –ssl-fips-mode={OFF|ON|STRICT}
Whether to enable FIPS mode on client-side
–ssl-key The file that contains X.509 key
–ssl-mode The desired security state of connection to the server
–tls-ciphersuites –tls-ciphersuites=ciphersuite_list
Permissible TLSv1.3 ciphersuites for encrypted connections
–tls-version –tls-version=protocol_list
Permissible TLS protocols for encrypted connections
–use-threads –use-threads=N
Number of threads for parallel file-loading
–user –user=user_name, -u user_name
MySQL user name to use when connecting to server
–verbose –verbose, -v
Verbose mode
–version –version, -V
Display version information and exit
–zstd-compression-level –zstd-compression-level=level
Compression level for connections to the server that use zstd compression
B) MySQLImport Example Queries
To understand MySQLImport, let’s go through the following example:
Creating a student Table in a Database called Test and importing Student data
mysql -e 'CREATE TABLE student(id INT, n VARCHAR(30))' test
$> ed
a
231 Mike Rogers
232 Ryan Smith
.
w imptest.txt
32
q
$> od -c imptest.txt
0000000 1 0 0 t M i k e R o g e r s n 1 0
0000020 1 t R a y n S m i t h n
0000040
$> mysqlimport --local test student.txt
test.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
$> mysql -e 'SELECT * FROM student' test
+------+------------+
| id | n |
+------+------------+
| 231 | Mike Rogers |
| 232 | Ryan Smith |
+------+------------+
Note that the file name should be exactly the same as the Table name you want the data imported. If not then you will get an error message.
For, example, here the “stu” table doesn’t exist in the test database. Hence, the filename student.txt should be selected to match the Student Table Name.
$> mysqlimport --local test stu.txt
mysqlimport: Error: Table 'test.stu' doesn't exist, when using table: stu
Top comments (0)