1.mysqlimport概述
mysqlimport是MySQL数据库提供的一个命令行程序，可用于数据导入。从本质上来说，是LOAD DATA INFILE的命令接口，而且大多数的选项都和LOAD DATA INFILE语法相同。其语法格式如下：
shell>mysqlimport [options] db_name textfilel [textfile2 …]
和LOAD DATA INFILE不同的是，mysqlimport命令可以用来导入多张表。并且通过–use-threads=参数并发地导入不同的文件。这里的并发是指并发导入多个文件，而不是指mysqlimport可以并发地导入一个文件，这是有明显区别的。此外，通常来说并发地对同一张表进行导入，其效果一般都不会比串行的方式好。
参数说明：
–use-threads=# Load files in parallel. The argument is the number of threads to use for loading data.
2.演示
2.1导出数据
cd /usr/local/mysql/bin
./mysqldump -uroot -poracle --tab=/data/backup test
使用mysqldump工具导出test库下面所有的表。添加–tab参数表名，导出的每张表的定义输出到一个文件（xxxTAB.sql），每张表的数据输出到另外一个文件（xxxTAB.txt）。
[root@source backup]# cd /usr/local/mysql/bin
[root@source bin]# ./mysqlpump --version
mysqlpump Ver 1.0.0 Distrib 5.7.20, for linux-glibc2.12 (x86_64)
[root@source bin]#
[root@source bin]# ./mysqldump -uroot -poracle --tab=/data/backup test
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@source bin]#
[root@source mysql]# cd /data/backup/
[root@source backup]# ll
total 28
-rw-r--r-- 1 root root 1408 Mar 20 17:37 BONUS.sql
-rw-rw-rw- 1 mysql mysql 0 Mar 20 17:37 BONUS.txt
-rw-r--r-- 1 root root 1400 Mar 20 17:37 DEPT.sql
-rw-rw-rw- 1 mysql mysql 80 Mar 20 17:37 DEPT.txt
-rw-r--r-- 1 root root 1662 Mar 20 17:37 EMP.sql
-rw-rw-rw- 1 mysql mysql 767 Mar 20 17:37 EMP.txt
-rw-r--r-- 1 root root 1383 Mar 20 17:37 SALGRADE.sql
-rw-rw-rw- 1 mysql mysql 59 Mar 20 17:37 SALGRADE.txt
[root@source backup]#
[root@source backup]# more /data/backup/DEPT.sql
-- MySQL dump 10.13 Distrib 5.7.20, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.7.20-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `DEPT`
--
DROP TABLE IF EXISTS `DEPT`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `DEPT` (
`DEPTNO` int(10) NOT NULL,
`DNAME` varchar(14) DEFAULT NULL,
`LOC` varchar(13) DEFAULT NULL,
PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2020-03-20 17:37:49
[root@source backup]#
[root@source backup]# more DEPT.txt
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
[root@source backup]#
2.2新建数据库test1，将数据导入到test1库
[root@source backup]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@db 17:41: [(none)]>
root@db 17:41: [(none)]> create database test1;
Query OK, 1 row affected (0.11 sec)
root@db 17:41: [(none)]>
root@db 17:41: [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| test1 |
+--------------------+
6 rows in set (0.00 sec)
root@db 17:41: [(none)]>
root@db 17:41: [(none)]>
root@db 17:41: [(none)]>
root@db 17:41: [(none)]> exit
Bye
[root@source backup]#
2.3导入数据
2.3.1导入方法1
使用mysql导入定义，使用mysqlimport方法导入数据
create database test1;
mysql -uroot -poracle test1 </data/backup/DEPT.sql
mysqlimport -uroot -poracle --local test1 /data/backup/DEPT.txt
mysqlimport参数说明：
-L, --local Read all files through the client.
[root@source backup]# mysql -uroot -poracle test1 </data/backup/DEPT.sql
[root@source backup]#
[root@source backup]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@db 17:43: [(none)]>
root@db 17:43: [(none)]> USE test1;
Database changed
root@db 17:43: [test1]>
root@db 17:43: [test1]> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| DEPT |
+-----------------+
1 row in set (0.00 sec)
root@db 17:43: [test1]>
root@db 17:43: [test1]> select * from DEPT;
Empty set (0.00 sec)
root@db 17:43: [test1]>
root@db 17:44: [test1]> exit
Bye
[root@source backup]#
[root@source backup]# mysqlimport -uroot -poracle --local test1 /data/backup/DEPT.txt
test1.DEPT: Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
[root@source backup]#
[root@source backup]# mysql -p test1
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.7.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@db 17:46: [test1]>
root@db 17:46: [test1]> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| DEPT |
+-----------------+
1 row in set (0.00 sec)
root@db 17:46: [test1]>
root@db 17:46: [test1]> select * from DEPT;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
root@db 17:46: [test1]>
2.3.2导入方法2
在mysql命令行执行脚本创建命令，再使用load data local infile … into …加载数据
mysql -p test1
source /data/backup/DEPT.sql
load data local infile ‘/data/backup/DEPT.txt’ into table DEPT;
[root@source backup]# mysql -p test1
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@db 17:47: [test1]>
root@db 17:47: [test1]> DROP TABLE DEPT;
Query OK, 0 rows affected (0.06 sec)
root@db 17:47: [test1]> source /data/backup/DEPT.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
root@db 17:47: [test1]>
root@db 17:47: [test1]>
root@db 17:47: [test1]> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| DEPT |
+-----------------+
1 row in set (0.00 sec)
root@db 17:47: [test1]>
root@db 17:47: [test1]> select * from DEPT;
Empty set (0.00 sec)
root@db 17:47: [test1]>
root@db 17:47: [test1]>
root@db 17:49: [test1]> load data local infile '/data/backup/DEPT.txt' into table DEPT;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
root@db 17:49: [test1]>
root@db 17:49: [test1]>
root@db 17:49: [test1]> select * from DEPT;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
root@db 17:49: [test1]>
2.4并行与串行演示
2.4.1环境准备
root@db 11:28: [(none)]> use test1
Database changed
root@db 11:28: [test1]>
root@db 11:28: [test1]> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| DEPT |
+-----------------+
1 rows in set (0.00 sec)
root@db 11:28: [test1]>
root@db 11:31: [test1]> create table sbtest1(id int(10) unsigned primary key,k int(10) unsigned,c char(120),pad char(60));
Query OK, 0 rows affected (0.05 sec)
root@db 11:32: [test1]> desc sbtest1;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| k | int(10) unsigned | YES | | NULL | |
| c | char(120) | YES | | NULL | |
| pad | char(60) | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
root@db 11:32: [test1]>
root@db 11:33: [test1]> create table sbtest2(id int(10) unsigned primary key,k int(10) unsigned,c char(120),pad char(60));
Query OK, 0 rows affected (0.02 sec)
root@db 11:33: [test1]>
root@db 11:33: [test1]> desc sbtest2;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| k | int(10) unsigned | YES | | NULL | |
| c | char(120) | YES | | NULL | |
| pad | char(60) | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
root@db 11:33: [test1]>
root@db 11:33: [test1]> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| DEPT |
| sbtest1 |
| sbtest2 |
+-----------------+
3 rows in set (0.00 sec)
root@db 11:33: [test1]>
root@db 11:33: [test1]> exit
Bye
[root@source ~]#
[root@source ~]# cd /data/
[root@source data]#
[root@source data]# ll
total 18372
drwxr-xr-x 2 mysql mysql 4096 Mar 21 11:35 backup
drwxr-xr-x 7 mysql mysql 4096 Mar 21 11:19 mysql
-rw-r--r-- 1 root root 6264322 Mar 21 11:36 sbtest1.txt
-rw-r--r-- 1 root root 6264322 Mar 21 11:36 sbtest2.txt
[root@source data]#
[root@source data]# more sbtest1.txt
1 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
2 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
3 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
4 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
5 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
6 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
7 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
8 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
9 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
10 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
11 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
12 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
13 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
14 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
15 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
16 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
17 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
18 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
19 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
20 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
21 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
22 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
23 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
24 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
25 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
26 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
27 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
28 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
29 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
30 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
31 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
32 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
33 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
34 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
35 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
36 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
37 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
38 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
39 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
40 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
41 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
42 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
43 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
44 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
。。。。。。
[root@source data]# more sbtest2.txt
1 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
2 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
3 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
4 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
5 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
6 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
7 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
8 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
9 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
10 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
11 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
12 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
13 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
14 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
15 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
16 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
17 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
18 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
19 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
20 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
21 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
22 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
23 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
24 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
25 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
26 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
27 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
28 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
29 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
30 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
31 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
32 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
33 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
34 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
35 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
36 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
37 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
38 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
39 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
40 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
41 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
42 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
43 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
44 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt
。。。。。。
2.4.2串行导入
下面演示串行导入2张表数据：
mysqlimport -uroot -poracle test1 /data/sbtest1.txt /data/sbtest2.txt
show full processlist;
窗口1：
[root@source data]# mysqlimport -uroot -poracle test1 /data/sbtest1.txt /data/sbtest2.txt
test1.sbtest1: Records: 100011 Deleted: 0 Skipped: 0 Warnings: 0
test1.sbtest2: Records: 100011 Deleted: 0 Skipped: 0 Warnings: 0
[root@source data]#
窗口2：
如果在上述命令的运行过程中，查看MySQL的数据库线程列表，应该可以看到类似如下内容：
root@db 11:38: [(none)]> show full processlist;
+----+------+-----------+-------+---------+------+-----------+----------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-------+---------+------+-----------+----------------------------------------------------------------------------+
| 9 | root | localhost | NULL | Query | 0 | starting | show full processlist |
| 10 | root | localhost | test1 | Query | 1 | executing | LOAD DATA INFILE '/data/sbtest1.txt' INTO TABLE `sbtest1` IGNORE 0 LINES |
+----+------+-----------+-------+---------+------+-----------+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
root@db 11:38: [(none)]>
root@db 11:38: [(none)]> show full processlist;
+----+------+-----------+-------+---------+------+-----------+----------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-------+---------+------+-----------+----------------------------------------------------------------------------+
| 9 | root | localhost | NULL | Query | 0 | starting | show full processlist |
| 10 | root | localhost | test1 | Query | 1 | executing | LOAD DATA INFILE '/data/sbtest2.txt' INTO TABLE `sbtest2` IGNORE 0 LINES |
+----+------+-----------+-------+---------+------+-----------+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
root@db 11:38: [(none)]>
可以看到，mysqlimport每次只有一个线程在导入数据，不加–use-threads=2参数，是串行地导人数据。
2.4.3并发导入
下面通过mysqlimport并发地导入2张表：
mysqlimport -uroot -poracle --use-threads=2 test1 /data/sbtest1.txt /data/sbtest2.txt
show full processlist;
窗口1：
[root@source data]# mysqlimport -uroot -poracle --use-threads=2 test1 /data/sbtest1.txt /data/sbtest2.txt
test1.sbtest1: Records: 100011 Deleted: 0 Skipped: 0 Warnings: 0
test1.sbtest2: Records: 100011 Deleted: 0 Skipped: 0 Warnings: 0
窗口2：
如果在上述命令的运行过程中，查看MySQL的数据库线程列表，应该可以看到类似如下内容：
root@db 11:45: [(none)]> show full processlist;
+----+------+-----------+-------+---------+------+-----------+----------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-------+---------+------+-----------+----------------------------------------------------------------------------+
| 9 | root | localhost | NULL | Query | 0 | starting | show full processlist |
| 11 | root | localhost | test1 | Query | 1 | executing | LOAD DATA INFILE '/data/sbtest1.txt' INTO TABLE `sbtest1` IGNORE 0 LINES |
| 12 | root | localhost | test1 | Query | 1 | executing | LOAD DATA INFILE '/data/sbtest2.txt' INTO TABLE `sbtest2` IGNORE 0 LINES |
+----+------+-----------+-------+---------+------+-----------+----------------------------------------------------------------------------+
3 rows in set (0.00 sec)
root@db 11:45: [(none)]>
可以看到，加–use-threads=2参数后，mysqlimport实际上是同时执行了两句LOAD DTA INFILE并发地导人数据。
最后修改时间：2020-04-03 12:38:23
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
