mysql二進制日志維護及從二進制日志中恢復

MySQL>show processlist; | Id       | User | Host      | db   | Command | Time | State | Info             | | 55467761 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----------+------+-----------+------+---------+------+-------+------------------+

沒有Binlog Dump的線程, 說明我這台機器沒有在做replication, 直接用以下命令清除所有的bin-log, 可以刪除列於索引文件中的所有二進制日志，把二進制日志索引文件重新設置為空，並創建一個新的二進制日志文件。

MySQL>reset master;
MySQL>show master status; | File     | Position | Binlog_Do_DB | Binlog_Ignore_DB | | xxx.000001 |     180 |              |                  |
+----------+----------+--------------+------------------+

假設我只想刪除xxx.000018以前的bin-log, 可以用以下的命令.

MySQL>purge master logs to 'xxx.000018'

有條件的機器最好保留一份bin-log在另外一個磁盤, 就算數據庫掛了, 我們也可以根據二進制日志來恢復, 怎麼保留二進制日志呢? 編輯你的my.cnf, 指定如下配置log-bin=/diskb/bin-logs/xxx_db-bin, 重新啟動數據庫的時候, 你就會發現/diskb/bin-logs哪裡多了兩個文件xxx_db-bin.000001, xxx_db-bin.index, 一個是二進制日志文件, 它將更改數據的所有查詢記入該文件, 另外一個是二進制日志文件名的索引文件.

下面講一下怎麼從二進制文件恢復數據, 假如不小心執行了drop table xxx_db, 假如你保留了完整的二進制日志的話, 先不要冒汗, 這是可以恢復的.

先看看日志

>MySQLbinlog /diskb/bin-logs/xxx_db-bin.000001

找到執行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, 數據庫就這樣恢復了, 不過--start-position="20"是不行的, 必須從--start-position="4"開始, 為什麼要強制從4開始, 這個問題我也暫時沒有搞清楚.

還有一種辦法是根據日期來恢復

>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之間的時間相距是一年, 或者在不同的二進制日志中, 且位置相距好遠, 就等著失眠吧! 做好備份, 小心操作才是正路啊...