MySql恢復資料方法梳理講解

一、前言

mysql具有binlog功能，可以記錄對錶進行過的操作、執行過的sql語句；

以下主要是總結下binlog的開啟方法、使用方法等；

如果誤刪資料庫表中的某些資料、或者誤刪整個表、誤修改表結構等，就可以使用這個方法進行恢復。

二、步驟

1.如果想使用MySql資料恢復功能，首先需要修改Mysql組態檔，開啟binlog功能。（有些版本binlog預設是不開啟的，因此需要手動修改組態檔開啟）

(1)windows環境

組態檔名稱為my.ini,可以安裝軟體everything搜尋這個檔案。路徑樣例如下：

C:ProgramDataMySQLMySQL Server 5.7my.ini

(2)linux環境

組態檔名稱為my.cnf,可以使用find命令搜尋檔案所在位置：

sudo find / -name my.cnf

位置樣例如下：

/etc/my.cnf

2.找到組態檔後(my.ini/my.cnf)，新增以下設定即可(當然先看看組態檔是否已設定了這行，不要重複了)：

log-bin=mysqlbinlog
binlog-format=ROW

或者也可以指定binlog的位置（下方是linux下的路徑）：

log-bin=/data/mysql/log/mysqlbinlog
binlog-format=ROW

注意，指定binlog在某個資料夾下時，必須自己建立資料夾，否則重啟mysql會報錯。

3.設定完成後，重啟mysql。

4.現在，對mysql的資料庫、表、資料等操作、執行增刪改sql，都會記錄到binlog中。（搜尋mysqlbinlog就可以找到這個紀錄檔檔案）

5.進行測試，新建一個表binlog_test，列隨意，然後寫幾行資料，然後清空表資料。（準備使用binlog還原資料）

6.檢視binlog的命令如下：

mysql> show binlog events;   #只檢視第一個binlog檔案的內容
mysql> show binlog events in 'mysqlbinlog.000002';#檢視指定binlog檔案的內容
mysql> show binary logs;  #獲取binlog檔案列表
mysql> show master status； #檢視當前正在寫入的binlog檔案

7.首先看當前使用的是哪個binlog:

show master status;

本人執行後，發現當前使用的binlog是：mysqlbinlog.000004

8.然後檢視這個binlog的內容：

show binlog events in 'mysqlbinlog.000004';

執行後，可以從查詢結果的Info列中，找到剛才建立binlog_test表的sql（對應的Event_type是Query）;

可以看到剛才清空表資料的記錄行，Event_type是Delete_rows；

在上一些，可以看到建立表後、新增資料的Event_type是Write_rows；

9.分析這個binlog，我們的目標是還原剛才被刪除的資料，因此找到create table語句後的第一個Event_type是Query的行，它的Pos為460；

再找到Event_type是Delete_rows上面最接近的、Info中是COMMIT的行，它的Event_type是Xid，End_log_pos是1199。

（每一行的End_log_pos對應下一行的Pos）

10.現在找到了一個範圍，從460到1199之間的執行紀錄檔，就是給表binlog_test增加資料的紀錄檔，因此只要重複執行這之間的操作，就可以還原資料了。

11-1.windows下，找到mysqlbinlog.exe的位置；

linux下，找到mysqlbinlog的位置（這個是mysql的bin目錄下的一個可執行檔案）；

然後執行以下命令，即可還原資料：

mysqlbinlog --no-defaults --start-position=460 --stop-position=1199 "C:ProgramDataMySQLMySQL Sever 5.7Datamysqlbinlog.000004" -d test | mysql -uroot -proot test

其中，指定了開始位置為460，結束位置為1199，命令執行後就會讀取紀錄檔中指定範圍內的命令並重新執行。

其它命令的含義為：

"-d test"的意思是，指定資料庫為test（binlog_test表在test資料庫中）
"|"是管道符
"mysql -uroot -proot test"是mysql的賬號密碼、登陸目標資料庫
"-uroot"：賬號是root
"-proot"：密碼是root
"test"：資料庫是test 

11-2.也可以把binlog紀錄檔中，指定範圍內的紀錄檔匯出，然後自己執行source命令還原資料，如下：

mysqlbinlog "C:ProgramDataMySQLMySQL Sever 5.7Datamysqlbinlog.000004" -d test  --skip-gtids  --start-position=460 --stop-position=1199 > test.sql

執行這個命令後，指定範圍內的紀錄檔被匯出到test.sql中；

然後登陸mysql，執行命令：

mysql> use test; 
mysql> source test.sql;

這樣就可以還原資料了。

三、其它筆記

1.如果使用truncate刪除表的資料，binlog中也會新增幾行記錄，記錄truncate操作，樣例如下：

mysqlbinlog.000004 | 3122 | Query | 1 | 3211 | use 'test'; TRUNCATE 'binlog_test'

如果想恢復資料，就需要找到最開始插入資料的Pos、到truncate之前的End_log_pos，然後重新執行這期間的命令。

2.增刪改操作會記錄到binlog中，慢查詢sql也可以記錄到慢查詢log中，不過也需要自己修改組態檔開啟慢查詢紀錄檔。

到此這篇關於MySql恢復資料方法梳理講解的文章就介紹到這了,更多相關MySql恢復資料內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！