DevOps

MySQL error: Can’t connect to MySQL server

You may have stumbled on this MySQL error. If you have already seen this before, then it’s really easy to recognize this and fix it. But if you haven’t seen this error before, then it takes a little longer to troubleshoot the issue. I am listing down the steps to troubleshoot this issue.
 
 
 
 
 
 
 

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

This issue can be caused by the following reasons:

  • MySQL service is not running
  • Port 3306 is not open
  • IP table rules blocking the incoming requests

MySQL service

You can quickly check the current status of the MySQL service by running the following command:

/> sudo service mysql status
/> mysql stop/waiting

If it appears ‘stop/waiting’, then just restart the service and retest your application.

/> sudo service mysql start

MySQL port and IP table rule

If the service is running and you are still not able to connect to remote database box, then port may not be open or MySQL server is listening on different port. Generally MySQL server listens on 3306 port.

/> netstat -nlp --inet

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      26417/mysqld
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      617/sshd
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1079/exim4
udp        0      0 172.26.196.17:123       0.0.0.0:*                           1274/ntpd
udp        0      0 127.0.0.1:123           0.0.0.0:*                           1274/ntpd
udp        0      0 0.0.0.0:123             0.0.0.0:*                           1274/ntpd
udp        0      0 0.0.0.0:161             0.0.0.0:*                           1155/snmpd

If you notice that mysqld is listening on this port then problem may be somewhere else. Next step is to make sure that IP table is allowing the traffic to port 3306.

/> iptables -L

Chain services (1 references)
target     prot opt source               destination
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:mysql

If you get the above line, that means IP rules are correct otherwise you need to add the IP rules to allow the traffic. I generally add the following rule to allow the traffic. This is very generic rule which allows the traffic from anywhere. You can even restrict the source depending on your requirement.

#!/bin/sh

IPTABLES=${IPTABLES:-/sbin/iptables}
CHAIN=${1:-services}
$IPTABLES -A $CHAIN -j ACCEPT -p tcp --dport 3306

After adding the above rule in /etc/iptables.rules.services run the following command and make sure that the rule has been reflected in IP table.

/> /etc/iptables.rules

Start the application again, it should not run into the same issue. If it does throw the same exception, then problem must be somewhere else. Check your application configuration.

Hope above solutions will help you to fix the problem. If you have figured out your own way to fix it, then please share it in the comment section.

Reference: MySQL error: Can’t connect to MySQL server from our JCG partner Rakesh Cusat at the Code4Reference blog.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button