MySQL数据库运维与管理PPT完整全套教学课件

数据库运维与管理项目1了解数据库运维工作项目1了解数据库运维.pptx项目2安装和配置MySQL数据库.pptx项目3管理MySQL权限与安全.pptx项目4分析MySQL日志.pptx项目5备份与恢复MySQL数据库.pptx项目6监控、测试并优化MySQL性能.pptx项目7MYSQL复制.pptx项目8搭建及运维MySQLCluster.pptx项目9结合Redis的MySQL运维.pptx项目10数据库自动化运维.pptx全套可编辑PPT课件教学目标知识目标了解数据管理发展历史掌握数据库基本概念了解数据库的类型了解数据库运维职位及上岗要求能力目标能根据岗位搜索并分析岗位任职要求能说出数据库运维岗位的任职要求能根据不同应用场景完成对数据库的选型能说出市场上常见的数据库运维平台产品素养目标信息搜索能力信息分析能力信息总结能力职业素养分析思政目标民族自豪感国产软件振兴的使命担当项目1了解数据库运维工作项目场景：

天天电器商场是销售家电、手机数码等各种电器类产品的省内大型连锁商场，全省有100多家实体商场店面。为了更好地服务顾客，天天电器商场建立了从采购到销售、到售后服务一整套完善的业务处理系统。随着业务变得越来越复杂，数据量也变得越来越庞大，在数据库的长期运行过程中，产生了各式各样的无效数据，此外，数据库的结构也变得臃肿，数据查询效率低，数据库系统不堪重负，常出现数据损坏，无法访问等异常现象。公司管理层经过研究，决定招聘2~3名数据库管理人员，专职负责公司数据库的运维工作。具体来说，就是负责维护公司的各种业务系统的数据库安全有效地运行，负责数据存储方案设计、数据库表设计、索引设计和SQL优化，对数据库进行变更、监控、备份、高可用设计等工作，以提高数据库的运行效率，保障数据库的稳定运行，从而保障公司业务系统的正常运行。项目知识导入一、数据管理发展历史1．人工管理阶段2．文件系统阶段3．数据库系统阶段数据库系统相比于文件系统具有如下特点。（1）数据的结构化存储（2）配备有专门的数据库管理系统进行数据安全性和完整性的控制。项目知识导入二、数据库基本概念1．数据库（Database,DB）

是指以一定方式储存在一起、能让多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。也可以理解为，数据库是按照特定的数据结构来组织、存储和管理数据的仓库。2．数据库管理系统(DatabaseManagementSystem,DBMS)

用于建立、使用和维护数据库的大型软件3．数据库系统（DatabaseSystem,DBS）

包含数据库和数据库管理系统

常见的数据库系统：MySQL、SQLServer、Oracle、

达梦（武汉达梦https:///

）等项目知识导入三、数据库类型

数据库有很多类型。区分数据库类型的主要参照的指标是数据的存储模型，常用的数据模型有：层次模型、网状模型、关系模型、面向对象模型、半结构化模型

由于关系模型在很长一段时间内成为主流的数据模型，所以也习惯性将数据库类型分为两类，关系型数据库和非关系型数据库。项目知识导入1、关系型数据库这是目前至今主流的数据库类型，其对应的数据存储模型就是关系型模型，数据以表格形式存储，字段关联数据。二维表结构是非常贴近逻辑世界的一个概念，它更容易理解，这是关系型数据库能够成为主流的其中一个重要原因。通过SQL进行表与表之间的连接查询非常的方便。缺点：海量数据下，对一张表的查询会显得很力不从心，这是因为数据的存储不具备特殊的数据结构，例如有些非关系型数据库的数据存储结构是类似树的结构，就使得查询上具有天然的优势。随着大数据时代的来临，海量数据下，传统的关系型数据的效率问题就会被逐渐放大。项目知识导入2、非关系型数据库非关系型数据库也被称为NoSQL数据库，它泛指所有非关系型数据库。非关系型数据库种类有很多，列举其中较为流行的几种。

（1）键值（Key-Value）存储数据库

如：Memcached、Redis、MemcacheDB、BerkeleyDB

（2）列存储（Column-oriented）数据库：如：Hbase（/p/131626000）

图1-1传统的行存储数据库结构图1-2列存储数据库结构项目知识导入2、非关系型数据库

（3）面向文档数据库

旨在将半结构化数据存储为文档，其中文档包括XML、YAML、JSON、BSON、office文档等。如：MongoDB、CouchDB。项目知识导入四、数据库运维常见工作内容环境部署

数据备份/恢复监控故障处理性能优化容灾升级/迁移安全管理项目知识导入五、数据库工程师（DBA）职位及其职责一般意义上的数据库工程师只是负责数据库的运营和维护，包括数据库的安装、监控、备份、恢复等基本工作。但是对于软件开发企业来说，数据库工程师职责比这个大得多，需要覆盖产品从需求设计、测试到交付上线的整个生命周期，在此过程中不仅要负责数据库管理系统的搭建和运维，更要参与到前期的数据库设计，中期的数据库测试和后期的数据库容量管理和性能优化。项目知识导入六、数据库运维发展阶段

1．人工运维

有关数据库的部署，监控，SQL上线或是故障处理等一些操作均为人工完成。DBA利用各种命令行完成数据库的整体支撑及运维工作2．工具运维

尝试将使用频次高的流程性脚本结合自身经验转换包装成常用运维工具，同时，围绕CMDB（配置管理数据库）对资产，日志及服务进行基础管理。项目知识导入六、数据库运维发展阶段3．平台运维

在工具时代的基础之上，运维平台完善了更加多样化的管理工具，并将管理平台以可视化、可操作化页面的形式展现在用户眼前，这是数据库运维的高级自动化时期。4、智能运维

在智能化的数据库运维时代，传统的DBA工作也不再需要了，DBA已转型成智能运维平台的构建者。数据库的智能运维集成了实用的机器学习算法，最终实现数据库的自我管理，自动感知、自动决策、自动执行，自动闭环，无须人为干预，最大限度提升资源利用率以及安全性和可靠性。项目知识导入七、数据库运维发展趋势亲临现场人工命令式脚本工具化隐身幕后智能诊断自动排障传统运维（人工救火）智能运维（服务自治）项目任务分解任务1-1：了解数据库工程师职位要求及就业前景任务说明

随着现代企业的数据存储需求日益增长，由常见的单机数据库到分布式数据库；由关系数据到非关系数据。大数据管理工作已成企业运营的常态，更成为企业数字化转型的助力。而随着企业数字化转型的深入，企业亟待解决的是数字化能力的提升。像天天电器商场这样需要专职数据库运维技术人员（数据库工程师）的企业将会越来越多。

本任务要求想从事数据库工程师职位工作的你，通过相关就业招聘网站，搜索并了解数据库工程师职位的基本能力要求和就业前景。任务实施过程项目任务分解任务1-2：了解国内数据库运维管理平台任务说明

近年来，随着大数据、机器学习等技术的发展，数据库运维工作也逐渐向自动化、智能化趋势发展。你也很快了解到国内市场有很多可用的数据库运维平台。通过综合的第三方数据库运维平台，不仅能减轻运维工作量，而且能够充分利用平台的巨大运维知识库，解决在运维过程中出现的疑难杂证，实现企业数据库的安全、稳定、高效地运行，真正实现企业数据价值最大化。本任务要求熟悉国内常见的提供数据库运维服务的企业，了解其提供的主要产品和服务内容。任务实施过程数据库运维与管理项目2安装和配置MySQL数据库教学目标知识目标了解MySQL数据库发展历史及特点掌握MySQL命令行工具的使用掌握MySQL

Workbench的使用掌握MySQL常用的配置内容能力目标能在Linux平台下完成MySQL数据库部署能在Windows平台下完成MySQL数据库部署能用第三方运维平台完成数据库自动部署素养目标安全意识能力职业规范操作自学能力思政目标民族自豪感国产软件振兴的使命担当项目2安装和配置MySQL数据库项目场景：你在招聘网站上看到了天天电器公司发布的招聘信息，并了解到该公司大多数的业务系统使用的数据库系统是MySQL。所以，你需要首先掌握在不同平台环境下完成MySQL数据库服务器的部署工作。接下来，请你按照任务要求来完成在不同环境平台下的MySQL数据库服务器的安装和配置工作。项目知识导入一、MySQL8.01．应用版本（1）MySQLCommunityServer：MySQL的社区版本，开源免费，但不提供官方技术支持。（2）MySQLEnterpriseEdition：MySQL的企业版本，需付费，可以试用30天。（3）MySQLCluster集群版，开源免费。可将几个MySQLServer封装成一个Server。（4）MySQLClusterCGE高级集群版，需付费。（5）MySQLWorkbench（GUITOOL）一款专为MySQL设计的ER/数据库建模工具。2．文件系统阶段。项目知识导入一、MySQL8.02．MySQL8.0新特性

（1）性能：MySQL8.0的速度要比MySQL5.7快很多。项目知识导入2．MySQL8.0新特性

（2）改进了NoSQL的存储。

（3）新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式。（4）隐藏索引。

（5）降序索引。

（6）通用表表达式(CommonTableExpressionsCTE)。（7）UTF-8:使用

utf8mb4作为MySQL的默认字符集。（8）JSON:MySQL8.0大幅改进了对JSON的支持（9）可靠性：InnoDB表上的DDL也可以实现事务完整性（10）高可用性：InnoDB集群为你的数据库提供集成的原生HA解决方案（11）安全性：对OpenSSL的改进、新的默认身份验证、SQL角色、密码强度、授权项目知识导入二、MySQL命令行实用程序命令名称命令别称命令含义clear\c清除当前输入语句connect\r连接到服务器，可选参数为数据库和主机delimiter\d设置语句分隔符ego\G发送命令到MySQL服务器，并显示结果exit\q退出MySQL命令行，同quitgo\g发送命令到MySQL服务器help\h显示帮助print\p打印当前命令prompt\R改变MySQL提示信息quit\q退出MySQL命令行source\.执行一个SQL脚本文件，以一个文件名作为参数status\s获取MySQL服务的状态信息tee\T设置输出文件，将所有信息输出至指定文件中use\u选择一个数据库使用，如：usetest;其中test为数据库名项目知识导入三、MySQLWorkbench

MySQLWorkbench是一款专为MySQL设计的集成化桌面软件，是可视化数据库设计软件，也是下一代的可视化数据库设计、管理的工具，它为数据库管理员和开发人员提供了一整套可视化的数据库操作环境，主要功能有数据库设计与模型建立、SQL开发（取代MySQLQueryBrowser）、数据库管理（取代MySQLAdministrator）。它同时有开源和商业化两个版本，可从官网下载

。项目知识导入四、MySQL配置解析配置项含义basedir=path使用给定目录作为根目录(安装目录)datadir=path从给定目录读取数据库文件pid-file=filename为mysqld程序指定一个存放进程ID的文件(仅适用于UNIX/Linux系统);Init-V脚本需要使用这个文件里的进程ID结束mysqld进程socket=filename为MySQL客户程序与服务器之间的本地通信指定一个套接字文件(仅适用于UNIX/Linux系统;默认设置一般是/var/lib/mysql/mysql.sock文件)。在Windows环境下，如果MySQL客户与服务器是通过命名管道进行通信的，–sock选项给出的将是该命名管道的名字(默认设置是MySQL)port=n为MySQL程序指定一个TCP/IP通信端口(通常是3306端口)user=namemysqld程序在启动后将在给定UNIX/Linux账户下执行;mysqld必须从root账户启动才能在启动后切换到另一个账户下执行;mysqld_safe脚本将默认使用–user=mysql选项来启动mysqld程序项目知识导入四、MySQL配置解析配置项含义slow_query_log=1/0是否开启慢查询日志，1表示开启，0表示关闭max_connections=nMySQL服务器同时处理的数据库连接的最大数量(默认设置是100)max_connect_errors=n

设置每个主机的连接请求异常中断的最大次数，当超过该次数，MYSQL服务器将禁止host的连接请求character-set-server=utf8

设置数据库服务器默认编码utf-8log-warnings=1/0默认为1,表示启用警告信息记录日志,不需要置0即可,大于1时表示将错误或者失败连接记录日志default-storage-engine=InnoDB默认数据库引擎，MySQL8默认为InnoDB项目任务分解任务2-1：在Linux系统下安装配置MySQL任务说明

MySQL支持多个系统，不同系统下的安装和配置和过程也不尽相同。考虑到实际应用中的环境多数是Linux系统，所以本任务要求在LinuxCentOS7系统下完成安装配置MySQL8.0社区版（Community）产品的任务。任务实施过程项目任务分解任务2-2：登录、退出MySQL服务器任务说明

安装完MySQL之后，就可以登录MySQL服务器进行管理。本任务要求使用root账户的临时密码进行登录，并更改root账户密码。任务实施过程项目任务分解任务2-3：在Windows系统下安装配置MySQL任务说明

在生产环境中，MySQL一般部署在Linux系统上。当然，MySQL也支持Windows系统。本任务要求完成在Windows系统中安装和配置MySQL。任务实施过程项目任务分解任务2-4：第三方运维平台下的自动化部署任务说明

大多数的数据库运维平台都有数据库的自动部署功能，如杭州美创科技有限公司（以下简称“美创科技”）的数据库运行安全管理平台。本任务要求基于“美创”产品平台实现数据库系统的自动化部署功能。任务实施过程常见问题解决问题1.启动MySQL服务时出现“ERROR2002(HY000):Can'tconnecttolocalMySQLserverthroughsocket'/tmp/mysql.sock'错误。原因分析：

首先了解mysql.sock的作用。Mysql有两种连接方式。（1）TCP/IP（2）socket对mysql.sock来说，其作用是程序与MySQL处于同一台机器，发起本地连接时可用，是一个临时文件，启动MySQL后才会生成。报错的原因就是，MySQL将其放在/tmp目录，而linux将其放在/var/mysql目录。解决方案：本问题解决起来十分简单，只需要创建一个软链接即可。1．创建目录sudomkdir/var/mysql（如果/var/下有mysql目录，则不需创建）2.创建软链接sudoln-s/tmp/mysql.sock/var/mysql/mysql.sock过程中如果提示“ln:creatingsymboliclink/data/mysqldata/mysql.sock'to/tmp/mysql.sock’:Fileexists”,则删除之前的mysql.sock文件，然后重新启动MySQL服务。

常见问题解决问题2.如何删除CentOS7自带的MariaDB数据库？原因分析：CentOS7系统里自带安装MariaDB数据库，如果不需要的话，可以考虑将其删除，防止在安装部署MySQL时出现不可预知问题。解决方案：1．查询所安装的MariaDB组件rpm-qa|grepMaria*2．卸载数据库yum-yremovemari*3．删除数据库文件rm-rf/var/lib/mysql/*常见问题解决问题3.Windows系统中，输入数据库初始化命令mysqld--initialize–insecure时，会提示计算机中丢失vcruntime140_1.dll原因分析：此问题由于缺少MySQL依赖的Windows库导致。解决方案：下载安装当前最新版VisualC++RedistributableforVisualStudio2019，然后即可正常安装Mysql。或者百度搜索“微软常用运行库合集2019”，安装后可以正常安装。数据库运维与管理项目3管理MySQL权限与安全教学目标知识目标了解MySQL权限管理原理掌握MySQL权限表掌握MySQL账户管理掌握MySQL权限管理能力目标能根据用户需求完成MySQL数据库的账户管理能根据应用场景完成MySQL数据库的权限管理能完成MySQL数据库的安全体系构建素养目标安全意识能力职业规范操作严谨细致思政目标民族自豪感国产软件振兴的使命担当项目3管理MySQL权限与安全项目场景：天天电器商场的售后服务部门最近通过外包的方式开发了一款售后服务登记系统，外包的软件开发商需要在公司的服务器上部署MySQL的数据库文件以提供数据共享服务。请你给外包开发公司建立一个访问MySQL数据库的账户，使其可以部署数据库文件，但是不能查看和改动部署在MySQL服务器上的其他数据文件。项目知识导入一、权限表5个常用的控制权限的表，分别为：user表，db表，tables_priv表，columns_priv表和procs_priv表。1、user表：

存放账户信息及全局级别（所有数据库）权限，决定了来自哪些主机的哪些账户可以访问数据库实例，如果有全局权限则意味着对所有数据库都有此权限。示例：查看user表结构mysql>usemysql;mysql>descuser;项目知识导入（1）账号字段User：账户名称；Host：允许访问的客户端IP地址或主机地址，为“%”时，表示所有客户端的账户；Host+Useruser表的主键，用于区分MySQL中的账户。（2）权限字段以“_priv”结尾的字段；字段保存了账户的全局权限，如“Select_priv”查询权限、“Insert_priv”插入权限等；数据类型都是ENUM枚举类型，取值为N或Y，N表示无对应权限，Y则表示拥有该权限；项目知识导入（3）安全连接字段ssl_type：用于保存安全连接的类型ssl_cipher：用于保存安全加密连接的特定密码x509_issuer：保存由CA签发的有效的X509证书x509_subject：保存包含主题的有效的X509证书（4）资源限制字段：保存账户对可使用的服务器资源的限制max_questions：每小时允许用户执行查询操作的最多次数。max_updates：每小时允许用户执行更新操作的最多次数。max_connections：每小时允许用户建立连接的最多次数。max_user_connections：允许单个用户同时建立连接的最多数量。项目知识导入（5）身份验证字段：保存账户的身份验证信息plugin：用于指定账户的验证插件名称；authentication_string：根据对应的验证插件对明文密码加密后的字符串；password_expired：密码是否过期；password_last_changed：密码最后修改时间；password_lifetime：密码的有效期；Password_reuse_history：修改密码不允许与最近几次使用的密码重复；Password_reuse_time：修改密码不允许与最近多少天使用的密码重复；Password_require_current：修改密码是否需要提供当前的登录密码；（6）账户锁定字段：account_locked：保存当前账户是否是锁定状态，当为Y时，即账户锁定，不能用于连接到服务器；项目知识导入二、账户管理1、建立账户

语法：CREATEUSER[IFNOTEXISTS]user[auth_option][,user[auth_option]]...DEFAULTROLErole[,role]...[REQUIRE{NONE|tls_option[[AND]tls_option]...}][WITHresource_option[resource_option]...][password_option|lock_option]...项目知识导入选项说明默认值user账户名，由“账户名@主机地址”组成

auth_option账户身份验证选项，即加密插件由default_authentication_plugin系统变量定义的插件进行身份验证role角色NONEtls_option加密连接协议选项NONEresource_option资源控制选项N(表示无限制)password_option密码管理选项PASSWORDEXPIREDEFAULTlock_option账户锁定选项ACCOUNTUNLOCK语法说明项目知识导入二、账户管理1、建立账户示例1：建立简单账户示例2：建立有密码的账户mysql>createuser'user1';mysql>CREATEUSER'user1'IDENTIFIEDby'123456';mysql>SELECTuser,hostFROMuser;mysql>SELECTplugin,authentication_stringFROMuserWHEREuser='user1';#查看明文密码加密后的效果#查询用户表中的用户信息项目知识导入二、账户管理1、建立账户示例3：同时创建多个账户示例4：建立账户并设置可操作资源范围mysql>CREATEUSER->'user3'IDENTIFIEDBY'333333',->'user4'IDENTIFIEDBY'444444';;mysql>CREATEUSER->'user5'IDENTIFIEDBY'555555'->WITHMAX_UPDATES_PER_HOUR2;#建立一个名为“user5”的账户，限制其每小时最多可以更新2次项目知识导入二、账户管理 2、修改账户语法：ALTERUSER[IFNOTEXISTS]user[auth_option][,user[auth_option]]...DEFAULTROLErole[,role]...[REQUIRE{NONE|tls_option[[AND]tls_option]...}][WITHresource_option[resource_option]...][password_option|lock_option]...项目知识导入二、账户管理 2、修改账户示例1：为登录的当前账户修改密码示例2：为指定的账户修改密码示例3：修改账户验证

将user1的账户加密插件修改为“caching_sha2_password”,密码修改“User1@111”,要求：每180天选择一个新密码；并启用登录失败跟踪（三个连续的错误密码将导致临时账户锁定两天）mysql>ALTERUSERUSER()IDENTIFIEDBY'Hello123.';mysql>ALTERUSER'user1'IDENTIFIEDby'HelloUser1.';mysql>ALTERUSER'user1'@'%'->IDENTIFIEDWITHcaching_sha2_passwordBY'User1@111'->PASSWORDEXPIREINTERVAL180DAY->FAILED_LOGIN_ATTEMPTS3PASSWORD_LOCK_TIME2;项目知识导入二、账户管理

2、修改账户示例4：修改账户状态之锁定账户示例5：修改账户状态之解锁账户示例6：设置账户的资源限制——设置user1账户最多可同时建立两个连接

mysql>ALTERUSER'user1'@'%'ACCOUNTLOCK;mysql>ALTERUSER'user1'@'%'ACCOUNTUNLOCK;mysql>ALTERUSER'user1'@'%‘

->WITHmax_user_connections2;

项目知识导入二、账户管理

3、删除账户

示例1：删除user5账户语法：DROPUSER[IFEXISTS]user[,user]…;mysql>DROPUSER'user5';

项目知识导入三、权限管理（一）MySQL的各种权限权限名称权限级别权限描述SELECT全局、数据库、表、列启用SELECTUPDATE全局、数据库、表、列启用UPDATEDELETE全局、数据库、表启用DELETEINSERT全局、数据库、表、列启用INSERTSHOWDATABASES全局启用SHOWDATABASES以显示所有数据库SHOWVIEW全局、数据库、表启用SHOWCREATEVIEWPROCESS全局使账户可以使用查看所有进程SHOWPROCESSLIST1.数据权限数据权限主要是针对数据库表中的数据行的操作权限项目知识导入权限名称权限级别权限描述DROP全局、数据库、表启用删除数据库、表和视图CREATE全局、数据库、表启用创建数据库、表CREATEROUTINE全局、数据库启用创建存储过程CREATETABLESPACE全局启用创建、修改或删除表空间和日志文件组CREATETEMPORARYTABLES全局、数据库启用CREATETEMPORARYTABLESCREATEVIEW全局、数据库、表启用创建或修改视图ALTER全局、数据库、表启用ALTERTABLEALTERROUTINE全局、数据库、存储过程启用删除或修改存储过程INDEX全局、数据库、表启用创建或删除索引TRIGGER全局、数据库、表启用创建、修改或删除触发器REFERENCES全局、数据库、表、列启用创建外键2.结构权限结构权限主要针对数据库表的结构或数据库的对象进行操作的权限项目知识导入3.管理权限这里的管理权限指的是通常只有管理员才有的管理权限权限名称权限级别权限描述SUPER全局能够使用如其他行政业务CHANGEMASTERTO，KILL，PURGEBINARYLOGS，SETGLOBAL，和中mysqladmin调试命令CREATEUSER全局允许使用CREATEUSER，DROPUSER，RENAMEUSER，和REVOKEALLPRIVILEGESGRANTOPTION全局、数据库、表、存储过程、代理允许授予或删除账户权限RELOAD全局启用FLUSH操作PROXY全局启用账户代理REPLICATIONCLIENT全局使账户能够询问主服务器或从服务器在哪里REPLICATIONSLAVE全局使复制从属服务器能够从主服务器读取二进制日志事件SHUTDOWN全局启用mysqladminshutdown使用LOCKTABLES全局、数据库启用LOCKTABLES对您拥有SELECT特权的on表的使用项目知识导入三、权限管理（二）查看权限语法：SHOWGRANTS[FOR账户]项目知识导入三、权限管理（三）授予权限GRANTpriv_type[(column_list)][,priv_type[(column_list)]]...ON[object_type]priv_levelTOuser_or_role[,user_or_role]...[WITHGRANTOPTION][ASuser[WITHROLEDEFAULT|NONE|ALL|ALLEXCEPTrole[,role]...|role[,role]...]]}priv_type：权限类型，即表3-3、表3-4、表3-5中所列权限名称。column_list：字段列表用于设置列权限object_type：权限作用的目标类型，默认为TABLE,其值还可以是FUNCTION(函数)、PROCEDURE(存储过程)。priv_level：权限级别，user_or_role：账户或角色WITHGRANTOPTION：表示当前账户可以为其他账户进行授权。项目知识导入三、权限管理（三）授予权限示例1：授予user1账户对test数据库中的student表的SELECT权限，以及对name和phone字段的插入权限。示例2：授予user1账户对test数据库所有的权限mysql>GRANTSELECT,INSERT(name,phone)ONtest.studenttouser1;mysql>GRANTALLONtest.*touser1;mysql>SHOWGRANTSFORuser1;项目知识导入三、权限管理（四）收回权限语法：REVOKEpriv_type[(column_list)][,priv_type[(column_list)]]...ON[object_type]priv_levelFROMuser_or_role[,user_or_role]...项目知识导入三、权限管理（四）收回权限示例1：收回user1账户对test数据库student表的插入权限。示例2：收回user1账户所有的权限mysql>REVOKEINSERT(name,phone)ONtest.studentFROMuser1;mysql>REVOKEALLONtest.studentFROMuser1;mysql>SHOWGRANTSFORuser1;项目知识导入三、权限管理（五）刷新权限

刷新权限指的是从系统数据库mysql中的权限表中重新加载账户的权限。这是因为GRANT、CREATEUSER等操作会将服务器的缓存信息保存到内存中，而REVOKE、DROPUSER操作并不会同步到内存中。因此可能会造成服务器内存的消耗，所以在REVOKE、DROPUSER操作后建议使用FLUSHPRIVILEGES重新加载账户的权限。具体的SQL语句如下。mysql>FLUSHPRIVILEGES;项目任务分解任务3-1：建立MySQL普通账户并用普通账户登录任务说明

安装配置完MySQL之后需要创建普通账户，尽管root账户具有最高权限，但还是需要普通账户进行管理。本任务要求创建MySQL普通账户并用普通账户登录。任务实施过程项目任务分解任务3-2：为MySQL普通账户授权数据库的远程访问任务说明

在上个任务中，创建了一个普通账户，但是这个账户只限于本地登录，不能用于远程访问。本任务要求为这个账户授权数据库的远程访问。任务实施过程项目任务分解任务3-3：为MySQL普通账户授权数据库表的读写操作任务说明

当前这个普通账户被创建后没有任何权限，如果要对数据库表的内容进行增加和删除等操作，则需要相应的读写权限。本任务要求对这个账户授予数据库表的读写权限。任务实施过程项目任务分解任务3-4：收回MySQL普通账户对特定数据库的操作权限任务说明

某些情况下，需要对普通账户进行限制，限制对特定数据库的操作。本任务要求收回这个普通账户权限对特定数据库的操作权限。任务实施过程常见问题解决问题1.登录时出现“ERROR1045(28000):Accessdeniedforuser:'root@localhost'(Usingpassword:YES)”类似错误。原因分析：出现这个报错代表当前账户访问被拒绝，账户名或密码错误。解决方案：输入正确的账户名密码即可。常见问题解决问题2.登录时出现“ERROR2003(HY000):Can’tconnecttoMySQLserveron‘5’(113)”类似错误。原因分析：出现这个报错代表主机ip输入错误。解决方案：输入正确的主机ip即可。常见问题解决问题3.授权时出现“ERROR1044(42000):Accessdeniedforuser'hzmc'@'%'todatabase'mysql'”类似错误。原因分析：授权时时出现这个错误，可能是当前登录的账户缺少withgrantoption权限，这个权限表示将这个权限授权给第三方的权限。解决方案：使用有withgrantoption这个权限的账户授权。数据库运维与管理项目4分析MySQL日志教学目标知识目标掌握MySQL错误日志掌握MySQL一般查询日志掌握MySQL慢查询日志掌握MySQL二进制日志能力目标能根据错误日志查找错误原因能根据mysqldumpslow工具分析查询数据库性能能使用mysqlbinlog通过时间点恢复数据素养目标安全意识能力职业规范操作严谨细致思政目标民族自豪感国产软件振兴的使命担当项目4分析MySQL日志项目场景：小明前两天刚应聘了天天电器商场的数据库管理员职位，负责带他的技术主管就出差了，各个业务部门都打来电话反映部门的OA系统查询数据有点慢，数据请求无法完成；甚至还有些部门因为误操作，把不该删除的记录不小心删除了，请求恢复。弄得小明有点头大，今天一大早，就接到销售部门打来的电话，反映OA系统登录不了，出现“数据库无法连接”的提示信息。小明要怎样做才能解决这些问题呢？项目知识导入在数据库运行过程中，一般都会产生很多的日志信息，这些日志信息记录着数据库运行工作的具体情况，帮助数据库管理员追踪在数据库运行期间发生过的各种事件。MySQL中的日志有6种，分别是错误日志、一般查询日志、慢查询日志、二进制日志、中继日志和DDL日志。一、错误日志错误日志记录MySQL服务在启动和停止时的正确及错误信息，还记录运行过程中的错误信息，是MySQL最重要的日志之一，当数据库由于出现任何故障导致无法正常使用时，可以首先查看此日志。项目知识导入1、配置错误日志组件MySQL8.0的错误日志采用组件架构，通过不同的组件执行日志的写入和过滤功能。系统变量log_error_services指定过滤组件、写入组件。查看MySQL8.0默认的过滤组件和写入组件，语句如下。

mysql>SELECT@@GLOBAL.log_error_services;++|@@GLOBAL.log_error_services|++|log_filter_internal;log_sink_internal|++1rowinset(0.00sec)内置过滤器组件内置日志记录器组件项目知识导入要启用日志组件，应首先使用命令INSTALLCOMPONENT命令加载例1：使用系统日志编写器log_sink_syseventlog代替默认编写器log_sink_internal，INSTALLCOMPONENT'file://component_log_sink_syseventlog';SETGLOBALlog_error_services='log_filter_internal;log_sink_syseventlog';例2：配置多个日志记录器，除了默认写入器之外还启用系统日志写入器SETGLOBALlog_error_services='log_filter_internal;log_sink_internal;log_sink_syseventlog';项目知识导入2、配置错误日志过滤错误日志配置通常包括一个日志过滤器组件和一个或多个日志编写器组件。MySQL提供了以下错误日志过滤组件。（1）log_filter_internal

此过滤器组件根据log_error_verbosity和log_error_suppression_list系统变量，结合日志事件优先级和错误代码进行错误日志过滤。内置并默认启用。（2）log_filter_dragnet

此过滤器组件根据用户提供的规则，结合dragnet.log_error_filter_rules系统变量进行错误日志过滤。

项目知识导入3、基于优先级的错误日志过滤（log_filter_internal）错误日志事件的优先级分别为ERROR、WARNING或INFORMATION。由log_error_verbosity系统变量控制服务器日志的详细程度。log_error_verbosity系统变量允许的值为1（仅错误），2（错误和警告），3（错误，警告和信息），默认值为3。在f文件中，可以配置log_error_verbosity系统变量[mysqld]log_error_verbosity=1#仅错误消息

项目知识导入4、启动和设置错误日志默认开启错误日志功能；错误日志存储在MySQL数据库的数据文件夹下，通常名称为hostname.err；mysqld使用“--log-error=[file_name]”设置错误日志文件；在f文件中配置log-error=/var/log/mysqld.log如果不知道错误日志的位置，可以通过查看变量log_error来获得；mysql>showvariableslike'log_error';项目知识导入5、查看错误日志Linux下使用tail命令查看，如tail-3/var/log/mysqld.log；6、删除错误日志在MySQL中，可以使用mysqladmin命令来开启新的错误日志，以保证MySQL服务器上的硬盘空间充足；mysql>mysqladmin-uroot-pflush-logs项目知识导入二、一般查询日志一般查询日志记录客户端的操作语句1.启动和设置一般查询日志通过showvariableslike'general_log'命令查看一般查询日志是否打开指定初始一般查询日志的状态，需使用“--general_log[={0|1}]”使用“--general_log_file=file_name”指定日志文件名general_log全局变量设置为0（或OFF）禁用日志，设置为1（或ON）启用日志

mysql>SETGLOBALgeneral_log='ON';2.查看一般查询日志通过查看general_log_file变量来查看日志文件路径。showvariableslike'general_log_file';项目知识导入3、删除一般查询日志在MySQL中，一般查询日志文件属于文本文件，可以直接删除，删除后需要使用flushlogs命令重新生成一般查询日志文件。

项目知识导入三、慢查询日志慢查询日志用于记录在MySQL中执行时间超过指定时间的查询语句。MySQL的慢查询日志是排查问题SQL语句，以及检查当前MySQL性能的一个重要工具。查看慢查询日志是否启动和超时设置项目知识导入1、启动和设置慢查询日志将log-slow-queries选项和long_query_time选项加入配置文件的[mysqld]组中，配置内容如下。

上述选项中的参数说明如下。dir参数用于指定慢查询日志的存储路径，如果不指定存储路径，慢查询日志将默认存储到MySQL的数据目录下。filename参数用于指定日志的文件名，日志文件的完整名称为filename-slow.log。如果不指定文件名，默认文件名为hostname-slow.log，hostname是MySQL服务器的主机名。n参数是设定的时间值，该值的单位是秒。如果不设置long_query_time选项，默认时间为10秒。项目知识导入2、查看慢查询日志慢查询日志也是以文本文件的形式存储的，可以使用普通的文本文件查看工具来查看。内容示例如下：Time：该查询发生的时间。User@Host：发起该查询的主机。Query_time：该查询总共用了多少时间。Lock_time：在查询过程中锁定表的时间。Rows_sent：返回了多少行数据。Rows_examined：表扫描了多少行数据才得到结果。SETtimestamp：发生慢查询时的时间戳。项目知识导入3、mysqldumpslow命令

庞大的慢查询日志文件采用人工分析很困难，可以使用MySQL自带的mysqldumpslow命令来分析慢查询日志。语法格式：项目知识导入例1：得到记录次数最多的10条SQL语句，命令如下。shell>mysqldumpslow-sc-t10/var/lib/mysql/localhost-slow.log例2：得到按照查询时间排序的前10条含有左连接的SQL语句，命令如下。

shell>mysqldumpslow-st-t10-g"leftjoin"/var/lib/mysql/localhost-slow.log4、删除慢查询日志慢查询日志的删除方法与一般查询日志的删除方法是一样的。可以使用文件管理方式删除慢查询日志，并可以使用flushlogs命令重新生成慢查询日志文件。项目知识导入四、二进制日志二进制日志用于记录所有的DDL语句和DML语句，但是不包括数据查询语句。语句以“事件”的形式保存，它描述了数据的更改过程。所以，二进制日志对于“灾难”出现时的数据恢复具有极其重要的作用。1、启动和设置二进制日志项目知识导入2、查看二进制日志（1）查看当前二进制日志文件的位置和状态项目知识导入2、查看二进制日志（2）查看日志文件列表

binlog.index是二进制日志索引文件，记录了最大的日志序号。项目知识导入（3）使用mysqlbinlog命令查看日志文件内容mysqlbinlog命令的语法格式如下：mysqlbinlog[options]log-files1log-files2...项目知识导入 4、删除二进制日志对于繁忙的事务处理系统，每天会生成大量日志内容，日志如果长时间不清除，将会对磁盘空间造成很大的浪费，因此定期删除日志是数据库管理员维护MySQL的一个重要工作内容。（1）resetmaster命令（2）purgemasterlogsto命令执行resetmaster命令将删除所有二进制日志，新日志编号从000001开始。purgemasterlogsto命令以日志编号为条件进行二进制日志删除。（3）purgemasterlogsbefore命令purgemasterlogsbefore命令以时间为条件进行二进制日志删除（4）expire_logs_days配置项expire_logs_days配置项设置在f文件中，用来设置日志的过期天数。超过指定的天数后日志将会被自动删除。项目任务分解任务4-1:通过错误日志查看MySQL服务不能启动的原因任务说明

启动MySQL服务失败的时候，错误信息会被记录到MySQL错误日志中。本任务要求通过错误日志查看MySQL服务不能启动的原因。任务实施过程项目任务分解任务4-2：记录客户端连接错误信息任务说明

在数据库管理过程中，有时需要记录客户端连接的信息，包括中断连接和连接访问被拒绝的信息，从而为数据库安全防范管理提供参考数据。本任务要求在错误日志文件中以JSON格式记录客户端连接的错误信息。任务实施过程项目任务分解任务4-3：使用mysqldumpslow分析慢查询日志任务说明

本任务要求使用mysqldumpslow分析慢查询日志中记录最多的查询语句以及查询用时最长的查询语句。任务实施过程项目任务分解任务4-4：使用mysqlbinlog基于时间点恢复数据任务说明

MySQL提供了专用工具mysqlbinlog，使用这个工具可以查看二进制日志文件中记录的信息，并且以SQL格式输出信息。如果因为误操作、修改或删除了某行数据，则可以利用二进制日志进行恢复。本任务要求使用mysqlbinlog基于时间点恢复数据。任务实施过程项目任务分解任务4-5：使用mysqlbinlog基于字节位置恢复数据任务说明

除了基于时间点，还可以基于二进制日志中的字节位置实现数据的恢复。本任务要求使用mysqlbinlog基于字节位置恢复数据。任务实施过程常见问题解决问题1.出现“[ERROR]unknownvariable‘default-character-set=utf8mb4’”错误提示。原因分析：若在f文件中添加default-character-set=utf8mb4选项，则在执行mysqlbinlog命令查看二进制日志时就会报错。解决方案：在mysqlbinlog命令中添加--no-defaults选项。常见问题解决问题2.设定参数时出现“ERROR1238(HY000):Variable‘log_bin’isareadonlyvariable”错误提示。

原因分析：这个错误表示log_bin是一个只读变量。解决方案：更改这个变量需要关闭MySQL服务，然后在初始化选项文件中修改。数据库运维与管理项目5备份与恢复MySQL数据库教学目标知识目标掌握MySQL备份的概念和分类掌握mysqldump命令的使用方法掌握PerconaXtraBackup工具的使用方法掌握数据库迁移的概念掌握MySQL数据表的导出和导入方法素养目标提高安全防范意识加强规范操作意识能力目标能使用mysqldump命令备份和恢复数据能使用PerconaXtraBackup实现热备份能基于crontab实现自动备份作业能基于各种命令实现数据表的导入和导出项目5

备份与恢复MySQL数据库项目场景：天天电器商场的服务器已使用多年。由于服务器硬件故障率高，经常会导致数据丢失，受到各部门抱怨。但公司近期没有设备更新计划，信息部门决定实施一个自动的备份方案，当服务器重装的时候能重新加载备份数据。数据备份一、备份的分类1、按照备份的方法分类按照备份的方法（是否需要数据库离线），可以将备份分为热备份、冷备份和温备份。（1）热备份（HotBackup）。热备份可以在数据库运行中直接进行，对正在运行的数据库操作没有任何的影响，数据库的读写操作可以正常执行。（2）冷备份（ColdBackup）。冷备份必须在数据库停止运行的情况下进行，数据库的读写操作不能执行。这种备份方式非常简单，一般只需要复制相关的数据库物理文件即可。（3）温备份（WarmBackup）。温备份是在数据库运行过程中进行的，但是会对当前数据库的操作有所影响，温备份时仅支持读操作，不支持写操作。数据备份2、按照备份的数据库内容分类

按照备份的数据库内容来分类，备份又可以分为完全备份和部分备份。1.完全备份。完全备份是指对数据库进行完整的备份，即备份整个数据库，如果数据较多则会占用较多的时间和较大的空间。2.部分备份。部分备份是指备份部分数据库内容（例如，只备份一个表）。部分备份又分为增量备份和差异备份。增量备份需要使用专业的备份工具。增量备份是指在上次完全备份的基础上，对更改的数据进行备份。注意：在MySQL中，进行不同方式的备份前，还要考虑存储引擎是否支持，例如MyISAM存储引擎不支持热备份，只支持温备份和冷备份；而InnoDB存储引擎则支持热备份、温备份和冷备份。备份的数据和备份工具二、备份的数据一般情况下，需要备份的数据分为以下几种。（1）表数据。（2）二进制日志、InnoDB事务日志。（3）代码（存储过程、存储函数、触发器、事件调度器）。（4）服务器配置文件。备份的数据和备份工具三、备份的工具以下是几种常用的备份工具。（1）mysqldump：逻辑备份工具，适用于所有的存储引擎，支持温备份、完全备份、部分备份，对于InnoDB存储引擎支持热备份。（2）cp、tar等归档复制工具：物理备份工具，适用于所有的存储引擎，支持冷备份、完全备份、部分备份。（3）lvm2snapshot：借助文件系统管理工具进行备份。（4）XtraBackup：一款由Percona公司提供的非常强大的InnoDB/XtraDB热备份工具，支持完全备份、增量备份。使用mysqldump命令备份

MySQL主要提供了mysqldump命令用于实现备份。使用mysqldump命令备份一个数据库的语法格式如下。mysqldump-uusername-pdbname[tbname...]>filename.sqlusername：账户名称。dbname：需要备份的数据库名称。tbname：数据库中需要备份的数据表名称，可以指定多个数据表。省略该参数时，会备份整个数据库。>：用来告诉mysqldump命令，将备份数据表的定义和数据写入指定的备份文件。filename.sql：备份文件的名称，文件名前面可以加绝对路径。通常将数据库备份成一个扩展名为.sql的文件。使用mysqldump命令备份下面通过示例来介绍mysqldump命令的基本使用方法。1．备份单个数据库信息，命令语法格式如下。mysqldump-u账户名-p密码--databases数据库>/保存路径/文件名.sql例如，备份data数据库，账户名是root，密码是root，备份到当前文件夹的1.sql文件中，命令如下。mysqldump-uroot-proot--databasesdata>1.sql2．备份全部数据库信息，命令语法格式如下。mysqldump-u账户名-p密码--all-databases>/保存路径/文件名.sql例如，备份全部数据库，账户名是root，密码是root，备份到当前文件夹的2.sql文件中，命令如下。mysqldump-uroot-proot--all-databases>2.sql3．备份某一个数据表，命令语法格式如下。mysqldump-u账户名-p密码数据库名表名>/保存路径/文件名.sql例如，备份data数据库中的users表，账户名是root，密码是root，备份到当前文件夹的3.sql文件中，命令如下。mysqldump-uroot-prootdatausers>3.sql使用PerconaXtraBackup工具快速备份备份操作示例（1）全库备份——备份所有数据库，存放在以时间命名的目录下。innobackupex会在指定存放数据的目录下用当前时间作为名称创建一个目录，所有生成的备份文件都会存放在这个目录下，命令如下。shell>innobackupex--defaults-file=/usr/local/mysql/f--user=root--password=xxx--socket=/var/lib/mysql/mysql.sock/data/backup/当出现“completedOK！”则表示备份完成。（2）全库备份——备份所有数据库，并指定目录的名称。在使用innobackupex进行备份时，可以使用--no-timestamp参数来阻止它自动创建一个以时间命名的目录，如此一来，innobackupex将会创建一个backup_dir目录来存储备份数据，命令如下。shell>innobackupex--defaults-file=/usr/local/mysql/f--user=root--password=xxx--socket=/var/lib/mysql/mysql.sock--no-timestamp/data/backup/backup_dir使用PerconaXtraBackup工具快速备份备份操作示例（3）全库备份——备份所有数据库，并打包。--stream=tar参数表示流式备份的格式，备份完成之后以指定格式存储到标准输出，目前只支持tar和xbstream格式，命令如下。shell>innobackupex--defaluts-file=/usr/local/mysql/f--user=root--password=xxx--socket=/var/lib/mysql/mysql.sock--stream=tar--no-timestamp/home/data/backup1>/home/data/backup/xtra_backup20210329.tar上述代码中，“1>”代表标准输出（stdout），“>”前面不加1，具有同样效果；“2>”代表标准错误（stderr）。（4）全库备份——备份所有数据库，并压缩打包。命令如下。shell>innobackupex--defaluts-file=/usr/local/mysql/f--user=root--password=xxx--socket=/var/lib/mysql/mysql.sock--stream=tar--no-timestamp/home/data/backup|gzip>/home/data/backup/150_backup.tar.gz解压PerconaXtraBackup的备份文件，必须使用tar和-i参数。例如，可以先创建解压的目录150_backup，然后解压到此目录。解压备份文件的命令如下。shell>tar-zixvf150_backup.tar.gz-C./150_backup使用PerconaXtraBackup工具快速备份备份操作示例（5）全库备份——备份到远程并压缩。命令如下。shell>innobackupex--defaluts-file=/usr/local/mysql/f--user=root--password=xxx--socket=/var/lib/mysql/mysql.sock--stream=tar/home/data/backup|sshroot@192.168.2.151"gzip>/home/data/150_backup/150_backup.tar.gz"（6）备份指定数据库。例如，备份数据库A和B，使用--databases参数指定数据库，命令如下。shell>innobackupex--defaults-file=/usr/local/mysql/f--user=root--password=xxx--socket=/var/lib/mysql/mysql.sock--databases="AB"--no-timestamp/data/backup/backup_database（7）备份不同数据库下的不同表。还是使用--databases参数，指定数据库中的表，命令如下。shell>innobackupex--defaults-file=/usr/local/mysql/f--user=root--password=xxx--socket=/var/lib/mysql/mysql.sock--databases="A.hrms_aoB.checktype"--no_x0002_timestamp/data/backup/backup_tables使用PerconaXtraBackup工具快速备份备份操作示例（8）备份一个数据库下的表。例如，备份A数据库下以h开头的表，使用--include参数，该参数使用正则表达式匹配表的名字，命令如下。shell>innobackupex--defaults-file=/usr/local/mysql/f--user=root--password=xxx--socket=/var/lib/mysql/mysql.sock--include="A.h"--no-