• 欢迎访问显哥博客,本网站纯属学习技术,绝无商业用途,欢迎小伙伴们共同学习!研究技术!QQ:52249909 加我QQ
  • 世界75亿人,这么小的概率,能认识你,是我一生的幸运,不妨加个QQ接触一下:52249909 加我QQ

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

MySQL lixian 4年前 (2020-06-09) 20793次浏览 0个评论 扫描二维码
文章目录[隐藏]

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

本站博主 , 版权所有丨如未注明 , 均为原创
转载请注明原文链接:企业级MySQL用户管理【显哥出品,必为精品】
喜欢 (0)

您必须 登录 才能发表评论!