体系结构和基础管理

体系结构和基础管理

1 体系结构

1.1 C/S(客户端/服务端)模型介绍**

TCP/IP方式（远程、本地）：
mysql -uroot -poldboy123 -h 10.0.0.51 -P3306
Socket方式(仅本地)：
mysql -uroot -poldboy123 -S /tmp/mysql.sock

1.2 实例介绍

实例=mysqld后台守护进程+Master Thread +干活的Thread+预分配的内存

公司=老板+经理+员工+办公室

1.3 mysqld程序运行原理

1.3.1 mysqld程序结构

1.3.2 一条SQL语句的执行过程

1.3.2.1 连接层

（1）提供连接协议：TCP/IP 、SOCKET

（2）提供验证：用户、密码，IP，SOCKET

（3）提供专用连接线程：接收用户SQL，返回结果

通过以下语句可以查看到连接线程基本情况

mysql> show processlist;

1.3.2.2 SQL层 （重点）

（1）接收上层传送的SQL语句

（2）语法验证模块：验证语句语法

（3）语义检查：判断SQL语句的类型

DDL ：数据定义语言

DCL ：数据控制语言

DML ：数据操作语言

DQL：数据查询语言

（4）权限检查：用户对库表有没有权限

（5）解析器：进行SQL的预处理，产生执行计划

（6）优化器：根据解析器得出的多种执行计划，进行判断，选择最优的执行计划

        代价模型：资源（CPU IO MEM）的耗损评估性能好坏

（7）执行器：根据最优执行计划，执行SQL语句，产生执行结果

执行结果：在磁盘的xxxx位置上

（8）提供查询缓存（默认是没开启的），会使用redis tair替代查询缓存功能

（9）提供日志记录（日志管理章节）：binlog，默认是没开启的。

1.3.2.3 存储引擎层（类似于Linux中的文件系统）

负责根据SQL层执行的结果，从磁盘上拿数据。将16进制的磁盘数据，交由SQL架构化成表，由连接层的专用线程返回给用户。

1.4 逻辑结构

1.4.1 库：库名，库权限，库数据类型等

1.4.2 表（二维表)

列：字段

行：记录

MySQL为了将来数据的准确、规范、有意义，对于传统认知的二维表添加了更多的功能

表属性：权限，存储引擎，字符集

列属性：数据类型、约束、其他属性（默认值，自增长，注释）

安全管理

1.1 用户管理

1.1.1 作用

Linux中用户
登录系统
管理系统对象
MySQL 用户
登陆MySQL
管理MySQL的对象

1.1.2 用户的命名

Linux 中
字符串代表用户
MySQL 中
用户名@‘白名单’
白名单？
定义的是允许登陆数据库的IP列表。

白名单支持的方式：
###oldboy@‘localhost’ # 允许本地的oldboy用户登陆数据库
oldboy@‘10.0.0.2’ # 允许10.0.0.2地址上的oldboy用户登陆数据库
###oldboy@‘10.0.0.%’ # 允许10.0.0.0/24网段的地址oldboy用户登陆数据库
oldboy@‘10.0.0.0/255.255.254.0’ # 允许10.0.0.0/23网段的地址oldboy用户登陆数据库
###oldboy@‘10.0.0.5%’ # 允许10.0.0.50-59
oldboy@’%’ # 允许所有IP地址链接
oldboy@‘db01’ # 允许db01主机名的地址登陆

1.1.3 用户管理

1 查询用户

mysql> select user,host ,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+

2 创建用户

mysql> create user oldguo@'10.0.0.%' identified by '123';
mysql> create user oldboy@'localhost' identified by '123';

3 删除用户

mysql> drop user oldboy@'localhost';

4 修改密码

mysql> alter user oldguo@'10.0.0.%' identified by '123456';

5 修改用户密码

mysql> alter user oldguo@'10.0.0.%' identified by '123456';

1.2 权限管理

1.2.0
GRANT 权限 ON 权限范围 TO 用户 IDENTIFIED BY ‘密码’;

小彩蛋：

8.0以前版本：
GRANT 权限 ON 权限范围 TO 用户 IDENTIFIED BY ‘密码’;
功能：
1. 创建用户
2. 授权

8.0 以后注意： Grant 命令只是授权命令，不能进行建用户和改密码。
语法变为了：
GRANT 权限 ON 权限范围 TO 用户 ，不支持改密码。

8.0 以后密码方面的“坑”。
密码验证方式从Native 换成了sha2插件。
大家自行扩展一下。

1.2.1 权限定义

ALL ： 以下列表中除了 Grant option 所有权限
Create,Delete,Insert ,…

mysql> show privileges;

1.2.2 权限范围

chmod -R 777 /
chmod -R 777 /oldboy
chmod -R 777 /oldboy/t1

. : 管理员 全库级别
oldboy.* ：普通用户 单库级别
oldboy.t1 ：不常用 单表级别
列 ：不常用 单列级别

1.2.3 授权管理

授权例子 ： 
#1. 授权一个管理员用户 
grant all on *.* to adm_oldguo@'10.0.0.5%' identified by 'OldGuo@123.Com' with grant option;

PS with grant option  oldguo用户可以去管理其他用户

2. 授权一个业务用户wp_user1用户可以通过10.0.0.% 操作wordpress库下所有表
grant select ,insert ,update, delete on wordpress.*  to wp_user1@'10.0.0.%' identified by 'Wpuser@123.Com';

回收权限
注意： MySQL中 修改权限，不能重复授权。所有授权都是相加关系。所以需要回收权限。
mysql> revoke delete on wordpress.* from wp_user1@'10.0.0.%';


3. 查看授权 
mysql> show grants for wp_user1@'10.0.0.%';

彩蛋

我们的权限都存在哪里呢？
授权表？
user ： 存储用户信息 + 全库级别的权限
db : 用户针对单库级别的权限
tables_priv ： 针对表级别的权限
columns_priv ： 针对列级别的权限
procs_priv ：
proxies_priv ：

2. MySQL 的连接管理

2.1 自带客户端程序

mysql 命令

2.1.1 连接方式（协议）

#本地 socket 方式登陆
前提： 需要提前准备能够通过localhost登陆的用户。例如： oldguo@‘localhost’

1. 授权本地用户

mysql> grant all on *.* to oldguo@'localhost' identified by '123';

2. 连接测试

mysql> select @@socket;
+-----------------+
| @@socket        |
+-----------------+
| /tmp/mysql.sock |
+-----------------+
1 row in set (0.00 sec)

[root@db01 ~]# mysql -uoldguo -p123 -S /tmp/mysql.sock

远程 TCP/IP 方式登陆

前提： 必须要提前授权远程连接的用户。例如：oldguo@‘10.0.0.%’

1. 授权远程用户

mysql> grant all on *.* to oldguo@'10.0.0.%' identified by '123';

2. 连接测试

[root@db01 ~]# mysql -uoldguo -p123 -h 10.0.0.51 -P 3306 

2.2 客户端工具

SQLyog
Navicat

2.3 程序连接 (了解一下)

PHP : php-mysql API驱动
Python ： pyMysql
JAVA
GO
C , C++
.NET
C#
perl
ruby
…

3. 初始化配置管理

3.1 方式

源码编译时
配置文件
命令行

3.2 配置文件应用

3.2.1 默认配置文件读取顺序

[root@db01 ~]# mysql --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

假设： /etc/my.cnf —> socket=/tmp/mysql.sock ~/.my.cnf --> socket=/tmp/my.sock

3.2.2 配置文件作用

1. 影响数据库服务端的启动和运行
2. 本地客户端的连接

3.2.3 配置文件的结构

[标签]
配置项=xxxx
[标签]作用是什么？
用来区分客户端和服务端的
标签的分类：
服务端 ： [server] 、 [mysqld] 、[mysqld_safe]
客户端 : [client] 、 [mysql] 、 [mysqldump]…

[root@db01 ~]# cat /etc/my.cnf 
[mysqld]                     # 服务器程序标签
user=mysql                   # 管理用户
basedir=/data/app/mysql      # 程序目录
datadir=/data/3306/data      # 数据目录
server_id=6                  # 数据库ID号，主从时使用，区分不同节点。
socket=/tmp/mysql.sock       # socket文件位置

[client]                     # 客户端标签
socket=/tmp/mysql.sock       # 连接数据库时使用的socket位置

3.2.4 自定制配置文件位置

mv /etc/my.cnf /opt
pkill mysqld 
[root@db01 ~]# mysqld_safe --defaults-file=/opt/my.cnf & 

4. MySQL的启动和关闭

6和

4.1 画图说明mysql的启动方式

略。
1. 通过直接调用程序启动
/data/app/mysql/bin/mysqld &
/data/app/mysql/bin/mysqld_safe   &

好处在于，灵活的在命令行添加需要的配置参数。
2. 通过sys-v  systemd 方式启动 
service mysqld restart /stop /start /status 
systemctl start/stop/restart/status  mysqld
好处在于，启动关闭方便。但是需要依赖配置文件，不能单独添加（service除外）参数启动数据库。

4.2 关闭方法

service mysqld stop
systemctl stop mysqld
mysql -e "shutdown"
mysqladmin shutdown 

5 . MySQL的体系结构

2.1 MySQL C/S工作模型

客户端 ------> 服务端
C S

TCP/IP方式（远程、本地）：

mysql -uroot -poldboy123 -h 10.0.0.51 -P3306

Socket方式(仅本地)：

mysql -uroot -poldboy123 -S /tmp/mysql.sock

实例概念

实例：

mysqld核心守护进程（Boss）+
Master Thread（经理）+
干活的线程（员工）+
预分配内存（办公室）

mysqld核心主程序结构

连接层 ： 连接协议，验证，连接线程
1.3.2.1 连接层
（1）提供连接协议：TCP/IP 、SOCKET
（2）提供验证：用户、密码，IP，SOCKET
（3）提供专用连接线程：接收用户SQL，返回结果
通过以下语句可以查看到连接线程基本情况
mysql> show processlist;

SQL层 ： 语法，语义，权限， 解析预处理（代价），选择执行计划，执行SQL
小细节：
1. 查询缓存。
select user,host from mysql.user; hash—》 HASH_ID + 结果。
use mysql ; select user,host from user;
2. 日志记录。binlog , slowlog …

（1）接收上层传送的SQL语句
（2）语法验证模块：验证语句语法,是否满足SQL_MODE
（3）语义检查：判断SQL语句的类型
DDL ：数据定义语言
DCL ：数据控制语言
DML ：数据操作语言
DQL： 数据查询语言
（4）权限检查：用户对库表有没有权限
（5）解析器：对语句执行前,进行预处理，生成解析树(执行计划),说白了就是生成多种执行方案.
（6）优化器：根据解析器得出的多种执行计划，进行判断，选择最优的执行计划
代价模型：资源（CPU IO MEM）的耗损评估性能好坏
（7）执行器：根据最优执行计划，执行SQL语句，产生执行结果
执行结果：在磁盘的xxxx位置上
（8）提供查询缓存（默认是没开启的），会使用redis tair替代查询缓存功能
（9）提供日志记录（日志管理章节）：binlog，默认是没开启的。

存储引擎层： 和磁盘交互的层次。相当于MySQL内置的文件系统。负责数据的存取。
负责根据SQL层执行的结果，从磁盘上拿数据。
将16进制的磁盘数据，交由SQL结构化化成表，
连接层的专用线程返回给用户。

2.4 逻辑结构（抽象结构）

Linux中一切皆文件，一切皆命令。
MySQL中一切皆表，一切皆SQL。
库 ： 库名 ， 库属性 ，相当于Linux中的目录。
表 ： 表名，列(列名,列属性,约束)，数据行，表属性

2.5 物理层

2.5.1 宏观角度
用文件系统的目录来存储

/data/3306/data 下的数据信息介绍：
库： 目录

表： 多个构成

mysql.user表 (MyISAM)： 
user.frm   : 存储的表的列信息
user.MYD   ：存储的表的数据行
user.MYI   ：存储的是索引(相当一本书中的目录)

mysql.time_zone(InnoDB): 
time_zone.frm ： 存储的表的列信息
time_zone.ibd ： 存储的表的数据行+索引
其他的信息： 放一放

2.5.2 InnoDB物理微观角度

段 ： segment，表段，非分区表就是一个段。一个或多个extents组成
区 ： extents ，簇，连续64个页，默认1M
页 ： pages ，数据页，默认大小是16KB，连续4个OS block 最小的存储单元，默认16k

为什么这么设计？
为了能够让MySQL的使用磁盘尽可能的都是顺序IO。