I am trying to use a simple PHP connection to connect remotely to a MySQL database. I have searched many threads and still receiving error ‘Connection failed: Can’t connect to MySQL server on ‘my IP address’ (111 "Connection refused")’
I can access the server remotely using the wan IP address no problem i.e. The software files. I am using port forwarding to do that on port 80.
My connection is very simple
<?php
$servername = "my ip address";
$username = "user";
$password = "password";
$dbname = "company";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, name FROM table";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
I have tried the following;
-
Creating a new user with all privileges in phpMyAdmin and using % as host
-
Allow from all in phpmyadmin.conf and HTTP.conf (don’t think this is the problem)
-
Adding port forwarding on router to 3306
-
Disabled all firewalls on local machine
-
Adding bind-address = 0.0.0.0 to my.ini file (restarting the server)
A lot of thread suggest that the default bind-address is set to localhost or 127.0.0.1 but in my.ini under [mysqld] all I have from default is
default_authentication_plugin=mysql_native_password
port = 3306
I am using WAMP version 3.1.9 64 bit version
2
Answers
After trying everything I set up another WAMP installation on a separate machine and connected to a separate network. I could get it to connect to the remote MYSQL on my original machine. It appears that the PHP script was blocked by cPanel before the request was even sent. The link here https://docs.cpanel.net/knowledge-base/general-systems-administration/how-to-configure-your-firewall-for-cpanel-services/84/ shows that cPanel does not allow outbound connection on port 3306. Unfortunately I can't access settings on my shared hosting to change this.
Separate your concerns.
First, verify that another machine on your local network can access the database directly at the 3306 port. This will verify that the server is working, that the log in is correct, and that the permissions are correct, and that any firewall settings have been addressed.
After all is working correctly, deal with accessing it via WAN. It’s unclear if you’re attempting to do this "from inside the house", ie, using your own external WAN ip address from yourself, there are multiple ways this can fail. It can work, but that’s additional setting/debugging. So ensure you’re dealing with a machine that’s actually outside your network for this section of the testing.
As it stands, somewhat impossible to know where the trouble lies.