[관리] Mysql 자동 백업 스크립트

#!/bin/sh

#

#

## Chilbong, Kim <san2 at linuxchannel.net>

## 2001.03.01(1)

## 2001.03.03(2)

##

## http://www.linuxchannel.net

##

##

## This SHELL script : $EACH DB dump to $EACH.sql.

##

###################################################

##

## This file name : “mysqldump.cron”

## cp mysqldump.cron /etc/cron.daily/

## chmod 700 /etc/cron.daily/mysqldump.cron

##

###################################################

## user setting

##

##

mysql_host=”localhost”

mysql_rootpasswd=”xxxxxxx”

backupdir=”/var/backup”

mysql_backupdir=”${backupdir}/mysqldump”

mysql_dump=”/usr/local/mysql/bin/mysqldump”

mysql_show=”/usr/local/mysql/bin/mysqlshow”

##

## Are you compress ‘xxxx-2001xxxx-xx.sql’ ?

##     1 = yes(xxxx-2001xxxx-xx.sql.gz)

##     0 = no

db_compress=”1″

##

## not include DB list ?

not_dump_db=”test|test2″

##

## end user setting !!!

####################################################

sysdtime=`date +%Y%m%d-%H`

mysql_connect=”-h $mysql_host -u root -p$mysql_rootpasswd”

mysql_DBs=`$mysql_show $mysql_connect | egrep -v “(${not_dump_db}

|Databases|\\+)” | awk ‘{print $2}’ 2>/dev/null`

mysql_dump_work() {

        mysql_each_DB=”$1″

        mysql_dump_file=”${mysql_backupdir}/${mysql_each_DB}-

${sysdtime}.sql”

        $mysql_dump $mysql_connect $mysql_each_DB >

$mysql_dump_file

        if [ “$db_compress” = “1” ] ; then

                gzip -9 $mysql_dump_file

        fi

}

if [ “$mysql_DBs” != “” ] ; then

        if [ ! -d “$mysql_backupdir” ] ; then

                mkdir -p “$mysql_backupdir”

                chmod 700 “$mysql_backupdir”

        fi

        ## All DB dump to one file(.sql) !!!!

        ## Verbal advice –> LSN tip board : ‘lubzzon’

        ## –all-databases > all_db.sql

        ##

        for mysql_DB in $mysql_DBs ; do

                mysql_dump_work $mysql_DB

        done

        #chmod 600 $mysql_backupdir

        #chown root mysql_backupdir

fi

exit 0