[PILLOLA] backup database con mysqldump

[Dirk Pitt]

Ciao a tutti.
Mi serviva un sistema che mi facesse il backup del database.
Inizialmente usavo uno script ben fatto (http://www.phpmybackuppro.net/), carino, pratico con diverse funzionalità. Peccato una volta raggiunto un db di dimensioni 6/8 Mb, il sistema smettesse di funzionare...

Quindi bisognava cercare un'altra soluzione: tramite mysqldump.

Creiamo una pagina backup.php come segue:

<?php 

$db_name = "nome_del_db";                // nome del database
$db_user = "username";                // utente database
$db_psw = "password";                                  // password utente database
$to = "info@miosito.com";        // indirizzo email a cui viene spedito il backup
$from = "do-not-reply@miosito.com";        // indirizzo email dal quale parte il backup
$dbhost = "localhost";                      // indirizzo del database

$backupFile = $db_name . "_" . date("d-m-Y_H-i-s") . '.gz';
$command = "mysqldump --opt -h " . $dbhost . " -u " . $db_user . " -p" . $db_psw . " " . $db_name . " | gzip > backup/dump_files/" . $backupFile . "";
system($command);

require_once 'class.mailer.php';

$mail = new PHPMailer(); //defaults to using php "mail()";

$mail->AddAddress($to, '');
$mail->SetFrom($from, '');
$mail->IsHTML(true);
$mail->Subject = "Backup DB: " . $backupFile;
$mail->Body = "In allegato il backup del database " . $db_name . ".

Email generata in automatico dal sistema, NON RISPONDERE A QUESTA EMAIL";
$mail->AddAttachment("backup/dump_files/" . $backupFile);
$mail->Send();

?>

Nella pagina viene incluso un file class.mailer.php, che altro non è se non il famoso PHPMailer (http://phpmailer.worxware.com/), che serve per spedire il backup via email.

Cosa fa lo script?
1. Crea un backup del database (usando le credenziali di accesso inserite), nominando il file con il nome del database stesso, aggiungendo data e ora;
2. Lo salva sul server nella cartella ove risiede il presente file (nell'esempio salva nella cartella backup/dump_files);
3. Spedisce una email all'indirizzo specificato contenente copia del file di backup.

Lo script può essere incluso in qualsiasi pagina e/o richiamato in base a determinati criteri.
Il modo più pratico e sicuro, è quello di far eseguire il file tramite un comando di cronjobs (per chi ha la possibilità di farlo), settandone la ricorrenza.

Lo script può essere implementato e migliorato a piacere, non c'è dubbio

[Dirk Pitt]

E se volessimo effettuare il backup solo una volta al giorno, "lanciando" lo script accedendo ad una pagina?
Creiamo una nuova tabella nel db, che chiameremo backup:

CREATE TABLE 'backup' (                 
          'data_backup' datetime default '0000-00-00 00:00:00'   
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1

Modifichiamo il nostro script come segue:

$db_name = "nome_del_db";                // nome del database
$db_user = "username_db";                // utente database
$db_psw = "password_db";                                  // password utente database
$to = "pippo@miosito.it";        // indirizzo email a cui viene spedito il backup
$from = "do-not-reply@miosito.it";        // indirizzo email dal quale parte il backup

$dbhost = "localhost";                      // indirizzo database

#crea connessione al db
$conn = mysql_connect($dbhost, $db_user, $db_psw);
    if (!$conn){
      die("Errore: " . mysql_error());
  }
        
mysql_select_db($db_name, $conn);

#verifica se il backup è già stato effettuato nella giornata di oggi

$get_data = "SELECT data_backup FROM backup WHERE data_backup = '" . date("Y-m-d") . "'";

$result = mysql_query($get_data, $conn);

if(mysql_num_rows($result) == 0){        // la data di backup non è uguale a quella corrente

    #effettua il backup del database
    $backupFile = $db_name . "_" . date("d-m-Y_H-i-s") . '.gz';
  $command = "mysqldump --opt -h " . $dbhost . " -u " . $db_user . " -p" . $db_psw . " " . $db_name . " | gzip > backup_files/" . $backupFile . "";
  system($command);
  
  require_once 'class.mailer.php';
  
  $mail = new PHPMailer(); //defaults to using php "mail()";
  
  $mail->AddAddress($to, '');
  $mail->SetFrom($from, '');
  $mail->IsHTML(true);
  $mail->Subject = "Backup DB: " . $backupFile;
  $mail->Body = "In allegato il backup del database " . $db_name . ".

Email generata in automatico dal sistema, NON RISPONDERE A QUESTA EMAIL";
  $mail->AddAttachment("backup_files/" . $backupFile);
  $mail->Send();
    
    
    #aggiorna la data di backup
    $update = "UPDATE backup SET data_backup = '" . date("Y-m-d") . "'";
  mysql_query($update, $conn);

}

mysql_close($conn); 

Basta includere questo script in una pagina php (possibilmente ad inizio pagina) affinchè all'accesso di quella pagina, venga effettuato il backup del database (se l'ultima data di backup è diversa da quella attuale).

[Dirk Pitt]

Dopo aver creato la tabella, bisogna inserire un record, altrimenti l'aggiornamento della data non avviene, in quanto non viene trovato nessun record da aggiornare.
Quindi:

CREATE TABLE 'backup' (                 
          'data_backup' datetime default '0000-00-00 00:00:00'   
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1

INSERT INTO `backup` VALUES ('2010-09-19 00:00:00');

Il valore della data non è importante, basta che sia presente un record.

[Dirk Pitt]

Con l'andare avanti nel tempo, la cartella che contiene i files di backup andrà man mano riempiendosi di files di backup vecchi, inutili.
Scegliamo quindi una quantità di files da mantenere costanti sul server ed andiamo ad eliminare quelli che eccedono questa quantità, partendo dai più vecchi.
Inseriamo questo codice subito dopo al codice che abbiamo usato per creare il backup:

#recupera nome e data files di backup
$_dir = $_SERVER["DOCUMENT_ROOT"] . "/backup_files/";
$_files = array();

if(is_dir($_dir)){
    if($_dh = opendir($_dir)) {
      while(false !== ($_file = readdir($_dh))){
            if($_file != '.' && $_file != '..'){
                $_files[] = array('file' => $_file, 'data' => date("d/m/Y", filemtime($_dir. "/" . $_file)));
            }
    }
      closedir($_dh);
        rsort($_files);
        $ultimo_back = $_files[0]["data"];   //qui ricavo la data dell'ultimo backup effettuato, utile se la si vuole stampare a video
    }

##MANUTENZIONE FILES DI BACKUP##

$max_files = 3;        //numero files di backup da mantenere costanti sul server
$tot_files = count($_files);

if($tot_files > $max_files){            // i files sul server sono maggiori di quelli consentiti
    for($i = $max_files; $i <= $tot_files-1; $i++){
        unlink($_dir . $_files[$i]["file"]);
    }
}

##FINE MANUTENZIONE FILES DI BACKUP## 

Dal momento che abbiamo creato nel database la tabella backup, anzichè usare il metodo qui sopra, possiamo di volta in volta salvare i riferimenti dei backup in quella tabella (modificandola un briciolo) ed effettuare i controllo di cui sopra sul db.

[Manuel.s]

ciao a tutti
riprendo questa vecchia discussione per una domanda:
come possiamo migliorare questo script perche funzioni indipendentemente dal SO (linux o win o altro) sul quale gira php?

[7stars]

Pardon, nel primo file non serve la connessione al database? Cioè, il mysqldump attraverso le variabili implica già la connessione? Grazie

[Dirk Pitt]

Nel prima versione, ovvero quella senza controllo sul db, la connessione al db non è necessario "aprirla", in quanto come dici tu, i dati relativi al db vengono passati direttamente a mysqldump.

[7stars]

Grazie Dirk... ma se il mio server di posta non manda allegati di grandi dimensioni come si può fare? Il mio db è 80+MB...ovviamente se proprio volessi usare l'opzione mail... altrimenti la elimino e me lo trovo cmq su ftp...quindi questo script può mandare solo dal server? Per esempio, se volessi mandare con jumbo di libero ad un account gmail è possibile?

[Dirk Pitt]

Nel mio esempio viene usato l'invio tramite la funzione mail() di php.
La classe phpmailer usata consente l'invio anche tramite il proprio server SMTP.
Vedi qui un esempio: http://phpmailer.worxware.com/index.php?pg=examplebsmtp

[jrm]

Ciao tutti, riapro un post vecchio su cui mi sono basato, senza crearne uno nuovo...
ho seguito le preziose indicazioni di Dirk Pitt, solo che a me crea correttamente il file nella cartella backup_files ma quando lo apro è completamente vuoto..
nella tabella backup sul DB viene riempita correttamente la 'data_backup' con indicazione della data di esecuzione del bck stesso.
ho usato l'esempio dopo

CREATE TABLE 'backup' (
'data_backup' datetime default '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Piccola nota: la nuova classe di 'PHPMailer' adesso si chiama: class.phpmailer.php
qualcuno sa dirmi cosa sbaglio?
grazie in anticipo
ciao