How to replace from one server to another?

Asked

Viewed 519 times

2

I have the table below in 2 servers and I need server 1 to send all this information to server 2 ie fields id, username, iddns, dns, ipdnstipo e ipdns 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 from my research but it doesn’t 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

  • this second server has the backup function only ?

  • Hello good night Not to backup because some parts will be changed by another php on the second server

  • I think the best way out here would be a replication system, the following article has an interesting example https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql

  • Thank you very much but already knew the tutorial and not quite that I only want to do the full update of 2 or 3 tables and increase the field in server 2 when inserted in server 1 if possible

  • I think we can improve on this title... It’s too long and confused, I think the previous one was clearer. The statement is the ideal place to put the details, not the title.

  • You have released server 1 IP on server 2 as authorized host to access?

  • Yes the field of connections from one server to another are working smoothly they are included with an include_once and are already serving other files.

  • I’m afraid mysql does not have a Linked server style feature. Maybe the way is to implement in the application.

  • 1

    I managed to solve one of my problems which was a specific field from one server to another as you can see here links now I need the whole table

Show 4 more comments

2 answers

4


First you need to set a primary key to this table otherwise it will be Readonly and Voce may not update this table.

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

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 found that the dns field data is coming with a '.' point at the end... maybe this is some problem... just something I noticed. Anything leave a comment.

  • 1

    Thank you for your explanation that worked perfectly, in relation to iddns yes already this as primary just not this on this post because primarily the question was asked to update and not to replace the points at the end of the domains are purposed and will subsequently be read by bind and so they are already there.

0

Seen in the documentation, I could see that this passing no data to be "inserted", so I imagine that could be like this:

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

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

but if it doesn’t work out, try something like this:

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

remembering that I didn’t take tests, so I don’t know for sure.

  • It doesn’t work @Marcelo Diniz and where he got the variable $trad I don’t understand you.

  • I just copied your code. A váriavel $trad is receiving the value of $row, that’s all. What I had seen in his was that Trad was being passed as a string and not as a variable, so I just made this change, but there was no way I could test it locally. Explain what is happening exactly, what the mistake is giving.

  • Okay in all of this I didn’t realize that $trad was already being treated as string but does not work and running in shell does not give any error.

  • Take a look I changed the answer

  • I can not get based also on this manual [link] (http://www.xoops.net.br/docs/mysql/manual/ch06s04.php) and I can not get there.

Browser other questions tagged mysql select

You are not signed in. Login or sign up in order to post.