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 ",$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 ?