Loading data from a file directly into a table is one of the nice features of MySQL. However, before this can be done we need to make sure that the correct permissions are assigned to the user performing the import. Otherwise the process will fail with the following error message:
mysql> LOAD DATA INFILE '/tmp/product.csv' INTO TABLE name LINES TERMINATED BY '\n'; ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: NO)
The error indicates that the user does not have the appropriate permission to perform this kind of operation. The LOAD DATA INFILE is controlled by the FILE global privileges. These are privileges that are not directly assigned on a database or table basis but on the MySQL server level.
To grant a global privilege we need to specify ‘*.*’ rather than ‘{database}.{table}’ that is common for INSERT, UPDATE, SELECT, DELETE privileges. The ‘*’ indicates all.
So in the case above we need to perform the following command as a privileged user (root).
GRANT FILE ON *.* TO user@localhost;
An alternative way of performing the same operation involves updating one of the MySQL server tables as follows:
UPDATE mysql.user SET File_priv = 'Y' WHERE Host = 'localhost' AND User = 'user'; FLUSH PRIVILEGES;