如何遠程備份MySQL binlog

如何遠程備份MySQL binlog

以前備份binlog時，都是先在本地進行備份壓縮，然後發送到遠程伺服器中。但是這其中還是有一定風險的，因為日誌的備份都是周期性的，如果在某個周期中，伺服器宕機了，硬碟損壞了，就可能導致這段時間的binlog就丟失了。

而且，以前用指令碼對遠程伺服器進行備份的方式，有個缺點：無法對MySQL伺服器當前正在寫的二進位記錄檔進行備份。所以，只能等到MySQL伺服器全部寫完才能進行備份。而寫完一個binlog的時間並不固定，這就導致備份周期的不確定。

從MySQL5.6開始，mysqlbinlog支援將遠程伺服器上的binlog即時複製到本機伺服器上。

mysqlbinlog的即時二進位複製功能並非簡單的將遠程伺服器的日誌複製過來，它是通過MySQL 5.6公布的Replication API即時擷取二進位事件。本質上，就相當於MySQL的從伺服器。與普通伺服器類似，主伺服器發生事件後，一般都會在0.5~1秒內進行備份。

備份命令

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

解釋如下：

--read-from-remote-server：用於備份遠程伺服器的binlog。如果不指定該選項，則會尋找本地的binlog。

--raw：binlog日誌會以二進位格式儲存在磁碟中，如果不指定該選項，則會以文本形式儲存。

--user：複製的MySQL使用者，只需要授予REPLICATION SLAVE許可權。

--stop-never：mysqlbinlog可以只從遠程伺服器擷取指定的幾個binlog，也可將不斷產生的binlog儲存到本地。指定此選項，代表只要遠程伺服器不關閉或者串連未斷開，mysqlbinlog就會不斷的複製遠程伺服器上的binlog。

mysql-bin.000001：代表從哪個binlog開始複製。

除了以上選項外，還有以下幾個選項需要注意：

--stop-never-slave-server-id：在備份遠程伺服器的binlog時，mysqlbinlog本質上就相當於一個從伺服器，該選項就是用來指定從伺服器的server-id的。預設為-1。

--to-last-log：代表mysqlbinlog不僅能夠擷取指定的binlog，還能擷取其後產生的binlog，擷取完了，才終止。如果指定了--stop-never選項則會隱式開啟--to-last-log選項。

--result-file：用於設定遠程伺服器的binlog，儲存到本地的首碼。譬如對於mysql-bin.000001，如果指定--result-file=/test/backup-，則儲存到本地後的檔案名稱為/test/backup-mysql-bin.000001。注意：如果將--result-file設定為目錄，則一定要帶上目錄分隔字元“/”。譬如--result-file=/test/，而不是--result-file=/test，不然儲存到本地的檔案名稱為/testmysql-bin.000001。

不足：

這個方式有個問題，對於常規的主從複製來說，如果主從直接的串連斷開了，則從會自動再次串連，而對於mysqlbinlog，如果斷開了，並不會自動連接。

解決方案：

可通過指令碼來彌補上述不足。

#!/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}

## 運行while迴圈，串連斷開後等待指定時間，重新串連
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停止，傳回碼：$?" | tee -a ${BACKUP_LOG}
  echo "${SLEEP_SECONDS}秒後再次串連並繼續備份" | tee -a ${BACKUP_LOG} 
  sleep ${SLEEP_SECONDS}
done

指令碼解讀：

1. 實際上定義了一個死迴圈，如果備份失敗，則10s後重新串連。

2. 第一次運行時需指定FIRST_BINLOG的值，指從哪個binlog開始複製，一般為mysql-bin.000001。後續執行的時候就直接擷取備份目錄下最新的binlog，從最新的binlog開始複製。

總結：

1. 如果指定了--raw，mysqlbinlog擷取事件後，並不會即時落盤，而是先儲存在本機伺服器的記憶體中，每4K刷盤一次。這也就減少了頻繁的日誌寫操作。如果此時mysqlbinlog和主伺服器之間的串連斷開了，則記憶體中的binlog會馬上重新整理到磁碟中。

2. 儘管mysqlbinlog類似於從伺服器，但從伺服器上的relaylog卻是即時存檔的，即從伺服器擷取主伺服器產生的事件後，會即時寫入到relaylog中。

3. 如果不指定--raw，這個時候會以文字格式設定存檔，此時，--result-file=/test/不能指定為目錄，必須明確寫上檔案名稱，譬如--result-file=/test/1.sql，此時，mysqlbinlog擷取事件後，是即時落盤的，不會每4K刷盤一次。

本文永久更新連結地址：