MySQL 查询输出到文件给出拒绝访问错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/668618/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query output to a file gives access denied error
提问by Arnkrishn
I am trying to capture the output of a SQL query in MySQL, to a text file using the following query.
我正在尝试使用以下查询将 MySQL 中 SQL 查询的输出捕获到文本文件中。
select count(predicate),subject from TableA group by subject into outfile '~/XYZ/output.txt';
I get the following error.
我收到以下错误。
ERROR 1045 (28000): Access denied for user 'username'@'%' (using password: YES)
ERROR 1045 (28000): 用户 'username'@'%' 访问被拒绝（使用密码：YES）
Any idea, where am I going wrong? Is it some permission related issue?
任何想法，我哪里出错了？这是一些与许可相关的问题吗？
回答by Fire Crow
Outfile is it's own permission in mysql.
Outfile 是它自己在 mysql 中的权限。
If you have ALL it's included.
如果您拥有所有，则包括在内。
But if you just have a safe collection such as SELECT, INSERT, UPDATE, DELETE, DROP, CREATE, but not OUTFILE, "into outfile" will not work in queries.
但是，如果您只有一个安全集合，例如 SELECT、INSERT、UPDATE、DELETE、DROP、CREATE，但没有 OUTFILE，则“into outfile”在查询中将不起作用。
The reason for this is that accessing files from within MySQL, even for write purposes, has certain security risks, because if you access a file from mysql you can access any file the mysql user has access to, thereby bypassing user-based file permissions.
这样做的原因是，从 MySQL 内部访问文件，即使是出于写入目的，也存在一定的安全风险，因为如果从 mysql 访问文件，则可以访问 mysql 用户有权访问的任何文件，从而绕过基于用户的文件权限。
To get around this, you can run your query directly into the output of whatever shell/language you're using to run the sql with.
为了解决这个问题，您可以将查询直接运行到您用来运行 sql 的任何 shell/语言的输出中。
Here is a *nix example
这是一个 *nix 示例
>$ echo "select count(predicate),subject from TableA group by subject" | mysql -u yourusername -p yourdatabasename > ~/XYZ/outputfile.txt
But do it all on one line without the "\" or use the "\" to escape the line break.
但是在没有“\”的情况下在一行上完成所有操作，或者使用“\”来逃避换行符。
What's happening here is that you're running a query into the mysql client and it's spitting out the result, then you're directing the output to a file. So the file is never called from within mysql, it's called after mysql runs.
这里发生的事情是，您正在向 mysql 客户端运行查询，它正在输出结果，然后您将输出定向到一个文件。所以该文件永远不会从 mysql 内部调用，它在 mysql 运行后调用。
So use mysql to get the information and THEN dump the data to the file from your own user shell and you'll be fine.
因此，使用 mysql 获取信息，然后将数据从您自己的用户 shell 转储到文件中，您会没事的。
Or find a way to get yourself the outfile mysql permission, either way.
或者找到一种方法让自己获得 outfile mysql 权限，无论哪种方式。
回答by Anachronous
If it's your system (you're admin), and you know how to secure it, this is how you enable those permissions.
如果它是您的系统（您是管理员），并且您知道如何保护它，那么这就是您启用这些权限的方式。
USE mysql;
UPDATE user SET File_priv = 'Y' WHERE User = 'db_user';
FLUSH PRIVILEGES;
