MySQL LOAD DATA INFILE需要哪些文件和目录权限? [英] What file and directory permissions are required for MySQL LOAD DATA INFILE?

问题描述

我有一个脚本正在尝试使用LOAD DATA INFILE将一些数据加载到MySQL中.出于某种原因，如果文件位于/tmp目录中，则它起作用，但是如果文件位于具有相同权限的另一个目录中，则它不起作用.我找不到任何方法让MySQL从/tmp目录或数据库目录之外导入数据，但在手册中找不到任何能解释为什么会出现这种情况的信息.

I have a script that is trying to load some data into MySQL with LOAD DATA INFILE. For some reason, it works if the file is in the /tmp directory, but not if the file is in another directory with identical permissions. I can't find any way to get MySQL to import data from outside the /tmp directory, or the database directory, but I can't find anything in the manual that explains why this would be the case.

情况:

$ ls -l /
...
drwxrwxrwt  21 root root  4096 2010-10-19 20:02 tmp
drwxrwxrwt   2 root root  4096 2010-10-19 20:14 tmp2

$ ls -l /tmp/data.csv 
-rwxr-xr-x 1 timm timm 415431 2010-10-19 20:02 /tmp/data.csv

$ ls -l /tmp2/data.csv 
-rwxr-xr-x 1 timm timm 415431 2010-10-19 20:14 /tmp2/data.csv

AFAICT在重要方面完全相同.但是，如果在MySQL命令行中我这样做:

AFAICT these are identical in the important respects. However, if at the MySQL command line I do:

> LOAD DATA INFILE '/tmp2/data.csv' IGNORE INTO TABLE ports
      FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ';
ERROR 29 (HY000): File '/tmp2/data.csv' not found (Errcode: 13)

> LOAD DATA INFILE '/tmp/data.csv' IGNORE INTO TABLE ports 
      FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ';
Query OK, 1 row affected, 1 warning (0.04 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

我从论坛发帖中收集到errno 13表示权限问题.看来/tmp被MySQL专门对待，但是为什么呢?我能找到的最接近的是手册中的一行:

I gather from forum postings that errno 13 indicates a permission problem. It seems that /tmp is treated specially by MySQL, but why? The closest I can come is a line in the manual saying:

出于安全原因，在读取服务器上的文本文件时，这些文件必须位于数据库目录中，或者所有人都可以读取.

For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all.

/tmp不在数据库目录中，但是也许像对待它那样对待它.那么我应该如何设置才能读取/tmp以外的文件?

/tmp isn't in the database directory, but maybe it gets treated as if it is. So how should I set things up in order for it to read files outside of /tmp?

推荐答案

mysqlimport --local <database> <infile>

OR

LOAD DATA LOCAL INFILE... should fix the issue.

这篇关于MySQL LOAD DATA INFILE需要哪些文件和目录权限?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！