一、MySQL体系结构
1.1、mysql C/S模型
主要有两种连接mysql的方式:1)TCP/IP
2)Socket
1.2、mysql实例概念
分布式系统架构中会使用多实例:在一个系统上要起多个mysql。
mysqld守护进程+Master thread + worker thread +专用内存 ====>实例
实例+数据 ====>完整的数据库管理系统
多实例==完整的数据库管理系统*n
1.3、mysql工作过程
一条SQL语句的执行过程:select user,authentication_string,host from mysql.user;
1.3.1、连接层工作原理
1.3.2、SQL层工作原理
1.4、mysql逻辑结构
1.5、mysql物理结构
1)对于Linux操作系统来讲,他的物理层是同柱面的扇区.
2)对于MySQL来讲,他是Linux系统之上的应用软件,他的物理层就是文件系统上的特定格式的文件,由MySQL决定的
对于myisam引擎来说:user.frm :表的列结构
user.MYD :数据行记录
user.MYI :表的索引
对于innodb引擎来说:(注意:mysql8.0中已经没有.frm文件了)t1.frm :表列的结构
t1.ibd :数据行记录和索引
二、用户权限管理
2.1、mysql用户
2.1.1、用户作用
1)登录MySQL
2)管理MySQL对象(逻辑结构:库(库名字,库属性)、表(表名,表的数据行,表的列(列名字及列属性),表的属性))
2.1.2、用户定义
abc@'白名单' 密码123
白名单: 允许白名单中的IP,可以通过abc用户及123密码登录和管理MySQL
白名单支持定义的方式:
2.2、用户管理命令
2.2.1、创建用户
2.2.2、删除用户
2.2.3、修改用户密码
2.3、权限管理
2.3.1、授权命令
注意:all权限内容
2.3.2、权限作用对象
2.4、生产授权
2.5、查看用户具备的权限
2.6、回收权限
三、MySQL基础管理
3.1、mysql连接管理
1)mysql客户端命令
2)mysql客户端程序sqlyog
navicat
workbench(EM)
3)基于API的连接
3.2、mysql启动流程
3.3、报错日志查看
3.4、初始化配置
1)初始化配置方式
(1) CMAKE 时可以实现初始化配置
(2) 初始化配置文件/etc/my.cnf *****用的最多的方法
(3) 命令行可以直接指定
2)优先级
命令行 > 配置文件 > 编译安装的
3)读取/etc/my.cnf顺序
3.5、配置文件书写
四、MySQL权限补充
4.1、权限级别
1)mysql权限级别全局性的管理权限,作用于整个MySQL实例级别
数据库级别的权限,作用于某个指定的数据库上或者所有的数据库上
数据库对象级别的权限,作用于指定的数据库对象上(表、视图等)或者所有的数据库对象上
2)权限存储在mysql库的user, db, tables_priv, columns_priv, and procs_priv这几个系统表中,待MySQL实例启动后就加载到内存中
4.2、特殊用户权限
补充:
4.3、权限详解
4.4、系统权限表
权限存储在mysql库的user,db, tables_priv, columns_priv, and procs_priv这几个系统表中,待MySQL实例启动后就加载到内存中User表:存放用户账户信息以及全局级别(所有数据库)权限,决定了来自哪些主机的哪些用户可以访问数据库实例,如果有全局权限则意味着对所有数据库都有此权限
Db表:存放数据库级别的权限,决定了来自哪些主机的哪些用户可以访问此数据库
Tables_priv表:存放表级别的权限,决定了来自哪些主机的哪些用户可以访问数据库的这个表
Columns_priv表:存放列级别的权限,决定了来自哪些主机的哪些用户可以访问数据库表的这个字段
Procs_priv表:存放存储过程和函数级别的权限
4.4.1、user权限表
User权限表结构中的特殊字段:
1)Plugin,password,authentication_string三个字段存放用户认证信息
2)Password_expired设置成’Y’则表明允许DBA将此用户的密码设置成过期而且过期后要求用户的使用者重置密码(alter user/set password重置密码)
3)Password_last_changed作为一个时间戳字段代表密码上次修改时间,执行create user/alter user/set password/grant等命令创建用户或修改用户密码时此数值自动更新
4)Password_lifetime代表从password_last_changed时间开始此密码过期的天数
5)Account_locked代表此用户被锁住,无法使用
4.4.2、Tables_priv和columns_priv权限表结构
Timestamp和grantor两个字段暂时没用
4.4.3、procs_priv权限表结构
Routine_type是枚举类型,代表是存储过程还是函数
Timestamp和grantor两个字段暂时没用
4.4.4、系统权限表字段长度限制表
4.5、权限认证中的大小写敏感问题
1)字段user,password,authencation_string,db,table_name大小写敏感
2)字段host,column_name,routine_name大小写不敏感
4.6、查看用户权限信息
4.7、MySQL修改权限的生效
1)执行Grant,revoke,set password,rename user命令修改权限之后,MySQL会自动将修改后的权限信息同步加载到系统内存中
2)如果执行insert/update/delete操作上述的系统权限表之后,则必须再执行刷新权限命令才能同步到系统内存中,刷新权限命令包括:flush privileges/mysqladmin flush-privileges/mysqladmin reload
3)如果是修改tables和columns级别的权限,则客户端的下次操作新权限就会生效
4)如果是修改database级别的权限,则新权限在客户端执行use database命令后生效
5)如果是修改global级别的权限,则需要重新创建连接新权限才能生效
6)--skip-grant-tables可以跳过所有系统权限表而允许所有用户登录,只在特殊情况下暂时使用
4.8、创建用户,赋予相应权限
五、mysql用户资源限制
5.1、资源限制介绍
1)通过设置全局变量max_user_connections可以限制所有用户在同一时间连接MySQL实例的数量,但此参数无法对每个用户区别对待,所以MySQL提供了对每个用户的资源限制管理
2)MAX_QUERIES_PER_HOUR:一个用户在一个小时内可以执行查询的次数(基本包含所有语句)
3)MAX_UPDATES_PER_HOUR:一个用户在一个小时内可以执行修改的次数(仅包含修改数据库或表的语句)
4)MAX_CONNECTIONS_PER_HOUR:一个用户在一个小时内可以连接MySQL的时间
5)MAX_USER_CONNECTIONS:一个用户可以在同一时间连接MySQL实例的数量
6)从5.0.3版本开始,对用户‘user’@‘%.example.com’的资源限制是指所有通过example.com域名主机连接user用户的连接,而不是分别指从host1.example.com和host2.example.com主机过来的连接
5.2、资源限制配置
1)通过执行create user/alter user设置/修改用户的资源限制
2)取消某项资源限制既是把原先的值修改成0
3)当针对某个用户的max_user_connections非0时,则忽略全局系统参数max_user_connections,反之则全局系统参数生效
5.3、设置mysql用户密码
5.4、设置mysql用户密码过期策略
5.5、mysql用户锁定
5.6、企业应用中的常规MySQL用户
5.7、企业应用中的MySQL用户密码设定
六、小问题
6.1、多个用户,那个用户连接?
当存在多个用户,同时匹配时,远程连接时会选择哪个用户进行连接??
七、mysql8.0新特性
7.1、密码认证新特性
1)在mysql8.0之前,用户的密码认证方式是mysql_native_password
2)mysql8.0采用的密码验证方式是caching_sha2_password
所以在使用一些客户端的工具时,会连接不上(sqlyog,navicat)
解决方法:更改创建用户的密码验证方式
7.2、角色的概念
相同功能的用户所属相同组,统一化管理