mysql备份shell脚本

1.创建一个：backdb.sh的shell脚本文件

#!/bin/sh

user="root"
#备份用户密码
password="123123"
#mysql连接端口
port="3306"
mysql_path="/usr/bin/"
date=$(date +%Y-%m-%d_%H-%M-%S)
del_date=$(date +%Y-%m-%d)
#备份路径---需要修改
backup_path="/home/backup"
backup_log_path="/home/backup/logs"
day=0
backup_log="/home/backup/logs/mysqldump_backup_${date}.log"
# 除了127.0.0.1以外的ip截取 /sbin/ifconfig -a|grep inet|grep -v 127.0.0.1|grep -v inet6|awk '{print $2}'|tr -d "addr:"
#删除以前备份
find ${backup_path} -type f -mtime +$day -name "*.tar.gz" -exec rm -rf {} \; > /dev/null 2>&1 

for f in `ls $backup_path`;
do
  if [[ $f = ${del_date}_* ]]; then
    rm -rf $backup_path/$f
  fi
done
#建立备份目录
if [ ! -e $backup_path/${date} ];then
 mkdir -p {$backup_path/${date},$backup_log_path}
fi
 
 
#echo "开始备份所有数据库" 
echo "备份开始,结果查看 $backup_log"
echo "==========All databases backups begin==========" >>$backup_log
#备份并压缩
 for dbname in $(mysql -P$port -u$user -p$password -A -e "show databases\G"|grep Database|grep -v schema|grep -v test|awk '{print $2}')
 do
 sleep 1
 mysqldump -P$port -u$user -p$password $dbname > $backup_path/${date}/$dbname.sql
 if [[ $? == 0 ]];then
  cd $backup_path/${date}
  size=$(du $backup_path/${date}/$dbname.sql -sh | awk '{print $1}')
  echo "备份时间:${date} 备份方式:mysqldump 备份数据库:$dbname($size) 备份状态:成功！" >>$backup_log
 else
  cd $backup_path/${date}
  echo "备份时间:${date} 备份方式:mysqldump 备份数据库:${dbname} 备份状态:失败,请查看日志." >>$backup_log
 fi
 
 done
 
 cd $backup_path
 tar zcpvf mysql_all_databases_$date.tar.gz $date
 rm -rf $backup_path/$date

du mysql_all_databases_$date.tar.gz -sh | awk '{print "文件:" $2 ",大小:" $1}'
echo "==========All databases backups over==========" >>$backup_log

3.添加定时任务

crontab -e

4:查看定时任务是否添加成功

crontab -l

 5:文件夹授权

chmod -R 777 文件夹路径

   

chmod -R 777 /home/backup

chmod -R 777 /home/backup/logs

日志目录:/var/spool/mail/