MySQL备份脚本

作者: sysit 分类: d 发表于 2019-10-21 126人围观

## 1. 授权

mysqldump 所需要的权限说明：

* 备份table —— select 权限； 
* 备份view —— show view 权限； 　　
* 备份trrigger —— trriger 权限； 　
* 一致的备份 —— lock tables 权限；

```
GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER ON *.* TO 'backup'@'%' IDENTIFIED BY 'password';
```
 
## 2. 脚本

```
#!/bin/bash
DB_HOST="127.0.0.1"
DB_PORT=3306
DB_USER="backup"
DB_PASS="password"

MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"

DEST="/data/backup"
MBD="$DEST/mysql"

DATE="$(date +"%Y-%m-%d")"

#DAYS=10代表删除10天前的备份
DAYS=10

FILE=""

DBS="$($MYSQL  -h ${DB_HOST} -P ${DB_PORT} -u ${DB_USER} -p${DB_PASS} -Bse 'show databases' | grep -Evi "information|performance|mysql|sys")"

#DBS="db1 db2 db3"

Ignore_DB="test"

[ ! -d $MBD ] && mkdir -p $MBD || :
# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST

for db in $DBS
do
  skipdb=-1
  if [ "${Ignore_DB}" != "" ];
  then
    for i in ${Ignore_DB}
    do
      [ "$db" == "$i" ] && skipdb=1 || :
    done
  fi
  if [ "$skipdb" == "-1" ] ; then
    FILE="$MBD/${db}_${DATE}.gz"
    $MYSQLDUMP -h ${DB_HOST} -P ${DB_PORT} -u ${DB_USER} -p${DB_PASS} $db --default-character-set=utf8  --set-gtid-purged=OFF --opt -Q -R --skip-lock-tables | $GZIP -9 > $FILE
    find $MBD -name "${db}_*" -type f -mtime +$DAYS -exec rm {} \;  #删除n天前的备份文件(注意：{} \;中间有空格)
  fi
done
```

如果觉得我的文章对您有用，请随意赞赏。您的支持将鼓励我继续创作！