Access denied issue with mysqlimport with remote machine

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 :

  1. 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s