PHP - PDO mysql backup to remote host?

I would start with the MySQL documentation on replication I would not recommend doing this with PHP and PDO. You'll have better results when you use the right tool for the job. IMHO.

I would start with the MySQL documentation on replication. I would not recommend doing this with PHP and PDO. You'll have better results when you use the right tool for the job.IMHO.

If it's about having a backup, you should really stick to established admin tools. Use SQLBuddy or PHPMySQLAdmin. You could write a duplication system using a RPC method or data transfer with JSON or something.

But that needs securing, would be slow and only helpful for content transfer, not SQL schema replication.

You'll have to perform a SELECT * FROM table on every table you have in your source database, then run a big transaction on your destination database that deletes all previous table data and then inserts the new data. Basically something similar to this snippet: beginTransaction(); $destDB->beginTransaction(); try { foreach ($tableNames as $tableName) { // Fetch records from source $srcStatement = $srcDB->query('SELECT * FROM '. $tableName); $rows = $srcStatement->fetchAll(PDO::FETCH_NUM); // Free resources allocated by query $srcStatement->closeCursor(); $srcStatement = null; if ( count($rows) === 0 ) continue; // No rows // Prepare records to insert $insertValues = array(); foreach ($rows as $row) $insertValues = '('.

Implode(',', array_map(array($destDB, 'quote'), $row)). ')'; // Clear destination table if ( $destDB->exec('DELETE FROM '. $tableName) === false ) throw new Exception('DELETE failed for table '.

$tableName); // Write records if ( $destDB->exec('INSERT INTO '. $tableName. ' VALUES '.

Implode(',', $insertValues)) === false ) throw new Exception('INSERT failed for table '. $tableName); } } catch (Exception $e) { $srcDB->rollBack(); $destDB->rollBack(); throw $e; } $result = $destDB->commit(); $srcDB->rollBack(); // or $srcDB->commit() - we did not change the source DB though if (!$result ) throw new Exception('Commit failed'); // Success? > Notes: At least for MySQL, quoting INTs (e.g. Primary key IDs) causes no errors and works properly, so $destDB->quote() in the array_map() should be safe then, but I don't know for sure if this applies for all data types and all database systems.

Doing everything in a single transaction prevents inconsistent backups. The example assumes that you have the same database structure in both databases, with all columns appearing in exactly the same order. Your tables have to be deleted and built in correct order to not violate any foreign key constraints, otherwise the backup could fail.

Test extensively (ideally on a dedicated test system with a copy of your live data) with different scenarios (some or all tables empty, tables with lots of rows, all tables non-empty etc. ) to ensure that it works reliably and that your backup script does not run out of memory due to a low memory_limit setting). Compare the destination database with the source by dumping them e.g. With mysqldump or the like to verify that they are identical and that the backup is complete. You might even encounter nasty PDO bugs in the worst case that you may need to work around – Google and SO are your friends :-).

EDIT: One could additionally use the special INFORMATION_SCHEMA database to query the table names and determine the foreign key dependencies automatically. Also, you might want to disable foreign key checks on the target database during transfer (and reenable them afterwards) when you have self-referential foreign key relations to prevent failures due to constraint violations.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.