MySQL数据库应用项目教程 教案 项目6 数据库运维 教案

项目名称项目六数据库运维教学内容MariaDB的权限表及权限系统的工作原理，MariaDB的用户管理和权限管理；MariaDB的数据备份和恢复，数据备份和恢复的方法；MariaDB中二进制日志、错误日志、通用查询日志、慢查询日志、中继日志、事务日志的管理；架构读写分离数据库系统，搭建高可用数据库集群。授课时间授课学时教学目标学习目标1.了解MariaDB的权限系统。2.掌握数据备份、数据还原和数据迁移的方法。3.掌握MariaDB日志文件类型及应用场景。4.了解主/从数据库、高性能数据库及读写分离数据库的架构和应用场景。技能目标1.会根据工作场景合理地进行用户管理和权限分配。2.会根据工作场景选择数据备份和还原的方法,并进行数据备份、数据还原和数据迁移等操作。3.会根据工作场景操作相应的MariaDB日志文件。4.会搭建主/从数据库、读写分离数据库和高性能数据库等集群。素养目标1.能从数据库日志的操作中,学会合理分配时间。2.具有规划意识,合理规划个人职业发展。3.在搭建数据库的同时,培养灵活的思维能力和分析信息能力。学情分析通过对本项目的学习，了解MariaDB的权限表及权限系统的工作原理，MariaDB的用户管理和权限管理；掌握MariaDB的数据备份和恢复，数据备份和恢复的方法；熟悉MariaDB中的日志管理；掌握架构读写分离数据库系统，搭建高可用数据库集群。学习数据库运维有助于帮助学生加深对数据库的应用。教学方法学生通过课前预习、查阅资料，课堂上紧跟着老师的思路，对老师的演示要熟记，课后练习提升、反思总结等方法进行本章节的学习，有电脑条件的学生可以根据在课堂上所学习的内容，反复的操作实践验证；实训课上要加强训练，不懂的问题及操作过程，及时的向老师询问或是同学之间相互讨论。教学媒介教材、配套的PPT课件、多媒体教案等 教学过程教学环节教学内容教学引入安全管理是数据库管理系统中非常重要的组成部分之一,也是数据库中的数据被合理访问和修改的基本保证。MariaDB既是一个多用户数据库管理系统,又是功能强大的访问控制系统，可以为不同用户指定允许的权限。MariaDB提供了多种对数据进行备份和还原的方法。教授新知一、明确教学目标二、教学内容“数据库运维”6.1MariaDB的权限系统6.1.1权限表MariaDB中的权限表都存放在mysql数据库中,与权限相关的数据表有user视图、db表、tables_priv表、columns_priv表、procs_priv表。1.user视图和db表user视图和db表的字段说明如书中表6-1所示。(1)user视图。user视图是从global_priv表中导出的。(2)db表。在db表中存储的是用户对某个数据库的操作权限。2.tables_priv表、columns_priv表和procs_priv表6.1.2MariaDB权限系统的工作原理1.合法性验证MariaDB使用user视图中的Host、User和Password三个字段进行身份验证。2.权限验证和分配MariaDB服务器的权限验证过程如下图：6.1.3用户管理1.创建用户MariaDB的用户可以分为普通用户和root用户(超级管理员)。(1)使用SQL语句(GRANT和CREATEUSER)创建用户。使用GRANT语句创建新用户。语法格式:GRANTpriv_type[(column_list)][,priv_type[(column_list)]]... ON[object_type]priv_level TOuser_specification[user_options...][WITHGRANTOPTION];使用CREATEUSER语句创建新用户。语法格式:CREATE[ORREPLACE]USER[IFNOTEXISTS]user_specification[,user_specification]...[WITHresource_option[resource_option]...];(2)利用HeidiSQL图形界面创建用户。2.修改用户名语法格式:RENAMEUSERold_userTOnew_user[,old_userTOnew_user]...;3.修改用户密码语法格式:SETPASSWORD[FORuser]{PASSWORD(‘somepassword’)|‘encryptedpassword’};4.删除用户语法格式:DROPUSER[IFEXISTS]user_name[,user_name]..;6.1.4权限管理1.权限类型在MariaDB中常见的权限如下图：2.查看权限查看用户权限可以使用SQL语句、查询mysql.user视图或HeidiSQL图形界面3.授予权限在MariaDB中,可以在HeidiSQL图形界面或使用GRANT语句为用户授权。(1)权限的级别。①全局层级②数据库层级③表层级④列层级⑤子程序层级。(2)授权语句GRANT。在MariaDB中,必须是拥有GRANT权限的用户才可以执行GRANT语句。4.收回权限(1)收回用户的所有权限。(2)收回用户的指定权限。(3)REVOKE语句的严格性。(4)设置root密码。6.2MariaDB的数据备份和恢复6.2.1数据备份和恢复1.数据损失的因素可能造成数据损失的因素如下。系统故障、介质故障、计算机病毒、错误操作、服务器崩溃、自然灾害。2.数据备份的分类(1)按备份时数据库是否离线将数据备份划分：冷备、温备、热备。(2)按备份设计的数据范围将数据备份划分为：完全备份、增量备份、差分备份、按需备份。6.2.2数据备份的方法1.MariaDB中的备份方式MariaDB中的数据备份方式主要有物理备份和逻辑备份。2.MariaDB中的备份工具(1)mariadb-backup。(2)mariadb-dump。(3)InnoDB存储引擎的逻辑备份。(4)快照。3.使用mariadb-dump命令备份(1)备份数据库或数据表。(2)备份多个数据库。6.2.3数据恢复的方法1.使用命令恢复语法格式:mariadb-uuser-ppassword[db_name]<filename.sql;2.直接复制整个数据库目录6.2.4数据迁移1.使用语句导出(1)使用SELECT语句导出。基本语法格式:SELECT[列名]FROMtb_name[WHERE语句]INTOOUTFILE‘目标文件’[OPTION];(2)使用mariadb-dump命令导出文本文件。基本语法格式:mariadb-dump-uroot-ppassword-hhost-Tpathdb_name[tb_name[,tb_name]][OPTIONS](3)使用mariadb命令导出文本文件。基本格式:mariadb-uroot-ppassword[OPTIONS]-e|--execute=“SELECT语句”db_name>filename.txt2.使用语句导入(1)用LOADDATAINFILE方式导入文件。语法格式:LOADDATA[LOCAL]INFILE‘file_name’INTOTABLEtb_name[OPTION][IGNOREnumbersLINES][字段列表][SETcolumn=exp];(2)使用mariadb-import命令导入文本文件。语法格式:mariadb-import-uroot-ppassword[LOCAL]db_namefile_name[OPTIONS]3.用HeidiSQL导出和导入6.3MariaDB日志管理6.3.1二进制日志1.启动和设置二进制日志启动二进制日志,可以通过配置文件my.ini或f中的[mysqld]或[mariadb]组中添加选项的方式启动二进制日志。2.查看二进制日志基本语法格式:mariadb-binlog[options]log-files3.删除二进制日志(1)使用RESETMASTER语句删除二进制日志文件。语法格式:RESETMASTER;(2)使用PURGEBINARYLOGS语句删除日志。语法格式:PURGE{MASTER|BINARY}BINARYLOGSTO‘file_name’;或PURGE{MASTER|BINARY}BINARYLOGSBEFORE‘datetime’;4.使用二进制日志文件恢复数据库使用mariadb-binlog命令恢复数据。语法格式:mariadb-binlog[options]log-files|mariadb-uroot-ppassword6.3.2错误日志1.启动和设置错误日志(1)默认配置。(2)配置错误日志文件名。(3)设置log-basename选项配置错误日志的文件名。2.查看错误日志使用SHOWVARIABLES语句查看MariaDB服务器的错误日志的存储位置。6.3.3通用查询日志1.启动和设置通用查询日志(1)启动通用查询日志。(2)设置通用查询日志的文件名。(3)选择通用查询日志的输出目的地。(4)关闭通用查询日志。(5)禁用通用查询日志。2.查看、删除通用查询日志(1)查看通用查询日志。(2)删除通用查询日志。6.3.4慢查询日志1.启动和设置慢查询日志(1)慢查询日志的常用参数。(2)启动慢查询日志。(3)设置慢查询日志文件名。(4)选择慢查询日志的输出目的地。(5)关闭慢查询日志。2.查看、删除慢查询日志(1)查看慢查询日志。(2)删除慢查询日志。6.3.5中继日志、事务日志中继日志的相关参数如下。(1)relay_log={ON|OFF|PATH}:中继日志的功能开关,定义了中继日志存放的路径和文件名称。(2)relay_log_recovery={ON|OFF}:当从服务器从宕机状态恢复后,如果中继日志损坏,会导致日志中的部分语句无法被重放或者全部不能被重放。2.事务日志(1)redolog:提供数据重做功能,实现回滚操作。(2)undolog:不仅提供数据恢复功能,实现回滚操作，而且提供了恢复操作及MVCC功能。6.4搭建高性能数据库系统6.4.1架构读写分离数据库系统1.架构主/从数据库(1)主/从数据库系统的优势。①增加服务健壮性。②实现负载均衡。③实现数据异地备份。(2)主/从复制的原理。(3)搭建主/从数据库。2.读写分离数据库的架构(1)读写分离能提高数据库的性能。(2)读写分离带来的问题。①主/从复制延迟。②读写分离的分配机制。(3)搭建读写分离数据库。用MyCat作为数据库中间件构建的读写分离数据库。6.4.2搭建高可用数据库集群1.数据库集群和分布式数据库数据库集群是串联工作的,分布式数据库是并联工作的。2.数据库集群(1)业务分库。①JOIN操作问题。②事务问题。③成本问题。(2)分表。①垂直分表。②水平分表。(3)“分库分表”的实现。3.高可用数据库集群的搭建课堂小结本项目主要讲述了：(1)MariaDB权限系统的工作原理，用户、权限的概念。(2)使用语句和图形界面进行用户与权限的管理。(3)数据备份与恢复的方式，常用的数据备份与恢复的语句和命令。(4)