MySql: can connect normally to local db, can connect via TCP remotely, but cannot connect via TCP locally

Question

Host A is running a mysql server. On A, I can connect to the db:

hostA: mysql -p

I have allowed user X from host B to connect. I am able to connect via this:

hostB: mysql -h A --protocol=TCP -p -u X

However, I cannot connect to A from A via TCP:

hostA: mysql -h 127.0.0.1 --protocol=tcp -p

It says:

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

I've tried the following addresses:

localhost, 127.0.0.1, <hostname>, <actual ip>

The bind-address in my.cnf is the actual IP address.

Any thoughts? Can anyone suggest further diagnostics?

Answer 1

Your command:

hostA: mysql -h 127.0.0.1 --protocol=tcp -p

means to connect to the server over TCP socket using the loopack IP address 127.0.0.1.

It seems that your mysql daemon (mysqld) is listening on the IP address of one of the interfaces. You can confirm that by looking at my.cnf file:

bind-address  = your_server_ip_addr

and using:

$ sudo netstat -lnp | grep mysql

If you specify the bind-address as:

1. 0.0.0.0: listen on all interfaces, or
2. 127.0.0.1: listen locally only

your command should work.

To fix the problem, you need either to change your command to be like:

mysql -h your_sever_ip_addr --protocol=tcp -p

or change the bind-address appropriately.

Answer 2

comment out line with bind-address in your my.cfn file restart deamon and try then After that mysqld should listen on all interfaces

#bind-address=...

Answer 3

mysqld has the very nasty habit of routing TCP/IP DB connections via 127.0.0.1 port 3306 into the socket file.

To see this, just login to mysql and run this command:

mysql> SELECT USER(),CURRENT_USER();

The USER() function tells you how you tried to authenticate

The CURRENT_USER() function tells you how mysqld allowed you to authenticate

If you see root@localhost for CURRENT_USER(), then TCP/IP is not being used.

@Khaled already mentioned doing this:

mysql -h your_sever_ip_addr --protocol=tcp -p

Here is something for you to research

Run this command

SELECT CONCAT(user,'@',host) RootUser,password FROM mysql.user WHERE user='root';

You should already have root@localhost. You should also have root@127.0.0.1 with either the same password as root@localhost or a different one if you so choose.

If you do not have root@127.0.0.1, here is how to create root@127.0.0.1 with the same password as root@localhost:

CREATE TABLE mysql.roothome LIKE mysql.user;
INSERT INTO mysql.roothome SELECT * FROM mysql.user
WHERE user='root' AND host='localhost';
UPDATE mysql.roothome SET host='127.0.0.1';
INSERT INTO mysql.user SELECT * FROM mysql.roothome;
DROP TABLE mysql.roothome;
FLUSH PRIVILEGES;

Here is how to create root@127.0.0.1 with a different password from root@localhost:

CREATE TABLE mysql.roothome LIKE mysql.user;
INSERT INTO mysql.roothome SELECT * FROM mysql.user
WHERE user='root' AND host='localhost';
UPDATE mysql.roothome SET host='127.0.0.1',password=password('whateveryouwant');
INSERT INTO mysql.user SELECT * FROM mysql.roothome;
DROP TABLE mysql.roothome;
FLUSH PRIVILEGES;

Give it a Try !!!