PHP remote connection on live is not working

Question

I am trying to access remote MySQL server by its IP using PHP.

Here is my code:

$servername = "100.XXX.XXX.XXX:3306"; // MySQL IP
$username = "root";
$password = "Password012";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} else {
    echo "success";
}

This is working fine on my local machine but not on live.

I am getting this error:

Warning: mysqli::mysqli(): (HY000/2002): Connection refused in [...]

Any suggestions?

Please paste the result of `SHOW GRANTS FOR 'root'@'localhost';` — Michał Szczech, Feb 28 '18 at 10:43
local machine port is different from server. some times port number 3306 use other resource. check port 3306 — Bilal Ahmed, Feb 28 '18 at 10:43
When testing on local machine are you connecting to remote MySQL? — lolbas, Feb 28 '18 at 10:45
@BilalAhmed - _"local machine port is different from server"_ - What? How do you know that? — M. Eriksson, Feb 28 '18 at 10:45
@MichałSzczech' GRANT ALL PRIVILEGES ON . TO \'root\'@\'%\' IDENTIFIED BY PASSWORD \'*glogo9006906906090504040409409449595\' WITH GRANT OPTION' — Hitu Bansal, Feb 28 '18 at 10:47
Please check with the firewall of remote server. Also check if the port 3306 is open for connection. (I wanted to add this as comment, however not able to do it due to less reputations) — Ashwin Geet D'Sa, Feb 28 '18 at 10:42
this is working fine on my local machine. do you want me to check on my live? — Hitu Bansal, Feb 28 '18 at 10:43

score 0 · Answer 1 · answered Feb 28 '18 at 10:46

0

You need to allow remote connection on your mysql server.

To do that, execute the following command in your mysql server. Please change the databasename and username from the following command, before execute the command

GRANT ALL PRIVILEGES ON databasename . * TO 'username'@'%';

answered Feb 28 '18 at 10:46

Iynga Iyngaran Iyathurai

796
1
9
14

1

No no no. Only grant the privileges that the user actual needs. Also, open it up to _everyone_ (`%`) is a very _bad_ idea. You should only open it up to the IP's that need it. – M. Eriksson Feb 28 '18 at 10:48
@MagnusEriksson Can we open using using query for particular IP ( PHP Server IP) – Hitu Bansal Feb 28 '18 at 10:49
Yes, I agree with you guys – Iynga Iyngaran Iyathurai Feb 28 '18 at 10:49
@HituBansal Yes. When adding a user, you can define what hosts are allowed to login with that user. `username@1.2.3.4` for example. – M. Eriksson Feb 28 '18 at 10:51
1

in that case, It will be like this GRANT SELECT, INSERT, DELETE ON ON databasename . * TO 'username'@'your-ip-address'; – Iynga Iyngaran Iyathurai Feb 28 '18 at 10:51

PHP remote connection on live is not working

1 Answers1