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.

4 Answers
4

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.

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.
–
David LockeOct 23 '08 at 23:59

What are the ownerships and permissions on the directory in which the file resides?
–
Bill KarwinOct 24 '08 at 2:24

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.
–
joelhardiOct 24 '08 at 3:20

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)
–
Manoj GovindanAug 26 '14 at 9:12