Holstenjungs
20.09.2006, 16:05:28
Moin,
habe da ein Backupscript in PHP welches meine Datenbank zu einem bestimmten Zeitpunkt per Cron sichert.
Hier das Script:
// mySQL - variables
$dbhost="localhost";
$dbuser="$db_user";
$dbpass="$db_passwd";
$dbname[]="$db_name";
// number of backups to keep
$backups = 100;
// hours between backups
$interval = 48;
// 1 only with ZLib support, else change value to 0
$compression = 1;
// full path to phpMyBackup
$path = $_SERVER['DOCUMENT_ROOT']."/backup/";
flush();
$conn = mysql_connect($dbhost,$dbuser,$dbpass)
or die(mysql_error());
$path = $path . "dump/";
//if (!is_dir($path)) mkdir($path, 0777);
function get_def($dbname, $table) {
global $conn;
$def = "";
// $def .= "DROP TABLE IF EXISTS $table;\n";
$def .= "CREATE TABLE $table (\n";
$result = mysql_db_query($dbname, "SHOW FIELDS FROM $table",$conn);
while($row = mysql_fetch_array($result)) {
$def .= " $row[Field] $row[Type]";
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) {
global $conn;
$content="";
if (1=1) {} else {
$result = mysql_db_query($dbname, "SELECT * FROM $table",$conn);
while($row = mysql_fetch_row($result)) {
$insert = "INSERT INTO $table 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 .= ");\n";
$content .= $insert;
}
return $content;
}
}
if ($compression==1) $filetype = "sql.gz";
else $filetype = "sql";
//if (filemtime($path . "0.$filetype") < time() - $interval * 3600 && !eregi("/restore\.",$PHP_SELF)) {
$cur_time=date("Y-m-d H:i");
$newfile="# Dump created with 'phpMyBackup v.$version' on $cur_time\r\n";
while (list(,$val) = each($dbname)) {
//alte backups renamen
for ($i = $backups-1; $i > 0; $i--) {
$j = $i-1;
$oldname = $path.$val. $j . ".$filetype";
$newname = $path.$val. $i . ".$filetype";
@rename($oldname,$newname);
}
//backuppen
$tables = mysql_list_tables($val,$conn);
$num_tables = @mysql_num_rows($tables);
$i = 0;
while($i < $num_tables) {
$table = mysql_tablename($tables, $i);
$newfile .= "\n# ----------------------------------------------------------\n#\n";
$newfile .= "# structur for table '$table'\n#\n";
$newfile .= get_def($val,$table);
$newfile .= "\n\n";
$newfile .= "#\n# data for table '$table'\n#\n";
145: $newfile .= get_content($val,$table);
$newfile .= "\n\n";
$i++;
}
if ($compression==1) {
$date = date("d-m-Y-H-i");
$fp = gzopen($path.$val."-$date.$filetype","w9");
// $fp = tmpfile();
gzwrite ($fp,$newfile);
gzclose ($fp);
} else {
$fp = fopen ($path.$val."0.$filetype","w");
fwrite ($fp,$newfile);
fclose ($fp);
}
}
Nun bekomme ich aber seit neuestem eine Fehlermeldung, hier:
Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 4015756 bytes) in /srv/www/htdocs/web201/html/backup/backup.php on line 145
Ich weiß, der Speicher reicht nicht aus un deshlab wird das Script abgebrochen.
Kann man das irgendwie umgehen, ohne die php.ini zu ändern?
Hab leider keinen eigenen Server und kann es deshalb nicht ändern.
Als Idee bleibt also nur die Tabellen einzeln zu exportieren und nicht die ganze Datenbank auf einmal.
Gibt es bessere Möglichkeiten?
habe da ein Backupscript in PHP welches meine Datenbank zu einem bestimmten Zeitpunkt per Cron sichert.
Hier das Script:
// mySQL - variables
$dbhost="localhost";
$dbuser="$db_user";
$dbpass="$db_passwd";
$dbname[]="$db_name";
// number of backups to keep
$backups = 100;
// hours between backups
$interval = 48;
// 1 only with ZLib support, else change value to 0
$compression = 1;
// full path to phpMyBackup
$path = $_SERVER['DOCUMENT_ROOT']."/backup/";
flush();
$conn = mysql_connect($dbhost,$dbuser,$dbpass)
or die(mysql_error());
$path = $path . "dump/";
//if (!is_dir($path)) mkdir($path, 0777);
function get_def($dbname, $table) {
global $conn;
$def = "";
// $def .= "DROP TABLE IF EXISTS $table;\n";
$def .= "CREATE TABLE $table (\n";
$result = mysql_db_query($dbname, "SHOW FIELDS FROM $table",$conn);
while($row = mysql_fetch_array($result)) {
$def .= " $row[Field] $row[Type]";
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) {
global $conn;
$content="";
if (1=1) {} else {
$result = mysql_db_query($dbname, "SELECT * FROM $table",$conn);
while($row = mysql_fetch_row($result)) {
$insert = "INSERT INTO $table 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 .= ");\n";
$content .= $insert;
}
return $content;
}
}
if ($compression==1) $filetype = "sql.gz";
else $filetype = "sql";
//if (filemtime($path . "0.$filetype") < time() - $interval * 3600 && !eregi("/restore\.",$PHP_SELF)) {
$cur_time=date("Y-m-d H:i");
$newfile="# Dump created with 'phpMyBackup v.$version' on $cur_time\r\n";
while (list(,$val) = each($dbname)) {
//alte backups renamen
for ($i = $backups-1; $i > 0; $i--) {
$j = $i-1;
$oldname = $path.$val. $j . ".$filetype";
$newname = $path.$val. $i . ".$filetype";
@rename($oldname,$newname);
}
//backuppen
$tables = mysql_list_tables($val,$conn);
$num_tables = @mysql_num_rows($tables);
$i = 0;
while($i < $num_tables) {
$table = mysql_tablename($tables, $i);
$newfile .= "\n# ----------------------------------------------------------\n#\n";
$newfile .= "# structur for table '$table'\n#\n";
$newfile .= get_def($val,$table);
$newfile .= "\n\n";
$newfile .= "#\n# data for table '$table'\n#\n";
145: $newfile .= get_content($val,$table);
$newfile .= "\n\n";
$i++;
}
if ($compression==1) {
$date = date("d-m-Y-H-i");
$fp = gzopen($path.$val."-$date.$filetype","w9");
// $fp = tmpfile();
gzwrite ($fp,$newfile);
gzclose ($fp);
} else {
$fp = fopen ($path.$val."0.$filetype","w");
fwrite ($fp,$newfile);
fclose ($fp);
}
}
Nun bekomme ich aber seit neuestem eine Fehlermeldung, hier:
Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 4015756 bytes) in /srv/www/htdocs/web201/html/backup/backup.php on line 145
Ich weiß, der Speicher reicht nicht aus un deshlab wird das Script abgebrochen.
Kann man das irgendwie umgehen, ohne die php.ini zu ändern?
Hab leider keinen eigenen Server und kann es deshalb nicht ändern.
Als Idee bleibt also nur die Tabellen einzeln zu exportieren und nicht die ganze Datenbank auf einmal.
Gibt es bessere Möglichkeiten?