数据库自动备份脚本

#!/bin/sh

# config information 定义数据库连接

db_host=localhost

db_port=3306

db_username=root

db_password=root!@#%

db_name=backup

 

backup_dir="/opt/backup/"

 

if [ ! -d "$backup_dir" ];then

mkdir "$backup_dir"

fi

 

today=`date "+%Y%m%d%H"`

 

# 定义需要备份的数据库表数组

backup_tables=(t_user t_role t_application t_user_role )

 

echo "================  begining backup basic data  ================="

 

cd $backup_dir

# 遍历备份的数据库表

for t in ${backup_tables[@]};

do

backup_file="${t}_${today}.sql"

if [ ! -e "$backup_file" ];then

  rm -f "$backup_file"

fi

# 最核心的就是这句话，使用mysqldump命令执行备份

mysqldump -h${db_host} -u $db_username -p${db_password}  $db_name $t > $backup_dir/$backup_file

 

done

 

finish_date=`date '+%Y-%m-%d %H:%M:%S'`

 

echo "The basic information tables backup successfully completed at ${finish_date}."

 

one_days_ago=`date -d "1 days ago" +%Y%m%d`

two_days_ago=`date -d "2 days ago" +%Y%m%d`

three_days_ago=`date -d "3 days ago" +%Y%m%d`

 

# 反向删除

 

find $backup_dir -name "*${one_days_ago}*.sql" |grep -v "${one_days_ago}00.sql" |grep -v "${one_days_ago}06.sql" |grep -v "${one_days_ago}12.sql" |grep -v "${one_days_ago}18.sql" | xargs -i rm -f {}

 

find $backup_dir -name "*${two_days_ago}*.sql" |grep -v "${two_days_ago}00.sql" |grep -v "${two_days_ago}12.sql" | xargs -i rm -f {}

 

find $backup_dir -name "*${three_days_ago}*.sql"|grep -v "${three_days_ago}00.sql" | xargs -i rm -f {}