MySQL Load Data Infile: ERROR 1045 (28000): Access denied for user…
Recently I was trying to load a CSV file into a MySQL database on a Linux box using the following command:
LOAD DATA INFILE '/path/to/my_data.csv' INTO TABLE `my_data` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
I kept getting the following error:
ERROR 1045 (28000): Access denied for user …
After Googling around I got some suggestions:
- chown mysql.mysql my_data.csv; chmod 666 my_data.csv
- move the data file to /tmp folder, where all processes have read access
- Grant a FILE permissions to the user. Basically GRANT *.* usage.
- Load data LOCAL infile…
Nothing seemed to help.
Luckily I found the following command which did the job:
mysqlimport -v --local --fields-enclosed-by='"' --fields-escaped-by='\' --fields-terminated-by=',' --lines-terminated-by='\r\n' -u[db_user] -p[db_password] -h [hostname] [db_name] '/path/to/my_data.csv'