使用rsnapshot和mysqldump备份远程mySQL数据库

Question

我正在使用rsnapshot来备份我们的远程服务器，并且我需要添加MySQL备份。

rsnapshot调用一个远程脚本，该脚本转储服务器上的mysql DB。问题是，脚本没有排除information_schema数据库，因此它死了)，或者看起来死了，我有第二个问题。)

我不确定如何从该脚本中排除information_schema DB：

### SETUP MYSQL LOGIN ###
MUSER='USER'
MPASS='PASSWORD'
MHOST="127.0.0.1"

### Set to 1 if you need to see progress while dumping dbs ###
VERBOSE=0

### Set bins path ###
GZIP=/bin/gzip
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump
RM=/bin/rm
MKDIR=/bin/mkdir
MYSQLADMIN=/usr/bin/mysqladmin
GREP=/bin/grep

### Setup dump directory ###
BAKRSNROOT=/tmp/rsnapshot/mysql

#####################################
### ----[ No Editing below ]------###
#####################################
### Default time format ###
TIME_FORMAT='%H_%M_%S%P'

### Make a backup ###
backup_mysql_rsnapshot(){
        local DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
        local db="";
        [ ! -d $BAKRSNROOT ] && ${MKDIR} -p $BAKRSNROOT
        ${RM} -f $BAKRSNROOT/* >/dev/null 2>&1
        [ $VERBOSE -eq 1 ] && echo "*** Dumping MySQL Database ***"
        [ $VERBOSE -eq 1 ] && echo -n "Database> "
        for db in $DBS
        do
                local tTime=$(date +"${TIME_FORMAT}")
                local FILE="${BAKRSNROOT}/${db}.${tTime}.gz"
                [ $VERBOSE -eq 1 ] && echo -n "$db.."
                ${MYSQLDUMP} -u ${MUSER} -h ${MHOST} -p${MPASS} $db | ${GZIP} -9 > $FILE
        done
                [ $VERBOSE -eq 1 ] && echo ""
                [ $VERBOSE -eq 1 ] && echo "*** Backup done [ files wrote to $BAKRSNROOT] ***"
}

### Die on demand with message ###
die(){
        echo "$@"
        exit 999
}

### Make sure bins exists.. else die
verify_bins(){
        [ ! -x $GZIP ] && die "File $GZIP does not exists. Make sure correct path is set in $0."
        [ ! -x $MYSQL ] && die "File $MYSQL does not exists. Make sure correct path is set in $0."
        [ ! -x $MYSQLDUMP ] && die "File $MYSQLDUMP does not exists. Make sure correct path is set in $0."
        [ ! -x $RM ] && die "File $RM does not exists. Make sure correct path is set in $0."
        [ ! -x $MKDIR ] && die "File $MKDIR does not exists. Make sure correct path is set in $0."
        [ ! -x $MYSQLADMIN ] && die "File $MYSQLADMIN does not exists. Make sure correct path is set in $0."
        [ ! -x $GREP ] && die "File $GREP does not exists. Make sure correct path is set in $0."
}

### Make sure we can connect to server ... else die
verify_mysql_connection(){
        $MYSQLADMIN  -u $MUSER -h $MHOST -p$MPASS ping | $GREP 'alive'>/dev/null
        [ $? -eq 0 ] || die "Error: Cannot connect to MySQL Server. Make sure username and password are set correctly in $0"
}

### main ####
verify_bins
verify_mysql_connection
backup_mysql_rsnapshot

Answer 1

查看mysqldump及其--all-databases参数。不要重复发明轮子！

mysqldump不转储theINFORMATION_SCHEMA数据库。如果在命令行上显式命名数据库，mysqldump会忽略它。

如果您的mysqldump版本确实不排除INFORMATION_SCHEMA，并且您需要单独文件中的所有DB，那么只需将其排除在您的循环中：

for db in $DBS 
do 
    if [ $db -ne "INFORMATION_SCHEMA" ] ; do
        YOUR_STUFF()
    done
dome

Answer 2

替换以下行：

local DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"

使用

local DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases' -s --skip-column-names|grep -vi information_schema)"