Unable to login to MySQL 5 remotely

Question

Hi,

I'm stuck at the seemingly simple task of connecting remotely to MySQL. I inherited the server, so don't know how it was installed. Took a look at /etc/my.cnf (the only my.cnf on the system as far as I can tell) and found

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

The error I get is

"Unable to connect to host 123.456.789.000 because access was denied. Double-check your username and password and ensure that access from your current location is permitted. MySQL said: Access denied for user 'my_user_name'@'098-765-432-111-mydomain.com' (using password: YES)"

Things I've done: I added bind-address = 0.0.0.0 to my.cnf and restarted the server. No luck.

Telnetted to the server on port 3306. Got mishmash of text back, which tells me the port is accessible.

Stopped the server and tried to connect again (to see what error I would get). Surprisingly, didn't get a timeout - got the same error as above.

Here's what SELECT User, Host, Password FROM mysql.user WHERE user = 'web_app_user';

+--------------+-----------------------+------------------+
| User         | Host                  | Password         |
+--------------+-----------------------+------------------+
| web_app_user | %                     | 54b4540f7c238486 | 
+--------------+-----------------------+------------------+

Answer 1

I've had the same problem long ago, i was caused because my user didn't have the remote right, basically when you log in MySQL you log as username@localhost, but when you are remoting it'll be [email protected], you need to create a user named [email protected] and use the GRANT method to give him the correct right, this can all be done via phpmyadmin but sadly I can't remember where...

Answer 2

A lot of times, a login is limited to be a local connection. You need to take a look at the user permissions and will likely need to add a record allowing that login and password to connect from a remote location.

On my web servers, I always limit the login to being from localhost, for security purposes.