#注意, the backup machine to the remote MySQL server requires a key-free login, which is executed every five minutes in the scheduled task, avoiding the Mysqlbinlog server process from hanging up for a long time without anyone knowing cat backup_binlog.sh #!/bin/bash[ -e /etc/profile ] && source /etc/profile | | exit 0# local binlog path local_binlog_dir=/data/3306/247binlog[ ! -d "$local _binlog_dir" ] && mkdir -p "$local _binlog_dir" cd "$local _binlog_dir" # Remote server SSH port ssh_port=22# remote server ipremote_host=192.168.0.68# local binlog file name 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} "&nbSp;cat \${remote_binlog_dir}/mysql-bin.index | tail -n 1 | awk -f '/' ' {print \\ $NF} ' ' #远程mysql用户remote_user =root# remote mysql user password 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" exitfi 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 & fifi}function Stop () {ps uax | grep mysqlbinlog | grep raw | awk ' { print $2} ' | xargs kill}case $1 instart) start ;; Stop) stop ;; *) # usage Basename= ' basename ' echo ' usage: $basename {start|stop} [ mysql binlogserver options ] " exit 1 ;; esac #使用sh backup_binlog.sh start #开启binlog server starts a backup and starts a Mysqlbinlog server daemon that listens to the remote server's Binlog , changes are synchronized to the backup machine sh backup_binlog.sh stop #关闭binlgo Server daemon, stop backing up the remote server binlog
Scripts for remote backup binlog using Mysqlbinlog server