第3篇：MySQL管理员常用的一些命令

第3篇：MySQL管理员常用的一些命令

主要内容

1、介绍MySQL的权限工作原理
2、查看所有用户
3、创建用户
4、修改密码
5、给用户授权
6、查看用户权限
7、撤销用户权限
8、删除用户
9、授权原则说明
10、总结

MySQL权限工作原理

MySQL是如何识别一个用户的？

MySQL使用主机名+用户名来判断一个用户的身份。在互联网中，用户名是大概率会重复的，但是主机ip是一定的，每一台机器的mac地址是绝对唯一的，这样我们就可以通过ip或者主机名来判断一台机器。而某个用户肯定是通过一台机器链接数据库的，所以我们可以将这台机器识别为一个用户，就可以使用主机名+用户名来识别用户的身份。当一个用户对MySQL发送指令的时候，MySQL就是通过用户名和来源断定用户的权限。

MySQL权限验证过程

MySQL的权限验证分为两个阶段：
1、连接数据库，此时MySQL会根据你的用户名以及你的来源（ip或主机名称）判断是否有权限连接
2、对MySQL服务器发起请求操作，如CREATE table、SELECT、DELETE、update、CREATE INDEX等操作，此时MySQL会判断你是否有权限操作这些指令

注：MySQL为了方便数据库的管理以及数据库的安全，在安装MySQL之后里面有一个数据库叫mysql，这个数据库中名为users的table定义了该数据库中的所有权限信息以及登陆密码。
这个table一般只有数据库管理员才能访问

权限生效时间

⽤户及权限信息放在库名为mysql的库中，mysql启动时，这些内容被读进内存并且从此
时⽣效，所以如果通过直接操作这些表来修改⽤户及权限信息的，需要重启mysql或者执
⾏flush privileges;才可以⽣效。
⽤户登录之后，mysql会和当前⽤户之间创建⼀个连接，此时⽤户相关的权限信息都保存
在这个连接中，存放在内存中，此时如果有其他地⽅修改了当前⽤户的权限，这些变更的
权限会在下⼀次登录时才会⽣效。

创建用户

CREATE user 用户名@主机名 IDENTIFIED BY '密码';
主机名默认为%，表示这个用户可以从任何主机连接MySQL服务器
密码可以省略，表示无密码登陆
⽤户创建之后可以在mysql库中通过 select user,host from user;查看到。

其他⽰例
create user 'test2'@'localhost' identified by '123';
说明：test2的主机为localhost表⽰本机，此⽤户只能登陆本机的mysql
create user 'test3'@% identified by '123';
说明：test3可以从任何机器连接到mysql服务器
create user 'test4'@'192.168.11.%' identified by '123';
说明：test4可以从192.168.11段的机器连接mysql

修改密码

方式1：
通过管理员修改密码
SET PASSWORD FOR '用户名'@'主机ip'=PASSWORD('密码');
方式2：
CREATE user 用户名@'主机ip' IDENTIFIED BY '密码';
方式3：
通过修改mysql.user表修改密码
use mysql;
update user set authentication_string = password('321') where user =
'test1' and host = '%';
flush privileges;
注意：
通过表的⽅式修改之后，需要执⾏flush privileges;才能对⽤户⽣效。
5.7中user表中的authentication_string字段表⽰密码，⽼的⼀些版本中密码字段是
password。

给用户授权

创建用户之后，需要给用户授权，才有意义
GRANT privaleges ON database.table TO 'username'@'hostIP' WITH GRANT OPTION;

grant命令说明：
• priveleges (权限列表)，可以是all，表⽰所有权限，也可以是select、update等权
限，多个权限之间⽤逗号分开。
• ON ⽤来指定权限针对哪些库和表，格式为数据库.表名 ，点号前⾯⽤来指定数据库
名，点号后⾯⽤来指定表名，*.* 表⽰所有数据库所有表。
• TO 表⽰将权限赋予某个⽤户, 格式为username@host，@前⾯为⽤户名，@后⾯接限
制的主机，可以是IP、IP段、域名以及%，%表⽰任何地⽅。
• WITH GRANT OPTION 这个选项表⽰该⽤户可以将⾃⼰拥有的权限授权给别⼈。注
意：经常有⼈在创建操作⽤户的时候不指定WITH GRANT OPTION选项导致后来该⽤
户不能使⽤GRANT命令创建⽤户或者给其它⽤户授权。 备注：可以使⽤GRANT重复
给⽤户添加权限，权限叠加，⽐如你先给⽤户添加⼀个select权限，然后又给⽤户添
加⼀个insert权限，那么该⽤户就同时拥有了select和insert权限。

⽰例：
grant all on *.* to 'test1'@‘%’;
说明：给test1授权可以操作所有库所有权限，相当于dba
grant select on seata.* to 'test1'@'%';
说明：test1可以对seata库中所有的表执⾏select
grant select,update on seata.* to 'test1'@'%';
说明：test1可以对seata库中所有的表执⾏select、update
grant select(user,host) on mysql.user to 'test1'@'localhost';
说明：test1⽤户只能查询mysql.user表的user,host字段

查看用户都有哪些权限

SHOW GRANTS FOR '用户名'@'主机ip';
主机可以省略，默认值为%
SHOW grants;
查看当前用户权限

撤销用户权限

语法
revoke privileges ON database.table FROM '⽤户名'[@'主机'];
可以先通过show grants命令查询⼀下⽤户对于的权限，然后使⽤revoke命令撤销⽤户对应的权限，⽰例：
SHOW grants FOR 'test1'@'localhost';
REVOKE SELECT ON mysql.user FROM test1@localhost;

上⾯我们先通过grants命令查看test1的权限，然后调⽤revoke命令撤销对mysql.user表
host字段的查询权限，最后又通过grants命令查看了test1的权限，和预期结果⼀致。

 SHOW grants FOR 'adair';
 REVOKE SELECT ON mysql.user FROM adair;
 SHOW grants FOR 'adair';
  上⾯我们先通过grants命令查看adair的权限，然后调⽤revoke命令撤销对mysql.user表
的查询权限，最后又通过grants命令查看了adair的权限，和预期结果⼀致。
  当ip地址为本地主机时可以省略，若是远程连接就必须在用户名后加上@'主机ip'
  若是对表的授权，表名后没有字段名。若是对字段授权，需要用括号括起来字段名，并且多个字段用,隔开。

删除用户

方式1；
DROP user'用户名'@'主机'

方式2：
通过删除mysql.user表数据的方式删除
DELETE　FROM　user　WHERE　user＝'用户名' AND host='主机';
FLUSH privileges;
注意通过表的⽅式删除的，需要调⽤flush privileges;刷新权限信息（权限启动的时
候在内存中保存着，通过表的⽅式修改之后需要刷新⼀下）。

授权原则说明

1、授予能满足需要的最小权限
2、创建用户时限制用户的登陆主机，一般是限制成指定IP或内网IP段
3、初始化数据库时删除没有密码的用户，安装完数据库时会自动创建一些用户，这些用户默认没有密码
4、为每个用户设置满足密码发杂度的密码
5、定期清理不需要的用户，回收权限或删除用户

总结

1、通过命令的方式操作用户和权限不需要刷新，下次登陆自动生效
2、通过操作mysql库中表的方式修改用户信息，需要调用FLUSH PRIVILEGES刷新，刷新完成后下次登陆生效
3、mysql识别用户身份的方式时：用户名+主机，一般在创建用户时给一个固定主机ip，只有在这台主机上登陆才有效
4、本⽂中讲到的⼀些指令中带主机的，主机都可以省略，默认值为%，表⽰所有机器
5、mysql中⽤户和权限的信息在库名为mysql的库中