12

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)

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
11

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
  • 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
1

Different distributions and OSes don't all handle the destinations for OUTFILEs the same.

For example, when running a mysqld daemon on Linux, which uses a socket, the OUTFILE sometimes is written to the /tmp directory. Not a big deal, it's just using the OUTFILE approach has shortcomings, namely dealing with permissions and finding where the file went.

Since the aim of this question isn't specifically "How to use an OUTFILE", but you're just looking to capture some MySQL data into a file, here's an alternative that doesn't require you to twiddle around with FILE permissions, etc.

$ (echo 'SELECT * FROM data' | mysql -ugs -p[password])> /home/dotancohen/in/data.csv

The output of this is tab-delimited by default. For commas, just pipe it through sed or something before writing it into the file.

0

I spent hours trying to understand the suggestions on this page and many other pages of StackOverflow.

No matter how I changed the permissions in MySql, I couldn't get anything to work.

I reverted back to the permissions I started with.

Ultimately what worked for me was simpler than others' suggestions:

echo "select id, emailAddress FROM contacts" | mysql --user=myusername --password mydatabasename > /home/my_output_file.tsv

  • 1
    Your method is good for use when on the Bash CLI. However the question in the OP asks about how to output data to a file from the MySQL client CLI. – dotancohen Aug 15 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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