How to remotely back up MySQL Binlog

When you previously backed up Binlog, the backup was compressed locally and then sent to the remote server. But there is a certain risk, because the backup of the log is periodic, if in a certain period, the server down, the hard disk is damaged, it may cause this period of time the Binlog lost.

Also, there is a drawback to using scripts to back up remote servers in a way that does not make a backup of the binary log files currently being written by the MySQL server. Therefore, you can only wait until the MySQL server has finished writing to make a backup. And the time to write a binlog is not fixed, which leads to the uncertainty of the backup cycle.

Starting with MySQL5.6, Mysqlbinlog supports the real-time replication of binlog on the remote server to the local server.

Mysqlbinlog's real-time binary replication feature is not simply a copy of the remote server's logs, it is a real-time binary event obtained through the replication API published by MySQL 5.6. Essentially, it's equivalent to MySQL's slave server. Similar to a normal server, after an event occurs in a primary server, it is typically backed up in 0.5-1 seconds.

Backup command

Mysqlbinlog--read-from-remote-server--raw--host=192.168. 244.145 --port=3306 --user=repl--password=repl--stop-never mysql-bin  .  000001

The explanations are as follows:

--read-from-remote-server: Binlog used to back up the remote server. If you do not specify this option, the local binlog is found.

--raw:binlog logs are stored in binary format on disk, and if this option is not specified, it is saved as text.

--user: Copy the MySQL user, only need to grant replication slave permissions.

--stop-never:mysqlbinlog can get only a few specified Binlog from the remote server, or you can save the continuously generated binlog to local. Specify this option, which means that Mysqlbinlog will replicate the Binlog on the remote server as long as the remote server does not shut down or the connection is not fractured.

MYSQL-BIN.000001: Represents the Binlog from which to start copying.

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

--stop-never-slave-server-id: When backing up a remote server's Binlog, Mysqlbinlog is essentially a slave server, which is used to specify the Server-id from the server. The default is-1.

--to-last-log: On behalf of Mysqlbinlog not only to obtain the specified Binlog, but also to obtain the binlog generated thereafter, obtained, before terminating. If the--stop-never option is specified, the--to-last-log option is implicitly turned on.

--result-file: Used to set the binlog of the remote server, save to local prefix. For example, for mysql-bin.000001, if you specify--result-file=/test/backup-, the file that is saved to the local name is/test/backup-mysql-bin.000001. Note: If you set--result-file to a directory, be sure to bring the directory separator "/". For example--result-file=/test/, not--result-file=/test, otherwise the file saved to the local name is/testmysql-bin.000001.

Insufficient:

There is a problem with this approach, for the regular master-slave replication, if the master-slave direct connection is broken, it will automatically reconnect, and for Mysqlbinlog, if disconnected, and will not automatically connect.

Solution:

These deficiencies can be remedied by scripting.

#!/bin/SHBackup_bin=/usr/bin/Mysqlbinloglocal_backup_dir=/backup/binlog/Backup_log=/backup/binlog/Backuplogremote_host=192.168.244.145Remote_port=3306Remote_user=Replremote_pass=Replfirst_binlog=mysql-bin.000001# TimeTowaitbefore reconnecting after Failuresleep_seconds=Ten# #create Local_backup_dirifnecessarymkdir-P ${LOCAL_BACKUP_DIR}CD ${local_backup_dir}## run while loop, wait for a specified time after the connection is disconnected, reconnect while : Do  if[ `ls-A"${local_backup_dir}"|WC-L '-eq0]; ThenLast_file=${first_binlog}ElseLast_file=`ls-L ${local_backup_dir} |grep-V Backuplog |Tail-N1|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} seconds after connecting again and continue backup"|Tee-a ${backup_log}Sleep${sleep_seconds} Done

Script Interpretation:

1. A dead loop is actually defined, and if the backup fails, it is reconnected after 10s.

2. The first run is required to specify the value of First_binlog, which binlog to start copying, generally mysql-bin.000001. Subsequent execution of the time directly to get the backup directory under the latest Binlog, from the latest binlog start copying.

Summarize:

1. If the--raw,mysqlbinlog acquisition event is specified, the disk is not dropped in real time, but is saved in memory on the local server, once per 4 K brush disk. This also reduces the frequency of log write operations. If the connection between Mysqlbinlog and the primary server is lost, the in-memory Binlog is immediately flushed to disk.

2. Although Mysqlbinlog is similar to the slave server, the Relaylog from the server is saved in real time, that is, the event generated by the primary server is fetched from the server, and is written to relaylog in real time.

3. If you do not specify--raw, this time will be saved in text format, at this time,--result-file=/test/can not be specified as a directory, you must explicitly write the file name, such as--result-file=/test/1.sql, at this time, Mysqlbinlog get the event, is real-time landing, not every 4 K brush disk once.

How to remotely back up MySQL Binlog