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'
Related posts:
