How to copy table from one table on remote server to table on a different remote db m

[Underwood]

How to copy table from one table on remote server to table on a different remote db mysql5.6 with PDO


I have matching databases on 2 different servers (mysql replication is not an option). I need to see if a record is missing from one table2 and if so truncate table2 and then copy from table1 to table2. each table is on a different IP/server.

[Uniopolis]

You can open two connections. Use one to read from the source server, the other two insert into the destination server. Use the ON DUPLICATE KEY IGNORE option to prevent errors when you try to overwrite existing rows, so it only inserts the missing rows.


$pdo1 = new PDO('mysql:host=server1;dbname=xxx', $username1, $password1);
$pdo2 = new PDO('mysql:host=servrer2; dbname=xxx', $username2, $password2);

$insert_stmt = $pdo2->prepare("INSERT INTO yourTable (col1, col2, col3, ...) VALUES (:col1, :col2, :col3, ...) ON DUPLICATE KEY IGNORE");
$select_results = $pdo1->query("SELECT * FROM yourTable");
while ($row = $select_results->fetch(PDO::FETCH_ASSOC)) {
$insert_stmt->execute($row);
}