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

$ pwd
$ 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)

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)
  • 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
    Commented Apr 24, 2012 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
    Commented Apr 24, 2012 at 2:40
  • Thank you, this user does have SELECT permissions. I often browse the database as this user.
    – dotancohen
    Commented Apr 24, 2012 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
    Commented Apr 24, 2012 at 3:43
  • 2
    Ensure that the user has FILE privilege as described by MySQL docs. Commented Apr 24, 2012 at 4:01

5 Answers 5


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


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


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

UPDATE 2012-05-01 07:09 EDT

To give yourself FILE privilege, do the following:

service mysql restart --skip-networking --skip-grant-tables
mysql <hit enter>
UPDATE mysql.user SET File_priv = 'Y' WHERE user='gs' AND host='localhost';
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
    Commented May 1, 2012 at 10:37
  • 1
    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. Commented May 1, 2012 at 10:48
  • "Azure Database for MariaDB" does not support OUTFILE, see learn.microsoft.com/en-us/azure/mariadb/concepts-limits
    – Pieter
    Commented Apr 26, 2020 at 17:56
  • @RolandoMySQLDBA Hi, specifying only the file name in SELECT INTO OUTFILE command creates the file in /var/lib/mysql/<DB-NAME> directory. This directory doesn't have write permissions for group and world(others). So after mysql creates a file there, other users aren't able to read it. Can/Should the permissions of innermost DB directory be changed?
    – Ayush
    Commented Jun 12, 2020 at 11:56

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

  • 3
    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
    Commented Aug 15, 2019 at 6:54
  • @dotancohen Is it possible that a person has access to MySQL CLI, but not to Bash CLI?
    – Rodrigo
    Commented Mar 8, 2023 at 21:23
  • 2
    @Rodrigo: Yes, certainly. For one thing, RDS (Amazon's hosted database servers) do not provide a CLI, though of course one could connect to them using any Bash instance and redirect as mentioned in this answer. But also, often enough one will be working comfortably in the MySQL CLI already and just need to export a file. This question is about fixing the permissions issue, not looking for workarounds.
    – dotancohen
    Commented Mar 10, 2023 at 6:03

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.


Regarding the two methods for outputting data to CSV (should be TSV indeed) mentioned above, I have found that if there are empty values in the entries that you are exporting, there is a risk that the data could be messed up due to misallocation of the data to the corresponding columns.

With concern for data integrity for recovery, I found this website:


It mentioned that --xml option in mysqldump allows exporting the data into XML format which could be then parsed by a custom script to any format needed, including TSV.


For MariaDB 10.1+ with SystemD possibly this Jira ticket provides the solution:


See very last comment at the end. Additional info:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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