How To Backup Your MySQL Database With PHP

The Script

First of all you need to connect to the database:


<?php
$db_host = "localhost";
$db_name = "";
$db_user = "";
$db_pass = "";

mysql_connect($db_host,$db_user,$db_pass);
@mysql_select_db($db_name) or die("Unable to select database.");
?>

And then you need to create a function that will backup your table contents:


<?php
function datadump ($table) {

    $result .= "# Dump of $table \n";
    $result .= "# Dump DATE : " . date("d-M-Y") ."\n\n";

    $query = mysql_query("select * from $table");
    $num_fields = @mysql_num_fields($query);
    $numrow = mysql_num_rows($query);

    for ($i =0; $i<$numrow; $i++) {
      $result .= "INSERT INTO ".$table." VALUES(";
          for($j=0; $j<$num_fields; $j++) {
          $row[$j] = addslashes($row[$j]);
          $row[$j] = ereg_replace("\n","\\n",$row[$j]);
          if (isset($row[$j])) $result .= "\"$row[$j]\"" ; else $result .= "\"\"";
          if ($j<($num_fields-1)) $result .= ",";
         }   
      $result .= ");\n";
     }
     return $result . "\n\n\n";
  }
?>

OK, now you add the last bit to the script:


<?php
$table1 = datadump ("table1");
$table2 = datadump ("table2");
$table3 = datadump ("table3");

$content = $table1 . $table2 . $table3;

$file_name = "MySQL_Database_Backup.sql";
Header("Content-type: application/octet-stream");
Header("Content-Disposition: attachment; filename=$file_name");
echo $content;
exit;
?>

See this part:

$table1 = datadump (“table1″);

$table2 = datadump (“table2″);

$table3 = datadump (“table3″);

$content = $table1 . $table2 . $table3;

That is where you need to specify the tables you want to backup.

Lets say you had 3 tables called links, categories and users. You would need to add:

$links = datadump (“links”);

$categories = datadump (“categories”);

$users = datadump (“users”);

And then to the $content area add:

$content = $links . $categories . $users;

That’s it. Enter your MySQL Database information and fill in the tables an hey presto, run the script and it should backup your table contents into a .sql file for download!

If your database information every got lost, you have a backup :)

Enjoy!

About the author

Written by Ben Sinclair.

http://www.webmaster-resources101.com Ben Sinclair is the Webmaster of Webmaster Resources 101 (http://www.webmaster-resources101.com/) and Webmaster Forums 101 (http://www.webmaster-forums101.com/)

Website

If you found this post useful you may also want to check these out: