Tux aux hermines
Accueil du site > Les Documentations > Sauvegardez vos bases de données

Sauvegardez vos bases de données

mercredi 16 juin 2010, par Patrice « PatLeNain » Le Gurun

La sauvegarde des données informatiques est une activité trop souvent négligée, mais essentielle. Qui n’a jamais pesté quand un disque dur crashe et que les données contenues sont irrémédiablement perdues ?

Dans cet article, je vais vous détailler pas à pas comment sauvegarder le contenu de vos bases de données MySQL et PostgreSQL à partir d’un script fait maison fonctionnant sur mon serveur personnel sous Debian Lenny.

Les bases de données

Aujourd’hui les bases de données sont incontournables, que ce soit pour gérer un site web, faire fonctionner une galerie photos ou un wiki, ou encore configurer un serveur de courriel.

Dans le monde libre, deux SGBD [1] principaux existent, MySQL et PostgreSQL (d’autres existent, comme SQLite, mais ils ne seront pas traités dans cet article). Le premier est léger et beaucoup utilisé dans les applications web. Le second et plus lourd, mais plus performant et adapté aux applications complexes.

Ces serveurs ont la capacité de générer des sauvegardes au format SQL via des outils en ligne de commande. Cette capacité permet d’être pleinement intégrée à des scripts, ce que nous allons utiliser dans cet article.

Principe de fonctionnement

Le principe est très simple. Il s’agit de lancer, de manière automatique, un script nocturne, permettant de sauvegarder tout le contenu des bases de données.

Note : Dans cet article, sauf mention contraire, toutes les commandes sont lancées par le super-utilisateur (appelé root).

Pré-requis

Le premier pré-requis est de choisir dans quel répertoire seront sauvegardées vos données. Ce choix est important car il va conditionner la sécurité de cette sauvegarde.

Dans mon cas, j’ai choisi de réaliser mes sauvegardes sur un disque différent du disque contenant les données du système. Ce choix permet, en cas de crash du disque système, de pouvoir récupérer ces données. Différentes possibilités permettent encore d’améliorer cette sécurité, comme utiliser un disque disponible en réseau, utiliser un système redondant comme le RAID, ou encore combiner ces possibilités.

Pour cet exemple, j’utilise une partition située sur un disque externe, partition montée sur le répertoire /backup.

Je ne réalise pas la sauvegarde directement dans le répertoire /backup, mais dans un sous-répertoire (/backup/db_dumps). Ce sous-répertoire a des droits réduits pour limiter la visibilité des sauvegardes (qui contiennent des données sensibles) et pour réserver au seul super-utilisateur la possibilité de restaurer les données. Dans le script, ce sous-répertoire sera désigné par la variable $DEST_DIR et défini par :

  1. DEST_DIR=/backup/db_dumps

Pour créer ce répertoire, on utilise les commandes suivantes :

  1. mkdir /backup/db_dumps
  2. chown root:root /backup/db_dumps
  3. chmod 640 /backup/db_dumps

Le second pré-requis est de permettre le lancement automatique de la sauvegarde des bases MySQL [2]. Ce lancement automatique est permis par l’utilisation d’un fichier de configuration nommé .my.cnf situé dans le répertoire /root. Le fichier doit contenir au moins les informations suivantes [3] :

  1. [mysqldump]
  2. user=root
  3. password=toto
  4. host=localhost

toto est le mot de passe de l’administrateur de la base MySQL, il est à remplacer par le votre. Cette section permet à la commande mysqldump de se connecter à la base de données sans demander de mot de passe.

Sauvegarde complète des bases

La sauvegarde complète des bases va contenir l’ensemble du contenu des bases de données. Cette sauvegarde va permettre de restaurer les bases de données à l’identique.

Pour MySQL, cette sauvegarde sera réalisée par la suite de commandes suivantes :

  1. # Rotation des fichiers de sauvegarde
  2. rotate mysql_dumpall.sql
  3. # Lance la sauvegarde complète
  4. mysqldump --all-databases > $DEST_DIR/mysql_dumpall.sql
  5. # Limite les droits sur la sauvegarde
  6. chmod 400 $DEST_DIR/mysql_dumpall.sql

Pour PostgreSQL la sauvegarde sera un peu plus compliquée :

  1. # Rotation des fichiers de sauvegarde
  2. rotate pg_dumpall.sql
  3. # Lance la sauvegarde complète par l'utilisateur postgres
  4. su -c "pg_dumpall > ~/pg_dumpall.sql" postgres
  5. # Modifie le propriétaire de la sauvegarde
  6. chown root:root ~postgres/pg_dumpall.sql
  7. # Limite les droits sur la sauvegarde
  8. chmod 400 ~postgres/pg_dumpall.sql
  9. # Déplace la sauvegarde dans le répertoire de destination
  10. mv ~postgres/pg_dumpall.sql $DEST_DIR

À la suite de ces instructions, deux fichiers seront générés, mysql_dumpall.sql et pg_dumpall.sql, situés dans le répertoire /backup/db_dumps, avec les droits de lecture uniquement pour le super-utilisateur.

Sauvegardes individuelles

Un serveur de base de données pouvant contenir plusieurs bases de données, nous allons les sauvegarder individuellement. Ainsi, une base de données pourra être restaurée suite à une manipulation accidentelle, ou encore réaliser une migration d’un serveur à un autre.

Afin d’éviter les commandes répétitives, nous allons créer des fonctions.

Pour MySQL, la fonction sera la suivante :

  1. # Sauvegarde une base de données MySQL
  2. # Usage : mydump <base>
  3. # Résultat : fichier nommé mysql_<base>.sql contenant la sauvegarde dans le
  4. #  répertoire $DEST_DIR
  5. mydump() {
  6.     # $1 représente le nom de la base à sauvegarder
  7.     # Rotation des fichiers de sauvegarde
  8.     rotate mysql_$1.sql
  9.     # Sauvegarde la base de données
  10.     mysqldump --databases $1 > $DEST_DIR/mysql_$1.sql
  11.     # Limite les droits sur cette sauvegarde
  12.     chmod 400 $DEST_DIR/mysql_$1.sql
  13. }

Pour PostgreSQL, la fonction sera la suivante :

  1. # Sauvegarde une base de données PostgreSQL
  2. # Usage : pgdump <base>
  3. # Résultat : fichier nommé pg_<base>.sql contenant la sauvegarde dans le
  4. #  répertoire $DEST_DIR
  5. pgdump() {
  6.     # $1 représente le nom de la base à sauvegarder
  7.     # Rotation des fichiers de sauvegarde
  8.     rotate pg_$1.sql
  9.     # Sauvegarde la base de données
  10.     su -c "pg_dump -C $1 > ~/pg_$1.sql" postgres
  11.     # Change le propriétaire de la sauvegarde
  12.     chown root:root ~postgres/pg_$1.sql
  13.     # Limite les droits sur cette sauvegarde
  14.     chmod 400 ~postgres/pg_$1.sql
  15.     # Déplace la sauvegarde dans le répertoire de destination
  16.     mv ~postgres/pg_$1.sql $DEST_DIR
  17. }

Ces fonctions seront appelées base par base. Par exemple :

  1. # Base système
  2. mydump mysql
  3. # Site web sous SPIP
  4. mydump spip
  5. # Site web de test sous SPIP
  6. mydump spiptest
  7. # Base utilisée par SpamAssassin
  8. mydump spamassassin
  9. # Calendrier PHP
  10. pgdump calendar
  11. # Galerie photos
  12. pgdump gallery

Rotation des sauvegardes

Vous avez pu voir plusieurs appels à rotate. Cette fonction permet, à chaque lancement, de copier les sauvegardes en indiquant le moment de cette copie et de compresser ces copies. Ainsi vous obtenez un historique des sauvegardes. La fonction est la suivante :

  1. ## Fonction de rotation des sauvegardes
  2. # Usage : rotate <fichier>
  3. rotate() {
  4.     # Copie <fichier> situé dans $DEST_DIR
  5.     # -d     Ajoute la date de la copie au nom du fichier copié
  6.     # -C     Force la suppression des anciennes copies
  7.     # -c 7   Ne garde que 7 copies
  8.     # -p     Conserve le propriétaire et les droits du fchier
  9.     # $1     Nom du fichier à copier
  10.     savelog -c 7 -d -C -p $DEST_DIR/$1 > /dev/null 2>&1
  11. }

Optimisation MySQL

À titre facultatif vous pouvez lancer l’optimisation des bases MySQL [4] à la fin de la sauvegarde.

De la même manière que pour la sauvegarde, il faut ajouter au fichier .my.cnf quelques informations pour que cette optimisation se fasse sans demander de mot de passe :

  1. [mysqlcheck]
  2. user=root
  3. password=toto
  4. host=localhost

L’appel de cette optimisation se fera par l’appel :

  1. mysqlcheck -A -a -o > /dev/null

Mise en place de la sauvegarde

Toutes ces commandes seront intégrées dans un script bash appelé backup_db.sh situé dans le répertoire /usr/local/sbin [5]. Ce script devra avoir les droits d’exécution.

Notre script étant terminé, il faut maintenant l’appeler. Cet appel se fera via le démon cron qui lance de manière régulière diverses actions. J’ai choisi de lancer ce script tous les jours à minuit. Suivant vos besoins vous pouvez le lancer plus ou moins souvent et/ou à des heures différentes. Ce lancement se fait par la création d’un fichier backup_db dans le répertoire /etc/cron.d contenant :

  1. 0 0 * * *       root    /usr/local/sbin/backup_db.sh

C’est fini !

Voila, les sauvegardes sont en place. Aucune manipulation supplémentaire n’est nécessaire. Seul l’ajout ou la suppression des sauvegardes individuelles est à réaliser en fonction des créations/suppressions de bases de données.

Ce script n’est pas la panacée, il ne remplace pas une sauvegarde complète du système. Des améliorations sont possibles, comme déterminer automatiquement les bases à sauvegarder individuellement, ou encore sauvegarder les bases PostgreSQL de manière plus simple.

J’espère que cet article vous a été utile.

P.-S.

Texte libre de diffusion et de modification, à condition de mentionner l’auteur, de mettre un lien sur l’article d’origine et de le redistribuer sous les mêmes conditions.

Creative Commons License
Cette création est mise à disposition sous un contrat Creative Commons.

Documents joints

  • Script de sauvegarde (Zip – 1.6 ko)

    Script Bash de sauvegarde de bases de données MySQL et PostgreSQL

Notes

[1] Serveur de Gestion de Base de Données

[2] Pour PostgreSQL, aucune préparation n’est nécessaire car le lancement de la sauvegarde diffère).

[3] Si ce fichier n’existe pas, il faut le créer avec les droits de lecture/écriture uniquement pour l’utilisateur root.

[4] PostgreSQL gère les optimisations automatiquement.

[5] Répertoire contenant les commandes systèmes spécifiques au serveur

2 Messages de forum

  • Sauvegardez vos bases de données 3 février 2014 13:54, par Romary

    Bonjour,

    Ne manquerait-il pas la commande bash dans le paramétrage du cron ?

    0 0 * * * root /usr/local/sbin/backup_db.sh =>

    Corrigé : 0 0 * * * root bash /usr/local/sbin/backup_db.sh

    En tout cas si on laisse la première ligne telle quelle sur ubuntu 12.04LTS, ça ne fonctionne pas.

    Cordialement,

    Romary

    • Sauvegardez vos bases de données 3 février 2014 19:33, par Patrice « PatLeNain » Le Gurun

      Bonjour
      Cela me paraissait couler de source, mais le script soit avoir les droits d’exécutions (chmod +x /usr/local/sbin/backup_db.sh).
      Mais votre correction est tout à fait valable :)

SPIP | squelette | | Plan du site | Suivre la vie du site RSS 2.0