MySQL的用户管理和密码管理

用户的密码管理

给用户改密码

• 初始化mysql后设置初始密码

mysqladmin -uroot password 'wzy666'

• 改变已有密码

mysqladmin -uroot -pwzy666 password 'wzy999'

• SQL语句改，前提是已经进入数据库

alter user root@'localhost' identified by '123456';

# 利用数据库服务SQL语句修改密码信息
mysql> alter user root@'localhost' identified by 'wzy123'; 
mysql> flush privileges;
# -- 适合于mysql 8.0

mysql> update mysql.user set authentication_string=PASSWORD('wzy123') where user='root' and host='localhost';
mysql> flush privileges;
# -- 适合于mysql 5.7
# PASSWORD是一个改密码的函数

mysql> set password for 'root'@'localhost'=PASSWORD('wzy123');
mysql> flush privileges;
# -- 适合于mysql 5.6

查看用户密码信息

如图，查看授权表

mysql> select user,host,authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | $A$005$dC 
                                          p!W6VI8SpVI YtOOsYvEqPZ4ksiKC2yTXWAfKFAFa3hPoEw0hDduo/D |
+------------------+-----------+------------------------------------------------------------------------+

root密码重置

说明：此次数据库为linux环境二进制部署的mysql8.0.26

1.首先关闭数据库。service mysqld stop…多种方式可以关闭

systemctl stop mysqld

2.安全模式下启动数据库。（安全模式可以免密登录）

/usr/local/mysql/bin/mysqld_safe \
--datadir=/data/3306/data \
--skip-grant-tables --skip-networking

# 输出信息：
Logging to '/data/3306/data/db01.err'.
2024-11-04T03:33:46.500373Z mysqld_safe Starting mysqld daemon with databases from /data/3306/data

• --datadir=/data/3306/data，安装数据库时，数据目录的位置

• --skip-networking，停用网络连接，防止无密码情况遭受外部连接，引发安全问题

• --skip-grant-tables，跳过授权表加载，这样就能连接数据库

3.尝试无密码登录成功

[root@db01~]# mysql -uroot
mysql>

3.重置密码信息

❗️此时内存中，并没有授权表信息，所以不能直接改密码。需要把磁盘中的授权表信息写入到内存中，再做修改

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

# 修改密码
mysql> alter user root@localhost identified by '123456';
Query OK, 0 rows affected (0.00 sec)

4.重新启动数据库

关闭之前开启的安全模式的数据库进程

pkill mysql

重新启动数据库

systemctl start mysqld

5.登录访问测试

[root@db01~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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的用户管理

• 增删改查用户
• 用户信息==用户名+登录的host

查看用户信息

• 查看所有登录的用户，show processlist

mysql> show processlist;
+----+-----------------+------------------+------+---------+------+------------------------+------------------+
| Id | User            | Host             | db   | Command | Time | State                  | Info             |
+----+-----------------+------------------+------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost        | NULL | Daemon  | 5842 | Waiting on empty queue | NULL             |
|  8 | user1           | 10.0.0.170:49194 | NULL | Sleep   | 5754 |                        | NULL             |
| 11 | root            | localhost        | NULL | Query   |    0 | init                   | show processlist |
| 14 | root            | 10.0.0.170:49198 | NULL | Sleep   | 4482 |                        | NULL             |
+----+-----------------+------------------+------+---------+------+------------------------+------------------+

MySQL配置root远程登录

如果使用的是MySQL初始化完成后的密码，虽然可以实现登录。但是会出现权限不足问题。建议创建一个远程账号

# 可选项，可以忽略
mysqld --initialize-insecure --user=mysql --datadir=/data/3306/data --basedir=/usr/local/mysql
mysql
create user root@'%' identified by '123456';
grant all on *.* to root@'%';

创建用户

db02创建用户
create user user2@'%' identified by 'wzy666';

web01使用mariadb客户端尝试远程登录失败
[root@web01~]# mysql -h 10.0.0.51 -uuser1 -pwzy666
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

原因：MySQL8和mariadb加密方式不一致

# MySQL8使用原密码插件，其他客户端可以远程登录
create user user3@'172.16.1.%' identified with mysql_native_password by 'wzy666';

• 查看当前数据库登录用户

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

删除用户，锁用户

方式1： 推荐
drop user user1;

方式2： 可能导致删除多个同名用户
delete from mysql.user where user='user2' and host='%';

• 删除多个用户，使用条件表达式where

delete from mysql.user where user='user1' or user='user2';

锁定/解锁用户

直接删除用户有风险，即使后期创建回来，也面临着权限再次设置的问题。当不需要用户时，可以暂时锁定用户，等待一段时间后，确认用户真的不需要了就可以删除用户。

# 锁定
alter user user3@'%' account lock;

# 解锁
alter user user3@'%' account unlock;

# 登录测试 失败，已被锁
[root@web01~]# mysql -h 10.0.0.51 -uuser3 -pwzy666
ERROR 3118 (HY000): Access denied for user 'user3'@'web01'. Account is locked.

2.查看用户锁定情况，mysql.user的 account_locked 字段Y表示锁定，N表示没有

用户密码的加密插件

现象：

./mysql -utest01 -p123 -h10.0.0.51
Warning: Using a password on the command line interface can be insecure.
ERROR 2059 (HY000): Authentication plugin ‘caching_sha2_password’ cannot be loaded: /usr/local/mysql/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

**原因：**新版数据库密码加密插件进行了更新

解决方式：

• 方式1：升级客户端，使客户端加密密码方式支持 caching_sha2_password
• 方式2：降级服务端，使服务端加密密码方式支持 mysql_native_password

查看用户的密码加密方式

1.查看系统默认加密方式

# MySQL8加密方式
mysql> show variables like '%auth%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+

# mariadb加密方式
MariaDB [(none)]> show variables like '%auth%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| secure_auth   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

2.查看每个用户的加密方式

mysql> select user,host,authentication_string,plugin from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user             | host      | authentication_string                                                  | plugin                |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user4            | $         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              | mysql_native_password |
| user1            | %         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              | mysql_native_password |
| user2            | %         | $A$005$&pFjeeAB
                                                >Q6yPTeTbJMbsJKqXdk6y8cgSpYWn3Y/AByDgrhFaH31I21B | caching_sha2_password |
| user3            | %         | *2032C0AE4B6A02A90186A076522B58DCBCA26467                              | mysql_native_password |
| user4            | %         | *2032C0AE4B6A02A90186A076522B58DCBCA26467                              | mysql_native_password |
| user5            | %         | *2032C0AE4B6A02A90186A076522B58DCBCA26467                              | mysql_native_password |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| root             | localhost | $A$005$LJS<)'E_\V:bzgSSZFmCsgks7ZdFYUqzu.kmMdC3X.rWniJra0gGUCP.r3 | caching_sha2_password |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+

3种使用旧版加密的方式

• 创建用户时使用旧版加密

create user user3@'172.16.1.%' identified with mysql_native_password by 'wzy666';

• 把现有用户的加密方式改为旧版

alter user 'user1'@'%' identified with mysql_native_password by '123456';

• 全局设置使用旧版加密

# 修改数据库服务配置文件信息
[root@xiaoQ-01 ~]# vim /etc/my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password
-- 修改数据库服务配置文件，并在mysqld中区域进行修改

# 修改数据库服务配置后重启服务程序
[root@xiaoQ-01 ~]# service mysqld restart
Shutting down MySQL. SUCCESS! 
Starting MySQL. SUCCESS! 

# 登录数据库服务查看加密插件默认配置：
mysql> show variables like '%auth%';
+----------------------------------------+------------------------------+
| Variable_name                          | Value                        |
+----------------------------------------+------------------------------+
| default_authentication_plugin          |        mysql_native_password |
+----------------------------------------+------------------------------+

登录白名单功能

MySQL 的 白名单功能用于限制哪些客户端可以连接到 MySQL 服务器。只有在白名单中的 IP 地址或者网络能够访问数据库，其他的则被拒绝连接。可以提高数据库的安全性，防止未经授权的访问

配置本地登录

使用回环接口 127.0.0.1 只允许本地登录

# 指定网段，只能标准掩码，不支持子网划分
root@'10.0.0.0/24'


[root@db01~]# mysql -uloopback -pwzy666 -h 127.0.0.1

提升登录范围

使用通配符 %，表示任意IP地址都可以远程登录mysql

UPDATE mysql.user SET host = '%' WHERE user = 'test01';

使用回环接口 127.0.0.1 只允许本地登录

# 指定网段，只能标准掩码，不支持子网划分
root@'10.0.0.0/24'


[root@db01~]# mysql -uloopback -pwzy666 -h 127.0.0.1

提升登录范围

使用通配符 %，表示任意IP地址都可以远程登录mysql

[外链图片转存中…(img-vompddTs-1733159127436)]

UPDATE mysql.user SET host = '%' WHERE user = 'test01';

[外链图片转存中…(img-1ITkpnoQ-1733159127437)]