Mysql and PHP - Update database from a different server

Question

I need to connect to a database on host "A" from a host "B".

I have read about how to do it but I can not find the right way. So I wrote this:

$servername = "118.140.84.78"; //host"A" ip
$username = "lpq";
$password = "*****";
$dbname = "cc";

$cc = new PDO("mysql:host=$servername;dbname=$dbname", "$username", "$password");
$cc->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

But when I try to connect I got this error:

Caught exception: SQLSTATE[HY000] [2013] Lost connection to MySQL server at 'reading initial communication packet', system error: 0

The privileges of my database look like this:

The ip there is the ip of host "B"

Any idea?

Might be [a firewall issue](http://stackoverflow.com/questions/5755819/lost-connection-to-mysql-server-at-reading-initial-communication-packet-syste). — Sergey Vidusov, Feb 24 '16 at 06:55
Does connection on that IP is open, I mean there is no issue in that IP. Please check it once — Nehal, Feb 24 '16 at 07:22
it might be the port you are tring to connect. You can set port on PDO like this: `$pdo = new PDO('mysql:host=118.140.84.78;port=$newPortNumber;dbname=$dbname', '$username', '$password');` — Atilla Arda Açıkgöz, Feb 25 '16 at 14:43

score 1 · Accepted Answer · answered Feb 29 '16 at 16:45

One solution to this issue might be rather than to establish a direct connection from server B to the MySQL database on server A, to implement a secure API on server A to run all database queries locally and have server B use the API to run queries.

For example on server A you could have saved in api.php something similar to:

<?php
/* Configuration */
define( 'DB_HOSTNAME', '127.0.0.1' );
define( 'DB_USERNAME', 'dbuser' );
define( 'DB_PASSWORD', 'dbpass' );
define( 'DB_DATABASE', 'dbname' );

/* Process JSON request */
$aRequest = (array)json_decode( file_get_contents( "php://input" ));
if( isset( $aRequest['query'] ) && isset( $aRequest['params'] )) {

  /* Connect to database and run requested query */    
  try {
    $oPDO = new PDO( sprintf( 'mysql:host=%s;dbname=%s', DB_HOSTNAME, DB_DATABASE ),
      DB_USERNAME, DB_PASSWORD );
    $oPDO->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $hStatement = $oPDO->prepare( (string)$aRequest['query'] );
    $hStatement->setFetchMode( PDO::FETCH_ASSOC );
    $hStatement->execute( (array)$aRequest['params'] );
    $aResponse = array( 'success' => true, 'data' => (array)$hStatement->fetchAll());
  } catch( PDOException $oError ) {
    $aResponse = array( 'success' => false, 'error' => (string)$oError->errorInfo[2] );
  }
} else {
  $aResponse = array( 'success' => false, 'error' => 'Invalid request' );
}

/* Process JSON response */
header( 'Content-Type: application/json' );
echo( json_encode( $aResponse ));

And then on server B you could launch your queries using something similar to:

<?php
/* Configuration */
define( 'PATH_API', 'http://118.140.84.78/api.php' );

class RemotePDO {
  private $sURL = '';

  function __construct( $sURL ) {
    $this->sURL = $sURL;
  }

  function exec( $sQuery, $aParams ) {
    $sRequest = json_encode( array( 'query' => $sQuery, 'params' => $aParams ));
    $aHttpOptions = array( 'http' => array( 'header' => 
      "Content-Type: application/json", 'method' => 'POST', 'content' => $sRequest ));
    $oHttpContext = stream_context_create( $aHttpOptions );
    return json_decode( @file_get_contents( $this->sURL, false, $oHttpContext ));
  }
}

/* Testing */
$sSQL = "SELECT * FROM orders WHERE order_id=:order_id";
$aParams = array( ':order_id' => 1 );
$oRemotePDO = new RemotePDO( PATH_API );
print_r( $oRemotePDO->exec( $sSQL, $aParams ));

Important Note: Obviously this implementation is not production-ready and would need some significant improvements in areas such as validation checking, security (encryption), expanding the PDO implementation etc. This example code is provided to demonstrate the API concept.

hey man, thanks for your help, well is not the aswer but is still working so will be really useful!! — Tomas Lucena, Mar 01 '16 at 04:41

Mysql and PHP - Update database from a different server

1 Answers1