1 数据导入/导出
1.1 问题
使用SQL语句完成下列导出、导入操作：
将/etc/passwd文件导入userdb库user表并给每条记录加编号
将userdb库user表中UID小于100的前10条记录导出，存为/myload/user2.txt文件
- 1
- 2
1.2 步骤
实现此案例需要按照如下步骤进行。
步骤一：将/etc/passwd文件导入MySQL数据库
导入后的表结构取决于/etc/passwd配置文件。若一时记不住各字段的含义，也可以查看passwd配置文件的man手册页，找到格式描述相关的说明，比如：
[root@dbsvr1 ~]# man 5 passwd
.. ..
Each line of the file describes a single user, and contains seven colon-sep‐
arated fields:
name:password:UID:GID:GECOS:directory:shell //各字段的顺序、大致用途
The field are as follows: //以下详细解释各字段的作用
name This is the user's login name. It should not contain capital
letters.
password This is either the encrypted user password, an asterisk (*), or
the letter 'x'. (See pwconv(8) for an explanation of 'x'.)
UID The privileged root login account (superuser) has the user ID 0.
GID This is the numeric primary group ID for this user. (Additional
groups for the user are defined in the system group file; see
group(5)).
GECOS stands for "General Electric Comprehensive Operating Sys‐
tem", which was renamed to GCOS when GE's large systems division
was sold to Honeywell. Dennis Ritchie has reported: "Sometimes
we sent printer output or batch jobs to the GCOS machine. The
gcos field in the password file was a place to stash the infor‐
mation for the $IDENTcard. Not elegant."
directory This is the user's home directory: the initial directory where
the user is placed after logging in. The value in this field is
used to set the HOME environment variable.
shell This is the program to run at login (if empty, use /bin/sh). If
set to a nonexistent executable, the user will be unable to
login through login(1). The value in this field is used to set
the SHELL environment variable.
.. ..
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
1）新建userdb库、user表
以数据库用户root登入MySQL服务：
[root@dbsvr1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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.
mysql>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
新建userdb库，切换到userdb库：
mysql> CREATE DATABASE userdb;
Query OK, 1 row affected (0.00 sec)
mysql> USE userdb;
Database changed
- 1
- 2
- 3
- 4
新建user表，字段设置及相关操作参考如下：
mysql> CREATE TABLE user(
-> username varchar(24) NOT NULL,
-> password varchar(48) DEFAULT 'x',
-> uid int(5) NOT NULL,
-> gid int(5) NOT NULL,
-> fullname varchar(48),
-> homedir varchar(64) NOT NULL,
-> shell varchar(24) NOT NULL
-> );
Query OK, 0 rows affected (0.70 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
确认user表的结构：
mysql> DESC user;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(24) | NO | | NULL | |
| password | varchar(48) | YES | | x | |
| uid | int(5) | NO | | NULL | |
| gid | int(5) | NO | | NULL | |
| fullname | varchar(48) | YES | | NULL | |
| homedir | varchar(64) | NO | | NULL | |
| shell | varchar(24) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
2）如果直接导入会报错。在MySQL 5.7.6版本之后，导入文件只能在secure_file_priv指定的文件夹下。执行show variables like '%secure%'命令显示文件目录：
mysql> LOAD DATA INFILE '/etc/passwd' INTO TABLE userlist FIELDS TERMINATED BY ':';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name | Value |
+--------------------------+-----------------------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
3）执行导入操作
将/etc/passwd文件复制到/var/lib/mysql-files/目录下，
读取/var/lib/mysql-files/passwd文件内容，以“:”为分隔，导入到user表中：
[root@dbsvr1 ~]#cp /etc/passwd /var/lib/mysql-files/
mysql> LOAD DATA INFILE '/var/lib/mysql-files/passwd'
-> INTO TABLE userlist
-> FIELDS TERMINATED BY ':';
Query OK, 39 rows affected (0.11 sec)
Records: 39 Deleted: 0 Skipped: 0 Warnings: 0
- 1
- 2
- 3
- 4
- 5
- 6
上述操作中省略了行分隔 LINES TERMINATED BY ‘\n’，因为这是默认的情况（每行一条原始记录），除非需要以其他字符分割行，才需要用到这个。比如，以下操作指定了行分隔为’\n’，将/var/lib/mysql-files/passwd文件的内容导入另一个表user2，最终user2表的内容与user的内容是一样的：
代码
4）确认导入结果
分别统计user、user2表内的记录个数：
mysql> SELECT COUNT(*) FROM user;
+----------+
| COUNT(*) |
+----------+
| 39 | //user表有39条记录
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM user2;
+----------+
| COUNT(*) |
+----------+
| 39 | //user2表也有39条记录
+----------+
1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
查看user表的前10条记录，列出用户名、UID、GID、宿主目录、登录Shell：
mysql> SELECT username,uid,gid,homedir,shell
-> FROM user LIMIT 10;
+----------+-----+-----+-----------------+----------------+
| username | uid | gid | homedir | shell |
+----------+-----+-----+-----------------+----------------+
| root | 0 | 0 | /root | /bin/bash |
| bin | 1 | 1 | /bin | /sbin/nologin |
| daemon | 2 | 2 | /sbin | /sbin/nologin |
| adm | 3 | 4 | /var/adm | /sbin/nologin |
| lp | 4 | 7 | /var/spool/lpd | /sbin/nologin |
| sync | 5 | 0 | /sbin | /bin/sync |
| shutdown | 6 | 0 | /sbin | /sbin/shutdown |
| halt | 7 | 0 | /sbin | /sbin/halt |
| mail | 8 | 12 | /var/spool/mail | /sbin/nologin |
| operator | 11 | 0 | /root | /sbin/nologin |
+----------+-----+-----+-----------------+----------------+
10 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
查看user2表的前10条记录，同样列出用户名、UID、GID、宿主目录、登录Shell：
mysql> SELECT username,uid,gid,homedir,shell
-> FROM user2 LIMIT 10;
+----------+-----+-----+-----------------+----------------+
| username | uid | gid | homedir | shell |
+----------+-----+-----+-----------------+----------------+
| root | 0 | 0 | /root | /bin/bash |
| bin | 1 | 1 | /bin | /sbin/nologin |
| daemon | 2 | 2 | /sbin | /sbin/nologin |
| adm | 3 | 4 | /var/adm | /sbin/nologin |
| lp | 4 | 7 | /var/spool/lpd | /sbin/nologin |
| sync | 5 | 0 | /sbin | /bin/sync |
| shutdown | 6 | 0 | /sbin | /sbin/shutdown |
| halt | 7 | 0 | /sbin | /sbin/halt |
| mail | 8 | 12 | /var/spool/mail | /sbin/nologin |
| operator | 11 | 0 | /root | /sbin/nologin |
+----------+-----+-----+-----------------+----------------+
10 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
步骤二：为user表中的每条记录添加自动编号
这个只要修改user表结构，添加一个自增字段即可。
比如，添加一个名为sn的序号列，作为user表的第一个字段：
1）添加自增主键字段sn
mysql> ALTER TABLE user
-> ADD sn int(4) AUTO_INCREMENT PRIMARY KEY FIRST;
Query OK, 0 rows affected (0.62 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 1
- 2
- 3
- 4
2）验证自动编号结果
查看user表的前10条记录，列出序号、用户名、UID、GID、宿主目录：
mysql> SELECT sn,username,uid,gid,homedir
-> FROM user LIMIT 10;
+----+----------+-----+-----+-----------------+
| sn | username | uid | gid | homedir |
+----+----------+-----+-----+-----------------+
| 1 | root | 0 | 0 | /root |
| 2 | bin | 1 | 1 | /bin |
| 3 | daemon | 2 | 2 | /sbin |
| 4 | adm | 3 | 4 | /var/adm |
| 5 | lp | 4 | 7 | /var/spool/lpd |
| 6 | sync | 5 | 0 | /sbin |
| 7 | shutdown | 6 | 0 | /sbin |
| 8 | halt | 7 | 0 | /sbin |
| 9 | mail | 8 | 12 | /var/spool/mail |
| 10 | operator | 11 | 0 | /root |
+----+----------+-----+-----+-----------------+
10 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
步骤三：从MySQL数据库中导出查询结果
以将userdb库user表中UID小于100的前10条记录导出为/myload/user2.txt文件为例。
1）确认存放导出数据的文件夹
[root@dbsvr1 ~]# ls -ld /var/lib/mysql-files/
drwxr-x---. 2 mysql mysql 19 4月 7 11:15 /var/lib/mysql-files/
- 1
- 2
2）修改目录及查看修改结果
[root@dbsvr1 ~]# mkdir /myload ; chown mysql /myload
[root@dbsvr1 ~]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/myload"
[root@dbsvr1 ~]# systemctl restart mysqld
mysql> show variables like "secure_file_priv";
+------------------+----------+
| Variable_name | Value |
+------------------+----------+
| secure_file_priv | /myload/ |
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
2）导出user表中UID小于100的前10条记录
如果以默认的’\n’ 为行分隔，导出操作同样可不指定LINES TERMINATED BY：
mysql> SELECT * FROM userdb.user WHERE uid<100
-> INTO OUTFILE '/myload/user2.txt'
-> FIELDS TERMINATED BY ':';
Query OK, 24 rows affected (0.00 sec)
- 1
- 2
- 3
- 4
3）确认导出结果
返回到Shell命令行，查看/myload/user2.txt文件的行数：
[root@dbsvr1 ~]# wc -l /myload/user2.txt
24 /myload/user2.txt
- 1
- 2
查看/myload/user2.txt文件的最后10行内容：
[root@dbsvr1 ~]# tail /myload/user2.txt
19:avahi:x:70:70:Avahi mDNS/DNS-SD Stack:/var/run/avahi-daemon:/sbin/nologin
24:rpc:x:32:32:Rpcbind Daemon:/var/lib/rpcbind:/sbin/nologin
25:rpcuser:x:29:29:RPC Service User:/var/lib/nfs:/sbin/nologin
28:radvd:x:75:75:radvd user:/:/sbin/nologin
29:ntp:x:38:38::/etc/ntp:/sbin/nologin
33:gdm:x:42:42::/var/lib/gdm:/sbin/nologin
35:postfix:x:89:89::/var/spool/postfix:/sbin/nologin
36:sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
37:tcpdump:x:72:72::/:/sbin/nologin
39:mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/false
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
</div>
<link href="https://csdnimg.cn/release/phoenix/mdeditor/markdown_views-b6c3c6d139.css" rel="stylesheet">
<div class="more-toolbox">
<div class="left-toolbox">
<ul class="toolbox-list">
<li class="tool-item tool-active is-like "><a href="javascript:;"><svg class="icon" aria-hidden="true">
<use xlink:href="#csdnc-thumbsup"></use>
</svg><span class="name">点赞</span>
<span class="count"></span>
</a></li>
<li class="tool-item tool-active is-collection "><a href="javascript:;" data-report-click="{"mod":"popu_824"}"><svg class="icon" aria-hidden="true">
<use xlink:href="#icon-csdnc-Collection-G"></use>
</svg><span class="name">收藏</span></a></li>
<li class="tool-item tool-active is-share"><a href="javascript:;" data-report-click="{"mod":"1582594662_002"}"><svg class="icon" aria-hidden="true">
<use xlink:href="#icon-csdnc-fenxiang"></use>
</svg>分享</a></li>
<!--打赏开始-->
<!--打赏结束-->
<li class="tool-item tool-more">
<a>
<svg t="1575545411852" class="icon" viewBox="0 0 1024 1024" version="1.1" xmlns="http://www.w3.org/2000/svg" p-id="5717" xmlns:xlink="http://www.w3.org/1999/xlink" width="200" height="200"><defs><style type="text/css"></style></defs><path d="M179.176 499.222m-113.245 0a113.245 113.245 0 1 0 226.49 0 113.245 113.245 0 1 0-226.49 0Z" p-id="5718"></path><path d="M509.684 499.222m-113.245 0a113.245 113.245 0 1 0 226.49 0 113.245 113.245 0 1 0-226.49 0Z" p-id="5719"></path><path d="M846.175 499.222m-113.245 0a113.245 113.245 0 1 0 226.49 0 113.245 113.245 0 1 0-226.49 0Z" p-id="5720"></path></svg>
</a>
<ul class="more-box">
<li class="item"><a class="article-report">文章举报</a></li>
</ul>
</li>
</ul>
</div>
</div>
<div class="person-messagebox">
<div class="left-message"><a href="https://blog.csdn.net/xie_qi_chao">
<img src="https://profile.csdnimg.cn/B/F/6/3_xie_qi_chao" class="avatar_pic" username="xie_qi_chao">
<img src="https://g.csdnimg.cn/static/user-reg-year/1x/2.png" class="user-years">
</a></div>
<div class="middle-message">
<div class="title"><span class="tit"><a href="https://blog.csdn.net/xie_qi_chao" data-report-click="{"mod":"popu_379"}" target="_blank">解启超</a></span>
</div>
<div class="text"><span>发布了317 篇原创文章</span> · <span>获赞 48</span> · <span>访问量 3万+</span></div>
</div>
<div class="right-message">
<a href="https://im.csdn.net/im/main.html?userName=xie_qi_chao" target="_blank" class="btn btn-sm btn-red-hollow bt-button personal-letter">私信
</a>
<a class="btn btn-sm attented bt-button personal-watch" data-report-click="{"mod":"popu_379"}">已关注</a>
</div>
</div>
</div>
</article>
420
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
