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)
Best Answer
According to MySQL Documentation on SELECT ... INTO OUTFILE
You should output the
SELECT INTO OUTFILE
to /var/lib/mysql as followsOf course, you need to make sure you have FILE permission on gs@localhost.
There are two ways to have this permission given
METHOD #1
METHOD #2
UPDATE 2012-05-01 07:09 EDT
To give yourself FILE privilege, do the following: