Generate CSV from MySQL
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;