MySQL用户管理：


mysql>use mysql

mysql>desc user;             ---查看



用户帐号管理：

mysql>help contents;

mysql>help  Account Management

wKioL1PE4nDB2X9mAAFw_Mbnpkg206.jpg



权限管理：

  GRANT 

  REVOKE


创建用户：

1.CREATE USER

   CREATE USER username@hostname

   [

     IDENTIFIED BY [PASSWORD] 'password'

   ]

   主机也可使用通配符：

    %： 任意字符

    _:  一个字符


   testuser@'172.16.100.1__'              ---匹配 172.16.100.100-172.16.100.199

 2.grant all on *.*  to 'testuser'@'172.16.100.1__' identified by '123456';


 3.insert into mysql.user(Host,User,Password) values("172.16.100.1__","testuser",password("123456"));


删除用户：

 1. mysql>drop  user testuser@'172.16.100.1__';

 2. mysql>Delete FROM user Where User='testuser' and Host='172.16.100.1__';

    mysql>flush privilegs;

权限：


MySQL的权限类别：

  库级别

  表级别

  字段级别

  管理类

  程序类


管理类权限：

  CREATE TEMPORARY TABLES

  CREATE USER

  FILE

  SUPER

  SHOW DATABASES

  RELOAD

  SHUTDOWN

  REPLICATION SLAVE

  REPLICATION CLIENT

  LOCK TABLES

  PROCESS


库级别和表级别：

  ALTER

  ALTER ROUTINE

  CREATE 

  CREATE ROUTINE

  CREATE VIEW

  DROP

  EXECUTE

  GRNAT OPTION

  INDEX

  SHOW VIEW


数据操作（表级别）：

  SELECT

  INSERT

  UPDATE

  DELETE


字段级别：

  SELECT(col1,...)

  UPDATE(col1,...)

  INSERT(col1,...)


所有权限：

ALL [PRIVILEGES]



GRANT ALL ON [FUNCTION] *.* 


GRANT priv_type [(column_list)]

     [, priv_type [(column_list)]] ...

   ON [TABLE|FUNCTION|PROCEDURE] priv_level

   TO username@hostname [IDENTIFIED BY 'password'], [username@hostname [],...]

   [REQUIRE SSL]

   [WITH with_option ...]


priv_level:

   *

 | *.*

 | db_name.*

 | db_name.tbl_name

 | tbl_name

 | db_name.routine_name


with_option:

   GRANT OPTION                     --把自己权限转给别人  不安全

 | MAX_QUERIES_PER_HOUR count

 | MAX_UPDATES_PER_HOUR count

 | MAX_CONNECTIONS_PER_HOUR count

 | MAX_USER_CONNECTIONS count



用户重命名：

wKioL1PE7AiiMoSLAAC76sdC1Qc034.jpg

mysql>RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1';

mysql>rename user  'test1'@'172.10.%.%' to 'test2'@172.10.%.%';


   ---If you specify only the user name part of the account name, a host name

part of '%' is used


收回授权：

REVOKE

   priv_type [(column_list)]

     [, priv_type [(column_list)]] ...

   ON [object_type] priv_level

   FROM user [, user] ...


REVOKE ALL PRIVILEGES, GRANT OPTION

   FROM user [, user] ...


查看用户能够使用的权限：SHOW GRANTS FOR username@'hostname'

mysql> select  user,host from user;

mysql>show grants for  root@localhost;

wKiom1PE336C0DwuAAH-w8gklqw060.jpg

mysql>show grants for 'test1'@'172.16.%.%';

mysql>revoke  create on testdb.tb1 from 'test1'@'172.16.%.%';

mysql>revoke  all on testdb.tb1 from 'test1'@'172.16.%.%';


设置密码：

mysql>help SET PASSWORD

SET PASSWORD [FOR user] =

    {

        PASSWORD('cleartext password')

      | OLD_PASSWORD('cleartext password')

      | 'encrypted password'

    }

 1.mysql>set password for  'test1'@'172.10.%.%' = password ('newpasswd');

 2.mysql>grant all on *.* to 'test1'@'172.16.100.1__' IDENTIFIED BY '123456';

            ---授权的同时如果又设定的密码 那么原密码被覆盖

 3.mysql>update mysql.user set password=password('123456') where user='testuser' and host='172.16.100.1__'; 


mysql>show processlist;

mysql>use mysql 

mysql>show tables;


几个跟用户授权相关的表：

db: 库级别权限；

host: 主机级别权限，已废弃

tables_priv: 表级别权限

colomns_priv：列级别的权限

procs_priv：存储过程和存储函数相关的权限

proxies_priv：代理用户权限




MySQL查询缓存


用于保存MySQL查询语句返回的完整结果。被命中时，MySQL会立即返回结果，省去解析、优化和执行等阶段。


如何检查缓存？

MySQL保存结果于缓存中：

把SELECT语句本身做hash计算，计算的结果作为key，查询结果作为value


什么样的语句不会被缓存？


查询语句中有一些不确定数据时，不会缓存：例如NOW(), CURRENT_TIME()；一般来说，如果查询中包含用户自定义函数、存储函数、用户变量、临时表、mysql库中系统表、或者任何包含权限的表，一般都不会缓存；


缓存会带来额外开销：

  1.每个查询都得先检查是否命中；

  2.查询结果要先缓存；


MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'query_cache%';

  +------------------------------+----------+

| Variable_name                | Value    |

+------------------------------+----------+

| query_cache_limit            | 1048576  |

| query_cache_min_res_unit     | 4096     |

| query_cache_size             | 16777216 |

| query_cache_strip_comments   | OFF      |

| query_cache_type             | ON       |

| query_cache_wlock_invalidate | OFF      |

+------------------------------+----------+


query_cache_type: 查询缓存类型；是否开启缓存功能，开启方式有三种{ON|OFF|DEMAND}；

                  DEMAND：意味着SELECT语句明确使用 SQL_CACHE 选项时才会缓存；


query_cache_size: 总空间，单位为字节，大小必须是1024的整数倍。MySQL启动时，会一次分配并立即初始化这里指定大小的内存空间；这意味着，如果修改此大小，会清空缓存并重新初始化的。


query_cache_min_res_unit: 存储缓存的最小内存块；(query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache能够获得一个理想的值。


query_cache_limit: 单个缓存对象的最大值，超出时则不预缓存；手动使用SQL_NO_CACHE可以人为地避免尝试缓存返回结果超出此参数限定值的语句。


query_cache_wlock_invalidate: 如果某个表被其它用户连接锁住了，是否仍然从缓存中返回结果。OFF表示返回。



如何判断命令率：

MariaDB [hellodb]> SHOW GLOBAL STATUS LIKE 'Qcache%';

+-------------------------+----------+

| Variable_name           | Value    |

+-------------------------+----------+

| Qcache_free_blocks      | 1        |

| Qcache_free_memory      | 16757008 |

| Qcache_hits             | 4        |

| Qcache_inserts          | 2        |

| Qcache_lowmem_prunes    | 0        |

| Qcache_not_cached       | 18       |

| Qcache_queries_in_cache | 2        |

| Qcache_total_blocks     | 6        |

+-------------------------+----------+


碎片整理：FLUSH QUERY_CACHE

清空缓存：RESET QUERY_CACHE


计算命中率：

MariaDB [hellodb]> SHOW GLOBAL STATUS WHERE Variable_name='Qcache_hits' OR Variable_name='Com_select';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Com_select    | 24    |

| Qcache_hits   | 4     |

+---------------+-------+


Qcache_hits/(Com_select+Qcache_hits)


也应该参考另外一个指标：命中和写入的比率，即Qcache_hits/Qcache_inserts的值，此比值如果能大于3:1，则表明缓存也是有效的。能达到10:1，为比较理想的情况。


配置查询缓存：

  修改配置文件，修改[mysqld]下的query_cache_size和query_cache_type（如果没有则添加）

如：  

query_cache_size=10M  

query_cache_type=1  


缓存优化使用思路：

1、批量写入而非多次单个写入；

2、缓存空间不宜过大，因为大量缓存同时失效时会导致服务器假死；

3、必要时，使用SQL_CACHE和SQL_N0_CACHE手动控制缓存；

4、对写密集型的应用场景来说，禁用缓存反而能提高性能；