Geek Answers Handbook

Mysql and PHP - updating a database from another server

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

I read about how to do this, but I cannot find the right way. So I wrote the following:

$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:

Exception fixed: SQLSTATE [HY000] [2013] Lost connection with MySQL server while "reading the source communication packet", system error: 0

My database privileges are as follows:

enter image description here

In ip there is a host ip "B"

Any idea?

+6
source share
1 answer

One solution to this problem might be rather not to connect a direct connection to server B with the MySQL database on server A in order to implement a secure API on server A to run all database queries locally, and server B to use the API to start queries.

For example, on server A, you could save 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 can run 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 ready for production and will require significant improvements in areas such as validation verification, protection (encryption), expansion of PDO implementation, etc. This sample code is provided to demonstrate the concept of an API.

+1
source

More articles:


All Articles