一:用户的构成

用户名@白名单
例子:
root@'localhost'
root@'10.0.0.2'
root@'10.0.0.%'
root@'10.0.0.0/255.255.254.0'
root@'%'
root@'10.0.0.5%'

二:用户的操作

2.1 添加用户

mysql> create user cxy@'10.0.0.%' identified by '123456'

2.2 查看用户

mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| cxy           | 10.0.0.%  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.01 sec)

2.3 修改用户密码

mysql>alter user root@'localhost' identified by '123';

2.4 删除用户

mysql>drop user cxy@'10.0.0.%';

三:权限管理

3.1 权限说明

格式:grant 权限 on 范围 to 用户 identified by  密码;
权限:
ALL:
        SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, 
 REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES,
 EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, 
SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

ALL:以上所有权限,一般是普通管理员拥有
with grant option：超级管理员才具备的，给别的用户授权的功能
应用用户：SELECT,INSERT, UPDATE, DELETE

权限作用范围:
*.* : 所有数据库
wordpress.*  :某个数据库
wordpress.t1 :数据库里面的某个表

3.2 给用户授权

3.2.1 给某个用户授权所有权限

mysql> grant all on *.* to cxy@'10.0.0.%' identified by '123456';

3.2.2 给某个用户授权特定的权限

注意:给某个用户授权特定的权限,权限不会覆盖,只会累加,如果已经给了用户所有权限,在给用户某些特定的权限,这个用户它还是拥有所有权限

mysql>grant SELECT,INSERT, UPDATE, DELETE, CREATE, DROP on *.* to cxy@'10.0.0.%' identified by '123456';

3.3 查看用户权限

mysql> show grants for cxy@'10.0.0.%' ;
+-------------------------------------------------+
| Grants for cxy@10.0.0.%                         |
+-------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'cxy'@'10.0.0.%' |
+-------------------------------------------------+
1 row in set (0.01 sec)

3.4 回收权限

常规情况下只授权select/insert/update/delete 4个权限即可

mysql>revoke delete on *.* from cxy@'10.0.0.%';  

四:忘记数据库本地root密码

4.1 先关闭数据库,并启动到安全模式

[root@mysql-51 ~]# systemctl stop mysqld.service 
[root@mysql-51 ~]# mysqld_safe --skip-grant-tables --skip-networking &

4.2 登录数据库.修改管理员密码

mysql> flush privileges;
Mysql>alter user root@'localhost' identified by '123456';

4.3 重新启动数据库

root@db02-51 ~]# systemctl start mysqld.service