MySQL用户管理

1. 用户名格式

   用户名@'白名单'
   user001@'10.1.1.1'
   user001@'10.1.1.%'
   user001@'10.1.1.0/255.255.255.128'
   user001@'10.1.1.1%'
   user001@'localhost'
   user001@'%'
   user001@'db01'
   

2. 增删改查

   增
   mysql> create user user001@'10.1.1.%' identified by '123456'
   查
   mysql> select user from mysql.user;
   mysql> select user,host,authentication_string from mysql.user;
   mysql> desc mysql.user;
   改
   mysql> alter user user001@'10.1.1.%' identified by 'admin@123';
   删
   mysql> drop user user001@'10.1.1.%';
   

3. 用户权限

   语法格式：
   grant 权限 on 范围 to 用户 with grant option
   例：
   mysql> grant select,insert,delete on *.* to user001@'10.1.1.%' with grant option;
   mysql> show grants for user001@'10.1.1.%';                //查看授权
   mysql> show privileges;                                   //查看有哪些权限
   mysql> revoke delete on *.* from user001@'10.1.1.%';      //回收delete权限
   

4. 忘记管理员密码

   [root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking & //跳过验证和网络
   mysql> flush privileges;
   mysql>  alter user root@'localhost' identified by '123456';
   [root@db01 ~]# pkill mysqld
   [root@db01 ~]# systemctl start  mysqld