使用shell脚本备份MySQL数据库

绝地灬酷狼 2022-07-13 00:22 275阅读 0赞

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/][https_github.com_vfhky_shell-tools] 。

GitHub地址： [https://coding.net/u/vfhky/p/shell-tools/git][https_coding.net_u_vfhky_p_shell-tools_git] 。

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

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

crontab -e

[https_github.com_vfhky_shell-tools]: https://github.com/vfhky/shell-tools/
[https_coding.net_u_vfhky_p_shell-tools_git]: https://coding.net/u/vfhky/p/shell-tools/git