About This Author :: Request Reprint :: Print Article :: Tell A Friend

How To Backup Your MySQL Database With PHP
By Ben Sinclair
Contact Ben Sinclair


If your website some how gets hacked and you lose all your MySQL Database information, I doubt you would be laughing... or smiling... That's why you need to backup your database from time to time so that you can put it back the way it was. Here is a simple script using PHP and MySQL to backup your database!

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) {

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

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

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



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!

Discuss this in our Webmaster Forums

About This Author :: Request Reprint :: Print Article :: Tell A Friend