使用shell脚本备份MySQL数据库

1 使用说明

下面是MySql数据库的shell备份脚本，其中参数 DBLIST 表示要备份的mysql数据库名称、 NUMDAYS 表示保留多少天以内的备份文件、 MyUSER 和 MyPASS 表示mysql数据库的用户名和密码。最终，每次执行这个脚本生成的备份文件名类似于 系统日期时间+.sql.gz ，例如20150606.sql.gz。

使用时通过 chmod +x mysql_backup.sh 命令添加执行权限，然后可以利用Linux系统的crontab定时任务工具执行该脚本。例如下面这个配置就是每天凌晨1点的时候执行该shell脚本，生成mysql数据库的备份文件。

[root@typecodes bak_list]# crontab -e
0 1 * * * /mydata/backups/bak_list/mysql_backup.sh

2 脚本mysql_backup.sh的全部内容

以后的更新都会提交到本文小节3中的两个git仓库中。

#!/bin/bash
# MySQL Backup Script v1.0.0
# (c) 2015 vfhky https://typecodes.com/linux/centos7mysqlregularbackup.html
# Reference: https://github.com/chekolyn/bash-scripts/blob/master/mysql-dbs-backup.sh
# https://github.com/vfhky/shell-tools/blob/master/backup/mysql_backup.sh
# https://coding.net/u/vfhky/p/shell-tools/git/blob/master/backup/mysql_backup.sh

# Space separated list of databases
DBLIST="your mysql database name witch you want to backup"

# Backup to this directory
BACKUPDIR=/mydata/backups/data/mysql

# Number of days to keep
NUMDAYS=60

# Some linux command and your mysql configure
FINDCMD="find"
MYSQLCMD="mysql"
MyUSER="your mysql user name"                   # USERNAME
MyPASS="your mysql password"                   # PASSWORD 
MyHOST="localhost"          # Hostname
DUMPCMD="mysqldump -u$MyUSER -h $MyHOST -p$MyPASS --lock-tables --databases "
GZIPCMD="gzip"

# Backup date format,e.g 20150505_2010
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 $@
}

# Sanity checks
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

# Lock down permissions
umask 077

# Create directory if needed
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

# exit 0

3 Shell Script工具集合

目前把这个脚本托管在自己的Coding.net和GitHub的上一个shell script工具集合，地址如下：

Coding.net地址： https://github.com/vfhky/shell-tools/ 。

GitHub地址： https://coding.net/u/vfhky/p/shell-tools/git 。

原文地址:http://www.tuicool.com/articles/qIzUR3q

补充说明：如果备份出来的*.sql.gz 文件无法下载到本地的话，请对文件执行命令：chmod -R 777   *.sql.gz

crontab -e