企业级MySQL用户管理【显哥出品，必为精品】

1.mysql用户的定义

mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| rep  | 127.0.0.1 |
| root | 127.0.0.1 |
| root | ::1       |
|      | db01      |
| root | db01      |
|      | localhost |
| root | localhost |
+------+-----------+
7 rows in set (0.01 sec)

#以上是7个用户，在mysql中，用户的定义是  '用户名'@'主机域'

1）用户名写法

mysql> create user lhd@'10.0.0.51';
Query OK, 0 rows affected (0.01 sec)

#数字我们不作为用户创建，如果要创建，加上引号将数字解析成字符串就能添加了
mysql> create user '123'@'10.0.0.51';
Query OK, 0 rows affected (0.00 sec)

2）主机域的写法

127.0.0.1
::1
localhost
db01
10.0.0.51
10.0.0.%
10.0.%.%
10.%.%.%
%
10.0.0.5%  （10.0.0.51-10.0.0.59,10.0.0.5）

10.0.0.0/255.255.255.0
10.0.0.0/24	#可以创建但是不生效
#测试：
	mysql> grant all on *.* to lhd@'10.0.0.0/255.255.255.0' identified by '123';
	Query OK, 0 rows affected (0.00 sec)

	mysql> grant all on *.* to qiudao@'10.0.0.0/24' identified by '123';
	Query OK, 0 rows affected (0.00 sec)

2.用户的管理

1）创建用户

mysql> create user test@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

2）查看用户

mysql> select user,host from mysql.user;
+--------+------------------------+
| user   | host                   |
+--------+------------------------+
| lhd    | 10.0.0.%               |
| test   | 10.0.0.%               |
| root   | localhost              |
+--------+------------------------+
6 rows in set (0.00 sec)

3）删除用户

mysql> drop user qiudao@'10.0.0.0/24';
Query OK, 0 rows affected (0.00 sec)

4）修改密码

#1.update修改密码
mysql> update mysql.user set password=password('345') where user='lhd' and host='10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

#2.mysqladmin修改密码
[root@db01 ~]# mysqladmin -uroot -p123 password '345'
为了不暴露密码
[root@db01 ~]# mysqladmin -uroot -p password
Enter password: 
New password: 
Confirm new password:

#3.grant授权修改密码
mysql> grant all on *.* to lhd@'10.0.0.%' identified by '789';
Query OK, 0 rows affected (0.00 sec)

#4.修改当前登录用户的密码
mysql> set password=password('456');
Query OK, 0 rows affected (0.00 sec)

3.忘记root密码怎么办？

1.停止数据库
[root@db01 ~]# systemctl stop mysql

2.跳过授权表和网络启动
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &

3.连接数据
[root@db01 ~]# mysql

4.update修改密码
mysql> update mysql.user set password=password('123') where  user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

5.刷新授权表
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

6.重启数据库
[root@db01 ~]# mysqladmin shutdown
[root@db01 ~]# systemctl start mysql

7.使用新密码连接测试
[root@db01 ~]# mysql -uroot -p123