Étiquette : SQL

Cela faisait plus d’un an que j’avais vaguement mentionné la question de la sauvegarde d’Unicoda et des autres services que j’héberge. Jusqu’à présent, je m’étais contenté de quelques sauvegardes manuelles effectuées de temps à autre, lorsque le contenu avait bien évolué ou que la sauvegarde précédente commençait à dater. Je me suis donc intéressé aux différentes solutions qu’on rencontre sur le net : rsync, rclone, duplicity, script bash, bacula, pour ne citer que quelque-uns des outils. Si l’automatisation de la sauvegarde ne m’avait jamais semblé essentielle, c’est que j’étais conscient des risques et me reposait sur la duplication RAID de l’hébergeur (pour le risque de défaut matériel), mais surtout, parce que la majorité des informations était généralement présente en local sur l’une ou l’autre de mes machines. Après les récents changements de serveurs et le passage à l’auto-hébergement de nombreux services, la sauvegarde devient critique. Commençons donc par nous pencher sur la sauvegarde des données du serveur SQL.

Pour exporter les données d’un serveur MySQL, la commande mysqldump est tout indiquée. Pour sauvegarder l’intégralité des bases du serveur avec l’utilisateur root, on pourra par exemple utiliser :

mysqldump -u root -p --all-databases > all_databases.sql

De la même manière, il est possible de n’extraire qu’une seule base de données :

mysqldump -u [user] -p maBase > maBDD.sql

Il est possible de spécifier le mot de passe de l’utilisateur directement dans la commande via -p[motDePasse] ou encore -p'[motDePasse]’.  Une fois en possession d’une copie de notre base, l’import s’effectue simplement en utilisant :

mysql -u [user] -p maNouvelleBase < maBDD.sql

J’ai eu l’occasion de mettre à l’épreuve cette méthode lors des différentes migrations d’Unicoda; toujours avec succès et sans jamais rencontrer de problème.

Maintenant que nous avons une façon de sauvegarder les données de MySQL, il est temps de s’intéresser à l’automatisation du processus.  Afin de régler la question de l’accumulation des fichiers de sauvegarde, j’ai décidé d’utiliser logrotate, en détournant quelque peu son usage pour l’appliquer à mes sauvegardes SQL et non à des fichiers de logs. Avant toutes choses, je crée un nouvel utilisateur au niveau de MySQL en le limitant aux droits Lock Tables et Select sur la base que je veux sauvegarder.

GRANT LOCK TABLES, SELECT ON maBase.* TO '<user>'@'localhost' IDENTIFIED BY '<password>';

On passe ensuite à la configuration de logrotate pour notre fichier de sauvegarde. J’ajoute donc un fichier dans /etc/logrotate.d/sauvegarde-base-sql avec le contenu suivant :

/var/backups/mysql/maBDD.sql.gz {
  daily
  dateext
  rotate 21
  nocompress
  create
  postrotate
  mysqldump --single-transaction --add-drop-table -u user -pmonMotDePasse baseASauvegarder > /var/backups/mysql/maBDD.sql
  gzip -9f /var/backups/mysql/maBDD.sql
  chown user:user /var/backups/mysql/maBDD.sql.gz
  chmod 640 /var/backups/mysql/maBDD.sql.gz
  endscript
}

Côté paramètres :

• L’exécution est journalière.
• La date est ajoutée au fichier lors de la rotation.
• On conserve 21 fichiers.
• Pas de compression
• Le nouveau fichier est créé avec les mêmes permissions et le même propriétaire que le précédent (create).
• On indique les commandes à exécuter après rotation (postrotate) à savoir :
  • Extraction des données de la base
  • Compression du fichier
  • Modification du propriétaire
  • Modification des droits

Enfin, on prépare l’état initial du script en ajoutant un fichier vide de départ :

sudo touch /var/backups/mysql/maBDD.sql.gz

Il est alors possible de tester l’exécution avec l’instruction suivante (débug avec -d et mode verbeux avec -v) :

sudo logrotate -f /etc/logrotate.d/sauvegarde-base-sql

La restauration de la base s’effectue avec la commande d’import décrite plus haut, il faudra bien sûr au préalable décompresser le fichier de sauvegarde :

gunzip maBDD.sql.gz

Grâce à cette configuration, je dispose désormais de 21 jours de sauvegarde de ma base SQL, le tout entièrement automatisé. Logrotate me permet de bénéficier simplement de la rotation de mes fichiers de sauvegarde. Il aurait été aussi possible d’utiliser un script bash et une tâche cron pour un résultat similaire, mais cela aurait nécessité de s’occuper de la partie gestion des anciens fichiers; ce que logrotate fait très bien pour moi. Dans le prochain article dédié à la sauvegarde, je décrirai la solution que j’ai retenue pour la sauvegarde distante des fichiers du serveur, dont ces exports SQL.

Par ailleurs, n’hésitez pas à partager votre manière de sauvegarder votre base de données dans les commentaires.

Source : scottlinux, LeaseWeb Labs, Linode.