mysql体系结构和管理

一. mysql数据库的体系结构

1.1 mysql的C/S模型

1.1.1 特点

  • 非对等相互作用，服务器有客户端不具备的硬件和软件资源、运算能力，服务器提供服务，客户请求服务

1.1.2 客户端和服务器的数量关系

  • 多个客户进程可以同时访问一个服务进程，一个客户进程可以同时访问多个服务器提供的服务

1.1.3 客户端和服务器的位置关系

  • 客户端和服务器运行在同一个机器上，应用于网络应用程序开发测试。
  • 客户端和服务器运行在同一个局域网内的不同机器上，应用于局域网文件共享、局域网打印机。
  • 客户端和服务器运行与广域网不同网络内的机器上，应用于最常见的网络应用程序。

1.1.4 客户端和服务器的角色关系

  • 应用程序作为纯粹的客户运行，如Web服务器。
  • 应用程序作为纯粹的服务器运行，如局部基本功能的ftp服务器。
  • 应用程序同时具备客户和服务器的两种角色。

1.1.5 工作过程

  • 服务器首先启动监听程序，对指定的端口进行监听，等待接收客户端的连接请求。
  • 客户端启动程序，请求连接服务器的指定端口。
  • 服务器收到客户端的连接请求后，与客户端建立套接字连接。
  • 连接建立成功，客户端与服务器分别打开两个流，其中客户端的输入流连接到服务器的输出流，服务器的输入流连接到客户端的输出流，两边的流连接成功后进行双向通信。
  • 当通信完毕后，客户端和服务器两边各自断开连接。

1.1.6 优点

  • 结构简单。
  • 支持分布式、并发环境，有效提高资源的利用率和共享程度。
  • 服务器集中管理资源，有利于权限控制和系统安全。
  • 可扩展性较好，客户和服务器均可单独升级。

1.2 mysql的TCP/IP模式

 
image.png

1.2.1 解释说明

  • TCP/IP套接字方式是MySQL在任何平台下都提供的连接方式，也是网络中使用得最多的一种方式。这种方式在TCP/IP连接上建立一个基于网络的连接请求，一般情况下客户端在一台服务器上，MySQL实例在另一台服务器上，这两台机器通过一个TCP/IP网络连接

1.2.2 TCP/IP方式与数据库建立连接命令

  • mysql -uroot -p123456 -h10.0.0.5
    通过网络连接串，TCP/IP：(用户名；密码；IP；port)
     
    image.png

1.2.3 socket(套接字)连接方式

  • Unix域套接字
    在Linux和Unix环境下，还可以使用Unix域套接字。Unix域套接字其实不是一个网络协议，所以只能在MySQL客户端和数据库实例在同一台服务器上的情况下使用(本地连接)。你可以在配置文件中指定套接字文件的路径，如-socket=/tmp/mysql.sock。
    当数据库实例启动后，我们可以通过下列命令来进行Unix域套接字文件的查找：
    登录实例后查询
     
    image.png

    或者通过socket文件登录MySQL数据库中
  • mysql -uroot -p123456 -S /application/mysql/tmp/mysql.sock
    通过套接字文件，SOCKET：（用户名；密码；Socket路径）

二. mysql数据库实例

  • mysqld是一直在运行的，类似于nginx一样的一个守护进程；mysqld对于内存是预分配的，运行时先分配好内存再使用
  • mysqld进程-----master thread（主线程）------N个干活的线程thread+工作需要的内存区域
  • 一个完整的数据库管理系统=实例+数据

三. mysql的程序结构

 
image.png

3.1 图形解析

  • 1）mysql是一个C/S架构模型，客户端通过与服务端建立连接来操作服务端数据；
  • 2）服务端的连接模块，将每一个客户端发送来的请求作为一个线程处理；
  • 3）分析器分析请求，并转发给优化器；
  • 4）通过缓存的方式提高查询性能；
  • 5）优化器负责和底层的存储引擎进行交互，存储和查询mysql的数据；

3.2 SQL语句执行过程各层次功能

3.2.1 连接层

（1）命令

  • mysql -uroot -pxxx
    （2）功能
    1. 提供链接协议，Linux中提供socket与TCP/IP的连接协议
    1. 验证用户名，密码等信息
    1. 提供连接线程（show full processlist查看），接收SQL语句，返回执行结果

3.2.2 SQL层

（1）命令

  • select user,host,authentication_string from mysql.user;
    （2）功能

    1. 语法检查模块，检查上层发过来的SQL语句，进行语法检查
    1. 权限检查模块，监测当前登录用户是否有权限操作数据库对象
    1. 语义检查模块，识别语句种类
    1. 解析器，解析出SQL语句所有可能的执行方式，生成计划树，这些方式被称为“执行计划”
    1. 优化器，基于执行代价（系统消耗的资源作为纬度CPU,IO,Mem等）管理员可以通过间接的方法干预优化器选择
    1. 执行器，按照优化器自带算法选择“最优”的执行计划，执行SQL语句，得出结论，发送结果给存储引擎层
    1. 提供查询缓存，默认没有开启，有局限性，一般会用redis，tair等产品替代他的功能
    1. 提供记录查询日志（binlog）默认没有开启
      （3）SQL层子模块

  • 1）初始化模块：数据库启动时，对数据库的初始化操作；

  • 2）核心API模块：底层操作的优化功能；

  • 3）网络交互模块：对外提供可接收发送数据的API接口；

  • 4）服务器客户端交互协议模块：实现客户端服务端的交互协议；

  • 5）用户模块：控制用户连接登录和授权；

  • 6）访问控制模块：监控用户的每一个操作，依赖于用户模块；

  • 7）连接管理、连接线程和线程管理模块：监听和管理与客户端连接的线程；

  • 8）转发模块：将请求转发到对应的处理模块；

  • 9）缓存模块：将查询请求的结果缓存，提高性能；

  • 10）优化器模块：根据查询请求计算提高查询访问速度的优化策略，根据最优策略返回查询语句；

  • 11）表变更模块：DML和DDL的语句处理；

12）表维护模块：检测表状态、分析、优化表结构、修复表；

  • 13）系统状态管理模块：将各种状态数据返回，如：show status;

  • 14）表管理器：维护系统生成的表文件如：.frm文件.ibd文件...将表结构的信息缓存，另外该模块还管表级别的锁；

  • 15）日志记录模块：负责整个数据库逻辑层的日志文件；

  • 16）复制模块：分为Master模块和Slave模块；Master模块负责复制binary文件，并与Slave端I/O线程交互；Slave模块主要负责从Master端接收binary日志，并写入本地I/O线程，以及从relay log文件中读取日志，解析成Slave端执行的命令，交给Slave端的SQL线程处理；

  • 17）存储引擎接口模块：实现了底层存储引擎插件式管理，将数据处理高度抽象化；
    （4）sql逻辑模块协调工作

  • 1）mysql启动以后，初始化模块就从系统配置文件中读取系统参数和命令参数，初始化整个系统，同时存储引擎也会启动；

  • 2）初始化结束后，连接管理模块会监听客户端的连接请求，并将连接请求转发给线程管理模块去请求一个连接线程；

  • 3）线程模块接到请求后会调用用户模块进行授权检查，通过授权以后会检查是否又空闲线程，如果有取出并与客户端连接，如果没有则新建立建立一个线程与客户端连接；

  • 4）mysql请求分为两种，一种是需要命令解析和分发才能执行，另一种可以直接执行；不管哪种，如果开启了日志，那么日志模块会记录日志；

  • 5）如果是Query类型的请求，会将控制权交给Query解析器，Query解析器检查是否Select类型，如果是则启动查询缓存模块，如果缓存命中则将缓存数据返回给连接线程模块，连接线程将数据传递到客户端；如果没有缓存或者不是一个可以缓存的查询，此时解析器会进行相应的处理，通过查询分发器给相关的处理模块；

  • 6）如果解析器结果是DML/DDL，则交给变更模块；如果是检查、修复的查询交给表维护模块，如果是一条没有被缓存的语句，则交给查询优化器模块。实际上表变更模块又分为若干小模块，例如：insert处理器、delete处理器、update处理器、create处理器，以及alter处理器这些小模块来负责不同的DML和DDL。总之，查询优化器、表变更模块、表维护模块、复制模块、状态模块都是根据命令解析器的结果不同而分发给不同的类型模块，最后和存储引擎进行交互。

  • 7）当一条命令执行完毕后，控制权都会还给连接线程模块，在上面各个模块处理过程中都依赖于核心API模块，比如：内存管理、文件I/O，字符串处理等。

3.2.3 存储引擎层

（1）功能

    1. 和“磁盘”（文件系统）打交道的层次
    1. 根据SQL层执行的结果，去磁盘上找到对应的数据，结构化成表的模式，返还给用户

四. mysql的逻辑结构

4.1 逻辑存储结构

 

 
image.png

数据库可划分为被称为表空间的逻辑存储单元。每一个表空间可以包含很多的Oracle 逻辑数据块。DB_BLOCK_SIZE 参数指定了逻辑块的大小。逻辑块的大小范围为2 KB 至 32 KB，默认大小为8 KB。特定数目的相邻逻辑块构成了一个区。为特定逻辑结构分配 的一组区构成了一个段。Oracle 数据块是逻辑I/O 的最小单位。

 

4.2 存储数据方式

 

 
image.png

创建表时，就会创建段来保存表数据。表空间包含一组段。从逻辑上讲，表包含由列值 组成的行。行最终将以行片段的形式存储在数据库块中。之所以称为行片段，是因为某些情 况下，不可以在一个位置存储一整行。当插入行由于太长而不适合单个块时，或者由于更 新而导致现有行大小超出了行的当前空间时，就会发生这种情况。

 

4.3 表空间和数据文件

Oracle 数据库（tablespace）是由若干个表空间构成的。任何数据库对象在存储时都必 须存储在某个表空间中。表空间对应于若干个磁盘文件，即表空间是由一个或多个磁盘文件 构成的。表空间相当于操作系统中的文件夹，也是数据库逻辑结构与物理文件之间的一个映 射。每个数据库至少有一个表空间，表空间的大小等于所有从属于它的数据文件大小的总和

4.4 MySQL的逻辑结构

4.4.1 库（database，scheama）

mysql库的存储就相当于linux中的目录，在磁盘的盘上就是一个目录，包括库名，库属性

4.4.2 表（table）

mysql表就相当于linux中的文件，使用多个文件存储表的信息，其中包括表名称，表属性，表列（统称为表的元数据）以及数据行

4.4.3 数据行介绍

（1）MyISAM引擎（三文件构成一张表）

    1. myi文件：存储索引相关信息
    1. myd文件：存储数据行信息
    1. frm文件：存储列的信息
      （2）InnoDB引擎（两文件构成一张表）
    1. time_zone.frm文件：存储列的信息
    1. time_zone.ibd文件：存储数据行和索引信息
      （3）InnoDB引擎底层存储结构
    1. 段（SEGMENT）：一个表就是一个段，在我们进行数据存储时，MySQL会在表段中至少分配一个区，也就是IM的物理存储空间，按实际数据大小进行分段，最少一个区或多个区。
    1. 区（EXTENT）：连续64个页构成的一个区，固定大小为1M。
    1. 页（PAGE）：MySQL中默认的最小IO单元，默认16k，可根据需求进行更改。

五. MySQL基础管理

5.1 用户管理

5.1.1 mysql用户作用

1. 登录mysql数据库
2. 管理mysql的对象（库与表）

5.1.2 mysql用户的表现方式

1. 用户名@'白名单'
**2. 比如：userl@'白名单'

5.1.3 白名单的支持设置

- userl@'10.0.0.200'
- userl@'10.0.0.%'
- userl@'10.0.0.5%'
- userl@'10.0.0.0/255.255.255.0'
- userl@'rdbmsa.com'
- userl@'%'
- userl@'localhost'
- userl@'db01'

5.1.4 用户的增删改查

1. 增：创建用户

  • create user oldguo@'10.0.0.%' identified by '123';
    2. 删：删除用户
  • drop user oldguo@'10.0.0.%';
    3. 改：修改用户密码
  • set password for oldguo@'10.0.0.%'=PASSWORD('456');
  • alter user oldguo@'10.0.0.%' identified by '456';（为创建用户但没有创建密码的用户添加密码）
    4. 查：查询用户
  • select user,host(,authentication_string) from mysql.user;（第一种）
  • select concat(user,"@","'",host,"'")from mysql.user;（第二种）
  • 上诉对于用户的管理操作在mysql8.0版本前后都可应用，并且可以利用grant命令自动创建用户并授权，而8.0版本之后则只能按上诉的管理命令，grant只做授权功能，不可创建用户（原因在于存储引擎的变化）

5.2 权限管理

5.2.1 mysql的权限作用

开启用户的管理对象的功能

5.2.2 mysql权限定义

按命令进行定义，赋予某个用户或网站什么样的权限

5.2.3 权限范围（与linux对比）

（1）*.*：全库级别，相当于给linux根下所有权限（-R 755）
（2）wordpress.*：单库级别，相当于给linux下某个目录授权，给某些开发人员使用
（3）wordpress.t1：单表级别，相当于给linux下某个目录下的文件授权，很少使用

5.2.4 grant命令重新解读

  • 举例：grant all on . to wordpress@'10.0.0.%' identified by '123';
    （1）授权wordpress@'10.0.0.%'能够对.（所有库和表），进行all（所有命令操作），除了grant和revoke**
    **（2）如果wordpress@'10.0.0.%'不存在则会被自动创建，并设定密码为'123'

5.2.5 权限回收

（1）权限查询

  • show grants for wordpress@'10.0.0.%';
  • show +grants+for（来自）+用户及网段
    （2）权限回收
  • revoke delete on . from wordpress@'10.0.0.%';
  • revoke+权限+on +对象范围+from+用户及网段

5.2.6 权限示义

ALL或ALL PRIVILEGES  代表指定权限等级的所有权限。
ALTER   允许使用ALTER TABLE来改变表的结构，ALTER TABLE同时也需要CREATE和INSERT权限。重命名一个表需要对旧表具有ALTER和DROP权限，对新表具有CREATE和INSERT权限。
ALTER ROUTINE   允许改变和删除存储过程和函数
CREATE  允许创建新的数据库和表
CREATE ROUTINE  允许创建存储过程和包
CREATE TABLESPACE   允许创建、更改和删除表空间和日志文件组
CREATE TEMPORARY TABLES 允许创建临时表
CREATE USER 允许更改、创建、删除、重命名用户和收回所有权限
CREATE VIEW     允许创建视图
DELETE  允许从数据库的表中删除行
DROP    允许删除数据库、表和视图
EVENT   允许在事件调度里面创建、更改、删除和查看事件
EXECUETE    允许执行存储过程和包
FILE        允许在服务器的主机上通过LOAD DATA INFILE、SELECT ... INTO OUTFILE和LOAD_FILE()函数读写文件
GRANT OPTION    允许向其他用户授予或移除权限
INDEX   允许创建和删除索引,mysql索引
INSERT  允许向数据库的表中插入行
LOCK TABLE  允许执行LOCK TABLES语句来锁定表
PROCESS 允许显示在服务器上执行的线程信息，即被会话所执行的语句信息。这个权限允许你执行SHOW PROCESSLIST和mysqladmin processlist命令来查看线程，同时这个权限也允许你执行SHOW ENGINE命令
PROXY   允许用户冒充成为另外一个用户
REFERENCES  允许创建外键
RELOAD  允许使用FLUSH语句
REPLICATION CLIENT  允许执行SHOW MASTER STATUS,SHOW SLAVE STATUS和SHOW BINARY LOGS命令
REPLICATION SLAVE   允许SLAVE服务器连接到当前服务器来作为他们的主服务器
SELECT  允许从数据库中查询表
SHOW DATABASES  允许账户执行SHOW DATABASE语句来查看数据库。没有这个权限的账户只能看到他们具有权限的数据库。
SHOW VIEW   允许执行SHOW CREATE VIEW语句
SHUTDOWN    允许执行SHUTDOWN语句和mysqladmin shutdown已经mysql_shutdown() C API函数
SUPER   允许用户执行CHANGE MASTER TO,KILL或mysqladmin kill命令来杀掉其他用户的线程，允许执行PURGE BINARY LOGS命令，通过SET GLOBAL来设置系统参数，执行mysqladmin debug命令，开启和关闭日志，即使read_only参数开启也可以执行update语句，打开和关闭从服务器上面的复制，允许在连接数达到max_connections的情况下连接到服务器。
TRIGGER 允许操作触发器
UPDATE  允许更新数据库中的表
USAGE   代表没有任何权限，只能登陆

5.2.7 授权管理生产需求实例

（1）授权一个管理员用户admin，密码为admin，可以通过10网段管理mysql

  • grant all on . to admin@'10.0.0.%' identified by 'admin';
    （2）开放一个wordpress应用的用户，密码123，nginx服务器使用，172.16.1.%网段
  • 应用用户权限（select ，insert ，update ，delete）
  • grant select,insert,update,delete on wordpress.* to wordpress@'172.16.1.%' identified by '123';
    （3）中小型公司（无专业DBA）开发人员授权leader，需要开发用户dev通过10网段对wordpress进行开发和管理
  • grant SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER,CREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,EVENT,TRIGGER on wordpress.* to dev@'10.0.0.%' identified by '123';

5.3 连接管理

5.3.1 MySQL自带命令（mysql）

-u：指定连接用户
-p：指定连接用户密码
-h：指定远程连接ip
-e：免交互执行（直接在命令行进行执行数据库）
-P：指定连接端口
-S：利用socket文件进行登录
-V：查看当前使用的mysql版本
-<：向数据库导入文件使用
--help：mysql命令的查询帮助

5.3.2 mysql的连接方法

（1）socket本地连接

  • mysql -uroot -p123 -S /tmp/mysql.sock
    （2）TCP/IP远程连接
  • mysql -uoldguo -p123 -h10.0.0.51 -P3306

5.3.3 客户端连接工具

（1）先授权

  • grant all on . to root@'10.0.0.%' identified by '123';
    （2）再连接
  • sqlyog工具与navicat工具

5.4 启动方式管理

前提说明：
1. mysqld存在bin目录下，不能直接使用，需要一些配置文件
2. mysql_safe存在bin目录下，可以加一些参数，特殊方法启动mysql
3. mysql.server（sys-v）存在/application/mysql/support_file文件中，启动最终还是要使用mysqld启动mysql

第一：mysql.server（sys-v）
调用此处：cp  /application/mysql/support_file/mysql.server   /etc/init.d/mysqld
启动：/etc/init.d/mysqld  start
关闭：/etc/init.d/mysqld  stop

第二：systemd
调用mysqld：在/etc/systemd/system/mysqld.service文件中的ExecStart模块中
启动：systemctl  start mysqld
关闭：systemctl  stop mysqld

第三：mysqld
调用mysqld：which mysqld可以查看命令位置
启动：mysqld  &
关闭：mysqladmin  -uroot   -p123  shutdown
（此命令是万金油，用什么方法启动的数据库都可以用此方法关闭）

第四：mysql_safe  &
命令跳过验证直接登录：mysql_safe  --skip-grant-tables  --skip-networking  & （在连接层跳过了验证直接登录）
---最后：以上所有方式最终调用的都是mysqld---

5.5 初始化配置

5.5.1 配置方式

5.5.1.1 预编译

cmake时指定参数，一般建议修改一些不会经常变化的参数

5.5.1.2 初始化配置文件（主流）

  • mysqld --help --verbose|grep my.cnf（命令）
  • /etc/my.cnf ---》 /etc/mysql/my.cnf ---》 /usr/local/mysql/etc/my.cnf ---》 ~/.my.cnf（显示）
    读取顺序如箭头所标

5.5.1.3 使用命令行参数干预mysql启动

通过mysql_safe指定socket文件及端口号进行启动

  • mysqld_safe --defaults-file=/tmp/my3306.cnf
    （此时要想启动mysql必须利用-S参数指定socket文件）

5.5.2 初始化配置文件的管理（/etc/my.cnf）

1. 查询：mysqld --help --verbose|grep my.cnf
2. 显示：1. /etc/my.cnf     2. /etc/mysql/my.cnf      3. /usr/local/mysql/etc/my.cnf  4. ~/.my.cnf 
---   从1--4依次读取，默认是找到最后一个为准（都需要手动创建）
3. 扩展参数：（--defaults-file）
    例：把/etc/my.cnf文件cp一个它不能读取的文件（不是那四个中的一个），修改文件中的路径，启动时：mysqld_safe  --defaults-file=cp后的文件路径+&即可开启数据库，一旦使用此参数，上诉查找过程不会执行
4. 作用：（1）影响数据库启动   （2）影响客户端登录
5. 格式：
   [标签]
   xxx=yyy（内容）
   [标签]
   xxx=nnn（内容）
6. 标签：
   -客户端
   [mysql]
   [mysqldump]
   [mysqladmin]
   [client]

   -服务端
   [mysqld]
   [mysqld_safe]
   [server]

7. 模板
   -vim /etc/my.cnf
   [mysqld]
   user=mysql
   basedir=/application/mysql
   datadir=/application/mysql/data
   socket=/application/mysql/tmp/mysql.sock
   port=3306
   log_error=/var/log/mysql.log
   server_id=6
   [mysql]
   user=root
   password=123
   socket=/application/mysql/tmp/mysql.sock
   prompt=\\ oldguo [\\d]
   -/etc/init.d/mysqld  restart（重启生效）



作者：假面骑士kabuto
链接：http://suoyin.dbbull.com/

posted @ 2021-11-22 09:10  DB哥  阅读(229)  评论(0)  编辑  收藏  举报