Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
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 at 14:01
    
I have missed the -h.... –  bhttoan Aug 15 at 14:03

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer
    
On the remote server I use mysql -u username -p DBSERVER_IP and still get the same error as above –  bhttoan Aug 15 at 13:59
1  
That's not what -p is for... –  Dennis Kaarsemaker Aug 15 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 at 14:01
    
what this does is 'connect to localhost, select the DBSERVER_IP database'. You need to use -h. –  Dennis Kaarsemaker Aug 15 at 14:03
    
I missed the -h –  bhttoan Aug 15 at 14:03

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';
share|improve this answer
    
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 at 14:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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