远程服务器数据库(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命令？
mysqldump -uRemoteusername -pRemotepassword -h192.168.1.19 RemoteDB RemoteTable --set-gtid-purged=OFF | mysql -h192.168.1.35 -uLocalname -pLocalpassword LocalDB
我将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的更多详细信息
