mysqlimport学习总结

发布时间：2020-08-18 10:35:10 阅读：283 作者：数据和云栏目：MySQL数据库

亿速云mysql数据库，读写分离，安全稳定，弹性扩容，低至0.3元/天！！点击查看>>

原文链接： https://www.modb.pro/db/23208?xy

摘要：mysqlimport是MySQL数据库提供的一个命令行程序，可用于数据导入。

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并发地导人数据。

亿速云「云数据库 MySQL」免部署即开即用，比自行安装部署数据库高出1倍以上的性能，双节点冗余防止单节点故障，数据自动定期备份随时恢复。点击查看>>

向AI问一下细节

mysqlimport学习总结

1.mysqlimport概述

2.演示

2.1导出数据

2.2新建数据库test1，将数据导入到test1库

2.3导入数据

2.3.1导入方法1

2.3.2导入方法2

2.4并行与串行演示

2.4.1环境准备

2.4.2串行导入

2.4.3并发导入

猜你喜欢

最新资讯

相关推荐

开发者交流群：

相关标签