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 just can't seem to figure this one out.

I want to use the command line to connect to a MySQL database residing on another server. I went ahead and created the username and password for the user. I have also granted all privileges on that user for that database.

When using the command: mysql -h <hostname> -u <username> -p, I get the following error:

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

The problem is that it keeps appending the current machine's hostname into the username. Obviously, that user@<local_machine_hostname> is not correct.

It doesn't matter what I type. For instance, if I type:

mysql -h <hostname> -u '<username>'@'<hostname>' -p

It does the same, only in the error output, it says:

Access denied for user '<username>@<hostname>'@'<local_machine_hostname>'

Is there a setting in a configuration file which is allowing this to happen? It's really quite annoying. I need to set up a tikiwiki server, and it cannot connect because during the step where you set up mysql, it keeps appending the local machine's hostname to the mysql login name.

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

MySQL's permissions are based on the host. When you CREATE USER (or GRANT) the user into existence, you provide a host name. It can be '%' or 'localhost' or any other IP or hostname. It's a security feature. You want this to happen. It's not "appending" the host name to the username when it tells you access denied. Each is stored in a different column of the mysql.users table. If you want to be able to login to MySQL from your machine as that user, you'll need to grant that user access from your machine...

From the MySQL docs on CREATE USER:

Each account is named using the same format as for the GRANT statement; 
for example, 'jeffrey'@'localhost'. If you specify only the user name 
part of the account name, a host name part of '%' is used. For additional 
information about specifying account names, see Section 12.4.1.3, “GRANT Syntax”.
share|improve this answer
    
Oh ok. I understand now. So basically, the mysql client must append the @localhost. The MySQL server needs to specifically allow that client, with the client's hostname to connect to it. I also didn't know about the '%' being a wildcard character. Thanks! –  Phanto Jun 8 '10 at 17:34
add comment

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.