MySQL备份脚本 作者: sysit 分类: d 发表于 2019-10-21 74人围观 ## 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 ``` 如果觉得我的文章对您有用,请随意赞赏。您的支持将鼓励我继续创作! 赞赏支持