3

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?

  • 1
    -p is the password, not the host name. Try mysql --help to get a list of options. The -h option is used to provide the host name or IP. – Sven Aug 15 '14 at 14:01
  • I have missed the -h.... – bhttoan Aug 15 '14 at 14:03
3

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

  • On the remote server I use mysql -u username -p DBSERVER_IP and still get the same error as above – bhttoan Aug 15 '14 at 13:59
  • 1
    That's not what -p is for... – Dennis Kaarsemaker Aug 15 '14 at 14:00
  • -p prompts me for my password which I enter and then get the above error, does the ordering matter? – bhttoan Aug 15 '14 at 14:01
  • 1
    what this does is 'connect to localhost, select the DBSERVER_IP database'. You need to use -h. – Dennis Kaarsemaker Aug 15 '14 at 14:03
  • I missed the -h – bhttoan Aug 15 '14 at 14:03
0

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

-1

You'll need to add the localhost user separately. You can do it in one command (rather than two).

GRANT ALL on database.* to 'user'@'localhost' identified by 'paswword';

If you want to add the user so it can connect from anywhere (less secure), use this:

GRANT ALL on database.* to 'user'@'%' identified by 'password';
  • The best answer to your issue is as stated in the comments above. The -p option is for password, use -h to specify the remote host. I originally thought you were trying to connect to a localhost db using the credentials from a remote db. – Satalink Aug 15 '14 at 14:30
-1

TIP:

comment out pid and socket file, if you can't connect to server.. mysql could be connecting with incorrect pid and/or socket file, so when you try to connect to server trough command-line it "looks" at the incorrect pid/socket file...

### comment out pid and socket file, if you can't connect to server..
#pid-file=/var/run/mysql/mysqld.pid #socket=/var/lib/mysql/mysql.sock

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.