《Linux系统管理与服务器配置》课件-项目5配置与管理MySQL数据库管理系统

Linux系统管理与服务器配置

(Centos9&RHEL9)机械工业出版社1国家级精品资源共享课、国家精品在线开放课程配套教材数据库管理系统与MySQL项目实践CONTENTS1.项目知识准备2.项目设计与准备3.项目实施01项目知识准备

数据库管理系统概述数据库管理系统（DatabaseManagementSystem，简称DBMS）是一种专门用于管理数据库的软件系统。它的核心功能包括存储、检索、定义、操作、管理和保护数据。DBMS不仅为数据库中的数据结构提供组织和存取方法，还控制、协调、监督和保护数据库数据的完整性、安全性和一致性。数据库管理系统的特性和功能1数据定义DBMS允许用户定义数据库中数据的结构，包括数据类型、数据之间的关系以及数据的约束条件等。这通常通过数据定义语言（DDL）来实现，如SQL中的CREATE、ALTER和DROP语句。3数据控制DBMS提供数据控制功能，包括数据的安全性控制、完整性控制和并发控制等。安全性控制确保只有授权用户才能访问和修改数据；完整性控制确保数据满足预定的约束条件；并发控制确保多个用户能够同时访问和修改数据库而不会导致数据冲突。5数据库恢复DBMS提供数据库恢复功能，能够在系统故障或数据丢失时恢复数据库到一致状态。这通常通过备份和恢复机制、事务日志和故障恢复算法来实现。常见的数据库管理系统包括关系型数据库管理系统（如MYSQL、PostgreSQL、Oracle、SQLServer等）和非关系型数据库管理系统（如MongoDB、Cassandra、Redis等）。每种DBMS都有其特定的应用场景和优缺点，用户可以根据实际需求选择合适的DBMS。2数据操作DBMS支持用户对数据库中的数据进行各种操作，如插入、更新、删除和查询等。这些操作通常通过数据操作语言（DML）来实现，如SQL中的INSERT、UPDATE、DELETE和SELECT语句。4数据存储和访问DBMS负责数据的存储和访问，包括数据的物理存储结构、索引机制、查询优化等。它使用有效的存储和访问技术来提高数据的存取效率。6接口和工具DBMS提供用户接口和工具，方便用户与数据库进行交互。这些接口和工具包括查询语言、图形用户界面（GUI）、命令行界面（CLI）以及各种数据库管理工具等。MySQL数据库管理系统MySQL是一个开源的关系型数据库管理系统（RDBMS），由瑞典MySQLAB公司开发，现属于Oracle旗下产品。MySQL数据库管理系统基本特点1开源性MySQL是开源软件，任何人都可以免费使用、修改和分发它。这一特点使得MySQL在开发者和中小企业中广受欢迎。3高性能MySQL具有高度优化的查询引擎和索引机制，能够处理大量的并发请求，提供快速的数据存储和检索功能。5标准化MySQL遵循SQL标准，并支持多种编程语言的API接口，使得开发者可以轻松地与MySQL进行交互。2可靠性MySQL以其可靠性而闻名，即使在高负载下也能平稳运行，确保数据的持久性和故障恢复。4可扩展性MySQL支持主从复制、分区和集群等功能，可以方便地扩展数据库的容量和性能。MySQL数据库管理系统应用场景Web应用MySQL是许多Web应用的首选数据库系统，特别是与PHP、Python、Java等后端技术结合使用时。许多流行的Web框架和CMS（内容管理系统），如WordPress、Drupal、Joomla、Laravel、Django等，都支持MySQL作为其后端数据库。企业应用许多企业使用MySQL来构建和管理其内部系统，如ERP（企业资源规划）、CRM（客户关系管理）和BI（商业智能）系统等。这些系统需要存储、处理和查询大量数据，而MySQL提供了可靠的性能和安全性。游戏开发在游戏开发中，MySQL用于存储和管理用户信息、游戏数据、排行榜等。由于游戏通常需要处理大量并发请求和实时数据更新，因此MySQL的高性能和可靠性使其成为游戏开发者的首选数据库系统。物联网（IoT）在物联网应用中，MySQL可以用于存储和管理来自各种设备的数据。这些数据可能包括传感器读数、设备状态、用户交互等。通过MySQL，开发者可以轻松地查询和分析这些数据，以优化设备的性能和用户体验。电子商务在电子商务网站中，MySQL用于存储用户信息、产品信息、订单详情等关键数据。MySQL的高性能和可扩展性使其能够轻松处理大量并发请求和交易数据。数据分析虽然MySQL不是一个专门的数据仓库或大数据分析平台，但它仍然可以用于存储和分析结构化数据。许多组织使用MySQL作为其数据仓库的一部分，并使用SQL查询来提取和分析数据。移动应用随着移动应用的普及，越来越多的开发者选择使用MySQL作为其后端数据库。通过RESTAPI或其他技术，移动应用可以与MySQL数据库进行交互，实现用户认证、数据存储和检索等功能。MySQL数据库管理系统主要功能和组件数据存储与管理MySQL能够存储大量的数据，并提供数据的增加、删除、修改和查询功能。事务支持MySQL支持事务处理，允许一系列的操作作为一个单一的工作单元进行。这些操作要么完全执行，要么完全不执行，从而帮助保持数据的完整性MySQL严格遵循ACID原则（原子性、一致性、隔离性、持久性），确保事务的可靠性和稳定性。索引优化通过使用索引，可以提高查询数据的速度，尤其是在处理大量数据时。MySQL支持多种索引类型，包括B树索引、哈希索引等。数据复制MySQL支持数据的复制功能，可以将数据从一个数据库服务器复制到另一个服务器，用于数据备份、分析或实现数据库的高可用性。分区管理MySQL支持将表中的数据分布在不同的分区（物理上的不同文件或磁盘上），以改善性能和管理的便利性。MySQL数据库管理系统主要功能和组件安全管理MySQL提供多层次的安全措施来保护数据的安全性。包括用户认证和权限控制机制、数据加密以及数据备份和恢复等。存储过程和触发器MySQL允许在数据库服务器端执行复杂的业务逻辑，通过存储过程和触发器实现自动化任务和数据验证。全文搜索MySQL提供全文索引和搜索功能，使得可以高效地搜索数据库中的文本数据。日志管理MySQL记录数据库操作日志、错误日志等，帮助数据库管理员监控数据库的状态和活动以及进行故障排查。多种存储引擎MySQL支持多种存储引擎，如InnoDB、MyISAM等。每种存储引擎都有其特点和适用场景，用户可以根据实际需求选择合适的存储引擎。02项目设计与准备

主机名操作系统IP地址角色及其他MySQL服务器：Server01CS9/RHEL9MySQL服务主机，VMnet1MySQL客户端：Client1CS9/RHEL91MySQL客户端，VMnet1网络配置本项目要用到Server01和Client1，首先要配置Server01和Client1的网络参数，计算机的配置信息如表5-1所示（可以使用VMwareworkstation的"克隆"技术快速安装需要的Linux客户端）。其中Server01的IP地址为/24，Client1的IP地址为1/24。主要任务1.安装MySQL数据库管理系统3.运行安全配置脚本5.创建数据库与数据表7.数据库的备份与恢复2.初始化MySQL服务4.管理MySQL账户6.管理数据表03项目实施

项

目

实

施

任务一：安装MySQL在Server01上安装mysql-server，启动MySQL服务，并确保MySQL服务正在监听端口3306和33060。（1）在Server01上安装mysql-server

（2）启动mysql服务项

目

实

施

任务一：安装MySQL（3）查看MySQL服务状态，查看MySQL服务是否正在监听特定的端口（通常是3306）项

目

实

施

任务二：修改初始密码MySQL在安装时是以不安全的方式初始化的，root用户没有设置初始密码。但是应该避免使用这种方式，或安装完成后立即修改root用户的密码，因为它会使root用户面临安全风险。（1）查看初始密码MySQL安装完成后，会在日志文件中生成一个初始的root用户密码。使用grep命令查看该密码。

项

目

实

施

任务二：修改初始密码（2）以初始密码登录mysql，修改root用户的登录密码。

使用ALTERUSER'root'@'localhost'IDENTIFIEDBY'NewStrongPassword!‘;

修改初始密码为“NewStrongPassword!”。项

目

实

施

任务三：运行安全配置文件

MySQL安装完成后，运行

mysql_secure_installation

脚本来进行安全配置。这个脚本会引导你完成一系列的安全设置，包括设置root密码、删除匿名用户、禁止远程root登录、删除测试数据库等。

在MySQL中，VALIDATEPASSWORD组件是一个密码验证插件，用于确保用户密码符合一定的安全标准。这个插件可以帮助防止使用弱密码，从而提高数据库的安全性。本例选择y.项

目

实

施

任务三：运行安全配置文件在MySQL中，VALIDATEPASSWORD插件的密码策略级别（validate_password.policy）决定了密码的复杂性和安全性要求。三个级别（LOW、MEDIUM、STRONG）各自有不同的要求。

LOW：密码长度必须至少为8个字符。没有其他字符类型（数字、大小写字母、特殊字符）的明确要求。MEDIUM：密码长度必须至少为8个字符。密码必须至少包含一个数字、一个大写字母、一个小写字母和一个特殊字符。STRONG：密码长度必须至少为8个字符。密码必须至少包含一个数字、一个大写字母、一个小写字母和一个特殊字符。

此外，密码不能是字典文件中的常见单词或短语（这要求有一个可用的字典文件，并且MySQL配置为使用它）项

目

实

施

任务四：让防火墙放行mysql服务MySQL数据库服务程序在安装和配置时，默认情况下会占用TCP端口3306用于客户端连接。这个端口号在MySQL的配置文件中（通常是f或my.ini）可以通过port参数进行更改，但大多数情况下，用户会保留这个默认设置。在防火墙策略中，当为MySQL服务配置入站或出站规则时，服务名称并不总是严格地叫作mysql。这取决于几个因素，包括操作系统、防火墙软件以及MySQL的安装方式。项

目

实

施

任务五：管理MySQL账户（1）登录MySQL需要使用具有足够权限的账户（通常是root账户）登录到MySQL服务器。可以使用以下命令，然后输入root账户的密码进行登录。

（2）创建新用户使用CREATEUSER语句可以创建新的MySQL用户。例如，要创建一个名为newuser的用户，并为其指定密码P@ssw0rd1。

这里的‘localhost’表示该用户只能从本地主机连接到MySQL服务器。如果希望该用户可以从任何主机连接，可以将‘localhost’替换为‘%’。同时密码必须符合前面设置的密码策略要求。

项

目

实

施

任务五：管理MySQL账户（3）修改用户密码使用ALTERUSER语句可以修改MySQL用户的密码。例如，要将newuser用户的密码更改为P@ssw0rd2（4）删除用户使用DROPUSER语句可以删除MySQL用户。例如，要删除名为newsql的用户

项

目

实

施

任务六：对MySQL账户权限的基本操作创建MySQL用户授予用户权限查看用户权限撤销用户权限刷新用户权限项

目

实

施

任务六：对MySQL账户权限的基本操作（1）创建一个测试用户

使用CREATEUSER语句创建一个名为newuser的用户，密码为P@ssw0rd123，并且这个用户只能从localhost登录。

（2）授予权限使用GRANT语句可以为MySQL用户授予特定的权限。可以使用GRANT语句来授予新用户一些权限。例：授予newuser对mydatabase数据库的SELECT和INSERT权限

SELECT权限：允许用户读取数据库中的数据，查询表中的行和列，如果没有SELECT权限，用户将无法查看表中的数据。

INSERT

权限：允许用户向数据库中的表添加新数据，向表中插入新行，若没有SELECT权限，用户将无法查看表中的数据。

项

目

实

施

任务六：对MySQL账户权限的基本操作（3）查看权限使用SHOWGRANTS语句可以查看MySQL用户的权限。例如，要查看用户newuser的权限

全局权限(1)GRANTUSAGEON*.*TO'newuser'@'localhost’(2)这条权限实际上是一个占位符，表示newuser在所有数据库（*.*）上有“使用”权限，但这并不意味着有任何实际的访问权限。USAGE权限是一个特殊的权限，它仅仅表示用户存在，但并不授予任何具体的数据库操作权限数据库级权限(1)GRANTSELECT,INSERTONmydatabase.*TO'newuser'@'localhost'(2)这条权限授予newuser在mydatabase数据库上的SELECT和INSERT权限。这意味着newuser可以查询mydatabase中的表，并向这些表中插入新数据项

目

实

施

任务六：对MySQL账户权限的基本操作（4）撤销权限使用REVOKE语句可以撤销MySQL用户的权限。例如，撤销newuser对mydatabase数据库的INSERT权限

若想撤销后再次授予newuser在mydatabase上的INSERT权限

项

目

实

施

任务六：对MySQL账户权限的基本操作（5）刷新权限在对MySQL用户的权限进行更改后，通常需要刷新权限表以使更改生效。可以使用以下命令来刷新权限在大多数情况下，不需要执行此操作，因为GRANT和REVOKE语句会自动更新权限并使其生效。项

目

实

施

任务七：创建数据库与表创建数据库选择数据库创建表给表添加字段和约束项

目

实

施

任务七：创建数据库与表（1）登录MySQL使用

mysql

–u

root

–p密码进行登录项

目

实

施

任务七：创建数据库与表（2）创建数据库在MySQL提示符下，使用CREATEDATABASE语句创建一个新的数据库

（3）使用新数据库病查看数据库列表使用USE语句选择数据库

使用SHOW语句查看数据库

项

目

实

施

任务七：创建数据库与表（4）创建表单（表）使用CREATE

TABLE语句创建表单。创建表的命令的参数说明及示例

项

目

实

施

任务七：创建数据库与表例5-1：使用USE语句选择数据库，然后使用CREATETABLE语句创建表例5-2：创建一个简单的departments表例5-3：创建一个名为employees的表，用于存储员工信息。这个表将包含员工的ID、姓名、职位、薪资、入职日期和部门ID等字段项

目

实

施

任务七：创建数据库与表（5）显示当前数据库中的所有表使用

SHOWTABLES;

命令成功可列出当前数据库

mydatabase

中的所有表，包括

departments、employees

和

mytable，共计3个表。这是一个标准的MySQL命令，用于显示指定数据库中的所有表

SHOWTABLES;

：执行的命令，用于列出mydatabase数据库中的所有表。Tables_in_mydatabase：输出结果的列标题，表示列出的是mydatabase数据库中的表。departments、employees、mytable：mydatabase数据库中的实际表名。3rowsinset(0.00sec)：表示查询返回了3行结果，并且查询执行时间非常短（0.00秒）。项

目

实

施

任务七：创建数据库与表（6）显示当前数据库中的表的结构使用

DESCRIBE

命令（或等效的

SHOWCOLUMNSFROM

命令）可以查看

mytable、departments

和

employees

这3个表的表结构

例5-4：显示上面创建的3个表的表结构项

目

实

施

任务八：插入表数据并验证在确保了employees表和departments表之间的关系通过外键约束正确设置之后，你可以向employees表中插入数据。但是，在插入之前，请确保departments表中已经存在你想要在employees表中引用的department_id。例5-4：先向departments表中插入一些数据（如果它们还不存在的话），然后向employees表中插入三条数据。插入完成后请进行验证解题思路：1.确保departments表中存在必要的部门

当执行这条语句时，MySQL将执行以下操作：检查departments表是否存在。检查department_id和department_name列是否存在于该表中。检查是否有任何与这些列相关的约束（如主键约束、外键约束、唯一性约束等）会被违反。如果所有检查都通过，MySQL将为每个提供的值对创建一条新记录。项

目

实

施

任务八：插入表数据并验证在这个例子中，假设department_id是主键或具有唯一性约束，那么每个department_id都必须是唯一的。由于为每个部门提供了一个唯一的department_id，因此这条语句应该能够成功执行，前提是表中尚未存在具有这些department_id的记录。执行这条语句后，可以使用以下查询来验证数据是否已正确插入：

返回departments表中的所有记录，包括刚刚插入的三条记录（如果表中没有其他记录的话）。

项

目

实

施

任务八：插入表数据并验证2.向departments表中添加3个部门要向departments表中再添加3条记录，只需再次使用INSERTINTO语句，并列出新的部门ID和部门名称。由于department_id字段被设置为AUTO_INCREMENT，通常您不需要手动指定它（除非有特定的原因需要这样做，比如迁移数据或填充已存在的ID）。添加完成后再次查看表departments的记录内容

项

目

实

施

任务八：插入表数据并验证3.向employees表中插入数据

向

employees

表中插入了3条记录，每条记录都包含了一个有效的

department_id，这些

department_id

对应于先前在

departments

表中插入的部门

在这个例子中，首先向departments表中插入了3个部门：Engineering、Marketing和Finance，每个部门都有一个唯一的department_id。然后，又向employees表中插入了3条记录，每条记录都引用了一个存在的department_id

。项

目

实

施

任务八：插入表数据并验证4.再向employees表中插入6条数据记录上面的插入语句将向employees表中添加以下员工：MichaelBrown，高级工程师，隶属于工程部（department_id=1）SarahLee，市场营销专员，隶属于市场部（department_id=2）DavidWilson，首席财务官，隶属于财务部（department_id=3）LisaTaylor，人力资源经理，隶属于人力资源部（department_id=4）RobertDavis，销售代表，隶属于销售部（department_id=5）KevinClark，IT支持专员，隶属于IT部（department_id=6）项

目

实

施

任务八：插入表数据并验证4.验证数据是否已正确插入例5-6：执行SQL语句，验证数据是否已正确插入项

目

实

施

任务九：数据库的备份与恢复1.数据库备份MySQL数据库的备份主要分为逻辑备份和物理备份两种方式。

备份步骤：（1）首先，确保MySQL服务正在运行。如果未运行，可以使用systemctlstartmysqld命令启动。逻辑备份是通过导出数据库中的数据和结构，生成SQL语句文件，这些文件可以在需要时重新导入数据库来恢复数据。项

目

实

施

任务九：数据库的备份与恢复（2）执行mysqldump命令来备份数据库。例如，要备份名为mydatabase的数据库，可以使用以下命令。其中，-u指定用户名（如root），-p会提示输入密码，可以输入root的密码“NewStrongPassword!”，/tmp/是备份文件的存储路径，mydatabase_backup.sql是备份文件的名称。输入密码（例如“NewStrongPassword!”）后，备份文件将保存在/tmp/mydatabase_backup.sql。（3）根据需要，可以备份所有数据库或特定的表。例如，备份所有数据库可以使用以下命令。项

目

实

施

任务九：数据库的备份与恢复2.删除备份过的数据库（用于恢复测试）项

目

实

施

任务九：数据库的备份与恢复3.使用逻辑备份恢复MySQL数据库（1）创建一个新的空数据库（如果尚未创建）

（2）导入备份文件到mydatabase：从MySQL命令行客户端中退出（通过quit命令），随后使用命令行将备份文件/tmp/mydatabase_backup.sql导入到名为mydatabase的MySQL数据库中</tmp/mydatabase_backup.sql：表示从文件/tmp/mydatabase_backup.sql中读取SQL语句并执行它们。<是Linux/Unix中的重定向操作符，用于将文件内容作为输入传递给前面的SQL命令。项

目

实

施

任务九：数据库的备份与恢复4.验证逻辑备份恢复（1）登录MySQL服务器并检查数据库

登录到MySQL服务器，并检查mydatabase数据库和是否存在。

项

目

实

施

任务九：数据库的备份与恢复（2）检查表的存在检查

mytable、departments

和

employees

表是否存在。

项

目

实

施

任务九：数据库的备份与恢复（3）检查表结构使用DESCRIBE命令检查每个表的结构。

项

目

实

施

任务九：数据库的备份与恢复（4）验证数据内容从每个表中选择几条记录，并与备份文件或预期的数据进行对比项

目

实

施

任务九：数据库的备份与恢复（5）检查外键约束（可选）如果

departments

和

employees

表之间有外键约束，可以检查这些约束是否正确恢复。

SHOWCREATETABLEemployees\G这条SQL命令用于显示名为employees的表的创建语句。在MySQL中，这个命令非常有用，因为它可以让我们看到创建该表时所使用的完整SQL语句，包括所有的列定义、索引、约束等信息

。项

目

实

施

任务九：数据库的备份与恢复5.物理备份MySQL数据库（1）关闭MySQL服务（为了数据一致性，建议在关闭MySQL服务后进行备份）（2）定位数据库文件

：MySQL的数据库文件通常存储在数据目录下，例如/var/lib/mysql/。在这个目录下，每个数据库都有一个对应的子目录，子目录内包含该数据库的表文件和其他相关文件。

（3）创建备份目录项

目

实

施

任务九：数据库的备份与恢复（4）复制departments和employees表的物理文件首先，找到mydatabase数据库目录下的departments和employees表对应的文件。在InnoDB存储引擎中，表文件以.ibd结尾，并且还有一个共享的表空间文件ibdata1（如果使用了独立表空间，则每个表都有自己的.ibd文件）。但是，直接复制这些文件可能会导致数据不一致，因为InnoDB使用了一种称为“表空间”的复杂存储机制。

假设使用的是MyISAM存储引擎，因为MyISAM表的物理文件是独立的，可以直接复制。在MyISAM中，每个表有两个文件：.frm（表定义）和.MYD（表数据），可能还有一个.MYI（表索引）文件。（5）重启MySQL服务项

目

实

施

任务九：数据库的备份与恢复6.物理恢复MySQL数据库并验证（1）关闭MySQL服务（2）清空

mydatabase

数据库中的

departments

和

employees

表（注意：这将删除表中的所有数据）（3）复制备份文件回原始位置（4）设置正确的权限项

目

实

施

任务九：数据库的备份与恢复（5）修复表（对于MyISAM表，有时需要这一步来确保表的一致性）修复命令的执行效果：

（6）验证恢复项

目

实

施

任务十：查询、修改、删除数据表1.查询数据表例5-7：下面使用WHERE子句来执行一些查询。（1）查询在Engineering部门工作的所有员工：使用SQL的

WHERE

子句和

JOIN

操作来从数据库中检索信息

项

目

实

施

任务十：查询、修改、删除数据表（2）查询薪资高于80000的员工：项

目

实

施

任务十：查询、修改、删除数据表（3）查询在2023年之后入职的员工：注意，这里的日期格式应该是与数据库中存储的格式相匹配的（通常是'YYYY-MM-DD'）。项

目

实

施

任务十：查询、修改、删除数据表（4）查询Marketing部门的部门名称：这条查询返回

departments

表中

department_id

为2的记录的

department_name

字段，即Marketing部门的名称。项

目

实

施

任务十：查询、修改、删除数据表（5）查询薪资在70000到90000之间的员工，包括他们的部门和职位：：项

目

实

施

任务十：查询、修改、删除数据表2.修改数据表例5-7：下面使用UPDATE语句来执行更新操作。使用UPDATE语句对表数据进行更新。（1）给JohnDoe加薪5%这条语句会将

employees

表中

first_name

为'John'且

last_name

为'Doe'的员工的

salary

增加5%。执行这条语句后，JohnDoe的新薪资将是他当前薪资的105%。项

目

实

施

任务十：查询、修改、删除数据表（2）将JaneSmith的职位从"ProductManager"更改为"SeniorProductManager"这条语句会将

employees

表中

first_name

为'Jane'且

last_name

为'Smith'的员工的

position

字段更新为'SeniorProductManager'。注意：在更改EmilyJones的部门之前，我们需要确保Engineering部门确实存在，并且其department_id为1。

（3）将EmilyJones的部门从"Finance"更改为"Engineering"项

目

实

施

任务十：查询、修改、删除数据表（4）查询员工表中的所有员工以验证更改

项

目

实

施

任务十：查询、修改、删除数据表（5）针对特定员工进行查询以验证更改