Mysql – Unable to connect to a local MYSQL server on wireles LAN

connectionlocal-area-networkMySQLwifi

Ok, Here is the technical description.
My laptop's config:
Ip Adress:192.168.2.5
Mysqlserver 5.0 on port : 3306
Operating system: Ubuntu jaunty (9.04)
3306 is open for both incoming and outgoing.

My friend's laptop config:
Ip Adress:192.168.2.4
Mysqlserver 5.0 on port : 3306
Operating system: Windows XP pro
3306 is open for both incoming and outgoing.

Both are on a wireless LAN connected through a belkin router (192.168.2.1)

Both the MYSQL servers have been given the sufficient GRANT privileges. I am also able to connect from 192.168.2.4 to 192.168.2.5's MYSQL instance but the vice versa is not happening. I am getting an (100061) error. Tried Telnetting on 3306; again happening from 192.168.2.4 to 192.168.2.5 but not the vice versa. Am I doing something wrong?
Kindly suggest.

Best Answer

most likely Mysql on Jaunty does not listen for external addresses or is not configured to accept connections from external addresses.

Can you telnet 192.168.2.4 3306 port from your Jaunty laptop?

You should check /etc/mysql/my.cnf bind-address variable and make sure to comment it out as well as skip-networking.

#skip-networking
#bind-address

Or bind address to all network interfaces

bind-address = 0.0.0.0

Related Solutions

MySQL Configuration – How to Bind MySQL Server to Multiple IP Addresses

No, there isn't (I just checked 1 hour ago). You can comment the bind-address in my.cnf:

Note: « 1 hour ago » is now more than 10 years ago.

#skip-networking
#bind-address                   = 127.0.0.1

If you want only 2 IPs, you will then have to use a firewall.

For MySql version 8.0.13 and above, you can specify a list of comma-separated IP addresses.

bind-address = 10.0.0.1,10.0.1.1,10.0.2.1

Relevant MySql documentation.

Remember to restart your MySQL instance after changing the config file.

MySQL – How to Fix Can’t Connect to Local MySQL Server Through Socket

Check the bind-address in your my.cnf. It should be 0.0.0.0 in order to allow both local and remote connections.

Check with ps xa | grep mysql how many instances are run.

Edit
Apparanently:

A MySQL client on Unix can connect to the mysqld server in two different ways: By using a Unix socket file to connect through a file in the file system (default /tmp/mysql.sock), or by using TCP/IP, which connects through a port number. A Unix socket file connection is faster than TCP/IP, but can be used only when connecting to a server on the same computer. A Unix socket file is used if you don't specify a host name or if you specify the special host name localhost.

Check here for some useful tip.

Edit2
One of possible reasons of Can't connect to local MySQL server, that seems to be the problem here:

Someone has removed the Unix socket file that mysqld uses (/tmp/mysql.sock by default). For example, you might have a cron job that removes old files from the /tmp directory. You can always run mysqladmin version to check whether the Unix socket file that mysqladmin is trying to use really exists. The fix in this case is to change the cron job to not remove mysql.sock or to place the socket file somewhere else. See Section B.5.4.5, “How to Protect or Change the MySQL Unix Socket File”.

Martin, make sure you have socket = /var/run/mysqld/mysqld.sock in my.cnf, I believe mysqld restart will solve your problem, it should recreate mysqld.sock and you should be able to connect locally.

Best Answer

Related Solutions

MySQL Configuration – How to Bind MySQL Server to Multiple IP Addresses

MySQL – How to Fix Can’t Connect to Local MySQL Server Through Socket

Related Topic