Avec MySQL comme avec les autres SGBDr, la façon la plus portable de sauvegarder ses données est d'en extraire des fichiers sql susceptibles de servir à reconstruire la base et y replacer ses données ce qui s'appelle en anglais un « dump ».

MySQL propose un script du nom de mysqldump qui fait ça très bien. Il produit des fichiers texte qui contiennent des instructions sql. Ces fichiers se compressent très bien, et comme les bases sont souvent grandes, les utilitaires comme gzip sont très utiles pour gagner de l'espace disque.

Le problème commence lorsqu'on envisage des sauvegardes incrémentales. Avec les utilitaires comme rsnapshot qui utilise rsync et les liens en dur, seuls les fichiers modifiés depuis la dernière sauvegarde sont sauvegardés. On économise ainsi de l'espace disque et de la bande passante. Mais les fichiers de dump sont à chaque fois nouveaux, car même si leur contenu n'a pas changé, le fichier lui est nouveau. Du coup rsync le télécharge. Et si vous faites un dump des bases de votres serveur MySQL toutes les quatre heures vous allez télécharger six fois par jour la même chose si vos bases reçoivent rarement des modifications.

Il faut donc trouver un moyen de tester si le nouveau dump est différent de l'ancien et si ce n'est pas le cas, conserver l'ancien. Ainsi rsync ne le retéléchargera pas inutilement. Il faut donc au cours du processus de création des dumps pouvoir tester si le contenu du nouveau fichier de dump est semblable ou différent de l'ancien.

A priori cela semble simple. Mais plusieurs éléments viennent perturber l'affaire. Le premier c'est que mysqldump par défaut rajoute en fin de fichier la date et l'heure de création. Donc évidemment deux dumps de la même base réalisés à quelques minutes d'intervalle vont donner des fichiers différents puisque contenant des horodatages différents (ce que l'on peut constater avec cmp, diff ou en calculant une somme md5 avec md5sum). Le second est que la compression avec gzip par défaut entre elle aussi un horodatage dans le fichier comprimé. Ce qui fait que si vous réaliser deux archives du même fichier avec gzip, elles vont avoir un octet de différence (justement l'horodatage, ce que l'on peut aussi voir avec cmp, diff ou md5sum).

Comme toujours la lecture de la page de man permet de trouver les solutions. Pour le premier problème man mysqldump dit :

Option --skip-dump-date

However, the date causes dump files taken at different times to appear to be different, even if the data are otherwise identical. --dump-date and --skip-dump-date control whether the date is added to the comment. The default is --dump-date (include the date in the comment). --skip-dump-date suppresses date printing. This option was added in MySQL 5.1.23.

Donc l'option --skip-dump-date est la solution à notre problème, qui supprime l'impression de la date qui semble rendre différents deux fichiers dont les données sql sont identiques. Si vous avez MySQL dans une version supérieure ou égale à MySQL 5.1.23 vous pouvez l'utiliser. C'est le cas sur Debian Wheezy.

Pour le second problème, la page man gzip donne elle aussi la solution :

Option -n --no-name

When compressing, do not save the original file name and time stamp by default. (The original name is always saved if the name had to be truncated.) When decompressing, do not restore the original file name if present (remove only the gzip suffix from the compressed file name) and do not restore the original time stamp if present (copy it from the compressed file). This option is the default when decompressing.

L'option -n évite que le timestamp ne soit stocké dans l'archive, rendant ainsi toutes ses versions identiques. On arrive donc à un script de sauvegarde au cours duquel on pourra tester par exemple avec cmp les deux fichiers new-dump.gz et dump.gz

cmp new-dump.gz dump.gz &> /dev/null

qui renverra 0 si les fichiers sont identiques ou 1 s'il présentent une différence. L'envoi du résultat vers /dev/null est rendu nécessaire par le fait que cmp affiche un message d'information si les fichiers sont différents et que dans un script ce message n'est d'aucune utilité. Un script de sauvegarde possible utilisant ces options serait donc le suivant (ce script a pour point de départ le script de sauvegarde que j'avais présenté dans un précédent article) :

#!/bin/bash -
# 
###############################################################################
# NOM : horodate_dump
VERSION='0.0.5'
#
###~~~~~~~~~~~~~~~~~~~~~~~~~~~ UTILISATION ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#
# Script de sauvegarde des bases d'un serveur MySQL. 
# Les sauvegardes se font en archives gzipées,
# une par base avec un nom de style mysql_nombase.sql.bz.
#
# Avec l'option -s (surcharge) les sauvegardes sont dans un répertoire publique, 
# de manière à pouvoir les sauvegarder avec rsnapshot. Si les bases n'ont pas changé 
# depuis le dernier lancement du script, les fichiers sont laissés tels que. Si ils 
# ont changé, le nouveau fichier remplace l'ancien. Ainsi rsnapshot le sauvegardera. 
# 
# Avec l'option -r (répertoire) le script va créer un répertoire par sauvegarde, 
# le nom de ce répertoire étant celui de l'horodatage de la sauvegarde. 
# Le nom des sauvegardes est également préfixé de l'horodatage. 
# Attention à la saturation du disque, car les fichiers sont créés que les bases aient 
# changé ou pas.
# Ce comportement est appelé à être modifié dans une prochaine version
#
###~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#
# Author: Marc GUILLAUME - marc@yakati.net
# This script is under GNU/GPL v3
#
#   This program is free software: you can redistribute it and/or modify
#   it under the terms of the GNU General Public License as published by
#   the Free Software Foundation, either version 3 of the License, or
#   (at your option) any later version.
#
#   This program is distributed in the hope that it will be useful,
#   but WITHOUT ANY WARRANTY; without even the implied warranty of
#   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#   GNU General Public License for more details.
#
#   You should have received a copy of the GNU General Public License
#   along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
#   Création : 2014-08-14                   Dernière modification : 2018-08-26
###############################################################################


#############################################
# DÉCLARATION ET INITIALISATION DES VARIABLES
#
# Vous devez remplacer ces données par celles
# correspondant à votre configuration

#~~~~ On indique dans quel répertoire vont se trouver les sauvegardes
BACKUP_DIR="/opt/backupsMySQL/"


########################
# FONCTIONS SPÉCIFIQUES
#

#~~~~ fonction pour le log
function log() {
   echo $1 | logger -p local0.notice -t MYSQLBKP;
}

#~~~~ affichage du message d'aide
Usage(){
  echo ""
  echo "$0 version ${VERSION} Sauvegarde des bases d'un serveur MySQL"
  echo "+"
  echo "++ Usage ; "$0" sans argument affiche la présente aide comme avec l'option h." 
  echo "+"
  echo "++ [-s] option surcharge. Si un ancien fichier de dump existe, il le compare avec le nouveau."
  echo "   Si les fichiers sont semblables il garde l'ancien, sinon il remplace "
  echo "   l'ancien par le nouveau."
  echo "+"
  echo "+ + [-r] Cette option crée un sous-répertoire nommé par un horodatage, "
  echo "    chaque fichier de base étant lui aussi préfixé de cet horodatge"
  echo "+ + [-h] affiche la présente aide"
  echo "+"
}


############################
# TRAITEMENT DES PARAMÈTRES
#
# Si aucun paramètre passé
if [ $# -lt 1 ]; then Usage; exit 1; fi

# Cas particulier de l'option -h: doit être seule et des paramètres vides
if [ "$1" = -h ]; then Usage; exit 1; fi

# Analyse et traitement des autres options. Si une option n'existe pas on le signale
FLAG_SURCHARGE=0
FLAG_REPERTOIRE=0

while getopts "sr" OPTION
do
    #echo "(-- option:$OPTION  $OPTIND - '$OPTARG' --)"
    case ${OPTION} in
        s) FLAG_SURCHARGE=1
        ;;
        r) FLAG_REPERTOIRE=1
        ;;
        \?)     echo " Option [-${OPTION}]  INVALIDE" >&2
                $(log "L'option [-${OPTARG}] est invalide");
                exit 2
        ;;
    esac
done

#####################################################
# DEBUT DU TRAITEMENT
#
if [ ${FLAG_REPERTOIRE} -eq 1 ]; then
  # On crée un timestamp pour l'horodatage des répertoires et fichiers (année-jour-mois-heure-minute)
  # ce qui permet d'envisager des sauvegardes horaires si besoin est.
  TIMESTAMP=$(date +"%Y%m%d%H%M")
  BACKUPPATH="${BACKUP_DIR}${TIMESTAMP}/"
  # On crée le répertoire de la sauvegarde portant comme nom l'horodatage
  $(mkdir -p ${BACKUPPATH})
fi

if [ ${FLAG_SURCHARGE} -eq 1 ]; then
  BACKUPPATH=${BACKUP_DIR}"unique/"
  if [ ! -d ${BACKUPPATH} ]; then
    $(mkdir -p ${BACKUPPATH})
  fi
  TIMESTAMP=''
fi

umask 007

# On donne une priorité moyenne au processus
renice 10 $$ > /dev/null

###~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# On lance le dump en parcourant la liste des bases donnée par la commande SHOW DATABASES
# Depuis Debian Etch l'utilisateur root du système a un accès au root de MySQL/MariaDB.
# Les sauvegardes sont compressées. Pour pouvoir les comparer aux anciennes on utilise l'option --skip-dump-date
# pour ne pas avoir d'horodatage interne au fichier de dump (voir man mysqldump) et l'option -n de gzip
# qui elle aussi supprime l'horodatage. Ainsi on peut faire un simple cmp sur les fichiers binaires.
###~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

for db in $(mysql --batch --skip-column-names --execute="SHOW DATABASES" | grep -v _schema)
do
    
    if $(/usr/bin/nice /usr/bin/mysqldump --events --ignore-table=mysql.event --single-transaction --skip-dump-date --quick --extended-insert ${db} | gzip -n > ${BACKUPPATH}${TIMESTAMP}new-mysql_${db}.sql.gz); then
        if [ ${FLAG_SURCHARGE} -eq 1 ]; then
            if [ -f ${BACKUPPATH}${TIMESTAMP}mysql_${db}.sql.gz ]; then
                # si il existe une sauvegarde on regarde si le fichier a changé
                if $(cmp ${BACKUPPATH}${TIMESTAMP}new-mysql_${db}.sql.gz ${BACKUPPATH}${TIMESTAMP}mysql_${db}.sql.gz) &> /dev/null ; then
                    # Ils sont les mêmes on efface donc le nouveau
                    $(rm -f ${BACKUPPATH}${TIMESTAMP}new-mysql_${db}.sql.gz);
                    $(log "Le fichier "${TIMESTAMP}mysql_${db}.sql.gz" n'a pas changé.");
                else
                    # il a changé, on remplace l'ancien fichier
                    $(mv -f ${BACKUPPATH}${TIMESTAMP}new-mysql_${db}.sql.gz ${BACKUPPATH}${TIMESTAMP}mysql_${db}.sql.gz);
                    $(log "Le fichier "${TIMESTAMP}mysql_${db}.sql.gz" est dans une nouvelle version.");
                fi
            else
                # si le fichier d'un précédent backup n'existe pas on renomme le nouveau fichier pour le créer
                $(mv -f ${BACKUPPATH}/${TIMESTAMP}new-mysql_${db}.sql.gz ${BACKUPPATH}/${TIMESTAMP}mysql_${db}.sql.gz);
                $(log "Le fichier "${TIMESTAMP}mysql_${db}.sql.gz" n'existait pas et a été créé.");
            fi
        fi
        if [ ${FLAG_REPERTOIRE} -eq 1 ]; then
            $(mv ${BACKUPPATH}${TIMESTAMP}new-mysql_${db}.sql.gz ${BACKUPPATH}${TIMESTAMP}-mysql_${db}.sql.gz);
            $(log "Création du fichier "${BACKUPPATH}${TIMESTAMP}-mysql_${db}.sql.gz".");
        fi
    else
            $(log "Problème avec la sauvegarde de "${BACKUPPATH}${TIMESTAMP}mysql_${db}.sql.gz".");
            exit 2;
    fi
done

# Puis on redonne la sauvegarde à l'utilisateur et au groupe mysql:mysql pour que
# dans une sauvegarde rsync on puisse plus facilement restaurer les bases.
$(chown -R mysql\: ${BACKUPPATH}) 

exit 0