体系结构和基础管理
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’
- 授权本地用户
mysql> grant all on *.* to oldguo@'localhost' identified by '123';
- 连接测试
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.%’
- 授权远程用户
mysql> grant all on *.* to oldguo@'10.0.0.%' identified by '123';
- 连接测试
[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 配置文件作用
- 影响数据库服务端的启动和运行
- 本地客户端的连接
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。