Export MySql Database Using PHP Script

Posted on August 20, 2014

Here is the script to export MySql database using php. Cheers!

// Check via PhpMyAdmin to see what character database is using 
mysql_query('SET NAMES utf8'); 
mysql_query('SET CHARACTER SET utf8'); 
$tables = '*'; 
//get all of the tables if($tables == '*') 

    $tables = array(); 
    $result = mysql_query('SHOW TABLES'); 
    while($row = mysql_fetch_row($result)) { 
    $tables[] = $row[0]; 


$tables = is_array($tables) ? $tables : explode(',',$tables); 

//cycle through 
foreach($tables as $table) 

$result = mysql_query('SELECT * FROM '.$table); 
$num_fields = mysql_num_fields($result); 
$return.= 'DROP TABLE '.$table.';'; 
$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table)); 
$return.= "\n\n".$row2[1].";\n\n"; 
for ($i = 0; $i &lt; $num_fields; $i++) 

while($row = mysql_fetch_row($result)) 

$return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $j&lt;$num_fields; $j++) { $row[$j] = addslashes($row[$j]); 
$row[$j] = ereg_replace("\n","\\n",$row[$j]); 
if (isset($row[$j])) 

$return.= '"'.$row[$j].'"' ; 


$return.= '""'; 

if ($j&lt;($num_fields-1)) 

$return.= ','; 

$return.= ");\n"; 


//save file 
$handle = fopen('backups/db-backup-'.date('Y-m-d H:i:s').'-'.(md5(implode(',',$tables))).'.sql','w+'); fwrite($handle,$return); fclose($handle); if($handle) 

echo "database is successfully stored in backups directory."; 


echo "database backup unsuccessfuly.";