Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

OSCommerce - How to Backup your Database?

I have noted this message in a readme file for a new OSCommerce contribution and I'd like to know what I can do about it...

"Whenever Importing any new SQL files to your Database ALWAYS do a FULL
DATABASE EXPORT first: include STRUCTURE, DATA and DROPTABLE - this
enables a quick restore alternative if errors do occur"

I have asked my developer to create a file which will download this 'backup'.  He says he can only create a file to Export this database.  However, when I run the file (PHP) I appear to get a huge list of text saying 'Insert this'...SQL type language.  It goes on and on though I haven't left it for longer than half an hour due to site bandwidth issues.

Attached is the code in full - there appears to be no harm posting it.
Tell me what you think and how you think you might go about exporting a database so that i'm left with a dump file.


PS-> The high point allocation suggests links to external documents and examples.




<?php
$no_redirect=true;
$require_options_path="includes/";
require($require_options_path."options.php");
 
function get_tables($MySQLDatabase){
	global $SQL99;
	
   $SQL99->SQLQuery="show tables";
   $SQL99->ExecSQL();
	$i = 0;
	while ($SQL99->FetchRow()){
		$res[$i]=$SQL99->SQLRow["Tables_in_".$MySQLDatabase];
		$i++;
	}
  return $res;
}
 
$tabs=get_tables($MySQLDatabase);
 
$i=0;
$ret_str="";
while($tabs[$i]){
	
	$SQL->SQLQuery="desc ".$tabs[$i];
	$SQL->ExecSQL();
	echo "create table ".$tabs[$i]." ( \n ";
	$s=0;
	while ($SQL->FetchRow()) {
		//print_r($SQL->SQLRow);
		if ($s) echo ", ";
		echo $SQL->SQLRow["Field"]." ".$SQL->SQLRow["Type"];
		
		if ($SQL->SQLRow["Key"]=="PRI") echo " not null primary key ";
		echo " ".$SQL->SQLRow["Extra"];
		echo "\n";
		$s++;
	}
	echo "); \n\n";
	
	$SQL->SQLQuery="select * from ".$tabs[$i];
	$SQL->ExecSQL();
	while($SQL->FetchRow()) {
		echo "insert into ".$tabs[$i]." (";
		for ($ii=0;$ii<$SQL->SQLResultNumColumns;$ii++){
			//print_r($SQL->SQLColumnName);
			if ($ii) echo ",";
			echo $SQL->SQLColumnName[$ii];
		}
		echo ") values (";
		
		for ($ii=0;$ii<$SQL->SQLResultNumColumns;$ii++){
			if ($ii) echo ",";
			echo "'".str_replace("'","''",$SQL->SQLRow[$SQL->SQLColumnName[$ii]])."'";
		}
		
		
		echo "); \n";
	
	}
	
 
$i++;
}
//$filename="spsbackup_".time().".sql";
//create_file("dump_files/".$filename, $ret_str);
//include("zip.lib.php");
//$ziper = new zipfile();
//$ziper->addFiles(array("dump_files/".$filename));
//$dump_buffer = $ziper -> file();
 
//$zipfilename='backup-'.date("Y-m-d").'.sql';
 // header('Pragma: public');
 // header('Content-type: application/zip');
//  header('Content-length: ' . strlen($ret_str));
 // header('Content-Disposition: attachment; filename="'.$zipfilename.'"');
 
  //echo $dump_buffer;
 // echo $ret_str;
?>

Open in new window

0
chriscounter07
Asked:
chriscounter07
1 Solution
 
absxCommented:
Hi Chris,

The SQL dump you're seeing is using the MySQL way of making backups. Even the MySQL-provided application mysqldump does exactly this, regenerates the SQL statements that were used to create and populate the database in the first place, allowing for a clean restore point should the database fail. Another way of backing up MySQL data is to have a file copy of the actual storage files, but most web hotel customers have no access to these.

The biggest problem with the (unfinished-looking) script above is that it writes the dump directly to the HTTP stream (user's browser), when it could be writing it to a text file to be zipped and given for the user to download. Even safer would be to not serve the backup file through HTTP at all, but save it at the web server and make the user fetch it with a secure connection.

There are some potential flaws in the script, too. Chances are that OSCommerce isn't made of tables only - it could have underlying views or procedures that this script ignores. Potential auto_increment values are also ignored in this example. Apparently OSCommerce also comes with it's own backup tool, "Database Backup Manager".

Most web hotel service providers offer the tool PHPMyAdmin for users to manage their database - this tool has a very reliable exporting (backup) feature which can serve the backup files readily zipped, reducing bandwidth requirements (guide: http://www.siteground.com/tutorials/php-mysql/mysql_export.htm). It can't be made to make the exports with a scheduler like a custom script could, though.

Hope this gets you on the right path!
0
 
chriscounter07Author Commented:
A well written response.

Note, I used a Curl batch file to write the response from http to a text file.  The Internet Browsers were crashing under the 32meg file
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now