mysql備份還原-基于binlog的增量備份還原

來源:本站原創 mysql_mariadb 超過257 views圍觀 0條評論

 

啟用binlog

vi my.cnf

log-bin=/var/lib/mysql/mysql-bin.log,如果是這樣的話log-bin=mysql-bin.log默認在datadir目錄下面

[[email protected] mysql]# ls |grep mysql-bin
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
mysql-bin.000006
mysql-bin.index

 

查看mysql-bin.000002這樣的文件里面到底是什么東西

[[email protected] mysql]# mysqlbinlog   /var/lib/mysql/mysql-bin.000002 > /tmp/add.sql

binlog增量備份和增量還原

1,增量備份

既然我們知道了,mysql里面新增加的數據在mysql-bin這樣的文件里面,我們只要把mysql-bin這樣的文件進行備份就可以了。

cp /var/lib/mysql/mysql-bin* /data/mysql_newbak/

備份命令

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。

 

 

 

 

我們現在要結合Binlog來恢復,但前提要找出誤操作前的pos點

 

通過事件的位置來恢復(不完全恢復)

 

  1. [[email protected] ~]# mysqlbinlog -v –base64-output=DECODE-ROWS localhost-bin.000002 |grep -C 10 -i "drop database"

  2. ### INSERT INTO `xuanzhi`.`tb1`

  3. ### SET

  4. ### @1=5

  5. ### @2=’ee’

  6. # at 290

  7. #170327 21:10:55 server id 1313306 end_log_pos 321 CRC32 0x825a2f99 Xid = 78

  8. COMMIT/*!*/;

  9. # at 321  <–開始

  10. #170327 21:19:25 server id 1313306 end_log_pos 422   <–結束點 CRC32 0x8c139cac Query thread_id=2 exec_time=0 error_code=0

  11. SET TIMESTAMP=1490620765/*!*/;

  12. drop database xuanzhi

 

上面的黃色加粗的就是,一個是start-position,一個是stop-position

 

從上面可以看到,誤操作前的pos點是321,那我們現在通過binlog來進行數據恢復:

  1. [[email protected] mysql-5.6]# mysqlbinlog –start-position=329 –stop-position=321 localhost-bin.000001 localhost-bin.000002 |mysql -uroot -p123456 xuanzhi

–start-position是備份后記錄下的pos點,

–stop-position是誤操前的pos點,

如果多個binlog文件,那么start-position是第一個binlog文件的pos點,stop-position是最后一個binlog的pos點

 

通過事件的時間來恢復(不完全恢復)

我們可以通過參數–start-datetime–stop-datetime指定恢復binlog日志的起止時間點,時間使用DATETIME格式。


    比如在時間點2005-04-20 10:00:00我們刪除掉一個庫,我們要恢復該時間點前的所有日志


[[email protected] /]# mysqlbinlog –stop-datetime="2005-04-20 9:59:59" /usr/local/mysql/data/binlog.123456 | mysql -u root

    我們可能幾個小時后才發現該錯誤,后面又有一系列的增刪查改等操作,我們還需要恢復后續的binlog,我們可以指定起始時間

 

組合

和基于時間點恢復類是,但是更加精確.因為同一時間點可能有多條SQL語句執行;

例:

#mysqlbinlog –start-date="2010-10-31 9:55:00"  –stop-date="2010-10-31 10:05:00" /usr/local/mysql/var/mysql-bin.000013 > /tmp/mysql_restore.sql

該命令將在/tmp/目錄下創建小的文件,編輯它找到錯誤語句前后的位置號,例如前后位置號分別是368312 和 368315

(2)恢復了以前的備份文件后,輸入

#mysqlbinlog –stop-position="368312" /usr/local/mysql/var/mysql-bin.000013 |mysql -uroot -p

#mysqlbinlog –start-position="368315" /usr/local/mysql/var/mysql-bin.000013 |mysql -uroot –p

 

總結:

        一、在恢復全備數據之前必須將該binlog文件移出,否則恢復過程中,會繼續寫入語句到binlog,最終導致增量恢復數據部分變得比較混亂

        二、做好數據文件及binlog的備份至關重要,但不是備份完就算了,要定期進行數據恢復測試或演練

        三、恢復時建議對外停止更新,即禁止更新數據庫

文章出自:CCIE那點事 http://www.qdxgqk.live/ 版權所有。本站文章除注明出處外,皆為作者原創文章,可自由引用,但請注明來源。 禁止全文轉載。
本文鏈接:http://www.qdxgqk.live/?p=4188轉載請注明轉自CCIE那點事
如果喜歡:點此訂閱本站
?
?
萌宠夺宝游戏 极速十一选五是谁开的 手机赚钱集中营 彩票2元网 分析猜谜 貌喜脉动棋牌手机版 股票涨跌撮合原理 iherb 赚钱 重庆时时彩开奖记录 北京pk10谁控制的 三分彩开奖从哪查询 pk10冠军挂机模式 刷信用卡pos机赚钱 山东福彩中心 青海11选5开奖直播 仙境传说ro平民铁匠快速赚钱 福彩中奖查询