MySQL資料庫二進位記錄備份和恢複步驟

基本概念

定義：

二進位日誌包含了所有更新了資料或者已經潛在更新了資料（例如，沒有匹配任何行的一個DELETE）的所有語句。

作用：

1.二進位日誌的主要目的是在恢複使能夠最大可能地更新資料庫，因為二進位日誌包含備份後進行的所有更新。

2.二進位日誌還用於在主複製伺服器上記錄所有將發送給從伺服器的語句。

不良影響：

運行伺服器時若啟用二進位日誌則效能大約慢1%。

MySQL預設二進位日誌是關閉狀態，先手動改動設定檔開啟二進位日誌

在my.cnf（windows下是my.ini）中的mysqld下 添加

log-bin=mysql-bin

binlog_format=mixed   //這行是來描述模式的，這兩行直接複製到mysqld下即可

重啟服務後便可以看到新增的記錄檔了

日誌位置

>>如果沒有指定檔案名稱,則Mysql使用hostname-bin檔案.

>>如果指定了相對路徑,則假定該路徑相對於資料目錄

>>Mysql在檔案名稱後添加了數字索引.所以該檔案最後的形式為filename.number如果你在日誌名中提供了副檔名(例如，–log

bin=file_name.extension)，則副檔名被悄悄除掉並忽略。

更換策略：

使用索引來迴圈檔案,在以下條件將迴圈至下一個索引

1.伺服器重啟

2.伺服器被更新

3.日誌到達了最大日誌長度 max_binlog_size

4.日誌被重新整理 mysql> flush logs;

工具介紹：

shell>>mysqlbinlog [option] binlogFile> newfile

如： D:mysqllog>mysqlbinlog binlog.000001 > 1.txt

一個例子：

log-bin=”D:/mysql/log/binlog” 那麼，在該檔案夾下就會有檔案D:/mysql/log/binlog.000001等

常見問題

1.如何清除binlog

>>>使用下面的兩個命令

PURGE {MASTER | BINARY} LOGS TO ‘log_name' //log_name不會被清除

PURGE {MASTER | BINARY} LOGS BEFORE ‘date' //date不會被清除

執行個體如下：

mysql> purge master logs to ‘binlog.000004′;

Query OK, 0 rows affected (0.01 sec)

mysql> purge master logs before '2009-09-22 00:00:00′;

Query OK, 0 rows affected (0.05 sec)

>>>或使用命令

RESET MASTER

刪除之前所有的binlog，並重建新的binlog

尾碼從000001開始

註：如果您有一個活性的從屬伺服器，該伺服器當前正在讀取您正在試圖刪除的日誌之一，

則本語句不會起作用，而是會失敗，並伴隨一個錯誤。

不過，如果從屬伺服器是休止的，並且您碰巧清理了其想要讀取的日誌之一，則從屬伺服器啟動後不能複製。

當從屬伺服器正在複製時，本語句可以安全運行。您不需要停止它們。

2.記錄到二進位日誌知的內容配置

binlog-do-db=sales 只記錄sales庫

binlog-ignore-db=sales 除sales庫不記錄，其他都記錄

但是如果在操作資料庫之前，不使用use $dbname 那麼所有的SQL都不會記錄

如果使用了use $dbname,那麼判斷規則取決於這裡的$dbname，而不是SQL中操作的庫

3.二進位日誌不準確的處理

預設情況下，並不是每次寫入時都將二進位日誌與硬碟同步。因此如果作業系統或機器(不僅僅是MySQL伺服器)崩潰，有可能二進位日誌中最後的

句丟失。

要想防止這種情況，你可以使用sync_binlog全域變數(1是最安全的值，但也是最慢的)，使二進位日誌在每N次二進位日誌寫入後與硬碟同步。

即使sync_binlog設定為1,出現崩潰時，也有可能表內容和二進位日誌內容之間存在不一致性。

如果崩潰恢複時MySQL伺服器發現二進位日誌變短了(即至少缺少一個成功提交的InnoDB事務)，

如果sync_binlog =1並且硬碟/檔案系統的確能根據需要進行同步(有些不需要)則不會發生，則輸出錯誤訊息 (“二進位日誌<名>比期望的要小”)。

在這種情況下，二進位日誌不準確，複製應從主伺服器的資料快照開始。

mysqldump --databases repltest --flush-logs --opt repltest>q:/repltest_full_01.sql

上面這句話可以在備份的同時重新整理日誌，產生新的日誌，這樣新日誌裡面的內容相當於這次備份之後的增量部分，可以用這種方式來實現mysql的

量備份，即先恢複資料庫到之前備份的某個資料備份節點上，然後執行之後的二進位日誌，因為二進位日誌實質上記錄的是所有資料變更的操作，

以來實現增量備份或者對某個時間點的還原

清理日誌

如果每天都會產生大量的二進位日誌，這些日誌長時間不清理的話，將會對磁碟空間帶來很大的浪費，所以定期清理日誌是DBA維護mysql的一個重

工作

1)RESET MASTER

在上面查看日誌存放的檔案夾中，二進位日誌命名的格式是以mysql-bin.*，*代表日誌的序號,序號是遞增的,其中還有mysql-bin.index是日誌的

引檔案,記錄了日誌的最大序號

我們執行RESET MASTER命名刪除全部日誌，新的日誌從頭開始

2)PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE

執行PURGE MASTER LOGS TO 'mysql-bin.******'命令，是將'******'編號之前的所有日誌進行刪除

執行PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'命令，是將在'yyyy-mm-dd hh:mm:ss'時間之前的所有日誌進行刪除

3)-EXPIRE_LOGS_DAYS

此參數是設定日誌的到期天數，到期的日誌將會被自動刪除，這有利於減少我們管理日誌的工作量，需要修改my.cnf

EXPIRE_LOGS_DAYS = 3 //即為日誌儲存三天，三天之後到期的日誌自動刪除

恢複

bin-log是記錄著mysql所有事件的操作，當mysql發生重大錯誤時，可以通過bin-log做完整恢複，基於時間點的恢複，和基於位置的恢複

完整恢複，假定我們每天淩晨2點都會使用mysqldump備份資料庫，但在第二天早上9點由於資料庫出現了故障，資料無法訪問，需要恢複資料，先

用昨天淩晨備份的檔案進行恢複到淩晨2點的狀態，在使用mysqlbinlog恢複自mysqldump備份以來的binlog

/usr/local/mysql/bin/mysqlbinlog bin.000001 |mysql -u root -p

這樣資料庫就可以完全的恢複到崩潰前的完全狀態

-----執行個體-------

小量的資料庫我們可以每天進行完整備份，因為這也用不了多少時間，但當資料庫很大時，我們就不太可能每天進行一次完整備份了，而且改成每

一次完整備份，每天一次增量備份類似這樣的備份策略。增量備份的原理就是使用了mysql的二進位日誌，所以我們必須啟用二進位日誌功能。

一、增量備份

1、比如我們在星期天下午11點做一次完整備份：

/usr/local/mysql/bin/mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > fullbackup_sunday_11_PM.sql

在sql檔案中我們會看到兩行：

– Position to start replication or point-in-time recovery from

– CHANGE MASTER TO MASTER_LOG_FILE=’bin-log.000002′, MASTER_LOG_POS=107;

第二行包含了我們需要的資訊，是指備份後所有的更改將會儲存到bin-log.000002二進位檔案中。

2、然後在星期一下午11點我們來做一次增量備份：

mysqladmin flush-logs

這時將會產生一個新的二進位記錄檔bin-log.000003，bin-log.000002則儲存了自星期天下午11點到現在的所有更改，我們只需要把這個檔案備

到安全的地方就行了。然後星期二我們又做增量備份，還是執行同樣的命令，這時我們儲存bin-log.000003檔案。

二、恢複備份

比如星期三中午12點出現了故障，這時需要恢複，我們首先匯入星期天的完整備份：

mysql -u root -p < fullbackup_sunday_3_AM.sql

接著我們匯入星期一和星期二的增量備份：

/usr/local/mysql/bin/mysqlbinlog bin-log.000002 bin-log.000003 | mysql -u root -p

這時我們已經恢複了所有備份資料，我們還可以找到bin-log.000004，進一步恢複最新的資料。

mysqlbinlog 後加 --database "具體庫" 可恢複具體庫的增量資訊

加 --stop-date="2010-12-20 13:15:00" 可恢複到該日誌的具體時間點