MySQL用户口令加密设置

1、MySQL数据库密码加密介绍

　　随着信息技术的快速发展，数据库的安全性变得越来越重要。在MySQL中，密码加密和设置是保护数据库安全的关键操作。本文将详细介绍MySQL数据库密码加密和设置的方法。

2、实验环境

　　数据库版本：8.0.45 MySQL Community Server - GPL

　　操作系统：Windows Server 2025

3、查看密码插件及加密方式

　　可通过以下SQL语句查看安装的密码插件：

mysql> show plugins;
+----------------------------------+----------+--------------------+---------+---------+
| Name                             | Status   | Type               | Library | License |
+----------------------------------+----------+--------------------+---------+---------+
| binlog                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |   --使用SHA1哈希算法，生成41位十六进制字符串（前面加一个'*'号，实际存储40位哈希值）
| sha256_password                  | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |   --使用SHA256算法，但传输协议不同
| caching_sha2_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |   --MySQL 8.0默认升级为caching_sha2_password（使用SHA-256哈希算法，生成更长的哈希值）
| sha2_cache_cleaner               | ACTIVE   | AUDIT              | NULL    | GPL     |
| daemon_keyring_proxy_plugin      | ACTIVE   | DAEMON             | NULL    | GPL     |
| CSV                              | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM                    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX_RESET       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TEMP_TABLE_INFO           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLES                    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLESTATS                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_INDEXES                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLESPACES               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_COLUMNS                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_VIRTUAL                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CACHED_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SESSION_TEMP_TABLESPACES  | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| MyISAM                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                       | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| PERFORMANCE_SCHEMA               | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| TempTable                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                          | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                        | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ndbcluster                       | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ndbinfo                          | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ndb_transid_mysql_connection_map | DISABLED | INFORMATION SCHEMA | NULL    | GPL     |
| ngram                            | ACTIVE   | FTPARSER           | NULL    | GPL     |
| mysqlx_cache_cleaner             | ACTIVE   | AUDIT              | NULL    | GPL     |
| mysqlx                           | ACTIVE   | DAEMON             | NULL    | GPL     |
+----------------------------------+----------+--------------------+---------+---------+
48 rows in set (0.00 sec)

　　MySQL通过mysql.user表存储用户密码的加密信息，可通过以下SQL语句查询具体的加密方式：

mysql> SELECT user,host,plugin,authentication_string FROM mysql.user;
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| user             | host      | plugin                | authentication_string                                                  |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | caching_sha2_password | $A$005$PEDTloQu)wMO:ZjsRqaLA2dcWNF4ZtyEv8D9HxkyEPQeGi6cR51Mj8XC        |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

　　plugin字段存储用户密码所使用的加密插件，authentication_string字段存储的是经过加密算法处理后的密码哈希值，具体格式取决于使用的密码组件。

　　在MySQL 8.0中，默认的身份验证插件是caching_sha2_password，它提供了更安全的密码哈希算法。如果需要兼容旧版本的客户端，可以改为使用mysql_native_password，但建议使用默认的caching_sha2_password。

4、设置密码的加密方式

　　若需将加密方式从caching_sha2_password修改为mysql_native_password，执行以下ALTER USER语句（需管理员权限）

mysql> CREATE USER 'test'@'localhost' IDENTIFIED BY 'Abcd@1234';　　--创建用户，默认使用caching_sha2_password加密方式
Query OK, 0 rows affected (0.03 sec) 
mysql> ALTER USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Abcd@1234'; 　　--修改用户密码的加密方式为mysql_native_password
Query OK, 0 rows affected (0.03 sec)　

　　查询修改结果：

mysql> select User,Host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
| test             | localhost | mysql_native_password |　　　　--test用户密码的加密方式已被修改为mysql_native_password
+------------------+-----------+-----------------------+
5 rows in set (0.01 sec)

　　查询所有账户密码所使用的加密组件以及加密情况：

mysql> select user,host,plugin,authentication_string from mysql.user;
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| user             | host      | plugin                | authentication_string                                                  |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | caching_sha2_password | $A$005$PEDTloQu)wMO:ZjsRqaLA2dcWNF4ZtyEv8D9HxkyEPQeGi6cR51Mj8XC        |
| test             | localhost | mysql_native_password | *47B150E012313114C04A1C9336709424085B6BD0                              |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)

　　查询指定账户密码所使用的加密组件以及加密情况：

mysql> select user,host,plugin,authentication_string from mysql.user where user='test';
+------+-----------+-----------------------+-------------------------------------------+
| user | host      | plugin                | authentication_string                     |
+------+-----------+-----------------------+-------------------------------------------+
| test | localhost | mysql_native_password | *47B150E012313114C04A1C9336709424085B6BD0 |
+------+-----------+-----------------------+-------------------------------------------+
1 row in set (0.00 sec)