Please bear with me as I am kind of sluggish with PHP
//Connect to both databases, which are set up the same
$localDB = new mysqli("localhost", $username, $password, "db");
$remoteDB = new mysqli("remotehost.com", $username, $password, "db");
$maxDate = //some timestamp in the past
//Set up query to get the data
$getQuery = "SELECT message FROM `log` WHERE timestamp < ?";
$getStmt = $remoteDB->prepare($getQuery);
$getStmt->bind_param('i',$maxDate);
$getStmt->execute();
$getStmt->bind_result($message);
//Set up query to insert the data 20 at a time
$commitCount = 0;
$commitLimit = 20;
$putQueryPrefix = "INSERT INTO `log` (message) VALUES ";
$putQuerySuffix = "";
$comma = "";
//Read many rows and insert them in bulk
while ($getStmt->fetch()) {
$putQuerySuffix .= "$comma('$message')";
$comma = ",";
$commitCount++;
if ( $commitCount == $commitLimit ) {
$putQuery = $putQueryPrefix . $putQuerySuffix;
$putStmt = $localDB->prepare($putQuery);
$putStmt->execute();
$commitCount = 0;
$putQuerySuffix = "";
$comma = "";
}
}
if ( $commitCount > 0 ) {
$putQuery = $putQueryPrefix . $putQuerySuffix;
$putStmt = $localDB->prepare($putQuery);
$putStmt->execute();
}
//And we're done
$getStmt->close();
$putStmt->close();
//Now delete it from the old server
$result = $remoteDB->query("DELETE FROM `log` WHERE timestamp < $maxDate");
$result->free();
$remoteDB->close();
$localDB->close();
The general idea is this: mysqldump preps multiple rows for a single INSERT statement. This is known as an extended INSERT. My example shows how to create a group of 20 rows to perform such an INSERT. If you want to increase $commitLimit
to something greater than 20, you may have to increase max_allowed_packet on the Remote Server.