Can’t connect to MySQL Server情况分析和解决

查看mysql 日志：cat /var/log/mysqld.log查看 mysql 状态：netstat -anp | grep mysqld

确定IP, port可工作： telnet *.*.*.* port (telnet 192.168.233.142 3306)

远程访问mysql:mysql -u root -h *.*.*.* -p

打开https 端口

There are a lot of different reasons behind this error. Sometime, you may not see the exact issue on the error message. You have to dig deeper to identify the exact cause. It may take a lot of time and energy to debug this error. I have encountered this error many times, so based on my experience and research on this issue; I have gathered and given the following possible reasons and workarounds to fix this error.

Error Message:

Can't connect to local MySQL server

Reason 1:MySQL Server is not running on the server.

Workaround 1:Check MySQL server is running on the server.

MySQL process name : mysqldMySQL default port : 3306

On Linux:To check the service status : systemctl status mysqlTo check the process list : ps ax | grep mysqlTo check the listening port : netstat –anp | grep mysql

On Windows:To check the service status : Ctrl + R -> MySQLTo check the process list : Task Manager -> mysqld

If you are trying to connect to MySQL service running on remote server, use telnet.telnet 127.0.0.1 3306 (Replace the IP and Port)

If not running, start the MySQL service and try to connect to MySQL server. If MySQL is not starting, see the error log to find out the reason and fix the error. If this is the reason, the error could have resolved now.

Reason 2:MySQL socket file is missing / deleted / different location.

Workaround:MySQL server can be connected through either socket file or TCP/IP protocol. When you are trying to connect, MySQL service running on localhost, it will be connected through the socket file. If socket file missing, you will get error message with location of the file.

Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2) when trying to connect.

Socket file is a special purpose file, it is used as interface to connect MySQL server from clients. This file is created automatically by the server. If the file is not exists, it is because either of the following issues:

MySQL server was not started.MySQL is created socket file in different location.Socket file unexpectedly removed / deleted /missing.MySQL service is started with socket variable but client trying to connect to default socket.

Example: mysqld –socket=/var/lib/mysqld.sock

If MySQL server is not started:

Use workaround 1 to verify MySQL is running or not. If MySQL service is not running, start the MySQL service by using the following method:

On Linux:

sudo systemctl start mysql

On Windows:

Go to services.msc and start MySQL service.

Note: you can also start the mysql service by other methods too.

If MySQL is created socket file but it is in different location / MySQL service is started with socket variable but client trying to connect to default socket.

You can use mysqladmin utility to check the current location of the MySQL socket file, as below:

mysqladmin -u root -h 127.0.0.1 -p version

When you connect it from client, you can specify the client explicitly using - -socket variable or change the socket file location in configuration file (This required service restart).

[mysqld]socket=/datadir/mysql.sock

If socket file is missing / removed /deleted:

I have seen some cases; socket file is missing for no reason. In this case, connect with TCP / IP protocol to restart the service. Socket, file cannot be created as plain empty file manually. The file should be created by server.

The worst part is you cannot shutdown the server. While stopping the server, it will look for socket file and if it not exists, it will through the socket file missing error. So the alternative method should be as follows:

To shutdown mysql server : mysqladmin –h 127.0.0.1 –protocol=tcp –u root –p shutdownTo login to MySQL server : mysql –h 127.0.0.1 –protocol=tcp –u root –p

Reason 3:Security Enhanced Linux is protecting mysqld process.

Workaround 3:Security Enhanced Linux (SELinux) is a Linux feature that provides mechanism for supporting access control security policies.

In simple words, it does not allow MySQL to access other directories and ports apart from pre-configured access objects. It means, by default, MySQL is configured to access/var/lib/mysql,but when you configured data directory and socket files to different folders, it does not permit access.

You can disable the SELinux protection for mysqld process. This you can done by:

To set SELinux into permissive mode : Setenforce 0 (It will allow but show warnings!)

This settings is applicable still the next server restart. In order to permanently disable the SELinux (private networks), edit the configuration as follows:

To edit the SELinux configuration file : vi /etc/selinux/configTo disable the SELinux protection : SELINUX=disabled (restart required!)

If you wish to configure SELinux protection for mysqld process with proper privileges (production environment), you can referthis pagefor complete details.

Reason 4:

MySQL server is bound to single address (bind_address)

Workaround 4:

To edit bind_address variable : vi /etc/my.cnfTo change bind_address : bind_address=0.0.0.0.0

Here, the problem is, sometimes, by default MySQL bind_address variable is set to localhost or some IP address. When you try to connect MySQL server from other hosts, MySQL does not allow, and provides the error message, since MySQL service is bound to only that bind_address value.

By changing bind_address value into 0.0.0.0 will accept connections from all server hosts.

Reason 5:Firewall blocking MySQL port:

Workaround 5:If the firewall is blocking MySQL port 3306, you can open the port on host machine to fix this issue.

sudo iptables -I INPUT -p tcp --dport 3306 -j ACCEPT

sudo service iptables save

Note: The firewall rules for Windows and latest version of CentOS may vary, use accordingly.

Reason 6:Access restricted to the server hosts.

Workaround 6:There is a possibility that access restricted to server. You can allow access to server by editing the host.allow (Linux) / hosts (Windows file)

To change in Linux : vi /etc/host.allowTo change in Windows : %windows%\system32\drivers\etcTo allow access from all hosts : mysqld: ALL: allow (This is not best practice to allow all – allow only the web server / client hosts allow)

Note: See the TCP wrapper for more details (if you are interested!!!).

Other Possible Reasons:

Incorrect host name / IP address – verify you are giving correct IP address / hostname.The user may not access to database - give access in MySQL using GRANT command.The MySQL variable old_passwords = 1, old_password() is deprecated in MySQL 5.7. It is hashing method to store MySQL password. Setting this variable to 0 will allow you connect MySQL. (See more about this here)The mysqld service unable to create pid file – this issue due to mysql user does not have access to create the file. Once, you given access this issue will be resolved.

I hope this article helps you in fixing this error. Still, if you are unable to fix this issue, please mention in comment section, I will get back to you to fix this error.

Do you know, other workarounds to solve this issue please mention in comment section, so that I will update the answer, it will be helpful to others.