Mysql体系结构与管理

1. 体系结构

1.1 C/S结构介绍

image-20210327115713609.png

客户端-服务器(Client/Server)结构简称C/S结构，是一种网络架构，通常在该网络架构下的软件分为客户端和服务器。

服务器是整个应用系统资源的存储和管理中心，多个客户端分别各自处理相应的功能，共同实现完整的应用。在客户/服务器结构中，客户端用户的请求被传送到数据库服务器，数据库服务器进行处理后，将结果返回给用户，从而减少网络数据的传输量。

用户在使用应用程序时，首先启动客户端，然后通过相关命令告知服务器进行连接以完成各种操作，而服务器则按照此请示提供相应的服务。每一个客户端软件的实例都可以向一个服务器或应用程序服务器发出请求。

客户端和服务器程序通常不在同一台计算机上运行，比如，我们平时在当当网上买书的时候，所使用的电脑和网页浏览器就被当做了一个客户端，同时，组成当当网的电脑、数据库和应用程序就被当做服务器。

1.2 Mysql客户端和服务端两种连接方式

  1. 1、TCP/IP方式（远程、本地）
  2. mysql -uroot -p123456 -h 10.0.0.36 -P3306
  4. 2、Socket方式（仅本地）
  5. mysql -uroot -p123456 -S /tmp/Mysql.sock

1.3 Mysql实列介绍

Mysql多实例，简单理解就是在一台服务器上，Mysql服务开启多个不同的端口（如3306、3307），运行多个服务进程。这些 Mysql 服务进程通过不同的 socket来监听不同的数据端口，进而互不干涉的提供各自的服务。

在同一台服务器上，Mysql 多实例会去共用一套 Mysql 应用程序，因此你在部署 Mysql 的时候只需要部署一次 Mysql程序即可，无需多次部署。但是，Mysql多实例之间会各自使用不同的 my.cnf 配置文件、启动程序和数据文件。在提供服务方面，Mysql多实例在逻辑上看起来是各自独立，互不干涉的，并且多个实例之间是根据配置文件的设定值，来获取相关服务器的硬件资源。

下面用一个比喻，来帮助大家理解 Mysql 多实例的本质。

  • Mysql 多实例相当于合租房，合租房里面有多个租客，每个租客都租有一个卧室，这个卧室就相当于我们的 Mysql 的一个实例。整个合租房就相当于一台服务器。合租房里面的洗衣机、卫生间、阳台就相当于我们服务器上的各种硬件资源，比如CPU、MEM、DISK等，这些东西都是公共资源，大家共用的。
  • 另外，多实例并不仅仅是 Mysql才有，其实我们日常运维中碰到的很多服务都可以部署使用多实例，并且在生产环境中也非常热衷去使用，甚至在门户网站应用也很广泛，例如nginx多实例、apache多实例、redis多实例等等。
  • 既然大家都去用 Mysql 多实例这种技术，那么Mysql 多实例都能为企业带来什么或者说有什么优缺点呢？

1.3.1 优点如下：

  • 有效利用服务器资源：当单个服务器资源过剩时，可以充分利用剩余的资源来提供更多的服务
  • 节约服务器资源：当公司资金紧张，但数据库又需要数据库之间各自提供服务时，并且还想使用主从同步等技术，此时多实例就再好不过了
  • 方便后期架构扩展：当公司的某个项目才启动时，启动初期并不一定有很大的用户量，因此可以先用一组物理数据库服务器，在上面部署多个实例，方便后续架构扩展、迁移

1.3.2 缺点如下：

  • 资源互相抢占问题：当某个服务实例并发很高或者有慢查询时，整个实例会消耗更多的内存、CPU和IO资源，这将导致服务器上的其它实例提供服务的质量下降。这就比如说合租房的各个租客，每当早晨上班时，都会洗漱，此时卫生间的占用率就大，各个租客总会发生等待。

1.3.3 多实例的应用场景

下面在重复一下，Mysql 多实例在生产环境下的应用场景！

  • 当一个公司业务访问量不太大，又想节俭成本，并且还希望不同业务的数据库服务能够各自尽量独立，提供服务能够互相不受影响。另外还需要应用主从同步等技术来提供数据库备份或读写分离服务，以及方便后期业务量增大时，数据库架构的扩展和迁移。此时，Mysql 多实例就再好不过了。比如，我们可以通过在 3 台服务器部署 6-9 个实例，然后交叉做主从同步备份及读写分离，来实现 6-9 台服务器才能够达到的效果
  • 公司业务访问量不是太大的时候，服务器的资源基本都是过剩状态。此时就很适合 Mysql 多实例的应用。如果对 SQL语句 优化做的比较好，Mysql 多实例 是一个很值得去使用的技术。即使后期业务并发很大，只要合理分配好系统资源，也不会有太大的问题
  • 为了规避 Mysql 对 SMP 架构不支持的缺陷，我们可以使用 Mysql 多实例绑定处理器的办法（NUMA处理器必须支持，不过现在大部分处理器都支持的）将不同的数据库分配到不同的实例上提供数据服务；
  • 传统游戏行业的 MMO/MMORPG以及Web Game，会将每个服都对应一个数据库，而且可能经常要做很多数据查询和数据订正工作。此时，为了减少维护而出错的概率，我们也可以采用多实例的部署方式，按区的概念来分配数据库。

1.3.4 Mysql多实例实现的3种方式

说了这么多，Mysql 多实例要如何去部署呢？部署方法有哪些哪些呢？

Mysql 多实例常规来讲，有三种方案可以实现，这三种方案各有利弊，如下：

1、基于多配置文件

通过使用多个配置文件来启动不同的进程，以此来实现多实例。

优点：逻辑简单，配置简单

缺点：管理起来不方便

2、基于mysqld_multi

通过官方自带的 mysqld_multi 工具，使用单独配置文件来实现多实例

优点： 便于集中管理管理

缺点： 不方便针对每个实例配置进行定制

3、基于IM

使用 Mysql 实例管理器（MYSQLMANAGER），这个方法好像比较好不过也有点复杂

优点：便于集中管理

缺点：耦合度高。IM一挂，实例全挂

1.4 Mysqld程序运行原理

1.4.1 Mysqld程序结构

image-20210328114546136.png

1.4.2 一条SQL语句的执行过程

  1. 连接层：
  2. 1、提供连接协议：TCP/IP、SOCKET
  3. 2、提供验证：用户、密码，IP，SOCKET
  4. 3、提供专用连接线程：接受SQL，返回结果
  5. 通过以下语句可以查看到连接线程基本情况
  6. mysql> show processlist;
  8. SQL层：
  9. （1）接收上层传送的SQL语句
  10. （2）语法验证模块：验证语句语法,是否满足SQL_MODE
  11. （3）语义检查：判断SQL语句的类型
  12. DDL ：数据定义语言
  13. DCL ：数据控制语言
  14. DML ：数据操作语言
  15. DQL： 数据查询语言
  16. ...
  17. （4）权限检查：用户对库表有没有权限
  18. （5）解析器：对语句执行前,进行预处理，生成解析树(执行计划),说白了就是生成多种执行方案.
  19. （6）优化器：根据解析器得出的多种执行计划，进行判断，选择最优的执行计划
  20.         代价模型：资源（CPU IO MEM）的耗损评估性能好坏
  21. （7）执行器：根据最优执行计划，执行SQL语句，产生执行结果
  22. 执行结果：在磁盘的xxxx位置上
  23. （8）提供查询缓存（默认是没开启的），会使用redis替代查询缓存功能
  24. （9）提供日志记录（日志管理章节）：binlog，默认是没开启的。
  26. 存储引擎层：（类似于Linux系统中的文件系统）
  27. 负责根据SQL层执行的结果，从磁盘上拿数据。
  28. 将16进制的磁盘数据，交由SQL结构化化成表，
  29. 连接层的专用线程返回给用户。

1.5 逻辑结构

image-20210328142958800.png

  1. 库：
  2. Mysql中的库由库名和库属性组成
  4. 表：
  5. 表由属性、列（我们俗称字段）和列属性、数据行组成

1.6 物理存储结构引入

image-20210328143219217.png

1.6.1 库的物理存储结构

  1. 用目录来存储：
  2. #1、我们可以现在现有的数据库里查询
  3. mysql> show databases;
  4. +--------------------+
  5. | Database           |
  6. +--------------------+
  7. | information_schema |
  8. | mysql              |
  9. | performance_schema |
  10. | sys                |
  11. +--------------------+
  12. 没有wordpress这个数据库
  14. #2、然后我们/data/mysql/data/里面创建wordpress目录
  15. [root@mysql-36 data]# cd /data/mysql/data/
  16. [root@mysql-36 data]# mkdir -p wordpress
  17. [root@mysql-36 data]# chown mysql:mysql wordpress/
  19. #3、然后再用show databases;查看是否有这个数据库
  20. mysql> show databases;
  21. +--------------------+
  22. | Database           |
  23. +--------------------+
  24. | information_schema |
  25. | mysql              |
  26. | performance_schema |
  27. | sys                |
  28. | wordpress          |
  29. +--------------------+
  30. 5 rows in set (0.00 sec)
  32. 事实证明数据库的物理存储就是一个目录

1.6.2 表的物理存储结构

  1. MyISAM（一种引擎）的表：
  2. -rw-r----- 1 mysql mysql   10816 Apr 18 11:37 user.frm
  3. -rw-r----- 1 mysql mysql     396 Apr 18 12:20  user.MYD
  4. -rw-r----- 1 mysql mysql    4096 Apr 18 14:48 user.MYI
  6. InnoDB(默认的存储引擎)的表：
  7. -rw-r----- 1 mysql mysql    8636 Apr 18 11:37 time_zone.frm
  8. -rw-r----- 1 mysql mysql   98304 Apr 18 11:37 time_zone.ibd
  9. time_zone.frm：存储列相关信息
  10. time_zone.ibd：数据行+索引

1.6.3 表的段、区、页（了解）

  1. 页：最小的存储单元，默认16k
  2. 区：64个连续的页，共1M
  3. 段：一个表就是一个段，包含一个或多个区

2. 基础管理

2.1 用户、权限管理

2.1.1 用户管理

  1. 作用：
  2. 登陆管理数据库的逻辑对象
  4. 定义：
  5. 用户名@'白名单'
  6. 白名单支持的方式？
  7. wordpress@'10.0.0.%'    
  8. wordpress@'%'
  9. wordpress@'10.0.0.200'
  10. wordpress@'localhost'
  11. wordpress@'db02'
  12. wordpress@'10.0.0.5%'
  13. wordpress@'10.0.0.0/255.255.254.0'
  15. 管理操作：
  16. 1、添加用户：
  17. mysql> create user xujun@'10.0.0.%' identified by '123456';
  18. Query OK, 0 rows affected (0.00 sec)
  20. 2、查询用户
  21. mysql> desc mysql.user;
  22. mysql> select user,host from mysql.user;
  23. +---------------+-----------+
  24. | user          | host      |
  25. +---------------+-----------+
  26. | xujun         | 10.0.0.%  |
  27. | mysql.session | localhost |
  28. | mysql.sys     | localhost |
  29. | root          | localhost |
  30. +---------------+-----------+
  32. 3、修改用户密码
  33. mysql> alter user xujun@'10.0.0.%' identified by '123';
  35. 4、删除用户
  36. mysql> drop user xujun@'10.0.0.%';

2.1.2 权限管理

  1. 权限管理操作：
  2. mysql> grant all on wordpress.* to wordpress@'10.0.0.%' identified by '123456';
  4. 语句解析：
  5. grant 权限  on 作用目标  to 用户  identified by 密码;
  1. 常用权限介绍：
  2. SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
  3. ALL : 以上所有权限，一般是普通管理员拥有的
  4. with grant option：超级管理员才具备的，给别的用户授权的功能
  1. 权限作用范围：
  2. *.*              ---->管理员用户
  3. wordpress.*      ---->对wordpress数据库里的所有表进行授权
  4. wordpress.t1     ---->对wordpress数据库中的t1表进行授权
  1. 查看和回收权限：
  3. 查看：
  4. mysql> show grants for wordpress@'10.0.0.%';
  6. 回收权限：
  7. mysql> revoke delete on wordpress.* from wordpress@'10.0.0.%';

2.1.3 授权测试题

  1. 1.创建一个管理员用户root，可以通过10网段，管理数据库
  2. create user root@'10.0.0.%';
  3. grant all on *.* to root@'10.0.0.%' identified by '123456';
  5. 2.创建一个应用用户wordpress，可以通过10网段，wordpress库下的所有表进行SELECT,INSERT, UPDATE, DELETE
  6. create user wordpress@'10.0.0.%' identified by '123456';
  7. grant SELECT,INSERT,UPDATE,DELETE on wordpress.* to wordpress@'10.0.0.%' identified by '123456';

3. 注意：8.0在grant命令的新特性

  1. 建用户和授权分开了
  2. grant 不再支持自动创建用户了，不支持改密码
  3. 授权之前，必须要提前创建用户。