mysql 备份脚本

时间：2006-02-28 来源：ruiqingzheng

mysql备份脚本

#!/bin/bash
# author: juey
now=`date "+%y%m%d%H%M"`
overtime=`date -d'3 day ago' "+%y%m%d"`
trap 'rm -f "${bakfname}"; rm -f "${bakfname}".tgz ; exit' 2 15

echo "Mysql bakup-script `basename $0` running at $now............................."
echo "Print deault backup vars:"
echo "Target database is mysql , bakpath : /backup, method: mysqldump "
echo "If you want change settings please enter 'yes' or 'y'"
read -t 3 ans

if [ "$ans" = "yes" -o "$ans" = "y" ] ; then
        read -s -p "mysql root password:" psd && echo
        read -p "bak path:" bakpath
        read -p "method:" method
        read -p "which database you want to backup:" dbname
else
        psd="123456"
        bakpath=${bakpath:="/backup"}
        method=${method:="mysqldump"}
        dbname=${dbname:="mysql"}
fi

check_vars()
{
        # make sure the psd bakpath method dbname are not empty
        if [ -z "$psd" -o ! -d "$bakpath" -o -z "$method" -o -z "$dbname" ] ; then
                echo "The variants you entered error!!!!"
                exit -1
        fi

        # check if the method is : mysqldump , tar or hotcopy
        # another way to deal
        #case "$method" in
#       mysql|tar|mysqlhotcoy)
#       match = yes
#       ;;
#       *)
#       match = no
#       exit -1
#       ;;
#       esac
        echo "$method" | grep -qe "mysqldump\|tar\|mysqlhotcopy"
        if [ $? -eq 0 ]; then
                echo "Will use method $method to bakup"
        else
                echo "Backup method $mmethod not support,now exit"
                exit -2
        fi

        # check if the db is exist
        # little complex so .... ignored
}

backup()
{
        for i in $dbname
        do
                bakfname=`echo "${bakpath}"/"${i}_${now}".sql`
#echo -e "\npsd=$psd bakpath=$bakpath method=$method dbname=$dbname bakfname=$bakfname "
                echo "Ok ... start backup $i to $bakfname " && sleep 3
#action statment here , just only mysqldump method here
                case "$method" in
                mysqldump)
                        $method -u root -p${psd} --database ${i} --add-drop-table > $bakfname
                        if [ $? -eq 0 ] ; then
#if bak succeed tar and zip it
                        tar zcf ${bakfname}.tgz $bakfname > /dev/null 2>&1
                        echo "Bakup succeed : $bakfname and ${bakfname}.tgz......"
                        else
                                rm -f $bakfname
                                echo "Failed backup!!! Please check mysqld is running"
                                sleep 2
                                exit -1
                        fi
                        ;;
                *)
                        echo "sorry , till now just mysqldump support"
                        exit -1
                        ;;
                esac

        done
}

clean_overtime()
{
        echo "Cleaning three days ago bak packages."
        for j in $dbname
        do
                for i in `ls $bakpath| grep "^$j"`
                do
                        #
                        no=`echo $i|sed 's/.*_$[0-9]\{1,\}$$[0-9]\{4\}.*$/\1/'`
                        if [ `expr $no - $overtime` -le 0 ]; then
                                rm "$bakpath"/"$i"
                        fi
                done
        done
}

check_vars
backup
clean_overtime

exit 0

运行

[root@localhost code]# ./baksql.sh
Mysql bakup-script baksql.sh running at 0602280429.............................
Print deault backup vars:
Target database is mysql , bakpath : /backup, method: mysqldump
If you want change settings please enter 'yes' or 'y'
y
mysql root password:
bak path:/root
method:mysqldump
which database you want to backup:mysql cp
Will use method mysqldump to bakup
Ok ... start backup mysql to /root/mysql_0602280429.sql
Bakup succeed : /root/mysql_0602280429.sql and /root/mysql_0602280429.sql.tgz......
Ok ... start backup cp to /root/cp_0602280429.sql
Bakup succeed : /root/cp_0602280429.sql and /root/cp_0602280429.sql.tgz......
Cleaning three days ago bak packages.