download mysql database as sql file using php

Question

In my website admin panel there is an option to take backup of database. For that I use the below code but I can't download db using this code. Did any one know how this happen. I tried to echo the $return variable and its outputs are correct but i cant download the file as sql.

<?php

backup_tables('localhost','root','','dbname');


/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{

  $link = mysql_connect($host,$user,$pass);
  mysql_select_db($name,$link);

  //get all of the tables
  if($tables == '*')
  {
    $tables = array();
    $result = mysql_query('SHOW TABLES');

    while($row = mysql_fetch_row($result))
    {
      $tables[] = $row[0];

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

  //cycle through
  foreach($tables as $table)
  {
    $return="";
    $result = mysql_query('SELECT * FROM '.$table);
    $num_fields = mysql_num_fields($result);
    //print_r($num_fields);exit;
    $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 < $num_fields; $i++) 
    {
      while($row = mysql_fetch_row($result))
      {
        $return.= 'INSERT INTO '.$table.' VALUES(';
        for($j=0; $j<$num_fields; $j++) 
        {
          $row[$j] = addslashes($row[$j]);
         // $row[$j] = preg_replace("\n","\\n",$row[$j]);

          $row[$j] = preg_replace("/(\n){2,}/", "\\n", $row[$j]); 

          if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
          if ($j<($num_fields-1)) { $return.= ','; }
        }
        $return.= ");\n";
      }
    }
    $return.="\n\n\n";

  }

  //save file
  $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
 // print_r($handle);exit;
  fwrite($handle,$return);
  fclose($handle);

}
?>

possible duplicate of [Export MySQL database using PHP only](http://stackoverflow.com/questions/22195493/export-mysql-database-using-php-only) — T.Todua, Nov 27 '14 at 20:56

score 2 · Answer 1 · answered Jan 11 '13 at 08:59

//save file
  $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
 // print_r($handle);exit;
  fwrite($handle,$return);
  fclose($handle);
//add below code to download it as a sql file
Header('Content-type: application/octet-stream');
Header('Content-Disposition: attachment; filename=db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql');
echo $return;

score 2 · Answer 2 · answered Nov 17 '11 at 11:30

instead of creating everything yourself you could use a system() call and just use the mysql backup functionality. This is one little statement which will create a file on your server where you want it to be. And for downloading the file, set the correct header with header() before echoing the content.

score 1 · Answer 3 · answered Nov 17 '11 at 11:32

1

You're reinventing the wheel! In the happy case this code could generate an insert script for a table, but there will be many special cases when it'll fail!

Use mysqldump http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

answered Nov 17 '11 at 11:32

bpgergo

15,669
5
44
68

score 0 · Answer 4 · answered Jun 02 '13 at 23:49

All previously mentioned concerns (loading into PHP memory and the availability of mysqldump) aside, there's also an error in the script that only returns the data for the last table. The line $return=""; needs to be outside and before the foreach loop it's in. Something like:

<?php

backup_tables('localhost','root','','dbname');


/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{

  $link = mysql_connect($host,$user,$pass);
  mysql_select_db($name,$link);
  $return="";

  //get all of the tables
  if($tables == '*')
  {
    $tables = array();
    $result = mysql_query('SHOW TABLES');

    while($row = mysql_fetch_row($result))
    {
      $tables[] = $row[0];

    }
  }
  else
  {
    $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);
    //print_r($num_fields);exit;
    $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 < $num_fields; $i++) 
    {
      while($row = mysql_fetch_row($result))
      {
        $return.= 'INSERT INTO '.$table.' VALUES(';
        for($j=0; $j<$num_fields; $j++) 
        {
          $row[$j] = addslashes($row[$j]);
         // $row[$j] = preg_replace("\n","\\n",$row[$j]);

          $row[$j] = preg_replace("/(\n){2,}/", "\\n", $row[$j]); 

          if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
          if ($j<($num_fields-1)) { $return.= ','; }
        }
        $return.= ");\n";
      }
    }
    $return.="\n\n\n";

  }

  //save file
  $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
 // print_r($handle);exit;
  fwrite($handle,$return);
  fclose($handle);

}
?>

fonini · Answer 5 · 2011-11-17T11:54:57.793

You're saving the file on the server instead of sending it back to the browser to download. You need to send a header with the file content for download.

header("Content-type: application/octet-stream");
header('Content-Disposition: attachment; filename=db_dump');
echo backup_tables('localhost','root','','dbname'); // change your function to return the data instead saving in a file

But keep in your mind that it is not the best approach to make a database dump.

score 0 · Answer 6 · answered Nov 17 '11 at 11:42

Loading the entire database into PHP memory does not strike me as a very sensible way to back up a database.

Assuming you don't have CLI access to run mysqldump nor can replicate the database elsewhere, the only sensible solution is to write the output of the select statements to the webserver buffer (flushing regularly).

The script you are trying to use also contains systemic defects. The data is written to the 'return' variable but this variable is never returned by the function. The variable is truncated at the start of processing each table.

All 3 of these issues would be resolved by replacing each instance of:

$return.=

With

print

(but don't forget to add some flushes)

HTH

download mysql database as sql file using php

6 Answers6

Linked