CentOS下自动备份7天的mysql数据库

###mysql授权
grant all privileges on *.* to 'backup'@'%' identified by 'password;
grant all privileges on *.* to 'backup'@'localhost' identified by 'password';

###建立目录
mkdir /pkg/web/mysqlbackup/mysqlbackup.sh
#传入mysqlbackup.sh

###建立cron

echo "10 4    * * *   root    /pkg/web/mysqlbackup/mysqlbackup.sh" >> /etc/crontab

mysqlbackup.sh里的内容如下：

++++++++++++++++++++++++++++++++#!/bin/bash
# mysqlbackup.sh: backup mysql databases and keep newest 7 days backup
#
# Last updated: 2013-02-28
# ----------------------------------------------------------------------
# Copyright (C) 2013 garfield
# Email :garfield3426@gmail.com
# ----------------------------------------------------------------------


#About your login mysql information
db_user="backup"
db_passwd="password"
db_host="localhost"


#The directory for story your backup file
backup_dir="/pkg/web/_BACKUP/"


#Date format your backup file
time=$(date +"%Y-%m-%d")


#Email file
emailfile=$backup_dir/email.txt


#Email address
#email="garfield3426@gmail.com"


#Check whether there is a directory backup
if [ ! -d "$backup_dir" ]
then
mkdir -p $backup_dir
chown -R mysql.mysql $backup_dir
fi


#check mount point if not mount
#IP="192.168.10.3"
#ping -c 1 -w 1 $IP &>/dev/null
#if [ $? = 0 ]
#then
#df -k | grep $IP &>/dev/null || mount -t cifs //$IP/IDKCenterDataBackup/svnbackup $SVN_BACKUP -o username=share,password=share,iocharset=utf8
# df -k | grep $IP &>/dev/null || mount -t nfs -o intr,nolock,rsize=8192,wsize=8192 192.168.10.3:/nfs/IDKCenterDataBackup/wikibackup $backup_dir
#else
# echo "$(date +%Y-%m-%d-%T)==>Backup Computer[$IP] already shutdown!" >> $backup_dir/backup_error.log
# echo " " >> $backup_dir/backup_error.log
# exit 1
#fi


#Check the dircetory for story backup is writeable
test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0 


#Check the directoy for story backup the newest backup
test ! -d "$backup_dir/mysql-$time" && mkdir -p "$backup_dir/mysql-$time" 


#Create log file
mkdir -p "$backup_dir/log"
Logfile="$backup_dir/log/$time.log"


#Get all databases
all_db=$(/usr/bin/mysql -h $db_host -u $db_user  -p$db_passwd -Bse 'show databases'|grep -Ev '(test)')


echo $all_db 


for db in $all_db
do
#/usr/local/bin/mysqldump -h $db_host -u $db_user  -p$db_passwd --single-transaction --opt --default-character-set=utf8 --master-data=2 --delete-master-logs $db | /bin/gzip -9 > "$backup_dir/$time/$time.$db.gz"
/usr/bin/mysqldump -h $db_host -u $db_user  -p$db_passwd --single-transaction --opt --default-character-set=utf8 $db | /bin/gzip -9 > "$backup_dir/mysql-$time/$db.gz"
done


#------------------------------------------del bin_log--------------------
#$MYSQL -u $db_user -h $db_host -p$db_passwd -e 'PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);'
#-------------------------------------------------------------------------


#--------------------------------Date for 7 days ago----------------------
#Backup for 7 days ago
oldtime=$(date --date ' 7 days ago ' +'%Y-%m-%d')
oldfile="$backup_dir/mysql-$oldtime"
#Check 7 days ago backup
if [ -d "$backup_dir/mysql-$oldtime" ]
then
rm -rf "$oldfile" >>$Logfile 2>&1
echo "[$oldtime file] delet old backup success!">>$Logfile 2>&1
else
echo "no [$oldtime file] backup file!!">>$Logfile 2>&1
fi


exit 0;