溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
原文鏈接： https://www.modb.pro/db/23208?xy
mysqlimport是MySQL數(shù)據(jù)庫提供的一個命令行程序，可用于數(shù)據(jù)導(dǎo)入。從本質(zhì)上來說，是LOAD DATA INFILE的命令接口，而且大多數(shù)的選項都和LOAD DATA INFILE語法相同。其語法格式如下：
shell>mysqlimport [options] db_name textfilel [textfile2 …]
和LOAD DATA INFILE不同的是，mysqlimport命令可以用來導(dǎo)入多張表。并且通過–use-threads=參數(shù)并發(fā)地導(dǎo)入不同的文件。這里的并發(fā)是指并發(fā)導(dǎo)入多個文件，而不是指mysqlimport可以并發(fā)地導(dǎo)入一個文件，這是有明顯區(qū)別的。此外，通常來說并發(fā)地對同一張表進行導(dǎo)入，其效果一般都不會比串行的方式好。
參數(shù)說明：
–use-threads=# Load files in parallel. The argument is the number of threads to use for loading data.
cd /usr/local/mysql/bin
./mysqldump -uroot -poracle --tab=/data/backup test
使用mysqldump工具導(dǎo)出test庫下面所有的表。添加–tab參數(shù)表名，導(dǎo)出的每張表的定義輸出到一個文件（xxxTAB.sql），每張表的數(shù)據(jù)輸出到另外一個文件（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]#
[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]#
使用mysql導(dǎo)入定義，使用mysqlimport方法導(dǎo)入數(shù)據(jù)
create database test1;
mysql -uroot -poracle test1 </data/backup/DEPT.sql
mysqlimport -uroot -poracle --local test1 /data/backup/DEPT.txt
mysqlimport參數(shù)說明：
-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]>
在mysql命令行執(zhí)行腳本創(chuàng)建命令，再使用load data local infile … into …加載數(shù)據(jù)
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]>
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 。。。。。。
下面演示串行導(dǎo)入2張表數(shù)據(jù)：
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的數(shù)據(jù)庫線程列表，應(yīng)該可以看到類似如下內(nèi)容：
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每次只有一個線程在導(dǎo)入數(shù)據(jù)，不加–use-threads=2參數(shù)，是串行地導(dǎo)人數(shù)據(jù)。
下面通過mysqlimport并發(fā)地導(dǎo)入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的數(shù)據(jù)庫線程列表，應(yīng)該可以看到類似如下內(nèi)容：
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參數(shù)后，mysqlimport實際上是同時執(zhí)行了兩句LOAD DTA INFILE并發(fā)地導(dǎo)人數(shù)據(jù)。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼
