mysql5.5.20備份

同事整理的備份指令碼，在從庫上執行。Xtrabackup目前還不支援5.5.20。

 

0 1  * * * /home/mysql/cron/backup_db.sh >> /home/mysql/cron/backup_db.log 2>&1

. ~/.bash_profile

#mysqldump=/data/mysql/bin/mysqldump
#mysqlbinlog=/data/mysql/bin/mysqlbinlog
#mysqladmin=/data/mysql/bin/mysqladmin
bak_dir=/data/mysql/backup
log_dir=/data/mysql/log
bak_opt="--single-transaction --flush-logs"
day=`date +%a`
hour=`date +%Y%m%d%H`
time=`date +%F%H%M%S`
deltime=`date "-d 3 day ago" +%Y%m%d%H%M%S`
db=thran
user=bak_user
passwd=BUteYhKEfVsqnnMSTOyo
bin_file_name=`ls /data/mysql/log | grep "mysql-bin" | grep -v "index" | awk -F"/" '{print $1}' | tail -n1`

#Sunday: full backup
full()
{
  mysqldump -u$user -p$passwd $bak_opt $db|gzip >$bak_dir/$db/full_$hour.gz
}

#Others: incr backup
incr()
{
  mysqladmin -u$user -p$passwd flush-logs
  mysqlbinlog -u$user -p$passwd $log_dir/$bin_file_name|gzip >$bak_dir/$db/incr_$hour.gz

  #Record Last Backup binlog scn
  echo $bin_file_name $time >> $bak_dir/binlog.index

  #delete old binlog file, use root privelegs
  bin_file_name=`ls /data/mysql/log | grep "mysql-bin" | grep -v "index" | awk -F"/" '{print $1}' | tail -n1`
  mysql -uroot -prs0JpKfvhjSIvknHQQRv -e"purge master logs to '${bin_file_name}' "
}

if [ $day = "Sun" ]
then
 full 
else
 incr
fi

# delete 10 days ago database backup
find $bak_dir/$db -mtime +10 -type f -exec rm -rf {} \;