检索路径–搜索路径
默认路径:
安装mysql时 系统自动创建 目录 /var/lib/mysql-files
查询检索路径方法:show variables like “secure_file_priv”;
修改检索路径
创建目录 修改配置文件 重启服务
我们把默认路径修改为/myload
[root@mysql50 ~]# mkdir /myload
[root@mysql50 ~]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/myload” //在配置文件中添加
[root@mysql50 ~]# chown mysql /myload
[root@mysql50 ~]# systemctl restart mysqld
数据导入
命令格式:
load date infile “目录/文件”
into table 库名.表名
fields terminated by “分隔符”
lines terminated by “\n” ;
只有root用户能导入权限
字段分隔符要与文件分隔符一致
表字段类型和字段个数要与文件匹配
导入步骤:建库–>建表–>拷贝文件到检索目录下–>导入数据–>查看表记录
1.建库 , 建表
mysql> create database db3;
Query OK, 1 row affected (0.00 sec)
mysql> create table db3.user(
-> name char(50),
-> passwdrd char(1),
-> uid int,
-> gid int,
-> comment varchar(150),
-> hostdir char(50),
-> shell char(50));
Query OK, 0 rows affected (0.64 sec)
mysql> desc db3.user
-> ;
2.拷贝文件到检索目录下
利用system 可以在数据库里执行系统命令
mysql> system cp /etc/passwd /myload/
mysql> system ls /myload
passwd
3.导入数据
mysql> load data infile "/myload/passwd" into table db3.user fields terminated by ":" lines terminated by "\n";
4.查看表数据
mysql> select * from db3.user;
数据导出
有三种方法导出数据
命令格式1:select into outfile “目录/文件”;
命令格式2:select into outfile “目录/文件”
fields terminated by “分隔符”;
命令格式3:select into outfile “目录/文件”
fields terminated by “分隔符”
lines terminated by “\n”;
导出数据有sql查询决定
导出的是表记录,不包括字段名
自动创建存储的文件
存储的文件不能重名,具有唯一性
默认分隔符是"\n".
- 把表前两行导出到/mysql/user1.txt
mysql> select * from db3.user where id<=2 into outfile "/myload/user1.txt";
Query OK, 2 rows affected (0.00 sec)
mysql> system cat /myload/user1.txt
1 root x 0 0 root /root /bin/bash
2 bin x 1 1 bin /bin /sbin/nologin
2.导出前两行命令到/myload/user2.txt 指定分隔符为 :
mysql> select * from db3.user where id <= 2 into outfile "/myload/user2.txt"
-> fields terminated by ":";
Query OK, 2 rows affected (0.00 sec)
mysql> system cat /myload/user2.txt
1:root:x:0:0:root:/root:/bin/bash
2:bin:x:1:1:bin:/bin:/sbin/nologin
3.导出前三行到/myload/user3.txt 指定分隔符为# 指定换行符为@@@
mysql> select * from db3.user where id <= 3 into outfile "/myload/user3.txt"
-> fields terminated by "#"
-> lines terminated by "@@@";
Query OK, 3 rows affected (0.00 sec)
mysql> system cat /myload/user3.txt
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@@@mysql>
4.导出name行uid行gid行到/myload/user4.txt 指定分隔符为 :
mysql> select name,uid,gid from db3.user into outfile "/myload/user4.txt"
-> fields terminated by ":";
Query OK, 21 rows affected (0.00 sec)
mysql> system cat /myload/user4.txt
root:0:0
bin:1:1
daemon:2:2
adm:3:4
lp:4:7
sync:5:0
shutdown:6:0
注意事项:
确认/myload目录有写入权限
确认重启mysql数据库