mysql用户权限与缓存

mysql 用户权限与缓存

mysql用户和权限管理：

权限类别：库级别，表级别，字段级别，管理类，程序类    
管理类：
  > CREATE TEMPORARY TABLES    临时表
  > CREATE USER
  > FILE
  > SUPER
  > SHOW DATABASES
  > RELOAD   重新装载授权表
  > SHUTDOWN      进程级别关闭数据库权限
  > REPLICATION SLAVE     复制权限
  > REPLICATION CLIENT    请求复制相关的主数据库相关数据
  > LOCK TABLES           表锁操作
  > PROCESS               线程(show process list)

程序类：
  > FUNCTION     函数
  > PROCEDURE    存储过程
  > TRIGGER      触发器
  支持 create,alter,drop,exute 四类操作，增，改，删，执行

库和表级别： table or database
   > ALTER
   > CREATE
   > CREATE VIEW    视图
   > DROP
   > INDEX          索引
   > SHOW VIEW
   > GRANT OPTION     获取的权限，可否转授权与其他用户(不建议使用  with grant option)

数据操作：
   > SELECT
   > INSERT
   > DELETE
   > UPDATE

字段级别：
   > SELECT (col1,col2,...)
   > UPDATE (col1,col2,...)
   > INSERT (col1,col2,...)

所有权限：ALL PRIVILEGES , ALL

元数据数据库：mysql

Grant System Tables (授权系统表)：
  > columns_priv 
  > tables_priv 
  > procs_priv
  > proxies_priv
  > db
  > user
Object Information System Tables (对象信息系统表)：
  > event   事件调度器的相关事件信息
  > func    用户自定义函数的相关信息
  > plugin  服务器的组件信息
  > proc    存储过程与函数的相关信息
Log System Tables (日志系统表 CSV storage engine):
  > general_log   一般查询日志表 
  > slow_log      慢查询日志表
Server-Side Help System Tables (服务器帮助表 InnoDB storage engine):
  > help_category   帮助类别相关信息
  > help_keyword    关键字想关帮助主题
  > help_relation   关键字与主题之间映射帮助信息
  > help_topic      主题帮助内容
Time Zone System Tables (时区相关系统表 InnoDB storage engine):
  > time_zone     时区ID，无论是否使用润秒
  > time_zone_leap_second    存在闰秒
  > time_zone_name    时区id与名字的映射
  > time_zone_transition     时区表述信息
  > time_zone_transition_type   时区表述信息
Replication System Tables (复制系统表，mysql版本不一样，此处数据表很大不一样，InnoDB storage engines):
  > gtid_executed         存储GTID值
  > ndb_binlog_index      集群复制二进制日志信息
  > slave_master_info     slave上存储复制信息
  > slave_relay_log_info  slave上存储复制信息
  > slave_worker_info     slave上存储复制信息
Optimizer System Tables (优化系统表)：
  > innodb_index_stats    用户innodb持续优化器统计数据
  > innodb_table_stats    用户innodb持续优化器统计数据
  > server_cost     服务器开销
  > engine_cost     引擎开销
Miscellanenous System Tables (其他类系统表):
  > audit_log_filter    审计过滤日志，mysql 企业版审计安装
  > audit_log_user      审计用户日志，mysql 企业版审计安装
  > firewall_users      提供持久存储信息给防火墙使用，mysql 企业版防火墙安装
  > firewall_whitelist  提供持久存储信息给防火墙使用，mysql 企业版防火墙安装
  > servers

# mysql官网：https://dev.mysql.com/doc/refman/5.7/en/system-database.html

用户帐号： 'USERNAME'@'HOST'

@'HOST' : 主机名，IP地址或网络地址，通配符(%,_ )
    > admin@eap.test.com
    > admin@192.168.1.1
    > admin@192.168.1.0
    > admin@192.168.1.11_
    > admin@192.168.%.%

创建用户：CREATE USER
    > CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'PASSWORD']
查看用户授权：SHOW GRANTS FOR
    > SHOW GRANTS FOR 'USERNAME'@'HOST'
重命名用户：RENAME USER
    > RENAME USER OLD_USERNAME TO NEW_USERNAME
删除用户：DROP USER 'USERNAME'@'HOST'
关闭名称解析功能：
skip_name_resolve = on;
修改密码：
    > SET PASSWORD FOR
    > UPDATE mysql.user SET password=PASSWORD('PASSWORD') WHERE USER='USERNAME';
    > mysqladmin password

忘记管理员密码的解决办法：

1> 启动mysqld进程时，为其使用：--skip-grant-tables --skip-network
2> 使用update命令修改管理员密码
3> 关闭mysqld进程，移除上述两个选项，重启mysqld

GRANT语法格式：

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]

GRANT PROXY ON user_specification
    TO user_specification [, user_specification] ...
    [WITH GRANT OPTION]

object_type:
    TABLE                表
  | FUNCTION             存储函数
  | PROCEDURE            存储过程

priv_level:
    *                    所有库
  | *.*                  所有库的表
  | db_name.*            某库所有表
  | db_name.tbl_name     某库某表
  | tbl_name             某表
  | db_name.routine_name (存储过程或存储函数)

user_specification:
    user
    [
        IDENTIFIED BY [PASSWORD] 'password'    指定密码
      | IDENTIFIED WITH auth_plugin [AS 'auth_string']     认证插件，外部认证程序
    ]

ssl_option:
    SSL
  | X509                X509格式
  | CIPHER 'cipher'     加密方式
  | ISSUER 'issuer'     限制ssl证书颁发的CA
  | SUBJECT 'subject'   限制ssl证书持有者名字

with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count       每小时查询次数
  | MAX_UPDATES_PER_HOUR count       每小时更新次数
  | MAX_CONNECTIONS_PER_HOUR count   每小时连接次数
  | MAX_USER_CONNECTIONS count       每用户同时连接次数


# GRANT USAGE ON PROCEDURE mydb.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
# GRANT ALL ON mydb.* TO 'admin'@'localhost' identified by '123456' with MAX_USER_CONNECTIONS 3 MAX_QUERIES_PER_HOUR 5;

查询缓存(瓶颈)：

缓存对于终端用户是属于透明的。
现在物理机器核心数越来越多，查询缓存是属于所有mysql进程共享的。
当超多物理核心上的mysql进程往缓存中发起查询，缓存成为整个系统mysql的竞争热点，单点。
有可能，这类缓存成了系统瓶颈，从而撑死mysql，并不是将查询缓存设置越大越好。

判断缓存命中：

通过查询语句的哈希值判断，查询本身，查询数据库，客户端使用协议版本。(查询语句的大小写字符，空格数不同都会导致查询语句的HASH值不通)

不能缓存的：

查询的结果是不确定的,查询中，包含任何用户自定义函数，存储过程，用户自定义变量，临时表,mysql库中系统表，或者包含列级别权限的表，有着不确定值的函数(Now())

设置缓存：

> show global variables like "%query%";
| query_cache_limit         1048576     (1M   大于1M的数据集将不会被缓存,在有着较大结果的查询语句，建议在select中使用SQL_NO_CACHE)
| query_cache_min_res_unit  4096        (4k   内存缓存分页大小,内存块的最小分配单位)
     较小值，会导致频繁的内存分配开销，较大值，会导致内存碎片过多，浪费    需要根据具体查询结果集
| query_cache_size          16777216    (16M 缓存大小)
| query_cache_type       {on|off|demand}  demand 只有在查询语句中限制指定缓存SQL_CACHE，结果才会被缓存
| query_cache_wlock_invalidate   {on | off}    要查询的表被锁，是否仍然可以从查询缓存中返回结果，默认为off，可以返回

查询缓存命中率：

> show global status like 'Qcache%';
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16758112 |
| Qcache_hits             | 0        |   缓存命中次数
| Qcache_inserts          | 1        |   可缓存语句的结果被放入缓存的次数
| Qcache_lowmem_prunes    | 0        |   查询缓存太小，导致缓存被清理的次数，此数值过大就需要调整缓存变大
| Qcache_not_cached       | 45073    |   没有被缓存的
| Qcache_queries_in_cache | 1        |   被缓存下的查询语句个数
| Qcache_total_blocks     | 4        |   空闲下来未被使用缓存空间块数(16=4+4+2+6   4块)

缓存命中率的评估：Qcache_hits/(Qcache_hits+Com_select)

考虑关闭mysql缓存，还可以借住memcached,redis等外部缓存工具，提高mysql查询缓存性能。