20

I am running into a permission error when trying to load data from a flat file database dump into a new table. I know that the schema of the file and my table is the same and I tried tweaking the permissions. What else should I try?

mysql> load data infile 'myfile.txt' into table mytable fields terminated by ',' enclosed by '"';
ERROR 1045 (28000): Access denied for user 'user'@'%' 

grant all on mytable.* to 'user'@'%
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
sutee
  • 12,568
  • 13
  • 49
  • 61
  • 4
    See also [What file and directory permissions are required for MySQL LOAD DATA INFILE?](http://stackoverflow.com/q/3971541) (try changing `LOAD DATA INFILE 'myfile.txt'` to `LOAD DATA LOCAL INFILE 'myfile.txt'` – Danny Beckett Jan 20 '14 at 14:39
  • possible duplicate of [access denied for load data infile in MySQL](http://stackoverflow.com/questions/2221335/access-denied-for-load-data-infile-in-mysql) – blo0p3r Apr 29 '14 at 17:21
  • @DannyBeckett solution with 'LOCAL' might not be available for all mysql versions. For instance , for '5.7.34-log' won't work. – mesh May 03 '22 at 13:44
  • Correcting myself, The above solution with 'LOCAL' might require an additional set of the property 'allowLoadLocalInfile' to 'TRUE'. – mesh May 03 '22 at 13:51

2 Answers2

40

Here's a thread on the MySQL forums that discusses exactly this.

Here's the answer, posted by Ken Tassell

Problem resolved using the command below:

grant file on *.* to kentest@localhost identified by 'kentest1';
Ólafur Waage
  • 68,817
  • 22
  • 142
  • 198
7

You might have MySQL privileges on the destination table, but you also need the FILE privilege to execute LOAD DATA, and of course the MySQL Server process needs operating-system privileges to the data file too.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828