MySQL binlog remote backup method details, mysqlbinlog

MySQL binlog remote backup method details, mysqlbinlog

Previously, binlogs were backed up and compressed locally, and then sent to a remote server. However, there are still some risks, because log backup is cyclical. If the server goes down and the hard disk is damaged in a certain cycle, this may cause the binlog to be lost during this period of time.

In addition, in the past, the remote server was backed up by a script, but the binary log file currently being written by the MySQL server could not be backed up. Therefore, the backup can only be performed after the MySQL server is fully written. The time for writing a binlog is not fixed, which leads to uncertainty in the backup cycle.

From MySQL5.6, mysqlbinlog allows you to copy the binlog on a remote server to a local server in real time.

The Real-Time Binary Replication function of mysqlbinlog does not simply copy logs from remote servers. It acquires binary events in real time through the Replication API published in MySQL 5.6. Essentially, it is equivalent to the slave server of MySQL. Similar to a common server, when an event occurs on the primary server, it is usually between 0.5 and ~ Back up data within 1 second.

Backup command

Copy codeThe Code is as follows:
Mysqlbinlog -- read-from-remote-server -- raw -- host = 192.168.244.145 -- port = 3306 -- user = repl -- password = repl -- stop-never mysql-bin.000001

Explanation:

-- Read-from-remote-server: used to back up the binlog of the remote server. If this option is not specified, the local binlog will be searched.

-- Raw: binlog logs are stored in the disk in binary format. If this option is not specified, the logs are saved in text format.

-- User: the copied MySQL user. You only need to grant the replication slave permission.

-- Stop-never: mysqlbinlog can retrieve only a few specified binlogs from the remote server, or save continuously generated binlogs to the local machine. If this option is specified, mysqlbinlog continuously copies the binlog on the remote server as long as the remote server is not closed or the connection is not disconnected.

Mysql-bin.000001: indicates the binlog from which the replication starts.

In addition to the preceding options, pay attention to the following options:

-- Stop-never-slave-server-id: When backing up the binlog of a remote server, mysqlbinlog is essentially equivalent to a slave server. This option is used to specify the server-id of the slave server. The default value is-1.

-- To-last-log: indicates that mysqlbinlog can not only obtain the specified binlog, but also obtain the generated binlog. It is terminated after it is obtained. If the -- stop-never option is specified, the -- to-last-log option is opened implicitly.

-- Result-file: used to set the binlog of the remote server and save it to the local prefix. For example, if -- result-file =/test/backup-is specified for a mysql-bin.000001, the file name saved locally is/test/backup-mysql-bin.000001. Note: If you set -- result-file as a directory, you must include the directory separator "/". For example -- result-file =/test/, instead of -- result-file =/test, otherwise the file name saved locally is/testmysql-bin.000001.

Disadvantages:

This method has a problem. For general master-slave replication, if the master-slave direct connection is disconnected, it will automatically connect again. For mysqlbinlog, if it is disconnected, it does not automatically connect.

Solution:

You can use scripts to make up for the above shortcomings.

#! /Bin/shBACKUP_BIN =/usr/bin/logs =/backup/binlog/BACKUP_LOG =/backup/binlog/logs = 192.168.244.145REMOTE _ PORT = 3306REMOTE_USER = replREMOTE_PASS = replFIRST_BINLOG = mysql-bin.000001 # time wait before reconnecting after failureSLEEP_SECONDS = 10 # create local_backup_dir if necessarymkdir-p $ {LOCAL_BACKUP_DIR} cd $ {LOCAL_BACKUP_DIR} # Run the while LOOP, after the connection is disconnected, wait for the specified time to 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 stopped, return code: $? "| Tee-a $ {BACKUP_LOG} echo" $ {SLEEP_SECONDS} seconds later, connect again and continue the backup "| tee-a $ {BACKUP_LOG} sleep $ {SLEEP_SECONDS} done

Script explanation:

1. an infinite loop is actually defined. If the backup fails, it will be reconnected after 10 s.

2. The value of FIRST_BINLOG must be specified during the first run, which indicates the binlog from which the replication starts, generally the mysql-bin.000001. During subsequent execution, the latest binlog under the backup directory is obtained and copied from the latest binlog.

Summary:

1. If -- raw is specified, after mysqlbinlog obtains the event, it will not be stored in the disk in real time. Instead, it will be saved in the memory of the local server and flushed every 4 K. This reduces frequent log write operations. If the connection between mysqlbinlog and the master server is disconnected, the binlog in the memory will be immediately refreshed to the disk.

2. Although mysqlbinlog is similar to the slave server, the relaylog on the slave server is stored in real time, that is, after the master server obtains the events generated by the master server, it is written to the relaylog in real time.

3. if -- raw is not specified, the disk is saved in text format. At this time, -- result-file =/test/cannot be specified as a directory, and the file name must be specified, for example, -- result-file =/test/1. SQL. at this time, after mysqlbinlog obtains the event, the disk is stored in real time and will not be flushed every 4 K.

The above is a detailed description of the MySQL binlog remote backup tutorial. I hope it will be helpful to you. If you have any questions, please leave a message for me, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!