PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2002] Connection refused

Question

I have a different server for my database.

When I connected to database with PDO, I used the IP address of database VM for host parameter:

$dbh = new pdo("mysql:host=192.168.56.103;port=3306;dbname=data",'root','root')

But I get connection refused (PDOException' with message 'SQLSTATE[HY000] [2002]'). I guess it couldn't find MySQL there. It could however, find the VM with that IP.

Answer 1

Well then check if mysql is really not running:

sudo aptitude install nmap

And then:

sudo nmap -sS <ipofdbserverhere>

You can also install the tool on the mysql server, the the command would be:

sudo nmap -sS localhost

In both cases your answer should look like this.

[simmel]@[mars]$ sudo nmap -sS 172.16.1.41

Starting Nmap 6.40 ( http://nmap.org ) at 2015-04-08 15:09 CEST

Nmap scan report for 172.16.1.41
Host is up (0.010s latency).
Not shown: 996 closed ports
PORT     STATE SERVICE
3306/tcp open  mysql

Nmap done: 1 IP address (1 host up) scanned in 33.17 seconds

If it shows you port 3306, it's indeed open and ready for connections. If that is the case use mysql via command line from your computer.

mysql -u<USER> -p<PASSWORD> -h<ipofdbserverhere> <databasename>

The same message appears? Connection refused? Then your user has probably the wrong rights.

Another reason for this is when the mysql server is bound to a certain address, like 127.0.0.1.

To find out check the output from:

sudo netstat -tlpen

If mysql is not listening to 0.0.0.0:3306 but to 127.0.0.1:3306, you only can connect from localhost.

To change that go to /etc/mysql/my.cnf and comment out the following line:

bind-address            = 127.0.0.1

The way to comment it out is to add a leading # character:

#bind-address            = 127.0.0.1

Save the file and restart the mysql service

sudo service mysql restart

Now you should be able to connect.

To change the port, enter, for example:

port = 8888

in /etc/mysql/my.cnf.

Save the file and restart the mysql service:

sudo service mysql restart

Answer 2

The error 2002 means that MySQL can't connect to local database server through the socket file (e.g. /tmp/mysql.sock).

To find out where is your socket file, run:

mysql_config --socket

then double check that your application uses the right Unix socket file or connect through the TCP/IP port instead.

Test the socket:

mysql --socket=/var/mysql/mysql.sock

If the Unix socket is wrong, you may symlink it, for example:

ln -vs /Applications/MAMP/tmp/mysql/mysql.sock /var/mysql/mysql.sock

or correct your configuration file (e.g. php.ini).

To test the PDO connection directly from PHP, you may run:

php -r "new PDO('mysql:host=localhost;port=3306;charset=utf8;dbname=dbname', 'root', 'root');"

Check also the configuration between Apache and CLI (command-line interface), as the configuration can be differ.

It might be that the server is running, but you are trying to connect using a TCP/IP port, named pipe, or Unix socket file different from the one on which the server is listening. To correct that you need to invoke a client program (e.g. specifying --port option) to indicate the proper port number, or the proper named pipe or Unix socket file (e.g. --socket option).

See: Troubleshooting Problems Connecting to MySQL

---

Other utils/commands which can help to track the problem:

• netstat -ln | grep mysql
• php -r "phpinfo();" | grep mysql
• php -i | grep mysql
• Use XDebug with xdebug.show_exception_trace=1 in your xdebug.ini
• On OS X try sudo dtruss -fn mysqld, on Linux debug with strace
• Check permissions on Unix socket: stat $(mysql_config --socket) and if you've enough free space (df -h).
• Restart your MySQL.
• Check net.core.somaxconn.