使用PHP将表内容从localserver传输到远程服务器DB的代码 [英] Code for transfering the table content from localserver to remote server DB using PHP

问题描述

<?php
$dbhost1 = "10.0.11.211";
$dbuser1 = "root";
$dbpassword1 = "";
$db1 = "product";
$connection1 = mysqli_connect($dbhost1, $dbuser1, $dbpassword1) or die (mysqli_error());
mysqli_select_db($connection1, $db1);

//MySQL Server 2
$dbhost2 = "localhost";
$dbuser2 = "root";
$dbpassword2 = "";
$db2 = "product";
$connection2 = mysqli_connect($dbhost2, $dbuser2, $dbpassword2) or die (mysqli_error());
mysqli_select_db($connection2, $db2);
$sql = "insert into 10.0.11.211.product.inserting select * from localhost.product.inserting";
$result = mysqli_query($connection1, $sql);
if (!$result) {
    echo "connection error";
}
?>

如何纠正此代码?实际上，我当时已连接到服务器.

How can I correct this code? Actually I connected to servers at time.

推荐答案

您可以设置联合表，它基本上是将一台服务器上的表链接到另一台服务器上的表.然后使用联合进行数据传输.

You can setup federated tables, which is basically linking a table on one server to a table on another. Then use the federation to do your data transfers.

首先，您必须在要远程服务器上有一个表 通过使用FEDERATED表进行访问.假设远程表在 联合数据库，其定义如下:

First, you must have a table on the remote server that you want to access by using a FEDERATED table. Suppose that the remote table is in the federated database and is defined like this:

您需要根据您的详细信息更改数据库主机和数据库/表名称

You need to change database host and database/table name as per your details

CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;
Next, create a FEDERATED table on the local server for accessing the remote table:
CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://root@10.0.11.211:9306/federated/test_table';

然后，您可以像查询其他任何表一样查询它.

Then you can query it like any other table.

但是限制您应该阅读有关以纯文本格式存储远程密码的信息.如果这只是一个一次性复制的临时设置，并且服务器不对公众开放，那么您已经将与之相关的大多数风险降到了最低.

There are however a decent number of limitations you should read about including the remote password being stored in plain text. If this was a temporary setup purely for a once off copy, and the server isn't available to the public you have already minimised most of the risk associated with it though.

这篇关于使用PHP将表内容从localserver传输到远程服务器DB的代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！