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?

Answer 1

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.