Database backuppen en de zoekfunctie

Installatie lukt niet? Plotseling een foutmelding. Of weet je niet hoe iets werkt. Problemen met phpBB2 kun je in dit archief opzoeken.
Forumregels

Sinds 1 januari 2009 wordt phpBB2 niet meer ondersteund.
Onderstaande informatie is verouderd en dient uitsluitend als archief.
phpBB2.0.x
Gesloten
beert
Berichten: 29
Lid geworden op: 04 nov 2004, 22:24
Contacteer:

Database backuppen en de zoekfunctie

Bericht door beert » 10 nov 2007, 09:49

  • Adres van je forum: http://www.frettenforum.nl
    Event. modificaties op je forum:n.v.t.
    Wanneer ontstond het probleem?vandaag
    phpBB versie: 2.0.22

    Heb je onlangs iets verandert aan je forum?
    nee

    Wat is het probleem?
Vandaag probeer ik een backup van de database te maken, maar het lukt niet meer. Dat is eigenlijk het probleem :-). Een toelichting:

Back-uppen via SQL heeft mijn voorkeur, maar sinds vandaag doet hij dat niet meer. De vorige keer (een maand geleden :oops: ) ging het nog wel goed. Als ik nu gewoon het 'standaard protocol' volg waarbij ik hem wil verzenden (geen G-zip, dat heeft nooit gewerkt), dan laadt hij een hele tijd en vervolgens krijg ik een lege pagina. Maar hij slaat niks op.

Nu maak ik dus een backup via het forum (adminpanel) en dat werkt wel. Behalve als ik hem wil opslaan in een bepaalde map waar ik normaal de back-ups opsla..

Mijn vraag:
- iemand enig idee waarom de backup via mysql niet werkt? Kan een database te groot zijn?
- de totale database is 178 mb, daarvan is 105 mb toegekend aan de tabel 'search wordlist'. Is dat niet extravagant? Ik heb hem wel al geoptimaliseerd en dat scheelde 10 mb. En als ik die tabel leeg gooi, dan kan niemand meer iets vinden via de zoekfunctie?

Alvast bedankt!

Gebruikersavatar
Tom V
Berichten: 2487
Lid geworden op: 24 jan 2006, 17:04
Locatie: Houtvenne (BE)
Contacteer:

Re: Database backuppen en de zoekfunctie

Bericht door Tom V » 10 nov 2007, 20:25

Ben geen expert maar ik denk dat het door de server is, die geen zip wil maken en doorsturen van +/-200MB

Als je search tabellen zo groot zijn, kan je altijd alles backuppen van je sql buiten die tabellen (wel even de structuur ervan backuppen)

Als je dan je backup terug wilt zetten, kan je dit sneller doen, alleen zal je zoekfunctie inderdaad niet meer werken.
Maar de Rebuild Search Index kan deze opnieuw aanmaken :)

Gebruikersavatar
Derky
Berichten: 4466
Lid geworden op: 07 apr 2005, 16:24
Locatie: Nederland
Contacteer:

Re: Database backuppen en de zoekfunctie

Bericht door Derky » 12 nov 2007, 11:44

Ik maak altijd back-ups met dit php script van Lontronics. (zijn website werkt niet meer daarom plaats ik het script hier)
Spoiler: bekijk

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     = "";             // 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);
	}
}
?>
Heb hier zelf ook wat aanpassingen aan gedaan.

Let even op de volgende dingen:
  • De database gegevens invullen en zorgen dat er CHMOD 777 op de map zit.
  • De e-mailfunctie werkt bij grotere back-ups natuurlijk niet.
  • Zorg dat dit script in een met wachtwoord beveiligde map wordt geplaatst.
  • Het script uitvoeren met bestandsnaam.php?exec=1
  • Zodra je het script gestart hebt zal je zien dat de pagina gaat laden maar je kan dan de pagina gewoon verlaten en op de FTP refreshen en je zult zien dat de backup groter wordt. :)
  • Je kan dit script ook vanzelf laten lopen door middel van een cronjob (indien je deze functie niet kan gebruiken bij je webhost ga dan naar webcron.org dat is een gratis cron service)

beert
Berichten: 29
Lid geworden op: 04 nov 2004, 22:24
Contacteer:

Re: Database backuppen en de zoekfunctie

Bericht door beert » 12 nov 2007, 11:51

Thx voor de reacties!

Ik ga het eens proberen met de Rebuild search en de back-up vaker doen zonder die ene tabel. Het gaat me toch meer om de berichten.

Het andere script ga ik ook eens proberen. Dat is ook handig natuurlijk, maar daar moet ik me even wat meer in verdiepen nog.

Gebruikersavatar
Derky
Berichten: 4466
Lid geworden op: 07 apr 2005, 16:24
Locatie: Nederland
Contacteer:

Re: Database backuppen en de zoekfunctie

Bericht door Derky » 12 nov 2007, 11:56

beert schreef:Thx voor de reacties!

Ik ga het eens proberen met de Rebuild search en de back-up vaker doen zonder die ene tabel. Het gaat me toch meer om de berichten.

Het andere script ga ik ook eens proberen. Dat is ook handig natuurlijk, maar daar moet ik me even wat meer in verdiepen nog.
Met die Rebuild Search mod moet je wel je forum sluiten, en met zo'n grote database duurt het een hele tijd voordat alle zoektabellen weer zijn opgebouwt. ;)

Gebruikersavatar
Ramon Fincken
Berichten: 2552
Lid geworden op: 27 nov 2005, 23:15
Locatie: Diemen
Contacteer:

Re: Database backuppen en de zoekfunctie

Bericht door Ramon Fincken » 28 nov 2007, 20:31

sterker nog je hebt 2 of 3 tabellen de inhoud niet nodig, omdat de rebuild de search_ opnieuw vult !
Freelance webdevelopment, including phpbb2 scripting!

Website founder van: phpBBinstallers.net phpBBantispam.com
Mods: zie op http://www.phpbb.com Blog in wording: RamonFincken.com

Gesloten