Automatische backup grote database

[Ger]

Mijn database is momenteel zo'n 40MB en groeiende. Logischerwijs wil ik iedere dag een backup hebben en mijn automatiseerdershart en mijn luie reet willen dat natuurlijk liefst automatisch laten verlopen.

Hoe doen anderen met grote databases dat? Met een gewoon backupscriptje krijg ik een time-out (want groter dan 16 MB).

[ElbertF]

Ik zou aan je host vragen of zij dit kunnen regelen (als ze dat niet al doen). Vaak synchroniseren ze gewoon de harde schijf met een andere, anders heb je er nog niet veel aan wanneer deze kapot gaat.

[Derky]

Mijn database is momenteel zo'n 40MB en groeiende. Logischerwijs wil ik iedere dag een backup hebben en mijn automatiseerdershart en mijn luie reet willen dat natuurlijk liefst automatisch laten verlopen.

Hoe doen anderen met grote databases dat? Met een gewoon backupscriptje krijg ik een time-out (want groter dan 16 MB).

Ik gebruik een PHP back-up script van Lontronics.nl alleen die site is nu weg.
Je laat het aanroepen door een cronjob iedere dag.
Het onderdrukt time-out en user_abort en de database die ik er iedere dag mee laat back-uppen is 150 MB (berichten: 115.000 onderwerpen: 13.000 gebruikers: 5.600) en die wordt ingepakt tot 30 MB.
Het maakt eerst een .sql back-up en daarna wordt er een .gz van gemaakt, er zit ook een optie bij om het te laten uploaden via FTP of om het te laten mailen. (als het bestand dus nog niet zo groot is).

Als je het script wil moet je het maar even laten weten.

Ik zou aan je host vragen of zij dit kunnen regelen (als ze dat niet al doen). Vaak synchroniseren ze gewoon de harde schijf met een andere, anders heb je er nog niet veel aan wanneer deze kapot gaat.

Als ze harde schijf synchroniseren heb je nog geen database back-up die je kan importeren, alleen de mappen van mysql.
Vaak maken hostingbedrijven wel back-ups maar dan alleen voor het eigen gebruik, als je als klant daarbij wil moet je waarschijnlijk bijbetalen.

[Ger]

Derky, ik ben best geïnteresseerd in dat script. Kun je dat eens mailen (neem aan dat je mijn mailadres kunt zien als mod?) of een codedump plaatsen?

En ik kan iedere dag wel een (.sql.gz) backup downloaden via cPanel, maar ik wil ergens onafhankelijk van de host ook een backup bewaren. Zeg maar dat als bij 2uhosting brand uitbreekt, ik nog steeds een backup beschikbaar heb.

[Ger]

Het is gelukt met het script van Derky!

Ik ben nog even aan het stoeien geweest, aangezien ik iedere keer op de cron-deamon de complete php-code met bij iedere regel een foutmelding kreeg. Snapte er niets van (ben ook een totale rookie met cron). Blijkt dus dat je die crontab eerst moet vertellen dat het om php gaat...

Toen ik dat wist, was het appeltje-eitje. Ik krijg nu fijntjes iedere nacht een backup op mijn Gmail-adres. Derky, bedankt!

[Kevin]

Ik heb ook wel intresse in het sciptje, hopenlijk kom ik eruit.

[Derky]

Aanroepen met file.php?exec

Code: Selecteer alles

<?php
@set_time_limit (0); 
@ignore_user_abort (true); 
// **************************************************************************
//
// LONTRONICS MYSQL BACKUP SCRIPT V1.04, MORE INFO AT: HTTP://WWW.LONTRONICS.NL
//
// 19-05-2006 V1.05_ALPHA ADDITIONS:
// Adjustable path setting removed; the backup file is stored in the script directory
// Fine adjustment of the mail script to prevent it handled as spam
// - some bugfixes:
// absolute path instead of relative path to solve errors with php-isapi
// prevent timeouts on large databases
// some changes in the mailroutine
//
// 26-02-2006 V1.04 ADDITIONS:
// - Before starting gzip compression, check if it is supported, otherwise give an error message
// - Size of backup attachment is added to mailtext
// - When backup size is exceeding a defined size, it will be stored on server instead of mailing it.
//   A mail will be sent to tell the defined maximum size is exceeded
// Scripts changes to make life a little easier
// - some bugfixes:
//   mimetypes in mail
//   error supression in gz compression routine
//   old mailit routine removed from code
//
// 22-08-2005 V1.03 ADDITIONS:
// - Small codechange for maximum mySql compatibility
//
// 22-07-2005 V1.02 ADDITIONS:
// - FTP function added to copy the backup to another webspace
// - Styles added for statistics on screen
// - Most parameters are checked now before running the script
// - Some small code changes
// - Execution only with 'http://mysite.com/lontronics_sqlbackup.php?exec=1'
// - $path parameter is working now
// - Backup name is same as used in PhpMyAdmin now
//
// 20-07-2005: V1.01 ADDITIONS:
// - Language setting for dutch, english and german available
// - Language settings are used for the complete script now
// - Reorganized the parameters for easier initial settings
//
// 12-05-2005: V1.00 OPTIONS:
// - Database backup on server or with mailfunction
// - Database compression in ZIP and GZ format
// - Statistics on screen
// - Translation possible of parameters in statistics
//
// **************************************************************************
//
// SHORT HELP CONTENTS
//
// 1. Initial settings
//    Before running the script it is important to do some initial settings.
//    It is possible to make a backup without sending it by mail or ftp, and store it on the server where the backup is made.
//    Therefor it is enough to add your database settings below.
//    When you want to send the backup by email or ftp, you have to fill in those settings too.
//    Because there is an explanation behind every setting, I will not give a more detailed description of those.
//    When something is not clear, please ask me on the lontronics support forum at http://www.lontronics.nl
//
// 2. How to run the backup script
//    You have to store the script on the server where the mysql database is you want to make a backup from.
//    Further it is important that the server is supporting php to run the script.
//    To be sure the script is not activated by some kind of robots or people searching around in your files,
//    You have to add ?exec=1 behind the name of the script to start it.
//    Ofcourse you can change the name of the script, with the original name it should be lontronics_sqlbackup_V105.php?exec=1 to execute it.
//
// 3. Support
//    More support is available on the supportforum of http://www.lontronics.nl
//    Register yourself and I will be happy to help you.    
//
// **************************************************************************
// CHANGE THE FOLLOWING SETTINGS TO YOUR NEEDS:
// **************************************************************************
//
// DATABASE SETTINGS:
//
$dbhost          = "localhost";                    // usually localhost
$dbuser          = "";			   // the username
$dbpass          = "";		   // password for database
$dbname          = "";			   // database name
//
// SETTING NEEDED FOR EMAIL FUNCTION
// (you can leave those settings empty when the mail function is not used):
//
$email           = "";  // Email adress to send the backup to
$name_email      = "";                   // Real name to send the backup to
$from            = "";           // Email address message will show as coming from
$name_from       = "";     // Real name as coming from
$maxmailsize     = "100000000";                         // Maximum size of attachment in kb. When larger, the file will be saved on the server
//
// SETTINGS NEEDED FOR FTP FUNCTION
// (you can leave those settings empty when the ftp function is not used):
//
$ftp_host        = "";                             // FTP Host to send the backup to
$ftp_user_name   = "";                             // FTP User name
$ftp_user_pass   = "";                             // FTP Password
$ftp_dir         = "";                     	   // FTP Directory where backup must be copied to (directory must exist!)
//
// OTHER SETTINGS:
//
$use_zip         = 1;                              // Use compression to make the backup smaller          0 = No      , 1 = GZIP  , 2 = ZIP (ZIP is experimental!!)
$use_mail        = 0;                              // Mail the file                                       0 = No      , 1 = Yes
$use_ftp         = 0;                              // Use ftp to upload the file to another webspace      0 = No      , 1 = Yes
$language        = 1;                              // Here you can set the language                       0 = English , 1 = Dutch , 2 = German
//
// NORMALLY YOU CAN LEAVE THE FOLLOWING SETTINGS:
//
$del_file        = 0;                              // Delete file after it is sent by mail or ftp         0 = No , 1 = Yes
$use_stats       = 1;                              // Show information on screen after making the backup  0 = No , 1 = Yes
$structure_only  = false;                          // set FALSE to get a complete backup with table content
$filename        = date('d-m-y')."-".$dbname.".sql"; // name of the uncompressed sql backup; PhpMyAdmin standard = date('ymd')."-".$dbname.".sql"

set_time_limit(0);                                 // No php script timeout to prevent timeouts on large databases
ini_set("max_execution_time","false");             // No php script timeout to prevent timeouts on large databases
ignore_user_abort(true);                           // Prevent the browser interrupting the script

$ThisFileName = basename(__FILE__);                // get the file name
$path = str_replace($ThisFileName,"",__FILE__);    // get the directory path

//
//
// **************************************************************************
// DO NOT CHANGE ANYTHING BELOW HERE !!!
// **************************************************************************
?>
<head>
<meta name="ROBOTS" content="noINDEX, noFOLLOW">
<style type="text/css">
table
{
	background-color: #FFFFFF; 
	border: 0px;
}
body,td,th
{
	font-family: Arial, Helvetica, Verdana, sans-serif;
	font-size: 12px;
	line-height: 14px;
	height: 14px;
	color: #333333;
	background-color: #FFFFFF;
	border: 0px;
	vertical-align: top;
	text-align: left;
}
.head
{
	font-family: Arial, Helvetica, Verdana, sans-serif;
	font-size: 12px;
	line-height: 14px;
	height: 14px;
	font-weight: bold;
	color: #600079;
	background-color: #FFFFFF;
	border: 0px;
	vertical-align: top;
	text-align: left;
}
</style>
</head>
<?php
// VERSION NUMBER:
$version = "V1.05_ALPHA";


// CHECK IF ACCES IS ALLOWED:
if (!isset($_GET['exec']))
{
	die("hacking attempt");
}

// CHECK PARAMETERS:
if (($dbuser == "") || ($dbpass == "") || ($dbname == ""))
{
	die("DATABASE settings are not correct!");
}
if (($use_zip < 0) || ($use_zip > 2))
{
	die("USE_ZIP setting is not correct!");
}
if (($use_mail != 0) && ($use_mail != 1))
{
	die("USE_MAIL setting is not correct!");
}
if (($del_file != 0) && ($del_file != 1))
{
	die("DEL_FILE setting is not correct!");

}
if (($use_stats != 0) && ($use_stats != 1))
{
	die("USE_STATS setting is not correct!");
}
if (($language < 0) || ($language > 2))
{
	die("LANGUAGE setting is not correct!");
}
if (($structure_only != false) && ($structure_only != true))
{
	die("STRUCTURE_ONLY setting is not correct!");
}
if ($use_mail == 1)
{
	if (!eregi('[A-Za-z0-9_-]+([.]{1}[A-Za-z0-9_-]+)*@[A-Za-z0-9-]+([.]{1}[A-Za-z0-9-]+)+',$email))
	{
		die("EMAIL TO setting is not correct!");
	}
	if (!eregi('[A-Za-z0-9_-]+([.]{1}[A-Za-z0-9_-]+)*@[A-Za-z0-9-]+([.]{1}[A-Za-z0-9-]+)+',$from))
	{
		die("EMAIL FROM setting is not correct!");
	}
}
if ((use_ftp == 1) && (($ftp_host == "") || ($ftp_user_name == "") || ($ftp_user_pass == "")))
{
	die("FTP PARAMETERS are needed for FTP function!");
}


// FILE NAME SETTINGS FOR .GZ AND .ZIP FILES:
$filecompgz	= $filename.".gz";
$filecompzip	= $filename.".zip";


// TRANSLATIONS:
if ($language == 0)	// ENGLISH
{
	$trans_tabel     = "Table name";
	$trans_bname     = "Backup file name";
	$trans_size      = "File size";
	$trans_made      = "Made at";
	$trans_mailed    = "Mailed to";
	$trans_nomail    = "Mail function is disabled";
	$trans_subject   = "MySql Backup dated: ".date('d-M-Y H:i');
	$trans_textline  = "The attachment of this mail is the MySQL backup";
	$trans_ftpadress = "Saved to FTP adress";
	$trans_ftpuser   = "Username";
	$trans_noftp     = "FTP function is disabled";
	$trans_local     = "Saved on server to";
	$trans_nolocal   = "Not saved on server";
	$trans_gentime   = "Backup generation time:";
	$trans_host      = "Host:";
	$trans_server    = "Server version:";
	$trans_database  = "Database:";
	$trans_structure = "Table structure for table";
	$trans_entries   = "Data entries for table";
	$trans_err_size  = "MySQL backup size is too large for sending by email and is stored on the server where the backup script is executed";
}
if ($language == 1)	// DUTCH
{
	$trans_tabel     = "Tabelnaam";
	$trans_bname     = "Backup naam";
	$trans_size      = "Bestandsgrootte";
	$trans_made      = "Gemaakt op";
	$trans_mailed    = "Gemaild naar";
	$trans_nomail    = "Mail-funcie is niet ingeschakeld";
	$trans_subject   = "MySql Backup van: ".date('d-M-Y H:i');
	$trans_textline  = "Deze mail bevat als bijlage de MySQL backup";
	$trans_ftpadress = "Opgeslagen op FTP adres";
	$trans_ftpuser   = "Gebruikersnaam";
	$trans_noftp     = "FTP-functie is niet ingeschakeld";	
	$trans_local     = "Opgeslagen op server op";
	$trans_nolocal   = "Niet op server opgeslagen";
	$trans_gentime   = "Backup gemaakt op:";
	$trans_host      = "Host:";
	$trans_server    = "Server versie:";
	$trans_database  = "Database:";
	$trans_structure = "Tabelinhoud voor tabel";
	$trans_entries   = "Data voor tabel";
	$trans_err_size  = "MySQL backup was te groot om als bijlage te sturen en is op de server opgeslagen waar het backupscript is uitgevoerd";
}
if ($language == 2)	// GERMAN
{
	$trans_tabel     = "Tabellenahme";
	$trans_bname     = "Backupnahme";
	$trans_size      = "BestandsgroBe";
	$trans_made      = "Produziert am";
	$trans_mailed    = "Uber Mail geschickt an";
	$trans_nomail    = "Mail-Funktion ausgeschaltet";
	$trans_subject   = "MySql Backup von: ".date('d-M-Y H:i');
	$trans_textline  = "Dieses Mail hat eine Anlage MySQL backup";
	$trans_ftpadress = "Gespeichert auf die FTP Adresse";
	$trans_ftpuser   = "Benutzer";	
	$trans_noftp     = "FTP-Funktion ausgeschaltet";	
	$trans_local     = "Gespeichert auf Server auf";
	$trans_nolocal   = "Nicht auf Server gespeichert";
	$trans_gentime   = "Backup produziert am:";
	$trans_host      = "Host:";
	$trans_server    = "Server Version:";
	$trans_database  = "Database:";
	$trans_structure = "Tabeleinhalt von Tabelle";
	$trans_entries   = "Dataeinhalt fur Tabelle";
	$trans_err_size  = "MySQL Backup war zu groB fuer Email und ist auf dem Server gespeichert wo das Backupscript ausgefuehrt ist";
}


// THE REAL SCRIPT STARTS HERE!!!

// Controleer of er vandaag al een back-up is gemaakt, zo ja geef dan een error en stop het script
if (file_exists($path.$filecompgz))
{
	echo 'Er is vandaag al een back-up gemaakt met de naam <b>' . $filecompgz . '</b>.<br />Het back-up script wordt afgebroken.';
	die;
};

// DEFINE CRLF:
$crlf = "\n";
if (strstr($HTTP_USER_AGENT, 'Win'))
{
	$crlf = "\r\n";
}
else if (strstr($HTTP_USER_AGENT, 'Mac'))
{
	$crlf = "\r";
}
else
{
	$crlf = "\n";
}


// THE BACKUP ROUTINE:

// here we test our database connection
$con=@mysql_connect($dbhost,$dbuser, $dbpass) or die("Could not connect");  
$db=@mysql_select_db($dbname,$con) or die("Could not select db");

if (strval($filename)!="") $fptr=fopen($filename,"w"); else $fptr=false;

// here we check MySQL Version
$result=@mysql_query("SELECT VERSION() AS version"); 
if ($result != FALSE && @mysql_num_rows($result) > 0)
{
	$row   = @mysql_fetch_array($result);
	$match = explode('.', $row['version']);
}
else
{
	$result=@mysql_query("SHOW VARIABLES LIKE \'version\'"); 
	if ($result != FALSE && @mysql_num_rows($result) > 0)
	{
		$row   = @mysql_fetch_row($result);
		$match = explode('.', $row[1]);
	}
}
if (!isset($match) || !isset($match[0]))
{
	$match[0] = 3;
}
if (!isset($match[1]))
{
	$match[1] = 21;
}
if (!isset($match[2]))
{
	$match[2] = 0;
}
if(!isset($row))
{
	$row = '3.21.0';
}
define('MYSQL_INT_VERSION', (int)sprintf('%d%02d%02d', $match[0], $match[1], intval($match[2])));
define('MYSQL_STR_VERSION', $row['version']);
unset($match);

$maketime = date("M j, Y");
$sql = "-- Lontronics SQL Dump script $version".$crlf;
$sql.= "--".$crlf;
$sql.= "-- $trans_gentime $maketime".$crlf;
$sql.= "-- $trans_host $dbhost".$crlf;
$sql.= "-- $trans_server ".MYSQL_STR_VERSION.$crlf;
$sql.= "--".$crlf;
$sql.= "-- $trans_database $dbname".$crlf;
$sql.= "--".$crlf;
out($fptr, $sql);

//set up database; Unmark the next two lines if you want to create the table too, otherwise the table for restore must already exist
// $sql = "create database $dbname".$crlf.$crlf;
// out($fptr, $sql);

$res=@mysql_list_tables($dbname);  
$nt=@mysql_num_rows($res);  

for ($a=0;$a<$nt;$a++)
{
	$row=mysql_fetch_row($res);  
	$tablename=$row[0];

	//start building the table creation query
	$sql = $crlf;
	$sql.= "-- --------------------------------------------------------".$crlf;
	$sql.= "--".$crlf;
	$sql.= "-- $trans_structure $tablename".$crlf;
	$sql.= "--".$crlf.$crlf;

	// For MySQL < 3.23.20  
	if (MYSQL_INT_VERSION >= 32321) 
	{
		$result=mysql_query("SHOW CREATE TABLE $tablename");
		if ($result != FALSE && mysql_num_rows($result) > 0)
		{
			$tmpres = mysql_fetch_array($result);
			$pos = strpos($tmpres[1], ' (');
			$tmpres[1] = substr($tmpres[1], 0, 13) . "`" . $tmpres[0] . "`" . substr($tmpres[1], $pos);
			$sql .= $tmpres[1].";".$crlf.$crlf;
		}
		mysql_free_result($result);
	}
	else
	{ 
		$sql.="CREATE TABLE $tablename(".$crlf;  
		$result=mysql_query("show fields  from $tablename",$con);  

		while ($row = mysql_fetch_array($result))
		{
			$sql .= "  ".$row['Field'];
			$sql .= ' ' . $row['Type'];
			if (isset($row['Default']) && $row['Default'] != '')
			{
				$sql .= ' DEFAULT \'' . $row['Default'] . '\'';
			}
			if ($row['Null'] != 'YES')
			{
				$sql .= ' NOT NULL';
			}
			if ($row['Extra'] != '')
			{
				$sql .= ' ' . $row['Extra'];
			}
			$sql .= ",".$crlf;
		}
		mysql_free_result($result);
		$sql = ereg_replace(',' . $crlf . '$', '', $sql);
		$result = mysql_query("SHOW KEYS FROM $tablename");
		while ($row = mysql_fetch_array($result))
		{
			$ISkeyname    = $row['Key_name'];
			$IScomment  = (isset($row['Comment'])) ? $row['Comment'] : '';
			$ISsub_part = (isset($row['Sub_part'])) ? $row['Sub_part'] : '';
			if ($ISkeyname != 'PRIMARY' && $row['Non_unique'] == 0)
			{
				$ISkeyname = "UNIQUE|$kname";
			}
			if ($IScomment == 'FULLTEXT')
			{
				$ISkeyname = 'FULLTEXT|$kname';
			}
			if (!isset($index[$ISkeyname]))
			{
				$index[$ISkeyname] = array();
			}
			if ($ISsub_part > 1)
			{
				$index[$ISkeyname][] = $row['Column_name'] . '(' . $ISsub_part . ')';
			}
			else
			{
				$index[$ISkeyname][] = $row['Column_name'];
			}
		} 
		mysql_free_result($result);

		while (list($x, $columns) = @each($index))
		{
			$sql     .= ",".$crlf;
			if ($x == 'PRIMARY')
			{
				$sql .= '  PRIMARY KEY (';
			}
			else if (substr($x, 0, 6) == 'UNIQUE')
			{
				$sql .= '  UNIQUE ' . substr($x, 7) . ' (';
			}
			else if (substr($x, 0, 8) == 'FULLTEXT')
			{
				$sql .= '  FULLTEXT ' . substr($x, 9) . ' (';
			}
			else
			{
				$sql .= '  KEY ' . $x . ' (';
			}
			$sql     .= implode($columns, ', ') . ')';
		} 
		$sql .=  $crlf.");".$crlf;
	} 
	out($fptr, $sql);

	if ($structure_only == FALSE)
	{
		$sql = $crlf;
		$sql.= "--".$crlf;
		$sql.= "-- $trans_entries $tablename".$crlf;
		$sql.= "--".$crlf.$crlf;
		out($fptr, $sql);

		// here we get table content
		$result = mysql_query("SELECT * FROM  $tablename");
		$fields_cnt   = mysql_num_fields($result);

		while ($row = mysql_fetch_row($result))
		{
			$table_list     = '(';
			for ($j = 0; $j < $fields_cnt; $j++)
			{
				$table_list .= mysql_field_name($result, $j) . ', ';
			}
			$table_list = substr($table_list, 0, -2);
			$table_list     .= ')';

			$sql = 'INSERT INTO ' . $tablename . ' VALUES (';

			for ($j = 0; $j < $fields_cnt; $j++)
			{
				if (!isset($row[$j]))
				{
					$sql .= ' NULL, ';
				}
				else if ($row[$j] == '0' || $row[$j] != '')
				{
					$type = mysql_field_type($result, $j);

					// a number
					if ($type == 'tinyint' || $type == 'smallint' || $type == 'mediumint' || $type == 'int' || $type == 'bigint'  ||$type == 'timestamp')
					{
						$sql .= $row[$j] . ', ';
					}

					// a string
					else
					{
						$dummy  = '';
						$srcstr = $row[$j];
						for ($xx = 0; $xx < strlen($srcstr); $xx++)
						{
							$yy = strlen($dummy);
							if ($srcstr[$xx] == '\\')   $dummy .= '\\\\';
							if ($srcstr[$xx] == '\'')   $dummy .= '\\\'';
							if ($srcstr[$xx] == "\x00") $dummy .= '\0';
							if ($srcstr[$xx] == "\x0a") $dummy .= '\n';
							if ($srcstr[$xx] == "\x0d") $dummy .= '\r';
							if ($srcstr[$xx] == "\x1a") $dummy .= '\Z';
							if (strlen($dummy) == $yy)  $dummy .= $srcstr[$xx];
						}
						$sql .= "'" . $dummy . "', ";
					}
				}
				else
				{
					$sql .= "'', ";
				}
			}
			$sql = ereg_replace(', $', '', $sql);
			$sql .= ");".$crlf;
			out($fptr, $sql);
		} 
		mysql_free_result($result);
	} 
}
if ($fptr!=false) fclose($fptr);


// Compress the backup file to the specified format

switch ($use_zip)
{
	case "1":	// .gz compression
		$fp = @fopen($path .$filename,"rb");
		if ($fp)
		{
			if (file_exists($path.$filecompgz))
			{
				unlink($path.$filecompgz);
			}
			$zp = @gzopen($path.$filecompgz, "wb9");
			if (!$zp)
			{
				die("<br><br>Error:<br>Probably GZ Compression is not supported on this server.<br>Failed opening $path$filecompgz.<br><br>The sql file $path$filename is stored on the server where the backup script is executed");
			}
			else
			{
				while(!feof($fp))
				{
					$data=fgets($fp);
					gzwrite($zp,$data);
				}
				gzclose($zp);	
				fclose($fp);
				unlink($path.$filename);
			}
		}
		$filename = $filecompgz;
		$mailapplic = "application/x-gzip";
		break;
	case "2":	// .zip compression (class zipfile is used)
		$zipadd = $path.$filename;
		$zipout = $path.$filecompzip;
		$ziper = new zipfile();
		$ziper->addFiles(array("$zipadd"));
		$ziper->output("$zipout");
		unlink($path.$filename);
		$filename = $filecompzip;
		$mailapplic = "application/zip";			
		break;
	default:
		$mailapplic = "text/x-sql";
}

// email the sql backup to specified adress
if($use_mail == 1)
{
	$mime_boundary = md5(time());
	$f_size = number_format((filesize($path.$filename) / 1024),2);
	$attachmentname = array_pop(explode("/", $filename));	// remove path for the attachment name
//Tijdelijk effe uitgezet
	/*	if ($f_size > $maxmailsize)
	{
		echo ("<br><br>$path$attachmentname ($f_size kb) $trans_err_size...");

		$message = "$attachmentname ($f_size kb) $trans_err_size";

		$headers = "To: $name_email <$email>\r\n";
		$headers .= "From: $name_from <$from>\r\n";
		$headers .= "Reply-To: $name_from <$from>\r\n";

		mail($email, $trans_subject, $message, $headers);

		die();
	}
	else
	{
		*/

		$message = "$trans_textline $attachmentname ($f_size kb)";

		$data = chunk_split(base64_encode(implode(file($path . $filename),'')));

		$headers = "To: $name_email <$email>\r\n";
		$headers .= "From: $name_from <$from>\r\n";
		$headers .= "Reply-To: $name_from <$from>\r\n";
		$headers .= "MIME-Version: 1.0\r\n";
		$headers .= "Content-type: multipart/mixed; boundary=\"{$mime_boundary}\"\r\n";

		$content = "This is a multi-part message in MIME format.\r\n\r\n";
		$content.= "--{$mime_boundary}\r\n";
		$content.= "Content-Type: text/plain; charset=\"iso-8859-1\"\r\n";
		$content.= "Content-Transfer-Encoding: 7bit\r\n\r\n";
		$content.= $message."\r\n\r\n";
		$content.= "--{$mime_boundary}\r\n";
		$content.= "Content-Type: $mailapplic; name=\"$filename\"\r\n";
		$content.= "Content-Transfer-Encoding: base64\r\n";
		$content.= "Content-Disposition: attachment; filename=\"$filename\"\r\n\r\n";
		$content.= $data."\r\n\r\n";
		$content.= "--{$mime_boundary}--";

		mail($email, $trans_subject, $content, $headers);
//	}
}

// use ftp to copy the sql backup to another webspace
if($use_ftp == 1)
{
	$ftp_err1 = "FTP Connection has failed!<br>Attempted to connect to $ftp_host for user $ftp_user_name";
	
	$hostip = gethostbyname($ftp_host);
	$conn_id = ftp_connect($hostip);
	$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);

	// Turn passive mode on
	ftp_pasv ( $conn_id, true );

	if ((!$conn_id) || (!$login_result)) die($ftp_err1);
	
	ftp_chdir ($conn_id, $ftp_dir);	
	ftp_put($conn_id, $filename, $path . $filename, FTP_BINARY);
	ftp_close($conn_id);
}

// View statistics
if($use_stats == 1)
{
	$f_size		= number_format((filesize($path.$filename) / 1024),2);
	$f_time		= date ("F d Y H:i:s", filemtime($path.$filename));
	$ftp_txt	= $ftp_host.$ftp_dir."<br />".$trans_ftpuser." : ".$ftp_user_name;
	$local_adress	= "http://".$_SERVER['HTTP_HOST'].dirname($_SERVER['PHP_SELF'])."/".$path;
	
	if($use_mail == 0)
	{
		$email=$trans_nomail;
	}
	if($use_ftp == 0)
	{
		$ftp_txt=$trans_noftp;
	}
	if(($use_mail == 1) || ($use_ftp == 1))
	{
		$local_adress = $trans_nolocal;
	}
	echo	"<table>";
	echo	"<tr><td colspan=3 class=head>LONTRONICS MYSQL BACKUP SCRIPT $version</td></tr>";
	echo	"<tr><td colspan=3 class=head>More info: <a href=http://www.lontronics.nl class=head>http://www.lontronics.nl</a><br /><br /></td></tr>";
	echo	"<tr><td>$trans_tabel</td><td> : </td><td>$dbname</td></tr>";
	echo	"<tr><td>$trans_bname</td><td> : </td><td>$filename</td></tr>";
	echo	"<tr><td>$trans_size</td><td> : </td><td>$f_size KB</td></tr>";
	echo	"<tr><td>$trans_made</td><td> : </td><td>$f_time</td></tr>";
	echo	"<tr><td>$trans_mailed</td><td> : </td><td>$email</td></tr>";
	echo	"<tr><td>$trans_ftpadress</td><td> : </td><td>$ftp_txt</td></tr>";
	echo	"<tr><td>$trans_local</td><td> : </td><td>$local_adress</td></tr>";	
	echo	"</table>";
}

// Delete file after mail and/ or ftp
if(($del_file == 1) && (($use_mail == 1) || ($use_ftp == 1)))
{
	unlink($path . $filename);
}


// USED FUNCTIONS:

function out($fptr,$s)
{
	if ($fptr==false) echo("$s"); else fputs($fptr,$s);
}

class zipfile
{
	var $datasec      = array();
	var $ctrl_dir     = array();
	var $eof_ctrl_dir = "\x50\x4b\x05\x06\x00\x00\x00\x00";
	var $old_offset   = 0;
	function unix2DosTime($unixtime = 0)
	{
		$timearray = ($unixtime == 0) ? getdate() : getdate($unixtime);
		if ($timearray['year'] < 1980)
		{
			$timearray['year']    = 1980;
			$timearray['mon']     = 1;
			$timearray['mday']    = 1;
			$timearray['hours']   = 0;
			$timearray['minutes'] = 0;
			$timearray['seconds'] = 0;
		}
		return (($timearray['year'] - 1980) << 25) | ($timearray['mon'] << 21) | ($timearray['mday'] << 16) |
                ($timearray['hours'] << 11) | ($timearray['minutes'] << 5) | ($timearray['seconds'] >> 1);
	}
	function addFile($data, $name, $time = 0)
	{
		$name     = str_replace('\\', '/', $name);

		$dtime    = dechex($this->unix2DosTime($time));
		$hexdtime = '\x' . $dtime[6] . $dtime[7] . '\x' . $dtime[4] . $dtime[5] . '\x' . $dtime[2] . $dtime[3] . '\x' . $dtime[0] . $dtime[1];
		eval('$hexdtime = "' . $hexdtime . '";');

		$fr   = "\x50\x4b\x03\x04";
		$fr   .= "\x14\x00";            // ver needed to extract
		$fr   .= "\x00\x00";            // gen purpose bit flag
		$fr   .= "\x08\x00";            // compression method
		$fr   .= $hexdtime;             // last mod time and date

		// "local file header" segment
		$unc_len = strlen($data);
		$crc     = crc32($data);
		$zdata   = gzcompress($data);
		$zdata   = substr(substr($zdata, 0, strlen($zdata) - 4), 2); // fix crc bug
		$c_len   = strlen($zdata);
		$fr      .= pack('V', $crc);             // crc32
		$fr      .= pack('V', $c_len);           // compressed filesize
		$fr      .= pack('V', $unc_len);         // uncompressed filesize
		$fr      .= pack('v', strlen($name));    // length of filename
		$fr      .= pack('v', 0);                // extra field length
		$fr      .= $name;

		// "file data" segment
		$fr .= $zdata;

		// "data descriptor" segment (optional but necessary if archive is not served as file)
		$fr .= pack('V', $crc);                 // crc32
		$fr .= pack('V', $c_len);               // compressed filesize
		$fr .= pack('V', $unc_len);             // uncompressed filesize

		// add this entry to array
		$this -> datasec[] = $fr;

		// now add to central directory record
		$cdrec = "\x50\x4b\x01\x02";
		$cdrec .= "\x00\x00";                // version made by
		$cdrec .= "\x14\x00";                // version needed to extract
		$cdrec .= "\x00\x00";                // gen purpose bit flag
		$cdrec .= "\x08\x00";                // compression method
		$cdrec .= $hexdtime;                 // last mod time & date
		$cdrec .= pack('V', $crc);           // crc32
		$cdrec .= pack('V', $c_len);         // compressed filesize
		$cdrec .= pack('V', $unc_len);       // uncompressed filesize
		$cdrec .= pack('v', strlen($name) ); // length of filename
		$cdrec .= pack('v', 0 );             // extra field length
		$cdrec .= pack('v', 0 );             // file comment length
		$cdrec .= pack('v', 0 );             // disk number start
		$cdrec .= pack('v', 0 );             // internal file attributes
		$cdrec .= pack('V', 32 );            // external file attributes - 'archive' bit set

		$cdrec .= pack('V', $this -> old_offset ); // relative offset of local header
		$this -> old_offset += strlen($fr);

		$cdrec .= $name;

		// optional extra field, file comment goes here
		// save to central directory
		$this -> ctrl_dir[] = $cdrec;
	}
	function file()
	{
		$data    = implode('', $this -> datasec);
		$ctrldir = implode('', $this -> ctrl_dir);

		return
		$data .
		$ctrldir .
		$this -> eof_ctrl_dir .
		pack('v', sizeof($this -> ctrl_dir)) .  // total # of entries "on this disk"
		pack('v', sizeof($this -> ctrl_dir)) .  // total # of entries overall
		pack('V', strlen($ctrldir)) .           // size of central dir
		pack('V', strlen($data)) .              // offset to start of central dir
		"\x00\x00";                             // .zip file comment length
	}
	function addFiles($files /*Only Pass Array*/)
	{
		foreach($files as $file)
		{
			if (is_file($file)) //directory check
			{
				$data = implode("",file($file));
				$this->addFile($data,$file);
			}
		}
	}
	function output($file)
	{
		$fp=fopen($file,"w");
		fwrite($fp,$this->file());
		fclose($fp);
	}
}
?>

[Kevin]

Krijg de melding "Could not connect"

[Derky]

Wat zou dat toch betekenen.

Überhaupt naar de code gekeken?

[Kevin]

Ehuh ja wat denk jij nou.

Database gegevens ingevuld, email gegevens, Ftp map aangemaakt en pad ingevuld

aantal zaken aan en uitgeschakeld maar niks.

[Ger]

1. Hoe staat de CHMOD voor het bestand? Heeft het rechten om uit te voeren?
2. Weet je ZEKER dat de databasegegevens (dbhost, dbuser, dbpass, dbname) correct zijn ingevuld?
3. Werkt het script wel als je het in de public_html folder zet en handmatig aanroept?

[Kevin]

1= 644
2= ja div keren nagekeken
3= handmatig oproept ? je bedoelt in de browser intoesten /backup.php?exec , want als ik dat doe krijg ik die foutmelding.

Maar nu had jij het over cron.... dat komt mij helemaal niet bekend voor.

[golem85]

Hoi Mr. Derky,

zelf ben ik eigenaar van http://www.pc-helpforum.be en ik zoek al een hele tijd zo'n script. Ik heb er al heel wat geprobeerd maar nog niks gevonden die prima werkt.

Mocht u zo vriendelijk willen zijn om het script door te sturen dan mag u dit doen naar support@pc-helpforum.be

Mijn database is intussen ook meer dan 100mb en ken het probleem van time-out maar al te best.
Mocht er een functie inzitten die na x aantal dagen backups wist zou dit nog beter zijn maar het is een begin

Bedankt alvast voor uw moeite

Mvg Karel B.

[Derky]

Script staat al hier: viewtopic.php?p=344705#p344705 (ik heb effe de spoiler tags die het verbergen weggehaald)

Als je het op een bepaald tijdstip wil laten doen kan je gebruik maken van de (gratis) service van de site http://www.webcron.org

[Ger]

Ik heb er een Wiki-artikel van gemaakt: http://wiki.phpbb.nl/Automatische_backup
Het script is ietwat gewijzigd om het allemaal wat simpeler te maken (scheelt ± 200 regels) . Ik heb alleen nog geen methode gevonden om het te uploaden, dus nu staat er een huge script in het Wiki-artikel. Is dat erg?

-edit- Oei, ik zie dat het wel een beetje een vertekend beeld geeft van mijn Wiki-coolness. Toch maar even zoeken naar een locatie om het te uploaden...

[golem85]

ik krijg de volgende melding:

Warning: fopen(20-01-08-pchelp_web.sql) [function.fopen]: failed to open stream: Permission denied in /home/pchelp/domains/pc-helpforum.be/public_html/lontronics_sqlbackup_V105.php on line 329

iemand ideeke?

Bedankt alvast

[Coen]

Warning: fopen(20-01-08-pchelp_web.sql) [function.fopen]: failed to open stream: Permission denied in /home/pchelp/domains/pc-helpforum.be/public_html/lontronics_sqlbackup_V105.php on line 329

Het bestand waar naar verwezen word, bestaat niet of heeft niet de juiste permissies om uitgevoerd te worden!

[golem85]

de map waarin dat bestand instaat heeft nogtans schrijfrechten 777
vreemd :s

ps. ff offtopic: mijn postteller blijft op 0 staan hier. Of staat postcount uit in dit forum?

[Ger]

Line 325-329 is:

Code: Selecteer alles

    // here we test our database connection
    $con=@mysql_connect($dbhost,$dbuser, $dbpass) or die("Could not connect"); 
    $db=@mysql_select_db($dbname,$con) or die("Could not select db");

    if (strval($filename)!="") $fptr=fopen($filename,"w"); else $fptr=false;

Lijkt me dus dat de database-gegevens niet correct zijn ingevoerd. Tip: kopieer het vanaf je config.php.

[golem85]

dat dacht ik ook maar dit staat er wel (ff wat verder gekopieerd in de melding:

Code: Selecteer alles

Warning: fopen(20-01-08-pchelp_web.sql) [function.fopen]: failed to open stream: Permission denied in /home/pchelp/domains/pc-helpforum.be/public_html/lontronics_sqlbackup_V105.php on line 329
-- Lontronics SQL Dump script V1.05_ALPHA -- -- Backup gemaakt op: Jan 20, 2008 -- Host: localhost -- Server versie: 5.0.37-standard -- -- Database: pchelp_web -- -- -------------------------------------------------------- -- -- Tabelinhoud voor tabel phpmysqlautobackup -- CREATE TABLE `phpmysqlautobackup` ( `id` int(11) NOT NULL, `version` varchar(6) default NULL, `time_last_run` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Data voor tabel phpmysqlautobackup -- INSERT INTO phpmysqlautobackup VALUES (1, '1.4.0', 1200764136); -- -------------------------------------------------------- -- -- Tabelinhoud voor tabel vbaccess -- CREATE TABLE `vbaccess` ( `userid` int(10) unsigned NOT NULL default '0', `forumid` smallint(5) unsigned NOT NULL default '0', `accessmask` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`userid`,`forumid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Data voor tabel vbaccess -- -- -------------------------------------------------------- -- -- Tabelinhoud voor tabel vbadminhelp -- CREATE TABLE `vbadminhelp` ( `adminhelpid` int(10) unsigned NOT NULL auto_increment, `script` varchar(50) NOT NULL default '', `action` varchar(25) NOT NULL default '', `optionname` varchar(100) NOT NULL default '', `displayorder` smallint(5) unsigned NOT NULL default '1', `volatile` smallint(5) unsigned NOT NULL default '0', `product` varchar(25) NOT NULL default '', PRIMARY KEY (`adminhelpid`), UNIQUE KEY `phraseunique` (`script`,`action`,`optionname`) ) ENGINE=MyISAM AUTO_INCREMENT=5589 DEFAULT CHARSET=latin1; -- -- Data voor tabel vbadminhelp -- INSERT INTO vbadminhelp VALUES (5212, 'subscriptions', 'transactions', 'state', 1, 1, 'vbulletin'); INSERT INTO vbadminhelp VALUES (5211, 'subscriptions', 'transactions', 'start', 1, 1, 'vbulletin'); INSERT INTO vbadminhelp VALUES (5210, 'subscriptions', 'remove', '', 1, 1, 'vbulletin'); INSERT INTO vbadminhelp VALUES (5209, 'subscriptions', 'modify', '', 1, 1, 'vbulletin'); INSERT INTO vbadminhelp VALUES (5208, 'subscriptions', 'find', '', 1, 1, 'vbulletin'); INSERT INTO vbadminhelp VALUES (5207, 'subscriptions', 'edit', '', 1, 1, 'vbulletin'); INSERT INTO vbadminhelp VALUES (5206, 'subscriptions', 'apirem', '', 1, 1, 'vbulletin'); INSERT INTO vbadminhelp VALUES (5205, 'subsc

en zo nog 1000 regels eronder ofzo