Simple tutorial for data import and recovery in MySQL _ MySQL

This article mainly introduces a simple tutorial on data import and recovery in MySQL, mainly using the LOADDATA statement, for more information about how to load MySQL data to files backed up from the MySQL database, see the following two simple methods.
Load data import DATA:

MySQL provides a load data Statement to LOAD large DATA volumes. The following is an example of reading a dump.txt file and loading it from the current directory to the mytbl table in the current database:

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

• If the local keyword does not exist, MySQL uses the absolute path name to find the data file of the file at the specified position on the server host, starting from the root directory of the file system. MySQL reads files from a given location.
• By default, load data assumes that the DATA file contains a line break (line feed) and DATA values in the row are separated by tabs.
• To explicitly specify the file format, use a FIELDS clause to describe the sequence of line terminators specified by a line clause in a field. The DATA file specified by the following load data statement contains values separated by colons and line breaks after the end of the line:

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl -> FIELDS TERMINATED BY ':' -> LINES TERMINATED BY '\r\n';

Load data assumes that the columns in the table in the DATA file have the same order. If this is true, you can specify that the data file columns of a column in the table column should be loaded. Assume that the columns A, B, and C in the table, but the columns B and C corresponding to the continuous columns in the data file can load the file as follows:

mysql> LOAD DATA LOCAL INFILE 'dump.txt'   -> INTO TABLE mytbl (b, c, a);

Import data mysqlimport

MySQL also includes a named mysqlimport utility, which is loaded directly in the command line input file as the load data package.

To upload data from dump.txt to mytbl, run the following command at a UNIX prompt.

$ mysqlimport -u root -p --local database_name dump.txtpassword *****

If you use the format specifiers provided by the mysqlimport command line option. The mysqlimport command corresponds to the previous two load data statements that look like this:

$ mysqlimport -u root -p --local --fields-terminated-by=":" \  --lines-terminated-by="\r\n" database_name dump.txtpassword *****

The order of the options specified by mysqlimport is not important, but they should all be prior to the database name.

The mysqlimport statement uses the-column option to specify the column sequence:

$ mysqlimport -u root -p --local --columns=b,c,a \  database_name dump.txtpassword *****

Handle quotation marks and special characters:

The FIELDS clause can specify other formats, except terminated. By default, load data assumes that the value is enclosed by quotation marks and the backslash (\) is used as a special character of the escape character. To explicitly reference a character with a specified value, use a closed character. MySQL deletes the data values at both ends of the character during input and processing. To change the default escape character, use the escape character.

For mysqlimport quotation marks and escape values, it is used to specify the corresponding command line options-closed fields-field escaping