LOAD DATA INFILE & Mysqlimport

+++++++++++++++++++++++++++++++++++++++++++++mysqlimpoRt++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++

In fact, if using Mysqlimport is a command-line program provided by MySQL database, in essence, j Load Data infile command interface, and most of the options are the same as the load data infile syntax.

shell>mysqlimport [option] db_name testfile1 [testfile2 ...]

Select INTO outfile note that the table name is added to. txt for recovery.

Unlike load data infile, Mysqlimport can be used to import multiple tables and concurrently import different files through the--user-thread parameter, where concurrency refers to multiple files being imported concurrently. Instead of referring to Mysqlimport, you can import a file concurrently.

[Email protected] andyxi3306]# MV 1.txt sbtest2.txt
[Email protected] andyxi3306]# MV 2.txt sbtest1.txt
[Email protected] andyxi3306]# mysqlimport-h127.0.0.1-uroot-p--use-threads=2 andyxi3306/tmp/andyxi3306/ Sbtest1.txt/tmp/andyxi3306/sbtest2.txt
Enter Password:
andyxi3306.sbtest1:records:500000 deleted:0 skipped:0 warnings:0
andyxi3306.sbtest2:records:500000 deleted:0 skipped:0 warnings:0

1. Row ***************************
Id:29
User:root
host:localhost:38566
db:andyxi3306
Command:sleep
time:130
State:
Info:null
2. Row ***************************
Id:37
User:root
host:localhost:38582
Db:null
Command:query
time:0
State:starting
Info:show processlist
3. Row ***************************
id:41
User:root
host:localhost:38590
db:andyxi3306
Command:query
Time:15
State:executing
Info:load DATA INFILE '/tmp/andyxi3306/sbtest2.txt ' into TABLE ' Sbtest2 ' IGNORE 0 LINES
4. Row ***************************
Id:42
User:root
host:localhost:38592
db:andyxi3306
Command:query
Time:15
State:executing
Info:LOAD DATA INFILE '/tmp/andyxi3306/sbtest1.txt ' into TABLE ' Sbtest1 ' IGNORE 0 LINES
4 rows in Set (0.00 sec)

Mysqlimport recovery is useful or load data infile

++++++++++++++++++++++++++++++++++++++++Load Data infile+++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++

If you export data by mysqldump plus parameter--tab=path, or through select ... into outfile, you can import it through load data infile.

Grammar:

[Email protected]:3306 [andyxi3306]>help load data
Name: ' LOAD DATA '
Description:
Syntax:
LOAD DATA [Low_priority | CONCURRENT] [LOCAL] INFILE ' file_name '
[REPLACE | IGNORE]
Into TABLE tbl_name
[PARTITION (Partition_name [, Partition_name] ...)]
[CHARACTER SET Charset_name]
[{fields | COLUMNS}
[TERMINATED by ' string ']
[[optionally] enclosed by ' char ']
[Escaped by ' char ']
]
[LINES
[Starting by ' string ']
[TERMINATED by ' string ']
]
[IGNORE Number {LINES | ROWS}]
[(Col_name_or_user_var
[, Col_name_or_user_var] ...)]
[SET col_name={expr | DEFAULT},
[, col_name={expr | DEFAULT}] ...

To use the load data infile for a server file, you must have file rights. The options for importing the format are exactly the same as the SELECT INTO OutFile command.

The Ignore number lines option ignores the first few rows that are imported.

Test:

[Email protected]:3306 [andyxi3306]>select * to outfile '/tmp/andyxi3306/2.txt ' from Sbtest1;

Query OK, 500000 rows affected (2.66 sec)

[Email protected]:3306 [Andyxi3306]>delete from Sbtest1;

Query OK,

500000 rows Affected (37.19 sec)

[Email protected]:3306 [andyxi3306]>select * from Sbtest1;

ERROR (HY000): Lost connection to MySQL server during query

[Email protected]:3306 [andyxi3306]>select * from Sbtest1;

ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect ... Connection id:29 Current database:andyxi3306

Empty Set (0.21 sec)

[email protected]:3306 [andyxi3306]>load data infile '/tmp/andyxi3306/2.txt ' into table sbtest1;

Query OK, 500000 rows affected (43.99 sec) records:500000 deleted:0 skipped:0 warnings:0

[Email protected]:3306 [andyxi3306]>select * from Sbtest1 limit 2\g;

1. Row ***************************

Id:1 k:252272

C: 76529735171-62619715576-43660157506-14122022461-23499333486-12530790621-96252745722-53101978020-30217711642-99182948190 pad:78049175839-40449686376-46985343292-10855530210-03527816365

2. Row ***************************

Id:2 k:251402

C: 35080843840-11823110763-17669742564-34386700636-31442953158-68823209002-35432290765-12996120436-32406040794-96855478492 pad:47097503610-43096903937-36148507150-61897060196-12382094341 2 rows in Set (0.00 sec)

Data Recovery Success

LOAD DATA INFILE & Mysqlimport