目錄mysql 日志文件binlog日志binlog日志開啟日志開啟方式：binlog 日志格式binlog日志查看工具：mysqlbinlog使用binlog恢復(fù)數(shù)據(jù)線下實操小結(jié)mysql 日志文件

任何成熟軟件都會有一套成熟的日志系統(tǒng)，當軟件出現(xiàn)問題時，這些日志就是查詢問題來源的寶庫。同樣，mysql也不例外，也會有一系列日志記錄mysql的運行狀態(tài)。

mysql主要有以下幾種日志：

錯誤日志：記錄mysql運行過程中的錯誤信息 一般查詢?nèi)罩荆河涗沵ysql正在運行的語句，包括查詢、修改、更新等的每條sql 慢查詢?nèi)罩荆河涗洸樵儽容^耗時的SQL語句 binlog日志：記錄數(shù)據(jù)修改記錄，包括創(chuàng)建表、數(shù)據(jù)更新等

這些日志均需要在my.cnf文件進行配置，如果不知道m(xù)ysql的配置文件路徑，可以使用mysql命令進行查找，

mysql --verbose --help|grep -A 1 ’Default options’ #該命令會羅列出my.cnf順序查找的路徑。binlog日志

binlog就是binary log，二進制日志文件，記錄所有數(shù)據(jù)庫更新語句，包括表更新和記錄更新，即數(shù)據(jù)操縱語言(DML)，binlog主要用于數(shù)據(jù)恢復(fù)和配置主從復(fù)制等；

數(shù)據(jù)恢復(fù)：當數(shù)據(jù)庫誤刪或者發(fā)生不可描述的事情時，可以通過binlog恢復(fù)到某個時間點的數(shù)據(jù)。主從復(fù)制：當有數(shù)據(jù)庫更新之后，主庫通過binlog記錄并通知從庫進行更新，從而保證主從數(shù)據(jù)庫數(shù)據(jù)一致；

mysql按照功能分為服務(wù)層模塊和存儲引擎層模塊，服務(wù)層負責客戶端連接、SQL語句處理優(yōu)化等操作，存儲引擎層負責數(shù)據(jù)的存儲和查詢；binlog屬于服務(wù)層模塊的日志，即引擎無關(guān)性，所有數(shù)據(jù)引擎的數(shù)據(jù)更改都會記錄binlog日志。當數(shù)據(jù)庫發(fā)生崩潰時，如果使用InnoDB引擎，binlog日志還可以檢驗InnoDB的redo日志的commit情況。

binlog日志開啟日志開啟方式：

1、添加配置

log_bin=ONlog_bin_basename=/path/bin-loglog_bin_index=/path/bin-log.index

2、僅僅設(shè)置log-bin參數(shù)

log-bin=/path/bin-log

當開啟binlog日志之后，mysql會創(chuàng)建一個 log_bin_index指定的 .index 文件和多個二進制日志文件，index中按順序記錄了mysql使用的所有binlog文件。binlog日志則會以指定的名稱(或默認值) 加自增的數(shù)字作為后綴，ex：bin-log.000001，當發(fā)生下述三種情況時，binlog日志便會進行重建:

文件大小達到max_binlog_size參數(shù)的值 執(zhí)行 flush logs命令 重啟mysql服務(wù)

binlog 日志格式

通過參數(shù)binlog_format參數(shù)的值，可以設(shè)置binlog的格式，可選值有 statement、row、mixed * statement格式：記錄數(shù)據(jù)庫執(zhí)行的原始SQL語句 * row格式：記錄具體的行的修改，這個為目前默認值 * mixed格式：因為上邊兩種格式各有優(yōu)缺點，所以就出現(xiàn)了mixed格式

binlog日志查看工具：mysqlbinlog

因為binlog是二進制文件，不能像其他文件一樣，直接打開查看。但mysql提供了binlog查看工具mysqlbinlog，可以解析二進制文件。當然不同格式的日志解析結(jié)果是不一樣的； 1. statement格式日志，執(zhí)行mysqlbinlog /path/bin-log.000001，可以直接看到原始執(zhí)行的SQL語句 2. row格式日志，則可讀性沒有那么好，但仍可通過參數(shù)使文檔更加可讀 mysqlbinlog -v /path/bin-log.000001

mysqlbinlog兩對非常重要的參數(shù) 1. --start-datetime --stop-datetime 解析某一個時間段內(nèi)的binlog； 2. --start-position --stop-position 解析在兩個position之間的binlog；

使用binlog恢復(fù)數(shù)據(jù)

使用binlog恢復(fù)數(shù)據(jù)，本質(zhì)上就是通過binlog找到所有DML操作，去掉錯誤的SQL語句，然后重走一遍長征路，就可以將數(shù)據(jù)恢復(fù)；

線下實操

1.創(chuàng)建數(shù)據(jù)表并插入初始值

CREATE TABLE `users` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `age` int(8) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `users` (`id`, `name`, `age`)VALUES (null, ’姓名一’, 5);

2.找到上一次全量備份的數(shù)據(jù)庫和binlog的position(ps：當然也可以通過時間進行恢復(fù))。此處以目前狀態(tài)作為備份的初始值，

mysqldump -uroot -p T > /path/xxx.sql; # 備份數(shù)據(jù)庫 show master status; # 查看當前的position位置，此時值為154

3.插入多條記錄

INSERT INTO `users` (`id`, `name`, `age`) VALUES (null, ’姓名二’, 13), (null, ’姓名三’, 14), (null, ’姓名四’, 15), (null, ’姓名五’, 16), (null, ’姓名六’, 17);

4.進行誤操作，并且在誤操作之后又插入幾條數(shù)據(jù)

update users set age = 5； INSERT INTO `users` (`id`, `name`, `age`) VALUES (null, ’姓名七’, 16), (null, ’姓名八’, 18);

5.發(fā)現(xiàn)誤操作之后，進行數(shù)據(jù)恢復(fù)，首先停止mysql對外的服務(wù)，利用備份數(shù)據(jù)恢復(fù)到上次數(shù)據(jù)；

6.通過mysqlbinlog命令對二進制文件進行分析，分析發(fā)現(xiàn)

誤操作發(fā)生在position為706位置，且上次正常操作的結(jié)束位置在513 在1152到結(jié)尾位置有正常執(zhí)行的SQL執(zhí)行

7.通過mysqlbinlog命令從binlog日志中導(dǎo)出可執(zhí)行的SQL文件，并將數(shù)據(jù)導(dǎo)入到mysql

mysqlbinlog --start-position=154 --stop-position=513 bin-log.000001 > /path/bak.sql; mysql -uroot -p < /path/bak.sql;

8.跳過錯誤的更新語句，再通過步驟7的邏輯把后續(xù)正常語句重新跑一遍，完成數(shù)據(jù)恢復(fù)工作

小結(jié)

無論什么時間，數(shù)據(jù)庫發(fā)生崩潰都會令人愁眉緊鎖，心煩意亂。binlog可以說是在各種情況下，數(shù)據(jù)庫崩潰、數(shù)據(jù)丟失之后的一粒后悔藥，本文通過線下環(huán)境，簡單的對數(shù)據(jù)庫進行了一次數(shù)據(jù)恢復(fù)實驗，如有不對，還請指教

以上就是MySQL通過 binlog 恢復(fù)數(shù)據(jù)的詳細內(nèi)容，更多關(guān)于MySQL binlog 恢復(fù)數(shù)據(jù)的資料請關(guān)注好吧啦網(wǎng)其它相關(guān)文章！