MySQL的数据导入导出

数据导入导出默认检索的目录

mysql> show variables like "secure_file_priv";
# ls -ld /var/lib/mysql-files/

修改检索路径

# mdkir /myload
# chown mysql /myload
# vim /etc/my.conf
[mysqld]
secure_file_priv="/myload"
# systemctl restart myload
mysql> show variables like "secure_file_priv";

创建目录并修改所有者 修改主配置文件 重启服务

数据导入：

mysql> load date infile "目录/文件名“ into table 库名.表名 fields terminated by "分隔符" lines terminated by "\n";

对导入的数据进行列的分割，与行的分割
如：

mysql> system cp /etc/passwd /myload
mysql> system ls /myload
mysql> create db1;
mysql> creste table db1.user(name char(50),password char(1),uid int,gid int,comment char(150),homedir char(150),shell char(100));
mysql> load date infile "/myload/passwd" into table db1.user fields terminated by ":" lines terminated by "\n";
mysql> alter table user add id int primary key auto_increment first;
mysql> select * from user where id=1;

注意
字段分隔符要与文件一致 表字段类型和字段个数要与文件匹配 导入数据时指定文件的绝对路径

数据导出：

mysql> select name,uid,homedir from  user where id<=3 into outfile "/myload/user.txt" fields terminated by "###" lines terminated by "|||";

注意:
导出数据行数由sql查询决定 导出的是表记录,不包括字段名 自动创建存储数据的文件
存储数据文件,具有唯一性