mysql案例导入_MySQL数据导入导出（用于个人学习与回顾）

最新推荐文章于 2024-02-04 11:26:47 发布

weixin_39963255

最新推荐文章于 2024-02-04 11:26:47 发布

阅读量139

点赞数

文章标签： mysql案例导入

本文链接：https://blog.csdn.net/weixin_39963255/article/details/113259170

版权

该实例将练习MySQL的导入导出操作

实例：将/etc/passwd文件导入userdb库user表并给每条记录加编号

将userdb库user表中UID小于100的前10条记录导出，存为/mydata/user1.txt文件

新建userdb库，切换到userdb库，并且设置如下字段；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;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

| password | varchar(48) | YES | | x | |

| uid | int(5) | NO | | NULL | |

| gid | int(5) | NO | | NULL | |

+----------+-------------+------+-----+---------+-------+

7 rows in set (0.01 sec)

注：在MySQL 5.7.6版本之后，导入文件只能在secure_file_priv指定的文件夹下。如果直接导入会报错。执行show variables like '%secure%'命令显示文件目录：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;

+----------+----------+-----+-----+----------+-----------------+----------------+

+----------+----------+-----+-----+----------+-----------------+----------------+

| 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)为user表中的每条记录添加自动编号，并验证自动编写结果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;

+----+----------+----------+-----+-----+----------+-----------------+----------------+

+----+----------+----------+-----+-----+----------+-----------------+----------------+

| 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)从MySQL数据库中导出查询结果

-以将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

weixin_39963255

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql案例导入_MySQL数据导入导出（用于个人学习与回顾）

该实例将练习MySQL的导入导出操作实例：将/etc/passwd文件导入userdb库user表并给每条记录加编号将userdb库user表中UID小于100的前10条记录导出，存为/mydata/user1.txt文件新建userdb库，切换到userdb库，并且设置如下字段；mysql>createdatabaseuserdb;QueryOK,1rowaffected(...
复制链接

扫一扫