MySQL体系结构与管理-1

1 MySQL C/S结构介绍

两种连接方式：

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

mysql -uroot -psyxk123 -h 10.0.0.51 -P3306

Socket方式(仅本地)：

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

2 MySQL实例的构成

公司： 老板 + 经理 + 员工 + 办公区
实例： mysqld + master thread + 干活的Thread + 预分配的内存

3 MySQL中mysqld服务器进程结构

mysql出现故障或性能问题80%以上是人为的。

3.1 SQL语句引入

结构化的查询语言
DQL 数据查询语言 select和show
DDL 数据定义语言 create和drop
DML 数据操作语言 insert 和update 和 delete
DCL 数据控制语言 grant 和 revoke

mysql> select user,host from mysql.user;

3.2 连接层

(1) 提供连接协议
Socket
TCP/IP
(2) 验证用户名（root@localhost）密码合法性，进行匹配专门的授权表。

(3) 派生一个专用连接线程（接收SQL，返回结果）

mysql> show processlist;

思考：
忘记密码的参数在哪做的手脚？
--skip-grant-tables
--skip-networking

3.3 SQL层(优化方面至关重要的)

（1）验证SQL语法和SQL_MODE
（2）验证语义
（3）验证权限
（4）解析器进行语句解析，生成执行计划（解析树）
（5）优化器（各种算法，基于执行代价），根据算法，找到代价最低的执行计划。
　　  代价：CPU  IO  MEM
（6）执行器按照优化器选择执行计划，执行SQL语句，得出获取数据的方法。
（7）提供query cache(默认不开)，一般不开，会用redis
（8）记录操作日志（binlog），默认没开

3.4 存储引擎层

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

3.5 MySQL逻辑存储结构

 

 

 

 

 

库：库名，库属性
表：表名，表属性
列:列名(字段),列属性(数据类型,约束等)
数据行(记录)

3.6 MySQL物理存储结构

库：使用FS上的目录来表示
表：
MyISAM(ext2)
user.frm：存储的表结构（列，列属性）
user.MYD：存储的数据记录
user.MYI ：存储索引

InnoDB(XFS)
time_zone.frm ：存储的表结构（列，列属性）
time_zone.ibd ：存储的数据记录和索引
ibdata1：数据字典信息

3.7 innodb 段 区 页

一般情况下（非分区表）
一个表就是一个段
一个段由多个区构成
一个区在（16k），64个连续的页，1M大小（一个区是连续的64个页，为1M大小）

SyXk.ibd

数据行存储：每次默认64个连续的page,也就是1M,我们把它称之为一个区，Mysql表根据存储的需求,会由多个区构成，我们又把表称之为一个段.

4 用户和权限管理

4.1 作用

登录MySQL
管理MySQL

4.2 用户的定义

用户名@'白名单'

白名单支持的方式？

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.%'

4.3 用户的操作

4.3.1 建用户

mysql> create user syxk@'10.0.0.%' identified by '123';
(建用户最标准的用法)
Query OK, 0 rows affected (0.00 sec)

说明：
8.0以前，可以自动创建用户并授权

提示：8.0在grant命令添加新特性，建用户和授权分开了，grant 不再支持自动创建用户了，不支持改密码。授权之前，必须要提前创建用户。

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

4.3.2 查询用户

mysql> select user,host from mysql.user;

4.3.3 修改用户密码

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

4.3.4 删除用户

mysql> drop user syxk@'10.0.0.%' ;

4.4 权限管理

4.4.1 权限列表

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

4.4.2 授权命令

grant all on *.* to syxk@'10.0.0.%' identified by '123' with grant option;

grant 权限 on 作用目标 to 用户 identified by 密码 with grant option;

grant SELECT,INSERT, UPDATE, DELETE, CREATE on wordpress.* to

作用目标：
*.*
wordpress.*
wordpress.t1

4.4.3 授权需求

1.创建一个管理员用户root，可以通过10网段，管理数据库.

grant all on *.* to root@'10.0.0.%' identified by '123' with grant option;

2.创建一个应用用户wordpress，可以通过10网段，wordpress库下的所有表进行SELECT,INSERT, UPDATE, DELETE.

grant SELECT,INSERT,UPDATE,DELETE on wordpress.* to wordpress@'10.0.0.%' identified by '123';

4.4.4 查看授权

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

4.4.5 回收权限

mysql> revoke delete on wordpress.* from 'wordpress'@'10.0.0.%';
mysql> show grants for wordpress@'10.0.0.%';

4.4.6 关于生产中开用户

(1) 如何沟通开用户

1. 是否有邮件批复
2. 对哪些库和表做操作
3. 做什么操作(不允许drop及拥有root用户)
4. 从什么地址来登录

(2) 开发人员找你要root用户密码？（不给）

1. 走流程拒绝他
2. 如果是金融类的公司

（1）原则上是不允许任何非DBA人员持有或申请root
（2）如果有人私下索要root密码，立刻（及时）举报。