mysql数据库binlog日志的异地备份
#!/bin/sh######脚本功能：本地定时备份生产目录的binlog到备份目录。#####
user="root"
password="linzj"
port="3306"
host="localhost"
name=`hostname`
last_binlog_dir="/home/mysql/chkpoint"
last_binlog_pos="$last_binlog_dir/last_binlog_pos.txt"###上一次备份的位置点
binlog_backup_dir="/tmp/logbak/$name" ###binlog异地存放目录
mysqlcommand="mysql -u$user -p$password -h$host -P$port -N --protocol=tcp -e "
logdir="/home/mysql/log"
binlogfile="$logdir/binlog_bak.log"
###脚本运行日志存放的目录必须先行存在，否则后续写日志会报日志文件不存在的问题
if [ ! -d $logdir ]
then
mkdir -p $logdir
fi
function create_timestamps()
{
text=$1
echo "$(date +%Y%m%d-%H:%M:%S):$text" >>$binlogfile
}
function init_binlog_backup_dir()
{
###判断存放上一次备份位置点的目录是否存在，不存在就创建
if [ ! -d $last_binlog_dir ]
then
#echo "$(date +%Y%m%d-%H:%M:%S):last binlog save dir is not existed, now create it !!!">>$binlogfile
create_timestamps "last binlog save dir is not existed, now create it !!!"
mkdir -p $last_binlog_dir
fi
###判断备份目录是否存在，不存在就创建
if [ ! -d $binlog_backup_dir ]
then
#echo "$(date +%Y%m%d-%H:%M:%S):binlog backup dir is not existed, now create it !!!">>$binlogfile
create_timestamps "binlog backup dir is not existed, now create it !!!"
mkdir -p $binlog_backup_dir
fi
}
function binlog_backup()
{
###获取存放binlog日志的目录
binlog_dir=`$mysqlcommand "show variables like 'log_bin_index';" 2>/dev/null|awk '{print "dirname "$2}'|sh`
###获取binlog日志的index文件名
binlog_index=`$mysqlcommand "show variables like 'log_bin_index';" 2>/dev/null|awk '{print $2}'`
###获取binlog日志的个数信息
binlog_num=`wc -l $binlog_index|awk '{print $1}'`
###如果是首次备份，偏移量binlog_start为1;如果非首次备份，偏移量binlog_start为上次偏移量+1。
if [ ! -f "$last_binlog_pos" ]
then
binlog_start="1"
else
binlog_last_file=`cat $last_binlog_pos|awk -F \/ '{print $NF}'`
binlog_last=`grep -n $binlog_last_file $binlog_index|awk -F \: '{print $1}'`
binlog_start=`expr ${binlog_last} + 1 `
fi
#echo "binlog_start is $binlog_start"
#flush logs,强制切换到新的binlog文件,避免备份当前最新的binlog文件时，mysql仍对其进行写操作###
$mysqlcommand "flush logs" 2>/dev/null
for (( i=$binlog_start;i $last_binlog_pos
fi
cd $binlog_dir
logfile=`sed -n "${i}p" $binlog_index|awk '{print "basename "$1}'|sh`
num=5 ###重传次数限制
###如果拷贝的binlog文件md5值对应不上，尝试重传$num次，md5值依然对不上，放弃备份binlog并记录日志。
for(( j=1;j> $binlogfile
break
fi
if [ "$j" == "$num" ]
then
rm -fr $binlog_backup_dir/$logfile
echo "$(date +%Y%m%d-%H:%M:%S):$logfile can not backup to the $binlog_backup_dir sucessfully,please check !!!">> $binlogfile
fi
done
done
}
create_timestamps "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"
create_timestamps "the binlog backup start now !!!"
init_binlog_backup_dir
binlog_backup
create_timestamps "the binlog backup end now !!!"
create_timestamps "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"
页:
[1]