mysql_2 体系结构

标签（空格分隔）： mysql

原博客： https://www.jianshu.com/p/e872bc12f583
原视频： https://www.bilibili.com/video/BV157411K7sf

连接mysql

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

执行过程

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

sql层

（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，默认是没开启的。

存储引擎层

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

逻辑结构

库（操作系统下的目录）
名称属性

表
字段记录
属性字段对应的数据
数据类型
约束

关系
库
表
记录字段

表

myisam
frm 框架信息
myd 数据行
myi 索引信息

innodb
frm 存储相关信息
ibd 数据行+索引

段区页

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

配置文件

cat /etc/my.cnf
[mysqld]
user=mysql #服务器端标签
basedir=/app/database/mysql #软件的安装位置
datadir=/data/3306 #数据存放位置
server_id=6 #表示节点的唯一编号主从用
port=3306 #端口号
socket=/tmp/mysql.sock #套接字文件

[mysql] #客户端标签
socket=/tmp/mysql.sock #读取socket文件位置点

启动

systemctl restart mysqld ----> mysqld.server -----> mysql_safe --> mysqld

mysqld_safe 和 mysqld 可以启动数据库时，加入自己执行的参数

关闭

systemctl stop mysqld
service mysqld stop
/etc/init.d/mysqld stop
mysqladmin -u root -p 123 shutdown
mysql -uroot -p 123 -e "shutdown"

mysql 多实例

1.规划
配置文件 3 分 /data/330{7..9}/my.cnf
数据目录 3 分 /data/330{7..9}
初始化数据 3分
日志目录 3分 /binlog/330{7..9}
socket 3 分 /tmp/mysql330{7..9}.sock
端口 3分 port=3307,3308,3309
server_id 3份 server_id=7,8,9
配置过程创建文件写好配置文件复制几份到固定位置

SQL介绍

结构化的查询语言，关系型数据库中通用的一类语言
sql标准有 89 92 99 03 等等
MYSQL

help contents 分类命令

For more information, type 'help ', where is one of the following
categories:
Account Management
Administration
Compound Statements
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Help Metadata
Language Structure
Plugins
Procedures
Table Maintenance
Transactions
User-Defined Functions
Utility

mysql sql基础

sql 语言
DDL 用户定义语言
DML 数据控制语言
DCL 数据操作语言
DQL 数据查询语言

sql mode 校对规则排序规则

影响了字符排序例如：大写字符和小写字符的排序

ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中
NO_AUTO_VALUE_ON_ZERO：该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。
STRICT_TRANS_TABLES：在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制
NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零
NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。
ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER：禁止GRANT创建密码为空的用户
NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
PIPES_AS_CONCAT：将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似
ANSI_QUOTES：启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

字符集以及校对规则
utf8 默认三个字节不支持表情
utf8mb4 支持表情四个字节

create databases zabbix charset utf8mb4;

校对规则

每种字符集有多种校对规则（排序规则）

a
b
ab
Ab
aB
ba
A
B
A Ba a aB b
ASCII码的规则

show collation;

数据类型

数据类型存储长度二进制数二进制数范围

网上有关于数据类型的介绍

https://www.runoob.com/mysql/mysql-data-types.html

enum 枚举
('成都','北京','上海')

json{
id:1
name:"sab"
}

约束
PK
NOT NULL
UNIQUE
UNSIGNED

SQL应用
client
c 结束上条命令运行
G 格式化输出
q 退出mysql会话

DDL 数据定义语言
库定义库名库属性
创建库
create database 库名 charset utf8mb4;

规范
1.库名大写业务有关不要数字开头库名不要太长不能使用保留字符串
2.必须指定字符集

查库
show databases；
show create database 库名

修改库
alter database 库名 charset 字符集

删除库
drop database 库名;

查看权限 show privileges

白名单

用户名@'白名单'
白名单支持的方式？
wordpress@'10.0.0.%'    
wordpress@'%'
wordpress@'10.0.0.200'
wordpress@'localhost'
wordpress@'db02'
wordpress@'10.0.0.5%'
wordpress@'10.0.0.0/255.255.254.0'

权限

增：
mysql> create user oldboy@'10.0.0.%' identified by '123';
查：
mysql> desc mysql.user;    ---->  authentication_string
mysql> select user ,host ,authentication_string from mysql.user
改:
mysql> alter user oldboy@'10.0.0.%' identified by '456';
删：
mysql> drop user oldboy@'10.0.0.%';

权限列表

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：超级管理员才具备的，给别的用户授权的功能

回收权限

revoke  delete on app.*  from app@'10.0.0.%'；

查看权限

mysql> show grants for app@'10.0.0.%';

保存

mysqldump
mysqladmin

mysqldump -root -p xxdb > /tmp/xxdb.sql