MySQL C/S结构介绍

#TCP/IP
mysql -uroot -p123456 -hlocalhost -P3306
#Socket
mysql -uroot -p123456 -S /tmp/mysql.sock

MySQL实例的构成

  • 实例:mysqld+master+thread+预分配的内存

MySQL中mysqld服务器进程结构

SQL语句引入

结构化的查询语言

  • DQL : 数据查询语言
  • DDL : 数据定义语言
  • DML : 数据操作语言
  • DCL : 数据控制语言

连接层

  • 提供连接协议(Socket,TCP/IP)
  • 验证用户名密码合法性
  • 派生一个专用连接线程show processlist;

如果忘记密码,可以使用这个方法

SQL层

  • 验证SQL语法和SQL_MODE
  • 验证语义
  • 验证权限
  • 解析器进行语句解析,生成执行计划(解析树)
  • 优化器(各种算法,基于执行代价),根据算法,找到代价最低的执行计划,代价是消耗主机的资源
  • 执行器按照优化器选择执行计划,执行SQL语句,得出获取数据的方法
  • 提供query cache(默认不开),一般不开,会用redis
  • 记录操作日志(binlog),默认没开

存储引擎层

  • 根据SQL层提供的取数据的方法,拿到数据,返回给SQL,结构化成表,再又连接层线程返回给用户

MySQL物理存储结构

MyISAM

  • *.frm : 存储的表结构(列,列属性)
  • *.MYD : 存储的数据记录
  • *.MYI : 存储索引

InnoDB

  • *.frm : 存储的表结构(列,列属性)
  • *.ibd : 存储的数据记录和索引
  • ibdata1 : 数据字典信息
  • 一般情况下(非分区表),一个表就是一个段,一个段由多个区构成,一个区为16k,64个连续的区大小为1M

段,区,页

有数据都被逻辑地存放在一个空间中,我们称之为表空间(tablespace).表空间又由段(segment),区(extent),页(page)组成

  • 段 : 1个表相当于1个段 1个段可以有1个或多个区
  • 区 : 64个连续的页 大小为1M 称为区
  • 页 : 1页默认16K

QQ截图20200309105943.jpg

用户和权限管理

用户的定义

#用户名@'白名单'
wordpress@'%'
wordpress@'localhost'
wordpress@'127.0.0.1'
wordpress@'10.0.0.%'
wordpress@'10.0.0.5%'
wordpress@'10.0.0.0/255.255.254.0'
wordpress@'10.0.%'

用户的操作

#创建一个1997sty用户限制ip为10.0.0.0/24,密码123456
create user 1997sty@'10.0.0.%' identified by '123456';
#8.0以前,可以自动创建用户并授权
grant all on *.* to 1997sty@'10.0.0.%' identified by '123456';
#查询用户
select user,host from mysql.user;
#修改密码
alter user 1997sty@'10.0.0.%' identified by '654321';
#删除用户
drop user 1997sty@'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
#授予所有库的所有表的所有权限
grant all on *.* to 1997sty@'10.0.0.%' identified by '123456' with grant option;
#授予wordpress库的所有表的增删改查权限
grant SELECT,INSERT,UPDATE,DELETE on wordpress.* to 1997sty@'10.0.0.%' identified by '123456' with grant option;
#回收1997sty所有库的所有表的删除权限
revoke delete on *.* from '1997sty'@'10.0.0.%';
#查看1997sty的权限
show grants for 1997sty@'10.0.0.%';

关于生产中开用户

如何沟通开用户

  • 是否有邮件批复
  • 对哪些库和表做操作
  • 做什么操作
  • 从什么地址来登录

开发人员找你要root用户密码,怎么办

  • 走流程拒绝他
  • 如果是金融类的公司,原则上是不允许任何非DBA人员持有或申请root,如果有人私下索要root密码,立刻举报