php - 如何连接两台服务器中的两个mysql数据库

Question

我想连接两个 mysql 数据库，一个位于本地主机，另一个位于服务器

这是我到目前为止所做的，我没有收到错误或数据

<?php
$con=mysql_connect('120.247.201.8:3306','root','root');
$con1=mysql_connect('localhost','root','');

//mysql_connect('localhost','root','');
if(!$con){
    die('Try Again');
}


if(!$con1){
    die('Try Again');
}

    mysql_select_db("iot",$con1); 
mysql_select_db("lora_gateway",$con);
$result =mysql_query("SELECT lora_gateway.`server_log`.`created_at`, lora_gateway.`server_log`.`temperature`FROM iot.`device` inner JOIN  `lora_gateway`.`server_log` on `lora_gateway`.`server_log`.`gateway_Id` = `iot`.`device`.`gatewayId` where iot.`device`.`deviceId`='23' ORDER BY lora_gateway.`server_log`.`created_at` desc");

$num= mysql_num_rows($result);



print_r($num);
?>

Answer 1

这是针对多个服务器、连接和数据库的解决方案。

• 两个或更多 MySQL 5 数据库服务器
• 两个或更多地点(本地和/或其他任何地点)
• 两个或多个不同的数据库
• 两个或多个表和字段

经过测试，工作正常:-)

//Define your database connections and select your database want to use. In this example I use two connections and two DBs. But you can use more than two.
 
<?php
    //MySQL Server 1
    $dbhost1 = "127.0.0.1";
    $dbuser1 = "dbuser1";
    $dbpassword1 = "dbpass1";
    $db1 = "database1";
    $connection1 = mysql_connect($dbhost1,$dbuser1,$dbpassword1) or die (mysql_error());
    mysql_select_db($db1,$connection1);
     
    //MySQL Server 2
    $dbhost2 = "xxx.xxx.xxx.xxx";
    $dbuser2 = "dbuser2";
    $dbpassword2 = "dbpass2";
    $db2 = "database2";
    $connection2 = mysql_connect($dbhost2,$dbuser2,$dbpassword2) or die (mysql_error());
    mysql_select_db($db2,$connection2);    
     
    //The SQL statement
    $sql =" SELECT database1.tablename1.fieldname1 AS field1, database2.tablename2.fieldname2 AS field2 FROM database1.tablename1,database2.tablename2";
     
    //Execute query and collect results in $results
    $results = mysql_query($sql);
     
    //Print result until end of records
    while($rows = mysql_fetch_array($results)){
        print $rows["field1"]." | ".$rows["field2"]."<br>";
    }
     
    ?>