MySQL command appends '@localhost' to username

Question

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.

Solution

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

Comments

[reowil]

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!