创建cmysql.sh 文件
输入以下内容
#!/bin/bash
DBLIST="备份的数据库名(数据库得创建相对应的数据库)"
BACKUPDIR=/sdb/data/mysql //备份文件存放路径,如果没有就自动创建
NUMDAYS=30
FINDCMD="find"
MYSQLCMD="mysql"
MyUSER="root"//数据库用户名
MyPASS="123456"//密码
MyHOST="localhost"
DUMPCMD="mysqldump -u$MyUSER -h $MyHOST -p$MyPASS --lock-tables --databases "
GZIPCMD="gzip"
BACKUPDATE=`date +%Y%m%d_%H%M`
function USAGE() {
cat << EOF
usage: $0 options
This script backs up a list of MySQL databases.
OPTIONS:
-H Show this message
-a Backup all databases
-l Databases to backup (space seperated)
-n Number of days to keep backups
EOF
}
while getopts "hal:n:" opt; do
case $opt in
a)
DBLIST=""
;;
h)
USAGE
exit 1
;;
l)
DBLIST="$OPTARG"
;;
n)
NUMDAYS=$OPTARG
;;
\?)
USAGE
exit
;;
:)
echo "Option -$OPTARG requires an argument." >&2
exit 1
;;
esac
done
function ERROR() {
echo && echo "[error] $@"
exit 1
}
function NOTICE() {
echo && echo "[notice] $@"
}
function RUNCMD() {
echo $@
eval $@
}
if [ ! -n "$DBLIST" ]; then
DBLIST=`$MYSQLCMD -N -s -e "show databases" | grep -viE '(information_schema|performance_schema|mysql|test)'`
if [ ! -n "$DBLIST" ]; then
ERROR "Invalid database list"
fi
fi
if [ ! -n "$BACKUPDIR" ]; then
ERROR "Invalid backup directory"
fi
if [[ ! $NUMDAYS =~ ^[0-9]+$ ]]; then
ERROR "Invalid number of days: $NUMDAYS"
elif [ "$NUMDAYS" -eq "0" ]; then
ERROR "Number of days must be greater than zero"
fi
umask 077
RUNCMD mkdir -p -v $BACKUPDIR
if [ ! -d $BACKUPDIR ]; then
ERROR "Invalid directory: $BACKUPDIR"
fi
NOTICE "Dumping MySQL databases..."
RC=0
for database in $DBLIST; do
NOTICE "Dumping $database..."
RUNCMD "$DUMPCMD $database | $GZIPCMD > $BACKUPDIR/$BACKUPDATE.sql.gz"
RC=$?
if [ $RC -gt 0 ]; then
continue;
fi
done
if [ $RC -gt 0 ]; then
ERROR "MySQLDump failed!"
else
NOTICE "Removing dumps older than $NUMDAYS days..."
RUNCMD "$FINDCMD $BACKUPDIR -name \"*.sql.gz\" -type f -mtime +$NUMDAYS -print0 | xargs -0 rm -fv"
NOTICE "Listing backup directory contents..."
RUNCMD ls -la $BACKUPDIR
NOTICE "MySQLDump is complete!"
fi
给与权限
chmod u+x cmysql.sh
运行脚本
./cmysql.sh
成功之后会在你配置的路径生成以时间为名的备份文件