mysql數(shù)據(jù)通過(guò)data文件恢復(fù)

mysql磁盤文件被損壞，無(wú)法啟動(dòng)，能看到data文件，在沒(méi)有備份的話如何復(fù)原？

情景1：知道數(shù)據(jù)庫(kù)中的表結(jié)構(gòu)

1、先執(zhí)行表結(jié)構(gòu)語(yǔ)句

2、解除綁定

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

此時(shí)當(dāng)前數(shù)據(jù)庫(kù)下所有的.idb文件都會(huì)被刪除，也就是只剩下表結(jié)構(gòu)，原表數(shù)據(jù)被刪除

3、導(dǎo)入 .idb文件現(xiàn)有數(shù)據(jù)庫(kù)

也就是添加表數(shù)據(jù)，不過(guò)，目前還沒(méi)有綁定

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

mysql數(shù)據(jù)恢復(fù)方法個(gè)人總結(jié)

mysql具有binlog功能，可以記錄對(duì)表進(jìn)行過(guò)的操作、執(zhí)行過(guò)的sql語(yǔ)句；

以下主要是總結(jié)下binlog的開(kāi)啟方法、使用方法等；

如果誤刪數(shù)據(jù)庫(kù)表中的某些數(shù)據(jù)、或者誤刪整個(gè)表、誤修改表結(jié)構(gòu)等，就可以使用這個(gè)方法進(jìn)行恢復(fù)。

步驟

1.如果想使用MySql數(shù)據(jù)恢復(fù)功能，首先需要修改Mysql配置文件，開(kāi)啟binlog功能。（有些版本binlog默認(rèn)是不開(kāi)啟的，因此需要手動(dòng)修改配置文件開(kāi)啟）

(1)windows環(huán)境

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

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)，新增以下配置即可(當(dāng)然先看看配置文件是否已配置了這行，不要重復(fù)了)：

log-bin=mysqlbinlog
binlog-format=ROW

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

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

注意，指定binlog在某個(gè)文件夾下時(shí)，必須自己創(chuàng)建文件夾，否則重啟mysql會(huì)報(bào)錯(cuò)。

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

4.現(xiàn)在，對(duì)mysql的數(shù)據(jù)庫(kù)、表、數(shù)據(jù)等操作、執(zhí)行增刪改sql，都會(huì)記錄到binlog中。（搜索mysqlbinlog就可以找到這個(gè)日志文件）

5.進(jìn)行測(cè)試，新建一個(gè)表binlog_test，列隨意，然后寫幾行數(shù)據(jù)，然后清空表數(shù)據(jù)。（準(zhǔn)備使用binlog還原數(shù)據(jù)）

6.查看binlog的命令如下：

mysql> show binlog events; ? #只查看第一個(gè)binlog文件的內(nèi)容
mysql> show binlog events in 'mysqlbinlog.000002';#查看指定binlog文件的內(nèi)容
mysql> show binary logs; ?#獲取binlog文件列表
mysql> show master status； #查看當(dāng)前正在寫入的binlog文件

7.首先看當(dāng)前使用的是哪個(gè)binlog:

show master status;

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

8.然后查看這個(gè)binlog的內(nèi)容：

show binlog events in 'mysqlbinlog.000004';

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

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

在上一些，可以看到創(chuàng)建表后、新增數(shù)據(jù)的Event_type是Write_rows；

9.分析這個(gè)binlog，我們的目標(biāo)是還原剛才被刪除的數(shù)據(jù)，因此找到create table語(yǔ)句后的第一個(gè)Event_type是Query的行，它的Pos為460；

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

（每一行的End_log_pos對(duì)應(yīng)下一行的Pos）

10.現(xiàn)在找到了一個(gè)范圍，從460到1199之間的執(zhí)行日志，就是給表binlog_test增加數(shù)據(jù)的日志，因此只要重復(fù)執(zhí)行這之間的操作，就可以還原數(shù)據(jù)了。

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

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

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

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

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

其它命令的含義為：

• "-d test"的意思是，指定數(shù)據(jù)庫(kù)為test（binlog_test表在test數(shù)據(jù)庫(kù)中）
• "|"是管道符
• "mysql -uroot -proot test"是mysql的賬號(hào)密碼、登陸目標(biāo)數(shù)據(jù)庫(kù)
• "-uroot"：賬號(hào)是root
• "-proot"：密碼是root
• "test"：數(shù)據(jù)庫(kù)是test 

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

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

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

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

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

這樣就可以還原數(shù)據(jù)了。

其它筆記

1.如果使用truncate刪除表的數(shù)據(jù)，binlog中也會(huì)新增幾行記錄，記錄truncate操作，樣例如下：

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

如果想恢復(fù)數(shù)據(jù)，就需要找到最開(kāi)始插入數(shù)據(jù)的Pos、到truncate之前的End_log_pos，然后重新執(zhí)行這期間的命令。

2.增刪改操作會(huì)記錄到binlog中，慢查詢sql也可以記錄到慢查詢log中，不過(guò)也需要自己修改配置文件開(kāi)啟慢查詢?nèi)罩尽?/p>

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論