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!
Simple MySQL DB Backup Script
Moderator: General Moderators
- jayshields
- DevNet Resident
- Posts: 1912
- Joined: Mon Aug 22, 2005 12:11 pm
- Location: Leeds/Manchester, England
- AKA Panama Jack
- Forum Regular
- Posts: 878
- Joined: Mon Nov 14, 2005 4:21 pm
Here is one...
You will need to populate variables in it for the database, connection and table to backup...
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>";
?>- jayshields
- DevNet Resident
- Posts: 1912
- Joined: Mon Aug 22, 2005 12:11 pm
- Location: Leeds/Manchester, England