Tags: mysql,backup

Sort by: Date / Title /

  1. 9 years ago by benoitbalon
    1. mysqldump -h localhost -u user -ppassword -r/tmp/mysql_backup.data nomdatabase
    2.  
    3. mysql -h localhost -u user -ppassword nomdatabase < /tmp/mysql_backup.data
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1761"></script>
  2. 11 years ago by skymaxs and saved by 1 other
    Backup :
    mysqldump -u root -pSuperMotDePasse --all-databases > backup.sql
    
    Restauration :
    mysql -u root -pSuperMotDePasse < backup.sql
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/436"></script>
  3. 11 years ago by dave and saved by 3 others
    #
    #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
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/317"></script>
  4. sponsorised links
  5. 11 years ago by xavier
    1. #!/bin/bash
    2.  
    3. prefixe=backup
    4. suffixe=$(date +%Y%m%d)
    5. filename=$prefixe$suffixe.sql
    6. host=host
    7. database=database
    8. user=user
    9. characterSet=UTF8
    10.  
    11. mysqldump --skip-comments \
    12. --complete-insert \
    13. --default-character-set=$characterSet \
    14. --no-create-info \
    15. --skip-add-locks \
    16. --skip-opt \
    17. --host $host \
    18. --user $user \
    19. -p $database > $filename
    20.  
    21. sed -i "1iuse $database;\nSET CHARACTER SET '$characterSet';" $filename
    22. exit 0
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/125"></script>

First / Previous / Next / Last / Page 1 of 1 (4 posteets)