15.3. Importing and Exporting Data from the Command Line

The mysqlimport and mysqldump client programs provide a command-line interface for importing and exporting data. mysqlimport imports data files into tables. mysqldump exports tables to data files.

15.3.1. Importing Data with mysqlimport

The mysqlimport client program loads data files into tables. It provides a command-line interface to the LOAD DATA INFILE statement. That is, mysqlimport examines the options given on the command line. It then connects to the server and, for each input file named in the command, issues a LOAD DATA INFILE statement that loads the file into the appropriate table.

Because mysqlimport works this way, to use it most effectively, you should be familiar with the LOAD DATA INFILE statement, which is discussed in Section 15.2.1, "Importing Data with LOAD DATA INFILE." This section describes mysqlimport invocation syntax and how its options correspond to various clauses of the LOAD DATA INFILE statement.

Invoke mysqlimport from the command line as follows:

 shell> mysqlimport options db_name input_file ...  

db_name names the database containing the table to be loaded and input_file names the file that contains the data to be loaded. You can name several input files following the database name if you like.

mysqlimport uses each filename to determine the name of the corresponding table into which the file's contents should be loaded. The program does this by stripping off any filename extension (the last period and anything following it) and using the result as the table name. For example, mysqlimport treats a file named City.txt or City.dat as input to be loaded into a table named City. After determining the table name corresponding to the filename, mysqlimport issues a LOAD DATA INFILE statement to load the file into the table.

Each table to be loaded by mysqlimport must already exist, and each input file should contain only data values. mysqlimport isn't intended for processing dump files that consist of SQL statements. (Such files can be created with the mysqldump program; for instructions on processing an SQL-format dump file, see Section 32.8.1, "Reloading mysqldump Output.")

The options part of the mysqlimport command may include any of the standard connection parameter options, such as --host or --user. You'll need to supply these options if the default connection parameters aren't appropriate. mysqlimport also understands options specific to its own operation. Invoke mysqlimport with the --help option to see a complete list of the options that can be used to tell mysqlimport the actions you want it to perform.

By default, input files for mysqlimport are assumed to contain lines terminated by newlines, with each line containing tab-separated data values. This is the same default format assumed by the LOAD DATA INFILE statement. For an input file that's in a different format, use the following options to tell mysqlimport how to interpret the file:

• --lines-terminated-by=string

  string specifies the character sequence that each input line ends with. The default is \n (linefeed, also known as newline); other common line terminators are \r (carriage return) and \r\n (carriage return/linefeed pairs).

• --fields-terminated-by=string

  string specifies the delimiter between data values within input lines. The default delimiter is \t (tab).

• --fields-enclosed-by=char or --fields-optionally-enclosed-by=char

  char indicates a quote character that surrounds data values in the file. By default, values are assumed to be unquoted. Use one of these options if values are quoted. A common value for char is the double quote character ('"'). If quote characters enclose a data value, they're removed before the value is loaded into the table.

• --fields-escaped-by=char

  By default, '\' within the input is taken as an escape character that signifies a special sequence. For example, if the \N sequence occurs alone in a field, it's interpreted as a NULL value. Use this option to specify a different escape character. To turn escaping off (no escape character), specify an empty value for char.

The preceding options give you the flexibility to load input files containing data in a variety of formats. Some examples follow; each one loads an input file named City.txt into the City table in the world database. Commands that are shown on multiple lines should be entered on a single line.

The following command loads a file that has lines ending in carriage return/linefeed pairs:

 shell> mysqlimport --lines-terminated-by="\r\n" world City.txt 

Note that the --lines-terminated-by value is quoted with double quotes. Format option values often contain special characters, such as backslash, that might have special meaning to your command interpreter. It might be necessary to quote such characters to tell your command interpreter to pass them unchanged to mysqlimport.

The syntax for specifying a double quote is trickier and depends on which command interpreter you use. The following command loads a data file containing values quoted by double quote characters:

 shell> mysqlimport --fields-enclosed-by='"' world City.txt 

This command should work on most Unix shells, which allow the double quote character to be quoted within single quotes. This doesn't work on Windows, where you must specify a double quote within a double-quoted string by escaping it:

 shell> mysqlimport --fields-enclosed-by="\"" world City.txt 

The following command loads a file that has data values separated by commas and lines ending with carriage returns:

 shell> mysqlimport --fields-terminated-by=,           --lines-terminated-by="\r" world City.txt 

Other mysqlimport options provide additional control over data file loading. The following list discusses some of those you're likely to find useful:

• --ignore or --replace

  These options tell mysqlimport how to handle input records that contain unique key values that are already present in the table. Such records result in duplicate-key errors and cannot be loaded by default. --ignore causes duplicates in the input file to be ignored. --replace causes existing records in the table to be replaced by duplicates in the input file. These options correspond to the use of IGNORE or REPLACE with LOAD DATA INFILE.

• --local

  By default, a data file to be loaded into a table is assumed to reside on the server host, allowing the server to read the file directly. This is very efficient, but requires the user running mysqlimport to have the FILE privilege (a powerful privilege normally reserved for administrators). The --local option allows use of a data file that's located locally on the client host where mysqlimport is invoked. With --local, mysqlimport reads the data file and sends it over the network to the server. This allows mysqlimport to read any file on the client host to which the invoker has access, without requiring the invoker to have the FILE privilege. For the --local option to work, the server must be configured to allow local files to be transferred to it. This option corresponds to the use of LOCAL with LOAD DATA INFILE.

15.3.2. Exporting Data with mysqldump

The mysqldump client program dumps table contents to files. It is useful for making database backups or for transferring database contents to another server. mysqldump can export tables as tab-delimited data files or produce SQL-format dump files that contain CREATE TABLE and INSERT statements for re-creating the dumped files. This section discusses how to use mysqldump to export tables as data files. For information on creating SQL-format dump files, see Section 32.4.2, "Making Text Backups with mysqldump."

To use mysqldump to export tables as tab-delimited data files, specify the --tab=dir_name (or -T dir_name) option on the command line. This option causes mysqldump to issue SELECT … INTO OUTFILE statements to tell the MySQL server to write each dumped table as a tab-delimited text file in the dir_name directory. For each table, mysqldump itself writes a file containing a CREATE TABLE statement that you can use to re-create the table before reloading the data file into it.

Invoke mysqldump from the command line as follows:

 shell> mysqldump --tab=dir_name options db_name tbl_name ... 

db_name names the database containing the table to be exported and tbl_name names the table to be exported. To export multiple tables, name all of them following the database name. If you don't provide any table names, mysqldump exports all tables in the database.

The options part of the mysqldump command may include any of the standard connection parameter options, such as --host or --user. You'll need to supply these options if the default connection parameters aren't appropriate. mysqldump also understands options specific to its own operation. Invoke mysqldump with the --help option to see a complete list of the options that can be used to tell mysqldump the actions you want it to perform.

Suppose that you dump the table City from the world database using the /tmp directory as the output directory:

 shell> mysqldump --tab=/tmp world City 

The output consists of a City.sql file containing the CREATE TABLE statement for the table, and a City.txt file containing the table data.

To reload data exported by invoking mysqldump with the --tab option, change location into the dump directory. Then use mysql to process the .sql file that contains the CREATE TABLE statement, and use mysqlimport to load the .txt file that contains the table data:

 shell> cd /tmp shell> mysql world < City.sql shell> mysqlimport world City.txt 

Using --tab to produce tab-delimited dump files is much faster than creating SQL-format files, but you should keep in mind the following points:

• The CREATE TABLE statement for each table table_name is sent by the server to mysqldump, which writes it to a file named table_name.sql in the dump directory on the client host. The .sql files are owned by you.

• The table contents are written directly by the server into a file named table_name.txt in the dump directory on the server host. The .txt files are owned by the server.

• Use of --tab can be confusing because some files are created by the client and some by the server, and because the .sql files have different ownerships than the .txt files. To minimize confusion, run mysqldump on the server host, specify the dump directory using its full pathname so that mysqldump and the server both interpret it as the same location, and specify a dump directory that is writable both to you and to the server.

• The MySQL account that you use for connecting to the server must have the FILE privilege because the dump operation causes the server to write data files on the server host.

• To create only the data files and not the .sql files that contain the CREATE TABLE statements, use the --no-create-info option.

The default data file format produced by the --tab option consists of tab-delimited lines with newline terminators. This is the same default format assumed by the SELECT … INTO OUTFILE statement. To control the format of the data files that mysqldump generates, use the following options:

• --lines-terminated-by=string

  string specifies the character sequence that each input line should end with. The default is \n (linefeed, also known as newline). Other common line terminators are \r (carriage return) and \r\n (carriage return/linefeed pairs).

• --fields-terminated-by=string

  string specifies the delimiter to write between data values within input lines. The default delimiter is \t (tab).

• --fields-enclosed-by=char or --fields-optionally-enclosed-by=char

  char indicates a quote character that should be written surrounding data values. By default, values are not quoted. A common value for char is the double quote character ('"'). With --fields-enclosed-by, all values are quoted. With --fields-optionally-enclosed-by, only values from string columns are quoted.

• --fields-escaped-by=char

  By default, special characters in data values are written preceded by '\' as an escape character, and NULL values are written as \N. Use this option to specify a different escape character. To turn escaping off (no escape character), specify an empty value for char.

The mysqlimport program has similar options for describing the data file format. See the discussion of that program for information about specifying char or string values that contain characters that your command interpreter considers special.

If you combine the --tab option with format-control options such as --fields-terminated-by and --fields-enclosed-by, you should specify the same format-control options with mysqlimport so that it knows how to interpret the data files.

The --all-databases and --databases options for mysqldump are used for dumping multiple databases. You cannot use those options together with --tab, which causes mysqldump to write the files for all dumped tables to a single directory. You would have no way to tell which files correspond to tables in each database.