Français : Exporter et importer des bases de données volumineuses avec PHP (grosses BDD > 5 Mo)
The following script is a good way to dump a large database with PHP, just because it is “memory_limit” & “max_execution_time” independent.
“import.php” will create DB dump files in the same directory, while “store.php” sends those dump files to any mysql server.
import.php
<?php
error_reporting(8191);
$rnb=(preg_match("`^([0-9]+)$`",$_GET["rnb"],$o))?$o[1]:0;
$tnb=(preg_match("`^([0-9]+)$`",$_GET["tnb"],$o))?$o[1]:-1;
$a=1024*1024*10; //max bytes per file
$m=1000*20; //max rows per file
$b="PREFIX%d-%d.sql"; //file format
$w="host";$x="user";$c="password";$v="base"; //db setup
$b=sprintf($b,$tnb,$rnb);
echo $b;
$link=mysql_connect($w,$x,$c);
if(!mysql_error()){
mysql_select_db($v,$link);
echo"<pre>";
$q=mysql_query("show table status",$link);
while($s[]=mysql_fetch_row($q)){}
if($rnb==0){$fp=fopen($b,"wb");fclose($fp);}
$fp=fopen($b,"ab");
echo"<br />::".$tnb."::".$rnb."<br />";
if($tnb==-1){
//tables structure
$out="";
foreach($s as $k=>$l){
$t=$l[0];
if($t!=""){
$out.="DROP TABLE IF EXISTS `{$t}`; \nCREATE TABLE `{$t}` (";
$p=mysql_query("SHOW FIELDS FROM `{$t}`");
while($row=mysql_fetch_object($p)){
$out.=" `{$row->Field}` {$row->Type}";
if($row->Default!=’CURRENT_TIMESTAMP’){
$out.=(!empty($row->Default))?" DEFAULT ‘{$row->Default}’":false;
}else{$out.=(!empty($row->Default))?" DEFAULT {$row->Default}":false;}
$out.=($row->Null!="YES")?" NOT NULL":false;
$out.=(!empty($row->Extra))?" {$row->Extra}":false;
$out.=",";}
$out=ereg_replace(",$","",$out);
unset($index);
$p=mysql_query("SHOW KEYS FROM `{$t}`");
while($row=mysql_fetch_object($p)){
if(($row->Key_name==’PRIMARY’) && ($row->Index_type==’BTREE’)){$index[’PRIMARY’][$row->Key_name]=$row->Column_name;}if(($row->Key_name!=’PRIMARY’) && ($row->Non_unique==’0′) && ($row->Index_type==’BTREE’)){$index[’UNIQUE’][$row->Key_name]=$row->Column_name;}
if(($row->Key_name!=’PRIMARY’) && ($row->Non_unique==’1′) && ($row->Index_type==’BTREE’)){$index[’INDEX’][$row->Key_name]=$row->Column_name;}
if(($row->Key_name!=’PRIMARY’) && ($row->Non_unique==’1′) && ($row->Index_type==’FULLTEXT’)){
$index[’FULLTEXT’][$row->Key_name]=$row->Column_name;}
}
if(is_array($index)){
foreach($index as $xy=>$columns){
$out.=",";$g=0;foreach($columns as $column_key=>$column_name){$g++;
$out.=($xy=="PRIMARY")?" PRIMARY KEY (`{$column_name}`)":false;
$out.=($xy=="UNIQUE")?" UNIQUE KEY `{$column_key}` (`{$column_name}`)":false;
$out.=($xy=="INDEX")?" KEY `{$column_key}` (`{$column_name}`)":false;
$out.=($xy=="FULLTEXT")?" FULLTEXT `{$column_key}` (`{$column_name}`)":false;
$out.=($g<(count($index[$xy])))?",":false;
}}}
$out.=");\n";
}}
fwrite($fp,$out);
$rnb=0;$tnb=0;
}
else {
//rows
$t=$s[$tnb][0];
echo $s[$tnb][4];
if($t!="" && $s[$tnb][4]>0){
$q=mysql_query("select * from `".$t."` limit ".$rnb.",".$m,$link);
echo mysql_error();
$nr=$s[$tnb][4];$nn=0;
while(filesize($b)<$a && $nn<$m){clearstatcache();$r=mysql_fetch_row($q);$out="";
foreach($r as $k=>$l){$out.="\"".str_replace(’"’,'\"’,stripslashes($l))."\",";}
if($out!=""){
$out=preg_replace("`,$`","",$out);
$out="insert into `".$t."` values (".$out.");\n";
fwrite($fp,$out);
}
$rnb++;$nn++;
echo " ";
//echo $rnb;
if($rnb==$nr){$rnb=0;$tnb++;break;}
}}else{$rnb=0;$tnb++;}}
mysql_close();
fclose($fp);
echo"</pre>";
}else{echo"<br />Error. Re-trying.";}
if(count($s)>$tnb || !$s){
echo "document.location.href=\"import.php?rnb=".$rnb."&tnb=".$tnb."\";";
echo "<script>document.location.href=\"import.php?rnb=".$rnb."&tnb=".$tnb."\";</script>";
}else{echo"<br />END";exit;}
?>
store.php
<?php
error_reporting(8191);
$fnb=(preg_match("`^([0-9]+)$`",$_GET["fnb"],$o))?$o[1]:0;
$w="host";$x="user";$c="password";$v="base"; //db setup
$a=glob("PREFIX*.sql");
$link=mysql_connect($w,$x,$c);
if(!mysql_error()){
mysql_select_db($v,$link);
echo"<pre>";
$l=$a[$fnb];
echo "<br />".$l."<br />";
$fp=fopen($l,"rb");
while($z=trim(fgets($fp))){
mysql_query($z,$link);echo " ".mysql_error();
}
fclose($fp);
$fnb++;
mysql_close();
echo"</pre>";
}else{echo"<br />Error. Re-trying.";}
if(count($a)>$fnb){
echo "document.location.href=\"store.php?fnb=".$fnb."\";";
echo "<script>document.location.href=\"store.php?fnb=".$fnb."\";</script>";
}else{echo"<br />END";exit;}
?>