Well shaped script, you can try using
CREATE TABLE ... LIKE syntax repeated through a list of tables, which you can get from SHOW TABLES .
The only problem is that indexes or foreign keys are not recreated initially. Therefore, you will have to list them and create them. Then a few INSERT ... SELECT queries for data entry.
If your schema never changes, only data. Then create a script that replicates the structure of the table, and then just execute the INSERT ... SELECT business in the transaction.
Otherwise, mysqldump , as others say, is pretty easy to get work from a script. I have a daily cron job that dumps all databases from my data center servers, connects via FTPS to my location and sends all dumps. This can be done quite efficiently. Obviously, you have to make sure that such objects are locked, but again, not too hard.
According to the code request
The code is proprietary, but I will show you the critical section that you need. This is from the middle of the foreach , so the continue statements and the $c.. prefixes (I use this to indicate the variables of the current loop (or instance)). The echo can be whatever you want, this is a cron script, so repeating the current state was appropriate. The flush() lines are useful when running a script in a browser, since the output will be sent to this point, so the browser results will be populated as they run, and not all at the end. The ftp_fput() obviously comes down to my situation of loading a dump somewhere and loading directly from a pipe - you can use another process open to output output to the mysql process for database replication. Providing appropriate amendments, if any.
$cDumpCmd = $mysqlDumpPath . ' -h' . $dbServer . ' -u' . escapeshellarg($cDBUser) . ' -p' . escapeshellarg($cDBPassword) . ' ' . $cDatabase . (!empty($dumpCommandOptions) ? ' ' . $dumpCommandOptions : ''); $cPipeDesc = array(0 => array('pipe', 'r'), 1 => array('pipe', 'w'), 2 => array('pipe', 'w')); $cPipes = array(); $cStartTime = microtime(true); $cDumpProc = proc_open($cDumpCmd, $cPipeDesc, $cPipes, '/tmp', array()); if (!is_resource($cDumpProc)) { echo "failed.\n"; continue; } else { echo "success.\n"; } echo "DB: " . $cDatabase . " - Uploading Database..."; flush(); $cUploadResult = ftp_fput($ftpConn, $dbFileName, $cPipes[1], FTP_BINARY); $cStopTime = microtime(true); if ($cUploadResult) { echo "success (" . round($cStopTime - $cStartTime, 3) . " seconds).\n"; $databaseCount++; } else { echo "failed.\n"; continue; } $cErrorOutput = stream_get_contents($cPipes[2]); foreach ($cPipes as $cFHandle) { fclose($cFHandle); } $cDumpStatus = proc_close($cDumpProc); if ($cDumpStatus != 0) { echo "DB: " . $cDatabase . " - Dump process caused an error:\n"; echo $cErrorOutput . "\n"; continue; } flush();