个人博客自动备份7天内的mysql数据库

1、先写个shell脚本

#!/bin/sh
# mysql_backup.sh: backup mysql databases and keep newest 7 days backup.
# -----------------------------
db_user="taotao"
db_passwd="taotaoclothes"
db_host="127.0.0.1"
db_port="3306"

# the directory for story your backup file.
backup_dir="/home/mysqldata"

# date format for backup file (dd-mm-yyyy)
time="$(date +"%Y-%m-%d")"

# mysql, mysqldump and some other bin's path
MYSQL="/usr/bin/mysql"
MYSQLDUMP="/usr/bin/mysqldump"
MKDIR="$(which mkdir)"
RM="$(which rm)"
MV="$(which mv)"
GZIP="$(which gzip)"
#针对不同系统，如果环境变量都有。可以去掉

# check the directory for store backup is writeable
test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0

# the directory for store the newest backup
test ! -d "$backup_dir" && $MKDIR "$backup_dir"

# get all databases
for db in mysql sms clothes 'clothes_v2'
do
         $MYSQLDUMP  -u $db_user -h $db_host -p$db_passwd $db -P $db_port  > "$backup_dir/$db_host-$db-$time.sql"
done

#delete the oldest backup 7 days ago
find $backup_dir -name "*.sql" -mtime +10 |xargs rm -rf

#zcat $backup_dir/$db_host-ad_db-$time.gz > $backup_dir/tmpsql &  /usr/bin/mysql -h 127.0.0.1 -u root -p'123456' ad_db< $backup_dir/tmpsql
 exit 0;

2、把shell脚本加入crontab执行

30 3 * * * /home/backup/mysqldump/mysqldump.sh