mysql數據通過data文件恢復

mysql磁盤文件被損壞，無法啟動，能看到data文件，在沒有備份的話如何復原？

情景1：知道數據庫中的表結構

1、先執(zhí)行表結構語句

2、解除綁定

alter table `表1` ?discard ?tablespace;
alter table `表2` ?discard ?tablespace;
......

此時當前數據庫下所有的.idb文件都會被刪除，也就是只剩下表結構，原表數據被刪除

3、導入 .idb文件現有數據庫

也就是添加表數據，不過，目前還沒有綁定

alter table `表1` ?import ? tablespace;
alter table `表2` ?import ? tablespace;
......

mysql數據恢復方法個人總結

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

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

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

步驟

1.如果想使用MySql數據恢復功能，首先需要修改Mysql配置文件，開啟binlog功能。（有些版本binlog默認是不開啟的，因此需要手動修改配置文件開啟）

(1)windows環(huán)境

配置文件名稱為my.ini,可以安裝軟件everything搜索這個文件。路徑樣例如下：

C:ProgramDataMySQLMySQL Server 5.7my.ini

(2)linux環(huán)境

配置文件名稱為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在某個文件夾下時，必須自己創(chuàng)建文件夾，否則重啟mysql會報錯。

3.配置完成后，重啟mysql。

4.現在，對mysql的數據庫、表、數據等操作、執(zhí)行增刪改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;

本人執(zhí)行后，發(fā)現當前使用的binlog是：mysqlbinlog.000004

8.然后查看這個binlog的內容：

show binlog events in 'mysqlbinlog.000004';

執(zhí)行后，可以從查詢結果的Info列中，找到剛才創(chuàng)建binlog_test表的sql（對應的Event_type是Query）;

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

在上一些，可以看到創(chuàng)建表后、新增數據的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之間的執(zhí)行日志，就是給表binlog_test增加數據的日志，因此只要重復執(zhí)行這之間的操作，就可以還原數據了。

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

linux下，找到mysqlbinlog的位置（這個是mysql的bin目錄下的一個可執(zhí)行文件）；

然后執(zhí)行以下命令，即可還原數據：

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

其中，指定了開始位置為460，結束位置為1199，命令執(zhí)行后就會讀取日志中指定范圍內的命令并重新執(zhí)行。

其它命令的含義為：

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

11-2.也可以把binlog日志中，指定范圍內的日志導出，然后自己執(zhí)行source命令還原數據，如下：

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

執(zhí)行這個命令后，指定范圍內的日志被導出到test.sql中；

然后登陸mysql，執(zhí)行命令：

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，然后重新執(zhí)行這期間的命令。

2.增刪改操作會記錄到binlog中，慢查詢sql也可以記錄到慢查詢log中，不過也需要自己修改配置文件開啟慢查詢日志。

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論