我怎么能有MySQL写输出文件作为不同的用户? - How can I have MySQL write outfiles as a different user?

- 此内容更新于:2016-01-24
主题:

  我使用一个MySQL查询到一个输出文件写道。我运行这个查询每隔一两天,所以我希望能够消除输出文件,而无需求助于苏或sudo。我能想到的唯一方法,是让输出文件写成旗下的mysql用户以外的人。这是可能的吗?编辑:我不是重定向输出到一个文件中,我使用到输出文件选择查询的一部分输出到一个文件。如果有帮助:
  mysql——版本
  mysql版本14.12Distrib5.0.32,pc-linux-gnu(x86_64)使用readline5.2

原文:

I'm working with a MySQL query that writes into an outfile. I run this query once every day or two and so I want to be able to remove the outfile without having to resort to su or sudo. The only way I can think of making that happen is to have the outfile written as owned by someone other than the mysql user. Is this possible?

Edit: I am not redirecting output to a file, I am using the INTO OUTFILE part of a select query to output to a file.

If it helps:

mysql --version
mysql  Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (x86_64) using readline 5.2

解决方案:
输出文件是mysqld创造的过程,而不是由你的客户的过程。因此输出文件必须由mysqld的uid和gid的过程。你可以避免sudo访问文件如果你访问它从流程下一个uid或gid,可以访问该文件。换句话说,如果mysqld创建文件由uid和gid“mysql”/“mysql”,然后添加您自己的账户组“mysql”。然后你应该能够访问文件,提供了包括集团访问文件的权限模式。编辑:你删除一个文件在/tmp目录权限rwxrwxrwt模式。粘贴位(t)意味着您可以删除文件只有你的uid是一样的文件的所有者,不管文件或目录的权限。如果你在另一个目录保存输出文件没有粘贴位设置,你应该可以正常删除文件。读这段节选手册页获得粘性(8):粘性目录的目录的粘贴位被设置成为一个扩展目录,或者,更准确地说,一个目录中删除的文件是受限制的。一种粘性的目录中的一个文件可能只由用户如果用户被删除或重命名目录的写权限,用户文件的所有者,目录的所有者或超级用户。这个特性有效地应用于目录(比如/tmp)必须公开可写的但应该否认用户许可随意删除或重命名彼此的文件。
原文:

The output file is created by the mysqld process, not by your client process. Therefore the output file must be owned by the uid and gid of the mysqld process.

You can avoid having to sudo to access the file if you access it from a process under a uid or gid that can access the file. In other words, if mysqld creates files owned by uid and gid "mysql"/"mysql", then add your own account to group "mysql". Then you should be able to access the file, provided the file's permission mode includes group access.

Edit:

You are deleting a file in /tmp, with a directory permission mode of rwxrwxrwt. The sticky bit ('t') means you can remove files only if your uid is the same as the owner of the file, regardless of permissions on the file or the directory.

If you save your output file in another directory that doesn't have the sticky bit set, you should be able to remove the file normally.

Read this excerpt from the man page for sticky(8):

STICKY DIRECTORIES

A directory whose `sticky bit' is set becomes an append-only directory, or, more accurately, a directory in which the deletion of files is restricted. A file in a sticky directory may only be removed or renamed by a user if the user has write permission for the directory and the user is the owner of the file, the owner of the directory, or the super-user. This feature is usefully applied to directories such as /tmp which must be publicly writable but should deny users the license to arbitrarily delete or rename each others' files.

网友:的文件拥有“mysql”/“mysql”和一些666年。我已经添加了mysql集团和我仍然不能删除它。

(原文:The file is owned at "mysql"/"mysql" and modded 666. I've added myself to the mysql group and I still can't delete it.)

网友:什么是所有权和权限的文件所在的目录吗?

(原文:What are the ownerships and permissions on the directory in which the file resides?)

网友:我认为比尔是的意思是,你必须有写权限包含dir能够删除一个文件在UNIX(b/c它更新dir清单)。所以“chgrpmysql“dir和chmodug+rwX“给你mysql-group用户写烫发dir。

(原文:I think what Bill is getting at is that you have to have write permission on the containing dir to be able to delete a file in UNIX (b/c it updates the dir listing). So "chgrp mysql" the dir and "chmod ug+rwX" it to give your mysql-group user write perms on the dir.)

网友:是的,那是我在暗示什么。

(原文:Yep, that was what I was getting at.)

网友:我正在写文件/tmp。“drwxrwxrwt”根/根

(原文:I'm writing the file to /tmp. "drwxrwxrwt" root/root)

解决方案:
不使用“选择…到输出文件”语法,没有。您需要运行查询(即客户端)作为另一个用户,并将输出重定向。例如,编辑你的crontab随时运行以下命令:将创建/tmp/输出文件。txt作为用户crontab命令来添加。
原文:

Not using the "SELECT...INTO OUTFILE" syntax, no.

You need to run the query (ie client) as another user, and redirect the output. For example, edit your crontab to run the following command whenever you want:

mysql db_schema -e 'SELECT col,... FROM table' > /tmp/outfile.txt

That will create /tmp/outfile.txt as the user who's crontab you've added the command to.

网友:请注意,管道相比,将产生不同的结果。例如管道包括列名第一行,NULL代替\N等(在Ubuntu。细节可能不同于其他操作系统)

(原文:Note that piping will yield different results when compared to into outfile. For instance piping includes column names as first row, NULL instead of \N etc. (in Ubuntu. Details may vary in other OSes))

解决方案:
如果你有另一个用户从cron运行查询,它将创建的文件的用户。
原文:

If you have another user run the query from cron, it will create the file as that user.

解决方案:
我只是做并添加,就是这样,我很容易能做任何文件名
原文:

I just do

sudo gedit /etc/apparmor.d/usr.sbin.mysqld

and add

 /var/www/codeigniter/assets/download/* w,

and

sudo service mysql restart

And that's it, I can do easily SELECT INTO OUTFILE any filename