get free code

How do I copy a MySQL database from one computer/server to another?

Short answer is you can copy database from one computer/server to another using ssh or mysql client.

You can run all the above 3 commands in one pass using mysqldump and mysql commands (insecure method, use only if you are using VPN or trust your network):
$ mysqldump db-name | mysql -h remote.box.com db-name

Use ssh if you don't have direct access to remote mysql server (secure method):
$ mysqldump db-name | ssh user@remote.box.com mysql db-name

You can just copy table called foo to remote database (and remote mysql server remote.box.com) called bar using same syntax:
$ mysqldump db-name foo | ssh user@remote.box.com mysql bar

This will not just save your time but you can impress your friend too ;). Almost all commands can be run using pipes under UNIX/Linux oses.

or

If you are looking for to develop PHP script which can copy all data and structure of all tables of first database to other database you are on right place. Previously I had posted a script with ability to copy all data of first mysql db to other mysql db with same table structure. But now this script update both data and structure of tables in destination database. Script do not remove or change any additional tables in destination db it just pick all tables of source db to destination db or if they exist in destination db script update their data and structure.

$dbc=@mysql_connect("localhost","dbUser","dbPass") OR die('Couldn\'t connect to MySql:'.mysql_error());


$result = mysql_list_tables ("database1name");
$i = 0;
while ($i < mysql_num_rows ($result))
{
$table[$i] = mysql_tablename ($result,$i);
$i++;
}


$j=count($table);
for($i=0;$i<$j;$i++)
{

$qry="drop table if exists database2name.".$table[$i];
mysql_query($qry)or die("Failed to delete:".mysql_error());

$query="CREATE TABLE database2name.".$table[$i]." SELECT * FROM database1name.".$table[$i];
mysql_query($query)or die("Table copy failed:".mysql_error());
echo "$table[$i] copy done...";
}
?>


So script actually works in a way that it picks list of tables in first db the delete those tables from second db then recreate them in second db. Thats it.

If you change or optimize the code to use I will be happy to see an update from you through comment.