2019-08-27 11:38:03 4 Comments Linux Boy.Lee

Centos Regular Auto-backup MySQL Database and send to email address

Centos use Cron do Regular Auto-Backup for MySQL database, and auto send email to Gmail by Mutt, and use Gmail's SPAM Strategy do Regularly Delete.


{ No.1 write Shell file }

// Path /root/eeBakcupShell/backup-email-mysql-data.sh

d=`date "+%Y-%m-%d"`
#current data:Year-Month-day

#backup file name:yiilib.com_currentData

#tmp save path, will do auto delete at the end.

mysql -e "show databases;" -uroot -ppassword | grep -Ev "Database|information_schema|mysql|test" | xargs mysqldump -uroot -ppassword --skip-lock-tables --databases  | gzip > $FP$FName.sql.gz
#here has a little trick to find need backup database name: list all database names and return no need backup name(Database|information_schema|mysql|test),rest will be backup, also add gzip support

echo "backup files"|mutt -s "backup tmp $t" yiilib.com@gmail.com -a $FP$FName.sql.gz
#use Mutt send mail to yiilib.com@gmail.com

rm -rf $FP$FName.sql.gz
#delete backup gz file



{ No.2 Test }

/root/eeBakcupShell/backup-email-mysql-data.sh, check your mail box for the new email, also check the spam box too.

if got error "/bin/sh^M: bad interpreter: No such file or directory", do the follow change.

vi /root/eeBakcupShell/backup-email-mysql-data.sh

:set ff  //here show fileformat=dos

:set ff=unix  //change to unix

:wq  //save and exit


{ No.3 set Cron task }

# crontab -e

//save result to log will help more
15 03 * * * /root/eeBakcupShell/backup-email-mysql-data.sh > /log/eelog.log


{ No.4 Use Gmail's SPAM Strategy do Regularly Delete }

Gmail will delete SPAM email in 30 days, so if you found the backup emails are in SPAM box, it's ok, we will use the 30 days delete logic here.


{ No.5 Setting to Non-SPAM Email }

If you want keep backup email more than 30 days, you can setting some Gmail Filters, so Gmail will move the backup email out SPAM box. In this case the backup email will not be Regularly deleted, but you will face a new problem, the capacity of your gmail account is 15GB, you need do manual delete.

Setting Filter like this:

0. open gmail website and login your account
1. Settings 
2. Filters and Blocked Address
3. Create a new filter
3.1 Has the words : 'backup' //subject keywords
3.2 Has attachment : yes
3.3 Create filter with this search
3.4 Never send it to Spam : yes
3.5 Create filter
3.x config more options if you want


{ Links }