mysqlbinlog恢複資料-update20140820

mysqlbinlog恢複資料

BINLOG就是一個記錄SQL語句的過程，和普通的LOG一樣。隻是它是二進制存儲，普通的是十進制存儲。

==============================================================================

1.啟動二進制日志記錄，預設mysql關閉binlog。

# vim /etc/my.cnf，修改或添加

#log-bin = mysql-bin (此處等号後邊名字可以自定義)  

重新開機mysql

bin-log日志的存儲位置：

如果log-bin配置項沒指定絕對路徑，則在配置的datadir目錄下，不指定的話預設和資料在一起，不友善管理。

可以自己指定如log-bin=/var/lib/mysql/binlog/mysql-bin

我的datadir=/var/lib/mysql，指定上邊的目錄就是在mysql下建立個binlog檔案夾，注意要先建立此檔案件，并且注意權限，之後才可以指定成上邊的存貯位置。

檢視bin-log是否開啟：

Show variables like "%log_bin%";

檢視bin-log日志檔案名：

show binary logs;

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

| Log_name         | File_size |

| mysql-bin.000001 |       149 |

| mysql-bin.000002 |       106 |

新生成一個bin-log日志（即再發生的sql操作将會寫入這個bin-log裡）：

flush logs;

檢視目前bin-log日志：

show master status;

清空bin-log日志：　

reset master; (一般在資料庫進行完一次完整的備份時就清空一次bin-log日志)

檢視某個日志：

mysqlbinlog --no-defaults mysql-bin.000001 [ | more]      此指令在指令行下執行報錯可以到 /usr/local/mysql/bin/下執行。

--no-defaults 作用：

如果my.cnf中設定了default-character-set選項，不加--np-defaults  會報錯  mysqlbinlog: unknown variable 'default-character-set=utf8'

這是mysql使用mysqlbinlog的bug:

使用mysqlbinlog工具檢視二進制日志時會重新讀取的mysql的配置檔案my.cnf(windows下是my.ini),而不是伺服器已經加 載進記憶體的配置檔案。也就是說隻要修改并儲存了my.cnf檔案，而不需要重起mysql伺服器，則使用mysqlbinlog檢視時修改後的 my.cnf配置檔案對mysqlbinlog而言已經生效。是以這裡可以使用此方法：把client選項組中default-character- set=utf8選項屏蔽掉,然後運作mysqlbinlog工具，則不會産生任何問題了。當然記得在不使用mysqlbinlog工具時把選項恢複。

使用--no-defaults 就不用修改上邊的配置了，是以還是使用這個選項比較友善。

以下是mysqlbinlog後跟的主要參數:

--stop-position="100"

--start-position="50"

--stop-date="2012-01-04 21:17:50"

--start-date="2012-01-04 19:10:10"

binlog日志删除：

binlog日志會使檔案增長非常快，很快會占滿磁盤空間，是以要定期删除。

mysql>show variables like ‘%expire_logs_days%’;   #預設是0，既不過期

mysql>set global expire_logs_days=7;   #儲存7天，設定全局參數（set global）是友善不重新開機mysql使之生效，想要重新開機後生效要配置到my.cnf。

2.測試

SQL code

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

mysql>  create table test(id  int auto_increment  not null primary key , val  int ,data  varchar (20)); mysql>  insert into test(val,data)  values (10, 'liang' ); Query OK, 1 row affected (0.03 sec) mysql>  insert into test(val,data)  values (20, 'jia' ); Query OK, 1 row affected (0.08 sec) mysql>  insert into test(val,data)  values (30, 'hui' ); Query OK, 1 row affected (0.03 sec) mysql> flush logs;    --産生第二個日志檔案 Query OK, 0  rows affected (0.09 sec) mysql>  insert into test(val,data)  values (40, 'aaa' ); Query OK, 1 row affected (0.05 sec) mysql>  insert into test(val,data)  values (50, 'bbb' ); Query OK, 1 row affected (0.03 sec) mysql>  insert into test(val,data)  values (60, 'ccc' ); Query OK, 1 row affected (0.03 sec) mysql>  delete from test  where id  between 4  and 5;   --删除記錄 Query OK, 2  rows affected (0.05 sec) mysql>  insert into test(val,data)  values (70, 'ddd' ); Query OK, 1 row affected (0.03 sec) mysql> flush logs;           --産生第三個檔案檔案 Query OK, 0  rows affected (0.11 sec) mysql>  insert into test(val,data)  values (80, 'dddd' ); Query OK, 1 row affected (0.05 sec) mysql>  insert into test(val,data)  values (90, 'eeee' ); Query OK, 1 row affected (0.03 sec) drop table test;        --删除表 Query OK, 0 row affected (0.05 sec)

OK，現在測試資料已經建好了，要的就是将test表的資料全部恢複出來。

先用mysqlbinlog工具将日志檔案生成txt檔案出來分析。

#mysqlbinlog mysql-bin.000001 > 001.txt   
#mysqlbinlog mysql-bin.000002 > 002.txt
#mysqlbinlog mysql-bin..000003 > 003.txt      

注：如果報mysqlbinlog指令不存在，需要進入到/usr/local/mysql/bin下執行,

[bin]  mysqlbinlog ../var/bin-log.000001 > /tmp/log1.txt   //指定bin-log的相對路徑

因為我們需要重做第一個日志檔案的所有操作，是以這裡隻需要将第一個日志檔案全恢複就行了。

mysqlbinlog mysql-bin.000001 | mysql -uroot –p123  [databasename]  //可以指定資料庫      

Ok,接着，我們需要分析的是第二個日志檔案。為什麼要分析它呢，

因為它中途執行了一個操作是DELETE，因為我們要做的是恢複全部資料，

也就是我們不希望去重做這個語句。是以在這裡我們要想辦法去繞開它。

我們先打開002.txt檔案來分析一下。

# at 546
#130908  2:15:17 server id 128  end_log_pos 654         Query   thread_id=19    exec_time=0     error_code=0
SET TIMESTAMP=1378577717/*!*/;
delete from gl_whos_online where full_name=2
/*!*/;      

在這個檔案中，我們可以看到DELETE的操作的起始位置是546，終止位置是654.

那麼我們隻要重做第二個日志檔案的開頭到546的操作，然後再從654到末尾的操作，

我們就可以把資料給恢複回來，而不會DELETE資料。是以執行兩個指令：

#mysqlbinlog mysql-bin.000002 --stop-pos=546 | mysql -uroot -p123
#mysqlbinlog mysql-bin.000002 --start-pos=654 | mysql -uroot -p 123      

OK,現在第二個日志檔案的資料了。

第三個日志檔案也是同理，隻要找到DROP TABLE的位置，就可以了。

#mysqlbinlog mysql-bin.000003 --stop-pos=574 | mysql -uroot –p123      

最終，全部資料都回來了。

總結：bin-log日志就是記錄了操作資料庫的語句，用bin-log恢複資料就是把曾執行的語句有選擇的再執行一遍，執行需要的，像delete什麼的就跳過。

我第一次按操作把表drop後執行binlog恢複總是報錯：指定的表不存在，

因為我恢複的日志中沒有建立表的語句，汗！

2014-08-20号添加内容

=====================================================================================

今天把資料庫中的前三天的資料不小心删了，想要通過binlog日志恢複，這個表的資料時通過每天的定時任務插入的。

我删除了17,18,19号的資料，想通過上邊的方法恢複，但是不可行，因為binlog檔案太大了，要從這個表的建表語句恢複到我執行的delete語句，要執行幾個binlog,檔案很大不說，關鍵的問題是裡邊不光是這一個表的語句，還包含了其他表的語句。

關鍵時刻，帆哥出馬：

思路：

1，先從binlog日志中找出17,18,19号的插入語句，因為表的資料通過每天的crontab(每天9:00執行)插入的，可以根據crontab的執行時間剔除掉binlog中的不需要的資料。

mysqlbinlog  --no-defaults  --start-date="2014-08-17 09:00:00" --stop-date="2014-08-17 09:01:00"  mysql-bin.000005 >> mylog.txt

mysqlbinlog  --no-defaults  --start-date="2014-08-18 09:00:00" --stop-date="2014-08-18 09:01:00"  mysql-bin.000005 >> mylog.txt

mysqlbinlog  --no-defaults  --start-date="2014-08-19 09:00:00" --stop-date="2014-08-19 09:01:00"  mysql-bin.000005 >> mylog.txt      

檢視crontab執行時間：最好執行的腳本在結束後echo開始時間和結束時間到指定log，以便檢視每天執行情況。

2，通過上邊的三條語句把17,18,19三天的插入語句都放入到了mylog.txt中，然後過濾掉操作的不是目前表名的行，不是insert into的行。

　　cat mylog.txt | grep 'INSERT INTO' | grep 'tablename' > newlog.sql

3, 導入newlog.sql到目前的表中，

導入時報錯：導入的檔案太大了，要設定配置項max_allowed_packet，方法參看http://www.cnblogs.com/leezhxing/p/3925332.html。

搞定，資料回來了。