MYSQL 进入输出文件“拒绝访问”-但我的用户具有“所有”访问权限.. 文件夹是 CHMOD 777

MYSQL into outfile "access denied" - but my user has "ALL" access.. and the folder is CHMOD 777

提问by Shackrock

Any ideas?

有任何想法吗？

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

Error:

错误：

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

回答by Pablo Santa Cruz

Try executing this SQL command:

尝试执行此 SQL 命令：

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

It seems that you are having issues with connecting to the database and not writing to the folder you're mentioning.

您似乎在连接到数据库而不是写入您提到的文件夹时遇到问题。

Also, make sure you have granted FILEto user 'asdfsdf'@'localhost'.

另外，请确保您已授予FILEuser 'asdfsdf'@'localhost'。

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

回答by user1028904

Honestly I didnt bother to deal with the grants and this worked even without the privileges:

老实说，我没有费心处理赠款，即使没有特权，这也有效：

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

回答by e18r

As @fijaaron says,

正如@fijaaron 所说，

1. GRANT ALLdoes not imply GRANT FILE
2. GRANT FILEonly works with *.*

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

So do

也一样

GRANT FILE ON *.* TO user;

回答by Grindlay

Since cP/WHM took away the ability to modify User privileges as root in PHPMyAdmin, you have to use the command line to:

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

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

Step 2 is to allow that user to dump a file in a specific folder. There are a few ways to do this but I ended up putting a folder in :

第 2 步是允许该用户转储特定文件夹中的文件。有几种方法可以做到这一点，但我最终把一个文件夹放在：

/home/user/tmp/db

and

和

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

That allows the mysql user to write the file. As previous posters have said, you can use the MySQL temp folder too, I don't suppose it really matters but you definitely don't want to make it 0777 permission (world-writeable) unless you want the world to see your data. There is a potential problem if you want to rinse-repeat the process as INTO OUTFILEwon't work if the file exists. If your files are owned by a different user then just trying to unlink($file)won't work. If you're like me (paranoid about 0777) then you can set your target directory using:

这允许 mysql 用户写入文件。正如之前的海报所说，您也可以使用 MySQL 临时文件夹，我认为这并不重要，但您绝对不想让它成为 0777 权限（世界可写），除非您希望全世界看到您的数据。如果您想冲洗重复该过程，INTO OUTFILE则存在潜在问题，因为如果文件存在，则该过程将不起作用。如果您的文件归其他用户所有，那么尝试unlink($file)将无法正常工作。如果您像我一样（对 0777 感到偏执），那么您可以使用以下方法设置目标目录：

chmod($dir,0777)

just prior to doing the SQL command, then

就在执行 SQL 命令之前，然后

chmod($dir,0755)

immediately after, followed by unlink(file)to delete the file. This keeps it all running under your web user and no need to invoke the mysql user.

紧接着，接着是unlink(file)删除文件。这使它在您的 web 用户下运行，无需调用 mysql 用户。

回答by Eino Gourdin

I tried all the solutions but it still wasn't sufficient. After some more digging I eventually found I had also to set the 'file_priv' flag, and restart mysql.

我尝试了所有解决方案，但仍然不够。经过更多的挖掘，我最终发现我还必须设置“file_priv”标志，然后重新启动 mysql。

To resume :

恢复 ：

Grant the privileges :

授予特权：

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

> GRANT FILE ON *.* TO my_user;

> FLUSH PRIVILEGES; 

Set the flag :

设置标志：

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

Finally restart the mysql server:

最后重启mysql服务器：

$ sudo service mysql restart

After that, I could write into the secure_file_privdirectory. For me it was /var/lib/mysql-files/, but you can check it with the following command :

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

> SHOW VARIABLES LIKE "secure_file_priv";

回答by Akshay Hegde

For future readers, one easy way is as follows if they wish to export in bulk using bash,

对于未来的读者，如果他们希望使用 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      |
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+

If you're exporting by non-root user then set permission like below

如果您是由非 root 用户导出，则设置如下权限

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

Restart or Reload mysqld

重新启动或重新加载 mysqld

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

Sample code snippet

示例代码片段

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

Execute

执行

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"