该实例将练习MySQL的导入导出操作
创新互联建站服务项目包括扶余网站建设、
扶余网站制作、扶余网页制作以及扶余网络营销策划等。多年来，我们专注于互联网行业，利用自身积累的技术优势、行业经验、深度合作伙伴关系等，向广大中小型企业、政府机构等提供互联网行业的解决方案，
扶余网站推广取得了明显的社会效益与经济效益。目前，我们服务的客户以成都为中心已经辐射到扶余省份的部分城市，未来相信会继续扩大服务区域并继续获得客户的支持与信任！
实例：
将/etc/passwd文件导入userdb库user表并给每条记录加编号
将userdb库user表中UID小于100的前10条记录导出，存为/mydata/user1.txt文件
mysql> create database userdb;
Query OK, 1 row affected (0.00 sec)
mysql> use userdb;
Database changed
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.01 sec)
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)
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
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)
-将/etc/passwd文件复制到/var/lib/mysql-files/目录下，
-读取/var/lib/mysql-files/passwd文件内容，以“:”为分隔，导入到user表中：
[root@host50 ~]#cp /etc/passwd /var/lib/mysql-files/
mysql> LOAD DATA INFILE '/var/lib/mysql-files/passwd'
-> INTO TABLE user
-> FIELDS TERMINATED BY ':';
Query OK, 42 rows affected (0.11 sec)
Records: 42 Deleted: 0 Skipped: 0 Warnings: 0
注：上述操作中省略了行分隔 LINES TERMINATED BY '\n'，因为这是默认的情况（每行一条原始记录），除非需要以其他字符分割行，才需要用到这个。
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
| 42 |
+----------+
1 row in set (0.00 sec)
mysql> select * from user limit 10;
+----------+----------+-----+-----+----------+-----------------+----------------+
| username | password | uid | gid | fullname | homedir | shell |
+----------+----------+-----+-----+----------+-----------------+----------------+
| root | x | 0 | 0 | root | /root | /bin/bash |
| bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
| daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
| adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin |
| lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin |
| sync | x | 5 | 0 | sync | /sbin | /bin/sync |
| shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown |
| halt | x | 7 | 0 | halt | /sbin | /sbin/halt |
| mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin |
| operator | x | 11 | 0 | operator | /root | /sbin/nologin |
+----------+----------+-----+-----+----------+-----------------+----------------+
10 rows in set (0.00 sec)
mysql> alter table user add sn int(4) auto_increment primary key first;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from user limit 10;
+----+----------+----------+-----+-----+----------+-----------------+----------------+
| sn | username | password | uid | gid | fullname | homedir | shell |
+----+----------+----------+-----+-----+----------+-----------------+----------------+
| 1 | root | x | 0 | 0 | root | /root | /bin/bash |
| 2 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
| 3 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
| 4 | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin |
| 5 | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin |
| 6 | sync | x | 5 | 0 | sync | /sbin | /bin/sync |
| 7 | shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown |
| 8 | halt | x | 7 | 0 | halt | /sbin | /sbin/halt |
| 9 | mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin |
| 10 | operator | x | 11 | 0 | operator | /root | /sbin/nologin |
+----+----------+----------+-----+-----+----------+-----------------+----------------+
10 rows in set (0.00 sec)
-以将userdb库user表中UID小于100的前10条记录导出为/myload/user2.txt文件为例
首先，修改配置文件中存放导出导入目录及查看修改结果
[root@host50 ~]# mkdir /myload ; chown mysql /myload
[root@host50 ~]# 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/ |
导出user表中UID小于100的前十条记录
mysql> select * from userdb.user where uid<100
-> into outfile '/myload/user.txt'
-> fields terminated by ":";
Query OK, 26 rows affected (0.00 sec)
确认导出结果
[root@host50 ~]# wc -l /myload/user.txt
26 /myload/user.txt
[root@host50 ~]# tail /myload/user.txt
25:tss:x:59:59:Account used by the trousers package to sandbox the tcsd daemon:/dev/null:/sbin/nologin
29:rpcuser:x:29:29:RPC Service User:/var/lib/nfs:/sbin/nologin
33:gdm:x:42:42::/var/lib/gdm:/sbin/nologin
35:sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
36:avahi:x:70:70:Avahi mDNS/DNS-SD Stack:/var/run/avahi-daemon:/sbin/nologin
37:postfix:x:89:89::/var/spool/postfix:/sbin/nologin
38:ntp:x:38:38::/etc/ntp:/sbin/nologin
39:tcpdump:x:72:72::/:/sbin/nologin
41:apache:x:48:48:Apache:/usr/share/httpd:/sbin/nologin
42:mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/false
另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
网页标题：MySQL数据导入导出（用于个人学习与回顾）-创新互联
分享地址：
http://njwzjz.com/article/ceideg.html
