How to do a replace into from one server to another?

Question

How to do a replace into from one server to another?

Navigation

#1 by (4 votes)
#2 by (0 votes)

2

I have the table below on 2 servers and I need server 1 to send all this information to server 2 ie the id, username, iddns, dns, ipdnstipo e ipdns fields as it is.

Table

CREATE TABLE IF NOT EXISTS 'trad' (
    'id' int(11) NOT NULL,
    'username' varchar(50) NOT NULL,
    'iddns' int(11) NOT NULL,
    'dns' varchar(255) DEFAULT NULL,
    'ipdnstipo' varchar(10) DEFAULT NULL,
    'ipdns' varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO 'trad' ('id', 'username', 'iddns', 'dns', 'ipdnstipo', 'ipdns') VALUES
    (1, 'master', 0, 'exemplo.com.', 'A', '10.0.0.101'),
    (1, 'master', 1, 'dns1.exemplo.com.', 'A', '10.0.0.101'),
    (1, 'master', 2, 'dns2.exemplo.com.', 'A', '10.0.0.102'),
    (1, 'master', 3, 'dns3.exemplo.com.', 'A', '10.0.0.103'),
    (1, 'master', 4, 'dns4.exemplo.com.', 'A', '10.0.0.101'),
    (1, 'master', 5, 'dns5.exemplo.com.', 'A', '10.0.0.102'),
    (1, 'master', 6, 'dns6.exemplo.com.', 'A', '10.0.0.103'),
    (1, 'master', 7, 'dns7.exemplo.com.', 'A', '10.0.0.101'),
    (1, 'master', 8, 'dns8.exemplo.com.', 'A', '10.0.0.102'),
    (1, 'master', 9, 'dns9.exemplo.com.', 'A', '10.0.0.103'),
    (1, 'master', 100, 'exemplo1.com.', 'A', '10.0.0.101'),
    (1, 'master', 1000, 'exemplo1.com.', 'A', '10.0.0.102'),
    (1, 'master', 10000, 'exemplo1.com.', 'A', '10.0.0.103'),
    (1, 'master', 101, 'dns1.exemplo1.com.', 'A', '10.0.0.101'),
    (1, 'master', 102, 'dns2.exemplo1.com.', 'A', '10.0.0.102'),
    (1, 'master', 103, 'dns3.exemplo1.com.', 'A', '10.0.0.103'),
    (1, 'master', 200, 'exemplo2.com.', 'A', '10.0.0.101'),
    (1, 'master', 2000, 'exemplo2.com.', 'A', '10.0.0.102'),
    (1, 'master', 20000, 'exemplo2.com.', 'A', '10.0.0.103'),
    (1, 'master', 201, 'dns1.exemplo2.com.', 'A', '10.0.0.101'),
    (1, 'master', 202, 'dns2.exemplo2.com.', 'A', '10.0.0.102'),
    (1, 'master', 203, 'dns3.exemplo2.com.', 'A', '10.0.0.103');

I only got this with my searches but it does not work.

<?PHP
$db_host1     = "10.0.0.101";
$db_user1     = "dns";
$db_password1 = "123456";
$db_name1     = "dns";
$db_connect1  = mysqli_connect($db_host1, $db_user1, $db_password1, $db_name1);
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$db_host2     = "10.0.0.102";
$db_user2     = "dns";
$db_password2 = "123456";
$db_name2     = "dns";
$db_connect2  = mysqli_connect($db_host2, $db_user2, $db_password2, $db_name2);
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query(
    $db_connect1,
    "SELECT * FROM trad"
);

$rows = mysqli_fetch_array($result,MYSQLI_NUM);

foreach ($rows as $row) {
    $trad = $row;
    mysqli_query(
        $db_connect2,
        "REPLACE INTO trad"
    );
}
mysqli_close($db_connect1);
mysqli_close($db_connect2);
?>

mysql select

asked by anonymous 05.09.2014 / 21:48

2 answers

0

Seen in documentation , I could see that no data is being passed to be " inserted ", so I imagine it could look like this:

$result = mysqli_query(
    $db_connect1,
    "SELECT * FROM trad"
);

mysqli_query(
    $db_connect2,
    "REPLACE INTO trad {$result}"
);

But if it does not work, try something like this:

foreach ($rows as $row) {
    $trad = $row;
    mysqli_query(
        $db_connect2,
        "REPLACE INTO trad {$trad}"
    );
}

Remembering that I did not test, then I'm not sure.

18.09.2014 / 15:46

How to disable JqueryDatatables auto search? Select with joins return last result by date

score 4 · Accepted Answer

First you need to set a primary key to this table otherwise it will be ReadOnly and you will not be able to perform any updates on this table.

In the case I set the IDDNS as PK for testing being the only field that its value is different in each row.

ALTER TABLE 'dns'.'trad' 
ADD PRIMARY KEY ('iddns');

The correct would be the ID, but it is 1 in all rows ...

Code working and tested locally:

<?PHP
$db_host1     = "10.0.0.101";
$db_user1     = "dns";
$db_password1 = "123456";
$db_name1     = "dns";
$db_connect1  = mysqli_connect($db_host1, $db_user1, $db_password1, $db_name1);
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$db_host2     = "10.0.0.102";
$db_user2     = "dns";
$db_password2 = "123456";
$db_name2     = "dns";
$db_connect2  = mysqli_connect($db_host2, $db_user2, $db_password2, $db_name2);
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($db_connect1,"SELECT * FROM trad");

while ($teste = mysqli_fetch_array($result))
{ 
    //echo "REPLACE INTO trad VALUES(".$teste['id'].", '".$teste['username']."', ".$teste['iddns'].", '".$teste['dns']."', '".$teste['ipdnstipo']."', '".$teste['ipdns']."') \n";
    mysqli_query($db_connect2,"REPLACE INTO trad VALUES(".$teste['id'].", '".$teste['username']."', ".$teste['iddns'].", '".$teste['dns']."', '".$teste['ipdnstipo']."', '".$teste['ipdns']."')");
}

mysqli_close($db_connect1);
mysqli_close($db_connect2);
?>

I also checked that the data in the dns field is coming with a '.' in the end ... maybe that's a problem ... just something I noticed. Anything leave a comment.