MySQL資料庫通過Binlog恢復資料的詳細步驟

Mysql Binlog 簡介

Mysql Binlog是二進位制格式的紀錄檔檔案
Binlog是用來記錄Mysql內部對資料庫的改動（只記錄對資料的修改操作），主要用於資料庫的主從複製以及增量恢復

開啟Binlog

檢視是否開啟了Binlog 【ON表示已經開啟 OFF表示關閉 預設關閉狀態】

show variables like ‘%log_bin%’;

開啟Binlog 【修改完以後重啟服務】

方法1：
找到mysql設定中的my.ini檔案，在[mysqld]下面新增如下引數
log_bin=mysql-bin
binlog-format=ROW

Mysql binlog紀錄檔有三種格式 【binlog-format引數】

1.Statement：每一條會修改資料的sql都會記錄在binlog中
2.Row:不記錄sql語句上下文相關資訊，僅儲存哪條記錄被修改
3.Mixedlevel:是以上兩種level的混合使用，一般的語句修改使用statment格式儲存binlog，如一些函數，statement無法完成主從複製的操作，則採用row格式儲存binlog,MySQL會根據執行的每一條具體的sql語句來區分對待記錄的紀錄檔形式，也就是在Statement和Row之間選擇一種.新版本的MySQL中對row level模式也被做了優化，並不是所有的修改都會以row level來記錄，像遇到表結構變更的時候就會以statement模式來記錄。至於update或者delete等修改資料的語句，還是會記錄所有行的變更。

方法2：
SET SQL_LOG_BIN=1 命令開啟
SET SQL_LOG_BIN=0 命令關閉

開啟以後，重啟服務會發現data目錄下多了紀錄檔檔案

使用Binlog恢復資料

show master status; 【檢視當前正在寫入的binlog檔案】

測試表， 測試update 【全部改為趙六，再改為老王，恢復為全部為趙六】

update user set name = ‘趙六’;
update user set name = ‘老王’;

show binlog EVENTS in ‘binlog.000178’ ; 【查詢所需要恢復的事件起止的位置】

如果是恢復最後一次修改前資料，則使用記錄的倒數第二條紀錄檔的結束起止座標

cmd 切換到MySQL存放mysqlbinlog.exe應用程式的bin目錄後，執行以下命令，指定資料恢復起始位置，轉換為SQL檔案

這裡的起止座標為2536 - 2848
mysqlbinlog --no-defaults “D:DataBaseMYSQLmysql-8.0.24-winx64databinlog.000178” -d test --skip-gtids --start-position=2536 --stop-position=2848>test.sql

cmd登入MySQL，切換到對應資料庫，執行命令指定SQL檔案位置恢復資料

mysql -uroot -p123456

use test;
source D:DataBaseMYSQLmysql-8.0.24-winx64bintest.sql

再次查詢表資料

附錄

cmd 切換到MySQL存放mysqlbinlog.exe應用程式的bin目錄後，執行以下命令，可以將Binlog檔案轉換為txt檔案，方便閱讀理解

cmd 切換到MySQL存放mysqlbinlog.exe應用程式的bin目錄後，執行以下命令，可以一次性完成資料恢復

mysqlbinlog.exe --no-defaults --start-position=2536 --stop-position=2848 --database=test “D:DataBaseMYSQLmysql-8.0.24-winx64databinlog.000178” | mysql -u root -p

mysqlbinlog: [ERROR] unknown variable ‘default-character-set=utf8’.

如果遇到這個錯誤原因是mysqlbinlog這個工具無法識別binlog中的設定中的default-character-set=utf8這個指令
兩個方法可以解決這個問題：
一：在MySQL的設定/etc/my.cnf中將default-character-set=utf8 修改為 character-set-server = utf8，需要重啟MySQL服務
二：用mysqlbinlog --no-defaults mysql-bin.000004 命令開啟

總結 

到此這篇關於MySQL資料庫通過Binlog恢復資料的文章就介紹到這了,更多相關MySQL Binlog恢復資料內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！