Comment programmer le backup des tables de sa base de données en php

 

La plupart des hébergeurs incluent la sauvegarde des sites qu'ils hébergent. Mais, dans le cas des formules les plus abordables, la base de données n'est sauvegardée qu'une fois par semaine, ce qui est nettement insuffisant pour des sites fréquemment mis à jour ou pour des CMS, qui stockent les contenus du site dans la base. Ne parlons pas des hébergements Free.fr, qui ne s'en occupent pas du tout (déjà bien gentils de vous héberger à l'oeil).

Alors, pourquoi ne pas créer votre propre backup ?

Le principe :

  • se connecter à la base
  • exporter les tables sous la forme de fichiers SQL
  • zipper le tout
  • l'envoyer par FTP sur un autre serveur
  • expédier un mail pour informer que la sauvegarde s'est bien déroulée (avec le nombre de tables sauvegardées et la taille de la sauvegarde)

Tout ça en php

Se connecter à la base

Personnellement, je procède à l'ancienne avec mysql_connect (attention cette fonction est dépréciée) :

$serveur="monserveur"; // sql.free.fr pour les sites sous Free, localhost pour les dédiés
$login_base="mabase.dedonnees"; // remplacez par le login à votre base, c'est ce qui precede le .free.fr dans le nom de domaine du site
$pass_base="dedonnees"; // remplacez par le mot de passe de votre base, chez Free c'est souvent c'est le même que celui du FTP
$base="mabase"; // remplacez par le nom de votre base
$connect= mysql_connect($serveur,$login_base,$pass_base); 
mysql_select_db($base);

 

Les includes

Pour zipper, j'utilise la librairie PclZip (pclzip.lib.php). Pour envoyer les mails j'utilise phpmailer. On suppose que le fichiers des librairies sont dans le même répertoire que le script de sauvegarde.

require_once('pclzip.lib.php');  
require ("class.phpmailer.php");

Exporter les tables et zipper le tout

Dans mon cas, j'exporte chaque table séparément, mais il est également possible de tout exporter d'un coup en seul SQL.

Pour le nom des fichiers, je m'appuie en général sur le nom de la base (sauf qu'il est trop abscon…) et sur la date de sauvegarde (à moins que vous en fassiez plusieurs par jour)

$fichier_dump=$base."_".date('Y-m-d')."_mysqldump";
$fichier_zip=$fichier_dump.".zip";

Le principe est de lister les tables une à une via un SHOW TABLES puis une boucle while. Une fois le fichier SQL créé, on l'ajoute au zip. Puis on le supprime.

La procédure de création du fichier SQL diffère selon le type d'hébergement.

Hébergement dédié ou VPS

J'utilise cet exemple pour les sites hébergés sur mon VPS Classic OVH.

Cet exemple s'appuie sur exec() pour lancer la commande, mais system() ou shell_exec() auraient probablement fonctionné. Important : il a fallu que j'indique le chemin complet vers la fonction mysqldump (sinon, erreur « mysqldump: command not found »). Alors que dans l'exemple mutualisé que vous verrez plus loin, pas besoin du chemin complet)

L'avantage du dédié, c'est qu'on peut avoir une base par site web. Pas besoin de filtrer sur le préfixe des tables. Cet exemple sauvegarde toutes les tables de la base l'une après l'autre.

 

$zip = new PclZip($fichier_zip);

$nb_de_tables=0;
$liste_tables=mysql_query("SHOW TABLES FROM ".$base);
while($donnees_liste_tables=mysql_fetch_array($liste_tables)){
 
   $la_table=$donnees_liste_tables[0];
   $filename=$la_table.".sql";

   exec("/usr/bin/mysqldump --host=".$serveur." --user=".$login_base." --password=".$pass_base." ".$base." --tables ".$la_table." > ".$la_table.".sql 2>&1");  


   $v_list = $zip->add($filename, PCLZIP_OPT_REMOVE_PATH, ''); // ajout au fichier zip
   if($v_list ==0){ die("Error : ".$zip->errorInfo(true)); }
   unlink($filename);
   $nb_de_tables++;
}

$taille_fichier=filesize($fichier_zip)/1024;

A quoi servent $nb_de_tables et $taille_fichier ? Je les utilise dans le mail de compte-rendu que je m'envoie à l'issue de la sauvegarde.

 

Hébergement mutualisé

L'hébergement doit permettre l'exécution de commandes. On s'appuie toujours sur la commande mysqldump. Dans mon cas, j'exporte chaque table séparément. La syntaxe ne diffère pas beaucoup de l'exemple précédent, à part que :

  • les tables de plusieurs sites sont hébergées dans la même base, or je fais des sauvegardes séparées pour chaque site. Comme j'ai préfixé mes tables (ici par ann_), je filtre sur ce préfixe.
  • je n'ai pas besoin de mentionner le chemin complet vers mysqldump
  • j'avais dû utiliser un appel à chmod, sinon le fichier créé n'avait pas les droits suffisants pour ensuite être supprimé

 

$nb_de_tables=0;
$liste_tables=mysql_query("SHOW TABLES FROM ".$base." LIKE 'ann_%' ");

while($donnees_liste_tables=mysql_fetch_array($liste_tables)){

   $la_table=$donnees_liste_tables[0];
   $filename=$la_table.".sql";

   system("mysqldump --host=".$serveur." --user=".$login_base." --password=".$pass_base." ".$base." --tables ".$la_table." > ".$la_table.".sql");

   $v_list = $zip->add($filename, PCLZIP_OPT_REMOVE_PATH, ''); // ajout au fichier zip
   if($v_list ==0){ die("Error : ".$zip->errorInfo(true)); } 
   unlink($filename); // suppression du fichier
   $nb_de_tables++;
}

chmod($fichier_zip,0777);
$taille_fichier=filesize($fichier_zip)/1024;

 

 

Hébergement gratuit (Free)

Un hébergeur tel que Free.fr bloque l'accès en ligne de commande (donc pas de mysldump) ainsi qu'un bon nombre de fonctions bien utiles, telles que les fonctions liées au FTP. Et ne propose pas de cron. Il va donc falloir :

  • un script exportant les tables
  • héberger la sauvegarde ailleurs
  • utiliser un cron externe pour le lancer

Voici le script que l'utilise. Il est artisanal, donc pas parfait : certains caractères spéciaux sont mal gérés, pouvant rendre les backups inutilisables. N'hésitez pas à l'améliorer.

$nb_de_tables=0;
$liste_tables=mysql_query("SHOW TABLES FROM ".$base);
while($donnees_liste_tables=mysql_fetch_array($liste_tables))
{
 $la_table=$donnees_liste_tables[0];

  $nb=0;
  $compte_colonnes=mysql_query("SHOW FIELDS FROM ".$la_table);
  while($donnees_compte_colonnes=mysql_fetch_array($compte_colonnes)) { $nb++; } // on compte le nb de colonnes
     $filename=$la_table.".sql";
     $file=fopen($filename,"w+");
     $contenu='';
     $requete=mysql_query("SELECT * FROM ".$la_table );

     while($donnees=mysql_fetch_array($requete))
     {
     $contenu.="INSERT INTO '".$la_table."' VALUES (";
        for ($k=0;$k<$nb;$k++)
        {
        if($k>0) { $contenu .=", "; }
        $contenu .= "'".str_replace("'","''",stripslashes($donnees[$k]))."'"; 
        }
 
     $contenu .= ");";
    }
  $ecriture=fwrite($file,utf8_encode($contenu)); // sinon le fichier sql est considere comme de l'ANSI et non de l'UTF8 et les caractères accentués ne sont pas geres
  fclose($file);

 
   $v_list = $zip->add($filename, PCLZIP_OPT_REMOVE_PATH, '');  // on rajoute au fichier zip
   if($v_list ==0){ die("Error : ".$zip->errorInfo(true)); }
 
 
 unlink($filename); // et on supprime
 $nb_de_tables++;
 
}

chmod($fichier_zip,0777);
$taille_fichier=filesize($fichier_zip)/1024;

 

Envoyer par FTP la sauvegarde sur un autre serveur

Serveur mutualisé, dédié, VPS

Ici, le fichier zip obtenu lors de l'étape précédente sera envoyé sur un serveur Free.

$serveur_ftp="ftpperso.free.fr";
$login_ftp="monlogin";
$pass_ftp="monmotdepasse";

$conn_id = ftp_connect($serveur_ftp);
$login_result = ftp_login($conn_id, $login_ftp, $pass_ftp);
ftp_pasv( $conn_id, true ); // mode passif obligatoire
if((!$conn_id) || (!$login_result)) { $ok_envoi=0; }
if(ftp_put($conn_id, "backups/".$fichier_zip, $fichier_zip, FTP_BINARY)){ $ok_envoi=1; }else{ $ok_envoi=0; }

Serveur Free

Les fonctions FTP sont bloquées. Il va falloir trouver autre chose… La solution ? Utiliser un 2e script hébergé sur un 2e serveur (dédié ou VPS) qui, lui , autorise le FTP. Il va chercher la sauvegarde générée puis l'envoyer sur un 3e serveur.

Il faut donc 2 crons. Le 1er qui lance le script de sauvegarde hébergé sur Free (il faut donc un cron qui autorise la commande wget), exécuté avant le 2e qui récupère la sauvegarde.

Compliqué ? Bah oui, un peu, mais bon…

Je me suis créé une fonction upload_backups(), afin de pouvoir l'utiliser pour plusieurs sites hébergés sous Free (ici, site 1 et site 2). Dans le cas de site 2, comme il est peu souvent modifié, une sauvegarde hebdomadaire suffit.

Attention, $ftp_source et $ftp_cible sont des arrays.

function upload_backups($ftp_source, $ftp_cible, $fichier_zip){
 
 $ok_reception=0;
 $ok_envoi=0;
 $message="";
 
 // recuperation
 $conn_id = ftp_connect($ftp_source['serveur_ftp']);
 $login_result = ftp_login($conn_id, $ftp_source['login_ftp'], $ftp_source['pass_ftp']);
 ftp_pasv( $conn_id, true ); // mode passif 
 if((!$conn_id) || (!$login_result)) { 
 $ok_reception=0;
 $message.= "Echec de la connexion au serveur FTP source
 "; 
 } 
 else { 
 $message.= "Connexion réussie au serveur FTP source ".$ftp_source['login_ftp']."
 "; 
 }
 
 if(ftp_get($conn_id, $fichier_zip, "backup/".$fichier_zip, FTP_BINARY)) { 
 $ok_reception=1; 
 $message.= "Récuperation du fichier ".$fichier_zip." à partir de " .$ftp_source['serveur_ftp']."
 ";
 ftp_close($conn_id);
 // pour le moment, pas suppression du fichier sur le 1er FTP 
 
 // puis envoi
 $conn_id = ftp_connect($ftp_cible['serveur_ftp']);
 $login_result = ftp_login($conn_id, $ftp_cible['login_ftp'], $ftp_cible['pass_ftp']);
 ftp_pasv( $conn_id, true ); // mode passif 
 if((!$conn_id) || (!$login_result)) { 
 $ok_envoi=0; 
 $message.= "Echec de la connexion au serveur FTP cible
 "; 
 } 
 else { 
 $message.= "Connexion réussie au serveur FTP cible
 "; 
 }
 if(ftp_put($conn_id, "backups/".$fichier_zip, $fichier_zip, FTP_BINARY)){ 
 $ok_envoi=1; 
 $message.= "Envoi du fichier ".$fichier_zip." vers " .$ftp_source['serveur_ftp']."
 ";
 unlink($fichier_zip); // suppression du fichier local 
 }
 else { 
 $ok_envoi=0; 
 }
 ftp_close($conn_id); 
 
 
 }
 else { 
 $ok_reception=0; 
 } 
 
 return $message;

}

// backup site 1 : tous les jours

$fichier_zip="site1_".date('Y-m-d').".zip";
$ftp_source = array('serveur_ftp'=>'ftpperso.free.fr', 'login_ftp' => 'site1', 'pass_ftp'=> 'motdepassesite1');
$ftp_cible = array('serveur_ftp'=>'ftpperso.free.fr', 'login_ftp' => 'hebergementbackup', 'pass_ftp'=> 'motdepassehebergementbackup');
echo upload_backups($ftp_source, $ftp_cible, $fichier_zip);

// backup site 2 : le lundi seulement
if(date('w')==1){
 $fichier_zip="site2_".date('Y-m-d').".zip";
 $ftp_source = array('serveur_ftp'=>'ftpperso.free.fr', 'login_ftp' => 'site2', 'pass_ftp'=> 'motdepassesite2');
 $ftp_cible = array('serveur_ftp'=>'ftpperso.free.fr', 'login_ftp' => 'hebergementbackup', 'pass_ftp'=> 'motdepassehebergementbackup');
 echo upload_backups($ftp_source, $ftp_cible, $fichier_zip);
}

Nous appellerons ce fichier transfer_backups_on_free.php (voir plus bas)

Envoyer un mail de compte-rendu

Rappel : j'utilise la classe phpmailer. On retrouve nos deux variables $nb_de_tables et $taille_fichier…

$message=$site_sauvegarde." : ".$nb_de_tables." traitées.
	Taille de la sauvegarde : ".round($taille_fichier)." Ko.";

$site_sauvegarde="nom de mon site"; // remplacez par le nom de votre site

$mail = new PHPmailer();
$mail->IsHTML(true); // le mail sera envoyé au format html
$mail->From="expediteur@mondomaine.com"; // remplacez par l'email de l'expéditeur
$mail->FromName= 'Backup '.$site_sauvegarde;
$mail->AddAddress("destinataire@mondomaine.fr"); // remplacez par l'email du destinataire
$mail->AddReplyTo("expediteur@mondomaine.com"); // remplacez par l'email de l'expéditeur
$mail->Subject="Sauvegarde ".$site_sauvegarde;
$mail->Body=nl2br('La sauvegarde a été effectuée à '.date('H:m').'.<br />'.$message);
if($mail->Send()){ $envoi=1; }
unset($mail);

unlink($fichier_zip); // après l'avoir envoyé, on supprime le fichier zip 

Les cron

A moins que vous voulez les lancer à la mimine, il va falloir programmer ces sauvegardes.

Serveur dédié ou VPS

La tâche va appeler le script php situé dans le répertoire backup/. Si votre VPS s'appuie sur un Webmin, ça se passe dans Système > Tâches cron.

Sinon, en ligne de commande, il faut saisir cette commande pour éditer crontab.

crontab -e

La syntaxe va ressembler à ça (attention sur certains serveurs ce n'est pas www. Si vous ne connaissez pas le chemin absolu de votre site, vous pouvez uploader dessus un script contenant echo $_SERVER[« DOCUMENT_ROOT »])

php /home/monsite/www/backup/index.php

Serveur mutualisé

Ca se passe dans hébergement > votre nom de domaine > Plus+ > Tâches planifiées – Cron et cliquer sur ajouter une planification

Serveur Free

Rappel : pas de cron chez Free. Il faut donc s'appuyer sur un serveur externe. Deux tâches planifiées sont nécessaires.

Exécution à distance de la sauvegarde à partir de votre serveur dédié/VPS (car le cron d'un mutualisé OVH n'autorise pas l'utilisation de wget)

wget -qO /dev/null http://monsite.free.fr/backup

puis 15 min plus tard, rapatriement du fichier contenant la sauvegarde et envoi de la sauvegarde sur un serveur

php /home/dedie/www/backup/transfer_backups_on_free.php

Une alternative

Cette requête permet également d'exporter une table vers un fichier texte.

SELECT * INTO OUTFILE 'matable.sql' FROM 'matable'

Hélas, cette possibilité est bloquée sous Free.fr

Si je tente sous mon VPS, le fichier est bien exporté. Je le retrouve dans le répertoire de stockage de la base (/home/mysql/mabase).

Tandis que sous Wampserver, MySQL me répond que l'option –secure-file-priv l'interdit.

 

Karine SANCHE

Partager cet article