Sauvegardes, restaurations et transferts

From Deimos.fr / Bloc Notes Informatique
Jump to: navigation, search
MySQL Logo

1 Sauvegardes

1.1 mysqldump

Ceci est à mon sens LA meilleure méthode. Elle est fournit en standart et fonctionne très bien. Cependant attention aux grosses bases de données. Si vous avez de grosses bases, alors regardez du côté d'XtraBackup.

Son usage avec un shell est comme suit (pour des bases de petite taille) :

Command 
mysqldump -uUTILISATEUR -pMOTDEPASSE -e --single-transaction --opt BASEDEDONNEES > backup-`date +%y%m%d`.sql

Pour sauvegarder toutes les bases d'un seul coup :

Command 
mysqldump -uUTILISATEUR -pMOTDEPASSE --all-databases > backup-`date +%y%m%d`.sql

Il existe aussi d'autres options :

  • --no-data : permet de générer un fichir qui contient toutes les instructions de création des tables de votre base (CREATE DATABASE, CREATE TABLE...), mais pas les données elles-mêmes.
  • --no-createdb et --no-create-info permettent de commenter automatiquement les instructions de création de schéma. Donc d'insérer des données depuis une sauvegarde dans une base avec des tables existantes.
  • --lock-tables : permet de poser un LOCK sur toute une base. On obtient alors une cohérence entre les tables, mais pas entre les bases.
  • --lock-all-tables : permet de poser un LOCK sur toutes les tables. Soyez conscient des impacts d'un tel LOCK sur une grosse base de données non transactionnel comme MyISAM. La mise en attente de toutes les requêtes pendant la durée de la sauvegarde pourraient faire croire au visiteurs que le serveur du site a planté. A la fin du dump, toutes les requêtes en attente seront exécutées (pas de pertes, sauf si la longueur de la queue a été dépassée).
  • -e : permet de cumuler les création d'éléments de même type (va donc plus vite à la réimportation
  • --single-transaction : permet de lancer les sauvegardes dans une transaction. Elle garantit donc que les tables InnoDB (moteur transactionnel) seront dans un état cohérent entre elles. Les tables qui ne supportent pas les transactions seront également sauvegardées, mais la cohérence ne sera pas garantie (l'instruction BEGIN est simplement ignorée pour ces tables). L'avantage de cette option est la non utilisation d'un LOCK. En faite, elles seront lockées à l'intérieur de la transaction, sans empêcher les autres clients de faire des modifications de la base.
  • --master-data : permet d'avoir les position du master pour la réplication directement dans le dump.
  • --opt : ajoute les drop-tables si elles existent.

1.1.1 Sauvegarder rapidement chaque base de données

Pour sauvegarder simplement et rapidement chaque base de donnée, voici une solution :

Command 
for I in `echo "show databases;" | mysql -uroot -p | grep -v Database`; do mysqldump -uroot -p -e --single-transaction --opt $I > "$I.sql"; done

A vous de mettre le mot de passe en dur (créez un user backup par exemple avec les droits de backup uniquement pour améliorer la sécu).

1.2 mysqlhotcopy

Cette commande est un script PERL livré avec MySQL qui effectue en gros une copie brut des fichiers. Cependant cette méthode est assez performante.

Cette commande permet de faire :

  • cp : si c'est pour une sauvegarde en locale
  • scp : si c'est pour du distant

Il pose lui même des fichiers de lock sur les tables à sauvegarder. Il existe aussi l'option --record_log_pos qui permet d'enregistrer dans une table la position dans les journaux binaires lorsque le serveur est en mode maitre ou esclave (pour permettre de créer rapidement un nouvel esclave). Quelques petits exemples pour une sauvegarde en local :

Command mysqlhotcopy 
mysqlhotcopy user password /var/lib/mysql/ma_base

Et pour une sauvegarde distante :

Command mysqlhotcopy 
mysqlhotcopy --user=user --password=pass user user@host:/home/mon_backup

Un petit inconvénient, mais qui a son importance : mysqlhostcopy ne fonctionne qu'avec MyISAM et ARCHIVE. Il existe un outil payant (HOT Backup) qui permet de faire la même chose mais avec InnoDB en plus.

1.3 Snapshot LVM

Je n'aborderais pas ici comment on utilise LVM, mais c'est la méthode à utiliser pour des grosses bases (16 Go par exemple).

Tout d'abord, il faut poser un lock (et oui, on est quand même obligé, mais cela ne dure que quelques centièmes de secondes) :

Command mysql 
flush tables with read lock;

Ensuite on fait notre snapshot :

Command lvcreate 
lvcreate --snapshot -n snap -L 16G /dev/volumegroupe/snapshot_mysql

L'opération étant instantanée, nous pouvons enlever le lock :

Command mysql 
mysql> unlock tables;

2 Restaurations

La restauration se fait en utilisant directement le programme principal :

Command 
mysql -u<user> -p<password> <database_name> < backup-`date +%y%m%d`.sql

Si vous souhaitez ne restaurer qu'une seule base depuis un dump ou il y a toutes les bases :

Command 
mysql -u<user> -p<password> --one-database <database_name> < backup-`date +%y%m%d`.sql

3 Transferts

Enfin, si l'objectif est de transférer la base d'une machine à l'autre, on peut combiner les deux appels sur une seule ligne :

Command 
mysqldump -uUTILISATEUR -pMOTDEPASSE BASEDEDONNEES | ssh utilisateur:motdepasse@IP "cat > myfile.sql"

4 Scripts

4.1 Backup par mail

Voici un petit script a adapter selon vos besoins :

Configuration File mysql_backup.sh 
 #!/bin/sh
 ## MySQL Backup Script
 ## Made by Pierre Mavro
 
 ## INFOS MYSQL ##
 LOGIN=root # Login for mysql
 PASS=toto # Pass for mysql
 
 ## Email ##
 ADMINMAIL=user@fqdn.com # This is the mail were the saves will be send
 
 ## Vars ##
 TMPDIR=/tmp/baksql # Temp directory
 
 ## DO NOT MODIFY NOW ##
 mkdir $TMPDIR
 # Backuping databases
 for databases in "cacti" "information_schema" "mysql" "snort" "wikidb" ; do
        mysqldump -u$LOGIN -p$PASS $databases > $TMPDIR/$databases-`date +%y%m%d`.sql && baksql=$baksql`echo "Sauvegarde de la base $databases - OK ; "` || baksql=$baksql`echo "Sauvegarde de la base $databases - FAILED ; "`
 done
 
 # Compressing and emailing
 tar -czf $TMPDIR/mysql_backup.tgz $TMPDIR/*.sql && echo "`echo $baksql`" | mutt -x -a $TMPDIR/mysql_backup.tgz -s "MySQL backup - `date +%d\-%m\-%Y` - fire" $ADMINMAIL
 rm -Rf $TMPDIR

4.2 Sauvegarde et compression de toutes les bases

Cette méthode plus fastidieuse à pour avantage la sauvegarde base par base qui permet de restaurer que la base qui vous intéresse en cas de problème.

De plus, elle comprends de la compression à la volée de votre base. Il faudra cependant installer 7zip au préalable (j'ai fais le choix de 7zip pour avoir une meilleure compression).

Nous allons créer un script que nous allons placer dans /etc/scripts par exemple :

Configuration File /etc/scripts/backup_mysql_databases.sh 
#!/bin/bash
user='root'
password='password'
destination='/tmp/backups_sql'
mail='my@mail.com'
 
mkdir -p $destination
for i in `echo "show databases;" | mysql -u$user -p$password | grep -v Database`; do
       mysqldump -u$user -p$password --opt --add-drop-table --routines --triggers --events --single-transaction --master-data=2 -B $i | 7z a -t7z -mx=9 -si $destination/$i.sql.7z
done
 
problem_text=''
problem=0
for i in `ls $destination/*` ; do
    size=`du -sk $i | awk '{ print $1 }'`
    if [ $size -le 4 ] ; then
        problem_text="$problem_text- $i database. Backupped database size is equal or under 4k ($size)\n"
        problem=1
    fi  
done
 
if [ $problem -ne 0 ] ; then
    echo -e "Backups problem detected on :\n\n$problem_text" | mail -s "$HOSTNAME - MySQL backup problem" $mail
fi

Le problème ici est le mot de passe en clair. Vérifiez donc à bien restreindre à l'utilisateur qui va backuper :

Command 
chmod 700 /etc/scripts/backup_mysql_databases.sh

4.3 Transferts d'une base mysql vers une autre via SSH

Configuration File transfert.sh 
#!/bin/sh
## MySQL Backup Script
## Made by Pierre Mavro
 
## INFOS MYSQL ##
LOGIN=root # Login for mysql
PASS=toto # Pass for mysql
 
## SSH SERVER FOR TRANSFERT & REINJECTION ##
MYSQLSRV2="192.168.0.1"
 
## Email ##
ADMINMAIL=user@fqdn.com # This is the mail were the transferts failures will be notified
 
## Vars ##
TMPDIR=/tmp/baksql # Temp directory
 
## DO NOT MODIFY NOW ##
mkdir $TMPDIR
# Backuping databases
for databases in "bugzilla" "cacti" "mysql" "networkdb" "wiki" ; do
       mysqldump -u$LOGIN -p$PASS $databases > $TMPDIR/$databases-`date +%y%m%d`.sql && baksql=$baksql`echo "Sauvegarde de la base $databases - OK ; "` || baksql=$baksql`echo "Sauvegarde de la base $databases - FAILED ; "`
done
 
# Transfering databases to the other SQL server (need ssh key)
tar -czf $TMPDIR/mysql_backup.tgz $TMPDIR/*.sql
scp $TMPDIR/mysql_backup.tgz $MYSQLSRV2:~/
ssh $MYSQLSRV2 tar -xzvf ~/mysql_backup.tgz
for databases in "bugzilla" "cacti" "mysql" "networkdb" "wiki" ; do
        ssh $MYSQLSRV2 "mysql -u$LOGIN -p$PASS $databases < ~/tmp/baksql/$databases-`date +%y%m%d`.sql" && baksql=$baksql`echo "Reinjection de la base $databases - OK ; "` || baksql=$baksql`echo "Reinjection de la base $databases - FAILED ; "`
done
 
# Delete temps
ssh $MYSQLSRV2 rm -Rf /root/tmp/baksql ~/mysql_backup.tgz ~/tmp
rm -Rf $TMPDIR
 
# Send mail
echo $baksql | mail -s "Mysql transferts" $ADMINMAIL

5 Ressources

How To Back Up MySQL Databases With mylvmbackup

Retrieved from "https://wiki.deimos.fr/index.php?title=Sauvegardes,_restaurations_et_transferts&oldid=13013"