Étiquette : sauvegarde

Sauvegarde automatique de base de données MySQL: mon script

Une sauvegarde automatique de la base de données MySQL peut sauver! Un piratage de site peut toujours arriver et sans sauvegarde, vous êtes bon pour tout recommencer depuis zéro.

Faire une sauvegarde (un backup) sur le même serveur, c’est un début mais ça ne suffit pas. Si un pirate a accès à votre serveur, il peut facilement tout supprimer, y compris vos sauvegarder. C’est pourquoi il faut conserver vos backups sur un autre support. Tout d’abord sur un autre serveur, puis également sur un support déconnecté d’Internet.

Quelles solutions possibles?

Sauvegarde automatique avec WordPress

Il existe plusieurs plugins WordPress qui fonctionnent très bien. Le plus connu est certainement UpDraftPlus.

Mais les plugins WordPress… faut pas non plus en abuser. Il y a déjà assez de plugins dans tous les sens, lorsque je peux éviter d’en ajouter, j’évite!

Sauvegarde WordPress sans plugin

Pour sauvegarder les articles de WordPress sans plugin et de manière automatique, il faut passer par un script de sauvegarde. Ce script fera appel à MysqlDump pour créer des sauvegardes et Crontab pour le planifier chaque jour.

Sauvegarde automatique avec MysqlDump et Crontab

J’ai mis en place un script qui effectue une sauvegarde automatique de la base de données tous les jours sur un NAS situé chez moi. Tous les 3 mois, je fais une sauvegarde froide, je fais une copie des dernières sauvegarde sur un disque externe. Avec ce process, j’ai une réponse à plusieurs risques:

  • Piratage du site: récupération depuis la sauvegarde de la veille
  • Piratage du site + du serveur de sauvegarde: récupération depuis la sauvegarde d’il y a max 3 mois.

Dans mon cas, le « serveur de sauvegarde » est un Raspberry Pi. C’est lui qui va créer les backups et les enregistrer sur le NAS.

Sécurité de la sauvegarde automatique de base de données

L’idée générale, c’est une tâche ordonnancé par cron qui se connecte automatiquement au serveur, sauvegarde les bases de données et les transfert sur le NAS. Pour limiter les accès du programme de sauvegarde, on va créer un utilisateur spécial pour se connecter en SSH, et un autre pour accéder à la base de donnée.

Créer un utilisateur sans permissions dans Linux

La configuration par défaut suffit. La commande est simple:

ubuntu@serveur: sudo adduser backupuser

Saisissez un mot de passe compliqué, issu d’un générateur de mot de passe. De toutes façons, on ne va jamais l’utiliser puisque la connexion SSH par mot de passe est désactivé sur le serveur web.

Pour permettre la connexion par clef SSH, il faut ajouter notre clef . Copier la clef SSH du serveur de sauvegarde:

ubuntu@pi:~$ cat ~/.ssh/id_rsa.pub

Sur le serveur web, ajouter votre clef pour le nouvel utilisateur:

ubuntu@serveur:~$ sudo -u backupuser bash
backupuser@serveur:~$ mkdir -p ~/.ssh
backupuser@serveur:~$ echo "MACLEFSSH" >> ~/.ssh/authorized_keys

Désormais, le serveur de sauvegarde sait se connecter au serveur web, avec un utilisateur sans permissions. Sans risque donc.

Créer un utilisateur MySQL dédié pour la sauvegarde

De même pour l’utilisateur MySQL (ou MariaDB). On créé un utilisateur n’ayant pour permissions que la lecture (=SELECT).

On se connecte à MariaDB avec les droits root pour créer cet utilisateur:

ubuntu@serveur:~$ sudo mysql
MariaDB [(none)]> GRANT SELECT ON *.* TO 'backupuser'@'localhost' IDENTIFIED BY 'secret-password';

On peut maintenant se connecter avec cet utilisateur pour sauvegarder la base de données: il peut lire toutes les bases de données et ne peut en modifier aucune: parfait!

Script de sauvegarde automatique

Le programme de sauvegarde est piloté par le serveur de sauvegarde (mon Raspberry Pi). Il va se connecter en SSH, exécuter le programme de sauvegarde, rapatrier les fichiers de sauvegarde créés et supprimer ces fichiers du serveur web.

#!/bin/sh

SQL_USER="backupuser"
SQL_PASS="secret-password"
SQL_HOST="localhost"

SSH_USER="backupuser"
SSH_HOST="monServeur"

FILE_PREFIX="backup_db"
FILE_PATH="/media/smb/Backups/"

FILE_DATE=`date +%Y-%m-%d-%H-%M-%S`
TARGET_FILE_PATH="/tmp/backupuser/"

DB_SCRIPT="MYSQL_PWD=$SQL_PASS mysql -h $SQL_HOST -u $SQL_USER -e \"SHOW DATABASES;\" | tr -d \" | \" | grep -v -e Database -e _schema -e mysql"
DB_SAVE="MYSQL_PWD=$SQL_PASS mysqldump -h $SQL_HOST -u $SQL_USER --single-transaction --skip-lock-tables \$DB_NAME > $TARGET_FILE_PATH$FILE_PREFIX.\$DB_NAME.$FILE_DATE.sql"
DB_COMPRESS="gzip $TARGET_FILE_PATH$FILE_PREFIX.\$DB_NAME.$FILE_DATE.sql"

ssh -T $SSH_USER@$SSH_HOST << EOSSH
mkdir -p $TARGET_FILE_PATH
DATABASES=\$($DB_SCRIPT)
for DB_NAME in \$DATABASES; do
	\$($DB_SAVE)
	\$($DB_COMPRESS)
done
EOSSH

# On transfert les données du script client
scp $SSH_USER@$SSH_HOST:$TARGET_FILE_PATH$FILE_PREFIX.*.sql.gz $FILE_PATH

# On supprime le script client et les fichiers temporaires
ssh $SSH_USER@$SSH_HOST "rm $TARGET_FILE_PATH$FILE_PREFIX.*.sql.gz"

Déchiffrons le fonctionnement du script, point par point

1/ La configuration du script

Ici, ce sont les variables qu’il faut modifier pour que le script fonctionne.

  • Nom du serveur Web
  • Utilisateur SSH du serveur Web
  • Nom du serveur SQL
  • Utilisateur SQL
  • Mot de passe SQL
  • Chemin où seront enregistrés les fichiers de sauvegarde
  • Préfixe pour le nom des fichiers de sauvegarde
SQL_USER="backupuser"
SQL_PASS="secret-password"
SQL_HOST="localhost"

SSH_USER="backupuser"
SSH_HOST="monServeur"

FILE_PREFIX="backup_db"
FILE_PATH="/media/smb/Backups/"

2/ Les variables de travail

On stocke la date actuelle qui servira à nommer les fichiers, afin d’avoir des noms fichiers uniques.

Le chemin temporaire de travail peut être modifié. /tmp est généralement stocké en RAM. Si vous avez des grosses bases de données, ça pourrait ralentir le serveur web.

FILE_DATE=`date +%Y-%m-%d-%H-%M-%S`
TARGET_FILE_PATH="/tmp/backupuser/"

Les 3 fonctions à exécuter

On définit les 3 opérations à exécuter:

  • Lire la liste de la base de données
  • Sauvegarder une base de données
  • Compresser un fichier de sauvegarde

Ces fonctions seront exécutés dans un tunnel SSH.

Les options de mysqldump:

  • Single Transaction: la sauvegarde se fera dans une transaction SQL: ça permet de conserver l’intégrité des données sans bloquer la base de données.
  • Skip Lock Tables: pas besoin de vérouiller la base de données pendant le backup puisqu’on l’effectue dans une seule transaction SQL

Je me suis inspiré de cet article intéressant sur les options de mysqldump.

DB_SCRIPT="MYSQL_PWD=$SQL_PASS mysql -h $SQL_HOST -u $SQL_USER -e \"SHOW DATABASES;\" | tr -d \" | \" | grep -v -e Database -e _schema -e mysql"

DB_SAVE="MYSQL_PWD=$SQL_PASS mysqldump -h $SQL_HOST -u $SQL_USER --single-transaction --skip-lock-tables \$DB_NAME > $TARGET_FILE_PATH$FILE_PREFIX.\$DB_NAME.$FILE_DATE.sql"

DB_COMPRESS="gzip $TARGET_FILE_PATH$FILE_PREFIX.\$DB_NAME.$FILE_DATE.sql"

Le tunnel SSH

On ouvre le tunnel SSH et on créer le dossier qui va recevoir nos fichiers temporaires. On sort le liste des bases de données puis on boucle dessus pour créer et compresser une sauvegarde pour chaque base de données

ssh -T $SSH_USER@$SSH_HOST << EOSSH
mkdir -p $TARGET_FILE_PATH
DATABASES=\$($DB_SCRIPT)
for DB_NAME in \$DATABASES; do
	\$($DB_SAVE)
	\$($DB_COMPRESS)
done
EOSSH

Rapatriement des fichiers de sauvegarde

Une simple copie avec SSH de tous les fichiers backups compressés (*.sql.gz) vers notre répertoire de sauvegarde.

scp $SSH_USER@$SSH_HOST:$TARGET_FILE_PATH$FILE_PREFIX.*.sql.gz $FILE_PATH

Nettoyage des fichiers temporaires

Pour ne pas laisser trainer des fichiers inutiles sur le serveur, on supprime tous les fichiers backups compressés de notre répertoire de travail

ssh $SSH_USER@$SSH_HOST "rm $TARGET_FILE_PATH$FILE_PREFIX.*.sql.gz"

Planification de la sauvegarde automatique dans Cron

Il ne reste plus qu’à planifier une sauvegarde automatique de base de données toutes les nuits, et le tour est joué. On ouvre crontab pour un utilisateur standard du serveur de sauvegarde.

ubuntu@pi:~$ crontab -e

Et on ajoute cette ligne:

3 33 * * * /home/ubuntu/Scripts/backup-bdd.sh

Tous les jours, à 3h33, une sauvegarde automatique de la base de données du serveur Web sera effectuée.

Faire taire le message d’accueil SSH

Quand vous utiliserez ce script, il est possible que le message d’accueil SSH vous agace. C’est mon cas.

La solution est très simple, il suffit d’ajouter un fichier .hushlogin dans son répertoire personnel.

backupuser@serveur:~$ touch .hushlogin