Copy mysql table from one server to another through php

PHP
1

I’m working on a backupscript for a mysql server. I want to do it only in php so that it still works without command line access or without phpmyadmin. So far I have something that connects to both servers, reads the structure of a table on server 1 and creates a similar table on server 2



<?php

$dblink1=mysql_connect('$ip1', '$user1', '$pass1'); // connect server 1
mysql_select_db('$database1',$dblink1);  // select database 1

$dblink1=mysql_connect('$ip2', '$user2', '$pass2'); // connect server 2	
mysql_select_db('$database2',$dblink2); // select database 2

$table='tabletest';

$tableinfo = mysql_fetch_array(mysql_query("SHOW CREATE TABLE $table  ",$dblink1)); // get structure from table on server 1

mysql_query(" $tableinfo[1] ",$dblink2); // use found structure to make table on server 2

// copy table content here

 mysql_close($dblink1);
 mysql_close($dblink2);

 ?>

But then i’m a bit lost. How can I copy all the content of the source table on server 1 and insert it into the newly created table on server 2?

I dont think a variation on this is going to work :

mysql_query("insert into $newtable select * from $oldtable ");

Because (afaik) you can only do such things if you stay within the same server ? Or would it be possible to use such a statement across 2 mysql servers ?

I could also loop through all the data with a select/while statement, and insert the rows one by one … but I guess then I’ll have to use table-specific information like keys to select/insert the right data. In the end I want to use this backup script for hundreds of tables at once, so I’m searching a generic solution.

Any suggestions please ?

2

Perhaps this will help:

http://stackoverflow.com/questions/338251/how-do-i-enable-cross-database-joins-in-mysql

Cheers,
Alex

3

Not sure, that seems to handle different databases on the same server. I want to go a level further, and handle different databases on different servers.

I might have found a solution myself though, will post it soon

4 
<?php

$dblink1=mysql_connect('$ip1', '$user1', '$pass1'); // connect server 1
mysql_select_db('$database1',$dblink1);  // select database 1

$dblink1=mysql_connect('$ip2', '$user2', '$pass2'); // connect server 2	
mysql_select_db('$database2',$dblink2); // select database 2

$table='tabletest';

$tableinfo = mysql_fetch_array(mysql_query("SHOW CREATE TABLE $table  ",$dblink1)); // get structure from table on server 1

mysql_query(" $tableinfo[1] ",$dblink2); // use found structure to make table on server 2

$result = mysql_query("SELECT * FROM $table  ",$dblink1); // 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))."')",$dblink2); // insert one row into new table
}

 mysql_close($dblink1);
 mysql_close($dblink2);

 ?>

This worked for me on a small test table. It will probably be very slow on huge tables, compared to the real command line backup tools. But it works, thats the main thing here.