MySQL remote connection - Access denied for user 'username'@'localhost' (using password: YES)

I am setting up a remote connection to a MySQL server but no matter what I try I am getting access denied error from the remote server.

I added the user by:

CREATE USER 'username'@'REMOTE_IP_ADDRESS' IDENTIFIED BY '***';

Then I gave permissions:

GRANT ALL PRIVILEGES ON databasename.* to username@REMOTE_IP_ADDRESS;

My user table on MySQL server shows (amongst other things):

+---------------+--------------------+
| user          | host               |
+---------------+--------------------+
| username      | REMOTE_IP_ADDRESS  |
+---------------+--------------------+

If I show grants for the user I get:

+------------------------------------------------------------------------------------+
| Grants for username@REMOTE_IP_ADDRESS
+------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'username'@'REMOTE_IP_ADDRESS' IDENTIFIED BY PASSWORD '***'
| GRANT ALL PRIVILEGES ON `databasename`.* TO 'username'@'REMOTE_IP_ADDRESS'
+------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

On the remote server I then use:

mysql -u username -p DB_SERVER_IP

and get the error message:

ERROR 1045 (28000): Access denied for user 'username'@'localhost' (using password: YES)

I have done "flush privileges" but I still cannot connect - I notice that the error is username@localhost and I don't have any permissions set up for username@localhost but I have never had to do this before. I have some other remote accounts on the same server and none of them have @localhost as they only connect locally.

What am I missing? Have I forgotten to do something or what else can I check?

Solution 1:

The error message indicates that you're not connecting to the remote MySQL at all, but to localhost. Make sure you connect using option -h to the remote server.

mysql -u username -h DB_SERVER_IP

Solution 2:

For simplicity, let's say this:

Computer A = Holds the MySQL DB. IPv4= 192.168.11.1

Computer B= Has an Eclipse program that wants to connect to Computer A and do stuff to the schemas. Ipv4= 192.168.22.2

Do the Following on Computer A:

It looks like you're trying to make a new user by the name of 'username'@'192.168.22.2' but a different user by the name of 'username'@'localhost' is getting in the way/taking precedence.

Do this command to see the users:

SELECT user,host FROM mysql.user;

You should see two entries:

1) user= username, host= localhost

2) user= username, host=192.168.22.2

So do these commands:

DROP User 'username'@'localhost';
DROP User 'username'@'192.168.22.2';

Note, if you see 'username'@'%' user, also drop/delete that row.

Restart MySQL Workbench

Then do your original commands again:

CREATE USER 'username'@'192.168.22.2' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'192.168.22.2';

I don't understand why u used "***" as your password so I replaced it with 'password'.

Then go to Computer B and open your Command Prompt/Terminal. Type in the following and you should be connected to Computer A. If you can't connect, check Computer A's firewall's inbound rules and check if the MySQL Service is running on Computer A.

mysql --host=192.168.11.1 --user=username --password=password

(Make sure you have mysql setup as an environmental variable otherwise Command Prompt won't understand the "mysql" command)

Helpful Troubleshooting Methods: Turn off all Firewall Rules on Computer A. From Computer B's Command Prompt/Terminal, do "ping 192.168.11.1" or "telnet 192.168.11.1 3306".