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

转载

架构魔法师 2024-06-25 15:55:40

文章标签 怎么查看MySQL的root mysql 缓存查询缓存 文章分类 MySQL 数据库

权限类别：

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

管理类：

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

查询相关的状态变量：

缓存命中的评估：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");

?>

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

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。