1

MySQL is running on a dedicated Linux Mint Box. I can connect to this box by IP every other way such as shell, Navicat MySQL but just not php. And I am out of ideas. Any help would be greatly appreciated. I have checked and rechecked credentials. I can also connect to mysql from the local mysql box using shell. But when I try and connect to the box from a remote shell using "mysql -uroot -h:199.91.65.82 -p" I get an error again. Same error - "Connection Refused"

See screenshot of firewall rules (ufw)

enter image description here

Gautam Sharma
  • 204
  • 1
  • 3
  • 11

2 Answers2

1

From the manual:

"The error (2003) Can't connect to MySQL server on 'server' (10061) indicates that the network connection has been refused. You should check that there is a MySQL server running, that it has network connections enabled, and that the network port you specified is the one configured on the server."

Almost certainly in your case you need to enable network connections on your mySQL server, via the admin panel.

EDIT: See below for a link. Users have to have appropriate privileges.

Mysql adding user for remote access

You can do this via mySQL workbench or editing the .ini file. You may have to restart the server.

See "Limit to host matching" field which determines or limits from which host the user may connect. enter image description here

bcperth
  • 2,191
  • 1
  • 10
  • 16
  • Well, mysql is listening on port 3306 - based on the netstat -tulnp grep 3306 command I run on the box. And yes, port 3306 is the port as per netstat. Here is the exact output: tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 19560/mysqld – Gautam Sharma Aug 12 '18 at 02:02
  • How do I check if it has network connections enabled? – Gautam Sharma Aug 12 '18 at 02:04
  • No luck; connection refused; – Gautam Sharma Aug 12 '18 at 03:47
  • Seems like its a firewall issue and not a mysql issue since I can connect remotely, just not thru PHP – Gautam Sharma Aug 12 '18 at 03:48
  • 1
    @Gautam Sharma ah OK that is another possibility. I have mySQL on an Ubuntu server, with PHP clients on Windows/PHP and I had to set up user permissions for remote use as indicated, before I could connect. – bcperth Aug 12 '18 at 04:17
  • @Gautam Sharma. I assume you have tried pinging the server from you PHP machine? Even if you fix that part, and the firewall, mySQL may still not connect until set up properly :-) – bcperth Aug 12 '18 at 04:20
  • I am able to connect to the mysql service remotely - when I use Navicat MySQL. I only run into issues when I use php. That is why I feel its not firewall related. But the message "connection Refused" indicates its a firewall issue - and the port 3306 is open. See attached screenshot. I am perplexed at this point. – Gautam Sharma Aug 12 '18 at 05:10
  • I can ping, yes. I can also telnet into the machine at port 22. Not 3306. – Gautam Sharma Aug 12 '18 at 05:12
  • @Gautam Sharma. There are many PHP possibilities and you may be using the mySQL or mySQLi extensions. Also there could be something in the PHP code that is not doing what you think. I would go back to basics and make a few trivial PHP programs just to make connections locally and remotely to try to isolate the issue. We are missing something. Is "connection Refused" a programmed in error message, or system generated? – bcperth Aug 12 '18 at 05:32
  • its system generated. My code is $d=new mysqli("199.91.65.82", "root", $pass, $db, 3306); – Gautam Sharma Aug 12 '18 at 07:50
1

For all of those who care, I fixed the issue.

After failing all traditional means, and realizing the amount of hours wasted, I went ahead and reformatted the db server and re installed all fresh.

Imagine then, my disbelief when upon connecting, I saw the same error - "Connection Refused"

I relectantly then resorted to what I really really avoid doing - a real time port to port packet trace between the web server and db server using shell and tcpdump or similar. The issue was immediately revealed. It wasnt the database server at all (of course not) - my web server that served php pages had an active entry in the firewall - requiring it to reject all outgoing 3306 traffic. Clearly, someone compromised my server.

Yes, connection was being refused. But as in life, nothing is as it seems. The webserver, in hara-kiri mode, was rejecting its own traffic. Lesson learnt.

Gautam Sharma
  • 204
  • 1
  • 3
  • 11