使用SQL语句完成下列导出、导入操作：
- 将/etc/passwd文件导入userdb库user表并给每条记录加编号
- 将userdb库user表中UID小于100的前10条记录导出，存为/myload/user2.txt文件
步骤一：将/etc/passwd文件导入MySQL数据库
导入后的表结构取决于/etc/passwd配置文件。若一时记不住各字段的含义，也可以查看passwd配置文件的man手册页，找到格式描述相关的说明，比如：
[root@zhangyx ~]# man 1 passwd
PASSWD(1) User utilities PASSWD(1)
NAME
passwd - update user's authentication tokens
SYNOPSIS
passwd [-k] [-l] [-u [-f]] [-d] [-e] [-n mindays]
[-x maxdays] [-w warndays] [-i inactivedays] [-S]
[--stdin] [username]
DESCRIPTION
The passwd utility is used to update user's authen‐
tication token(s).
This task is achieved through calls to the Linux-
PAM and Libuser API. Essentially, it initializes
itself as a "passwd" service with Linux-PAM and
utilizes configured password modules to authenti‐
cate and then update a user's password.
A simple entry in the global Linux-PAM configura‐
tion file for this service would be:
#
# passwd service entry that does strength checking
of
# a proposed password before updating it.
#
passwd password requisite pam_cracklib.so retry=3
passwd password required pam_unix.so use_authtok
#
Note, other module types are not required for this
application to function correctly.1）新建userdb库、user表
以数据库用户root登入MySQL服务：
[root@zhangyx ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.27-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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>新建userd库，切换到userd库：
mysql> create database userd;
Query OK, 1 row affected (0.01 sec)
mysql> USE userd;
Database changed
mysql>新建user表，字段设置及相关操作参考如下：
mysql> create table user(
-> username varchar(24) NOT NULL,
-> password varchar(48) DEFAULT 'x',
-> uid int(5) NOT NULL,
-> fullname varchar(48),
-> homedir varchar(64) NOT NULL,
-> shell varchar(24) NOT NULL
-> );
Query OK, 0 rows affected (0.02 sec)确认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 | |
| fullname | varchar(48) | YES | | NULL | |
| homedir | varchar(64) | NO | | NULL | |
| shell | varchar(24) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)2）如果直接导入会报错。在MySQL 5.7.6版本之后，导入文件只能在secure_file_priv指定的文件夹下。
mysql> load data infile '/etc/passwd' into table user fields terminated by ':';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement执行show variables like '%secure%'命令显示文件目录，报错即解决：
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)3）执行导入操作
将/etc/passwd文件复制到/var/lib/mysql-files/目录下
[root@zhangyx ~]# cp /etc/passwd /var/lib/mysql-files //这里复制格式为cp a b （a与b之间要有空格，否则报错）读取/var/lib/mysql-files/passwd文件内容，以“:”为分隔，导入到user表中：
mysql> load data infile '/var/lib/mysql-files/passwd' into table user fields terminated by ':';
Query OK, 23 rows affected, 23 warnings (0.01 sec)
Records: 23 Deleted: 0 Skipped: 0 Warnings: 23注：如果这里报如下错：
ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns则很可能是由于数据文件中的列数跟 MySQL 数据表字段数目没有完全匹配，并且 sql_mode 设为 strict 模式的缘故。要想在这种情况下继续导入数据到 MySQL 表中，则需要设置 MySQL sql_mode 变量。把“strict_trans_tables” 从 sql_mode 中去掉，如下：
mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)设置 MySQL sql_mode，使其不包含 “strict_trans_tables” mode
mysql> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)这样报错问题就解决了
上述操作中省略了行分隔 LINES TERMINATED BY ‘\n’，因为这是默认的情况（每行一条原始记录），除非需要以其他字符分割行，才需要用到这个。
比如，以下操作指定了行分隔为’\n’，将/var/lib/mysql-files/passwd文件的内容导入另一个表user2，最终user2表的内容与user的内容是一样的。
mysql> CREATE TABLE user2 //参照user表结构建立新表
-> SELECT * FROM user WHERE FALSE;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> LOAD DATA INFILE '/var/lib/mysql-files/passwd' //导入时指定行分隔
-> INTO TABLE user2
-> FIELDS TERMINATED BY ':'
-> LINES TERMINATED BY '\n';
Query OK, 23 rows affected, 23 warnings (0.01 sec)
Records: 23 Deleted: 0 Skipped: 0 Warnings: 234）确认导入结果
分别统计user、user2表内的记录个数：
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
| 23 | //user表有23条记录
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from user2;
+----------+
| count(*) |
+----------+
| 23 | //user2表也有23条记录
+----------+
1 row in set (0.00 sec)查看user表的前10条记录，列出用户名、UID、GID、宿主目录、登录Shell：
mysql> select username,uid,homedir,shell from user limit 10;
+----------+-----+----------+-----------------+
| username | uid | homedir | shell |
+----------+-----+----------+-----------------+
| root | 0 | root | /root |
| bin | 1 | bin | /bin |
| daemon | 2 | daemon | /sbin |
| adm | 3 | adm | /var/adm |
| lp | 4 | lp | /var/spool/lpd |
| sync | 5 | sync | /sbin |
| shutdown | 6 | shutdown | /sbin |
| halt | 7 | halt | /sbin |
| mail | 8 | mail | /var/spool/mail |
| operator | 11 | operator | /root |
+----------+-----+----------+-----------------+
10 rows in set (0.01 sec)查看user2表的前10条记录，同样列出用户名、UID、GID、宿主目录、登录Shell：
mysql> select username,uid,homedir,shell from user2 limit 10;
+----------+-----+----------+-----------------+
| username | uid | homedir | shell |
+----------+-----+----------+-----------------+
| root | 0 | root | /root |
| bin | 1 | bin | /bin |
| daemon | 2 | daemon | /sbin |
| adm | 3 | adm | /var/adm |
| lp | 4 | lp | /var/spool/lpd |
| sync | 5 | sync | /sbin |
| shutdown | 6 | shutdown | /sbin |
| halt | 7 | halt | /sbin |
| mail | 8 | mail | /var/spool/mail |
| operator | 11 | operator | /root |
+----------+-----+----------+-----------------+
10 rows in set (0.00 sec)步骤二：为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.08 sec)
Records: 0 Duplicates: 0 Warnings: 02）验证自动编号结果
查看user表的前10条记录，列出序号、用户名、UID、GID、宿主目录：
mysql> select sn username,uid,homedir,shell from user limit 10;
+----------+-----+----------+-----------------+
| username | uid | homedir | shell |
+----------+-----+----------+-----------------+
| 1 | 0 | root | /root |
| 2 | 1 | bin | /bin |
| 3 | 2 | daemon | /sbin |
| 4 | 3 | adm | /var/adm |
| 5 | 4 | lp | /var/spool/lpd |
| 6 | 5 | sync | /sbin |
| 7 | 6 | shutdown | /sbin |
| 8 | 7 | halt | /sbin |
| 9 | 8 | mail | /var/spool/mail |
| 10 | 11 | operator | /root |
+----------+-----+----------+-----------------+步骤三：从MySQL数据库中导出查询结果
以将userdb库user表中UID小于100的前10条记录导出为/myload/user2.txt文件为例。
1）确认存放导出数据的文件夹
[root@zhangyx ~]# ls -ld /var/lib/mysql-files/
drwxr-x--- 2 mysql mysql 4096 Jul 26 15:13 /var/lib/mysql-files/2）修改目录及查看修改结果
[root@zhangyx ~]# mkdir /myloads; chown mysql /myload
[root@zhangyx ~]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/myloads"
[root@zhangyx ~]# systemctl restart mysqld
mysql> show variables like "secure_file_priv";
+------------------+-----------+
| Variable_name | Value |
+------------------+-----------+
| secure_file_priv | /myloads/ |
+------------------+-----------+
1 row in set (0.00 sec)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)3）确认导出结果
返回到Shell命令行，查看/myload/user2.txt文件的行数：
[root@zhangyx ~]# wc -l /myload/user2.txt
24 /myload/user2.txt查看/myload/user2.txt文件的最后10行内容：
[root@zhangyx ~]# 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
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
