13

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)
7
  • 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. Apr 24 '12 at 4:01
13

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
4
  • 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. May 1 '12 at 10:48
  • "Azure Database for MariaDB" does not support OUTFILE, see docs.microsoft.com/en-us/azure/mariadb/concepts-limits
    – Pieter
    Apr 26 '20 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
    Jun 12 '20 at 11:56
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.

1

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
  • 2
    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 '19 at 6:54
0

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:

https://www.eversql.com/exporting-mysql-schema-structure-to-xml-using-mysql-clients/

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.

0

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

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.