文章目录

• 一、配置远程主机可登录mysql数据库
• 二、MySQL的密码复杂度
• • 5.7的密码复杂度是由validate_password_policy参数控制
  • 8.0的密码复杂度是由validate_password.policy参数控制
• 三、修改密码过期时间
• 四、修改root密码

一、配置远程主机可登录mysql数据库

用户可从哪台主机连接mysql数据库是由mysql.user表中的host值来确定的。

默认情况下host值都为localhost，如用户需要从任何主机都可连接mysql数据库，可将host值置为%，host值也可为固定IP地址，表示该用户只能从该IP地址所属主机登录连接mysql。mysql> select user,host from mysql.user;+------------------+-----------+| user | host|+------------------+-----------+| mysql.infoschema | localhost || mysql.session| localhost || mysql.sys| localhost || root | localhost |+------------------+-----------+

将user用户的host值设置为%

mysql> update mysql.user set host='%' where user='root';Query OK, 1 row affected (0.01 sec)Rows matched: 1Changed: 1Warnings: 0

刷新权限

mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)

二、MySQL的密码复杂度

MySQL 系统自带有 validate_password 插件，此插件可以验证密码强度，未达到规定强度的密码则不允许被设置。MySQL 5.7版本默认是不启用该插件的，8.0 版本默认情况下启用该插件。

5.7的密码复杂度是由validate_password_policy参数控制

mysql> show variables like'validate_password%';Empty set (0.00 sec)--查询参数后发现参数不存在，我们需要安装一下validate_password 插件。

安装插件

mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';Query OK, 0 rows affected (0.05 sec)--插件安装完成后，即可看到相关参数。mysql> show variables like'validate_password%';+--------------------------------------+--------+| Variable_name| Value|+--------------------------------------+--------+| validate_password_check_user_name| OFF|| validate_password_dictionary_file||| validate_password_length | 8|| validate_password_mixed_case_count | 1|| validate_password_number_count | 1|| validate_password_policy | MEDIUM || validate_password_special_char_count | 1|+--------------------------------------+--------+7 rows in set (0.00 sec)--安装插件后密码强度默认值是MEDIUM，不影响我们现有弱密码用户登录数据库

8.0的密码复杂度是由validate_password.policy参数控制

mysql> show variables like'validate_password%';+--------------------------------------+--------+| Variable_name| Value|+--------------------------------------+--------+| validate_password.check_user_name| ON || validate_password.dictionary_file||| validate_password.length | 8|| validate_password.mixed_case_count | 1|| validate_password.number_count | 1|| validate_password.policy | MEDIUM || validate_password.special_char_count | 1|+--------------------------------------+--------+7 rows in set (0.00 sec)

validate_password 插件参数解释：

1、validate_password_policy代表的密码策略，默认是MEDIUM 可配置的值有以下：0 or LOW 仅需需符合密码长度（由参数validate_password_length指定）1 or MEDIUM 满足LOW策略，同时还需满足至少有1个数字，小写字母，大写字母和特殊字符2 or STRONG 满足MEDIUM策略，同时密码不能存在字典文件（dictionary file）中2、validate_password_dictionary_file用于配置密码的字典文件，当validate_password_policy设置为STRONG时可以配置密码字典文件，字典文件中存在的密码不得使用。3、validate_password_length用来设置密码的最小长度，默认值是84、validate_password_mixed_case_count当validate_password_policy设置为MEDIUM或者STRONG时，密码中至少同时拥有的小写和大写字母的数量，默认是1最小是0；默认是至少拥有一个小写和一个大写字母。5、validate_password_number_count当validate_password_policy设置为MEDIUM或者STRONG时，密码中至少拥有的数字的个数，默认1最小是06、validate_password_special_char_count当validate_password_policy设置为MEDIUM或者STRONG时，密码中至少拥有的特殊字符的个数，默认1最小是0

三、修改密码过期时间

mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user;+------------------+-----------+------------------+-------------------+-----------------------+----------------+| user | host| password_expired | password_lifetime | password_last_changed | account_locked |+------------------+-----------+------------------+-------------------+-----------------------+----------------+| root | % | N|NULL | 2021-12-30 11:28:07 | N|| mysql.infoschema | localhost | N|NULL | 2021-12-30 11:14:57 | Y|| mysql.session| localhost | N|NULL | 2021-12-30 11:14:57 | Y|| mysql.sys| localhost | N|NULL | 2021-12-30 11:14:57 | Y|+------------------+-----------+------------------+-------------------+-----------------------+----------------+4 rows in set (0.00 sec)--用户密码过期状态由password_expired控制，过期时间由password_lifetime控制。

使test用户密码立即过期

mysql> create user test identified by'Huawei12#$';Query OK, 0 rows affected (0.05 sec)mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user;+------------------+-----------+------------------+-------------------+-----------------------+----------------+| user | host| password_expired | password_lifetime | password_last_changed | account_locked |+------------------+-----------+------------------+-------------------+-----------------------+----------------+| root | % | N|NULL | 2021-12-30 11:28:07 | N|| test | % | N|NULL | 2022-05-16 22:52:51 | N|| mysql.infoschema | localhost | N|NULL | 2021-12-30 11:14:57 | Y|| mysql.session| localhost | N|NULL | 2021-12-30 11:14:57 | Y|| mysql.sys| localhost | N|NULL | 2021-12-30 11:14:57 | Y|+------------------+-----------+------------------+-------------------+-----------------------+----------------+5 rows in set (0.00 sec)mysql> alter user 'test'@'%' password expire;Query OK, 0 rows affected (0.01 sec)mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user;+------------------+-----------+------------------+-------------------+-----------------------+----------------+| user | host| password_expired | password_lifetime | password_last_changed | account_locked |+------------------+-----------+------------------+-------------------+-----------------------+----------------+| root | % | N|NULL | 2021-12-30 11:28:07 | N|| test | % | Y|NULL | 2022-05-16 22:52:51 | N|| mysql.infoschema | localhost | N|NULL | 2021-12-30 11:14:57 | Y|| mysql.session| localhost | N|NULL | 2021-12-30 11:14:57 | Y|| mysql.sys| localhost | N|NULL | 2021-12-30 11:14:57 | Y|+------------------+-----------+------------------+-------------------+-----------------------+----------------+5 rows in set (0.00 sec--test用户的password_expired列值立即变为了Y，重新用test用户登录会提示你修改密码。

修改账号密码永不过期

mysql> alter user 'test'@'%' password expire never;Query OK, 0 rows affected (0.15 sec)mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user;+------------------+-----------+------------------+-------------------+-----------------------+----------------+| user | host| password_expired | password_lifetime | password_last_changed | account_locked |+------------------+-----------+------------------+-------------------+-----------------------+----------------+| root | % | N|NULL | 2021-12-30 11:28:07 | N|| test | % | N| 0 | 2022-05-16 22:57:55 | N|| mysql.infoschema | localhost | N|NULL | 2021-12-30 11:14:57 | Y|| mysql.session| localhost | N|NULL | 2021-12-30 11:14:57 | Y|| mysql.sys| localhost | N|NULL | 2021-12-30 11:14:57 | Y|+------------------+-----------+------------------+-------------------+-----------------------+----------------+5 rows in set (0.01 sec)--test用户的password_lifetime列值立即变为了0。

设置账号密码90天过期

mysql> ALTER USER 'test'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;Query OK, 0 rows affected (0.01 sec)mysql>select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user;+------------------+-----------+------------------+-------------------+-----------------------+----------------+| user | host| password_expired | password_lifetime | password_last_changed | account_locked |+------------------+-----------+------------------+-------------------+-----------------------+----------------+| root | % | N|NULL | 2021-12-30 11:28:07 | N|| test | % | N|90 | 2022-05-16 22:57:55 | N|| mysql.infoschema | localhost | N|NULL | 2021-12-30 11:14:57 | Y|| mysql.session| localhost | N|NULL | 2021-12-30 11:14:57 | Y|| mysql.sys| localhost | N|NULL | 2021-12-30 11:14:57 | Y|+------------------+-----------+------------------+-------------------+-----------------------+----------------+5 rows in set (0.00 sec)

将账号test使用默认的密码过期全局策略

mysql> ALTER USER 'test'@'%' PASSWORD EXPIRE DEFAULT;Query OK, 0 rows affected (0.06 sec)mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user;+------------------+-----------+------------------+-------------------+-----------------------+----------------+| user | host| password_expired | password_lifetime | password_last_changed | account_locked |+------------------+-----------+------------------+-------------------+-----------------------+----------------+| root | % | N|NULL | 2021-12-30 11:28:07 | N|| test | % | N|NULL | 2022-05-16 22:57:55 | N|| mysql.infoschema | localhost | N|NULL | 2021-12-30 11:14:57 | Y|| mysql.session| localhost | N|NULL | 2021-12-30 11:14:57 | Y|| mysql.sys| localhost | N|NULL | 2021-12-30 11:14:57 | Y|+------------------+-----------+------------------+-------------------+-----------------------+----------------+5 rows in set (0.00 sec)

设置密码全局过期策略

mysql> show variables like 'default_password_lifetime';+---------------------------+-------+| Variable_name | Value |+---------------------------+-------+| default_password_lifetime | 0 |+---------------------------+-------+1 row in set (0.00 sec)--默认为0，表示密码不过期。mysql> SET GLOBAL default_password_lifetime = 90;Query OK, 0 rows affected (0.00 sec)--设置密码90天过期mysql> show variables like 'default_password_lifetime';+---------------------------+-------+| Variable_name | Value |+---------------------------+-------+| default_password_lifetime | 90|+---------------------------+-------+1 row in set (0.01 sec)# 写入配置文件使得重启生效[mysqld]default_password_lifetime = 90

四、修改root密码

修改密码有很多种方法，这里记录最简单的一种。

--5.7和8.0均可用这种方式修改。mysql> alter user root@'%' identified with mysql_native_password by'Huawei12#$';

这里要注意一个问题，我们这修改的是root@’%’这个用户的密码，也就是修改的root远程登录时候的密码，本底登录也就是root@’localhost’的密码并没有修改。

--用户表里面有root@'%'和root@'localhost'，user表的user和host列是双主键。mysql> select user,host from mysql.user;+---------------+-----------+| user| host|+---------------+-----------+| root| % || mysql.session | localhost || mysql.sys | localhost || root| localhost |+---------------+-----------+4 rows in set (0.00 sec)--(root,%)，表示可以远程登录，并且是除服务器外的其他任何终端，%表示任意IP都可登录。--(root,localhost)，表示可以本地登录，即可以在服务器上登陆，localhost则只允许本地登录。--(root,127.0.0.1 )，表示可以本机登陆，即可以在服务器上登陆

忘记root密码

--MySQL 5.7.6 and later#vi /etc/my.cnfbasedir=/usr/local/mysqldatadir=/usr/local/mysql/dataskip-grant-tables--在my.cnf配置文件内添加skip-grant-tables跳过权限验证#service mysqld restart --重启mysql服务--直接输入mysql登录[root@hisdb etc]# mysqlWelcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 7Server version: 8.0.27 MySQL Community Server - GPLCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>--开始修改root密码