mysql数据库备份脚本

通过定时任务对mysql进行备份

1,假设每周备份一次，删除2周前的备份,脚本如下：

vi mysql_back.sh

#!/bin/bash
# mysql备份脚本，每周日备份一次，删除2周前备份
#backdir
backupDir=/home/mysql/dback
#mysqlDump
mysqldump=/usr/local/mysql/bin/mysqldump
#ip address
host=127.0.0.1
#username && password
username=root
password=xxxxx

#今天日期
today=`date +%Y%m%d`
#2周前的日期
timeTwoWeekAgo=`date -d -15day +%Y%m%d`

echo '开始备份mysql'
$mysqldump -h$host --port 13306 -u$username -p$password --all-databases | gzip > $backupDir/mysqlback-$today.sql.gz
echo '成功备份'$database'到'$backupDir/mysqlback-$today.sql.gz

if [ ! -f "$backupDir/mysqlback-$timeTwoWeekAgo.sql.gz" ]; then
          echo '2周前备份不存在，无需删除'
      else
          \rm $backupDir/mysqlback-$timeTwoWeekAgo.sql.gz
          echo '删除2周前的备份文件'$backupDir/$database-$timeTwoWeekAgo.sql.gz
fi

2,定时任务设置(设置每周日1:30进行备份)

crontab -e

30 1 * * 0  /home/mysql/mysql_back.sh