====== Umgebung ======
Debian (Lenny), MySQL, php5-cli
====== Installation ======
===== Verzeichnis vorbereiten =====
# mkdir /var/sqlb
===== Code abspeichern =====
Folgendes Script in /root/bin abspeichern:
# pico /root/bin/sqlb
#!/usr/bin/php5 -q
###############################################################################################
# Mysql Database backup #
# This small php script should be used to backup all databases on a server #
# Version 1.0 (08.01.02) #
# Question or comment: sebastien@morier.com #
###############################################################################################
# Configuration Option #
###############################################################################################
#Mysql Server information
$server = "localhost";
$username = "root";
$password = "DEIN-MYSQL-PASSWORT";
#Path to save file, create this folder and then execute the script!
$path_to_save = "/var/sqlb/";
#Backup file name
$date_backup = date("dmy"); #(dmy = day month year mdy = month day year)
#$filename = "mysql-".$date_backup.".tar.gz"; #do not change the $date_backup part
# Filenamengenerierung nur mit Tagen !
$date_tar = date("D");
$filename = "mysql-backup-$date_tar.tar.gz";
#FTP transfert information
#Ftp backup (yes for ftp transfert, no if you don't want to transfert it via ftp or if php has no ftp support)
$ftp_backup = "no";
$ftp_server = "";
$ftp_user_name = "";
$ftp_user_pass = "";
#Directory on the server where you want to transfer the backup (enter 2 backslashes for a subdirectory example test\\test2);
#or leave blank if you don't want to change directory
#$ftp_directory = "";
$ftp_directory = "";
#Remove gzip file from localhost after backup and ftp
$original_remove = "";
#Set timeout of the script (2 hours)... should be enough... if you have a some big databases change it...
set_time_limit (7200);
###############################################################################################
# End of configuration option #
###############################################################################################
echo "\n\n";
echo "###########################################################\n";
echo "# MysqlBackup php script #\n";
echo "###########################################################\n\n";
echo "Start time: ".date("G:i:s")."\n";
//check if the backup directory exist, if not... warning message and exit....
if (!file_exists($path_to_save)) {
echo "Warning! Backup directory (path_to_save configuration option), doesn't exist, please create it!\n\n";
exit;
}
//Connection to the mysql server
@mysql_connect("$server","$username","$password") or die("Warning! Unable to connect to mysql server, check server, username and password");
//show the database and make a loop to backup all databases
$sqlstr = "show databases";
$result = mysql_query($sqlstr);
$i = 0;
while ($i < mysql_num_rows($result)):
$database_to_save = mysql_result($result, $i, 0);
//command to backup the database to a file
$backup_command = "mysqldump -h$server -u$username -p$password --opt --add-drop-table --quote-names $database_to_save > $path_to_save$database_to_save.sql";
if (system($backup_command) === false) :
echo "Error with database backup $database_to_save... exiting script\n\n";
exit;
else:
echo "Backing up database $database_to_save\n";
endif;
$i++;
endwhile;
//Make a gzip tar file of the backup...
echo "Taring and gzipping database backup...\n";
//change current directory to the save path
chdir ("$path_to_save");
//Tar gzip the backup
system ("tar -czf $filename *.sql &> /dev/null ");
//find the size of the file
$filesize = filesize($filename);
//chmod the file to 700, so nobody else than root can read it
chmod ("$filename", 0700);
//remove the .sql file created before
system ("rm *.sql -f");
echo "\nBackup file: ".$filename."\n\n";
//Check if the user want to ftp the file
if ($ftp_backup == "yes"):
//Find the correct size....
$kb = 1024; // Kilobyte
$mb = 1024 * $kb; // Megabyte
$gb = 1024 * $mb; // Gigabyte
if($filesize < $kb):
$filesize .= " Bytes";
elseif($size < $mb):
$filesize = round($filesize/$kb,2)." KB";
elseif($size < $gb):
$filesize = round($filesize/$mb,2)." MB";
else:
$filesize = round($filesize/$gb,2)." GB";
endif;
//Name of the file for the transfert....
$source_file = $filename;
$destination_file = $source_file;
//Ftp transfert...
// set up basic connection
$conn_id = ftp_connect($ftp_server);
// login with username and password
$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);
// check connection
if ((!$conn_id) || (!$login_result)) {
echo "FTP connection has failed!\n";
echo "Attempted to connect to $ftp_server for user $ftp_user_name\n\n";
exit;
} else {
echo "Connected to $ftp_server, for user $ftp_user_name\n";
}
if ($ftp_directory):
if (@ftp_chdir($conn_id, "$ftp_directory")):
echo "Changing FTP direcotry to \"$ftp_directory\"\n";
else:
echo "Error directory \"$ftp_directory\" doesn't exist on FTP server, create it and run script again\n\n";
exit;
endif;
endif;
//Message file transfert...
echo "Starting file transfert: $source_file ($filesize)\n";
// upload the file
$upload = ftp_put($conn_id, $destination_file, $source_file, FTP_BINARY);
// check upload status
if (!$upload) {
echo "FTP upload has failed!\n";
} else {
echo "Uploaded $source_file to $ftp_server as $destination_file\n";
}
// close the FTP stream
//ftp_close($conn_id);
echo "\n\n";
if ($original_remove == "yes"):
if (@file_exists($filename)):
unlink("$filename");
echo "File $filename removed from localhost\n\n";
else:
echo "Error... $filename not here\n\n";
endif;
endif;
endif;
echo "End time: ".date("G:i:s")."\n";
echo "###########################################################\n\n";
?>
**Die Variable mit dem Passwort auf das eigene System anpassen!**
===== Ausführbar machen =====
# chmod +x /root/bin/sqlb
====== Test ======
/root/bin/sqlb
====== Crontab ======
# crontab -e
Folgendes einfügen:
#MySQL-Datenbanken sichern
0 0 * * * /root/bin/sqlb