Home / Data Back Up / Import Data / Question No: 176

Can you explain, how to use mysqlimport client to import data into MySQL table?

The mysqlimport client is used to import data into MySQL table.  It is really just a command-line version of the LOAD DATA INFILE SQL statement. Its general syntax follows:

mysqlimport [options] database textfile1 [textfile2 ... textfileN]

Useful options of mysqlimport client

• --columns, -c: This option should be used when the number or ordering of the fields in the target file does not match that found in the table. For example, suppose you were inserting the following target file, which orders the fields as client_id, order_id, sub_total, shipping_cost, total_cost, and order_time:

  45633,12309,22.04,5.67,27.71,2005-12-19 01:13:42
  942,12310,11.50,3.40,14.90,2005-12-19 01:15:12
  7879,12311,95.99,15.00,110.99,2005-12-19 01:15:22
  

• Suppose your table lists the fields in this order: order_id, client_id, order_time, sub_total, shipping_cost, and total_cost. You can re-arrange the input fields during the parsing process so that the data is inserted in the proper location, by including this option:

  --columns=client_id,order_id,sub_total,shipping_cost,total_cost,and order_time

• --compress, -C: Including this option compresses the data flowing between the client and the server, assuming that both support compression. This option is most effective if you’re loading a target file that does not reside on the same server as the database.
• --debug, -#: This option is used to create trace files when debugging.
• --delete, -d: This option deletes the target table’s contents before importing the targetfile’s data.
• --fields-terminated-by=, --fields-enclosed-by=, --fields-optionally-enclosed-by=,--fields-escaped-by=: These four options determine mysqlimport’s behavior in terms of how both fields and lines are recognized during the parsing procedure.
  
• --force, -f: Including this option causes mysqlimport to continue execution even if errors occur during execution.
• --help, -?: Including this option generates a short help file and a comprehensive list of the options discussed in this section.
• --host, -h: This option specifies the server location of the target database. The default is localhost
• --ignore, -i: This option causes mysqlimport to ignore any rows located in the target file that share the same primary or unique key as a row already located in the table.
• --ignore-lines=n: This option tells mysqlimport to ignore the first n lines of the targetfile. It's useful when the target file contains header information that should bedisregarded.
• --lines-terminated-by=: This option determines how mysqlimport will recognize each separate line in the file.
• --lock-tables, -l: This option write-locks all tables located in the target database for the duration of mysqlimport’s execution.
• --local, -L: This option specifies that the target file is located on the client. By default, it is assumed that this file is located on the database server; therefore, you need to includethis option if you’re executing this command remotely and have not uploaded the file to the server.
• --low-priority: This option delays execution of mysqlimport until no other clients are reading from the table.
• --password=your_password, -pyour_password: This option is used to specify the password component of your authentication credentials. If the your_password part of this option is omitted, you will be prompted for the password.
• --port, -P: If the target MySQL server is running on a nonstandard port (MySQL’s standard port is 3306), you need to specify that port value with this option.
• --replace, -r: This option causes mysqlimport to overwrite any rows located in the targetfile that share the same primary or unique key as a row already located in the table.
• --silent, -s: This option tells mysqlimport to output only error information.
• --socket, -S: This option should be included if a nondefault socket file had been declared when the MySQL server was started.
• --ssl: This option specifies that SSL should be used for the connection.
• --user, -u: By default, mysqlimport compares the name/host combination of the executing system user to the mysql privilege tables, ensuring that the executing user possesses adequate permissions to carry out the requested operation. Because it’s often useful toperform such procedures under the guise of another user, you can specify the “user”component of credentials with this option.
• --verbose, -v: This option causes mysqlimport to output a host of potentially useful information pertinent to its behavior.
• --version, -V: This option causes mysqlimport to output version information and exit.