Tutoriel Vidéo MySQL PHP Sauvegarder ses bases de données

Télécharger la vidéo Télécharger les sources

Aujourd'hui, je voulais partager un petit bout de code que j'ai mis en place pour me faciliter la vie avec les sauvegardes de mes bases de données mysql. Quand on n'a pas accès aux commandes linux (mutualisé par exemple) créer un script PHP pour sauvegarder nos bases de données peut s'avérer plutôt utile. Pour nous simplifier le travail nous utiliserons mysqldump-php qui permet de gérer la génération du fichier SQL très simplement.

Création du projet

Nous allons commencer par créer un nouveau dossier qui devra être accessible depuis une page Web (dans le cas d'un mutualisé). Ensuite, afin de gérer plus simplement nos dépendances nous utiliseront composer. Nous allons donc démarrer composer en lançant en ligne de commande

composer init
# ou si vous n'avez pas composer d'enregistré globalement
php composer.phar init

Maintenant, nous pouvons récupérer une dépendance qui va nous être très utile: mysqldump-php développée et maintenue par Diego Torres qui est disponible sur le site packagist.org

Dans le fichier composer.json qui a été généré par la commande composer.init nous allons ajouter

    "require": {
        "ifsnop/mysqldump-php": "dev-master"
    }

Enfin, on peut lancer l'installation

composer install

On a maintenant notre dossier vendor qui contient notre autoloader ainsi que la librairie mysqldump-php

Sauvegarder les bases

Maintenant que nous avons tout ce qu'il faut, nous allons pouvoir procéder à la partie connexion et exécution du script. Pour cela, dans notre fichier php nous allons charger l'autoloader de Composer et initialiser mysqldump

require 'vendor/autoload.php';

$db_username = 'root';
$db_password = 'root';

$dump = new \Ifsnop\Mysqldump\Mysqldump($db_name, $db_username, $db_password);
$dump->start('dump.sql');

Cela va permettre de créer un fichier dump.sql. Mais ce que l'on souhaite faire c'est sauvegarder plusieurs bases de données à la fois pour automatiser la chose. Donc nous allons devoir commencer par récupérer la liste de nos bases de données.

$db_username = 'root';
$db_password = 'root';
$db = new PDO('mysql:host=localhost', $db_username, $db_password);
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
$q = $db->query('SHOW DATABASES');
$databases = $q->fetchAll();

J'ai ici configuré PDO pour récupérer les informations sous forme d'objets car je préfère l'écriture, mais vous pouvez évidemment adapter à vos besoins. Le problème de cette requête c'est qu'elle va récupérer TOUTES les bases et certaines ne sont pas nécessaires. Nous allons créer un tableau contenant les bases à exclure.

$exclude = ['information_schema', 'mysql', 'performance_schema'];

On utilisera la fonction in_array() pour vérifier si le nom de notre table se trouve dans cette liste. Pour finir notre script de sauvegarde il nous suffit de faire une boucle sur $databases.

foreach ($databases as $database) {
    $db_name = $database->Database;
    if (!in_array($db_name, $exclude)) {
        $file = $db_name . '.sql';
        try {
            $dump = new \Ifsnop\Mysqldump\Mysqldump($db_name, $db_username, $db_password);
            $dump->start($file);
        } catch (Exception $e) {
            echo 'Impossible de faire une sauvegarde : ' . $e->getMessage();
        }
    }
}

J'ai entouré la partie dump d'un try / catch afin d'avoir un retour en cas de problème. Donc notre script va maintenant créer des fichiers .sql pour chacune de nos tables.

Envoie des dump par FTP

Nos dumps ne servent à rien si ils sont stockés sur le même serveur que le site. On va donc avoir besoin d'envoyer nos fichiers ailleurs, sur un serveur de backup. Pour cela nous utiliserons une connexion FTP.

// On se connecte à notre FTP 
$ftp_connect = ftp_connect(FTP_HOST, 21, 5);
ftp_login($ftp_connect, FTP_USERNAME, FTP_PASSWORD);
ftp_pasv($ftp_connect, true);  // On active le mode passif si la connexion ne marche pas sans

Une fois cette connexion ouverte on va pouvoir envoyer nos dumps en utilisant la connexion FTP $ftp_connect. Pour le ftp j'ai créé un dossier dumps qui ne sera pas accessible à distance (un niveau au dessus du dossier public). J'en profite aussi pour prefixer le nom de mes dumps par une date, ceci afin de m'y retrouver plus facilement et d'éviter que chaque sauvegarde soit écrasée par la précédente.

$date = date('Y-m-d');
if (ftp_put($ftp_connect, '/dumps/' . $date . '-' . $db_name . '.sql', $file, FTP_ASCII)) {
    echo "{$db_name}.sql envoyé !\n";
} else {
    echo 'Erreur lors de l\'envoi du fichier ' . $db_name . ' .sql';
}

Malheureusement ftp_put ne propose pas d'Exception en cas d'erreur et se contentera de renvoyer un false. Donc on met une petite condition afin d'afficher un retrou en cas de problème lors de l'envoi FTP.

On peut ensuite supprimer le dump sql de notre serveur local

unlink($file);

Et on finit par fermer la connexion au serveur FTP avec un ftp_close() avec un ftp_close()

ftp_close($ftp_connect); 

Tâches planifiées

Enfin la dernière chose à faire c'est maintenant de générer ce dump tous les soirs. Si vous êtes sur un serveur dédié il suffit de créer une tâche cron.

crontab -e

Puis d'ajouter

0 0 * * * php /chemin/du/backup/file.php

L'avantage c'est que l'on peut placer notre dossier de backup où on le souhaite sur le serveur. On peut ensuite l'éxécuter avec la commande php.

En revanche si vous êtes sur un mutualisé vous n'aurez pas accès à ces commandes. Parfois votre hébergeur vous laissera configrer une tâche qui appellera une URL. Dans ce cas, il faudra que votre système de sauvegarde soit appellable par le web (moins sécurisé vu que n'importe qui pourra déclencher la sauvegarde de vos bases, et ralentir votre serveur par la même occasion).

Pour améliorer

Nous avons ici une bonne base de code pour gérer nos sauvegardes MySQL. Comme d'habitude il est possible d'améliorer grandement le script. Quelques pistes :

  • Créer un dossier par mois sur le FTP
  • Sauvegarder sur plusieurs services, Amazon AWS, Dropbox ou autre
  • Ecrire des tests unitaire pour s'assurer de la pertinence de notre outil