September 5th, 2013 by Gia

Query to generate CSV file :

mysql> SELECT * FROM tablename
INTO OUTFILE '/tmp/csvfile.csv'
FIELDS terminated by ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Error when user don’t have privilege to create file :

mysql>SELECT * FROM tablename
INTO OUTFILE '/tmp/csvfile.csv'
FIELDS terminated by ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
ERROR 1045 (28000): Access denied for user 'demo'@'localhost' (using password: YES)

Add grant to create file (execute using root user or user with grant option) :

mysql> GRANT FILE ON *.* TO 'demo'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql>FLUSH PRIVILEGES;

** GRANT FILE only works with ON *.*

If you want delete, remove or revoke GRANT file :

mysql> REVOKE FILE on *.* FROM 'demo'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql>FLUSH PRIVILEGES;

Comments

comments

Tags: ,
Posted in Database, MySQL | No Comments »
Leave a Reply

Time limit is exhausted. Please reload CAPTCHA.