MySQL:实验二:数据控制

一．实验目的（1）掌握事务管理的基本原理以及事务的编程方法。（2）掌握数据转储备份和恢复的方法。（3）掌握并发控制的基本原理及其应用方法。（4）掌握自主存取控制权限的定义和维护方法。二．实验内容1.准备测试数据：准备一个表，并向表中插入预设数据备用。语句如下：create table student(sno char(9) primary key,sname varchar(20),ssex char(6) default '男');insert into student values('95001','张三','男');insert into student values('95002','李四','男');insert into student values('95003','孙丽','女');select * from student;在MYSQL命令行下的运行截图如下：2.关闭事务提交功能，语句如下：select @@autocommit;set autocommit=0;在MYSQL命令行下的运行截图如下：3..root账号下，创建两个用户：u1,u2，并分别授权，语句如下：create user ‘u1’@’%’ identified by ‘u1’;grant all privileges on *.* to u1;create user ‘u2’@’%’ identified by ‘u2’;grant all privileges on *.* to u2;flush privileges;4.验证read-uncommitted隔离级别下的“脏读”问题。（1）事务开始前设置事务的隔离级别，语句如下：select @@global.transaction_isolation;set global transaction_isolation=’事务隔离级别’;（2）打开两个客户端窗口，分别以u1,u2登录，执行事务语句如下：序号事务A（A窗口，用户u1）事务B（B窗口，用户u2）1select @@session.transaction_isolation;set session transaction_isolation=’read-uncommitted’;select @@session.transaction_isolation;2start transaction;select * from student;（观察查询结果）3start transaction；select * from student;4insert into student(sno,sname,ssex) values(‘95010’,王浩’,default);5select * from student;（观察查询结果）6select * from student;（观察查询结果，“脏读”和“不可重复读”）7delete from student where sno=’95011’;（幻读问题）8rollback;9select * from student;（观察查询结果）10commit;在MYSQL命令行下的运行截图格式如下（两窗口并排展示）：5.验证read-uncommitted隔离级别下的“可提交读”问题。（1）事务开始前设置事务的隔离级别，语句如下：select @@transaction_isolation;set global transaction_isolation=’read-committed’;（2）打开两个客户端窗口，分别以u1,u2登录，执行事务语句如下：序号事务A（A窗口，用户u1）事务B（B窗口，用户u2）1select @@session.transaction_isolation;set session transaction_isolation=’read-committed’;select @@session.transaction_isolation;2start transaction;select * from student;（观察查询结果）3start transaction；select * from student;4insert into student(sno,sname,ssex) values(‘95011’,’李林’,default);5select * from student;（观察查询结果）6select * from student;（观察查询结果）8commit;9select * from student;（观察查询结果，“不可重复读”）10commit;在MYSQL命令行下的运行截图格式如下（两窗口并排展示）：6.验证read-uncommitted隔离级别下的“可重复读”问题。（1）事务开始前设置事务的隔离级别，语句如下：select @@transaction_isolation;set global transaction_isolation=’repeatable-read’;（2）打开两个客户端窗口，分别以u1,u2登录，执行事务语句如下：序号事务A（A窗口，用户u1）事务B（B窗口，用户u2）1select @@session.transaction_isolation;set session transaction_isolation=’repeatable-read’;select @@transaction_isolation;2start transaction;select * from student;（观察查询结果）3start transaction；select * from student;4insert into student(sno,sname,ssex) values(‘95012’,’张强’,default);5select * from student;（观察查询结果）6select * from student;（观察查询结果）7commit;select * from student;（观察查询结果）9insert into student(sno,sname,ssex) values(‘95012’,’张强’,default);幻读select * from student;（观察查询结果）10commit;11select * from student;（观察查询结果）在MYSQL命令行下的运行截图格式如下（两窗口并排展示）：2.数据库备份和恢复的相关操作（1）备份test数据库下student表的数据到D盘file文件夹下。语句如下：use testselect * from student into outfile ‘D:/file/student.txt’ fields terminated by ‘,’ optionally enclosed by ‘”’ lines terminated by ‘?’;在MYSQL命令行下的运行截图如下：（2）删除student表中部分数据，再使用SQL语句恢复student表。语句如下：load data infile ‘D:/file/student.txt’into table student fields terminated by ‘,’ optionally enclosed by ‘”’ lines terminated by ‘?’;在MYSQL命令行下的运行截图如下：（3）使用MYSQL自带客户端工具mysqldump备份student表、备份test数据库,（包括表结构和表数据，属于完全备份）。语句如下：mysqldump -h localhost -u root -proot test student>d:/file/student.sql（备份student表、DOS命令，最后不需要加分号）mysql -h localhost -u root -proot test<d:/file/student.sql（完全恢复student表到备份时刻状态）mysqldump -h localhost -u root -proot -d test student>d:/file/student.sql（只备份表结构）mysqldump -h localhost -u root -proot --databases test>d:/file/test.sql（备份test数据库）mysqldump -h localhost -u root -proot -d --databases test>d:/file/test.sql（备份test数据库结构）mysqldump -h localhost -u root -proot --all-databases>d:/file/all.sql(备份服务器所有库)在MYSQL命令行下的运行截图如下：（4）先删除student 表，使用客户端工具mysql恢复数据库（包括表结构和表数据）。语句如下：mysql -u root -p root test<d:/file/student.sql //恢复数据库test中的student表mysql -u root -p root test<d:/file/test.sql //恢复数据库test在MYSQL命令行下的运行截图如下：（5）使用mysqlimport恢复数据。select * from student into outfile ‘D:/file/course.txt’;mysqlimport -uroot -proot --replace test d:/file/course.txt（作用等同如下load data infile命令）load data infile 'D:/file/course.txt' into table course;在MYSQL命令行下的运行截图如下：

（6）利用二进制日志文件进行增量备份和恢复序号操作功能1mysql命令行下执行show variables like '%log_bin%';查看MySQL的二进制日志是否开启2打开文件my.ini，在该文件的 [mysqld] 语句下，添加以下log-bin = mysql-binlog重启mysql服务，开启二进制日志；mysql8默认开启。3mysql命令行下执行set binlog_format = statement;修改日志记录的格式为语句格式，默认row格式4DOS下执行mysqladmin -uroot -proot flush-logs或mysql命令行下执行flush logs;归档现有日志文件，生成新日志文件，新日志文件编号增15DOS下执行mysqlbinlog --no-defaults binlog.000798>d:/file/bin_log000798.txtDOS命令，导出归档日志文件，查看了解日志文件结构6DOS下执行mysqldump -h localhost -u root -proot test student>d:/file/student.sql完全备份student 表（记录student表状态）7修改student 表数据记录student 表状态8flush logs;记录归档日志时刻和日志文件编号9mysql命令行下执行，向student 表插入数据，并提交记录student 表状态10mysql命令行下执行flush logs;记录归档日志时刻和日志文件编号11假设故障发生（删除student表）12DOS下执行mysql -h localhost -u root -proot test <d:/file/student.sql恢复student表到第6步备份状态13mysql命令行下：查询student 表验证结果14DOS下执行mysqlbinlog binlog.000798|mysql -uroot -proot增量恢复到第8步日志文件归档时刻的数据库状态15mysql命令行下：查询student 表验证结果16DOS下执行mysqlbinlog binlog.000798|mysql -uroot -proot继续增量恢复到第10步日志文件归档时刻的数据库状态17mysql命令行下：查询student 表验证结果18mysql命令行下：reset master;清除日志在MYSQL命令行下的运行截图如下（若有多张图，请说明每张图的功能）：开启二进制日志，并修改其记录格式为statement格式，如下图：生成新日志文件，如下图：导出归档日志，完全备份student表，如下图：修改student表数据（我选择了清空数据），记录归档日志；插入数据，记录归档日志，如下图：发生故障（删除student表），查询student表不存在，如下图：恢复备份student表备份状态，并验证查询，如下图：增量恢复日志文件，并查询student表（此时是修改表后插入新数据状态）；继续增量恢复并查询（此时是发生故障删除表的状态），如下图：清除日志，回到最初状态，如下图：3.数据库安全性相关操作（1）以root账号登录，创建用户T1，T2。create user T1;create user T2;（2）授予用户T1对student,course表的所有权限，并可给其他用户授权。以用户T1登录，查看该用户的权限情况，验证权限的分配是否有效。（此操作序列自己设计）grant all on student to T1 with grant option;grant all on course to T1 with grant option;查看用户T1的权限：show grants for T1;分配有效，如下图：（3）创建角色app_write，并授予该角色对所有数据库的所有权限，激活该角色。create role app_write;grant all on *.* to app_write;set global activate_all_roles_on_login=ON;（永久激活）或set default role app_write toT2;（临时激活，需要T2重新登录）show grants for app_write;（4）将角色app_write授予用户T2。以用户T2登录，查看该用户的权限情况，验证角色权限的分配是否有效。（此操作序列自己设计）grant app_write to T2;show grants for T2;select current_role();（5）撤销T1,T2用户给所授予的权限，撤销app_write角色所拥有的权限。，验证相关权限是否已经失效。revoke all from T1;revoke app_write from T2;revoke all on *.* from app_write;相关权限已失效，验证如下：三．心得体会1.本次实验项目的收获如下：（1）学习到了事物管理的基本原理以及事物的不同隔离级别：可序列化，可重复读，提交读和不可重复读；本次实验主要练习了后三种级别下的进行并发操作时带来的数据不一致性问题（丢失修改，不可重复读和读“脏”数据），产生数据的不一致性问题的主要原因是并发操作破坏了事物的隔离级别；（2）练习并掌握了数据转储备份和恢复的方法：第一种是直接备份test数据库下的表导出（into outfile）到D盘file文件夹下,删除表中部分数据后使用SQL语句（load data inflie ‘D:/file/student.txt’ into table student）恢复student表数据；第二种是用mysqldump（MySQL自带客户端工具）备份表和数据库（完全备份），删除表后，使用mysql或mysqlimport相关语句恢复数据库和相应数据；第三种是利用二进制日志文件进行增量备份和恢复，归档现有日志文件，生成新日志文件（实现增量备份），当出现故障时可以利用所归档的日志文件恢复到备份状态，从而恢复相应数据；（3）熟悉了数据库安全保护的存取控制机制，主要包括用户权限定义（由数据库对象和操作类型两个要素组成）和合法权限检查两个部分；存取控制方法有自主存取控制和强制存取控制，本次实验练习的是自主存取控制方法，其主要通过SQL的GRANT语句（向用户授予权限）和REVOKE语句（收回已经授予用户的权限）来实现。2.本次实验项目的不足之处如下：（1）验证事物隔离级别下的数据不一致性问题，需要兼顾多个CMD命令窗口，并且要兼顾各个操作语句之间的顺序，需要我们十分注意，一步一步小心地进行操作；（2）实现数据库备份和恢复相关操作时，使用到的SQL语句比较陌生，且输入语句较长，输入时如果不细心容易出现格式错误。实验过程中的错误以及调试解决方法（1）在重启MySQL80服务时，显示无法启动，查看发现本机安装路径根目录下没有data文件和my.ini配置文件，解决方法如下：先删除MySQL80 服务（sc delete MySQL80),在文件夹中右键显示本机隐藏的项目，找到ProgramDada文件，再找到相应mysql文件中的my.ini配置文件，复制到MySQL的安装路径根目录下；在CMD命令窗口相应路径下进行初始化（mysqld --initialize-insecure --user=mysql），就能在根目录下生成新的data文件，重新安装该服务并绑定my.ini配置文件（mysqld --install “MySQL80” --defaults-file=“C:\Program Files\MySQL\MySQL Server 8.0\my.ini”），此时再重启服务（net start MySQL80）则可以成功启动该服务。（2）在DOS下执行mysqlbinlog命令来导出归档日志文件时出现错误，如下图：推测是文件路径问题，修改路径，发现还是错误，如下图：再尝试给文件路径加上双引号，错误得以解决，可成功导出，如下图：（3）在撤销用户T1权限时，出现错误，如下图：提示语句错误，推测与所授权的权限内容有关，尝试修改语句，调试运行成功，如下图：