OSCommerce - How to Backup your Database?

Posted on 2009-02-17
Last Modified: 2013-12-12
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
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.





function get_tables($MySQLDatabase){

	global $SQL99;


   $SQL99->SQLQuery="show tables";


	$i = 0;

	while ($SQL99->FetchRow()){




  return $res;







	$SQL->SQLQuery="desc ".$tabs[$i];


	echo "create table ".$tabs[$i]." ( \n ";


	while ($SQL->FetchRow()) {


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



	echo "); \n\n";


	$SQL->SQLQuery="select * from ".$tabs[$i];


	while($SQL->FetchRow()) {

		echo "insert into ".$tabs[$i]." (";

		for ($ii=0;$ii<$SQL->SQLResultNumColumns;$ii++){


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







//create_file("dump_files/".$filename, $ret_str);


//$ziper = new zipfile();


//$dump_buffer = $ziper -> file();


 // 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

Question by:chriscounter07
    LVL 9

    Accepted Solution

    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: 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!

    Author Closing Comment

    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

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Specific format 21 118
    Upload PDF On Mobile 1 43
    Can't see php code problem 12 30
    PHP Word Writer 1 30
    Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now