On importing a csv file into a table with same structure that placed on different machine, with mysql import using mysql root user , it is common that we may end up with some access denied error messages.
mysqlimport -h yourhostname –port 3306 -u root -pyourpassword –fields-terminated-by=’;’ –local csv_test tweet.csv
For instance the above stated query which worked in some machines, may raise
mysqlimport: Error: 1045 Access denied for user ‘root’@’ipaddress-of-remote-host’ (using password: YES)
The fix to this issue is as follows :
- First check the my.cnf file ( inside /etc/mysql folder ) and look for the line :
bind-address = 127.0.0.1
If it not commented, comments it out, as :
#bind-address = 127.0.0.1
This tells system that, this mysql is accessible from different hosts as well.
2. Next we need to grant permission to the root user.
For that login to the mysql prompt using mysql -u root -p. Then execute the following query,
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘password’;
3. Now restart mysql using the command:
service mysql restart