Member Avatar for manor

Hello everyone,

I am new to this community..
I have no clue how to go further with the doubt i am going to post.

I am supposed to connect to XYZ(remoteserver) which uses mysql server and fetch data from it through my localhost ABC.

I wrote my PHP script and which is running perfectly fine on localhost. But when i connect it to XYZ it shows an error saying " could not connect to SQL server on XYZ." I asked my boss. He says for many security reasons XYZ is blocked and he asked me to make a hole to get into XYZ from ABC. . I asked my boss to give privileges, he made me the admin for that XYZ server too. despite of that I am unable to connect. Could anyone throw some light on this query of mine that how to go further and get into XYZ server

here is my php script ( which is absolutely running fine on localhost)
<?php

// Database variables
$remoteServer = "XYZ";
$remoteuser = "****";
$remotepass = "******";
$remoteDB = "***";


//connection to the database
$dbhandle = mysql_connect($remoteServer, $remoteuser, $remotepass)
or die("Couldn't connect to SQL Server on $remoteServer");


//select a database to work with
$selected = mysql_select_db($remoteDB, $dbhandle)
or die("Couldn't open database $remoteDB");

//declare the SQL statement that will query the database
$query = "SELECT abc FROM xyz";

//execute the SQL query and return records
$result = mysql_query($query);

$numRows = mysql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";

//display the results
while($row = mysql_fetch_array($result))
{
echo $row["name"];
}

//close the connection
mysql_close($dbhandle);
?>

Awaiting for some help.

Thnx
manor

  • 2 Contributors
  • 9 Replies
  • 124 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by R0bb0b

Dani AI

Generated

Short answer: this is almost always a networking or MySQL-server configuration problem, not a PHP bug. Because your script works locally, focus first on whether ABC can reach XYZ over TCP and whether the MySQL instance on XYZ accepts remote TCP connections and permits your MySQL user/host.

Quick checklist to run (start on the client ABC, then on the server XYZ):

From ABC — test raw connectivity

telnet XYZ 3306
# or
nc -vz XYZ 3306
# or try the mysql client
mysql -h XYZ -u remoteuser -p

On XYZ — check MySQL and OS networking

# is mysqld listening on a TCP socket?
netstat -plnt | grep 3306
# or
ss -ltnp | grep 3306

# check MySQL variables (run as root in mysql)
mysql -u root -p -e "SHOW VARIABLES LIKE 'bind_address'; SHOW VARIABLES LIKE 'skip_networking';"
mysql -u root -p -e "SELECT User,Host FROM mysql.user;"

Common fixes

  • If bind_address = 127.0.0.1 or skip_networking = ON, change my.cnf to allow TCP (bind to the server IP or 0.0.0.0) and restart mysqld.
  • Open/tweak firewall/security-group rules so only ABC's IP can talk to TCP/3306.
  • Create a MySQL account that allows connections from ABC (prefer the specific IP rather than '%'):
    GRANT SELECT ON yourdb.* TO 'remoteuser'@'ABC_IP' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

If opening 3306 is not acceptable, use an SSH tunnel or VPN (as suggested). Example: forward a local port on ABC to XYZ: 

ssh -L 33306:127.0.0.1:3306 user@XYZ -N
# then point your PHP to localhost:33306

Security note: avoid exposing MySQL to the public Internet. Prefer SSH/VPN, limit host-based grants and firewall rules, and enable TLS for client/server connections where possible.

Recommended Answers

All 9 Replies

Member Avatar for R0bb0b 
<?php

// Database variables
$remoteServer = "XYZ";
$remoteuser = "****";
$remotepass = "******";
$remoteDB = "***";


//connection to the database
$dbhandle = mysql_connect($remoteServer, $remoteuser, $remotepass)
or die("Couldn't connect to SQL Server on $remoteServer");


//select a database to work with
$selected = mysql_select_db($remoteDB, $dbhandle)
or die("Couldn't open database $remoteDB");

//declare the SQL statement that will query the database
$query = "SELECT abc FROM xyz";

//execute the SQL query and return records
$result = mysql_query($query);

$numRows = mysql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";

//display the results
while($row = mysql_fetch_array($result))
{
echo $row["name"];
}

//close the connection
mysql_close($dbhandle);
?>
Member Avatar for manor

Thnx Bob.. I will try to do that and will get back to u.. Thnx for quick reply.
But is it legal to dump the remote database to local machine? I know i am sounding silly..

Member Avatar for manor

Hi bob,

As you said to dump the database from remote serevr to local server.. which is oaky if the databse is stagnant. But the database changes every day. So i cant dump the database daily...

please do reciprocate

Thnx, manor

Member Avatar for manor

Thanks Bob.. Hope I should crack it this time :)

Member Avatar for R0bb0b

Sorry, SSH is the only way that I know.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.