爱站程序员基地使用SQL语句完成下列导出、导入操作：
- 将/etc/passwd文件导入userdb库user表并给每条记录加编号
- 将userdb库user表中UID小于100的前10条记录导出，存为/myload/user2.txt文件
步骤一：将/etc/passwd文件导入MySQL数据库
导入后的表结构取决于/etc/passwd配置文件。若一时记不住各字段的含义，也可以查看passwd配置文件的man手册页，找到格式描述相关的说明，比如：
[root@zhangyx ~]# man 1 passwdPASSWD(1) User utilities PASSWD(1)NAMEpasswd - update user\'s authentication tokensSYNOPSISpasswd [-k] [-l] [-u [-f]] [-d] [-e] [-n mindays][-x maxdays] [-w warndays] [-i inactivedays] [-S][--stdin] [username]DESCRIPTIONThe 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 initializesitself as a \"passwd\" service with Linux-PAM andutilizes 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 checkingof# a proposed password before updating it.#passwd password requisite pam_cracklib.so retry=3passwd password required pam_unix.so use_authtok#Note, other module types are not required for thisapplication to function correctly.
1）新建userdb库、user表
以数据库用户root登入MySQL服务：
[root@zhangyx ~]# mysql -u root -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \\g.Your MySQL connection id is 6Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 changedmysql>
新建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: 0mysql> 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: 23
4）确认导入结果
分别统计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: 0
2）验证自动编号结果
查看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 mysqldmysql> 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.txt24 /myload/user2.txt
查看/myload/user2.txt文件的最后10行内容：
[root@zhangyx ~]# tail /myload/user2.txt19:avahi:x:70:70:Avahi mDNS/DNS-SD Stack:/var/run/avahi-daemon:/sbin/nologin24:rpc:x:32:32:Rpcbind Daemon:/var/lib/rpcbind:/sbin/nologin25:rpcuser:x:29:29:RPC Service User:/var/lib/nfs:/sbin/nologin28:radvd:x:75:75:radvd user:/:/sbin/nologin29:ntp:x:38:38::/etc/ntp:/sbin/nologin33:gdm:x:42:42::/var/lib/gdm:/sbin/nologin35:postfix:x:89:89::/var/spool/postfix:/sbin/nologin36:sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin37:tcpdump:x:72:72::/:/sbin/nologin39:mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/false
