Script – Sauvegarde des bases de données sous Linux

I.  Présentation

Afin d'assurer une tolérance aux pannes et une sauvegarde des informations importantes, une sauvegarde des bases de données Mysql peut être faite avec un script.

Il est en effet intéressant de pouvoir automatiser la sauvegarde des bases de données d'un serveur, car elles contiennent souvent des informations importantes et vitales dans une entreprise. Ce backup sera effectué par un utilisateur « backup » qui sauvegardera régulièrement et automatiquement l'ensemble des bases de données du serveur.

II. Création de l'utilisateur de backup

Il faut tout d'abord créer un utilisateur qui aura uniquement les droits de lecture sur l'ensemble des bases de données. Les opérations seront lancées à partir d'un script, il serait donc dangereux de les lancer avec un utilisateur ayant des droits de modification, de création ou de suppression sur les bases de données.On se connecte au serveur de base de données :

mysql -u root -p

On crée l'utilisateur 'backup'@'localhost'. Il est important de préciser que backup n'agira uniquement que depuis le serveur local (depuis le script qui sera situé sur le serveur).

CREATE USER 'backup'@'localhost' IDENTIFIED BY '';

On donne uniquement les droits de lecture à l'utilisateur 'backup'@'localhost' sur toutes les bases de données :

GRANT SHOW DATABASES, SELECT, LOCK TABLES, RELOAD ON *.* to 'backup'@'localhost' ;

On demande au serveur de relire la table des droits pour s'assurer que les commandes prennent bien effet :

FLUSH PRIVILEGES ;

III. Le script de sauvegarde

Si on dispose de plusieurs bases de données, il est intéressant de faire un script qui automatisera le listage, la lecture et la sauvegarde de chaque base de données.Il nous faut tout d'abord créer un répertoire temporaire afin de stocker les données :

mkdir /home/user/save_BD

Pour sauvegarder la base de données, nous utiliserons mysqldump. C'est un utilitaire qui permet d'exporter une base ou un groupe de bases vers un fichier texte, pour la sauvegarde ou le transfert entre deux serveurs (pas nécessairement entre serveurs MySQL).

L'export contiendra les requêtes SQL nécessaires pour créer la table et la remplir.

Puis on crée le script qui automatisera la sauvegarde.

vim backup_script.sh

Nous y mettrons ce contenu :

#!/bin/bash
# On liste nos bases de données
LISTEBDD=$( echo 'show databases' | mysql -u backup --password=< mot de passe >)
for BDD in $LISTEBDD do
# Exclusion des BDD information_schema , mysql et Database
if [[ $BDD != "information_schema" ]] && [[ $BDD != "mysql" ]] && [[ $BDD != "Database" ]]; then
# Emplacement du dossier ou nous allons stocker les bases de données, un dossier par base de données
  CHEMIN=/home/user/save_BD/$BDD
# On backup notre base de donnees
  mysqldump -u backup --single-transaction --password= $BDD > "$CHEMIN/$BDD.sql"
  echo "|Sauvegarde de la base de donnees $BDD.sql ";
fi
done

Explication des paramètres:

• LISTEBDD : Cette variable contiendra l'ensemble des noms des bases de données de notre serveur MySQL :

• echo 'show databases" : on chercher ici à lister les bases de données de notre serveur MySQL

• | mysql -u backup --password=< mot de passe >) : on indique ici les identifiants de la connexion MySQL. -u est pour l'utilisateur.

• mysqldump : on lance l’outil mysqldump qui sert au backup des bases de données

• -u backup : C'est l'utilisateur qui agira dans mysqldump, il doit avoir au minimum les droits de lectures sur les bases de données

• --single-transaction : Cette option ajoute la commande SQL BEGIN avant d'exporter les données vers le serveur. C'est généralement pratique pour les tables InnoDB et le niveau d'isolation de transaction READ_COMMITTED, car ce mode va exporter l'état de la base au moment de la commande BEGIN sans bloquer les autres applications. Il faut pour utiliser cette option que l'utilisateur ait les droit "LOCK TABLES"

• --password : C'est le mot de passe de l'utilisateur en question

• $BDD : nom de la base de données, ce nom est une variable qui change en fonction de la lecture

• $LISTEDBB> "$CHEMIN/$BDD"_"$DATE.sql": on stocke ensuite la base de données dans un fichier portant son nom avec l'extension ".sql"

Il faut donner la possibilité à ce script d'être exécuté :

chmod +x backup_script.sh

IV. L'automatisation de la tâche

Nous pouvons utiliser le service cron pour le lancement automatique du script de sauvegarde à un intervalle régulier. Par exemple, si nous souhaitons que le script s'exécute toutes les nuits à 4 heures du matin. Nous utiliserons la commande "contrab -e" pour modifier le fichier contrab. Puis nous y ajouterons cette ligne:

00 4 * * * root sh

Le script exécutera tous les jours à 4 heures du matin.

V. Améliorations

A. Affichage de la date

Pour savoir à quel moment a été faite la sauvegarde, nous pouvons enregistrer l'heure dans le nom du fichier enregistré. On stocke pour cela la date dans une variable :

DATE=`date +%y_%m_%d`

Le nom de la variable est $DATE, on utilise la commande POSIX "date" pour afficher la date.

• %y : affiche l'année sur deux chiffres

• %m : affiche le mois 00-12

• %d : affiche le numéro du jour 01-31

On affiche ensuite cette variable lors de la sauvegarde du fichier :

mysqldump -u backup --single-transaction --password=< le mot de passe > $BDD > "$SHARE/$BDD"_"$DATE.sql"

B. Compter le nombre de script et supprimer les anciens scripts :

Il peut aussi être intéressant d'effectuer un roulement au niveau des sauvegardes. Si l'on souhaite par exemple avoir 4 sauvegardes d'une base de données et qu'au bout de la 5ème, la plus ancienne soit supprimée, on utilisera les lignes suivantes juste avant la sauvegarde de la nouvelle base de données :

# On compte le nombre d'archives presente dans le dossier
NbArchive=$(ls -A $CHEMIN/ |wc -l)
# Si il y a plus de 4 archives, on supprime la plus ancienne
if [ "$NbArchive" -gt 4 ];then
# On recupere l'archive la plus ancienne
  Old_backup=$(ls -lrt $CHEMIN/ |grep ".sql" | head -n 1 | cut -d ":" -f 2 | cut -d " " -f 2);
# On supprime l'archive la plus ancienne
  rm $CHEMIN/$Old_backup
fi

Explication des options:

NbArchive=$(ls -A $CHEMIN/ |wc -l)

• ls -A$CHEMIN : On liste les fichiers présents dans le dossier indiqué

• |wc -l : On y compte les fichiers qui sont à l'intérieur

On fini par mettre ce chiffre dans la variable $NbArchive

Old_backup=$(ls -lrt $CHEMIN/ |grep ".sql" | head -n 1 | cut -d " " -f 10);

• ls -lrt $CHEMIN/ : On liste par date de modification les sauvegardes présentes dans le dossier. Le script le plus ancien sera toujours affiché en premier dans la liste. Il est important de savoir que ce dossier ne doit contenir que les sauvegardes d'une seule BDD et seulement celles-ci. Sinon l'affichage sera faussé.

• grep ".sql" : on filtre les fichiers affichés pour n'afficher que ceux qui ont l'extension ".sql" .

• head -n 1 : on récupère la première ligne qui contient forcément le nom de la sauvegarde la plus ancienne

• cut -d ":" -f 2 : on coupe la ligne pour ne récupérer que le nom du fichier (on récupère la deuxième partie de la ligne quand elle est coupée par le ":"

• cut -d "" -f 2 : on coupe la ligne pour ne récupérer que le nom du fichier (on récupère la deuxième partie de la ligne quand elle est coupée par un espace

Nous aurions pu utiliser un "cut -d " " -f 10" pour récupérer le nom du fichier (on coupe la phrase selon les espaces), mais le nombre d'espaces varie selon que le numéro du jour est sur un ou deux chiffres, nous ne pouvons donc pas nous fier à cet élément. On préférera donc le ":" dans un premier temps qui est toujours fixe puis l'espace séparant les deux derniers éléments de la ligne.

On stocke ce nom dans une variable $Old_backup qui sera supprimée ensuite.

C. Création du dossier de réception s’il n'existe pas

Nous pouvons aussi faire en sorte de vérifier si le dossier dans lequel nous allons stocker les sauvegardes de notre base de données existe et le crée le cas échéant.

# Si le repertoire de la BDD dans $CHEMIN n'existe pas, on le cree
if [ ! -d "$CHEMIN" ];then
mkdir -p $CHEMIN/
fi

Le "-d" dans la condition nous permet de vérifier l'existence du dossier et le "-p" dans le mkdir nous permet de recréer toute une arborescence de dossier en une seule commande. Si le dossier n'existe pas ("!" signifiant l'inverse), alors on créer le dossier.

D. Stocker le mot de passe dans un fichier à part

Il peut être gênant d'avoir à stocker le mot de passe directement dans le script qui est exécuté. Nous allons donc chercher à le stocker dans un fichier et à aller le lire par une option supplémentaire dans les commandes. On passe par cela par un fichier déja existant qui est "/etc/mysql/debian.cnf". Nous copions donc ce fichier

cp /etc/mysql/debian.cnf /etc/mysql/user.sql

Puis nous le modifions pour y mettre nos identifiants dans les champs "user" et "password" la partie "[client]" :

Par sécurité, on réduit les droits sur ce fichier. On ne laisse que le propriétaire avoir les droits de lecture dessus :

chmod 400 user.cnf

On peut ensuite aller modifier notre script. Les deux lignes concernées sont celles où l'on fournis le mot de passe de l'utilisateur, on remplacera donc ces deux lignes :

LISTEBDD=$( echo 'show databases' | mysql -u backup --password=< mot de passe >)
mysqldump -u backup --single-transaction --password=< le mot de passe > $BDD > "$CHEMIN/$BDD"_"$DATE.sql"

Par celles-là :

LISTEBDD=$( echo 'show databases' | mysql --defaults-extra-file=/etc/mysql/user.cnf)
mysqldump --default-extra-file=/etc/mysql/user.cnf --single-transaction $BDD > "$CHEMIN/$BDD"_"$DATE.sql"

On doit bien faire attention à ce que l’option "--defaults-extra-file" soit la première de toutes les options.

E. Intégrer des options de réimplantation

Lors de la réimplantation d'une base de données qui existe toujours, mais qui est corrompue ( par exemple), nous pourrons avoir besoin d'options présentes dans le fichier .sql backupé qui permettront de faciliter cette réimplantation. Il s'agit des commandes comme "DROP DATABASE IF EXISTS" qui efface la base de données voulue avant de la réimplanter, de "CREATE DATABASE " qui la recrée et de "USE nom_bdd" qui permet de préciser sur quelle base de données vont agirent les commandes SQL qui vont suivre.

Par défaut, ces commandes ne sont pas ajoutées dans le fichier ".sql" qui est sauvegardé. Nous devons alors ajouter des options lors du passage de la commande mysqldump. La commande deviendra donc celle-là :

mysqldump --default-extra-file=/etc/mysql/user.cnf --single-transaction --add-drop-dabatase --databases $BDD > "$CHEMIN/$BDD"_"$DATE.sql"

On ajoute donc les options "--database" qui ajoute le "CREATE DATABASE" et "USE nom_bdd" et "--add-drop-databases" qui ajoute le "DROP DATABASE IF EXISTS" dans le passage de la commande "mysqldump".

Mickael Dorigny Co-founder

Co-fondateur d'IT-Connect.fr. Auditeur/Pentester chez Orange Cyberdéfense.

See Full Bio