MySQL数据库通常是由总管理员创建不同的管理账户，然后分配不同的权限，把这些账户交给相应的管理人员使用


接下来我将对用户与授权做一些基本的操作


#新建一个名为‘user01’的用户，密码为‘123456',主机名为localhost

mysql> create user 'user01'@'localhost' identified by '123456';


#查看系统用户，创建后的用户是保存在mysql数据库的user表中的，所以在查看用户时要先进mysql这个库

mysql> use mysql;
Database changed
mysql> select user, authentication_string, host from user;
+-----------+-------------------------------------------+-----------+
| user      | authentication_string                     | host      |
+-----------+-------------------------------------------+-----------+
| root      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| root      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | %         |
| user01    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |                  #可以看到user表中有我刚刚创建的user01用户，且主机名为localhost
+-----------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)



#对用户进行重命名

mysql> rename user 'user01'@'localhost' to 'server01'@'localhost';        #这里我将用户名‘user01’改为‘server01’
Query OK, 0 rows affected (0.00 sec)

mysql> select user, authentication_string, host from user;
+-----------+-------------------------------------------+-----------+
| user      | authentication_string                     | host      |
+-----------+-------------------------------------------+-----------+
| root      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| root      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | %         |
| server01   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | host      |                   #再次进行查看，可以看到用户名已经改变
+-----------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)
#修改用户密码

   （1）修改当前用户的密码

mysql> set password = password('123123');            #使用该命令将密码改为‘123123’
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit;
Bye
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.        #登入成功

  （2）修改其他用户的密码


mysql> set password for 'server01'@'localhost' = password('123123');       #将server01用户密码改为123123
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit;
Bye
[root@localhost ~]# mysql -u server01 -p                                            #用新密码进行登录
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.



#忘记root密码的解决办法

    （1）关闭数据库，进主配置文件


[root@localhost ~]# systemctl stop mysqld.service 
[root@localhost ~]# vim /etc/my.cnf

    （2）对主配置文件进行更改


[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
skip-grant-tables                    #加入这条语句，作用是登录数据库不需要密码

   （3）重启数据库，直接登录

[root@localhost ~]# systemctl restart mysqld.service 
[root@localhost ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 Source distribution

Copyright (c) 2000, 2016, 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）使用update修改root密码


mysql> update mysql.user set authentication_string=password('abc123') where user='root';           #将密码修改为‘abc123’
Query OK, 2 rows affected, 1 warning (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 1

mysql> quit;
Bye
[root@localhost ~]# vim /etc/my.cnf                                      #进入主配置文件
#skip-grant-tables                                                                  #注释掉前面加的这条语句，一定要注释掉这条命令

[root@localhost ~]# systemctl restart mysqld.service             #重启mysql服务
[root@localhost ~]# mysql -uroot -p                                     #使用新密码登录
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 Source distribution

Copyright (c) 2000, 2016, 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>                                                                                                         #登录成功


#删除用户

mysql> drop user 'server01'@'localhost';

#给用户授权      grant 权限列表 on 库名.表名 to 用户名@主机名 [identified by '密码']

mysql> grant all on mysql.user to 'tom'@'localhost' identified by '123456';        #all表示所有的权限都给tom这个用户，这里也可以更某一个权限，这条命令的用法有很多，如果用户列表中没有tom这个用户，便会自动创建，带有创建用户的功能，如果有这个用户，那指定的密码会覆盖原有的密码，带有更改用户密码的功能

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user, authentication_string, host from user;
+-----------+-------------------------------------------+-----------+
| user      | authentication_string                     | host      |
+-----------+-------------------------------------------+-----------+
| root      | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| root      | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | %         |
| tom       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
+-----------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)

    

#查看权限     show grants for 'username'@'主机名';

mysql> show grants for tom@localhost;           #查看tom的权限
+-------------------------------------------------------------+
| Grants for tom@localhost                                    |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'localhost'                     |
| GRANT ALL PRIVILEGES ON "mysql"."user" TO 'tom'@'localhost' |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)

#撤销用户权限        revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

mysql> revoke drop on mysql.user from 'tom'@'localhost';               #撤销tom的drop权限
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'tom'@'localhost';                                   #查看tom权限
+------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for tom@localhost                                                                                                                       |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'localhost'                                                                                                        |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON "mysql"."user" TO 'tom'@'localhost' |         #可以看到这里已经没有drop权限了
+------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)








转载于:https://blog.51cto.com/13706760/2165672