mysqlbinlog Can be disguised as slave Go to the main server to get binlog. This script encapsulates this function .

This script is better used with scheduled tasks .

1. Script download address

github: https://github.com/ddcw/ddcw/blob/master/shells/mysqlBinlogSYNC.sh

Or see the end of the text

2. Instructions

This script does not support positional parameters , Only the parameters in the script can be modified

REMOTE_HOST="172.17.0.10"

REMOTE_PORT="3311"

REMOTE_USER="repuser"

REMOTE_PASSWORD="repuser"

# Empty words , Will be first LOCAL_PRENAME Find the latest binlog, And then from that Binlog Start syncing .

# If you don't get it under this directory , From the current login query lsn Start

# If specified , From the designated Binlog Start , Do not check whether the name is correct

BEGIN_BINLOG=""

# That is, to preserve binlog The directory prefix for , such as

#/data/bin1 Words , Namely /data/bin1binlog.000002 such , Very ugly , Add... To all the directories / ending , This variable is not formatted , What you set is what you set

#/data/bin1/ In this way, it is /data/bin1/binlog.000002

# If it is empty by default , It's the current directory

LOCAL_PRENAME=""

3. Examples of use

Configure the corresponding server first IP Account and password

Just execute the script directly ( It is recommended to put it in the background , It is better to add a scheduled task , If the script hangs, execute it again )

Test data

Check the current binlog Of lsn Whether the

A scheduled task can be written like this ( Change the path yourself )

* * * * * [[ -d /proc$(cat .mysqlBinlogSYNC.pid) ]] || sh mysqlBinlogSYNC.sh

4. Script

The script is as follows :

#!/bin/env bash
#write by ddcw at 2021.06.29
# Backup to a remote location is not supported , You can run this script on the server you want to save
#mysqlbinlog and mysql The command needs to be configured in the environment variable
REMOTE_HOST="172.17.0.10"
REMOTE_PORT="3311"
REMOTE_USER="repuser"
REMOTE_PASSWORD="repuser"
# Empty words , Will be first LOCAL_PRENAME Find the latest binlog, And then from that Binlog Start syncing .
# If you don't get it under this directory , From the current login query lsn Start
# If specified , From the designated Binlog Start , Do not check whether the name is correct
BEGIN_BINLOG=""
# That is, to preserve binlog The directory prefix for , such as
#/data/bin1 Words , Namely /data/bin1binlog.000002 such , Very ugly , Add... To all the directories / ending , This variable is not formatted , What you set is what you set
#/data/bin1/ In this way, it is /data/bin1/binlog.000002
# If it is empty by default , It's the current directory
LOCAL_PRENAME=""
# Add one PID file , Convenient scheduled tasks , Judge , such as : * * * * * [[ -d /proc$(cat .mysqlBinlogSYNC.pid) ]] || sh mysqlBinlogSYNC.sh
PIDFILE=.mysqlBinlogSYNC.pid
echo $$ > ${PIDFILE}
#run this function and exit with $2
function exits(){
echo -e "[`date +%Y%m%d-%H:%M:%S`] \033[31;40m$1\033[0m"
[ -z $2 ] && exit $2
exit 1
}
function echo_color() {
case $1 in
green)
echo -e "\033[32;40m$2\033[0m"
;;
red)
echo -e "\033[31;40m$2\033[0m"
;;
error|erro|ERROR|E|e)
echo -e "[\033[1;5;41;33mERROR\033[0m `date +%Y%m%d-%H:%M:%S`] \033[1;41;33m$2\033[0m"
;;
redflicker)
echo -e "\033[1;5;41;33m$2\033[0m"
;;
info|INFO|IF|I|i)
echo -e "[\033[32;40mINFO\033[0m `date +%Y%m%d-%H:%M:%S`] \033[32;40m$2\033[0m"
;;
highlightbold)
echo -e "\033[1;41;33m$2\033[0m"
;;
warn|w|W|WARN|warning)
echo -e "[\033[31;40mWARNNING\033[0m `date +%Y%m%d-%H:%M:%S`] \033[31;40m$2\033[0m"
;;
*)
echo "INTERNAL ERROR: echo_color KEY VALUE"
;;
esac
}
LOCAL_CURRENT_BINLOG=${BEGIN_BINLOG}
# Judge ctrl+c
trap 'WhenCtrlC' INT
function WhenCtrlC () {
binlog_file=$(ls ${LOCAL_PRENAME}${MASTER_BINLOG_PRE}* 2>/dev/null | grep -v \.index | sort | tail -1)
lsn=$(mysqlbinlog ${binlog_file} | grep end_log_pos | grep server | grep CRC32 | awk -F end_log_pos '{print $2}' | awk '{print $1}' | tail -1)
echo_color info " This program is kill 了 , Current and current binlog yes :${binlog_file} lsn: ${lsn}"
echo_color info " The next startup will automatically start the synchronization ( If you don't delete the latest binlog, And don't move )"
rm -rf ${PIDFILE} >/dev/null 2>&1
exit 1
}
function init_() {
# Check to see if there is any mysql and mysqlbinlog command
which mysql >/dev/null 2>&1 || exits "no command mysql in env"
which mysqlbinlog >/dev/null 2>&1 || exits "no command mysqlbinlog in env"
# Check whether it can be connected normally
cat /dev/null > /tmp/.${0}_msyqltmpbyddcw.tmp
mysql -s -h ${REMOTE_HOST} -P ${REMOTE_PORT} -u ${REMOTE_USER} -p${REMOTE_PASSWORD} -e "show master status;" 2>/dev/null > /tmp/.${0}_msyqltmpbyddcw.tmp || exits "connect failed or exec 'show master status;' failed"
#echo "/tmp/.${0}_msyqltmpbyddcw.tmp"
export MASTER_CURRENT_BINLOG=$(awk '{print $1}' /tmp/.${0}_msyqltmpbyddcw.tmp)
export MASTER_CURRENT_LSN=$(awk '{print $2}' /tmp/.${0}_msyqltmpbyddcw.tmp)
export MASTER_BINLOG_PRE=$(echo ${MASTER_CURRENT_BINLOG} | awk -F \. '{print $1}')
[[ -z ${LOCAL_CURRENT_BINLOG} ]] && LOCAL_CURRENT_BINLOG=$(ls ${LOCAL_PRENAME}${MASTER_BINLOG_PRE}* 2>/dev/null | grep -v \.index | sort | tail -1)
[[ -z ${LOCAL_CURRENT_BINLOG} ]] && export LOCAL_CURRENT_BINLOG=${MASTER_CURRENT_BINLOG}
[[ -z ${LOCAL_CURRENT_BINLOG} ]] && exits " Please set the enabled binlog, Auto get failed "
[[ -f ${LOCAL_PRENAME}${LOCAL_CURRENT_BINLOG} ]] && export LOCAL_LSN=$(mysqlbinlog ${LOCAL_PRENAME}${LOCAL_CURRENT_BINLOG} | grep end_log_pos | grep server | grep CRC32 | awk -F end_log_pos '{print $2}' | awk '{print $1}' | tail -1)
[[ -z ${LOCAL_LSN} ]] && export LOCAL_LSN=${MASTER_CURRENT_LSN}
}
function main_() {
echo_color info " Start syncing binlog"
echo_color info " Current binlog: ${LOCAL_CURRENT_BINLOG} LSN: ${LOCAL_LSN}"
if [[ -z ${LOCAL_PRENAME} ]];then
while :
do
mysqlbinlog --read-from-remote-server --raw --host=${REMOTE_HOST} --port=${REMOTE_PORT} --user=${REMOTE_USER} --password=${REMOTE_PASSWORD} --stop-never ${LOCAL_CURRENT_BINLOG} 2>/dev/null
echo_color warn "mysqlbinlog stopd with some error, and begin after 10s"
sleep 10
done
else
while :
do
mysqlbinlog --read-from-remote-server --raw --host=${REMOTE_HOST} --port=${REMOTE_PORT} --user=${REMOTE_USER} --password=${REMOTE_PASSWORD} --stop-never ${LOCAL_CURRENT_BINLOG} --result-file=${LOCAL_PRENAME} 2>/dev/null
echo_color warn "mysqlbinlog stopd with some error, and begin after 10s"
sleep 10
done
fi
}
init_
main_
rm -rf ${PIDFILE} >/dev/null 2>&1