Registreren

[RedRose]

Inleiding
In dit artikel staat een script waarmee je eenvoudig je MySQL database kunt backuppen.


Aanroepen
Bij het aanroepen van de klasse moet je een database opgeven en een array met alle tabellen die je gebackupped wilt hebben.

Aanroepen gebeurd op de volgende manier:

PHP

Code: Alles selecteren

<?php
// Uitpoepen als plaintext:
mysql_connect('localhost', 'username', 'password');
$bck = new MySQLBackup(1, '');
$bck->SetDatabase('databasenaam', array('tabel1', 'tabel2'));
echo $bck->Backup();
 
// Uitpoepen als HTML:
mysql_connect('localhost', 'username', 'password');
$bck = new MySQLBackup(2, '');
$bck->SetDatabase('databasenaam', array('tabel1', 'tabel2'));
echo $bck->Backup();
 
// Uitpoepen in het bestand sql.sql in dezelfde map als het aanroepende script:
mysql_connect('localhost', 'username', 'password');
$bck = new MySQLBackup(3, 'sql.sql');
$bck->SetDatabase('databasenaam', array('tabel1', 'tabel2'));
$bck->Backup();
 
// Ter download aanbieden:
mysql_connect('localhost', 'username', 'password');
header('Content-Type: application/octetstream');
header('Content-Disposition: attachment; filename="sql.sql"');
 
$bck = new MySQLBackup(1, '');
$bck->SetDatabase('databasenaam', array('tabel1', 'tabel2'));
echo $bck->Backup();
?>

Terugzetten
Terugzetten is een eitje:

PHP

Code: Alles selecteren

<?php
mysql_connect('localhost', 'username', 'password');
// Gesteld dat het bestand sql.sql zich in dezelfde map als het uitvoerende script bevindt:
MySQLBackup::SetBack(file_get_contents('sql.sql'));
?>

Het script
PHP

Code: Alles selecteren

<?php
/**
* Copyright (c) 2005, Jorn van der Pol
* All rights reserved.
*
* Redistribution of source is permitted, provided that the above
* copyrightmessage, this condition and following disclaimer are retained.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHTHOLDER AND CONTRIBUTORS "AS-IS".
* BECAUSE THIS SOFTWARE IS LICENSED FREE OF CHARGE THERE IS NO WARRANTY OF
* ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE
* IMPLIED WARRANTIES OF FITNESS AND MERCHANTABILITY FOR A PARTICULAR PURPOSE.
*
* THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE SOFTWARE IS WITH YOU.
* SHOULD THE SOFTWARE PROOF DEFECT, YOU ASSUME THE COST OF ALL NECESSARY
* SERVICING, REPAIR OR CORRECTION.
*
* IN NO EVENT, UNLESS STATED OTHERWISE OR REQUIRED BY APPLICABLE LAW, WILL THE
* COPYRIGHTHOLDER OR ANY CONTRIBUTOR BE LIABLE TO YOU FOR ANY GENERAL, SPECIAL
* INCIDENTAL OR CONSEQUENTIAL DAMAGE CAUSED BY THE SOFTWARE, INCLUDING, BUT NOT
* LIMITED TO, LOSS OF DATA AND COMPUTERMALFUNCTION, EVEN IF THE COPYRIGHTHOLDER
* OR CONTRIBUTOR IS AWARE OF THE POSSIBILITY OF SUCH RISKS.
*/

/**
* Author : Jorn van der Pol
* Email : ikbenconsequent at hotmail dot com
*/

/**
* class MySQLBackup
* This class provides a simple but usefull MySQL backup-tool.
* It allows the user to specify a database and tables to be
* backupped.
*
* There are three types of output:
* - PLAIN
* - HTML
* - FILE
*/
class MySQLBackup{
var $filename = '';
var $database = '';
var $tables = array();
var $output = '';
var $type = 0;

var $TYPE_PLAIN = 1;
var $TYPE_HTML = 2;
var $TYPE_FILE = 3;

/**
* Constructor MySQLBackup
* Constructor of class MySQLBackup.
* @access : public
* @param type : (int) The type of output.
* @param file : (string) The file to output the backup to if type is TYPE_FILE.
*/
function MySQLBackup($type, $file = ''){
// Check if this is a valid type
if($type != $this->TYPE_PLAIN && $type != $this->TYPE_HTML && $type != $this->TYPE_FILE){
$this->Error('Wrong type. Type should either be MySQLBackup::TYPE_PLAIN, MySQLBackup::TYPE_HTML or MySQLBackup::TYPE_FILE.', __LINE__, 'MySQLBackup::MySQLBackup');
}

// If output is set to file, check if a file is specified.
if($type == $this->TYPE_FILE && !$file){
die('Please set a filename to save the output as a file.');
}

$this->type = $type;

$this->filename = $file;

// Begin output.
$this->output .= "#\n";
$this->output .= "# MySQLBackup\n";
$this->output .= "# Copyright (c) 2005, Jorn van der Pol\n";
$this->output .= "# All rights reserved.\n#\n";
$this->output .= "# Date: " . date('Y-m-d h:i:s A') . "\n";
$this->output .= "#\n\n";
}

/**
* void SetDatabase
* Allows the user to specify a database and tables.
* @access : public
* @param database : (string) The database to backup.
* @param tables : (array) The tables to backup.
*/
function SetDatabase($database, $tables){
if(!is_string($database) || !is_array($tables) || sizeof($tables) == 0){
// One of the parameters is invalid.
Error('Invalid parameter', __LINE__, 'MySQLBackup::SetDatabase');
}
else{
$this->database = $database;
$this->tables = $tables;

// Output: Name of the assigned database.
$this->output .= "#\n# Database: {$database}\n";
$this->output .= "# Tables: " . implode(', ', $tables) . "\n#\n\n";
$this->output .= "D-ROP DATABASE IF EXISTS `{$database}`;\n";
$this->output .= "CREATE DATABASE `{$database}`;\n";
$this->output .= "USE `{$database}`;\n\n";
}
}

/**
* mixed Backup
* Backups the database and tables specified and either returns or saves the backup.
* @access : public
* @return
* A string containing the backup (either plain or html) or true.
*/
function Backup(){
for($index = 0; $index < sizeof($this->tables); $index++){
$this->BackupTable($this->tables[$index]);
}

switch($this->type){
case $this->TYPE_PLAIN:
// Output as plain text.
return $this->output;
break;
case $this->TYPE_HTML:
// Output as HTML.
return nl2br(htmlentities($this->output));
break;
case $this->TYPE_FILE:
// Write output to file.
$fp = fopen($this->filename, 'w+');
fwrite($fp, $this->output);
fclose($fp);
return true;
break;
}
}

/**
* void BackupTable
* Backups a table.
* @access : private
* @param tablename : (string) The table to backup.
*/
function BackupTable($tablename){
$query = "SHOW CREATE TABLE `{$this->database}`.`{$tablename}`";
$result = mysql_query($query) or $this->Error(mysql_error(), __LINE__, 'MySQLBackup::BackupTable');
if($result = mysql_fetch_array($result)){
$this->output .= $this->GetTableComment($tablename);
$this->output .= "D-ROP TABLE IF EXISTS `{$tablename}`;\n";
$this->output .= $result[1];
$this->output .= ";\n\n";
$this->BackupTableContent($tablename);
}
}

/**
* void BackupTableContent
* Backups a all rows in a table
* @access : private
* @param tablename : (string) The table to backup the rows of.
*/
function BackupTableContent($tablename){
// Retrieve all fieldnames.
$query = "SHOW COLUMNS FROM `{$this->database}`.`{$tablename}`";
$result = mysql_query($query) or $this->Error(mysql_error(), __LINE__, 'MySQLBackup::BackupTableContent');

if(mysql_num_rows($result) > 0){
$fields = array();
while($row = mysql_fetch_array($result)){
$fields[] = $row[0];
}

// Retrieve all data within fields.
$query = "SELECT * FROM `{$this->database}`.`{$tablename}`";
$result = mysql_query($query) or $this->Error(mysql_error(), __LINE__, 'MySQLBackup::BackupTableContent');

$this->output .= $this->GetTableContentComment($tablename);

while($row = mysql_fetch_array($result)){
$tmp = "INSERT INTO `{$tablename}` (";
for($index = 0; $index < sizeof($fields); $index++){
if($index == sizeof($fields) - 1){
$tmp .= "`" . mysql_real_escape_string($fields[$index]) . "`";
}
else{
$tmp .= "`" . mysql_real_escape_string($fields[$index]) . "`, ";
}
}
$tmp .= ") VALUES (";
for($index = 0; $index < sizeof($fields); $index++){
if($index == sizeof($fields) - 1){
$tmp .= "'" . mysql_real_escape_string($row[$fields[$index]]) . "'";
}
else{
$tmp .= "'" . mysql_real_escape_string($row[$fields[$index]]) . "', ";
}
}

$tmp .= ");";
$this->output .= $tmp . "\n";
}

$this->output .= "\n";
}
}

/**
* string GetTableComment
* @access : private
* @param tablename : (string) The table to be commented.
* @return
* A comment on the structure of the current table.
*/
function GetTableComment($tablename){
return "#\n# Structure for table {$tablename}:\n#\n\n";
}

/**
* string GetTableContentComment
* @access : private
* @param tablename : (string) The table to be commented.
* @return
* A comment on the data of the current table.
*/
function GetTableContentComment($tablename){
return "#\n# Data for table {$tablename}:\n#\n\n";
}

/**
* void SetBack
* Resets the database with the supplied sql-dump.
* @param sql : The SQL-dump to reset this database with.
*/
function SetBack($sql){
// Execute queries one by one.
$sql = explode(";\n", $sql);
for($index = 0; $index < sizeof($sql); $index++){
mysql_query($sql[$index]);
}
}

/**
* void Error
* Shows an error and stops execution of the page.
* @access : private
* @param errormessage : (string) The message to be shown.
* @param line : (int) The line on which the error occured.
* @param function : (string) The function in which the error occured.
*/
function Error($errormessage, $line, $function){
echo "<strong>MySQLBackup::Error</strong>: {$errormessage} on line <strong>{$line}</strong> in function <strong>{$function}</strong>";
exit;
}
}
?>