Backup (logique) et restauration MySQL

skymaxs — Thu, 15 Nov 2007 09:31:25 +0000

Backup :
mysqldump -u root -pSuperMotDePasse --all-databases > backup.sql

Restauration :
mysql -u root -pSuperMotDePasse < backup.sql

[backup] [mysql] [restauration] [sauvegarde]

Backup MySQL

dave — Tue, 06 Nov 2007 13:23:45 +0000

#
#Fichier de configuration (backup-mysql.conf)
#

###########################################################
# Fichier de configuration pour le script backup-mysql.sh #
###########################################################


#Serveur à backuper
SERVER='localhost'

#Utilisateur MySQL
DBUSER='root'

#Mot de passe MySQL
DBPASS='viOvyornye'

#On specifie les bases a backuper ("all" pour tout backuper)
DB='all'

#Date format
DATE_FORMAT='+%d-%m-%Y'

#Repertoire de backup
BACKUP_DIR='/var/backup/mysql/'

#Repertoire du jour
TODAY_DIR=$(date $DATE_FORMAT)

#Nomre de backups a conserver (en jours)
HISTORY='7'

#Type de compression (bzip2/gzip)
COMP_TYPE='bzip2'

#Creer un repertoire pour chaque base de donnees (yes/no)
SEP_DB='yes'

#Separer les tables en differents fichiers (yes/no)
SEP_TABLE='yes'

#Options pour le dump
#Utiliser 'man mysqldump' ou aller sur la configuration pour plus de details
OPTS='--default-character-set=latin1 --skip-extended-insert'

#Fichier de logs
LOG_FILE='/tmp/backup.log'

#Adresse mail pour les logs d'execution du script
MAIL_ADDR='admin@domain.tld'

#
#Script de backup MySQL 
#

#!/bin/bash 

CONF_PATH='/etc'
CONF_FILE='backup-mysql.conf'

if [ ! -d "$CONF_PATH" ] || [ ! -f "$CONF_PATH/$CONF_FILE" ]; then
        echo "File : $CONF_PATH/$CONF_FILE missing !"
        exit 1;
else
        . "$CONF_PATH/$CONF_FILE"
fi

send-mail () {
        cat "$LOG_FILE" | mail -s "Rapport d'execution du script $(basename $0) sur $HOSTNAME" $MAIL_ADDR
        rm "$LOG_FILE"
}

check_dirs () {
        #On verifie si le repertoire de backup existe
        if [ ! -d "$BACKUP_DIR" ]; then
                echo "$BACKUP_DIR n'existe pas." >> "$LOG_FILE"
                send-mail
                exit 1;
        fi
        #On verifie si le repertoire du jour existe
        if [ ! -d "$BACKUP_DIR/$TODAY_DIR" ]; then
                mkdir "$BACKUP_DIR/$TODAY_DIR"
        fi
        #On supprime le backup le plus ancien
        TO_DELETE=$(date --date "$HISTORY days ago" $DATE_FORMAT)
        if [ -d "$BACKUP_DIR/$TO_DELETE" ]; then
                rm -r "$BACKUP_DIR/$TO_DELETE"
        fi
}

backup () {
        if [ -z "$1" ]; then
                echo 'Liste des bases non transmise' >> $LOG_FILE
                send-mail
                exit 1;
        else
                if [ "$SEP_DB" == 'yes' ]; then
                        for DB_TO_BACKUP in $1; do
                                #On cree le repertoire pour separer les bases
                                mkdir "$BACKUP_DIR/$TODAY_DIR/$DB_TO_BACKUP"
                                if [ "$SEP_TABLE" == 'yes' ]; then
                                        for TABLE_TO_BACKUP in $(mysql -h$SERVER -u$DBUSER -p$DBPASS --skip-column-names $DB_TO_BACKUP -e "show tables"); do
                                                mysqldump -h"$SERVER" -u"$DBUSER" -p"$DBPASS" $OPTS "$DB_TO_BACKUP" "$TABLE_TO_BACKUP" | "$COMP_TYPE" > "$BACKUP_DIR/$TODAY_DIR/$DB_TO_BACKUP/$DB_TO_BACKUP-$TABLE_TO_BACKUP-$TODAY_DIR.$FILE_EXT"
                                        done
                                else 
                                        mysqldump -h"$SERVER" -u"$DBUSER" -p"$DBPASS" $OPTS "$DB_TO_BACKUP" "$TABLE_TO_BACKUP" | "$COMP_TYPE" > "$BACKUP_DIR/$TODAY_DIR/$DB_TO_BACKUP/$DB_TO_BACKUP-$TODAY_DIR.$FILE_EXT"
                                fi
                        done
                                
                else
                        if [ "$SEP_TABLE" == 'yes' ]; then
                                for DB_TO_BACKUP in $1; do
                                        for TABLE_TO_BACKUP in $(mysql -h$SERVER -u$DBUSER -p$DBPASS --skip-column-names $DB_TO_BACKUP -e "show tables"); do
                                        mysqldump -h"$SERVER" -u"$DBUSER" -p"$DBPASS" $OPTS "$DB_TO_BACKUP" "$TABLE_TO_BACKUP" | "$COMP_TYPE" > "$BACKUP_DIR/$TODAY_DIR/$DB_TO_BACKUP-$TABLE_TO_BACKUP-$TODAY_DIR.$FILE_EXT"
                                        done
                                done
                        else
                                mysqldump -h"$SERVER" -u"$DBUSER" -p"$DBPASS" $OPTS --all-databases | "$COMP_TYPE" > "$BACKUP_DIR/$TODAY_DIR/$TODAY_DIR.$FILE_EXT"
                        fi
                fi
        fi
}

# On cree le fichier de log
touch "$LOG_FILE"
# on verifie que les bases a backuper sont bien renseignees
if [ -z "$DB" ]; then
        echo 'variable $DB vide.' >> "$LOG_FILE"
        send-mail
        exit 1;
elif [ "$DB" == "all" ]; then
        DB_LIST=$(mysql -h$SERVER -u$DBUSER -p$DBPASS --skip-column-names -e "show databases" | xargs)
else
        DB_LIST="$DB"
fi      
# On verifie que la compression est correcte
if [ "$COMP_TYPE" == 'bzip2' ]; then
        FILE_EXT='sql.bz2'
elif [ "$COMP_TYPE" == 'gzip' ]; then
        FILE_EXT='sql.gz'
else
        echo '$COMP_TYPE non spécifié.' >> "$LOG_FILE"
        send-mail
        exit 1;
fi

check_dirs
backup "$DB_LIST"

#On verifie si le fichier de logs existe et qu'il est vide
if [ -f "$LOG_FILE" ] && [ "$(cat $LOG_FILE | wc -l)" -eq 0 ]; then
        #Si il est vide on efface
        rm "$LOG_FILE"
else
        #Sinon on envoie le rapport
        send-mail
fi

[backup] [mysql]

mysql_backup.sh

xavier — Sat, 03 Nov 2007 13:44:28 +0000

#!/bin/bash

prefixe=backup
suffixe=$(date +%Y%m%d)
filename=$prefixe$suffixe.sql
host=host
database=database
user=user
characterSet=UTF8

mysqldump --skip-comments \
--complete-insert \
--default-character-set=$characterSet \
--no-create-info \
--skip-add-locks \
--skip-opt \
--host $host \
--user $user \
-p $database > $filename

sed -i "1iuse $database;\nSET CHARACTER SET '$characterSet';" $filename
exit 0

[backup] [bash] [data] [données] [mysql] [sauvegarde]

Posteet: backup,mysql

Backup (logique) et restauration MySQL

Backup MySQL

mysql_backup.sh