遠端備份MySQL二進位制日誌--read-from-remote-server
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;
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| 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';
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| 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;
+-----------------+-----+------------+-----------+-------------+----------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+------------+-----------+-------------+----------------------------------------------------+
| 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寶典今日頭條號地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● 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 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麥苗出版的資料庫類叢書： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客戶端掃描下面的二維碼來關注小麥苗的微 信公眾號（ xiaomaimiaolhr）及QQ群（DBA寶典）、新增小麥苗微 信， 學習最實用的資料庫技術。
........................................................................................................................ |
 |
|
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2673792/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL二進位制日誌MySql
- mysql 二進位制日誌MySql
- mysql 日誌之二進位制日誌MySql
- mysqldump全量備份+mysqlbinlog二進位制日誌增量備份MySql
- 管理mysql二進位制日誌MySql
- mysql的二進位制日誌MySql
- MySQL 壓縮二進位制日誌MySql
- mysql二進位制日誌詳解MySql
- 【Mysql】遠端備份binlog日誌到本地MySql
- mysql二進位制日誌是什麼MySql
- mysql二進位制日誌格式介紹MySql
- MySQL如何傳輸二進位制日誌MySql
- 使用canal偷取MySQL的二進位制日誌MySql
- mysql二進位制日誌相關引數MySql
- mysqlbinlog命令詳解 Part 7 備份二進位制日誌檔案MySql
- mysql二進位制日誌的引數介紹MySql
- Mysql資料庫二進位制日誌的管理MySql資料庫
- MySQL二進位制日誌刪除與恢復MySql
- 如何在MySQL中檢視binlog二進位制日誌？MySql
- MySQL什麼時候輪換二進位制日誌MySql
- 二進位制備份還原案例
- mysql之 日誌體系（錯誤日誌、查詢日誌、二進位制日誌、事務日誌、中繼日誌）MySql中繼
- mysql關於二進位制日誌binary log的總結MySql
- mysql5.7無法開啟二進位制日誌問題MySql
- mysqlbinlog二進位制日誌檢視工具MySql
- MySQL二進位制日誌的三種格式優缺點比較MySql
- 【MySQL解惑筆記】Mysql5.7.x無法開啟二進位制日誌MySql筆記
- 使用Xtrabackup遠端備份MysqlMySql
- Web 前端開發日誌（二）：JavaScript 的二進位制操作Web前端JavaScript
- MySQL遠端備份策略舉例MySql
- mysql關於ib_logfile事務日誌和binary log二進位制日誌的區別MySql
- mysqlbinlog 處理二進位制日誌檔案的工具MySql
- MySQL建立二進位制日誌產生1067錯誤的解決方案MySql
- 解析MYSQL BINLOG 二進位制格式(1)--準備工作MySql
- 使用dd命令進行遠端備份
- MySQL日誌管理，備份和恢復MySql
- 實時備份mysql binlog日誌MySql
- Backup And Recovery User's Guide-RMAN備份概念-備份集二進位制壓縮GUIIDE