一.MySQL用户管理

1.1创建用户

#说明host为主机域
create user root@'host' #5.7版本不支持
create user root@'host' identified by '123';
grant all on \*.\* to root@'host' identified by '123';

1.2MySQL用户定义

用户名@'主机域'
root@'localhost'
root@'%'
root@'10.0.0.%'
root@'10.0.%.%'
root@'10.%.%.%'
root@'10.0.0.5%' 范围：50~59 '5'
root@'10.0.0.0/255.255.255.0'

说明：用户的信息保存在mysql数据库中的user表中，通过以下的sql语句可以进行查看创建是否成功。

select user,host,password from mysql.user; 

1.3修改用户密码

1.grant

mysql> grant all on *.* to root@'localhost' identified by '123';

2.mysqladmin

[root@db01 ~]# mysqladmin  -uroot -p123 password '123456'

3.update

mysql> update mysql.user set password=PASSWORD('123456') where user='root' and host='localhost';
mysql> update mysql.user set authentication_string
=PASSWORD('123456') where user='root' and host='localhost';#mysql5.7版本以后的修改密码字段为authentication_string
#修改完成后刷新权限表
mysql> flush privileges;

4.set

#修改当前登录的用户密码
mysql> set password=PASSWORD('123456');

5.alter

#注意:从MySQL 5.7.6开始，不赞成使用grant修改密码。使用 ALTER USER来代替。
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'mypass';
mysql> ALTER USER USER() IDENTIFIED BY 'mypass'; 如果当前连接是匿名用户可以用该语句修改密码

1.4修改用户名

1.update
mysql> update mysql.user set user='test1' where user='test';
2.grant
3.alter
mysql> ALTER USER test@'%' IDENTIFIED BY '123456'; ## MySQL5.7更改用户名密码
4.rename
mysql> rename user test@'%' to test1@'localhost';

1.5删除用户

mysql> drop user root@'::1'
mysql> delete from mysql.user where user='root' and host='::1';
mysql> flush privileges;

二.MySQL用户权限

2.1权限分类

USAGE                                             #无权限,只有登录数据库,只可以使用test或test_*数据库
ALL                                               # 所有权限
INSERT,SELECT, 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 # 指定的权限
with grant option                                 # 允许把自己的权限授予其它用户或者从其他用户收回自己的权限      

2.2作用范围

*.*                        　　　　　　　　　　　 　# 全库、全表（mysql.user）
mysql.*                    　　　　　　　　　　　　 # mysql库下所有表（某库中的所有表）(mysql.db)
mysql.user                 　　　　　　　　　　　   # mysql库中user表(单表)(mysql.table_priv)
mysql.user.host            　　　　　　　　　　　　 # mysql库中user表的host列(mysql.columns_priv)

2.3主机登录权限

user@localhost                                   #表示user只能在本地通过socket登录服务器
user@10.0.0.1                                    #表示user用户只能在10.0.0.1登录数据库服务器
user@10.0.0.0/255.555.255.0                      #指定某个子网的主机可以登录数据库 
user@%                                           #表示user用户能在所有的机器上登录数据库服务器

2.4用户授权

格式如下：

show grants; 查看用户权限
grant 权限 on 库.表 to 用户@主机
grant 权限(列1,列2,...) on 库.表 to 用户@主机

mysql> grant select on db01.* to 'test'@'10.0.0.0/255.255.255.0';
mysql> flush privileges;

mysql> grant update(name,math) on db01.t2 to 'test1'@'10.0.0.0/255.255.255.0';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;

mysql> grant all on *.* to 'test2'@'10.0.0.254' identified by '123'
mysql> flush privileges;

2.5权限保存位置

mysql.user              # 所有mysql用户的账号和密码，以及对用户对全库全表权限（*.*）
mysql.db                # 非mysql库的授权都保存在此(db.*)
mysql.table_priv        # 某库某表的授权(db.table)
mysql.columns_priv      # 某库某表某列的授权(db.table.col1)
mysql.procs_priv        # 某库存储过程的授权

2.6回收权限：revoke

mysql> revoke select on mysql.user from test@localhost;   # --撤消指定的权限
mysql> revoke all privileges,grant option from test2@'%'; # --撤消所有的权限

三.误删除所有用户恢复

方法一

如图删除所有用户

#1.停止数据库
[root@db01 ~]# systemctl stop mysqld.service
#2.跳过授权表，跳过网络，启动MySQL
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
#3.登录数据库
[root@db01 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.40 Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
#4.往用户表（mysql.user）插入数据
mysql> insert into mysql.user values ('localhost','root',PASSWORD('123'),
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> 'Y',
    -> '',
    -> '',
    -> '',
    -> '',0,0,0,0,'mysql_native_password','','N');
Query OK, 1 row affected (0.00 sec)
#5.退出数据库之后重启数据库
[root@db01 ~]# systemctl restart mysqld.service
#6.测试连接数据库
[root@db01 ~]# mysql -uroot -p123
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.40 Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

方法二
如图删除用户表

#1.退出并停止数据库
[root@db01 ~]# systemctl stop mysqld.service
#2.跳过授权表，跳过网络，启动MySQL
[root@db01 ~]# mysqld_safe --skip-grant-tables -skip-networking &
#3.登录数据
[root@db01 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.40 Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
#4.刷新系统授权表
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#5.直接创建用户 并授权 给密码
mysql> grant all on *.* to root@'localhost' identified by '123' with grant option;
Query OK, 0 rows affected (0.00 sec)
#6.退出并重启数据库
[root@db01 ~]# systemctl restart mysqld.service
#7.测试连接
[root@db01 ~]# mysql -uroot -p123
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.40 Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

四.忘记root密码修改密码

#1.停止MySQL
[root@db01 ~]# systemctl stop mysqld
#2.跳过授权表，跳过网络，启动MySQL
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
#3.修改密码
mysql> update mysql.user set password=PASSWORD('123') where user='root' and host='localhost';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0
#4.刷新系统授权表
mysql>  flush privileges;
Query OK, 0 rows affected (0.00 sec)

#5.测试连接数据库
[root@db01 /application/mysql/scripts]# mysql -uroot -p123
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.40 Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>