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

connectionMySQL

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?

Best Answer

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:

0.0.0.0: listen on all interfaces, or
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.

Related Solutions

Mysql – Cannot connect to MySQL over TCP locally – Connection Timeout – Ubuntu 9.04

are you absolutely sure the is no iptables rule preventing tcp/3306 traffic? try this as temporary workaround:

iptables -I INPUT -p tcp --dport 3306 -i lo -j ACCEPT
iptables -A OUTPUT -p tcp --sport 3306 -o lo -j ACCEPT

or maybe your NAT rules to a little bit more than you expect them to do?

ok - your loopback is not only down, but does not have 127.0.0.1 bound to it. add to /etc/network/interfaces:

auto lo
iface lo inet loopback

and.. why on earth did you bound it to vnet0? remove it from there.

quick one-time cmd line fix:

ip a d 127.0.0.1/32 dev venet0
ip a a 127.0.0.1/32 dev lo
ip link set dev lo up

Ubuntu – Can not connect remotely to MySQL Server on Ubuntu 10.10

Check the bind-address in /etc/mysql/my.cnf as it is set to 127.0.0.1 by default on Debian-based systems. If you want to be able to connect to it from another machine besides localhost you will need to change this and restart mysql to do so.

You can confirm this is the case by running netstat -plunta |grep :3306 as root and you will likely see it bound only to 127.0.0.1 and when you modify the my.cnf and restart this will not be the case any more.

Best Answer

Related Solutions

Mysql – Cannot connect to MySQL over TCP locally – Connection Timeout – Ubuntu 9.04

Ubuntu – Can not connect remotely to MySQL Server on Ubuntu 10.10

Related Topic