分类
服务器与web应用

企业级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

星九

时间会带来惊喜~

发表评论

邮箱地址不会被公开。 必填项已用*标注

11 − 1 =