1.MySQL数据目录

1.1 MySQL8主要数据目录

数据库文件的存放路径：

1	/var/lib/mysql

相关命令目录：

1 2	/usr/bin #mysqladmin, mysqlbinlong, mysqldump等 /usr/sbin

配置文件目录：

1 2	/usr/share/mysql-8.0 #命令以及配置文件 /etc/mysql #my.cnf

1.2 数据库与文件系统

1	SHOW DATABASES;

4个自带数据库：

mysql

MySQL自带的核心数据库，存储MySQL的用户账户与权限信息，存储过程，事件的定义信息，运行过程中产生的日志信息，帮助信息，时区信息等
information——schema

存储MySQL服务器维护的所有其他数据库的信息，如表，视图，触发器，列，索引等。这些并不是真实的用户数据，而是一些描述性信息，也称之为元数据。该数据库下innodb_sys开头的表为内部系统表
performance_schema

存储MySQL服务器运行过程中的状态信息，可以监控MySQL服务的各项性能指标。包括最近执行了哪些语句，执行过程的各个阶段耗时，内存使用情况等
sys

该数据库主要通过视图的形式把information_schema与performance_schema结合起来，帮助监控MySQL的技术性能

1.3 表与文件系统

1.3.1 InnoDB存储引擎

1.系统表空间（system tablespace）

默认情况下InnoDB会在数据目录下创建一个名为ibdata1，大小为12M的文件，该文件对应系统表空间在文件系统上的表示，它是自扩展文件，不够用时会自己增加文件大小。

2.独立表空间（file-per-table tablespace）

MySQL5.6.6之后，InnoDB不会默认把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立空间。使用表独立空间存储表数据会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名与表名相同，扩展名为**.ibd**

1 2	test.frm #存储表结构 test.ibd #存储数据和索引

3.系统表空间与独立表空间的设置

1 2	[server] innodb_file_per_table=0 #0:使用系统表空间； 1：使用独立表空间

4.其他类型表空间

通用表空间（general tablespace），临时表空间（temporary tablespace）等

1.3.2 MyISAM存储引擎

1.表结构

表结构与InnoDB一样，数据目录下对应的数据库子目录下创建一个专门描述表结构的文件XX.frm

2.表中数据与索引

MyISAM的索引全部是二级索引，该存储引擎的数据与索引分开存放。

1
2
3

test.frm	#存储表结构
test.MYD	#存储数据（MYData）
test.MYI	#存储索引（MYIndex）

3.显式指定引擎

1
2
3

CREATE TABLE 'sid'(
······
)ENGINE=MYISAM AUTO_INCREMENT=0 DEFAULT CHARSET = utf8mb4

2.用户与权限管理

2.1 用户管理

1.直接登录MySQL服务器

1	mysql -h hostname\|hostIP -P port -u username -p DatabaseName -e "SQL语句"

1	mysql -uroot -p -hlocalhost -P3306 mysql -e "SELECT host, user FROM user"

2.1.1 用户的创删改

1. 创建用户

1	CREATE USER 用户名 [IDENTIFIED BY '密码'][, 用户名 [IDENTIFIED BY '密码']];

eg.

1	CREATE USER zhuo IDENTIFIED BY '123456';

1	CREATE USER 'zhuo'@'localhost' IDENTIFIED BY '123456';

2.修改用户

1 2	UPDATE mysql.user SET USER = 'zhuo1' WHERE USER = 'zhuo'; FLUSH PRIVILEGES;

3.删除用户

DROP删除（推荐）

1	DROP USER user[, user]...;

1	DROP USER zhuo; #默认删除host为%的用户

1	DROP USER 'zhuo'@'localhost'; #指定host删除

DELETE删除（不推荐，系统会有残留信息保留）

1	DELETE FROM mysql.user WHERE Host = 'hostname' AND User = 'username';

1 2	DELETE FROM mysql.user WHERE Host = 'hostname' AND User = 'zhuo'; FLUSH PRIVILEGEs;

2.1.2 用户密码修改

1.修改当前用户密码

ALTER

1	ALTER USER user() IDENTIFIED BY 'new_password';

SET(root用户)

1	SET PASSWORD = 'new_password';

2.修改其他用户密码

ALTER修改普通用户(普通用户)

1	ALTER USER user [IDENTIFIED BY 'new_password'][, user[IDENTIFIED BY 'new_pw']]...;

SET修改普通用户(root用户使用)

1	SET PASSWORD FOR 'username'@'hostname' = 'new_pd';

2.1.3 MySQL8密码过期策略

DBA可以手动设置账号密码过期，也可以建立自动密码过期策略
过期策略可以是全局的，也可以是单独的

1.直接使用SQL语句指定相关变量

1	SET PERSIST default_password_lifetime = 180;

2.配置文件my.cnf

1 2	[mysqld] default_password_lifetime=180 #全局策略，密码每隔180天过期

3.单独设置

#zhuo账号90天过期
CREATE USER 'zhuo'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'zhuo'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

#永不过期
CREATE USER 'zhuo'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER ...;

#使用全局策略
CREATE USER 'zhuo'@'localhost' PASSWORD EXPIRE DEFAULT;
ALTER USER ...

2.1.4 MySQL8密码重用策略

1.直接使用SQL语句指定

1 2	SET PERSIST password_history = 6; #不能最近使用过的6个密码 SET PERSIST password_reuse_interval = 365; #不能最近一年的密码

2.配置文件my.cnf

1
2
3

[mysqld]
password_history=6
password_reuse_interval=365

3.单独设置

#不能使用最近6个密码
CREATE USER 'zhuo'@'localhost' PASSWORD HISTORY 6;
ALTER USER ...;

#不能使用最近365天内的密码
CREATE USER 'zhuo'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER ...;

#同时不能
CREATE USER 'zhuo'@'localhost'
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER ...;

2.2 权限管理

1	mysql> show privileges;

CREATE/DROP权限：创建/删除数据库和表
SELECT/INSERT/UPDATE/DELETE权限：允许在一个数据库中现有的表上操作
INDEX权限：允许创建/删除索引
ALTER权限：可以ALTER TABLE来更改表结构或重命名
CREATE ROUTINE权限：可以更改或删除保存的程序(函数)
EXECUTE权限：可以执行保存的程序(函数)
GRANT权限：允许授权给其他用户，数据库，表，程序(函数)
FILE权限：可以使用LOAD DATA INFILE和SELECT…INTO OUTFILE等语句读写服务器上的文件

1.权限授予原则

只授予可满足需求的最小权限
创建用户时限制用户的登录主机，限制指定IP或内网IP段
为用户设置满足密码复杂度的密码
定期清理用户，回收权限

2.权限授予

全员授予的两种方式：

直接对用户授权
通过角色授权

1	GRANT 权限1, 权限2... ON 数据库名.表名 TO 用户名@地址 [IDENTIFIED BY '密码']；

如果没有该用户，则会直接创建一个新用户

对本地登录用户zhuo授予test数据库下所有表的增删查改权限

1	GRANT SELECT, INSERT, DELETE, UPDATE ON test.* TO zhuo@localhost;

对网络登录用户mai授予所有权限，密码为123(不包括GRANT权限)

1	GRANT ALL PRIVILEGES ON . TO mai@'%' IDENTIFIED BY '123';

3.查看权限

1
2
3

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

查看某用户的全局权限

1	SHOW GRANTS FOR 'user'@'主机地址';

4.收回权限

收回用户不必要的权限以保证系统的安全性。使用REVOKE收回权限之后，用户的记录将从db, host, tables_priv,columns_priv表中删除，但仍会在user表中保留(删除user表中记录用DROP USER)

1	REVOKE 权限1, 权限2... ON 数据库名.表名 FROM 用户名@用户地址;

收回mai的所有权限

1	REVOKE ALL PRIVILEGES ON . FROM mai@'%';

收回zhuo的对应权限

1	REVOKE SELECT, INSERT, DELETE, UPDATE ON mysql.* FROM zhuo@localhost;

2.3 权限表

2.3.1 user表

最重要的权限表，记录账号和权限信息

+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                    | Type                              | Null | Key | Default               | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                     | char(255)                         | NO   | PRI |                       |       |
| User                     | char(32)                          | NO   | PRI |                       |       |
| Select_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv                | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv                | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv      | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv   | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type                 | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher               | blob                              | NO   |     | NULL                  |       |
| x509_issuer              | blob                              | NO   |     | NULL                  |       |
| x509_subject             | blob                              | NO   |     | NULL                  |       |
| max_questions            | int unsigned                      | NO   |     | 0                     |       |
| max_updates              | int unsigned                      | NO   |     | 0                     |       |
| max_connections          | int unsigned                      | NO   |     | 0                     |       |
| max_user_connections     | int unsigned                      | NO   |     | 0                     |       |
| plugin                   | char(64)                          | NO   |     | caching_sha2_password |       |
| authentication_string    | text                              | YES  |     | NULL                  |       |
| password_expired         | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed    | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime        | smallint unsigned                 | YES  |     | NULL                  |       |
| account_locked           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_role_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_role_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Password_reuse_history   | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_reuse_time      | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_require_current | enum('N','Y')                     | YES  |     | NULL                  |       |
| User_attributes          | json                              | YES  |     | NULL                  |       |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows

1.范围列(用户列)

host: 表示连接类型
- % 所有远程通过TCP方式连接
- IP地址(192.168.1.2) 通过指定的ip地址进行TCP连接
- 机器名 通过指定网络中的机器名进行TCP方式连接
- ::1 IPv6的本机ip地址，等同于IPv4的127.0.0.1
- localhost 本地通过命令行方式连接，mysql -uXXX -pXXX
user: 表用户名，统一用户通过不同方式连接的权限是可以不同的
password: 密码
- 通过password（明文字符串）生成密文字符串。MySQL8加密方式由SHA1改为了SHA2, 不可逆。
- MySQL8密码保存到authentication_string字段中不再使用password字段

2.权限列

Grant_priv字段：

表示是否拥有GRANT权限
Shutdown_priv字段:

表示是否拥有停止MySQL服务的权限
Super_priv字段:

表示是否拥有超级权限
Execute_priv字段:

表示是否拥有EXECUTE权限(执行存储过程和函数)
Select_priv， Insert_priv等:

为该用户所拥有的权限

3.安全列

用于加密的ssl相关2个（ssl_type, ssl_cipher）
用于标识用户的x509相关2个（x509_issuer, x509_subject）
用于验证用户身份的非空Plugin字段两个（如果为空则服务器使用内建授权机制验证用户身份）

4.资源控制列

max_questions:

用户每小时允许执行的查询操作次数
max_updates:

用户每小时允许执行的更新次数
max_connections:

用户每小时允许执行的连接操作次数
max_user_connections:

用户允许同时建立的连接次数

查看用户，以列的方式显示数据

1	SELECT * FROM mysql.user /G;

2.3.2 db表

1.用户列

**host:**主机名
**User:**用户名
**Db:**数据库名

表示从某个主机连接某个用户对某个数据库的操作权限，这3个字段组成联合主键

2.权限列

Create_routine_priv:

用户是否具有创建存储过程的权限
Alter_routine_priv:

用户是否具有修改存储过程的权限

2.3.3 tables_priv/columns_priv表

1.tables_priv表

对表设置操作权限

2.columns_priv表

对某一列设置权限

2.3.4 procs_priv表

对存储过程和存储函数设置权限

2.4 访问控制

1.连接核实阶段

客户端向服务器发送用户名，密码，主机地址。服务器接收到请求后使用user表中的host, user, authentication_string字段来匹配客户端提供的信息

如果连接核实没有通过，则拒绝访问。如果通过则进入2.请求核实阶段

2.请求核实阶段

连接完成后，服务器对该连接上传来的每一个请求进行权限检查以判断是否执行。通过权限表检查。

首先检查user表，如果该请求的权限没有在user表中赋予，则继续检查db表，之后是tables_priv表，columns_priv表。如果所有权限表检查完毕都没找到允许的操作，则返回错误信息，拒绝该请求的操作

2.5 角色管理

2.5.1 角色创建

1	CREATE ROLE 'role_name'[@'host_name']...

host_name省略则默认为%

1	CREATE ROLE 'manager'@'localhost';

2.5.2 角色权限

1	GRANT privileges ON table_name TO 'role_name'[@'host_name'];

查看权限

1	SHOW PRIVILEGES\G;

2.5.3 查看权限

1	SHOW GRANTS [FOR 'role_name'];

2.5.4 回收权限

1	REVOKE privileges ON 数据库名.表 FROM '角色名';

2.5.5 赋予角色

1	GRANT role[, role2...] TO user[, user2];

1	GRANT 'manager' TO 'zhuo'@'localhost';

查看是否添加成功

1	SHOW GRANT FOR 'zhuo'@'localhost';

查看当前激活角色

1	SELECT CURRENT_ROLE();

2.5.6 激活角色

为zhuo用户默认激活所有已拥有的角色

1	SET DEFAULT ROLE ALL TO 'zhuo'@'localhost';

对所有角色永久激活

1	SET GLOBAL activate_all_roles_on_login=ON;

2.5.7 撤销角色

1	REVOKE 角色 FROM 用户;

1 2	REVOKE 'manager' FROM 'zhuo'@'localhost'; SHOW GRANTS FOR 'zhuo'@'localhost';

2.5.8 强制角色

服务启动前设置

1 2	[mysqld] mandatory_roles='role1,role2@localhost...'

服务运行时设置

#系统重启后仍然有效
SET PERSIST mandatory_roles = 'role1,role2@localhost...'
#系统重启后失效
SET GLOBAL mandatory_roles = 'role1,role2@localhost...'

3.逻辑架构

3.1 基本处理流程

3.1.1 连接层

客户端访问MySQL服务器前，先建立TCP连接，三次握手建立连接后，MySQL服务器对TCP传输过来的账号密码做身份认证，权限获取

用户密码错误，Access denied for user错误，客户端程序结束执行
用户密码正确，从权限表查该账号的权限与连接关联，之后的权限判断都将依赖于此时读到的权限
TCP连接收到请求后，需要专门分配一个线程专门与该客户端交互。因此存在一个线程池，其他线程去走后续流程。从线程池中获取线程，节省创建与销毁线程的开销

3.1.2 服务层

SQL Interface：SQL接口
- 接收用户的SQL命令，返回用户需要的查询结果（例：SELECT..FROM..）
- MySQL支持DML（数据操作语言），DDL（数据定义语言），存储过程，视图，触发器，自定义函数等多种SQL语言接口
Parser：解析器

倬倬吃三碗

MySQL笔记（下）