mysql+自动化数据备份_Mysql：自动化备份

简介

在这个数据为王的时代，数据的备份十分重要，这里就分享一篇mysql数据库自动备份的脚本(是从网上搜到的)，其将配置文件和备份脚本分离，提高了安全性，脚本风格规范严谨，分享给大家希望对需要的小伙伴有所帮助。

mysql备份配置文件

配置文件中，可以用y、n控制是否开启该功能，需要填写相关数据库等信息，比较好懂，在此不多说。

cat >> backupmysql.conf << "EOF"

# William Guozi

# https://www.cnblogs.com/William-Guozi, williamguozi.github.io

# You are free to modify and distribute this code,

# so long as you keep my name and URL in it.

# your MySQL server's name

SERVER=`hostname -f`

# directory to backup to

BACKDIR=/home/rdsbackup

# date format that is appended to filename

DATE=`date -u +'%F-%T'`

#----------------------MySQL Settings--------------------#

DBNAMES[0]="db1 db2 db3" # databases you want to backup, separated by a space; leave empty to backup all databases on this host

DBUSER[0]="username" # MySQL username

DBPASS[0]='password' # MySQL password

DBHOST[0]="localhost" # your MySQL server's location (IP address is best)

DBPORT[0]="3306" # MySQL port

DBTABLES[0]="" # tables you want to backup or exclude, separated by a space; leave empty to back up all tables

DBTABLESMATCH[0]="include" # include will backup ONLY the tables in DBTABLES, exclude will backup all tables BUT those in DBTABLES

DBOPTIONS[0]="--quick --single-transaction"

#----------------------Mail Settings--------------------#

# set to 'y' if you'd like to be emailed the backup (requires mutt)

MAIL=y

# email addresses to send backups to, separated by a space

EMAILS="ops@glinux.cn"

SUBJECT="$DBNAMES[0] Backup On $SERVER ($DATE)"

#----------------------Weixin Settings--------------------#

# set to 'y' if you'd like to be emailed the backup (requires mutt)

WEIXIN=n

# email addresses to send backups to, separated by a space

CropID='wwc791b89a193451a'

Secret='3YwOqQ85VTYc3K6rbwKcFlMmwjbW39KeE-Cd6SMEA'

#也就是agentid

AppID=1000002

##也就是用户的唯一表示id

UserID=@all

##也就是联系人的组id

PartyID=1

WeChatMsg="$DBNAMES Backup On $SERVER ($DATE)"

#----------------------Duplicity Settings--------------------#

DUPLICITY=n

DUPLICITY_OPTIONS="--no-encryption -v8 --s3-use-new-style"

DUPLICITY_TARGET_URL="s3+http://my-backups/db/"

#----------------------S3 Settings--------------------#

S3_UPLOAD=n

S3_PATH="my-backups/db/"

S3_OPTIONS=""

AWS_CLI_OPTIONS="--region us-east-1"

#----------------------FTP Settings--------------------#

# set "FTP=y" if you want to enable FTP backups

FTP=n

# FTP server settings; group each remote server using arrays

# you can have unlimited remote FTP servers

FTPHOST[0]="ftphostip"

FTPUSER[0]="username"

FTPPASS[0]="password"

FTPDIR[0]="backups"

FTPPORT[0]="21"

# directory to backup to; if it doesn't exist, file will be uploaded to

# first logged-in directory; the array indices correspond to the FTP info above

#-------------------Deletion Settings-------------------#

# delete old files?

DELETE=y

# how many days of backups do you want to keep?

DAYS=7

#-------------------Compression Settings-------------------#

COMPRESSION_COMMAND="gzip -f -9"

COMPRESSION_EXTENSION="gz"

#----------------------End of Settings------------------#

mysql备份脚本

其中微信发送部分为添加部分，原版请查看参考文档。

#! /bin/bash

PATH=$PATH:/usr/local/mysql/bin

# William Guozi

# https://www.cnblogs.com/William-Guozi, williamguozi.github.io

# You are free to modify and distribute this code,

# so long as you keep my name and URL in it.

#----------------------Start of Script------------------#

function die () {

echo >&2 "$@"

exit 1

}

CONFIG=${1:-`dirname $0`/backupmysql.conf}

[ -f "$CONFIG" ] && . "$CONFIG" || die "Could not load configuration file ${CONFIG}!"

# check of the backup directory exists

# if not, create it

if [ ! -d $BACKDIR ]; then

echo -n "Creating $BACKDIR..."

mkdir -p $BACKDIR

echo "done!"

fi

for KEY in "${!DBHOST[@]}"; do

echo "Backing up MySQL database on ${DBHOST[$KEY]}..."

if [ -z "${DBNAMES[$KEY]}" ]; then

echo -n "Creating list of all your databases..."

DBS=`mysql -h ${DBHOST[$KEY]} --user=${DBUSER[$KEY]} --password=${DBPASS[$KEY]} -Bse "show databases;"`

echo "done!"

else

DBS=${DBNAMES[$KEY]}

fi

# filter out the tables to backup

if [ -n "${DBTABLES[$KEY]}" ]; then

if [ ${DBTABLESMATCH[$KEY]} = "exclude" ]; then

TABLES=''

for table in ${DBTABLES[$KEY]}; do

TABLES="$TABLES --ignore-table=$table "

done

else

TABLES=${DBTABLES[$KEY]}

fi

fi

start_time=`date +%T`

Beijing_start_time=`date -u -d "$start_time -0800"`

for database in $DBS; do

echo -n "Backing up database $database..."

test ${DBHOST[$KEY]} = "localhost" && SERVER=`hostname -f` || SERVER=${DBHOST[$KEY]}

mysqldump --host ${DBHOST[$KEY]} --port ${DBPORT[$KEY]} --user=${DBUSER[$KEY]} --password=${DBPASS[$KEY]} \

${DBOPTIONS[$KEY]} $database $TABLES > $BACKDIR/$SERVER-$database-$DATE-mysqlbackup.sql

$COMPRESSION_COMMAND $BACKDIR/$SERVER-$database-$DATE-mysqlbackup.sql

echo "done!"

done

done

# end_time=date -u -d "`date +%T` -0800"

end_time=`date +%T`

Beijing_end_time=`date -u -d "$end_time -0800"`

# if you have the mail program 'mutt' installed on

# your server, this script will have mutt attach the backup

# and send it to the email addresses in $EMAILS

####delete database not in this machine

# find $BACKDIR -size -4k |xargs rm -rf

if [ $MAIL = "y" ]; then

BODY="你的备份已经完毕! backup start at $Beijing_start_time,end at $Beijing_end_time \n\n"

BODY=$BODY`cd $BACKDIR; for file in *$DATE-mysqlbackup.sql.$COMPRESSION_EXTENSION; do echo "md5值是"; md5sum ${file}&&du -sh ${file}|awk '{print $1}'; done`

ATTACH=`for file in $BACKDIR/*$DATE-mysqlbackup.sql.$COMPRESSION_EXTENSION; do echo -n "-a ${file} "; done`

echo -e "$BODY" | mutt -s "$SUBJECT" -- $EMAILS

if [[ $? -ne 0 ]]; then

echo -e "ERROR: Your backup could not be emailed to you! \n";

else

echo -e "Your backup has been emailed to you! \n"

fi

fi

# send by weixin

if [ $WEIXIN = "y" ];then

GURL="https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=$CropID&corpsecret=$Secret"

Gtoken=$(/usr/bin/curl -s -G $GURL | awk -F\" '{print $10}')

PURL="https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token=$Gtoken"

function body() {

printf '{\n'

printf '\t"touser": "'"$UserID"\"",\n"

printf '\t"toparty": "'"$PartyID"\"",\n"

printf '\t"msgtype": "text",\n'

printf '\t"agentid": "'"$AppID "\"",\n"

printf '\t"text": {\n'

printf '\t\t"content": "'"$WeChatMsg"\""\n"

printf '\t"},\n'

printf '\t"safe:":0\n'

printf '}\n'

}

/usr/bin/curl --data-ascii "$(body)" $PURL

fi

if [ $DELETE = "y" ]; then

OLDDBS=`cd $BACKDIR; find . -name "*-mysqlbackup.sql.$COMPRESSION_EXTENSION" -mtime +$DAYS`

REMOVE=`for file in $OLDDBS; do echo -n -e "delete ${file}\n"; done` # will be used in FTP

cd $BACKDIR; for file in $OLDDBS; do rm -v ${file}; done

if [ $DAYS = "1" ]; then

echo "Yesterday's backup has been deleted."

else

echo "The backups from $DAYS days ago and earlier have been deleted."

fi

fi

if [ $DUPLICITY = "y" ]; then

duplicity full --progress $DUPLICITY_OPTIONS $BACKDIR $DUPLICITY_TARGET_URL

fi

if [ $S3_UPLOAD = "y" ]; then

aws $AWS_CLI_OPTIONS s3 sync $BACKDIR s3://$S3_PATH $S3_OPTIONS

fi

if [ $FTP = "y" ]; then

echo "Initiating FTP connection..."

cd $BACKDIR

ATTACH=`for file in *$DATE-mysqlbackup.sql.$COMPRESSION_EXTENSION; do echo -n -e "put ${file}\n"; done`

for KEY in "${!FTPHOST[@]}"; do

echo -e "\nConnecting to ${FTPHOST[$KEY]} with user ${FTPUSER[$KEY]}..."

ftp -nvp <

open ${FTPHOST[$KEY]} ${FTPPORT[$KEY]}

user ${FTPUSER[$KEY]} ${FTPPASS[$KEY]}

binary

tick

mkdir ${FTPDIR[$KEY]}

cd ${FTPDIR[$KEY]}

$REMOVE

$ATTACH

quit

EOF

done

echo -e "FTP transfer complete! \n"

fi

echo "Your backup is complete!"

制定定时任务

crontab -l

00 14 * * * bash /data/scripts/backupmysql.sh

效果展示

下图为微信收到的报告信息

参考文档