MySQL binlog remote backup method details

In previous backups of binlog, the backup was compressed locally and then sent to a remote server. However, there is a certain risk, because log backups are always periodic, and if during a certain cycle, the server goes down and the hard disk is damaged, the binlog may be lost during this period.

Also, the previous script backup of the remote server had a disadvantage: it was impossible to back up the binary log files that the MySQL server was currently writing. Therefore, you can only backup until the MySQL server is fully written. The time to write an binlog is not fixed, which leads to the uncertainty of the backup cycle.

Starting with MySQL5.6, mysqlbinlog supports real-time replication of binlog from a remote server to a local server.

The real-time binary replication feature of mysqlbinlog does not simply copy logs from a remote server. It fetches binary events in real time via Replication API published in MySQL 5.6. In essence, this is the slave server of MySQL. Similar to the ordinary server, after the event of the main server, 1 will usually be backed up within 0.5~1 seconds.

The backup command

mysqlbinlog --read-from-remote-server --raw --host=192.168.244.145 --port=3306 --user=repl --password=repl --stop-never mysql-bin.000001

Here's the explanation:

--read-from-remote-server: binlog for backup of remote server. If you do not specify this option, the local ES34en is looked up.

--raw: The binlog log is stored on disk in binary format or as text if you do not specify this option.

--user: Replicated MySQL users only need to grant REPLICATION SLAVE privileges.

--stop-never: mysqlbinlog can fetch only specified binlog from a remote server, or it can save continuously generated binlog locally. Specifying this option means that mysqlbinlog will continue to replicate binlog on the remote server as long as the remote server is not closed or the connection is not broken.

mysql-bin.000001: Represents from which binlog is copied.

In addition to the above options, there are several other options to note:

--stop-never-slave-server-id: When backing up binlog on a remote server, mysqlbinlog is essentially a slave. This option is used to specify server-id on the slave. Default is -1.

--to-last-log: Represents that mysqlbinlog can obtain not only the specified binlog, but also the generated binlog. The -- stop-never option is implicitly turned on if the -- to-last-log option is specified.

--result-file: binlog used to set up the remote server, save to local prefix. For example, for mysql-bin.000001, if you specify --result-file=/test/backup-, the file will be saved locally as /test/backup-mysql-bin.000001. Note: If you set -- result-file as a directory, 1 must have the directory delimiter "/". For example -- result-file =/test/, instead of -- result-file =/test, the locally saved file would be/testmysql-bin.000001.

Inadequate:

The problem with this approach is that for regular master-slave replication, if the direct master-slave connection is broken, the slave will automatically reconnect, whereas for mysqlbinlog, if the direct connection is broken, the slave will not automatically reconnect.

Solution:

Scripts can be used to make up for the above deficiencies.

#!/bin/sh
BACKUP_BIN=/usr/bin/mysqlbinlog
LOCAL_BACKUP_DIR=/backup/binlog/
BACKUP_LOG=/backup/binlog/backuplog
REMOTE_HOST=192.168.244.145
REMOTE_PORT=3306
REMOTE_USER=repl
REMOTE_PASS=repl
FIRST_BINLOG=mysql-bin.000001
#time to wait before reconnecting after failure
SLEEP_SECONDS=10
##create local_backup_dir if necessary
mkdir -p ${LOCAL_BACKUP_DIR}
cd ${LOCAL_BACKUP_DIR}
##  run while Loop, wait for the specified time after the connection is disconnected, and reconnect 
while :
do
 if [ `ls -A "${LOCAL_BACKUP_DIR}" |wc -l` -eq 0 ];then
 LAST_FILE=${FIRST_BINLOG}
 else
 LAST_FILE=`ls -l ${LOCAL_BACKUP_DIR} | grep -v backuplog |tail -n 1 |awk '{print $9}'`
 fi
 ${BACKUP_BIN} --raw --read-from-remote-server --stop-never --host=${REMOTE_HOST} --port=${REMOTE_PORT} --user=${REMOTE_USER} --password=${REMOTE_PASS} ${LAST_FILE}
 echo "`date +"%Y/%m/%d %H:%M:%S"` mysqlbinlog Stop, return code: $?" | tee -a ${BACKUP_LOG}
 echo "${SLEEP_SECONDS} Connect again after a second and continue the backup " | tee -a ${BACKUP_LOG} 
 sleep ${SLEEP_SECONDS}
done

Script interpretation:

1. In effect, an infinite loop is defined. If the backup fails, reconnect after 10s.

FIRST_BINLOG: mysql-bin.000001: binlog:

_BINLOG: binlog: FIRST_BINLOG: binlog: mysql-bin.000001 For subsequent execution, get the latest binlog in the backup directory directly, and start copying from the latest binlog.

Conclusion:

1. If --raw is specified, mysqlbinlog will not fetch the event in real time, but will first save in the local server memory, every 4K once. This reduces the frequency of log writes. If the connection between mysqlbinlog and the primary server is broken at this point, binlog in memory will be flushed to disk immediately.

2. Although mysqlbinlog is similar to a slave server, relaylog on a slave server is saved in real time, that is, events generated by the master server are fetched from the server and written to relaylog in real time.

-- result-file =/test/ -- result-file =/test/ 1.sql -- result-file =/test/ 1.sql -- mysqlbinlog =/test/ sql -- result-file =/test/1.