MySQL数据库详解 五：用户管理

1. 数据库的用户管理

1.1 新建用户

create user '用户名'@'来源地址' [identified by [passwd] '密码'];

'用户名'：指定将创建的用户名
'来源地址'：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符%
'密码'：若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密；
        若使用加密密码，需要先使用SELECT PASSWORD('密码'); 获取密文，再在语句中添加 PASSWORD '密文';
        若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用）
1
2
3
4
5
6
7

select user();
#查看当前登录用户
1
2

use mysql
select user,host,authentication_string from user;

select user,host,authentication_string from mysql.user;
#查看当前所有用户信息
1
2
3
4
5

create user 'aaa'@'%' identified by '123456';
#创建名为aaa的用户并可以使用任意地址登录

create user 'aaa'@'192.168.67.101' identified by '123456';
#创建名为aaa的用户并只能使用192.168.67.101地址登录

create user 'aaa'@'192.168.67.0/24' identified by '123456';
#创建名为aaa的用户并只能使用192.168.67.0网段地址登录

create user 'aaa'@'localhost' identified by '123456';
#创建名为aaa的用户并只能使用本机地址登录
1
2
3
4
5
6
7
8
9
10
11

1.2 重命名用户

rename user '旧用户名'@'源地址' to '新用户名'@'源地址';

#示例
rename user 'aaa'@'192.168.67.101' to 'bbb'@'192.168.67.0/24';
1
2
3
4

1.3 删除用户

drop user '用户名'@'源地址';

#示例
drop user 'bbb'@'192.168.67.0/24';
1
2
3
4

1.4 修改用户密码

set password = password('密码');                
#修改当前用户密码

set password for '用户名'@'源地址' = password('密码');
或
alter user '用户名'@'源地址' identified by '密码';
#修改目标用户密码
1
2
3
4
5
6
7

1.5 忘记用户密码的解决方法

#方式一
1. 修改mysql配置文件，在 [mysqld] 下面添加 skip-grant-tables 配置项
2. 重启mysqld服务，使用 mysql 密码直接登录 mysql
3. 执行 update user set authentication_string=password('密码') where user='root'; 命令修改 root 用户的密码
4. 还原mysql配置文件，重启mysqld服务，使用 mysql -u 用户名 -p密码 [-h mysql地址 -P mysql端口] 命令来验证登录
1
2
3
4
5

#方式二
#使用 update 修改 root 密码，刷新数据库
update mysql.user set authentication_string = passwd('abc123') where user='root';

FLUSH PRIVILEGES;
quit

mysql -u root -pabc123

注意：最后再把 /etc/my.cnf 配置文件里的 skip-grant-tables 删除，并重启 mysql 服务。
1
2
3
4
5
6
7
8
9
10

1.6 数据库用户授权

grant语句：专门用来设置数据库用户的访问权限。

当指定的用户名不存在时，grant语句将会创建新的用户；
当指定的用户名存在时， grant 语句用于修改用户信息。

1.6.1 授权用户权限类别

all privilege 权限如下

1.6.2 添加权限

grant 权限列表 on 数据库名.表名 to '用户名'@'来源地址' [identified by '密码'];
1

#示例
grant insert,update,select on scj.* to 'aaa'@'192.168.67.101';
#为用户aaa添加为scj库下所有表插入数据、更新表数据、查看表数据的权限
flush privileges;
#刷新权限使其立即生效

show grants for 用户名@来源地址;        #查看用户权限

show grants for 'aaa'@'192.168.67.101';
#查看aaa用户所有的权限
1
2
3
4
5
6
7
8
9
10

1.6.2 撤销权限

revoke 权限列表 on 数据库名.表名 from 用户名@来源地址;
1

#示例
revoke all on *.* from 'aaa'@'192.168.67.101';
#撤销aaa用户所有的权限

revoke select on *.* from 'aaa'@'192.168.67.101';
#撤销aaa用户的查看权限

flush privileges;
#刷新权限使其立即生效
1
2
3
4
5
6
7
8
9

2. mysql命令

mysql [OPTIONS] [database]
命令格式

-A, --no-auto-rehash 禁止补全
-u, --user= 用户名,默认为root
-h, --host= 服务器主机,默认为localhost
-p, --passowrd= 用户密码,建议使用-p,默认为空密码
-P, --port= 服务器端口
-S, --socket= 指定连接socket文件路径
-D, --database= 指定默认数据库
-C, --compress 启用压缩
-e   "SQL" 执行SQL命令
-V, --version 显示版本
-v  --verbose 显示详细信息
--print-defaults 获取程序默认使用的配置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql>use mysql           #切换数据库
mysql> select database(); #查看当前数据库
mysql>select user();      #查看当前用户
mysql>system clear        #清屏
mysql> ^DBye              #ctrl+d 退出
1
2
3
4
5

#mysqladmin命令

mysqladmin [OPTIONS] command command...

#查看mysql服务是否正常，如果正常提示mysqld is alive
mysqladmin -u用户 -p密码   ping
mysqladmin -uroot -p123123  ping

#关闭mysql服务，但mysqladmin命令无法开启
mysqladmin -uroot -pcentos shutdown

#创建数据库testdb
mysqladmin -uroot -pcentos   create testdb 

#删除数据库testdb
mysqladmin -uroot -pcentos   drop testdb

#修改root密码
mysqladmin -uroot -pAdmin@123 password ‘123'

#日志滚动,生成新文件/var/lib/mysql/mariadb-bin.00000N
mysqladmin -uroot -pcentos flush-logs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22