#注意，备份机到远程mysql服务器需要免密钥登录，此脚本放到计划任务中每五分钟执行一次，避免mysqlbinlog server进程长时间挂掉无人知晓
cat backup_binlog.sh
#!/bin/bash
[ -e /etc/profile ] && source /etc/profile || exit 0
#本地binlog路径
local_binlog_dir=/data/3306/247binlog
[ ! -d "$local_binlog_dir" ] && mkdir -p "$local_binlog_dir"
cd "$local_binlog_dir"
#远程服务器ssh端口
ssh_port=22
#远程服务器ip
remote_host=192.168.0.68
#本地binlog文件名
local_logfile=`ls -al "$local_binlog_dir" | grep 'mysql-bin.[0-9]+' |tail -n 1 | awk '{print $NF}'`
#远程服务器binlog路径
remote_binlog_dir=/data/mysql3306/
#远程服务器第一个binlog文件名
first_remote_lofile=`ssh -p ${ssh_port} -o StrictHostKeyChecking=no ${remote_host} " cat ${remote_binlog_dir}/mysql-bin.index | head -n 1 | awk -F'/' '{print \$NF}'"`
last_remote_logfile=`ssh -p ${ssh_port} -o StrictHostKeyChecking=no ${remote_host} " cat ${remote_binlog_dir}/mysql-bin.index | tail -n 1 | awk -F'/' '{print \$NF}'"`
#远程mysql用户
remote_user=root
#远程mysql用户密码
remote_password=xx
function start() {
running=`ps uax | grep 'mysqlbinlog -R --raw' | grep -v grep|grep raw | awk '{print $2}'`
if [ "$running" != "" ];then
echo "mysqlbinlog server is running"
exit
fi
if [ "$local_logfile" == "" ];then
#echo "the binlogserver is first start "
mysqlbinlog -R --raw --host=$remote_host --user="$remote_user" --password="$remote_password" --stop-never $first_remote_lofile &
else
if ! ssh -p ${ssh_port} -o StrictHostKeyChecking=no ${remote_host} "ls -lh ${remote_binlog_dir}/${local_logfile}" &> /dev/null;then
local_logfile_num=`ll /data/3306/247binlog/ |tail -1 |awk '{print $NF}' |grep -o '([1-9])+([0-9])+'`
binlogs=(`ssh -p ${ssh_port} -o StrictHostKeyChecking=no ${remote_host} "ls -lh ${remote_binlog_dir}/mysql-bin.* |grep -v index |awk -F'/' '{print \$NF}' |wc -l"`)
for binlog in `seq 1 $binlogs`
do
local_logfile_num=`expr $local_logfile_num + 1`
if [ "$local_logfile_num" -lt 10 ];then
local_logfile=mysql-bin.00000${local_logfile_num}
elif [ "$local_logfile_num" -lt 100 ];then
local_logfile=mysql-bin.0000${local_logfile_num}
elif [ "$local_logfile_num" -lt 1000 ];then
local_logfile=mysql-bin.000${local_logfile_num}
elif [ "$local_logfile_num" -lt 10000 ];then
local_logfile=mysql-bin.00${local_logfile_num}
elif [ "$local_logfile_num" -lt 100000 ];then
local_logfile=mysql-bin.0${local_logfile_num}
else
local_logfile=mysql-bin.${local_logfile_num}
fi
if ssh -p ${ssh_port} -o StrictHostKeyChecking=no ${remote_host} "ls -lh ${remote_binlog_dir}/${local_logfile}" &> /dev/null;then
break
fi
done
mysqlbinlog -R --raw --host=$remote_host --user="$remote_user" --password="$remote_password" --stop-never $local_logfile &
else
mysqlbinlog -R --raw --host=$remote_host --user="$remote_user" --password="$remote_password" --stop-never $local_logfile &
fi
fi
}
function stop() {
ps uax | grep mysqlbinlog | grep raw | awk '{print $2}' | xargs kill
}
case $1 in
start)
start
;;
stop)
stop
;;
*)
# usage
basename=`basename "$0"`
echo "Usage: $basename {start|stop} [ MySQL BinlogServer options ]"
exit 1
;;
esac
#使用
sh backup_binlog.sh start #开启binlog server开始备份，会启动一个mysqlbinlog server守护进程一直监听远程服务器的binlog，有变动会同步到备份机
sh backup_binlog.sh stop #关闭binlog server守护进程，停止备份远程服务器binlog