MySQL Binlog Remote Backup method detailed _mysql

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

Also, the way in which you previously backed up a remote server with a script has a disadvantage: you cannot back up the binary log files that are currently being written by the MySQL server. So, you can only wait until the MySQL server has finished writing to backup. 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 is not simply a copy of the remote server log, it is a real-time binary event via the replication API published by MySQL 5.6. Essentially, the equivalent of MySQL from the server. Like a normal server, the primary server typically backs up in 0.5-1 seconds after an event occurs.

Backup command

Copy Code code as follows:

Mysqlbinlog--read-from-remote-server--raw--host=192.168.244.145--port=3306--USER=REPL-- Stop-never mysql-bin.000001

Explained as follows:

--read-from-remote-server: Binlog for backing up a remote server. If you do not specify this option, the local binlog is found.

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

--user: Copy the MySQL user, just grant replication slave permissions.

--stop-never:mysqlbinlog can either get the specified number of Binlog from the remote server or save the continuously generated binlog to local. Specifies this option, which means that as long as the remote server does not shut down or the connection is not disconnected, the Mysqlbinlog will continually replicate the Binlog on the remote server.

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

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 essentially corresponds to a 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 be able to obtain the specified Binlog, but also to obtain the resulting binlog, obtained after the end. If the--stop-never option is specified, the--to-last-log option is implicitly opened.

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

Insufficient:

This way there is a problem, for the general master-slave replication, if the master-slave direct connection is disconnected, then automatically reconnect, and for Mysqlbinlog, if disconnected, will not automatically connect.

Solution:

This can be remedied by scripting.

 #!/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 nece Ssary mkdir-p ${local_backup_dir CD ${local_backup_dir} # # Run while loop, wait for a specified time after disconnecting, reconnect while:do if [' Ls-a ' ${local_b Ackup_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 $} ' fi ${backup_bin}--raw--read-from-remote-server--stop-never Emote_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 later connect 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, then reconnect after 10s.

2. The first time the runtime needs to specify the value of First_binlog, from which binlog start copying, generally mysql-bin.000001. Follow up with the latest binlog in the backup directory and start copying from the latest binlog.

Summary:

1. If you specify a--raw,mysqlbinlog to get the event, and will not immediately drop the disk, but first saved in the local server memory, every 4 K brush disk once. This also reduces the frequent log write operations. If the connection between the Mysqlbinlog and the primary server is broken, the Binlog in memory is immediately flushed to disk.

2. Although Mysqlbinlog is similar to a server, the Relaylog from the server is saved in real time, that is, the event generated by the primary server from the server is immediately written to the Relaylog.

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

The above is a small set to introduce the MySQL binlog remote backup tutorial Detailed knowledge of the relevant, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!