Backup une base de données avec Git

Dans ce post, nous allons aborder une solution possible pour backup une base de données. Cet article est fortement inspiré d’un vieille article anglophone, n’hésitez pas à y jeter un œil.

L’exemple donné dans l’article se base sur une distribution Débian et une base de données MySQL.

Disclamer : Attention ! Cette solution ne semble pas faire l’unanimité. Plusieurs sysadmin sont revenus vers moi pour la déconseiller, merci à eux pour leurs retours! Le principal problème se trouve être le volume de dépôt Git qui risque de grimper avec le temps et de connaître des problèmes sérieux de performance. Cette solution ne convient que pour des bases de données de volume faibles! Je vous laisse le soin de vérifier les impacts possibles de cette mise en place sur la volumétrie de votre dépôt Git à moyen/long terme notamment.

Backup une base de données en production, un enjeu majeur

Faire le backup de vos bases de données de production est un challenge. Pour énormément de raisons, il s’agit d’une réflexion complexe. Il existe beaucoup de besoins différents, appelant à autant de réponses possibles.

Cependant, il existe plusieurs contraintes:

  • la fréquence des backups
  • les problèmes de performances durant le backup
  • l’espace disque utilisé par les backups
  • la stratégie de conservation des backups

Je n’irai pas plus loin dans l’analyse, cet article n’est pas là pour définir qu’elle est l’approche la plus adaptée en fonction de votre situation, mais pour proposer une solution possible à cette problématique.

Utiliser Git pour historiser sa base de données

L’idée d’utiliser Git pour stocker ces backups SQL a du sens. En effet, le SQL est un langage, un backup de base n’est rien d’autre que du code, pourquoi ne pas le stocker directement grâce à Git … comme le reste de votre code.

J’y vois deux avantages :

  • la simplicité de l’automatisation d’un tel processus
  • bénéficier de la performance de stockage de Git qui ne stocke pas l’ensemble des versions des fichiers, mais les différences entre ces versions

Préparation du répertoire

Dans un premier temps, après avoir installé et configuré git, il faut préparer le répertoire dans lequel vous allez gérer votre backup :

# création du répertoire de backup et création du backup initial
mkdir -p /path/to/backup
cd /path/to/backup 
mysqldump -u [user] -p[pwd] --skip-extended-insert [database] > [database].sql 
# initialisation de git
git init 
git add [database].sql 
git commit -m "Initial commit backup database" 

# paramétrer le dépôt git distant
git remote add origin [git-depot-url]
git push -u origin --all
git push --tags

avec :

  • [database] : le nom de base à backup
  • [user] et [pwd] : le nom d’utilisateur et le password de l’utilisateur de la base utilisée pour le backup
  • [git-depot-url] : l’URL du dépôt sur lequel déposer votre base

L’utilisation de l’option « –skip-extended-insert » de mysqldump, permet de rendre beaucoup plus lisible les commits. Chaque commit laissera ainsi apparaître uniquement les lignes ajoutées, modifiées ou supprimés dans la base. De plus, git appréciera, car il devra gérer un dépôt de taille plus raisonnable.

Automatisation des backups

Maintenant que tout est initialisé, il faut passer à l’automatisation des backups, pour cela éditer le fichier crontab avec la commande :

crontab -e

Ajouter la ligne suivante :

0 0 * * * cd /path/to/backup && \ mysqldump -u [user] -p[pwd] --skip-extended-insert [database] > [database].sql && \ git commit -am "Updating DB backup" &&  \ git push

Elle créée le backup et le commit et le push sur Git toutes les heures. La fréquence de backup est à adapter au projet, cron offre de nombreuses possibilités de planification.

En bonus, quelques conseils et astuces pour mettre en place cette solution:

  • Mettre en place la connexion SSH avec le dépôt Git avec une paire de clef public/privé
  • Optimisez les performances de Git en ajoutant un cron hebdomadaire avec la commande « git gc »
  • Créer un utilisateur spécifique dans la base de données ayant uniquement les droits en lecture sur la base de données depuis la machine
  • Faire les backups sur un autre point de montage, pour éviter de grignoter l’espace disque du point de montage de la base de données
  • Utiliser bitbucket, ce dernier permet d’avoir des dépôts privés gratuits

Pour conclure

D’après moi, cette solution peut être efficace dans le cadre de base de données pas trop volumineuse, permettant ainsi de réaliser des backups fréquents sans consommer énormément d’espaces disques grâce à Git.

Si vous avez déjà mis en place un système similaire ou que vous être plus compétent que moi, n’hésitez pas à faire des remarques! Je suis tout ouïe!

N’hésitez pas à me laisser un feedback sur cet article en attribuant une note (système d’étoiles en dessous de l’article) ou en me commentant vos impressions. Si vous l’avez apprécié, vous pouvez également le partager pour mon plus grand plaisir ;).

Sources :


Partager l'article :

Facebooktwittergoogle_plusredditlinkedinmail
 

13 commentaires

  1. enguerran Répondre

    J’ai utilisé un système similaire pendant un an sur une 30aine des bases de données, avec mercurial à la place de git. J’avais aussi mis en place des outils pour ne faire le dump que si la base avait été modifiée (engine MyISAM, donc j’avais la date de dernière modification disponible). Cela évite la surcharge de dump. Bon c’est aussi parce que je voulais un historique toutes les heures pour vérifier qui modifiait quoi et retracer les erreurs des utilisateurs.
    Depuis je suis passé sur un système qui fait des exports du contenu lui-même, pas de la base de donnée. Donc c’est maintenant spécifique à mon schéma. Il y a toujours de backups régulièrement mais fait sans git, avec un historique.

  2. Grunk Répondre

    Git travail mal avec les gros fichiers et il est d’ailleurs recommandé de garder ses dépots sous le Go de données. Pas top pour du backup de base de données donc. Après on peut sans doute améliorer les choses avec git lfs mais je suis pas convaincu de l’intérêt d’utiliser git.
    Git est un outil de versionning pas d’archivage de fichier , ça veux donc dire qu’un serveur git n’a pas forcément la même redondance qu’un serveur de fichier qui lui est là pour ça. Pas idéal donc si le but premier est de sauvegarder les données.

    • Wodric Auteur de l’articleRépondre

      C’étais très vrai il y a quelques années, j’ai pu travailler facilement (et très efficacement) avec des dépôts de 5-10 giga dans l’année écoulée.
      Cependant, se poser la question est important et si la base dépasse ou va dépasser les quelques gigas, il faut bench avec ce volume de données pour vérifier tout cela.

  3. jriley Répondre

    Certains projets comme git-annex ont su gérer la problématique des gros fichiers en enlevant le contenu des metadatas. Du coup, une solution avec git-annex en front et bup en back pourrait être une solution intéressante.

  4. lcoullet (@lcoullet) Répondre

    Je ne critique absolument pas l’astuce en tant que telle (pourquoi pas), mais on versionne des états de la base et non pas ce qui aurait permis de passer d’un état à l’autre. Comme déjà dit plus haut, Git s’accomode mal des gros volumes et à part l’aspect sauvegarde je ne vois pas trop l’intérêt de sauvegarder ce qui n’est pas un fichier texte. (Ex : un fichier PSD, une BDD, un binaire).

  5. lcoullet (@lcoullet) Répondre

    à part l’aspect sauvegarde je ne vois pas trop l’intérêt d’inclure dans git ce qui n’est pas un fichier texte. (Ex : un fichier PSD, une BDD, un binaire).
    (pardon)

    • Wodric Auteur de l’articleRépondre

      Merci d’avoir pris le temps @lcoullet de venir poster ici tes doutes sur la solution. Tu n’es pas le premier à me faire une retour, j’ai donc enrichis le disclamer pour éviter que des personnes partent tête baissée avec cette solution.

      A noter que –skip-extended-insert permet d’avoir un aperçu « simple » (entre guillemet car sur un fichier énorme tout est à relativiser) des modifications. Cependant je vais fouiller un peu pour savoir s’il est très facilement faisable (c’est l’objectif que je me fixe) d’embarquer uniquement les modifications.

      Encore merci,
      See you

  6. aanceau Répondre

    L’idée est intéressante, et pas si déconnante. J’ai déjà utilisé git pour tracer l’évolution de la structure d’une base de dev (100+ développeurs), et ça s’est avéré assez efficace, mais c’était juste des dumps de structure donc pas de problème de volumétrie.

    On peut contourner le problème des gros fichier en demandant à mysqldump de générer un fichier par table (ça passera surement par une requête intermédiaire pour avoir la liste des tables). Bien sur il faudra quand même rester sur une base de taille raisonnable.

    On peut aussi imaginer rebaser automatiquement le dépot de « sauvegarde » pour récupérer de l’espace. Par exemple, garder tous les « commits » du mois en cours, puis 1 par semaine pour le mois précédent, puis 1 par mois pour l’année courante, etc …

    • Wodric Auteur de l’articleRépondre

      L’idée du rebase est intéressant, à essayer je pense. Du coup la mise ne place devient plus complexe.

  7. aurelienrb Répondre

    Je viens justement de mettre en place le même système pour backuper un petite base sqlite (blog ghost) sur un repo privé gitlab (gratuit). Je trouve ça super simple et pratique.
    Dans mon cas, la volumétrie est très petite donc c’est nickel. Par contre, à partie de quelques dizaines de Mo, je doute moi aussi que ce soit une approche viable.

  8. f4b1 Répondre

    C’est vraiment une bonne idée, surtout pour suivre les modifications sur la base de donnée et c’est justement ce que je veux faire ! Parfait pour moi, merci !

Vous aussi participez, laissez un commentaire