mysql5.7 backup for difference

mysql_backup_difference.sh

#!/bin/bash
BACKUP_LAST=backup_last
BACKUP_CUR=backup_cur
BACKUP_SNAP=backup.sql.gz
BINLOG=binlog-$(date +%Y%m%d-%H%M%S-%N).sql.gz
BINLOGS=binlogs.txt
CONNECT_OPT=" -u root -h docker-db-pc -pexample "

get_binlog_list() {
	echo "show binary logs;" | mysql ${CONNECT_OPT} | cut -f 1 | tail -n +2
}

get_db_list() {
	echo "show databases;" | mysql ${CONNECT_OPT} | cut -f 1 | tail -n +2 | sed -e '/^information_schema$/d;/^sys$/d;/^mysql$/d;/^performance_schema$/d'
}

get_point() {
	grep -e "^\(-- \)\?CHANGE MASTER" | sed -e "s/\(-- \)\?CHANGE MASTER TO MASTER_LOG_FILE='\([^']\+\)', MASTER_LOG_POS=\([0-9]\+\).*/\2\t\3/"
}

get_backup() {
	mysqldump ${CONNECT_OPT} --compress --single-transaction --master-data=2 --databases $(get_db_list) | gzip -c > ${BACKUP_SNAP}
	gunzip -c ${BACKUP_SNAP} | head -n 30 | get_point
}

get_pos() {
	mysqldump ${CONNECT_OPT} --single-transaction --master-data --no-create-info --no-data mysql | get_point
}

execute_with_log() {
	echo -- $*
	$*
}


if [ ! -e ${BACKUP_LAST} ]
then
	if [ -e ${BINLOGS} ]
	then
		rm $(cat ${BINLOGS})
		rm ${BINLOGS}
	fi
	#バックアップ
	get_backup > ${BACKUP_LAST}
else
	if [ ! -e ${BACKUP_CUR} ]
	then
		get_pos > ${BACKUP_CUR}
	fi

	read binlog_file_last binlog_pos_last <<< $(cat ${BACKUP_LAST})
	read binlog_file_cur binlog_pos_cur <<< $(cat ${BACKUP_CUR}) 
	readstate=init
	binlogs=
	get_binlog_list | ( while [ ${readstate} != "done" ]
	do
		read fname
		if [ $? -ne 0 ]
		then
			break
		fi

		if [ ${readstate} == init -a ${fname} == ${binlog_file_last} ]
		then
			readstate=read
		fi

		if [ ${readstate} == read ]
		then
			scp -C myuser@docker-db-pc:/var/lib/mysql/${fname} .
			binlogs="${binlogs} ${fname}"
			if [ ${fname} == ${binlog_file_cur} ]
			then
				readstate=done
				break
			fi
		fi

	done
	if [ ${readstate} != done ]
	then
		echo binlogが見つかりません。 \(${binlog_file_last}:${binlog_pos_last} 〜 ${binlog_file_cur}:${binlog_pos_cur}\)
		exit 1
	fi

	execute_with_log mysqlbinlog --start-position=${binlog_pos_last} --stop-position=${binlog_pos_cur} ${binlogs} | gzip -c > ${BINLOG}
	if [ $? != 0 ]
	then
		echo binlogの変換に失敗しました。
		exit 1
	fi
	echo ${BINLOG} >> ${BINLOGS}

	rm ${binlogs}
	mv -f ${BACKUP_CUR} ${BACKUP_LAST}
	)
fi