access denied for load data infile in MySQL

Question

I just ran into this issue as well. I had to add LOCAL to my SQL statement.

For example, this gives the permission problem:

LOAD DATA INFILE '{$file}' INTO TABLE {$table}

Add LOCAL to your statement and the permissions issue should go away. Like so:

LOAD DATA LOCAL INFILE '{$file}' INTO TABLE {$table}

I had this problem. I searched around and did not find a satisfactory answer. I summarise below the results of my searches.

The access denied error could mean that:

• 'user'@'localhost' does not have the FILE privilege (GRANT FILE on *.* to user@'localhost'); or,
• the file you are trying to load does not exist on the machine running mysql server (if using LOAD DATA INFILE); or,
• the file you are trying to load does not exist on your local machine (if using LOAD DATA LOCAL INFILE); or,
• the file you are trying to load is not world readable (you need the file and all parent directories to be world-readable: chmod 755 directory; and, chmod 744 file.dat)

Try using this command:

load data local infile 'home/data.txt' into table customer;

This should work. It worked in my case.

Ensure your MySQL user has the FILE privilege granted.

If you are on shared web hosting, there is a chance this is blocked by your hosting provider.

I found easy one if you are using command line

Login asmysql -u[username] -p[password] --local-infile

then SET GLOBAL local_infile = 1;

select your database by use [db_name]

and finally LOAD DATA LOCAL INFILE 'C:\\Users\\shant\\Downloads\\data-1573708892247.csv' INTO TABLE visitors_final_test FIELDS TERMINATED BY ','LINES TERMINATED BY '\r \n' IGNORE 1 LINES;