一、 實驗目的

了解Mysql數據庫系統中數據恢復機制和主要方法。

二、 實驗環境

操作系統：Microsoft Windows 7旗艦版（32&64位）/Linux。
硬件：容量足以滿足MySQL 5.7（8.0）安裝及后續實驗的使用。
軟件：數據庫版本：MySQL 5.7（8.0）。

三、 實驗內容

（1） 利用配置文件開啟各種MYSQL日志

需要提醒：相關的日志文件都是通過設置生成，實際實驗過程中文件名可能和下面例舉名稱不同。注意調整！！！
① 查看MYSQL的錯誤日志，了解其中記錄的信息情況。

在my.ini的mysqld下可以查詢錯誤日志的配置文件名：
例如：log-error="Pc-202002191131.err"
打開安裝路徑下的 mysql 下的 data 下的 Pc-202002191131.err 錯誤日志文件，并利用文本編輯器打開和閱讀分析。

② 利用log-bin參數開啟二進制日志，并通過反復重新啟動MYSQL服務器查看產生的文件，以及二進制文件的索引文件。

在my.ini的mysqld下加上log-bin及其配置信息：
例如：log-bin="Pc-202002191131-bin"
然后重啟服務器。
在data下會生成新的二進制日志文件（Pc-202002191131-bin.000030，…..）和.Index文件（Pc-202002191131-bin.index）。

需要特別指出的是，二進制的文件個數與MySQL服務啟動有關，一般每啟動一次mysql，將會產生一個新的日志文件！！！（編號會遞增）

③ 利用general-log參數開啟通用查詢日志文件，在MYSQL中進行相關操作，查看日志文件中的記錄信息。
在my.ini的mysqld下將general-log=0 改為1，并設置general_log_file的文件名：
general-log=1
general_log_file="Pc-202002191131.log"
然后重啟服務器。
在data下會生成通用日志Pc-202002191131.log文件。利用文本編輯器打開閱讀。

④利用log-slow-queries參數開啟慢日志文件，在MYSQL中進行操作，查看日志文件中的記錄信息。
在my.ini的mysqld下加上log-slow-queries和long_query_time并設置：
slow-query-log=1
slow_query_log_file="Pc-202002191131-slow.log"
long_query_time=10
然后重啟服務器.
在data下會生成Pc-202002191131-slow.log文件。利用文本編輯器打開閱讀。

（2） 二進制文件相關操作（多次重新啟動MYSQL服務器）

① 利用有關命令查詢二進制日志文件信息。
確認二進制日志是否啟用

show variables like 'log_bin'

查看二進制日志文件存放的目錄：

show variables like 'datadir';

查看當前二進制日志文件（列表最后一個）信息：

show master status;

查看所有二進制日志文件信息：

show binary logs;

查看某個二進制文件內容，兩種方法：
a．通過MySQL的mysqlbinlog.exe工具命令行狀態下運行查看（注意：cmd命令行）

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqlbinlog.exe"
"C:\ProgramData\MySQL\MySQL Server 8.0\Data\Pc-202002191131-bin.000045"

b．通過MySQL的show binlog events命令查看

show binlog events in 'Pc-202002191131-bin.000045'

② 利用Reset master刪除所有二進制日志。（注意，本實驗不操作該命令！該命令會消除所有已存在日志，一般慎重使用！）

RESET MASTER

使用該命令，可以刪除所有二進制日志文件，新的日志文件名將從000001開始。執行SHOW BINARY LOGS命令查看當前日志文件，可見mysql日志已經更新。
③ 使用PURGE MASTER LOGS語句刪除指定日志文件。
#刪除指定日志名前所有的日志文件

PURGE {MASTER | BINARY} LOGS TO '日志名';

#刪除指定日期前的所有日志文件

PURGE {MASTER | BINARY} LOGS BEFORE '日期';

利用purge master logs to ‘****.000003’刪除指定number序號前的所有日志文件
利用purge master logs before ‘指定時間’刪除指定時間之前的所有日志文件。
④ 關閉當前使用的binary log，然后打開一個新的binary log文件，文件的序號加1。
flush logs命令的作用就是關閉當前使用的binary log,然后打開一個新的binary log文件,文件的序號加1

flush logs;

（3） 利用mysqlbinlog.exe命令通過二進制日志恢復數據操作

①實驗條件
首先要明確MySQL的二進制日志處于打開狀態。

show variables like 'log_bin'

a.如果返回NO，則需要開啟binlog日志。操作如下：
找到mysql安裝目錄，打開配置文件my.ini
在[mysqld]下添加：
log-bin=mysql-bin
保存后重啟mysql服務。此時在data目錄會生成mysql-bin.000001和mysql-bin.index。
注意：MySQL每次重啟服務會重新生成一個binlog二進制文件。
b.如果返回YES，則binlog日志已開啟。操作如下命令強制打開一個新的二進制文件，以便簡化內容和后續處理：

flush logs;

②實驗步驟
a.搭建測試環境和數據。

create database t1;
uset1;
create table ceshi(id int not null);
insert into ceshi values(1),(2),(3);
select *from ceshi;

b.此時模擬服務器定期備份任務,備份t1數據庫。(-l是備份期間加讀鎖，-F 刷新binlog日志，此時會重新生成一個binlog日志，名字是mysql-bin.000002)。

mysqldump.exe -uroot -p*****t1 -l -F >ceshi.sql

mysqldump.exe命令的選項使用請參閱后面附件內容！
這里也可以嘗試利用客戶端工具保存ceshi表的結構和數據至sql文件。

c.此時向數據庫中新插入三條數據。

insert into ceshi values(4),(5),(6);
select *from ceshi;

d.模擬數據庫破壞。

drop table ceshi;

select *from ceshi; 會報錯！

e.此時數據庫破壞后通過備份我們只能恢復備份那一刻的數據，但是在第c步新插入的三條數據不能通過備份恢復。可以通過binlog日志來恢復。

mysql.exe -uroot -p***** <ceshi.sql

也可以利用客戶端工具加載運行備份的sql文件來恢復ceshi表和前三行數據。

f.通過binlog恢復新插入的三條數據。
首先要確定二進制日志文件中后插入三條數據的位置，可以有兩種方法。
方法1：通過命令行 mysqlbinlog --no-defaults binlog路徑\mysql-bin.000002 |more查詢。
實際操作：

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqlbinlog" --no-defaults "C:\ProgramData\MySQL\MySQL Server 8.0\Data\Pc-202002191131-bin.000002" |more

分析判斷，只需要恢復上圖紅色標識位置812-1094這段即可。

方法2：可以在客戶端利用show binlog events命令查詢（推薦使用）
實際操作：

show binlog events in 'Pc-202002191131-bin.000002'

分析找到后三條數據追加命令的起始位置812，結束位置1094。(注意有明顯的事務標識來輔助提示位置判斷！！！)

最后命令行執行mysqlbinlog實施恢復操作。
命令格式：

mysqlbinlog.exe --start-position *** --stop-position ***bin.000002 | mysql.exe -uroot –p***

實際操作：

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqlbinlog.exe" --start-position 812--stop-position 1094"C:\ProgramData\MySQL\MySQL Server 8.0\Data\Pc-202002191131-bin.000002" |"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -uroot -p1234

成功！利用binlog恢復數據完成。

（4） 數據庫備份與還原

以下操作均是以命令行形式給出，實際實驗大家可以利用Navicat客戶端平臺來實施相同功能的任務要求。
① Mysqldump –uroot –p --all-database>all.sql 備份所有數據庫Mysqldump.exe

② Mysqldump –uroot –p --database test>test.sql 備份test數據庫
③ Mysqldump –uroot –p –l teststudent course sc>s_c_sc.sql 備份test數據庫中的學生、課程、選課成績表
④ Mysql –uroot –p <all.sql 還原所有數據庫
⑤ Mysql –uroot –p test<test.sql 還原test數據庫

（5） 導出文件與加載數據

① 利用SELECT ...INTO OUTFILE導出stud表到stud.txt文本文件中
② 利用LOAD DATA INFILE導入文本文件stud.txt的數據到新建的stud1空表中

附：mysqldump.exe命令選項說明