1,mysql用户和权限管理
在mysql自带的库中有一个mysql，这个库包含了太多的东西，其中有一张表user，这张表存储了所有的用户信息。
mysql> select user,host,password from user;
+------+-----------------------+-------------------------------------------+
| user | host | password |
+------+-----------------------+-------------------------------------------+
| root | localhost | *E01F5425C006D5AF5E3E22EEF9D6AF6BD19FDFED |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | localhost.localdomain | |
+------+-----------------------+-------------------------------------------+
6 rows in set (0.00 sec)
一、重置密码
首先来解决，如果root密码忘记了，该怎么解决？
找回密码有前提，前提就是你能够停止mysql服务。
1）停服务
# /etc/init.d/mysqld stop
2）使用跳过授权表的方式启动数据库
# mysqld_safe --skip-grant-tables --user=mysql &
关闭：mysqladmin shutdown
3）匿名登录
# mysql
4）修改user表
mysql> update user set password='' where user='root' and host='localhost';
5）重启mysql服务
# /etc/init.d/mysqld restart
6）重设用户密码密码
mysql> use mysql;
mysql> update user set password=password(‘123’) where user=‘root’ and host=‘localhost’;
mysql> update user set password=PASSWORD('要修改的密码') where user='root';
MySQL5.7版本后，password这个字段被改成 authentication_string
mysql> update user set authentication_string=password("您要修改的密码") where user="root";
mysql> flush privileges;
二、用户管理
1.创建用户
语法：create user 用户名@主机 identified by 密码;
mysql> create user kebi@localhost identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from user;
+------+-----------------------+-------------------------------------------+
| user | host | password |
+------+-----------------------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | localhost.localdomain | |
| kebi | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+------+-----------------------+-------------------------------------------+
7 rows in set (0.00 sec)
2.删除用户
语法：drop user 用户名@主机;
mysql> select user,host,password from user;
+------+-----------------------+-------------------------------------------+
| user | host | password |
+------+-----------------------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | localhost.localdomain | |
+------+-----------------------+-------------------------------------------+
6 rows in set (0.00 sec)
3.授权
grant不仅可以用来授权，还可以用来创建用户；
授权的语法：grant 权限列表 on 库名.表名 to 用户@主机 identified by '密码';
刷新权限：flush privileges;
mysql> grant all on *.* to kebi@'192.168.%.%' identified by '123'; #*.*：所有库上的所有表； 192.168.%.%：192.168内的所有Ip；123：密码
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from user;
+------+-----------------------+-------------------------------------------+
| user | host | password |
+------+-----------------------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | localhost.localdomain | |
| kebi | 192.168.%.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+------+-----------------------+-------------------------------------------+
7 rows in set (0.00 sec)
mysql> grant select,insert on class_7.* to maoxian@localhost identified by '123';
授权 读，写 在库 class_7上的所有表 对于 毛线 用123登陆
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from user;
+---------+-----------------------+-------------------------------------------+
| user | host | password |
+---------+-----------------------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | localhost.localdomain | |
| kebi | 192.168.%.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| maoxian | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+---------+-----------------------+-------------------------------------------+
8 rows in set (0.00 sec)
4.查看用户权限
回收语法：show grants for 用户@主机;
mysql> show grants for maoxian@localhost;
+----------------------------------------------------------------------------------------------------------------+
| Grants for maoxian@localhost |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'maoxian'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, INSERT ON `class_7`.* TO 'maoxian'@'localhost' |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
5.回收权限
回收权限有两种方法：删除用户和revoke
回收语法： revoke 权限 on 库名.表名 from 用户@主机；
回收部分权限
mysql> revoke insert,select on *.* from maoxian@localhost;
Query OK, 0 rows affected (0.00 sec)
回收全部权限
mysql> revoke all on *.* from maoxian@localhost;
Query OK, 0 rows affected (0.00 sec)
