如何将MySQL远程数据库表插入本地数据库表？

Question

远程服务器数据库(IP:192.168.1.19)

数据库名称:remotedb

表名 · · · · · · :remotetable

本地服务器数据库(IP:192.168.1.35)

数据库名称:localdb

表名 · · · · · · :localtable

MySQL查询

INSERT INTO LocalDB.LocalTable SELECT * FROM RemoteDB.RemoteTable

但是，由于数据库连接不同，此查询没有执行。

如何从本地服务器(IP:192.168.1.35)执行此查询？或者任何linux shell命令？

Answer 1

我将CronJob的批准答案转换为PHP脚本，如下所示-

配置：

$remoteDbUser   = '***';
$remoteDbPass   = '***';
$remoteDbHost   = '192.168.1.19';
$remoteDb       = 'RemoteDB';
#$remoteDbTable = '';
$localDbHost    = '192.168.1.35';
$localDbUser    = '***';
$localDbPass    = '***';
$localDb        = 'LocalDB';

同步功能：

/*Passing `$remoteDbTable` name as function argument, Because I have many Remote DB table to synchronize with my Local DB table*/
function RemoteDbSynchronze($remoteDbTable='RemoteTable'){
  global $remoteDbUser,
    $remoteDbPass,
    $remoteDbHost,
    $remoteDb,
    $localDbHost,
    $localDbUser,
    $localDbPass,
    $localDb;
  $sql = sprintf(
    "mysqldump -u%s -p%s -h%s %s %s  --set-gtid-purged=OFF  | mysql -h%s -u%s -p%s %s",
    $remoteDbUser,
    $remoteDbPass,
    $remoteDbHost,
    $remoteDb,
    $remoteDbTable,
    $localDbHost,
    $localDbUser,
    $localDbPass,
    $localDb
  );
  shell_exec($sql);
}

从Cron脚本调用函数：

RemoteDbSynchronze('RemoteTable');
RemoteDbSynchronze('AnotherRemoteTable1');
RemoteDbSynchronze('AnotherRemoteTable2');
..........................................
RemoteDbSynchronze('AnotherRemoteTableN');

有关sprintf、shell_exec和Cron的更多详细信息

Answer 2

mysqldump -uRemoteusername -pRemotepassword -h192.168.1.19 RemoteDB RemoteTable  --set-gtid-purged=OFF  | mysql -h192.168.1.35 -uLocalname -pLocalpassword   LocalDB