Sauvegardes scriptées de la base de données MySQL

Aperçu

Cet article décrit un script de sauvegarde de base de données MySQL qui vous permet d'implémenter une sauvegarde quotidienne (ou plus fréquente) automatisée, horodatée et compressée de vos bases de données MySQL. L'avantage d'utiliser ce script est qu'il sauvegardera toutes les bases de données, les stockera dans un répertoire (/data/db-backups) et enregistrera son activité dans un fichier dans (/logs/db-backup) pour audit et examen au cas où de tout problème.

Idéalement, le script doit être automatisé à l'aide de CRON afin qu'au moins tous les jours une sauvegarde complète de chaque base de données se produise. Cela vous permettra d'avoir un certain degré de récupération en cas d'erreurs dans votre base de données ou de faciliter la migration de votre ou vos bases de données vers un autre système. Comme tous les processus automatisés, une fois installé, il doit continuer indéfiniment à effectuer des sauvegardes. Vous devrez donc vous assurer que les anciennes sauvegardes sont supprimées afin que vos disques ne se remplissent pas.

Le scénario

Le script est un simple script shell BASH propre testé sur la distribution CentOS Linux mais capable de s'exécuter sur d'autres variantes de Linux avec peu ou pas de modification.

1. Coupez et collez le texte suivant dans un éditeur et apportez les modifications nécessaires pour améliorer ou réduire la fonctionnalité.

#!/bin/bash
# mysql-backup.sh
# use mysqldump to Dump DB and compress it on the fly to a mounted partition
#
BACKUP_DIR="/data/db-backups"
mkdir -p $BACKUP_DIR
chmod 777 $BACKUP_DIR
#
#
SERIAL="`date +%Y%m%d-%H%M%S`"

#=====================================
# Log Functions
#
function LogStart
{
echo "====== Log Start =========" >> $LF
echo "Time: `date`" >> $LF
echo " " >> $LF
}
function LogEnd
{
echo " " >> $LF
echo "Time: `date`" >> $LF
echo "====== Log End   =========" >> $LF
}

#=====================================
#
#
function GetDBList
{
echo "Calling GetDBList()" >> $LF
mysqlshow |grep "|"| tr -d ' '|tr -d '|'| egrep -v Databases > $DBLIST
}

#=====================================
#
#
function DoBackup
{
echo "Calling DoBackup()" >> $LF

DBFILE=$BACKUP_DIR/db-$DB-$SERIAL.sql
echo "Host [$H]" >> $LF
echo "DB File [$DBFILE]" >> $LF
if [ -a  $DBFILE ]
then
mv $DBFILE $DBFILE.`date '+%M%S'`
fi
echo "Dumping ${DB}" >> $LF
mysqldump -B ${DB}  --add-drop-database --add-drop-table >> ${DBFILE}
echo "Zipping up file!" >> $LF
gzip ${DBFILE}
echo "Done!" >> $LF
}

FILE_DATE=`date '+%Y-%m-%d'`
LF_DIR=/logs/db-backup
LF=$LF_DIR/db-backup-$FILE_DATE.log
mkdir -p $LF_DIR
chmod 777 $LF_DIR
touch $LF
chmod 664 $LF

DBLIST=/tmp/dblist-$FILE_DATE.list

LogStart
#=====================================
#
#                     MAIN Code Start

GetDBList
while read line
do
echo "Backuping up: $line"
H="localhost"
DB=$line
DoBackup
done < $DBLIST
echo "All backups Completed" >> $LF
LogEnd
#
# EOF

Tester la sauvegarde

1. Pour vérifier que le script de sauvegarde fonctionne, exécutez d'abord la commande de sauvegarde comme suit :

   /usr/local/bin/mysql-backups.sh

2. Ensuite, effectuez une liste de répertoires de /data/db-backup répertoire créé par le script, vous devriez voir quelques fichiers, chacun représentant votre base de données compressée et sauvegardée !

   [root@yourserver]#ls -l /data/db-backups
   
   -rw-r--r-- 1 root root    510 Mar  5 15:21 db-information_schema-20140305-152103.sql.gz
   -rw-r--r-- 1 root root    526 Mar  5 15:22 db-information_schema-20140305-152229.sql.gz
   -rw-r--r-- 1 root root 142122 Mar  5 15:21 db-mysql-20140305-152103.sql.gz
   -rw-r--r-- 1 root root 142283 Mar  5 15:22 db-mysql-20140305-152229.sql.gz
   ....
   [root@yourserver]#

Automatiser le script

Le script doit être placé dans un répertoire appelé /usr/local/bin . Ce répertoire est spécifiquement réservé aux scripts applicables au système écrits par l'utilisateur.

Le script doit être rendu exécutable par l'utilisateur root, utilisez la commande suivante pour marquer le fichier comme exécutable :

"chmod 744 /usr/local/bin/mysql-backup.sh"

Pour automatiser l'exécution du script nous allons utiliser l'application CRON, nous allons donc ajouter une ligne à notre crontab en utilisant la commande "crontab -e" , cela fera apparaître un éditeur et nous ajouterons une ligne comme suit :

30 22 * * * /usr/local/bin/mysql-backups.sh > /dev/null 2>&1

Fondamentalement, cela permettra au script de s'exécuter à 22h30 tous les jours. Il masquera également toute sortie du programme lorsque le programme se connecte au disque.

Récupération

Pour récupérer une sauvegarde, utilisez gunzip pour décompresser le fichier puis utilisez mysql

Voici un exemple typique de ce que vous devez exécuter :

cd /data/db-backups
gunzip db-mybigdatabase-20140305-152229.sql.gz
mysql < db-mybigdatabase-20140305-152229.sql

Avertissement :Faites très attention à ce que vous faites car vous écraserez votre base de données si vous faites une erreur.

Où aller d'ici ?

Si vous sauvegardez votre serveur, envisagez de copier automatiquement les fichiers sur un autre serveur et de les récupérer automatiquement, cela vous permet d'avoir une copie de production de bases de données sélectionnées à des fins de test et d'audit en interne.