Debian (Lenny), MySQL, php5-cli

# mkdir /var/sqlb

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!

# chmod +x /root/bin/sqlb

/root/bin/sqlb

# crontab -e

Folgendes einfügen:

#MySQL-Datenbanken sichern
0  0 * * * /root/bin/sqlb