[SOLVED] backup mysql database with PHP....

[stelthius]

Hi guys,

 

I've been looking on google for quite some time now into making a script to back up a specific database table with PHP and then being able to download it i want to use PHP to do this as i feel more comfertable working with PHP but my problem i s i cant seem to find any tuts that actually work.

 

 

Rick

[stelthius]

Ok i found a snippet that works to an extent,

 

 

<?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.");

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";
  }

$active_guests = datadump ("active_guests");
$active_users = datadump ("active_users");
$banned_users = datadump ("banned_users");

$content = $active_guests . $active_users . $banned_users;

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

 

 

But for some reason its giving me this in my output file and im not exactly to sure why, i cant seem to see anything wrong here.

 

 

# Dump of active_guests 
# Dump DATE : 15-Dec-2008




# Dump of active_users 
# Dump DATE : 15-Dec-2008

INSERT INTO active_users VALUES("","");



# Dump of banned_users 
# Dump DATE : 15-Dec-2008

INSERT INTO banned_users VALUES("","");

 

Any thoughts on this one guys ?

[gevans]

Sorry not looking at the code, just a suggestion. I just loop through a table and make a csv file for download

[tomfmason]

A quick search of phpclasses shows http://www.phpclasses.org/browse/package/2527.html

 

This class can be used to generate a dump of the structure and data contained in a given MySQL database.

 

It generates a string that contains of SQL statements that when executed can recreate the structure of tables as well the contents of its rows.

 

Therefore, it can be used to generate backup copies of a given MySQL database.

[stelthius]

Hello,

 

I tried that one tom but it doesnt dump the SQL properly it kinda just throws it all over the page lol and im looking for one that downlaods it to your local machine not just spits the DB out all over your screen :/

[tomfmason]

Hello,

 

I tried that one tom but it doesnt dump the SQL properly it kinda just throws it all over the page lol and im looking for one that downlaods it to your local machine not just spits the DB out all over your screen :/

 

some simple modifications to the example.php yielded the desired results

 

<?php

mysql_connect("localhost", "root", "");

require("class_mysqldump.php");
$dump = new MySQLDump();
$file_name = "MySQL_Database_Backup.sql";
Header("Content-type: application/octet-stream");
Header("Content-Disposition: attachment; filename=$file_name");
print $dump->dumpDatabase("db");

?>