权限类别:

  • 库级别
  • 表级别
  • 字段级别
  • 管理类
  • 程序类

 

管理类:

  • CREATE TEMPORARY TABLES
  • CREATE USER
  • FILE
  • SUPER
  • SHOW DATABASES
  • RELOAD
  • SHUTDOWN
  • REPLICATION SLAVE
  • REPLICATION
  • LOCK TABLES
  • PROCESS

程序类

CREATE 、alter 、DROP、EXCUTE

  • FUNCTION
  • PROCEDURE
  • TRIGGER

库和表级别:

  • ALTER
  • CREATE
  • CREATE VIEW
  • DROP
  • INDEX
  • SHOW VIEW
  • GRANT OPTION: 能够把自己获得的权限赠其他用户一个副本

数据操作:

  • SELECT
  • INSERT
  • DELETE
  • UPDATE

 

所有:ALL PRIVILEGE 

 

三、元数据数据库:USE mysql;

  show tables;

  授权表: 

用户账号和密码等信息是保存在安装完mysql服务器,并启动起来以后一个mysql数据库中多个表来实现的
 	mysql权限表主要涉及到mysql数据库中的user、db、host、tables_priv、columns_priv、procs_priv。
从MySQL5.6开始,host表已经没有了。MariaDB中虽然有host表,但却不用。
mysqld进程启动时候,读取这6个表,并在内存中生成授权表;以后的后续用户的登录及其访问权限的检查,都通过这个6张表来实现,
  检查的过程不在是通过访问磁盘中的表,而是访问内存中所生成的结构信息来完成。
注:任何一个SQL语句的执行,都有可能查询授权表,因为任何一个SQL语句执行,都需要检查用户是否有访问对应权限的资源,
因此为了加速这个过程,MySQL将所有的授权表读进内存进行管理

注:MySQL服务器通过MySQL权限表来控制用户对数据库的访问,MySQL权限表存放在mysql数据库里,由bin目录下mysql_install_db脚本初始化。

 

四、用户账号及权限管理:

1.1、user表
Contains user accounts,global  privileges , and other non-privilege  columns
包含:用户账号、全局权限
作用:记录允许连接到服务器的用户帐号信息,里面的权限是全局级的。

1.2、db表 :Contains  database-level  privileges 
包含:库级别权限
作用:记录各个帐号在各个数据库上的操作权限。

1.3、host表:Obsolete -----------已废弃 整合进user表中去了
作用:配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

1.4、tables_priv:Contains  table-level  privileges 
表级别权限
作用:记录数据表级的操作权限。

1.5、columns_priv:Contains  table-level  privileges 
列级别权限
作用:记录数据列级的操作权限。

1.6、procs_priv:contaions stored  procedure  and  function  privileges
存储过程和存储函数相关的权限

1.7、proxies_priv:contains  proxy-user  privileges
代理用户权限(授权将mysql的访问,给某个代理服务来验证)

 

用户账号:'username'@'host'

    user :用户名

    host : 此用户访问mysqld服务时允许通过哪些主机远程创建连接

       IP 、网络地址、主机名、

       通配符(%和_):

          _: 匹配任意单个字符, 172.16.0.__ 匹配10~99

          % :匹配任意字符

 

mysqld服务器端在验证客服端身份时,它会尝试在每一次客户端创建连接时反解客户端的IP地址为主机名,并根据主机名来检查权限

有时候明明做了授权却没法访问,可能是你根据主机名做了授权,但本机服务器却没法反解对方IP地址到主机名
因此建议 :禁止检查主机名

  /etc/my.cnf  
  [mysqld]
 skip_name_resolve=yes  跳过主机名解析

 

4.1、创建用户账号:

帮助:HLEP CREATE USER; 

语法:

创建用户指定密码(该用户只能连到mysql上,仅能查看,无其它权限):
CREATE USER [IF NOT EXISTS] 'username'@'host' IDENTIFIED BY 'password'

e.g

查看有哪些用户:

  mysql> select user,host,authentication_string from mysql.user;

 

怎么查看MySQL的root 查看mysql的所有用户和权限_缓存

4.2、删除用户:

帮助:HELP DROP USER;

语法:

DROP USER [IF NOT EXISTS] 'username1'@'host','username2'@'host',...;

用户重命名:RENAME USER 

RENAME USER old_user_name TO new_user_name

 4.3、mysql5.7修改密码三种方法

  •   SET PASSWORD FOR
  •   UPDATE mysql.usr SET password=PASSWORD('your_password')  WHERE clause;
  •   mysqladmin password

注:mysql5.7中 user 表的 password 被 authentication_string 字段所取代

法一:
mysql> alter user 'root'@'localhost' identified by 'cy7m0ypu8CpLFperzI45';
法二:
mysql> set password for 'root'@'localhost'=password('cy7m0ypu8CpLFperzI45');
法三:
mysql> update mysql.user set authentication_string=password('cy7m0ypu8CpLFperzI45') where user='root' and Host = 'localhost';
--此方法最后必须要刷新权限
mysql> flush privileges;

 

4.4、查看授权

   权限级别:管理权限、数据库、表、字段、存储例程

GRANT

帮助: 

  mysql> HELP GRANT;

  mysql> HELP SHOW GRANTS;

查看指定用户获得的权限

SHOW  GRANTS FOR  'user'@'host';

查看当前用户所拥有的的权限

SHOW GRANTS FOR CURRENT_USER;

 

基本语法:

GRANT priv_type,... ON  [object_type] db_name.tbl_name  TO 'user'@'host' [IDENTIFIED BY 'password']

注:object_type与db_name.*  类型要一致

priv_type :ALL [PRIVILEGES]、column_list

object_type: {
  TABLE            # 默认
  | FUNCTION     #存储函数上的权限
  | PROCEDURE    #存储过程上的权限

db_name.tbl_name :
  *.*              :所有库的所有表
  db_name.*           : 指定库的所有表
  db_name.tbl_name      : 指定库的指定表
  db_name.routine_name   :指定库的存储例程

 

REVOKE

帮助信息:

   mysql> HELP REVOKE;

基本语法:  

REVOKE priv_type,... ON db_name.tb_name FROM 'user'@'host';

E.G

mysql> GRANT SELECT,DELETE ON testdb.* TO 'test'@'%' IDENTIFIED BY 'test';

mysql> SHOW GRANTS FOR CURRENT_USER;

怎么查看MySQL的root 查看mysql的所有用户和权限_怎么查看MySQL的root_02

 

mysql> REVOKE DELETE ON testdb.* FROM 'test'@'%';

怎么查看MySQL的root 查看mysql的所有用户和权限_mysql_03

 

注:MariaDB服务器进程启动时会读取mysql库中的所有授权表至内存中

  (1)使用GRANT或REVOKE等执行权限操作会保存在表中,MariaDB的服务进程会自动重读授权表;

  (2)对于不能够或不能及时重读授权表的命令,可手动让MariaDB的服务进程重读授权表

      mysql>FLUSH PRIVILEGES;

 

练习题:

  http://www.voidcn.com/article/p-yhqfwlpn-boa.html

 授权test用户通过任意主机连接当前mysqld,但每秒钟最大查询次数不得超过5次;此账户的同时连接次数不得超过3次。

 

忘记管理员密码的解决方法:

http://note.youdao.com/noteshare?id=10023dbc5417120b2e0a75a11eec7ab3&sub=39C5C092FB374788AD42CE82321B2C3B

 



 

查询缓存:

  如何判断是否命中:

  • 通过查询语句的哈希值判断:哈希值考虑的因素包括
  • 查询本身、要查询的数据库、客户端使用协议版本,。。。。

      注:  查询语句任何字符上的不同,都会导致缓存不能命中

 

  哪些查询可能不会被缓存

查询中包含UDF、存储函数、用户自定义变量、临时表、mysql库中系统表、或者包含列级权限的表、有着不确定值的函数now()

内存创建回收,势必会造成内存碎片,必须高效的内存的分配回收算法,尽可能降低内存碎片

 

mysql> SHOW GLOBAL VARIABLES LIKE "%QUERY%"

查询缓存相关的服务器变量:

   query_cache_min_res_unit     查询缓存中内存块最小分配单位

  • 较小值会减少浪费,但会导致更频繁的内存分配操作
  • 较大值会带来浪费,会导致碎片过多

query_cache_limit 

      对于有着较大结果的查询语句,建议在SELECT中使用SQL_NO_CACHE

query_cache_size

query_cache_type

 query_cache_wlock_invalidate 

         默认值为OFF,表示可以在表被其它连接锁定的场景中继续从缓存返回数据;ON则表示不允许

1)当查询进行的时候,Mysql把查询结果保存在qurey cache中,但如果要保存的结果比较大,超过query_cache_min_res_unit的值 ,这时候mysql将一边检索结果,一边进行保存结果,所以,有时候并不是把所有结果全部得到后再进行一次性保存,而是每次分配一块 query_cache_min_res_unit 大小的内存空间保存结果集,使用完后,接着再分配一个这样的块,如果还不不够,接着再分配一个块,依此类推,也就是说,有可能在一次查询中,mysql要 进行多次内存分配的操作。
2)内存碎片的产生。当一块分配的内存没有完全使用时,MySQL会把这块内存Trim掉,把没有使用的那部分归还以重 复利用。比如,第一次分配4KB,只用了3KB,剩1KB,第二次连续操作,分配4KB,用了2KB,剩2KB,这两次连续操作共剩下的 1KB+2KB=3KB,不足以做个一个内存单元分配, 这时候,内存碎片便产生了。
3)使用flush query cache,可以消除碎片

4)如果Qcache_free_blocks值过大,可能是query_cache_min_res_unit值过大,应该调小些
5)query_cache_min_res_unit的估计值:(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

 

如何判断查询缓存是否高效,以及如何提交查询缓存命中率呢?

  

怎么查看MySQL的root 查看mysql的所有用户和权限_怎么查看MySQL的root_04

 

 

查询相关的状态变量:

mysql> SHOW status LIKE "%Qcache%";
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |              #空闲的内存块(分隔以后能够被缓存 查询结果的内存块)

| Qcache_free_memory | 1031832 |     #仍然空闲的内存空间
| Qcache_hits | 0 |            #
| Qcache_inserts | 0 |            #可缓存查询语句的结果被放入缓存中的次数
| Qcache_lowmem_prunes | 0 |       # 有多少次是因为我们查询缓存空间内存太少,而被不得不利用lLRU算法去清理缓存空间
| Qcache_not_cached | 166 |        #可缓存却没能被缓存的结果
| Qcache_queries_in_cache | 0 |        #当前的缓存空间中,被缓存下来的查询的个数
| Qcache_total_blocks | 1 |            #整个查询缓存一共有多少个内存块(整个内存区段)
+-------------------------+---------+
8 rows in set (0.01 sec)

缓存命中的评估:Qcache_hits /(Qcache_hits +Com_selelct)  仅仅记录mysql执行的查询语句,从缓存中命中的话,值不会增加。

 

# 如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况;

如果Qcache_hits的值非常大,则表明查询缓冲使用非常频繁,如果该值较小反而会影响效率,那么可以考虑不用查询缓冲;

Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多。

 “Qcache_free_blocks”:Query Cache 中目前还有多少剩余的blocks。如果该值显示较大,则说明Query Cache 中的内存碎片较多了,可能需要寻找合适的机会进行整理。
● “Qcache_free_memory”:Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够,是需要增加还是过多了;
● “Qcache_hits”:多少次命中。通过这个参数我们可以查看到Query Cache 的基本效果;
● “Qcache_inserts”:多少次未命中然后插入。通过“Qcache_hits”和“Qcache_inserts”两个参数我们就可以算出Query Cache 的命中率了:
Query Cache 命中率= Qcache_hits / ( Qcache_hits + Qcache_inserts );
● “Qcache_lowmem_prunes”:多少条Query 因为内存不足而被清除出Query Cache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合,能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够,是否非常频繁的出现因为内存不足而有Query 被换出
● “Qcache_not_cached”:因为query_cache_type 的设置或者不能被cache 的Query 的数量;
● “Qcache_queries_in_cache”:当前Query Cache 中cache 的Query 数量;

 

二、监控缓存命中率


通过Nagios+pnp4nagios来监控缓存命中率,并通过图表来展示。


1、监控脚本: check_mysql_qch.sh.sh



#!/bin/bash

#function:查询缓存命中率

#time:20121130

#author:system group

while getopts ":w:c:h" optname

do

case "$optname" in

"w")

WARN=$OPTARG

;;

"c")

CIRT=$OPTARG

;;

"h")

echo "Useage: check_mysql_qch.sh -w warn -c cirt"

exit

;;

"?")

echo "Unknown option $OPTARG"

exit

;;

":")

echo "No argument value for option $OPTARG"

exit

;;

*)

# Should not occur

echo "Unknown error while processing options"

exit

;;

esac

done

[ $? -ne 0 ] && echo "error: Unknown option " && exit

[ -z $WARN ] && WARN=60

[ -z $CIRT ] && CIRT=50

export selete=`/usr/local/mysql/bin/mysql -h 127.0.0.1 -uroot -Bse "SHOW GLOBAL STATUS LIKE 'Com_select';" |awk '{print $2}'`

export hits=`/usr/local/mysql/bin/mysql -h 127.0.0.1 -uroot -Bse "SHOW GLOBAL STATUS LIKE 'Qcache_hits';" |awk '{print $2}'`

a=$(($selete+$hits))

#rw_ratio=$(($a/$b))

#echo "rw_ratio=$rw_ratio"

#ratio=$(($rw_ratio*100))

#echo "ratio=$ratio"

if [ $a -ne "0" ];then

percent=`awk 'BEGIN{printf "%.2f%\n",('$hits'/'$a')*100}'`

Qch=`awk 'BEGIN{printf ('$hits'/'$a')*100}'`

fi

C=`echo "$Qch < $CIRT" | bc`

W=`echo "$Qch < $WARN" | bc`

O=`echo "$Qch > $WARN" | bc`

if [ $C == 1 ];then

echo -e "CIRT - Mysql Qcache Hits is $percent,Com_select is $selete,Qcache_hits is $hits|Qcache_hits=${Qch}%;${selete};${hits};0"

exit 2

fi

if [ $W == 1 ];then

echo -e "WARN - Mysql Qcache Hits is $percent,Com_select is $selete,Qcache_hits is $hits|Qcache_hits=${Qch}%;${selete};${hits};0"

exit 1

fi

if [ $O == 1 ];then

echo -e "OK - Mysql Qcache Hits is $percent,Com_select is $selete,Qcache_hits is $hits|Qcache_hits=${Qch}%;${selete};${hits};0"

exit 0

fi


2、生成报表

Pnp4nagios templates:check_mysql_qch.php 



<?php

#

# Copyright (c) 2006-2010 system (http://www.cnfol.com)

# Plugin: check_mysql_qch

#

$opt[1] = "--vertical-label hits/s -l0 --title \"Mysql Qcache Hits for $hostname / $servicedesc\" ";

#

#

#

$def[1] = rrd::def("var1", $RRDFILE[1], $DS[1], "AVERAGE");

if ($WARN[1] != "") {

    $def[1] .= "HRULE:$WARN[1]#FFFF00 ";

}

if ($CRIT[1] != "") {

    $def[1] .= "HRULE:$CRIT[1]#FF0000 ";

}

$def[1] .= rrd::area("var1", "#0000FF", "Mysql Qcache Hits percent") ;

$def[1] .= rrd::gprint("var1", array("LAST", "AVERAGE", "MAX"), "%6.2lf");

?>

 

人的思维,观点是一直都会进化的。如果现在的思维,观点看法;和十年前的一模一样的话。那就可以说,你这十年是白活的。