Cannot output MySQL data to file

Question

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)

• Marc B: 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.
• user1352636: 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
• dotancohen: Thank you, this user does have SELECT permissions. I often browse the database as this user.
• dotancohen: 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?
• Mike Purcell: Ensure that the user has FILE privilege as described by MySQL docs.

Answer 1

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:

service mysql restart --skip-networking --skip-grant-tables
mysql <hit enter>
UPDATE mysql.user SET File_priv = 'Y' WHERE user='gs' AND host='localhost';
exit
service mysql restart

• dotancohen: Thanks. I updated the question with the output of SHOW GRANTS. It looks like the DB user should have the proper permissions.
• RolandoMySQLDBA: 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.
• Pieter: "Azure Database for MariaDB" does not support OUTFILE, see learn.microsoft.com/en-us/azure/mariadb/concepts-limits
• Ayush: @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?

Answer 2

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

• dotancohen: 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.
• Rodrigo: @dotancohen Is it possible that a person has access to MySQL CLI, but not to Bash CLI?
• dotancohen: @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.

Answer 3

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.

Answer 4

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

https://jira.mariadb.org/browse/MDEV-10025

See very last comment at the end. Additional info:

https://www.freedesktop.org/software/systemd/man/systemd.exec.html