Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to output the data from an MySQL table to a file but getting permission errors:

$ pwd
/home/dotancohen
$ mkdir in
$ chmod 777 in/
$ mysql -ugs -p
mysql> USE someDatabase;
mysql> SELECT * FROM data INTO OUTFILE '/home/dotancohen/in/data.csv';
ERROR 1045 (28000): Access denied for user 'gs'@'localhost' (using password: YES)
mysql>

If the directory in question is chmodded to 777, then why cannot the MySQL user write the file? Interestingly enough, I cannot write to /tmp/ either.

EDIT: It looks like the DB user has the proper MySQL permissions:

mysql> show grants;
+----------------------------------------------------------------------------------+
| Grants for gs@localhost                                                          |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'gs'@'localhost' IDENTIFIED BY PASSWORD 'somePassword'     | 
| GRANT ALL PRIVILEGES ON `gs\_%`.* TO 'gs'@'localhost'                            | 
+----------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
share|improve this question

migrated from stackoverflow.com Apr 25 '12 at 16:53

This question came from our site for professional and enthusiast programmers.

2  
You have to grant access rights to MySQL on the entire directory tree. Granting rights on in is pointless if MySQL can't access dotanchoen. In other words, the safety depost box in the bank vault can be left wide open, but if the bank vault door is locked, you ain't getting into the box. Your gs user must also have the mysql FILE privilege to actually execute that query. –  Marc B Apr 24 '12 at 2:39
    
The problem is you might not have select perms? Mysql permissions are not controlled by directory permissions. You need to use mysql -u <username> -p to run as a specific mysql user. To give user access to a db, take a look MySql grant. dev.mysql.com/doc/refman/5.1/en/grant.html –  user1352636 Apr 24 '12 at 2:40
    
Thank you, this user does have SELECT permissions. I often browse the database as this user. –  dotancohen Apr 24 '12 at 3:42
    
Thanks, Marc, that is what I was afraid of. So I cannot have an "inbox" folder that other users can write to, without letting them read / write to my home directory as well? –  dotancohen Apr 24 '12 at 3:43
2  
Ensure that the user has FILE privilege as described by MySQL docs‌​. –  Mike Purcell Apr 24 '12 at 4:01

1 Answer 1

up vote 2 down vote accepted

According to MySQL Documentation on SELECT ... INTO OUTFILE

Any file created by INTO OUTFILE or INTO DUMPFILE is writable by all users on the server host. The reason for this is that the MySQL server cannot create a file that is owned by anyone other than the user under whose account it is running. (You should never run mysqld as root for this and other reasons.) The file thus must be world-writable so that you can manipulate its contents.

You should output the SELECT INTO OUTFILE to /var/lib/mysql as follows

SELECT * FROM data INTO OUTFILE 'data.csv';

Of course, you need to make sure you have FILE permission on gs@localhost.

There are two ways to have this permission given

METHOD #1

GRANT FILE ON *.* TO 'gs'@'localhost';

METHOD #2

UPDATE mysql.user SET File_priv = 'Y' WHERE user='gs' AND host='localhost';
FLUSH PRIVILEGES;

UPDATE 2012-05-01 07:09 EDT

To give yourself FILE privilege, do the following:

  • STEP 01) service mysql restart --skip-networking --skip-grant-tables
  • STEP 02) mysql <hit enter>
  • STEP 03) UPDATE mysql.user SET File_priv = 'Y' WHERE user='gs' AND host='localhost';
  • STEP 04) exit
  • STEP 05) service mysql restart
share|improve this answer
    
Thank you Rolando! How can I check if I do have FILE privileges, considering that I do not have privileges to DESCRIBE the mysql.user table? –  dotancohen May 1 '12 at 10:10
    
Just run the query SHOW GRANTS; and it will output what privileges you currently have. –  RolandoMySQLDBA May 1 '12 at 10:31
    
Thanks. I updated the question with the output of SHOW GRANTS. It looks like the DB user should have the proper permissions. –  dotancohen May 1 '12 at 10:37
    
The DB user does not have the proper permissions. FILE privilege is only given to a user with GRANT ALL PRIVILEGES ON *.*, just like RELOAD and SHUTDOWN. This is so because those are global administrative privileges. The GRANT ALL privilege you have is for the gs database only. –  RolandoMySQLDBA May 1 '12 at 10:48
    
I see, thank you! –  dotancohen May 1 '12 at 10:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.