mysqlimport学习总结
原文链接： 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 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 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并发地导人数据。
当前标题：mysqlimport学习总结
文章转载：http://myzitong.com/article/gdgdje.html