Simple MySQL DB Backup Script

Ye' old general discussion board. Basically, for everything that isn't covered elsewhere. Come here to shoot the breeze, shoot your mouth off, or whatever suits your fancy.
This forum is not for asking programming related questions.

Moderator: General Moderators

Post Reply
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Simple MySQL DB Backup Script

Post 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!
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post 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>";
?>
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post by jayshields »

Thanks alot. I'll have a better look at that later.
Post Reply