查询输出到文件给出访问被拒绝错误 [英] Query output to a file gives access denied error

问题描述

我正在尝试使用以下查询将MySQL中SQL查询的输出捕获到文本文件中.

I am trying to capture the output of a SQL query in MySQL, to a text file using the following query.

select count(predicate),subject from TableA group by subject into outfile '~/XYZ/output.txt';

我收到以下错误消息.

错误1045(28000):用户'username'@'％'的访问被拒绝(使用密码:是)

ERROR 1045 (28000): Access denied for user 'username'@'%' (using password: YES)

任何想法，我要去哪里错了?这是与权限相关的问题吗?

Any idea, where am I going wrong? Is it some permission related issue?

推荐答案

Outfile是mysql本身的权限.

Outfile is it's own permission in mysql.

如果所有内容都包含在内.

If you have ALL it's included.

但是，如果您只有一个安全的集合，例如SELECT，INSERT，UPDATE，DELETE，DROP，CREATE，但没有OUTFILE，则"into outfile"将不适用于查询.

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.

这样做的原因是，即使出于写目的，从MySQL内部访问文件也存在一定的安全风险，因为如果从mysql访问文件，则可以访问mysql用户有权访问的任何文件，从而绕过基于用户的访问文件权限.

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.

要解决此问题，您可以将查询直接运行到用来运行sql的任何shell/语言的输出中.

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.

这是一个* nix示例

Here is a *nix example

>$ 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.

这里发生的事情是，您正在向mysql客户端运行查询，它会吐出结果，然后将输出定向到文件.因此，永远不会从mysql内部调用该文件，而是在mysql运行后调用该文件.

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获取信息，然后将数据从您自己的用户shell中转储到文件中，就可以了.

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权限，无论哪种方式.

Or find a way to get yourself the outfile mysql permission, either way.

这篇关于查询输出到文件给出访问被拒绝错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！