Page 1 of 1

Simple MySQL DB Backup Script

Posted: Wed Mar 01, 2006 12:20 pm
by jayshields
Hi guys.

I've looked on Google, Hotscripts, etc. I've checked on raghavan's OO DB Backup Script, and I can't find one for me.

I don't think I can use the shell command with exec() to do it because I'm on a shared host. I've also looked at SELECT ... INTO OUTFILE but it seems it only dumps the data, I would like the structures aswell if possible.

Does a single file backup script not exist?

All I want is a script that I edit to set the username, password, hostname, database. Then whenever I execute it, it downloads a file with the backup inside, .txt is fine, .sql, .anything!

Surely that's simple enough but I can't find one anywhere.

Raghavans looked nice but I need 2 classes and also to implement it myself, and there isn't a usage example. I don't have time to read all the code. Also a DB class file is required at the top of the script which doesn't look to be available for download?

Thanks for any pointers!

Posted: Wed Mar 01, 2006 8:48 pm
by AKA Panama Jack
Here is one...

You will need to populate variables in it for the database, connection and table to backup...

Code: Select all

<?php
// Selective Database Backup

function get_def($dbname, $table) {
    global $conn, $fieldnames;
	$count = 0;
    $def = "";
    $def .= "DROP TABLE IF EXISTS $table;\n";
    $def .= "CREATE TABLE $table (\n";
    $result = mysql_db_query($dbname, "SHOW FIELDS FROM $table",$conn) or die("Table $table not existing in database");
    while($row = mysql_fetch_array($result)) {
        $def .= "    $row[Field] $row[Type]";
 		$fieldnames[$count] = $row['Field'];
		$count++;
        if ($row["Default"] != "") $def .= " DEFAULT '$row[Default]'";
        if ($row["Null"] != "YES") $def .= " NOT NULL";
       	if ($row['Extra'] != "") $def .= " $row[Extra]";
        	$def .= ",\n";
     }
     $def = ereg_replace(",\n$","", $def);
     $result = mysql_db_query($dbname, "SHOW KEYS FROM $table",$conn);
     while($row = mysql_fetch_array($result)) {
          $kname=$row['Key_name'];
          if(($kname != "PRIMARY") && ($row['Non_unique'] == 0)) $kname="UNIQUE|$kname";
          if(!isset($index[$kname])) $index[$kname] = array();
          $index[$kname][] = $row['Column_name'];
     }
     while(list($x, $columns) = @each($index)) {
          $def .= ",\n";
          if($x == "PRIMARY") $def .= "   PRIMARY KEY (" . implode($columns, ", ") . ")";
          else if (substr($x,0,6) == "UNIQUE") $def .= "   UNIQUE ".substr($x,7)." (" . implode($columns, ", ") . ")";
          else $def .= "   KEY $x (" . implode($columns, ", ") . ")";
     }

     $def .= "\n);";
     return (stripslashes($def));
}

function get_content($dbname, $table, $namelist) {
    global $conn, $encrypt, $path, $backup_encryption_type;

	$fp = fopen ($path.$table . "_data.sql","w");

	$content="";
	$result = mysql_db_query($dbname, "SELECT * FROM $table",$conn);
	while($row = mysql_fetch_row($result)) {
		$insert = "INSERT INTO $table (" .$namelist . ") VALUES (";
		for($j=0; $j<mysql_num_fields($result);$j++) {
			if(!isset($row[$j])) $insert .= "NULL,";
			else if($row[$j] != "") $insert .= "'".addslashes($row[$j])."',";
			else $insert .= "'',";
		}
		$insert = ereg_replace(",$","",$insert);
		$insert .= ");";

		$content .= $insert."\n";
		fwrite ($fp,$content);
		$content = "";
	}
	fclose ($fp);
}

//$path = the directory path where you want the information stored
//$dbhost = host address
//$dbuname = database user name
//$dbpass = database password
//$dbname = database name
//$tablename = the table in the database you want to backup

$conn = @mysql_connect($dbhost,$dbuname,$dbpass);
if ($conn==false)  
	die("password / user or database name wrong");

$path = $path . "backup/";
echo "<b>Starting Database Backup</b><br><br>";
flush();
echo "Backing up table: $tablename<br>";
flush();

if(!file_exists($path . $tablename . "_table.sql"))
{
	 @unlink($path.$tablename . "_table.sql");
   	 @unlink($path.$tablename . "_data.sql");
}

$cur_time=date("Y-m-d H:i");

unset($fieldnames);
$newfile = get_def($dbname,$tablename);

$fp = fopen ($path.$tablename . "_table.sql","w");
fwrite ($fp,$newfile);
fclose ($fp);

$namelist = "";
for($i = 0; $i < count($fieldnames); $i++){
	$namelist .= $fieldnames[$i];
	if($i != count($fieldnames) - 1)
		$namelist .= ",";
}

get_content($dbname,$tablename, $namelist);

echo "<br><b>Database Backup Complete</b><br><br>";
?>

Posted: Thu Mar 02, 2006 7:05 am
by jayshields
Thanks alot. I'll have a better look at that later.