1、mysqldump+binlog增量恢復
create database testdump;
use testdump;
CREATE TABLE mytime(
  id  INT AUTO_INCREMENT PRIMARY KEY,
  now DATETIME
);
insert into mytime(now) values(now());
insert into mytime(now) values(now());
insert into mytime(now) values(now());
insert into mytime(now) values(now());
insert into mytime(now) values(now());
(root@localhost) [testdump]> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| rhel6lhr-bin.000013 |     1808 |              |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
mysqldump -uroot -p testdump > db_testdump.sql
source db_testdump.sql
insert into mytime(now) values(now());
insert into mytime(now) values(now());
mysqlbinlog -uroot -plhr -h192.168.59.130 -P3313 --database=testdump -vv --base64-output=decode-rows --start-position=1808  --read-from-remote-server rhel6lhr-bin.000013 rhel6lhr-bin.000014 > binlog_increment_001.sql
mysqlbinlog -S/usr/local/mysql56/mysql5637/data56373313/mysql56373313.sock --database=testdump -vv --base64-output=decode-rows --start-position=1808 /usr/local/mysql56/mysql5637/data56373313/rhel6lhr-bin.000013 /usr/local/mysql56/mysql5637/data56373313/rhel6lhr-bin.000014 > binlog_increment_001.sql
mysqlbinlog -uroot -plhr -h192.168.59.130 -P3313 --database=testdump -vv --base64-output=decode-rows --start-position=120  --read-from-remote-server rhel6lhr-bin.000015 > binlog_increment_002.sql
mysql -uroot -plhr -h192.168.59.130 -P3312 --database=testdump
source binlog_increment_001.sql

以前備份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/shBACKUP_BIN=/usr/bin/mysqlbinlog
LOCAL_BACKUP_DIR=/backup/binlog/BACKUP_LOG=/backup/binlog/backuplog
REMOTE_HOST=192.168.244.145REMOTE_PORT=3306REMOTE_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 necessarymkdir -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刷盤一次。

使用mysqlbinlog提取二進位制日誌

原文連結：

https://blog.csdn.net/leshami/article/details/41962243

1、提取mysqlbinlog的幾種方式

a、使用show binlog events方式可以獲取當前以及指定binlog的日誌，不適宜提取大量日誌。

b、使用mysqlbinlog命令列提取(適宜批次提取日誌)。

2、演示show binlog events方式

mysql> show variables like 'version';

+---------------+------------+

| Variable_name | Value |

+---------------+------------+

| version | 5.6.12-log |

+---------------+------------+

mysql> show binary logs;

+-----------------+-----------+

| Log_name | File_size |

+-----------------+-----------+

| APP01bin.000001 | 120 |

+-----------------+-----------+

a、只檢視第一個binlog檔案的內容(show binlog events)

mysql> use replication;

Database changed

mysql> select * from tb;

+------+-------+

| id | val |

+------+-------+

| 1 | robin |

+------+-------+

mysql> insert into tb values(2,'jack');

Query OK, 1 row affected (0.02 sec)

mysql> flush logs;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb values(3,'fred');

Query OK, 1 row affected (0.00 sec)

mysql> show binary logs;

+-----------------+-----------+

| Log_name | File_size |

+-----------------+-----------+

| APP01bin.000001 | 409 |

| APP01bin.000002 | 363 |

+-----------------+-----------+

mysql> show binlog events;

+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+

+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+

| APP01bin.000001 | 4 | Format_desc | 11 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 |

| APP01bin.000001 | 120 | Query | 11 | 213 | BEGIN |

| APP01bin.000001 | 213 | Query | 11 | 332 | use `replication`; insert into tb values(2,'jack') |

| APP01bin.000001 | 332 | Xid | 11 | 363 | COMMIT /* xid=382 */ |

| APP01bin.000001 | 363 | Rotate | 11 | 409 | APP01bin.000002;pos=4 |

+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+

-- 在上面的結果中第3行可以看到我們執行的SQL語句，第4行為自動提交

-- Author : Leshami

-- Blog : http://blog.csdn.net/leshami

b、檢視指定binlog檔案的內容(show binlog events in 'binname.xxxxx')

mysql> show binlog events in 'APP01bin.000002';

+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+

+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+

| APP01bin.000002 | 4 | Format_desc | 11 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 |

| APP01bin.000002 | 120 | Query | 11 | 213 | BEGIN |

| APP01bin.000002 | 213 | Query | 11 | 332 | use `replication`; insert into tb values(3,'fred') |

| APP01bin.000002 | 332 | Xid | 11 | 363 | COMMIT /* xid=394 */ |

+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+

c、檢視當前正在寫入的binlog檔案(show master status\G)

mysql> show master status\G

*************************** 1. row ***************************

File: APP01bin.000002

Position: 363

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

d、獲取指定位置binlog的內容(show binlog events from)

mysql> show binlog events from 213;

+-----------------+-----+------------+-----------+-------------+----------------------------------------------------+

+-----------------+-----+------------+-----------+-------------+----------------------------------------------------+

| APP01bin.000001 | 213 | Query | 11 | 332 | use `replication`; insert into tb values(2,'jack') |

| APP01bin.000001 | 332 | Xid | 11 | 363 | COMMIT /* xid=382 */ |

| APP01bin.000001 | 363 | Rotate | 11 | 409 | APP01bin.000002;pos=4 |

+-----------------+-----+------------+-----------+-------------+----------------------------------------------------+

3、演示mysqlbinlog方式提取binlog

a、提取指定的binlog日誌

# mysqlbinlog /opt/data/APP01bin.000001

# mysqlbinlog /opt/data/APP01bin.000001|grep insert

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

insert into tb values(2,'jack')

b、提取指定position位置的binlog日誌

# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001

c、提取指定position位置的binlog日誌並輸出到壓縮檔案

# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 |gzip >extra_01.sql.gz

d、提取指定position位置的binlog日誌匯入資料庫

# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 | mysql -uroot -p

e、提取指定開始時間的binlog並輸出到日誌檔案

# mysqlbinlog --start-datetime="2014-12-15 20:15:23" /opt/data/APP01bin.000002 --result-file=extra02.sql

f、提取指定位置的多個binlog日誌檔案

# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 /opt/data/APP01bin.000002|more

g、提取指定資料庫binlog並轉換字符集到UTF8

# mysqlbinlog --database=test --set-charset=utf8 /opt/data/APP01bin.000001 /opt/data/APP01bin.000002 >test.sql

h、遠端提取日誌，指定結束時間

# mysqlbinlog -urobin -p -h192.168.1.116 -P3306 --stop-datetime="2014-12-15 20:30:23" --read-from-remote-server mysql-bin.000033 |more

i、遠端提取使用row格式的binlog日誌並輸出到本地檔案

# mysqlbinlog -urobin -p -P3606 -h192.168.1.177 --read-from-remote-server -vv inst3606bin.000005 >row.sql

4、獲取mysqlbinlog的幫助資訊(僅列出常用選項)

-?, --help

顯示幫助訊息並退出。

-d, --database=name

只列出該資料庫的條目(只適用本地日誌)。

-f, --force-read

使用該選項，如果mysqlbinlog讀它不能識別的二進位制日誌事件，它會列印警告，忽略該事件並繼續。沒有該選項，如果mysqlbinlog讀到此類事件則停止。

-h, --host=name

獲取給定主機上的MySQL伺服器的二進位制日誌。

-l, --local-load=name

為指定目錄中的LOAD DATA INFILE預處理本地臨時檔案。

-o, --offset=#

跳過前N個條目。

-p, --password[=name]

當連線伺服器時使用的密碼。如果使用短選項形式(-p)，選項和密碼之間不能有空格。

如果在命令列中–password或-p選項後面沒有密碼值，則提示輸入一個密碼。

-P, --port=#

用於連線遠端伺服器的TCP/IP埠號。

--protocol=name

使用的連線協議。

-R, --read-from-remote-server|--read-from-remote-master=name

從MySQL伺服器讀二進位制日誌。如果未給出該選項，任何連線引數選項將被忽略，即連線到本地。

這些選項是–host、–password、–port、–protocol、–socket和–user。

-r, --result-file=name

將輸出指向給定的檔案。

-s, --short-form

只顯示日誌中包含的語句，不顯示其它資訊，該方式可以縮小生成sql檔案的尺寸。

-S, --socket=name

用於連線的套接字檔案。

--start-datetime=name

從二進位制日誌中讀取等於或晚於datetime參量的事件，datetime值相對於執行mysqlbinlog的機器上的本地時區。

該值格式應符合DATETIME或TIMESTAMP資料型別。例如：2004-12-25 11:25:56 ，建議使用引號標識。

--stop-datetime=name

從二進位制日誌中讀取小於或等於datetime的所有日誌事件。關於datetime值的描述參見--start-datetime選項。

-j, --start-position=#

從二進位制日誌中第1個位置等於N參量時的事件開始讀。

--stop-position=#

從二進位制日誌中第1個位置等於和大於N參量時的事件起停止讀。

--server-id=#

僅僅提取指定server_id的binlog日誌

--set-charset=name

新增SET NAMES character_set到輸出

-t, --to-last-log

在MySQL伺服器中請求的二進位制日誌的結尾處不停止，而是繼續列印直到最後一個二進位制日誌的結尾。

如果將輸出傳送給同一臺MySQL伺服器，會導致無限迴圈。該選項要求–read-from-remote-server。

-D, --disable-log-bin

禁用二進位制日誌。如果使用–to-last-logs選項將輸出傳送給同一臺MySQL伺服器，可以避免無限迴圈。

該選項在崩潰恢復時也很有用，可以避免複製已經記錄的語句。註釋：該選項要求有SUPER許可權。

-u, --user=name

連線遠端伺服器時使用的MySQL使用者名稱。

-v, --verbose

用於輸出基於row模式的binlog日誌，-vv為列資料型別新增註釋

-V, --version

顯示版本資訊並退出。

5、小結

a、可以透過show binlog events以及mysqlbinlog方式來提取binlog日誌。

b、show binlog events 引數有限不適宜批次提取，mysqlbinlog可用於批次提取來建立恢復資料庫的SQL。

c、mysqlbinlog可以基於時間點，position等方式實現不完全恢復或時點恢復。

d、mysqlbinlog可以從支援本地或遠端方式提取binlog日誌。

e、mysqlbinlog可以基於server_id，以及基於資料庫級別提取日誌，不支援表級別。

About Me

........................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在itpub、部落格園、CSDN和個人微 信公眾號（
xiaomaimiaolhr）上有同步更新

● 本文itpub地址：
http://blog.itpub.net/26736162 

● 本文部落格園地址：
http://www.cnblogs.com/lhrbest 

● 本文CSDN地址：
https://blog.csdn.net/lihuarongaini 

● 本文pdf版、個人簡介及小麥苗雲盤地址：
http://blog.itpub.net/26736162/viewspace-1624453/ 

● 資料庫筆試面試題庫及解答：
http://blog.itpub.net/26736162/viewspace-2134706/ 

● DBA寶典今日頭條號地址：
 

........................................................................................................................

● QQ群號：
230161599
、618766405

● 微 信群：可加我微 信，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友
（
646634621
），註明新增緣由

● 於 2020-01-01 06:00 ~ 2020-01-31 24:00 在西安完成

● 最新修改時間：2020-01-01 06:00 ~ 2020-01-31 24:00

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

........................................................................................................................

● 
小麥苗的微店：

● 
小麥苗出版的資料庫類叢書：
http://blog.itpub.net/26736162/viewspace-2142121/

● 
小麥苗OCP、OCM、高可用網路班：
http://blog.itpub.net/26736162/viewspace-2148098/

● 
小麥苗騰訊課堂主頁：
https://lhr.ke.qq.com/

........................................................................................................................
使用

微 信客戶端掃描下面的二維碼來關注小麥苗的微 信公眾號（
xiaomaimiaolhr）及QQ群（DBA寶典）、新增小麥苗微 信，
學習最實用的資料庫技術。 


........................................................................................................................

遠端備份MySQL二進位制日誌--read-from-remote-server

相關文章