Skip to content

Instantly share code, notes, and snippets.

@r8r
Forked from kharysharpe/copydb.php
Created December 2, 2021 10:39
Show Gist options
  • Save r8r/5ff56990e6b6e5a780d860f8648b3960 to your computer and use it in GitHub Desktop.
Save r8r/5ff56990e6b6e5a780d860f8648b3960 to your computer and use it in GitHub Desktop.
Copy a table from one MYSQL database server to another database another
<?php
/**
*
* Code was adapted from SitePoint
* http://www.sitepoint.com/forums/showthread.php?697857-Copy-mysql-table-from-one-server-to-another-through-php&s=b5b25e09ff44749d2e49e0d7c1640fd8&p=4680578&viewfull=1#post4680578
*
*/
// Prevent script from timing out
set_time_limit(0);
// Table to be duplicated
$table = 'table_name';
// Source server of the table to be duplicated
$sourceHost = '127.0.0.1';
$sourceUser = 'your-username';
$sourcePassword = 'your-password';
$sourceDatabase = 'your-db';
// Destination server to duplicate the table
$destinationHost = '127.0.0.2';
$destinationUser = 'your-username';
$destinationPassword = 'your-password';
$destinationDatabase = 'your-db';
// Connect to source server
$source = mysql_connect($sourceHost, $sourceUser, $sourcePassword);
mysql_select_db($sourceDatabase, $source);
// Connect to destination server
$destination = mysql_connect($destinationHost, $destinationUser, $destinationPassword); // connect server 2
mysql_select_db($destinationDatabase, $destination); // select database 2
// Get the table structure from the source and create it on destination server
$tableInfo = mysql_fetch_array(mysql_query("SHOW CREATE TABLE $table ", $source)); // get structure from table on server 1
mysql_query(" $tableInfo[1] ", $destination); // use found structure to make table on server 2
// Copy data from source to destination
$result = mysql_query("SELECT * FROM $table ", $source); // select all content
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
mysql_query("INSERT INTO $table (" . implode(", ", array_keys($row)) . ") VALUES ('" . implode("', '", array_values($row)) . "')", $destination);
}
// Close connections
mysql_close($source);
mysql_close($destination);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment