MySQL通過binlog恢復(fù)數(shù)據(jù)

mysql 日志文件

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

mysql主要有以下幾種日志：

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

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

mysql --verbose --help|grep -A 1 'Default options' #該命令會(huì)羅列出my.cnf順序查找的路徑。

binlog日志

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

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

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

binlog日志開啟

日志開啟方式：

1、添加配置

log_bin=ON
log_bin_basename=/path/bin-log
log_bin_index=/path/bin-log.index

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

 log-bin=/path/bin-log

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

文件大小達(dá)到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格式：記錄具體的行的修改，這個(gè)為目前默認(rèn)值 * mixed格式：因?yàn)樯线厓煞N格式各有優(yōu)缺點(diǎn)，所以就出現(xiàn)了mixed格式

binlog日志查看工具：mysqlbinlog

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

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

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

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

線下實(shí)操

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：當(dāng)然也可以通過時(shí)間進(jìn)行恢復(fù))。此處以目前狀態(tài)作為備份的初始值，

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

3.插入多條記錄

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

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

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

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

6.通過mysqlbinlog命令對(duì)二進(jìn)制文件進(jìn)行分析，分析發(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.跳過錯(cuò)誤的更新語句，再通過步驟7的邏輯把后續(xù)正常語句重新跑一遍，完成數(shù)據(jù)恢復(fù)工作

小結(jié)

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

以上就是MySQL通過 binlog 恢復(fù)數(shù)據(jù)的詳細(xì)內(nèi)容，更多關(guān)于MySQL binlog 恢復(fù)數(shù)據(jù)的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！