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.

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?

share|improve this question
    
what is the bind-address in your /etc/mysql/my.cnf ?? –  B14D3 Jan 5 '12 at 12:53
    
updated with this info (it's the actual IP address) –  muckabout Jan 5 '12 at 13:01

3 Answers 3

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.

share|improve this answer
    
I tried the latter, and my error is: ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0 –  muckabout Jan 5 '12 at 14:16
    
What have you changed? –  Khaled Jan 5 '12 at 14:21
    
@muckabout did you tried to comment out this line ? –  B14D3 Jan 5 '12 at 14:21

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

share|improve this answer

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 !!!

share|improve this answer

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.