problem with db backup script - running out of memory

[progman1010]

The point of the script is to backup up an entire database and output a mysql file. The script below works just fine on small databases, but i have a 136MB database that i'm trying to get it to run on and I'm having trouble getting it to finish running. (writes a partial file and dies)

i have tried a lot of things here- and actually what you see is a result of about the last hour's worth of work- it used to use mysql_query() instead of mysql_unbuffered_query(), and I added mysql_free_result() too.

actually, the latest issue is that when running the full code, it goes to a 500 error! if I add die('hello world'); to the first line of the file, it works fine. the server is fine, but that's what i get.

i'm stumped!

<?


/*creates a backup of the database in the following locations:

1. on the main fileserver
2. on a separate, outside database (COMING SOON)

*/
//SET KEY VARIABLES

//ini_set('max_execution_time',60*60);
ini_set("memory_limit","512M");

include_once("../common/configuration.php");
include_once("../common/functions.php");


$filename1="../backup/db_struc_".date("m-d-Y_His",time()+TIME_OFFSET).".bak";
$filename2="../backup/db_data_".date("m-d-Y_His",time()+TIME_OFFSET).".bak";



//first, the db dump.
function structuredump ($table) {
	$r="# Structure of $table \n\n CREATE TABLE IF NOT EXISTS $table ( \n";
	$key="";
	$result=mysql_unbuffered_query("SHOW COLUMNS FROM $table") or die(mysql_error());
	while ($line=mysql_fetch_object($result)) {
		$r.=" `".$line->Field."` ".$line->Type." ";
		$r.=($line->Null) ? "NULL " : "NOT NULL ";
		($line->Key) ? $key=" PRIMARY KEY (".$line->Field.") "  : null;
		$r.=((!$line->Default) ? null : "default '".$line->Default."'")." ".$line->Extra.",\n";
	}
	$r.="\n $key\n);\n\n\n";

	mysql_free_result($result);
	return $r;
}

//the insert function
function datadump ($table) {

    $r = "# Dump of $table \n";
    $r .= "# Dump DATE : " . date("M d, Y",time()+TIME_OFFSET) ."\n";

    $result = mysql_unbuffered_query("SELECT * FROM $table");
    $num_fields = mysql_num_fields($result);
	//$r .= "# Showing ".mysql_num_rows($result)." rows\n\n";

	while ($line=mysql_fetch_row($result)) {
      $r .= "INSERT INTO `".$table."` VALUES(";

          for($j=0; $j<$num_fields; $j++) {
			  $line[$j] = mysql_real_escape_string($line[$j]);
			  $line[$j] = str_replace("\n","\\n",$line[$j]);
			  $r.=(isset($line[$j])) ? "'".$line[$j]."'" : "''";
			  if ($j<($num_fields-1)) $r .= ",";
         }   
      $r .= ");\n";
     }
     $r .="\n\n\n";

	mysql_free_result($result); 
	return $r;
  }

$file.="# Created on: ".date("M d, Y - H:i:s",time()+TIME_OFFSET)."\n";
$file.="# Generated by: ".(($_GET['referrer']) ? $_GET['referrer'] : "manual generation")."\n";

//$result=mysql_list_tables(DB_NAME);
//alternative, working method
$sql = "SHOW TABLES FROM ".DB_NAME;
$result = mysql_query($sql);




//WRITE STRUCTURE into $f1 AND DATA into $f2
if ($result) {

	$f1=fopen($filename1,'w');
	$f2=fopen($filename2,'w');
	if ($f1) {
		while ($line=mysql_fetch_row($result)) {
			$s=structuredump($line[0]);
			fwrite($f1,$s);
			//echo "<p>$s</p>";
			$s = datadump($line[0]);
			fwrite($f2,$s);
			$tables.=$line[0]."<br>";

		}
		echo "<p>Backup written ok.</p>";

		fwrite($f1,"\n\n#End of File");
		fwrite($f2,"\n\n#End of File");
		$value = date("D, m d, Y - H:i:s",time()+TIME_OFFSET);
		@fclose($f1);	
		@fclose($f2);
		
	} else {
		$value = "ERROR1!";
	}
} else {
	$value = "ERROR2!";
}

//$temp = getQuery("SELECT MAX(id) AS id FROM cron_log");
//doQuery("UPDATE cron_log SET backup_run = '$value' WHERE id='{$temp['id']}'");
// [URL unfurl="true"]http://www.bniconnection.com/cron/backup.php[/URL]
echo $tables;
echo $value;

@mysql_close();
?>

To run the actual script:

http://www.bniconnection.com/cron/backup.php

Thanks for your help in advance!

 

[jpadie]

progman101,
it's difficult to debug this kind of problem without knowing your php info (phpinfo()) and your database size.

here's some code that approaches things slightly differently to yours but the core differences are unlikely to fix the issue on their own. it might be worth a whorl though.

if it doesn't work, try posting back with the other info.

<?php
$myBackup = new mysqlBackup($dbHost, $dbName, $dbUser, $dbPasswd);
$myBackup->backupToFile("/Users/justinadie/Desktop/sqlbackup.txt");

class mysqlBackup{
	
	private $createDB = '';
	private $tables = array();
	private $schema = array();
	private $tableInserts = array();
	
	
	public function __construct($host, $dbName, $user, $pwd){
		mysql_connect($host, $user, $pwd)
			or die ('Connection Error '. mysql_error());
		mysql_select_db($dbName)
			or die ('selection error '. mysql_error());
		$this->host = $host;
		$this->dbServer = $dbName;
	}
	public function backupToFile($fileName){
		
		$this->fileName = $fileName;
		if (!is_writable($this->fileName) && !is_writable (dirname($this->fileName))){
			die ('I cannot write to this file name');
		}
		if (empty($this->tables)) {
			$this->getTables();
		}
		$this->getCreateDatabase();
		$this->getTableSchema();
		$this->writeStructure();
		$this->getInserts();
		
	}
	public function backupRemote($host, $dbServer, $user, $pwd){
		//not implemented
	}
	
	private function getTables(){
		$sql = 'Show tables';
		$result = mysql_query($sql) or die(mysql_error());
		while ($row = mysql_fetch_array($result)){
			$this->tables[] = $row[0];
		}
	}
	private function getCreateDatabase(){
		$sql = "show create database {$this->dbName}";
		$result = mysql_query($sql) or die(mysql_error() . $sql);
		$row = mysql_fetch_array($result);
		$this->createDB = $row[1];
	}
	private function getTableSchema(){
		foreach ($this->tables as $table){
			$sql = "show create table $table";
			$result = mysql_query($sql)
				or die (mysql_error());
			$row = mysql_fetch_array($result);
			$this->schema[$table] = $row[1] .';';
		}
	}
	private function writeStructure(){
		$tables = implode ("\n", $this->schema);
		file_put_contents($this->fileName, $this->createDB . "\n" . $tables, FILE_APPEND) or die('cannot write to file');
		echo "Structure written to file $this->fileName <br/>";
	}
	private function getInserts(){
		$fh = fopen($this->fileName, "abt");
		fwrite($fh, "\n\n\n"); //add a few blank lines
		foreach ($this->tables as $table){
			set_time_limit(60); // reset the time limit ... might need to play with this figure.
			$sql = "select * from $table";
			$result = mysql_query($sql) or die (mysql_error());
			
			while ($row = mysql_fetch_assoc($result)){
				$fields = array();
				foreach ($row as $fieldName=>$data){
					$data = str_replace("'", mysql_real_escape_string("'"), $data);
					$fields[] = "$fieldName='$data'";
				}
				$f = implode (',', $fields);
				$output = "Insert into $table set $f ;\n";
				fwrite($fh, $output);
			}
			echo "Finished writing inserts for $table <br/>";
		}
		fclose ($fh);
		echo "Finished the whole schebang";
	}
}
?>

 

[jet042]

Not that doing with PHP is bad in any way, but why aren't you using the mysqldump utility that comes with MySQL? It's command-line based and can do what it looks like you are trying to accomplish with this script.

 

[jpadie]

i don't think that mysqldump includes the dbschema. it also doesn't include the triggers/views and grant tables etc (not that the code above does either).

but in terms of just getting an output of the inserts, I agree: mysqldump would be much better.