《第10章 管理和维护数据库-MySQL.pptx》由会员分享，可在线阅读，更多相关《第10章 管理和维护数据库-MySQL.pptx（146页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、课程主讲人：第10章 管理和维护数据库-MySQLMySQL数据库设计与应用主讲：张成叔微信（QQ）：7153265 微信公众号：张成叔第 10 章 管理和维护数据库工作情境和任务n数据库用户量和数据量都在急剧增多，用户的操作各不相同u系统管理员需要办理新生注册，毕业生离校u学生需要查看自己选修的课程信息和成绩信息u老师需要分析自己授课班级的成绩分布，以改方法和提高质量u辅导员要分析班级学生的成绩总评信息，为奖学金评定的依据n数据库开发人员需要为他们分别创建用户账户u赋予不同的权限来满足各自的需求工作情境和任务n如果系统管理员在对学生信息表进行管理u误删了重要的学生数据，为了挽回误操作造成的损
2、失u数据库管理人员需要对数据库进行数据备份u在出现操作事故后可以将之前的数据还原n用户管理和权限管理n备份数据和恢复数据知识和技能目标n能使用SQL语句创建和删除用户账户n能使用SQL语句授予和回收权限n能备份数据库数据n能恢复数据库数据本章重点和难点n权限的授予与回收n在不同计算机之间迁移数据库和数据知识技能结构图 数据库的安全管理2 2 存储过程1 1内容结构图nMySQL数据库安全管理u数据库用户u权限管理u数据的备份与恢复nMySQL数据库访问过程u经历服务器的验证连接和权限验证两个阶段u数据库权限u数据表权限u操作权限数据库的安全管理n用户要访问数据库u首先必须能连接数据库所在的My
3、SQL服务器u必须拥有登录MySQL服务器的用户名和密码nMySQL的访问控制分为两个阶段u第一个阶段：服务器验证是否允许连接u第二个阶段：连接成功后，验证每个请求是否具有实施的权限n例如：要查看表中的数据uMySQL会检查是否具有对这个表的SELECT权限u要执行某个存储过程，MySQL会检查是否具有该存储过程的执行权限用户管理n由系统管理员创建登录账户u给定用户名和登录密码u登录的位置和默认连接的数据库n系统管理员是root用户u拥有最高的权限u可以完成所有的操作u它的密码在安装MySQL服务器时设置 创建用户n用户名：创建用户的名字n主机名u指定创建用户所连接主机u可以：某个IP地址主机
4、名（如localhost）、某个IP段u也可包含：通配符%（任意个字符）、_（任意单个字符）分析分析 创建用户 语法语法n密码u用户对应的密码u在大多数SQL产品中，用户名和密码只由字母和数字组成分析分析 创建用户 语法语法nIDENTIFIED BY子句u可选子句u可以为账户指定一个密码u要在纯文本中指定密码，需忽略PASSWORD关键字分析分析 创建用户 语法语法nPASSWORD关键字u不想以明文发送密码u而且知道PASSWORD()函数返回给密码的混编值u则可以指定该混编值分析分析 创建用户 语法语法一起努力 共同提高 【演示示例10-1】创建数据库新用户【演示示例10-1】创建数据库
5、新用户n在MySQL数据库中n创建数据库新用户u用户名：test100u用户密码：123456需求需求 【演示示例10-1】创建数据库新用户参考代码参考代码 【演示示例10-1】创建数据库新用户n关键字localhost指定创建用户为MySQL服务器本地主机用户nMySQL验证用户的方式是用户名+主机nMySQL的用户信息存储在服务器自带的系统数据库mysql的user表中，创建新账户成功，将会在该表中添加一条新记录代码分析代码分析分析结果分析结果操作操作演示演示：创建数据库用户及查看用户：创建数据库用户及查看用户 【演示示例10-1】创建数据库新用户n使用CREATE USER必须拥有MyS
6、QL系统数据库的全局CREATE USER权限或INSERT权限n如果需要创建的用户已存在，则会提示出错n创建新用户后，可以测试用新用户和对应的密码登录扩展扩展 【演示示例10-1】创建数据库新用户一起努力 共同提高n用户名：表示要修改的用户名n主机名u指定用户所在的主机u可以是某个IP地址、主机名（如本地用户可以使用localhost）u通配符%（表示任意远程主机）分析分析 修改用户密码 语法语法n新密码u指需要修改指定用户名的新的明文密码n刷新权限u输入“FLUSH PRIVILEGES;”语句，刷新权限，修改结束分析分析 修改用户密码 语法语法n用户名u旧用户名为已经存在的MySQL用户
7、，新用户名为新的MySQL用户n必须拥有u全局CREATE USER权限或系统数据库MySQL的UPDATE权限n如果旧账户不存在或者新账户已存在，都将报错分析分析 修改用户名 语法语法n必须拥有u全局CREATE USER权限或系统数据库MySQL的DELETE权限n用于删除一个或多个MySQL账户，并取消其权限n被删除用户所创建的表、索引或其他数据库对象继续保留u因为MySQL并没有记录是对象的创建者分析分析 修改用户名 语法语法一起努力 共同提高n用户执行SQL语句uMySQL将逐级进行权限检查u看用户是否具有操作对象的SQL语句的执行权限nMySQL的权限管理u允许做权利范围以内的事情
8、，不允许越界u例如：只允许执行SELECT操作，那么就不能执行INSERT操作权限管理nMySQL中用户权限的五个层级u全局层级u数据库层级u表层级u列层级u子程序层级权限管理n全局层级u适用MySQL服务器中的所有数据库，存储在系统数据库mysql的user表中n数据库层级u适用给定数据库中的所有内容，该权限存储在系统数据库mysql的db表中n表层级u适用给定表中的所有列，该权限存储在系统数据库mysql的table_priv表中权限管理n列层级u适用给定表中的某列，该权限存储在系统数据库mysql的columns_priv表中n子程序层级uCREATE ROUTINE、ALTER ROU
9、TINE、EXECUTE和GRANT权限适用于自存储的子程序u这些权限可以授予全局层级和数据库层级uCREATE ROUTINE不可以被授予子程序层级权限管理nMySQL中包含的权限MySQL的权限nMySQL中包含的权限MySQL的权限nMySQL中包含的权限MySQL的权限一起努力 共同提高n权限u权限的名称，如SELECT、UPDATE等u给不同的对象授予权限的值也不相同分析分析权限授予 语法语法nN关键字uON关键字后面给出的是要授予权限的目标范围u可以是表(TABLE)、函数(FUNCTION)、存储过程(PROCEDURE)分析分析权限授予 语法语法n表名：表级权限，适用于指定数据
10、库中的所有表n*：u如果未选择数据库，意义同*.*，否则为当前数据库的数据库级权限分析分析权限授予 语法语法n*.*：全局权限，适用于所有的数据库和所有表n库名.*：指定数据库中的所有表分析分析权限授予 语法语法nTO子句u用来设定授权的对象u可以指定一个或多个用户分析分析权限授予 语法语法nWITH权限限制u指WITH GRANT OPTION子句uGARNT OPTION是指将自己的权限赋予其他用户分析分析权限授予 语法语法一起努力 共同提高【演示示例10-2】授予指定用户指定权限【演示示例10-2】授予指定用户指定权限n授予用户test100权限u在subject表上uSELECT权限需
11、求需求【演示示例10-2】授予指定用户指定权限参考代码参考代码【演示示例10-2】授予指定用户指定权限n使用root用户连接数据库nSELECT是分配给test100用户在subject表上的操作权限n在MySQL 8.0以上的版本创建用户及授权u需要先创建用户和设置密码，然后才能授权代码分析代码分析操作操作演示演示：授予指定用户指定权限：授予指定用户指定权限一起努力 共同提高【演示示例10-3】授予指定用户指定权限【演示示例10-3】授予指定用户指定权限n授予用户test100权限u在subject表上的SubjectName列uUPDATE权限需求需求【演示示例10-3】授予指定用户指定权
12、限参考代码参考代码【演示示例10-3】授予指定用户指定权限ntest100用户u可以查询SchoolDB数据库中subject表的数据u可以对表中的SubjectName列进行更新操作ntest100用户不能对其他列进行增加、修改、删除操作代码分析代码分析操作操作演示演示：授予指定用户指定权限：授予指定用户指定权限一起努力 共同提高【演示示例10-4】授予指定用户指定权限【演示示例10-4】授予指定用户指定权限n授予用户test100权限u在SchoolDB数据库中uSELECT权限需求需求【演示示例10-4】授予指定用户指定权限参考代码参考代码【演示示例10-4】授予指定用户指定权限ntes
13、t100用户可以查询SchoolDB中的所有表数据n该权限适用于uSchoolDB中所有已有的表u此后添加到SchoolDB数据库中的任何表代码分析代码分析操作操作演示演示：授予指定用户指定权限：授予指定用户指定权限一起努力 共同提高【演示示例10-5】授予指定用户指定权限【演示示例10-5】授予指定用户指定权限n授予用户test权限uSchoolDB数据库u所有的数据库权限需求需求【演示示例10-5】授予指定用户指定权限参考代码参考代码【演示示例10-5】授予指定用户指定权限ntest用户可以对SchoolDB数据库中的所有表实现所有的操作n和表权限类似u授予一个数据库权限也不意味着拥有另一
14、个权限u例如：某用户被授予可以创建新表和视图的权限，并不意味着可以访问它们，必须单独被授予SELECT权限代码分析代码分析操作操作演示演示：授予指定用户指定权限：授予指定用户指定权限一起努力 共同提高【演示示例10-6】授予指定用户指定权限【演示示例10-6】授予指定用户指定权限n创建用户test200n授予其权限u对所有数据库中所有表的CREATE、ALTER、DROP权限u创建新用户的权限需求需求【演示示例10-6】授予指定用户指定权限参考代码参考代码【演示示例10-6】授予指定用户指定权限n首先创建本地用户test200，并设置密码：pwdn*.*代表数据库中所有数据表nCREATE U
15、SER为创建用户权限代码分析代码分析操作操作演示演示：授予指定用户指定权限：授予指定用户指定权限一起努力 共同提高【演示示例10-7】授予指定用户指定权限【演示示例10-6】授予指定用户指定权限n授予test300ustudent表上的SELECT权限u并允许其将该权限授予给其他用户需求需求【演示示例10-7】授予指定用户指定权限参考代码参考代码【演示示例10-7】授予指定用户指定权限代码分析代码分析【演示示例10-7】授予指定用户指定权限n在root用户下u创建了test300与sam两个用户，并授予test300用户SELECT权限n以test300用户身份登录MySQLn打开Window
16、s命令行窗口，进入MySQL的安装目录下bin目录u使用test300用户连接数据库服务器ntest300用户只有查询SchoolDB数据库中student表的权利u可以把这个权限传递给其他用户，如test300将权限传递给sam操作操作演示演示：授予指定用户指定权限：授予指定用户指定权限一起努力 共同提高n各子句与授予权限功能相似n用于收回指定用户的指定权限分析分析权限收回 语法语法n回收该用户的所有权限分析分析权限收回 语法语法一起努力 共同提高【演示示例10-8】收回指定用户指定权限【演示示例10-8】收回指定用户指定权限n回收user1的权限u在subject表上uSELECT权限需求
17、需求【演示示例10-8】收回指定用户指定权限参考代码参考代码【演示示例10-8】收回指定用户指定权限nuser1用户对subject表的SELECT权限被回收u直接或间接依赖于它的所有权限也被回收代码分析代码分析操作操作演示演示：收回指定用户指定权限：收回指定用户指定权限一起努力 共同提高【技能训练10-1】管理用户和权限【技能训练10-1】管理数据库SchoolDB的用户和权限n熟练掌握使用SQL语句实现用户创建于管理的方法n熟练掌握使用SQL语句实现权限控制的方法技能目标技能目标需求分析需求分析n管理SchoolDB数据库的用户和权限【技能训练10-1】管理用户和权限n创建用户stud01
18、、stud02和test，密码分别为123、456和789n将用户test重命名为stud03n删除用户stud03n授予stud01对数据库SchoolDB中学生表的全部权限n授予stud01对数据库SchoolDB中成绩表的查询权限n授予stud02对数据库SchoolDB的所有权限需求需求【技能训练10-1】管理用户和权限n要熟练掌握命令行方式下用户创建、修改、重命名等命令u用户名一定要对应主机n用户一般授予能满足需求的最小权限即可关键步骤关键步骤【技能训练10-1】管理用户和权限n在为创建的用户分配权限时u一般限制用户的登录主机u通常限制程指定IP或内网IP段n初始化数据库的时候建议u
19、删除没有密码的用户u为每个用户设置满足密码复杂度的密码n定期清理不需要的用户，回收权限或删除用户安全安全提示提示【技能训练10-1】管理用户和权限一起努力 共同提高知识技能结构图第1章 创建和管理数据库 数据库的安全管理1 1 备份和恢复数据库2 2目 录内容结构图n目的：当数据库发生故障时可通过备份数据文件恢复数据n原因：u存储介质故障：保存数据文件的磁盘设备损坏u用户的错误操作：用户有意或无意删除了重要数据，甚至整个数据库u服务器的瘫痪：数据库服务器因为软件漏洞彻底瘫痪n备份方式u使用mysqldump备份u使用SQL命令备份数据表u使用mysql命令备份数据备份数据库nmysqldump
20、是MySQL提供的一个客户端工具u存储在MySQL的安装路径bin目录下nmysqldump命令执行后将产生一个SQL脚本文件u包含：数据库表的创建语句以及对应表数据的添加语句n具体执行步骤u先检查所需要备份数据的表结构，在对应的脚本文件中产生CREATE语句u然后检查数据内容，在脚本文件中生成INSERT INTO语句u如果后期需要还原数据，可利用该脚本文件重新创建表和添加表数据使用mysqldump备份n-u后的user表示用户名n-h后的host表示主机名n-p后的password表示用户密码u-p选项和密码之间不能有空格u如果是本地MySQL服务器，则-h选项可以省略分析分析使用mys
21、qldump备份数据库或者指定表 语法语法ndb表示需备份的数据库名utb1、tb2表示该数据库需备份的u可以选择多个表进行备份u数据库名与表名相互都用空格隔开，u如果要备份整个数据库，则可以省略表名分析分析使用mysqldump备份数据库或者指定表 语法语法n：表示要将备份的数据库或者表写入到备份文件中nfilename表示备份的文件名u一般使用.sql作为文件后缀名u如果需要保存在指定路径，则在这里可以指定具体路径u在该路径下不能有同名的文件，否则新的备份文件会覆盖原来的文件分析分析使用mysqldump备份数据库或者指定表 语法语法一起努力 共同提高 【演示示例10-9】使用命令备份数据
22、库【演示示例10-9】使用mysqldump命令备份数据库n使用mysqldump命令实现SchoolDB数据库的备份n将该数据库备份到D:backupn备份的文件名为SchoolDB_backup.sql需求需求 【演示示例10-9】使用命令备份数据库代码分析代码分析 【演示示例10-9】使用命令备份数据库n首先进入到MySQL安装路径的bin目录n执行备份操作，当前root账户密码为空关键步骤关键步骤操作操作演示演示：使用命令备份数据库：使用命令备份数据库一起努力 共同提高 【演示示例10-10】使用命令备份数据表【演示示例10-10】使用mysqldump命令备份数据表n使用mysqld
23、ump命令u备份SchoolDB数据库中学生信息表与课程表u备份到D:backupu备份的文件名：SchoolDB_student_subject_backup.sql需求需求 【演示示例10-10】使用命令备份数据表代码分析代码分析【演示示例10-10】使用命令备份数据表n首先进入到MySQL安装路径的bin目录n执行备份操作，当前root账户密码为空nstudent和subject表使用空格分隔关键步骤关键步骤操作操作演示演示：使用命令备份数据表：使用命令备份数据表一起努力 共同提高n-databases：要备份多个数据库u后面至少要指定一个数据库名称u多个数据库用空格隔开ndb1、db2
24、、db3表示要备份的多个数据库的名称分析分析使用mysqldump备份多个数据库 语法语法【演示示例10-11】备份多个数据库【演示示例10-11】使用mysqldump命令备份多个数据库n使用mysqldump命令u备份SchoolDB数据库和mysql数据库u将该数据备份到D:backupu备份的文件名：SchoolDB_mysql_backup.sql需求需求【演示示例10-11】备份多个数据库代码分析代码分析【演示示例10-11】备份多个数据库n首先进入到MySQL安装路径的bin目录n执行备份操作，当前root账户密码为空n数据库名使用空格分隔关键步骤关键步骤操作操作演示演示：使用命
25、令备份多个数据库：使用命令备份多个数据库一起努力 共同提高n-all-databases表示要备份数据库服务器中所有数据库分析分析使用mysqldump备份所有数据库 语法语法【演示示例10-12】备份所有数据库【演示示例10-12】使用mysqldump命令备份所有数据库n使用mysqldump命令u实现本地服务器所有数据库的备份u备份到D:backupu备份的文件名：all_backup.sql需求需求【演示示例10-12】备份所有数据库代码分析代码分析【演示示例10-12】备份所有数据库n首先进入到MySQL安装路径的bin目录n执行备份操作，当前root账户密码为空关键步骤关键步骤操作
26、操作演示演示：使用命令备份所有数据库：使用命令备份所有数据库一起努力 共同提高ncolumns为查询的列，*为查询全部ntablename是要查询的表名nfilename表示导出的外部文件名分析分析使用SQL命令备份数据表 语法语法nOPTION表示设置相应的选项u决定数据行在文件中存放的格式fields terminated bystring：字段的分隔符为字符串对象，默认为“t”lines starting bystring：每行开始的字符串符号，默认不使用任何字符lines terminated bystring：每行结束的字符串符号，默认为“n” fields escaped by c
27、har：设置转义字符的字符符号，默认使用“”分析分析使用SQL命令备份数据表 语法语法【演示示例10-13】SQL命令备份数据表【演示示例10-13】使用SQL命令备份数据表n使用SELECTINTO OUTFILE语句u导出SchoolDB数据库中学生表数据u备份到D:backupu备份的文件名：SchoolDB_student_data.txt需求需求【演示示例10-13】SQL命令备份数据表参考代码参考代码【演示示例10-13】SQL命令备份数据表n打开MySQL 8.0 Command Line Client命令客户端u登录后执行命令代码分析代码分析参考代码参考代码【演示示例10-13
28、】SQL命令备份数据表n需要在指定目录下进行u MySQL8.0配置文件默认在路径c:ProgramDataMySQLMySQL8.0代码分析代码分析操作操作演示演示：使用命令备份所有数据库：使用命令备份所有数据库一起努力 共同提高【演示示例10-14】SQL命令备份数据表【演示示例10-14】使用SQL命令备份数据表n使用SELECTINTO OUTFILE语句u导出SchoolDB数据库中学生表数据u备份到D:backupu备份文件名：SchoolDB_student01_data.txtu字段值之间的分隔符为“、”u如果是字符或者字符串，则用双引号括起值u每行的开始使用“”字符需求需求【
29、演示示例10-14】SQL命令备份数据表参考代码参考代码【演示示例10-14】SQL命令备份数据表操作操作演示演示：使用：使用SQL命令备份数据命令备份数据表表一起努力 共同提高n-e：表示执行后面的查询语句nDb：表示查询表数据所在的数据库分析分析使用mysql命令备份数据 语法语法一起努力 共同提高nDb：表示要还原的数据名称u为可选项，可以指定数据库名，也可以不指定u如果使用-all-databases备份所有数据库，则还原时无需指定数据库u如果指定了数据库，则数据库要先创建分析分析使用mysql命令恢复数据 语法语法n”字符开始需求需求【技能训练10-2】备份和恢复数据n使用LOAD DATA INFILE语句u还原D:backupSchoolDB_score_data.txtu到SchoolDB数据库u新表score_new中需求需求【技能训练10-2】备份和恢复数据n执行数据备份或恢复操作时u当不使用默认目录，需要修改配置文件u保存配置文件后，重新启动MySQL数据服务器n不同操作系统下的目录分隔符存在差异，注意区分n数据库备份在项目中极其重要u要做到定时备份与即时故障恢复关键步骤关键步骤【技能训练10-2】备份和恢复数据知识技能结构图一起努力 共同提高