同事整理的备份脚本，在从库上执行。Xtrabackup目前还不支持5.5.20。

[mysql@MYSQL-DB02 ~]$ crontab -l

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

[mysql@MYSQL-DB02 ~]$ more /home/mysql/cron/backup_db.sh

. ~/.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 {} \;

ITPub博客

mysql5.5.20备份

最新文章