一、 实验目的
了解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命令选项说明