本文從binlog恢復(fù)數(shù)據(jù)及Mysqlbinlog文件刪除是作者的工作經(jīng)驗(yàn)的總結(jié)，希望對(duì)各位數(shù)據(jù)庫管理員有幫助，獻(xiàn)給大家！
做了mysql主從也有一段時(shí)間了，這兩天檢查磁盤空間情況，發(fā)現(xiàn)放數(shù)據(jù)庫的分區(qū)磁盤激增了40多G，一路查看下來，發(fā)現(xiàn)配置好主從復(fù)制以來到現(xiàn)在的binlog就有40多G，原來根源出在這里，查看了一下my.cnf，看到binlog的size是1G就做分割，但沒有看到刪除的配置，在mysql里查看了一下variables
mysql>show variables like '%log%';
查到了
| expire_logs_days | 0 |
這個(gè)默認(rèn)是0，也就是logs不過期，這個(gè)是一個(gè)global的參數(shù)，所以需要執(zhí)行
set global expire_logs_days=8;
這樣8天前的log就會(huì)被刪除了，如果有回復(fù)的需要，請(qǐng)做好備份工作，但這樣設(shè)置還不行，下次重啟mysql了，配置又恢復(fù)默認(rèn)了，所以需在my.cnf中設(shè)置
expire_logs_days = 8
這樣重啟也不怕了
想要恢愎數(shù)據(jù)庫以前的資料,執(zhí)行mysql>show binlog events;
由于數(shù)據(jù)量很多，查看起來很麻煩，光打開個(gè)文件就要閃半天，所以應(yīng)該適當(dāng)刪除部分可不用的日志。
并且如果使用的時(shí)間足夠長(zhǎng)的話，會(huì)把我的硬盤空間都給吃掉
1、登錄系統(tǒng)，/usr/bin/mysql
使用mysql查看日志
mysql>show binary logs;
+—————-+———–+
| Log_name | File_size |
+—————-+———–+
| ablelee.000001 | 150462942 |
| ablelee.000002 | 120332942 |
| ablelee.000003 | 141462942 |
+—————-+———–+
2、刪除bin-log(刪除ablelee.000003之前的而沒有包含ablelee.000003)
mysql> purge binary logs to ′ablelee.000003′;
Query OK, 0 rows affected (0.16 sec)
3、查詢結(jié)果(現(xiàn)在只有一條記錄了)
mysql> show binlog events\G
*************************** 1. row ***************************
Log_name: ablelee.000003
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 106
Info: Server ver: 5.1.26-rc-log, Binlog ver: 4
1 row in set (0.01 sec)
(ablelee.000001和ablelee.000002已被刪除)
mysql> show binary logs;
+—————-+———–+
| Log_name | File_size |
+—————-+———–+
| ablelee.000003 | 106 |
+—————-+———–+
1 row in set (0.00 sec)
(刪除的其它格式運(yùn)用!)
PURGE {MASTER | BINARY} LOGS TO ‘log_name’
PURGE {MASTER | BINARY} LOGS BEFORE ‘date’
用于刪除列于在指定的日志或日期之前的日志索引中的所有二進(jìn)制日志。這些日志也會(huì)從記錄在日志索引文件
中的清單中被刪除，這樣被給定的日志成為第一個(gè)。
例如：
PURGE MASTER LOGS TO ‘mysql-bin.010′;
PURGE MASTER LOGS BEFORE ‘2008-06-22 13:00:00′;
清除3天前的 binlog
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);
BEFORE變量的date自變量可以為’YYYY-MM-DD hh:mm:ss’格式。MASTER和BINARY是同義詞。
如果您有一個(gè)活性的從屬服務(wù)器，該服務(wù)器當(dāng)前正在讀取您正在試圖刪除的日志之一，則本語句不會(huì)起作用，
而是會(huì)失敗，并伴隨一個(gè)錯(cuò)誤。不過，如果從屬服務(wù)器是休止的，并且您碰巧清理了其想要讀取的日志之一，則從
屬服務(wù)器啟動(dòng)后不能復(fù)制。當(dāng)從屬服務(wù)器正在復(fù)制時(shí)，本語句可以安全運(yùn)行。您不需要停止它們。
要清理日志，需按照以下步驟：
1. 在每個(gè)從屬服務(wù)器上，使用SHOW SLAVE STATUS來檢查它正在讀取哪個(gè)日志。
2. 使用SHOW MASTER LOGS獲得主服務(wù)器上的一系列日志。
3. 在所有的從屬服務(wù)器中判定最早的日志。這個(gè)是目標(biāo)日志。如果所有的從屬服務(wù)器是更新的，這是清單上的
最后一個(gè)日志。
4. 制作您將要?jiǎng)h除的所有日志的備份(這個(gè)步驟是自選的，但是建議采用)。
5. 清理所有的日志，但是不包括目標(biāo)日志。
下面講一下怎么從二進(jìn)制文件恢復(fù)數(shù)據(jù), 假如不小心執(zhí)行了drop table xxx_db, 假如你保留了完整的二進(jìn)制日志的話, 先不要冒汗, 這是可以恢復(fù)的.
先看看日志
#mysqlbinlog /diskb/bin-logs/xxx_db-bin.000001
找到執(zhí)行create table xxx_db之后和drop table xxx_db之前的position, 假如是20, 1000
#mysqlbinlog --start-position="4" --stop-position="1000" /diskb/bin-logs/xxx_db-bin.000001 | mysql -u root
伴隨著一大堆的ERROR 1062 (23000) at line 12355: Duplicate entry '139' for key 1, 數(shù)據(jù)庫就這樣恢復(fù)了, 不過--start-position="20"是不行的, 必須從--start-position="4"開始, 為什么要強(qiáng)制從4開始, 這個(gè)問題我也暫時(shí)沒有搞清楚.
還有一種辦法是根據(jù)日期來恢復(fù)
#mysqlbinlog --start-datetime="2009-09-14 0:20:00" --stop-datetim="2009-09-15 01:25:00" /diskb/bin-logs/xxx_db-bin.000001 | mysql -u root
如果create table xxx_db和drop table xxx_db之間的時(shí)間相距是一年, 或者在不同的二進(jìn)制日志中, 且位置相距好遠(yuǎn), 就等著失眠吧! 做好備份, 小心操作才是正路??！
聯(lián)系客服
