MySQL Data import recovery (Restore) instance

There are two simple methods in MySQL to load data into a MySQL database from a previously backed up file.

LOAD Data import:
MySQL provides the load data statement as a bulk load. The following is an example declaration that reads a file dump.txt, and loads the table from the current directory into the current database mytbl:

The code is as follows	Copy Code
mysql> LOAD DATA local INFILE ' dump.txt ' into TABLE mytbl;

If the local keyword does not exist, the look of MySQL uses an absolute pathname to find the data file on the server host at the fully specified location, starting with the file system root directory. MySQL reads files from a given location.

By default, the LOAD data assumes that the data file contains multiple lines terminating the line break (newline) and the data values in the row are delimited by tabs.

To specify the file format explicitly, use a fields clause to describe the line in the field, a lines clause that specifies the row terminator sequence. The data file specified by the following load data statement contains the return and newline characters separated by a colon and the end of the line:

The code is as follows	Copy Code
mysql> LOAD DATA local INFILE ' dump.txt ' into TABLE mytbl -> FIELDS terminated by ': ' -> LINES terminated by ' RN ';

LOAD data assumes that the columns in the table of the columns in the data file have the same order. If this is not true, you can specify that a column of table column's data file columns should be mounted. Suppose columns A, B, and C in the table, but the columns B and C for successive columns in the data file can load the file as follows:

The code is as follows	Copy Code
mysql> LOAD DATA local INFILE ' Dump.txt ' -> into TABLE mytbl (b, C, a);

Import Data Mysqlimport
MySQL also includes a named Mysqlimport utility that is loaded directly into the command line as the load data packet.

To load data from Dump.txt to mytbl use, commands at the UNIX prompt below.

The code is as follows	Copy Code
$ mysqlimport-u root-p--local database_name dump.txt Password * * * *

If the format specifier is provided using the Mysqlimport command-line option. The Mysqlimport command corresponds to the previous two load data statements that look like this:

The code is as follows	Copy Code
$ mysqlimport-u root-p--local--fields-terminated-by= ":" --lines-terminated-by= "RN" database_name dump.txt Password * * * *

Mysqlimport the order of the options specified is not important, but they should all precede the name of the database.

The Mysqlimport statement uses the-column option to specify the order of the columns:

The code is as follows	Copy Code
$ mysqlimport-u root-p--local--columns=b,c,a database_name Dump.txt Password * * * *

Working with quotes and special characters:
The fields clause can specify a selection of other formats, except terminated by. By default, the LOAD data assumes a quotation mark and interprets the backslash () as a special character for the escape character. To indicate that the value explicitly refers to the character, use the enclosing; MySQL will remove the character's input processing from the data values on both ends. To change the default escape character, use to escape.

For Mysqlimport quotes and escape values, specify the appropriate command-line options-closed domain-realm escape