Mysql-2数据导入导出

Mysql之数据导入导出

• 搜索路径(导入导出前准备)

mysql> show variables like "secure_file_priv"; //查看变量-secure_file_priv
+---------------------+-------------------+
| Variable_name       |     Value         |
+---------------------+---------------------+
|secure_file_priv     | /var/lib/mysql-files/|
+---------------------+-----------------------+
//默认导入导出的文件夹,,,修改导入导出默认文件夹
mkdir /myload
chown mysql /myload  //修改文件夹的权属,mysql进程可以对/myload 读写操作
vim /etc/my.cnf
   [mysqld]
   secure_file_priv="/myload"
 systemctl restart mysqld  //重启服务

• 导入数据
  导入步骤: 默认只有root用户有数据导入权限;建库建表,导入到表
  命令格式:
  mysql> load data infile “目录名/文件名” into table 库名.表名
  fields terminated by “分隔符”
  lines terminated by “\n”;
  示例:

load data infile  "/myload/a.txt" into db.user fields terminated by ":" lines terminated by "\n";

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

• 数据导出
  mysql> selcet命令 into outfile “目录名/文件名”
  fields terminated by “分隔符”
  lines terminated by “\n”;
  示例:

select user,host from db.user into outfile "/myload/b/txt";