Mysql – Difference of write authorization on file system with theSQL

access-controlcommand lineMySQLpermissions

On my local mySQL (on CentOS 6 VM) database I wanted to export some data from the DB to a file like this:

SELECT html
FROM mytable
WHERE lang = 'en' and id='KEY01'
INTO OUTFILE 'key01_en.html'

By doing this, the file will be saved in the /var/lib/mysql/mydbschema/ by default.

Now, I want the file to be saved in my user let say /home/userone/dump_html folder

If I say this (I launch the mysql command from the shell, and logged as "userone": 

SELECT html
FROM mytable
WHERE lang = 'en' and id='KEY01'
INTO OUTFILE '/home/userone/dumphtml/key01_en.html'

It makes an error that the process does not have enough rights to write there.

But now still from the mysql> prompt, if I make:

SELECT html
FROM mytable
WHERE lang = 'en' and id='KEY01'
INTO OUTFILE '/tmp/key01_en.html'
;

-- and then
system mv /tmp/key01_en.html /home/userone/dumphtml/key01_en.html
;

It works without any issue.

It looks strange to me the SQL cannot write on my disk but the system command can. Any idea why?
(The discussion started on this point from this answer on SO)

Best Answer

Your problem is that MySQL cannot write to your home directory, as by default it will run as the MySQL user, or as some other user you defined when you installed and configured it, if not using the default rpm or other install package. Users are not, by default, able to write to another user's home directory as it is each account's private space.

/tmp is by default writable by all users. As such MySQL can write there. Depending on user groups and umask on your system you may be able to read that output as your user.

If you don't want to use /tmp you could create another directory where your user and MySQL can write and make sure your user and MySQL are members of the same group.

Related Question