最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

MySQL体系结构及基础管理

XAMPP案例 admin 400浏览 0评论
本文以linux系统的MySQL5.7版本为例详细介绍MySQL的体系结构,因为在实际生产环境中MySQL的运行环境都是linux系统。同时介绍MySQL的基础管理,包括用户管理和权限管理等。
 MySQL体系结构
 MySQL客户端/服务器工作模型
MySQL是C/S架构,工作模型大致如下图所示:
drr82
MySQL客户端连接MySQL服务端mysqld有两种方式:第一种,通过本地socket文件连接,只能在本地使用,不依赖IP和端口。

-- socket文件路径通过下述命令找到socket = /xx/mysql.sock
cat /etc/my.cnf

-- 本地通过socket文件连接MySQL服务端:mysql -S socket文件路径
mysql -S /tmp/mysql.sock

第二种,远程TCP/IP连接,这种连接方式在本地和远程都可以使用,依赖IP和端口

-- mysql -u 用户名(默认就是root) -p -h ip地址 -P 端口
mysql -u root -p  -h 127.0.0.1 -P 3306 -- 建议回车后再输入密码

 mysqld工作原理

mysqld是MySQL的服务端,也称为MySQL实例,这个实例包括mysqld的后台守护进程和工作线程,每个线程都负责不同的任务,另外还有预分配的内存结构,实例的功能就是管理数据。mysqld的程序结构如下图所示:drr082mysqld分为server层(连接层+SQL层)和存储引擎层,server层中的连接层和SQL层并不是MySQL官方提出的分层,只是因为这样分层更加方便理解而已。当客户端输入[mysql -u root -p xxx -h ip地址-P 3306]回车后,就会连接到server端的连接层,连接层共用三个作用,分别是:①提供连接协议,tcp/ip协议;

②提供验证,验证客户端发送过来的用户名、密码、IP地址和端口信息是否正确;

③提供专用的连接线程,接收客户端发送过来的SQL语句并将SQL语句的执行结果返回给客户端;

ps:可以通过show processlist;查看连接线程的基本情况。

mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
| 30 | root | localhost:63292 | NULL | Query   |    0 | init  | show processlist |
+----+------+-----------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

连接mysqld成功之后,客户端会输入SQL语句后回车发送给服务端,由SQL层执行SQL语句,SQL层主要负责下述功能(功能较多):

①接收上层(连接层)发送的SQL语句;

②通过语法验证模块验证SQL语句是否符合语法规则;

③进行语义检查,判断SQL语句的类型(DDL/DML/DCL);

④进行权限检查,判断当前用户对当前SQL语句的执行是否有权限进行操作;

⑤解析器在SQL语句执行前进行预处理,生成多种执行计划,就像出门去目的地的几种路线一样;

⑥优化器根据解析器得到的多种执行计划进行评估,选择最优的执行计划;

⑦执行器会根据优化器选择的执行计划执行SQL语句,产生执行结果;

⑧提供查询缓存(query cache),但是这个功能有点鸡肋,默认是没有开启的,因为在实际开发中,通常会使用redis代替查询缓存功能;

⑨提供日志记录,比如binlog(二进制日志),默认也是没有开启的。

存储引擎层是和磁盘进行交互的模块,根据SQL层执行的结果从磁盘上获取数据,将16进制的数据交给SQL结构化成表,再通过连接层的专用线程返回给客户端。

MySQL的物理存储结构

页(page):MySQL的最小IO单元是页,一个页默认是16kb,由连续的4个OS block块组成;区(extent):一个区(簇),默认是1M,由连续的64pages构成。段:一张表就是一个段,可以由1个或者多个区构成,不一定是连续的区。分区表除外(一个段拆成几个段,每个段还是由1个或者多个区构成)MySQL使用段区页的概念就是为了连续IO,提高查询速度。MySQL体系结构总结
根据上述mysqld的结构和MySQL物理存储结构可以总结出MySQL体系结构图如下:drr0082MySQL基础管理
 用户管理
 用户的作用就是登录MySQL数据库并且管理MySQL对象,可以通过user表查看所有的用户。用户到底长啥样呢?就长这个样子[用户名@’白名单’],白名单是地址,允许白名单的IP 登录MySQL并且管理MySQL。比如:

root@'localhost'  -- root用户可以通过本地登录MySQL(socket登录)
gui@'10.0.0.10'  -- gui用户能够在IP地址为10.0.0.10的主机远程登录MySQL
root@'10.0.0.%'  -- root用户能够在IP地址为某个网段的主机上远程登录MySQL
root@'%'  -- root用户能够在任意主机上登录MySQL

 

如何对用户进行管理呢?就是如何对用户进行增删改查,请看下述SQL语句:

-- 查看当前所有的MySQL用户
mysql> select user, host, password from mysql.user;
+--------+-----------+-------------------------------------------+
| user   | host      | password                                  |
+--------+-----------+-------------------------------------------+
| root   | localhost | *6AD24CD1D6D180895D5EF57D84E41A40094C85DB |
|        | localhost |                                           |
| xu1    | 10.0.0.%  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| python | localhost |                                           |
| xu     | 10.0.0.%  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+--------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)


-- 新增用户mysql>create user python@'localhost';   -- 增加用户时没有设置密码
Query OK, 0 rows affected (0.00 sec)

mysql>create user xu@'10.0.0.%' identified by '123';  -- 创建xu@'10.0.0.%'用户,密码是123
Query OK, 0 rows affected (0.00 sec)

mysql> select user, host, authentication_string from mysql.user;
+--------+-----------+-----------------------+
| user   | host      | authentication_string |
+--------+-----------+-----------------------+
| root   | localhost |                       |
|        | localhost | NULL                  |
| python | localhost |                       |
| xu     | 10.0.0.%  |                       |
+--------+-----------+-----------------------+
4 rows in set (0.00 sec)

-- 对用户信息进行修改,在5.7版本中可以正常使用
mysql>alter user xu@'10.0.0.%' identified by '456'; --xu@'10.0.0.%'用户修改密码
Query OK, 0 rows affected (0.00 sec)

-- 删除用户
mysql> drop user xu@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

 权限管理

MySQL的用户和计算机的用户是类似的,不同的用户有不同的权限,MySQL中的权限管理就是管理用户对数据库的操作有哪些权限,用户权限的管理请看下述SQL:

-- 查看所有权限
mysql> show privileges\G;

对用户进行授权,需要通过grant语句。这里需要注意的是,在8.0版本之前,创建用户并赋予权限可以使用一条grant语句完成,但是在8.0版本及之后,都需要创建用户之后再对用户进行授权。推荐使用先创建用户再授权.

-- grant 权限 on 数据库对象 to 用户 (identified by '密码');
-- 权限的种类:
all:所有权限
权限1,权限2...:为开发人员和业务用户开通的部分权限
with grant option:超级管理员,可以为其他用户授权 --

-- 权限作用范围:
*.*:所有数据库的所有权限库名.*:某个数据库下的所有表库名.表名:某个数据库下的某张表 --

-- 授权举例
create user python@'%' identified by '123';
create user java@'%' identified by '123';

-- 为python@'%'用户授予超级管理员权限,可以操作所有的表并且可以为其他用户授权
grant all on *.* to python@'%' with grant option;  

-- 为java@'%'用户授予app库下的所有表的增删改查的权限并修改密码
grant select, update, insert, delete, create on app.* to java@'%' identified by '456';

 

既然能够为用户授予权限就能回收用户的权限,MySQL中不能通过重复授权对用户进行修改权限,只能通过回收权限的方式对用户的权限进行修改,如下:

-- 将java@'%'用户的create权限进行回收
grant select ,update,insert,delete on app.* to app@'10.0.0.%';  -- ❌,用户的权限并未修改
revoke create(权限) on app.*(对象) from app@'10.0.0.%'(用户);  -- ✔️,用户的权限修改成功,回收了用户的create权限

 连接管理

使用MySQL自带客户端连接MySQL服务端时,有以下常用的参数:

-u 用户名
-p 密码
-s 本地socket文件位置
-h 数据库IP地址
-P 端口号
-e 免交互执行数据库命令
< 导入sql脚本

使用本地socket文件连接:

-- 使用root用户登录时下述连接方式任选
mysql -u root -p 123 -S /tmp/mysql.sock(my.cnf中可以查看mysql.sock文件的位置)
mysql -u root -p -S /tmp/mysql.sock ---- 推荐方式
mysql 
mysql -p

使用tcp/ip远程连接,前提是必须创建好可以远程连接的用户:

mysql -u python -p 123 -h 112.126.87.94 -P 3306

转载请注明:XAMPP中文组官网 » MySQL体系结构及基础管理

您必须 登录 才能发表评论!