Script per realizzare un backup automatico dei dati di MySQL utilizzando mysqldump, in bash e in php

di Giulio di Bari :: 10 marzo 2018

In precedenti articoli abbiamo visto come utilizzare mysqldump per esportare "manualmente" dati da un database MySQL.

Potrebbe tuttavia essere necessario creare un sistema di backup automatico per salvare periodicamente una copia completa del nostro database.

Vediamo come creare uno script di backup e come schedulare con cron la sua esecuzione a determinati intervalli periodici, ad esempio tutte le notti.

Nel nostro esempio creiamo un backup all'interno dello stesso server... ma ovviamente un backup, in quanto tale, va salvato su un server diverso, oppure su un Hard Disk USB collegato.

Vedremo assieme due script simili, uno realizzato in bash, uno in php.

Per entrambi gli script procediamo come segue:

abbiamo accesso al server come utenti "root"
accediamo al server mysql con l'utente "root" e la password "pluto"
vogliamo creare un file che esegua un backup una volta al giorno, in notturna, alle ore 2.00.
eseguiamo il nostro lavoro all'interno di una directory chiamata "BACKUP" che creiamo nella root del server "/BACKUP"
all'interno di questa directory sarà localizzato lo script che stiamo realizzando
sempre all'interno di questa directory creiamo un'altra directory, "/BACKUP/storico", dove salveremo, ogni giorno, il backup dei nostri database. Ogni backup sarà un file compresso tar.gz il cui nome sarà contrassegnato da data e ora del backup, ad esempio "/BACKUP/storico/20180309091254.tar.gz"
sempre all'interno di questa directory creiamo, solo per la durata del backup, una cartella temporanea "/BACKUP/tmp"
utilizziamo mysqldump per salvare i singoli database l'interno della cartella temporanea: non creiamo un unico file .sql con tutto dentro, ma un file .sql per ogni database da esportare
creiamo il file compresso tar.gz al cui interno inseriamo tutti i file .sql ottenuti, e lo collochiamo nella cartella dello storico "/BACKUP/storico"
cancelliamo quindi la directory temporanea "/BACKUP/tmp"
infine cancelliamo i backup eseguiti da più di 7 giorni (cioè le cartelle giornaliere precedentemente create), il modo da pulire il nostro hard disk

Mettiamoci al lavoro !

Bash script per realizzare un backup mysql

Andiamo a creare la cartella BACKUP nella root del server

# mkdir /BACKUP

e al suo interno criamo un file chiamato "backup.sh" a cui assegnamo permessi 700

# touch /BACKUP/backup.sh
# chmod 700 /BACKUP/backup.sh

Apriamo questo file utilizzando l'editor di vostro gradimento (ad esempio vi) ed inseriamo questo codice, che dopo commenteremo

#!/bin/sh

DATE=`/bin/date +%Y%m%d%H%M%S`

PATH_BACKUP="/BACKUP"

PATH_STORICO="$PATH_BACKUP/storico"
if [ ! -d $PATH_BKP]; then
	mkdir -p $PATHBKP
fi

PATH_TMP="$PATH_BACKUP/tmp"
if [ -d $PATH_TMP]; then
	rm -f PATH_TMP
fi
mkdir -p $PATH_TMP

DATABASE_LIST=`mysql --user=root --password=pluto --skip-column-names --execute="show databases"`

for i in $DATABASE_LIST; do
	if [[ $i != "information_schema" && $i != "mysql" && $i != "performance_schema" ]]; then
	mysqldump --user=root --password=pluto --databases $i > $PATH_TMP/$i.sql
	fi
done

cd $PATH_BACKUP
/bin/tar -pzcf $PATH_STORICO/$DATE.tar.gz tmp
rm -f $PATH_TMP

find $PATH_STORICO/* -mtime +7 -type f -delete

echo "Backup ultimato"

Vediamo nel dettaglio quando abbiamo programmato

la variabile DATE serve ad ottenere una timestamp (anno mese giorno ore minuti secondi) del momento il cui lo script viene lanciato, e verrà usata per nominare la cartella di backup dove salveremo i database
la variabile PATH_BACKUP è la directory in cui stiamo operando
la variabile PATH_STORICO indica la cartella, all'interno di PATH_BACKUP, in cui salveremo lo storico dei nostri backup giornalieri (un file compresso per giorno). Se non è presente la creiamo con "mkdir"
la variabile PATH_TMP indica la cartella temporanea, all'interno di PATH_BACKUP, che creiamo al momento del lancio dello script, e che cancelliamo ultimato lo script: al suo interno salveremo i singoli file .sql dei database esportati con mysqldump. Se al momento del lancio dello script questa cartella fosse presente viene cancellata e ricreata in modo da eliminare eventuali file .sql salvati in un precedente backup.
DATABASE_LIST è la lista dei database che volgiamo esportare e viene popolato tramite l'output ottenuto lanciando la query "show databases" su mysql
Per ogni database di questa lista, ad eccezione dei database di sistema "information_schema" "mysql" e "performance_schema" che non ci interessa salvare, effettuiamo un mysqldump: creiamo così un file .sql per ogni database. Ad esempio, se avevamo tre database chiamati "fatture" "clienti" e "categorie", otterremo i tre file "fatture.sql" "clienti.sql" e "categorie.sql"
Entriamo nella cartella di lavoro ("cd /BACKUP") e creiamo un file compresso tar.gz della cartella temporanea "tmp", al cui interno ci sono i file ottenuti, e lo salviamo nella cartella storico. Il nome di questo file sarà la timestamp ottenuta con la variabile DATE, ad esempio "20180309091254.tar.gz"
Adesso che il file della giornata è stato creato cancelliamo la cartella "tmp" con tutto il suo contenuto con il comando "rm"
Infine, cancelliamo i backup creati da almeno 7 giorni, in modo da ripulire l'hard disk. Utilizziamo il comando "find" con il quale cerchiamo tutti i file ("-type f") contenuti nella cartella "storico" (utilizzando l'asterisco *), creati almeno 7 giorni fa,("-mtime +7") , e li cancelliamo "-delete".
Ultimato lo script avvisiamo con la frase "Backup ultimato"

La programmazione del nostro script è così terminata. Verifichiamo il suo funzionamento

Entriamo nella directory "/BACKUP" ed esguiamo lo script

# cd /BACKUP
# ./backup.sh

Se non si presentano errori, attendere la sua esecuzione e la frase "Backup ultimato". Accedete alla cartella "/BACKUP/storico" e verificate se il file è presente.

PHP script per realizzare un backup mysql

Vediamo adesso come realizzare il medesimo script in PHP, in poche parole realizziamo quello che si chiama "porting", cioè riscriviamo fedelmente lo script da bash a php.

Il php lo conosciamo come linguaggio di programmazione utilizzato per le pagine web, tuttavia utilizzato da linea di comando (php cli) è molto robusto è può certamente essere utilizzato anche in queste situazioni.

Assicurati di avere intallato il pacchetto php-cli. Nella distribuzione Centos che utilizziamo per le nostre prove utilizziamo yum per installare php-cli

# yum install php-cli

Il file che andiamo a realizzare questa volta lo chiamiamo "backp.php" e che salviamo nella nostra cartella "/BACKUP"

# touch /BACKUP/backup.php
# chmod 700 /BACKUP/backup.php

Questo il codice PHP del file backup.php

#!/usr/bin/php

<?php

date_default_timezone_set("Europe/Rome");
ini_set("memory_limit", "-1");

// ottengo una timestamp da usare per salvare il backup
$data=date("YmdHis");

// funzione usata per cancellare la directory temporanea
function rm_tmp($dir)
{
	if(file_exists($dir))
	{
		if(!$dh = @opendir($dir)) return;
		while ($obj = readdir($dh))
		{
			if($obj=='.' || $obj=='..') continue;
			unlink($dir.'/'.$obj);
		}
		closedir($dh);
		rmdir($dir);
	}
	return;
}

// funzione usata per cancellare i backup storici
function rm_bkp($dir,$seconds)
{
	if(!$dh = @opendir($dir)) return;
	while ($obj = readdir($dh))
	{
		if($obj=='.' || $obj=='..') continue;
        	if ((time()-filemtime($dir."/".$obj)) > $seconds) 
		{
		  	unlink($dir."/".$obj);		
		}
	}
	closedir($dh);
	return;
}

// path area di lavoro
$path_backup="/BACKUP";
// path storico bkp
$path_storico=$path_backup."/storico";
if(!file_exists($path_storico)){mkdir($path_storico,0700,true);}
// path temporaneo
$path_tmp=$path_backup."/tmp";
if(file_exists($path_tmp)){rm_tmp($path_tmp);}
mkdir($path_tmp,0700,true);

// connessione al database
$dbserver = "localhost";
$dbuser = "root";
$dbpassword = "pluto";
$mysql_link = mysqli_connect($dbserver,$dbuser,$dbpassword);

// ottengo la lista dei database disponibili
$query = "SHOW DATABASES";
$result = mysqli_query($mysql_link,$query);

$array_db=array();
while ($row = mysqli_fetch_assoc($result))
{
    if($row['Database']!='information_schema' && $row['Database']!='mysql' && $row['Database']!='performance_schema') {
	// lancio mysqldump
	exec("mysqldump --user=root --password=pluto --databases $row[Database] > $path_tmp/$row[Database].sql");
	$array_db[]=$row['Database'].".sql";
    }
}

// creo un file tar nella cartella storico, con all'interno i file esportarti nella cartella temporanea, utilizzando la classe nativa di PHP 5 "PharData"
$p = new PharData($path_storico."/".$data.".tar");
for($d=0;$d<count($array_db);$d++){
    // aggiungo al tar i singoli database esportati. il primo parameto è il path completo al file, il secondo è il nome del file
    $p->addFile($path_tmp."/".$array_db[$d],$array_db[$d]);
}
// comprimo con gzip ed ottengo il file .gz
$p->compress(Phar::GZ);
// cancello il file tar
unlink($path_storico."/".$data.".tar");

// cancello la directory tmp
rm_tmp($path_tmp);

// cancello i bkp creati più giorni prima (604.800 secondi)
rm_bkp($path_storico,'604800');

echo "Backup ultimato";

?>

Lo script PHP, come vedete, è già commentato.

Abbiamo creato due semplici funzioni per cancellare la directory temporanea e i backup storici, e utilizzato la classe nativa di PHP 5 "PharData" per ottenere il file tar.gz.

Il lancio del mysqldump è stato effettuato con exec che ci consente di eseguirlo come se fossimo nella shell di linux.

That's all!

Approfondimenti

php-cli: php da linea di comando

Script per realizzare un backup automatico dei dati di MySQL utilizzando mysqldump, in bash e in php

Bash script per realizzare un backup mysql

PHP script per realizzare un backup mysql

Approfondimenti

Potrebbe interessarti