MySQL數據庫二進制日志備份和恢復步驟

2024-07-24 12:37:02

字體：大中小

來源：轉載

供稿：網友

在mysql中利用二進制日志備份與恢復數據庫算是一種比較高級的數據庫備份操作方法了，我們必須先在my.ini中打開mysql-bin功能，下面我來給大有介紹一下。

基本概念

定義:二進制日志包含了所有更新了數據或者已經潛在更新了數據（例如，沒有匹配任何行的一個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 //Vevb.com

執行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文件.

二、恢復備份