MySQL數(shù)據(jù)庫通過Binlog恢復數(shù)據(jù)的詳細步驟

更新時間：2022年06月06日 11:29:14 作者：擼碼社區(qū)

MySQL的binlog日志是MySQL日志中非常重要的一種日志,記錄了數(shù)據(jù)庫所有的DML操作,下面這篇文章主要給大家介紹了關于MySQL數(shù)據(jù)庫通過Binlog恢復數(shù)據(jù)的相關資料,文中通過圖文介紹的非常詳細,需要的朋友可以參考下

Mysql Binlog 簡介

Mysql Binlog是二進制格式的日志文件
Binlog是用來記錄Mysql內部對數(shù)據(jù)庫的改動（只記錄對數(shù)據(jù)的修改操作），主要用于數(shù)據(jù)庫的主從復制以及增量恢復

開啟Binlog

查看是否打開了Binlog 【ON表示已經(jīng)打開 OFF表示關閉默認關閉狀態(tài)】

show variables like ‘%log_bin%’;

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

方法1：
找到mysql配置中的my.ini文件，在[mysqld]下面添加如下參數(shù)
log_bin=mysql-bin
binlog-format=ROW

Mysql binlog日志有三種格式 【binlog-format參數(shù)】

1.Statement：每一條會修改數(shù)據(jù)的sql都會記錄在binlog中
2.Row:不記錄sql語句上下文相關信息，僅保存哪條記錄被修改
3.Mixedlevel:是以上兩種level的混合使用，一般的語句修改使用statment格式保存binlog，如一些函數(shù)，statement無法完成主從復制的操作，則采用row格式保存binlog,MySQL會根據(jù)執(zhí)行的每一條具體的sql語句來區(qū)分對待記錄的日志形式，也就是在Statement和Row之間選擇一種.新版本的MySQL中對row level模式也被做了優(yōu)化，并不是所有的修改都會以row level來記錄，像遇到表結構變更的時候就會以statement模式來記錄。至于update或者delete等修改數(shù)據(jù)的語句，還是會記錄所有行的變更。

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

開啟以后，重啟服務會發(fā)現(xiàn)data目錄下多了日志文件

使用Binlog恢復數(shù)據(jù)

show master status; 【查看當前正在寫入的binlog文件】

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

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

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

如果是恢復最后一次修改前數(shù)據(jù)，則使用記錄的倒數(shù)第二條日志的結束起止坐標

cmd 切換到MySQL存放mysqlbinlog.exe應用程序的bin目錄后，執(zhí)行以下命令，指定數(shù)據(jù)恢復起始位置，轉換為SQL文件

這里的起止坐標為2536 - 2848
mysqlbinlog --no-defaults “D:\DataBase\MYSQL\mysql-8.0.24-winx64\data\binlog.000178” -d test --skip-gtids --start-position=2536 --stop-position=2848>test.sql

cmd登錄MySQL，切換到對應數(shù)據(jù)庫，執(zhí)行命令指定SQL文件位置恢復數(shù)據(jù)

mysql -uroot -p123456

use test;
source D:\DataBase\MYSQL\mysql-8.0.24-winx64\bin\test.sql

再次查詢表數(shù)據(jù)

附錄

cmd 切換到MySQL存放mysqlbinlog.exe應用程序的bin目錄后，執(zhí)行以下命令，可以將Binlog文件轉換為txt文件，方便閱讀理解

cmd 切換到MySQL存放mysqlbinlog.exe應用程序的bin目錄后，執(zhí)行以下命令，可以一次性完成數(shù)據(jù)恢復

mysqlbinlog.exe --no-defaults --start-position=2536 --stop-position=2848 --database=test “D:\DataBase\MYSQL\mysql-8.0.24-winx64\data\binlog.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 命令打開