elliot

Я таким скриптом пользуюсь.

На выгоде получаются сжатые данные — по файлу на базу.
А скопировать по ssh или ftp на локальную машину можно.

#!/bin/bash


##########################################################
# Common options
##########################################################

# Linux bin paths, change this if it can not be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"
TAR="$(which tar)"
RM="$(which rm)"


HOST="$(hostname)"
BACKUP_DIRECTORY="/var/www/_DUMP"






##########################################################
# MySQL options
##########################################################
MYSQL_USER="root" # Username
MYSQL_HOST="localhost" # Hostname
MYSQLDUMP_OPTIONS="--opt --default_character_set utf8"
MYSQL_BACKUP_DIR="$BACKUP_DIRECTORY/mysql" # Main directory where backup will be stored
MYSQL_SKIP_BACKUP_DATABASE_LIST="test information_schema mysql" # don't backup these databases


# get MYSQL_PASSWORD
read -s -p "Enter root MySQL password: " MYSQL_PASSWORD
echo "" 1>&2





#############################################################
# Backup MySQL databases
############################################################

# create backup dir
[ ! -d $MYSQL_BACKUP_DIR ] && mkdir -p $MYSQL_BACKUP_DIR || :


# Get MySQL database list
MYSQL_DATABASE_LIST="$($MYSQL -u $MYSQL_USER -h $MYSQL_HOST -p$MYSQL_PASSWORD -Bse 'show databases')"

for db in $MYSQL_DATABASE_LIST
do
skipdb=-1

    if [ "$MYSQL_SKIP_BACKUP_DATABASE_LIST" != "" ];
    then
for i in $MYSQL_SKIP_BACKUP_DATABASE_LIST
        do
            [ "$db" == "$i" ] && skipdb=1 || :
        done
fi

if [ "$skipdb" == "-1" ] ; then
FILE="$MYSQL_BACKUP_DIR/$db.$HOST.sql.gz"

     echo "Dump database: $db"

     # do all job in pipe,
     # connect to mysql using mysqldump for select mysql database
     # and pipe it out to gz file in backup dir
        $MYSQLDUMP $MYSQLDUMP_OPTIONS -u $MYSQL_USER -h $MYSQL_HOST -p$MYSQL_PASSWORD $db | $GZIP -9 > $FILE

        echo "Done."
    fi
done