MySQL的体系结构与基础管理
2020-01-05 本文已影响0人
静如止水yw
一、MySQL的工作模型
MySQL主要分为客户端模型和服务端模型
- 服务端程序：mysqld
- 客户端程序：mysql mysqldump mysqladmin
- 连接协议：
(1) 网络套接字：
TCP/IP网络连接串 ---> mysql -uroot -p123 -h10.0.0.51 -P3306
(2) 本地套接字：
socket本地连接串 ---> mysql -uroot -p123 -S /tmp/mysql.sock
图1 体系结构
二、MySQL实例
实例=mysqld+MAster Thread+worker Threads+专用内存
图2 实例流程图
三、mysqld程序结构
1. SQL是什么
结构化查询语言，关系型数据库中的专用命令。mysql的程序结构：连接层、SQL层、存储引擎层。
图3 mysqld程序结构
2. 一条SQL语句的执行逻辑
（1）连接层
a. 提供连接协议：TCP/IP、SOCKET
b. 提供验证：用户、密码、IP、SOCKET
c. 提供专用连接线程：接收用户SQL，返回结果
通过以下语句可以查看到连接线程基本情况
mysql> show processlist;
（2）SQL层***
a. 接收上层传送的SQL语句
b. 语法验证模块：验证语句语法，是否满足SQL_MODE
c. 语义检查：判断SQL语句的类型
DDL : 数据定义语言
DCL : 数据控制语言
DML : 数据操作语言
DQL : 数据查询语言
d. 权限检查：用户对库表有没有权限
e. 解析器：对语句执行前，进行预处理，生成解析树(执行计划)，简单的说就是生辰多种执行方案
f. 优化器：根据解析器得出的多种执行计划，进行判断，选择最优的执行计划.
代价模型：资源(CPU IO MEM) 的耗损评估性能好坏.
g. 执行器：根据最优执行计划，执行SQL语句，产生执行结果(执行结果：在磁盘的xxxx位置上).
h. 提供查询缓存(默认是没有开启的)，会使用redis tair替代查询缓存功能
i. 提供日志记录(日志管理章节)：binlog(默认是没开启的)
（3）存储引擎层(类似与Linux中的文件系统)
a. 负责根据SQL层执行的结果，从磁盘上拿数据.
b. 将16进制的磁盘数据，交由SQL结构化成表.
c. 连接层的专用线程返回给用户.
图4 一条SQL语句的执行逻辑
3. 逻辑结构
图5 数据库的逻辑结构
- 库：库名(database) / 库属性(schema)
- 表：表名、表属性、字段(列/表结构)、数据行(行)
4. MySQL对象物理结构
- 宏观：一个库对应一个目录
- 微观：如下图
图6 MySQL对象物理结构
表的段、区、页
每一长数据表(segment段)都包含有extent区(默认是1M)，每一个extent包含有连续的64个page(默认16KB)，一个page包含4个连续的os block(默认是4K)，一个os block包含连续的8个扇区(默认是512B)
四、MySQL基础管理
1. MySQL用户管理
- 作用：登录和管理对象
- 定义：用户名@'白名单'
用户名：不要太长，要和业务有关
白名单支持的方式（常用的几种方式）:
wordpress@'10.0.0.%' #10网段任意地址
wordpress@'10.0.0.0/255.255.255.0' #23位
wordpress@'10.0.0.5%' #50-59
wordpress@'localhost' #本地
其他：
wordpress@'10.0.0.56'
wordpress@'%' #任意地址
wordpress@'10.0.0.%' 255.255.255.0 #24位
- 用户管理操作
增加用户：
mysql> create user wyw@'10.0.0.%' identified by '123';
查询用户：
mysql> desc mysql.user; ----> authentication_string
mysql> select user,host,authentication_string from mysql.user;
修改用户：
mysql> alter user wyw@'10.0.0.%' identified by '123456';
删除用户：
mysql> drop user wyw@'10.0.0.%';
2. mysql权限管理
- 权限管理操作
mysql> grant all on *.* to wordpress '%' identified by '123';
- 常用权限介绍：
ALL:
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
ALL : 以上所有权限，一般是普通管理员拥有的
with grant option：超级管理员才具备的，给别的用户授权的功能
- 权限作用范围
*.* ---->管理员用户
wordpress.* ---->开发和应用用户
wordpress.t1
- 企业授权案例
（1）授权一个管理员用户wyw, 可以从10网段任意地址登录管理数据库
mysql> grant all on *.* wyw@'10.0.0.%' identified by '123' with grant option;
（2）授权一个业务用户app，可以从10网段地址访问app库的所有表
mysql> grant select,update,insert,delete on app.* to app@'10.0.0.%' identified by '123';
（3）授权一个开发用户dev，可以对dev库进行业务开发
mysql> grant select,create,insert on dev.* to dev@'10.0.0.%' identified by '123';
- root管理员密码忘记或被篡改如何处理
# 关闭数据库，并启动到单用户模式
/etc/init.d/mysqld stop 或者 systemctl stop mysqld
mysqld_safe --skip-grant-tables --skip-networking &
--skip-grant-tables #连接层关闭验证模块，所有验证表不加载
--skip-networking #连接层关闭TCP/IP协议，禁止远程访问.
#无密码登录MySQL并修改密码
mysql> flush privileges;
mysql> alter user root@'localhost' identified by '123';
#重启数据库到正常模式
/etc/init.d/mysqld restart
mysql -uroot -p123
- 查询用户权限
mysql> show grants for app@'10.0.0.%';
+---------------------------------------------------------------------+
| Grants for app@10.0.0.% |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'app'@'10.0.0.%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `app`.* TO 'app'@'10.0.0.%' |
+---------------------------------------------------------------------+
- 回收权限
mysql> remove delete,drop on app.* from app@'10.0.0.%';
mysq> show grants for app@'10.0.0.%';
+-------------------------------------------------------------+
| Grants for app@10.0.0.% |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'app'@'10.0.0.%' |
| GRANT SELECT, INSERT, UPDATE ON `app`.* TO 'app'@'10.0.0.%' |
+-------------------------------------------------------------+
3. MySQL连接管理
- 自带客户端工具
(1) mysql
-u 用户名
-p 密码
-h IP
-P 端口
-S socket位置
-e 免交互
< 导入SQL脚本
案例：
1. TCP连接串远程登录
注：需提前创建用户
例子:
mysql> grant all on *.* to oldguo@'10.0.0.%' identified by '123';
[root@db01 data_3306]# mysql -uoldguo -p -h 10.0.0.51 -P 3306
Enter password:
2. socket连接方式
注:需要提前创建好localhost用户
mysql> grant all on *.* to oldguo@'localhost' identified by '123';
[root@db01 data_3306]# mysql -uoldguo -p -S /tmp/mysql.sock
Enter password:
3. 免交互式的登录
[root@db01 ~]# mysql -uroot -p -e "show processlist"
4. 导入SQL脚本
mysql -uroot -p < t100w.sql #命令行导入
mysql> source /root/world.sql #在数据库里导入
(2) mysqladmin
#修改密码
mysqladmin -uroot -p123456 password 123
mysql -uroot -p123
#关闭数据库
mysqladmin -uroot -p123 shutdown
#重启数据库
systemctl start mysqld（或者/etc/init.d/mysql start）
如何验证一个用户是通过远程还是通过本地登录的(show processlist)
图7 验证数据库是从本地登录还是远程登录
- 第三方开发工具
sqlyog、navicat、workbench - 应用程序连接
php-mysql
pip install mysql
jar
go
4. MySQL启动关闭
- 启动方式：
systemctl ---->mysql.server start ----->mysqld_safe ---->mysqld（service启动方式（centos6版本的启动））
systemctl start mysqld
/etc/init.d/mysql start
mysql_safe & ## 不打印日志到前台
mysqld ## 打印日志在前台
5. MySQL初始化配置
- 配置方法（优先级）
源码安装定制<初始化配置文件< 命令行启动时定制 - 初始化配置文件
mysqld --help --verbose|grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default
##依次从左到右读取，后面的会覆盖前面的
MySQL配置文件的默认读取顺序
/etc/my.cnf
basedir/my.cnf
datadir/my.cnf
--defaults-extra-file
~/.my.cnf
#建议一个mysql实例一个配置文件
- 配置文件书写格式
[root@db01 data_3306]# cat /etc/my.cnf
[mysqld]
user=mysql
port=3306
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3306
server_id=6
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
标签项 --->[mysqld]
服务器端：[mysqld],[mysqld_safe] ---> 影响到MySQL启动
客户端： [clinet],[mysql],[mysqldump] ---> 影响本地客户端程序
配置项 ----->key=value
- 命令行(mysqld mysqld_safe)
--default
--skip-grant-tables
--skip-networking
- 优化配置文件
mysqld --defaults-file=/opt/a.cnf & (需要提前准备好配置文件)
