MYSQL导出到文件时出现“拒绝访问” - 但我的用户具有“全部”访问权限..并且文件夹的CHMOD为777。

Question

任何想法？

SELECT * INTO OUTFILE '/home/myacnt/docs/mysqlCSVtest.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '*'
LINES TERMINATED BY '\n'
FROM tbl_property 
WHERE managerGroupID = {$managerGroupID}

错误：

Access denied for user 'asdfsdf'@'localhost' (using password: YES)

- Shackrock

---

“ALL PRIVILEGES” 并不包括所有权限。与此案例相关，它不包括 FILE 权限，而该权限在使用 “SELECT INTO OUTFILE” 语句时是必需的。我曾经遇到过英文和 MySQL 之间的不匹配问题，导致了很多麻烦。http://dba.stackexchange.com/a/96894/53784 - WAF

如果你遇到了“MySQL服务器正在使用--secure-file-priv选项运行，因此无法执行此语句”的问题，请查看这里：https://dev59.com/k1wY5IYBdhLWcg3wVGiB - sneaky

Answer 1

尝试执行此SQL命令：

> grant all privileges 
  on YOUR_DATABASE.* 
  to 'asdfsdf'@'localhost' 
  identified by 'your_password';
> flush privileges; 

看起来你遇到了连接数据库的问题，而不是写入你提到的文件夹的问题。

另外，请确保已将FILE授权给用户'asdfsdf'@'localhost'。

> GRANT FILE ON *.* TO 'asdfsdf'@'localhost';

- Pablo Santa Cruz

---

@Shackrock：请查看更新后的答案。确保已将“FILE”授权给用户。 - Pablo Santa Cruz

这里有两个问题。1）GRANT ALL不能授予FILE权限。2）GRANT FILE只能使用*.*（即全局）。这都是MySQL中的错误。 - fijiaaron

如果您不是数据库管理员，请在进行此操作之前认真考虑。我的管理员发现我更改了权限后并不高兴（“不要啊！”）。 :-) - Ellen Spertus

似乎它在写入文件系统中除了 /var/lib/mysql 以外的位置时也存在问题，这与文档描述相矛盾。即使目标文件夹归 mysql:mysql 所有并且每个人都具有 rwx 权限和/或使用 sudo 调用...（来自 Ubuntu） - quickthyme

在使用GRANT命令授权时，您不需要执行FLUSH PRIVILEGES。只有在使用SQL修改权限表时，才需要执行该命令。 - Barmar

Answer 2

老实说，我没有费心去处理授予权限的事情，但这甚至在没有特权的情况下也可以工作：

echo "select * from employee" | mysql --host=HOST --port=PORT --user=UserName --password=Password DATABASE.SCHEMA > output.txt

- user1028904

---

这是因为你只是进行了普通的SELECT查询，而没有使用SELECT INTO OUTFILE（在这种情况下生成一个.csv文件）。 - aqn

对于较大的表格，这种做法不太好，因为客户端会在将任何内容输出到文件之前缓冲整个结果集。 - Chris Seline

MySQL将使用这种方法以制表符分隔文件，非常完美。在尝试了十几个文件权限、MySQL设置和不同的授权（涵盖了5或6个不同的帮助页面）后，这个方法终于奏效了。为了避免头痛，请使用这种方法。 - Paul Kenjora

在使用该方法之前，请不要忘记切换到适当的代码页，例如“chcp 1252”或“chcp 65001”，如果导出的数据中有特殊字符。 - trapper_hag

到目前为止，这是最实用的答案，其他答案非常依赖于一些人无法控制的特权。 - Ahmad Khundaqji

Answer 3

正如@fijaaron所说：

1. GRANT ALL并不意味着GRANT FILE
2. GRANT FILE仅适用于*.*

所以执行：

GRANT FILE ON *.* TO user;

- e18r

Answer 4

由于cP/WHM在PHPMyAdmin中取消了以root身份修改用户权限的能力，因此您需要使用命令行来执行以下操作：

mysql>  GRANT FILE ON *.* TO 'user'@'localhost';

第二步是允许用户将文件转储到特定文件夹中。有几种方法可以实现这一点，但最终我选择在以下位置放置一个文件夹：

/home/user/tmp/db

并且。

chown mysql:mysql /home/user/tmp/db

这允许mysql用户写入文件。如前面的帖子所述，您也可以使用MySQL临时文件夹，我不认为这真的有什么关系，但您绝对不想将它设置为0777权限（全世界可写），除非您希望全世界都能看到您的数据。如果您想反复进行该过程，则可能会出现问题，因为INTO OUTFILE将无法工作，如果该文件已经存在。如果您的文件归不同的用户所有，则尝试unlink($file)将无法起作用。如果您像我一样（对0777感到偏执），则可以使用以下命令设置目标目录：

chmod($dir,0777)

在执行SQL命令之前，请先进行以下操作：

chmod($dir,0755)

紧接着，再使用unlink(file)删除文件。这样可以让所有操作都在您的Web用户下运行，并且无需调用MySQL用户。

- Grindlay

Answer 5

对于未来的读者，如果他们想使用Bash批量导出，可以采用以下简单方法：

akshay@ideapad:/tmp$ mysql -u someuser -p test -e "select * from offices"
Enter password: 
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
| officeCode | city          | phone            | addressLine1             | addressLine2 | state      | country   | postalCode | territory |
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
| 1          | San Francisco | [phone]  | 100 Market Street        | Suite 300    | CA         | USA       | 94080      | NA        |
| 2          | Boston        | [phone]  | 1550 Court Place         | Suite 102    | MA         | USA       | 02107      | NA        |
| 3          | NYC           | [phone]  | 523 East 53rd Street     | apt. 5A      | NY         | USA       | 10022      | NA        |
| 4          | Paris         | [phone]  | 43 Rue Jouffroy D'abbans | NULL         | NULL       | France    | 75017      | EMEA      |
| 5          | Tokyo         | [phone]  | 4-1 Kioicho              | NULL         | Chiyoda-Ku | Japan     | 102-8578   | Japan     |
| 6          | Sydney        | [phone]  | 5-11 Wentworth Avenue    | Floor #2     | NULL       | Australia | NSW 2010   | APAC      |
| 7          | London        | [phone] | 25 Old Broad Street      | Level 7      | NULL       | UK        | EC2N 1HN   | EMEA      |
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+

如果您是通过非root用户进行导出，则需要设置如下权限。

root@ideapad:/tmp# mysql -u root -p
MariaDB[(none)]> UPDATE mysql.user SET File_priv = 'Y' WHERE user='someuser' AND host='localhost';

重启或重新加载mysqld

akshay@ideapad:/tmp$ sudo su
root@ideapad:/tmp#  systemctl restart mariadb

代码示例

akshay@ideapad:/tmp$ cat test.sh 
#!/usr/bin/env bash

user="someuser"
password="password"
database="test"

mysql -u"$user" -p"$password" "$database" <<EOF
SELECT * 
INTO OUTFILE '/tmp/csvs/offices.csv' 
FIELDS TERMINATED BY '|' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
FROM offices;
EOF

执行

akshay@ideapad:/tmp$ mkdir -p /tmp/csvs
akshay@ideapad:/tmp$ chmod +x test.sh
akshay@ideapad:/tmp$ ./test.sh 
akshay@ideapad:/tmp$ cat /tmp/csvs/offices.csv 
"1"|"San Francisco"|"[phone]"|"100 Market Street"|"Suite 300"|"CA"|"USA"|"94080"|"NA"
"2"|"Boston"|"[phone]"|"1550 Court Place"|"Suite 102"|"MA"|"USA"|"02107"|"NA"
"3"|"NYC"|"[phone]"|"523 East 53rd Street"|"apt. 5A"|"NY"|"USA"|"10022"|"NA"
"4"|"Paris"|"[phone]"|"43 Rue Jouffroy D'abbans"|\N|\N|"France"|"75017"|"EMEA"
"5"|"Tokyo"|"[phone]"|"4-1 Kioicho"|\N|"Chiyoda-Ku"|"Japan"|"102-8578"|"Japan"
"6"|"Sydney"|"[phone]"|"5-11 Wentworth Avenue"|"Floor #2"|\N|"Australia"|"NSW 2010"|"APAC"
"7"|"London"|"[phone]"|"25 Old Broad Street"|"Level 7"|\N|"UK"|"EC2N 1HN"|"EMEA"

- Akshay Hegde

Answer 6

我尝试了所有的解决方案，但仍然不够。经过更深入的挖掘，我最终发现还需要设置'file_priv'标志，并重新启动mysql。

总之：

授予权限：

> GRANT ALL PRIVILEGES
  ON my_database.* 
  to 'my_user'@'localhost';

> GRANT FILE ON *.* TO my_user;

> FLUSH PRIVILEGES; 

设置标志：

> UPDATE mysql.user SET File_priv = 'Y' WHERE user='my_user' AND host='localhost';

最后重新启动MySQL服务器：

$ sudo service mysql restart

在此之后，我可以写入 secure_file_priv 目录。对于我来说，它是 /var/lib/mysql-files/，但您可以使用以下命令进行检查：

> SHOW VARIABLES LIKE "secure_file_priv";

- Eino Gourdin