加入收藏 | 设为首页 | 会员中心 | 我要投稿 核心网 （https://www.hxwgxz.com/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 编程 > 正文

mysqlimport学习归总

发布时间：2022-03-21 12:25:13 所属栏目：编程 来源：互联网
导读：摘要：mysqlimport是MySQL数据库提供的一个命令行程序，可用于数据导入。 1.mysqlimport概述 mysqlimport是MySQL数据库提供的一个命令行程序，可用于数据导入。从本质上来说，是LOAD DATA INFILE的命令接口，而且大多数的选项都和LOAD DATA INFILE语法相同。
       摘要：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 */;
 
[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
 
+-----------------+
| 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
 
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
 
2.4.2串行导入
下面演示串行导入2张表数据：
mysqlimport -uroot -poracle test1 /data/sbtest1.txt /data/sbtest2.txt
show full processlist;

（编辑：核心网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
      站长推荐
      热点阅读

        【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

        建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

        Copygight © 2008-2022 https://www.hxwgxz.com/ All Rights Reserved. 核心网