Linux 使用shell脚本备份数据库

创建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

成功之后会在你配置的路径生成以时间为名的备份文件