项目九 维护教学管理系统数据库

项目九维护教学管理系统数据库项目目标①了解用户与权限的作用。②掌握CREATEUSE创建用户。③掌握GRANT授予用户权限。④掌握数据库的备份与恢复方法。① 能够备份和管理数据库。② 能够管理数据库用户。① 提高安全意识和法治意识。② 具有强烈的社会责任感。③ 具有良好的职业道德和职业品格。能力目标知识目标素养目标目录CONTENTS任务9.1管理用户任务9.2任务9.3拓展训练管理权限备份和恢复教学管理系统数据库维护乡村振兴助农电商平台数据库拓展阅读维护国家安全、加强个人信息保护项目情境项目情境目前，教学管理数据库拥有一个超级用户root，前期的开发工作是用root超级用户登录完成的。为了更好的维护教学管理系统数据库，控制数据库操作人员的访问与操作范围。需要为教学管理系统创建用户并授予相应的权限。任务9.1管理用户子任务9.1.1创建用户任务描述为教学管理系统创建6个用户。要求如下：分别创建2个没有密码的用户u1和u2，机地址都为localhost。然后分别创建2个有密码的用户，用户“operator1”的密码为“123456”，用户“operator2”的密码为“abc”最后，同时创建2个用户“operator3”和“operator4”，其中用户“operator3”的密码为“333333”，用户“operator4”的密码为“555555”子任务9.1.1创建用户知识引入使⽤createuser创建用户的最简⽅式创建用户语法如下：CREATEUSER[IFNOTEXISTS]账户名[用户身份验证选项][,账户名[用户身份验证选项]]…[WITH资源控制选项][密码管理选项|账户锁定选项]子任务9.1.1创建用户知识引入CREATEUSER语句选项默认值表选项默认值用户身份验证选项由default_authentication_plugin系统变量定义的插件进行身份验证加密连接协议选项NONE资源控制选项N（表示无限制）密码管理选项PASSWORDEXPIREDEFAULT用户锁定选项ACCOUNTUNLOCK说明:CREATEUSER可以一次创建多个用户，多个用户之间使用逗号分隔。账户名是由“用户名@主机地址”组成。其余选项在创建用户时，若未设置则使用默认值。用户名的设置不能超过32个字符，且区分大小写，但是主机地址不区分大小写。用户身份验证选项的设置仅适用于其前面的用户名，可将其理解为某个用户的私有属性。其余的选项对声明中的所有用户都有效，可以将其理解为全局属性。子任务9.1.1创建用户任务实现在创建用户时，若不指定主机地址、密码以及相关的用户选项，则表示此用户在访问MySQL服务器时，不限定客户端、不需要密码并且没有任何限制。host的值为“%”表示任何主机，其值为localhost时，表本地主机，其值为空字符串（‘’）时，表示所有客户端。（1）添加用户u1和用户u2CREATEUSER'u1'@'localhost';CREATEUSER'u2'@'localhost';查看用户是否已创建SELECThost,userFROMmysql.user;子任务9.1.1创建用户任务实现（2）创建两个含有密码的用户，其中用户“operator1”的密码为“123456”，用户“operator2”的密码为“abc”CREATEUSER'operator1'@'localhost'IDENTIFIEDBY'123456';CREATEUSER'operator2'@'localhost'IDENTIFIEDBY'abc';（3）同时创建多个用户，其中用户“operator3”的密码为“333333”，用户“operator4”的密码为“555555”CREATEUSER'operator3'@'localhost'IDENTIFIEDBY'333333','operator4'@'localhost'IDENTIFIEDBY'555555';子任务9.1.2删除用户任务描述删除用户operator4。知识引入在MySQL中经常会创建多个普通用户管理数据库，但如果发现某些用户是没有必要的，就可以将其删除，通常删除用户的方式采用MySQL提供的专门SQL语句。语法如下：DROPUSER[IFEXISTS]账户名[,账户名]…;（1）MySQL5.x版本之后DROPUSER语句可以同时删除一个或多个MySQL中的指定用户，并会同时从授权表中删除账户对应的权限行。（2）在MySQL5.x之前的版本中，在删除用户前必须先回收用户的权限。其中，账户名与创建用户的格式相同，由“用户名@主机地址”组成。子任务9.1.2删除用户任务实现方法1：DROPuser'operator4'@'localhost';方法2：DELETEFROMmysql.userWHEREuser='operator4';

删除用户后要刷新权限，需要将新加入的用户写入到权限表中，即更新granttable。使用下面语句:FLUSHPRIVILEGES;子任务9.1.3修改用户名任务描述将用户operator3的用户名修改为op3,来源主机为localhost。知识引入修改⽤户名和来源主机，语法格式：RENAMEUSER旧用户名1TO新用户名1[,旧用户名2TO新用户名2]…说明：（1）RENAMEUSER在为用户重命名时，旧用户名与新用户名之间使用TO关键字连接。（2）同时为多个用户重命名时使用逗号（,）进行分割。（3）被重命名的旧用户不存在或新用户名已存在时，系统会报错。子任务9.1.3修改用户名任务实现将用户operator3的用户名修改为op3,的脚本如下：RENAMEUSER'operator3'@'localhost'TO'op3'@'localhost';查看是否修改成功，运行下面语句，结果如图所示。SELECThost,userFROMmysql.user;子任务9.1.4修改密码任务描述“operator2”用户已经创建成功，为了保证数据库的安全性，需要为用户“operator2”设置密码“12345678”。知识引入MySQL中的用户密码一旦丢失就需要及时进行修改，root用户具有最高的权限可以修改自己的密码、普通用户的密码，而普通用户只能修改自己的密码。1.使用GRANTUSAGE语句修改GRANTUSAGEON*.*TO'username'@'localhost'IDENTIFIEDBY[PASSWORD]'new_password';子任务9.1.4修改密码知识引入2.使用UPDATE语句修改密码UPDATEmysql.userSETPassword=PASSWORD('new_password')WHEREUser='username'ANDHost='hostname';3.使用SET语句修改SETPASSWORDFOR'username'@'localhost'=PASSWORD('new_password');子任务9.1.4修改密码任务实现为用户“operator2”设置密码“12345678”的方法有3个。方法一：使用GRANTUSAGE语句修改GRANTUSAGEON*.*TO'operator2'@'localhost'IDENTIFIEDBY'12345678';方法二：使用UPDATE语句修改密码UPDATEmysql.userSETPassword=PASSWORD('12345678')WHEREUser='operator2'ANDHost='localhost';方法三：使用SET语句修改SETpasswordFORoperator2@localhost=password('12345678');任务9.2管理权限子任务9.2.1授予权限任务描述授予operator2用户jxgl.departments表（系部表）的SELECT权限，以及插入记录的权限。知识引入1.了解用户权限权限信息根据其作用范围，分别存储在mysql数据库中的不同数据表中。当MySQL启动时会自动加载这些权限信息，并将这些权限信息读取到内存中。子任务9.2.1授予权限知识引入数据表描述user保存用户被授予的全局权限db保存用户被授予的数据库权限tables_priv保存用户被授予的表权限columns_priv保存用户被授予的列权限procs_priv保存用户被授予的存储过程权限proxies_priv保存用户被授予的代理权限子任务9.2.1授予权限知识引入根据权限的操作内容可将权限大致分为数据权限、结构权限以及管理权限。分类权限权限级别描述数据权限SELECT全局、数据库、表、列SELECTUPDATE全局、数据库、表、列UPDATEDELETE全局、数据库、表DELETEINSERT全局、数据库、表、列INSERTSHOWDATABASES全局SHOWDATABASESSHOWVIEW全局、数据库、表SHOWCREATEVIEWPROCESS全局SHOWPROCESSLIST子任务9.2.1授予权限知识引入分类权限权限级别描述结构权限DROP全局、数据库、表允许删除数据库、表和视图CREATE全局、数据库、表创建数据库、表CREATEROUTINE全局、数据库创建存储过程CREATETABLESPACE全局允许创建、修改或删除表空间和日志文件组CREATETEMPORARYTABLES全局、数据库CREATETEMPORARYTABLECREATEVIEW全局、数据库、表允许创建或修改视图ALTER全局、数据库、表ALTERTABLEALTERROUTINE全局、数据库、存储过程允许删除或修改存储过程INDEX全局、数据库、表允许创建或删除索引子任务9.2.1授予权限知识引入分类权限权限级别描述管理权限REPLICATIONSLAVE全局允许复制从服务器读取的主服务器二进制日志事件SHUTDOWN全局允许使用mysqladminshutdownLOCKTABLES全局、数据库允许在有SELECT表权限上使用LOCKTABLES子任务9.2.1授予权限知识引入2.授予权限GRANT权限类型[字段列表][,权限类型[字段列表]]...ON[目标类型]权限级别TO账户名[用户身份验证选项][,账户名[用户身份验证选项]]...[REQUIRE连接方式][WITH{GRANTOPTION|资源控制选项}]说明：权限类型：指的就是SELECT、DROP、CREATE等权限。字段列表：用于设置列权限。目标类型：默认为TABLE，表示将全局、数据库、表或列中的某些权限授予给指定的用户。其他值为FUNCTION（函数）或PROCEDURE（存储过程）。权限级别：用于定义全局权限、数据库权限和表权限。添加GRANTOPTION：表示当前账户可以为其他账户进行授权。其余各参数均与CREATEUSER中的用户选项相同，这里不再赘述。子任务9.2.1授予权限任务实现授予operator2用户jxgl.departments表（系部表）的SELECT权限，以及插入记录的权限，执行如下语句：GRANTSELECT,INSERTONjxgl.departmentsTO'operator2'@'localhost';

新建连接用operator2用户连接数据库，输入密码12345678。分别查询系部表（departments）和班级表（class），查看是否成功。子任务9.2.2查看权限任务描述查看root用户和operator2用户的授权情况。知识引入查看root用户语句：SHOWGRANTSFOR'root'@'localhost';子任务9.2.2查看权限知识引入operator2用户的授权情况，结果如图所示。SHOWGRANTSFOR'operator2'@'localhost';说明：（1） ALLPRIVILEGES表示除GRANTOPTION（授权权限）和PROXY（代理权限）外的所有权限。（2） USAGE表示没有任何权限。（3） ON后的*.*表示全局级别的权限，即MySQL服务器下的所有数据库下的所有表，‘’@‘’表示任何主机中的匿名用户。子任务9.2.3回收权限任务描述回收用户operator2对系部表（departments）的插入权限。知识引入在MySQL中，为了保证数据库的安全性，需要将用户不必要的权限回收。MySQL用于回收指定用户的权限的语句为REVOKE。子任务9.2.3回收权限知识引入1.回收指定用户的指定权限REVOKE权限类型[(字段列表)][,权限类型[(字段列表)]]…ON[目标类型]权限级别FROM账户名[,账户名]…2.回收所有权限以及可为其他用户授权的权限REVOKEALL[PRIVILEGES],GRANTOPTIONFROM账户名[,账户名]…3.回收用户的代理权限REVOKEPROXYON账户名FROM账户名1[,账户名2]…子任务9.2.3回收权限任务实现针对任务要求，回收用户operator2对系部表的插入权限语句如下：REVOKEINSERTONjxgl.departmentsFROM'operator2'@'localhost';执行完毕以后，查看用户operator2权限，结果如图所示。SHOWGRANTSFOR'operator2'@'localhost';任务9.3备份和恢复教学管理系统数据库子任务9.3.1备份数据库任务描述(1)备份教学管理系统数据库，具体要求如下：①备份指定的数据库，或者此数据库中某些表。②备份指定的一个或多个数据库。③备份所有数据库。(2)使用Navicat备份教学管理系统数据库子任务9.3.1备份数据库知识引入1.备份/恢复策略对于一个DBA来说，定制合理的备份策略无疑是很重要的，以下是我们在进行备份或恢复操作时需要考虑的一些因素。（1）确定要备份的表的存储引擎是事务型还是非事务性，两种不同的存储引擎备份方式在处理数据一致性方面是不太一样的。（2）确定使用全备份还是增量备份。全备份的优点是备份保持最新备份，恢复的时候可以花费更少的时间；缺点是如果数据量大，将会花费很多的时间，并对系统造成较长时间的压力。增量备份则恰恰相反，只需要备份每天的增量日志，备份时间少，对负载压力也小；缺点就是恢复的时候需要全备份加上次备份到故障前的所有日志，恢复时间会长些。子任务9.3.1备份数据库知识引入（3）可以考虑采取复制的方法来做异地备份，但是，复制不能代替备份，它对数据库的误操作也无能为力。（4）要定期做备份，备份的周期要充分考虑系统可以承受的恢复时间。备份要在系统负载较小的时候进行。（5）确保MySQL打开log-bin选项，有了BINLOG，MySQL才可以在必要的时候做完整恢复，或基于时间点的恢复，或基于位置的恢复。（6）要经常做备份恢复测试，确保备份是有效的，并且是可以恢复的。子任务9.3.1备份数据库知识引入2.逻辑备份在MySQL里面，逻辑备份的最大优点是对于各种存储引擎，都可以用同样的方法来备份；而物理备份则不同，不同的存储引擎有着不同的备份方法。因此，对于不同存储引擎混合的数据库，用逻辑备份会更简单一些。下面将详细介绍逻辑备份以及相应的恢复方法。子任务9.3.1备份数据库知识引入1）使用mysqldump命令备份在MySQL中，有以下3种方法来调用mysqldump：

(1)备份指定的数据库，或者此数据库中某些表。语法：mysqldump[-h主机名-P端口-u用户名-p密码]db_name[tables]说明：db_name是要备份的数据库名，tables表示数据库中的表名。

(2)备份指定的一个或多个数据库。语法：

mysqldump[-h主机名-P端口-u用户名-p密码]--databaseDB1[DB2DB3...]说明：--database后面至少指定一个数据库名，如果多个数据库用空格隔开

(3)备份所有数据库。语法：

mysqldump[-h主机名-P端口-u用户名-p密码]--all--databases子任务9.3.1备份数据库知识引入2）使用mysqlhotcopy工具快速备份如果备份时不能停止MySQL服务器，可以采用mysqlhotcopy工具。mysqlhotcopy工具的备份方式比mysqldump命令快。下面为读者介绍mysqlhotcopy工具的工作原理和使用方法。mysqlhotcopy工具是一个Perl脚本，主要在Linux操作系统下使用。mysqlhotcopy工具使用LOCKTABLES、FLUSHTABLES和cp来进行快速备份。其工作原理是：先将需要备份的数据库加上一个读操作锁，然后用FLUSHTABLES将内存中的数据写回到硬盘上的数据库中，最后把需要备份的数据库文件复制到目标目录。使用mysqlhotcopy的命令如下：[root@localhost~]#mysqlhotcopy[option]dbname1dbname2…backupDir子任务9.3.1备份数据库知识引入3.使用Navicat备份数据库借助工具Navicat可以备份数据库。选择要备份的数据库,点击工具栏“备份”，然后点击下方“新建备份”，如图所示。子任务9.3.1备份数据库知识引入如图所示，在打开的“新建备份”窗体，可以直接选择“备份”按钮，此时会备份出一个默认名称的数据库备份文件。子任务9.3.1备份数据库知识引入在“对象选择”选项卡中，还可以设置要备份的对象类型。在这里我们使用默认设置。子任务9.3.1备份数据库知识引入如果想指定备份文件的名称，可以点击“高级”选项卡，如图所示，然后勾选“使用指定文件名”选项，并在下方文本框输入备份文件的名称。子任务9.3.1备份数据库任务实现(1)备份教学管理系统数据库按下快捷组合键win+r，输入cmd，运行。进入mysql所在的bin目录下，如：cdC:\ProgramFiles\MySQL\MySQLServer8.0.29\bin①备份数据库jxglmysqldump-uroot-prootjxgl>jxgl.sql②备份数据库jxgl下的表score表mysqldump-uroot-prootjxglscore>score.sql③备份数据库jxgl下的表class表和students表：

mysqldump-uroot-prootjxglclassstudents>class_stu.sql子任务9.3.1备份数据库任务实现

(2)使用Navicat备份教学关系系统数据库选择“jxgl”数据库,点击工具栏中的“备份”，然后点击下方“新建备份”。打开“新建备份”窗口，直接点击“高级”选项卡，勾选“使用指定文件名”选项，并在下方文本框输入“jxgl”。

子任务9.3.1备份数据库任务实现点击“备份”按钮。待进度条执行完毕后点击“关闭”按钮。子任务9.3.2恢复数据库任务描述数据库管理员上午10：00将教学管理系统数据库的系部表备份，然后向系部表添加2条记录。中午12：00点数据库发生故障，完全恢复数据库jxgl。知识引入

管理员的非法操作和计算机的故障都会破坏数据库文件。当数据库遭到这些意外时，可以通过备份文件将数据库还原到备份时的状态。这样可以将损失降低到最小。

管理员通常使用mysqldump命令将数据库中的数据备份成一个文本文件。通常这个文件的后缀名是sql。需要还原时，可以使用mysql命令来还原备份的数据。

备份文件中通常包含CREATE语句和INSERT语句。mysql命令可以执行备份文件中的CREATE语句和INSERT语句。通过CREATE语句来创建数据库和表。通过INSERT语句来插入子任务9.3.2恢复数据库知识引入1.完全恢复mysqldump的恢复也很简单，将备份作为输入执行即可，具体语法如下：mysql–uroot–pdbname<bakfile注意，将备份恢复后数据并不完整，还需要将备份后执行的日志进行重做，语法如下：mysqlbinlogbinlog-file|mysql-uroot–p***子任务9.3.2恢复数据库知识引入2.基于时间点恢复由于误操作，比如误删除了一张表，这时使用完全恢复是没有用的，因为日志里面还存在误操作的语句，我们需要的是恢复到误操作之前的状态，然后跳过误操作语句，再恢复后面执行的语句，完成我们的恢复。这种恢复叫不完全恢复，在MySQL中，不完全恢复分为基于时间点的恢复和基于位置的恢复。示例9-1：（1）如果上午10点发生了误操作，可以用以下语句用备份和BINLOG将数据恢复到故障前：mysqlbinlog--stop-date="2022-05-209:59:59"/var/log/mysql/bin.123456|mysql-uroot–pmypwd（2）跳过故障时的时间点，继续执行后面的BINLOG，完成恢复。mysqlbinlog--start-date="2022-05-2010:01:00"/var/log/mysql/bin.123456|mysql-uroot-pmypwd\子任务9.3.2恢复数据库知识引入3.基于位置恢复和基于时间点的恢复类似，但是更精确，因为同一个时间点可能有很多条SQL语句同时执行。示例9-2：（1）在shell下执行如下命令：mysqlbinlog--start-date="2022-04-209:55:00"--stop-date="2022-04-2010:05:00"/var/log/mysql/bin.123456>/tmp/mysql_restore.sql该命令将在/tmp目录创建小的文本文件，编辑此文件，找到出错语句前后的位置号，例如前后位置号分别是368312和368315。（2）恢复了以前的备份文件后，应从命令行输入下面内容：mysqlbinlog--stop-position="368312"/var/log/mysql/bin.123456\|mysql-uroot-pmypwdmysqlbinlog--start-position="368315"/var/log/mysql/bin.123456\|mysql-uroot-pmypwd\子任务9.3.2恢复数据库知识引入4.用Navicat还原数据库首先，新建一个数据库。然后展开数据库，鼠标右键选择下方“备份”，在弹出的右键菜单中选择“还原备份从…”。子任务9.3.2恢复数据库任务实现按下快捷组合键win+r，输入cmd，运行。进入mysql所在的bin目录下，如：cdC:\ProgramFiles\MySQL\MySQLServer8.0.29\bin（1）上午10点，备份数据库jxglmysqldump-uroot–p–l–Fjxgl>jxgl.dmp说明：

-l参数表示给所有表加读锁

-F表示生成一个新的日志文件此时，jxgl中departments表的数据如下：select*fromdepartments;子任务9.3.2恢复数据库任务实现（2）10点半备份完毕，然后，插入新的数据：

insertintodepartmentsvalues('D005','机电技术');QueryOK,1rowaffected(0.08sec)insertintodepartmentsvalues('D006','现代服务');QueryOK,1rowaffected(0.05sec)（3）12点，数据库突然故障，数据无法访问。需要恢复备份：

mysql-uroot-pjxgl<jxgl.dmp恢复后的数据如下：

SELECT*FROMdepartments;子任务9.3.2恢复数据库任务实现④使用mysqlbinlog恢复自mysqldump备份以来的BINLOG。mysqlbinloglocalhost-bin.000015|mysql-uroot–pjxgl查询完全恢复的数据如下：+------+----------+|d_no|d_name|+------+----------+|D001|信息技术||D002|装备技术||D003|工商管理||D004|基础部||D005|机电技术||D006|现代服务|+------+----------+6rowsinset(0.07sec)至此，数据库完全恢复。子任务9.3.3导出和导入数据表任务描述MySQL数据库中的表可以导出成文本文件、XML文件