输入密码报错ERROR 1045 (28000)

1
2
3
4
[root@cc01 ~]# mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

解决办法

1、在my.cnf文件下添加内容skip-grant-tables

1
[root@cc01 ~]# vim /etc/my.cnf

my.cnf文件如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[root@cc01 ~]# cat /var/my.cnf
cat: /var/my.cnf: 没有那个文件或目录
[root@cc01 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
skip-grant-tables
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

2、重新启动服务service mysqld restart

1
2
3
[root@cc01 ~]# service mysqld restart
或者
[root@cc01 ~]# systemctl restart mysqld.service

3、获取默认密码为：cat /var/log/mysqld.log | grep password

1
2
3
[root@cc01 ~]# cat /var/log/mysqld.log|grep password
2023-06-25T04:13:06.877387Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 8r?eVhBzesGK
2023-06-25T15:27:18.813616Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 9c9K#HOkc%s3

这里肯定是取后面一个最新的临时密码

4、修改密码

mysql -uroot -p原密码 password 新密码（新密码必须为字母加数字)

如果之前设置了密码策略，这里新密码不做要求

1
2
3
4
5
6
7
8
9
[root@cc01 ~]# mysql -uroot -p9c9K#HOkc%s3 password 12345678
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql  Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2000, 2023, 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.
·······

5、使用新密码进入：mysql -uroot -p即可

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@cc01 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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>

修改密码策略和修改密码冲突

解决办法1

可以先在 /etc/my.cnf文件下，在[mysqld]下面加上skip-grant-tables；然后进入mysql，此时更改密码策略，如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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.01 sec)

mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> set global validate_password.policy=LOW;               <---------------------- 
Query OK, 0 rows affected (0.00 sec) 

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             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.01 sec)

mysql> 
mysql> set global validate_password.length=6;   					<---------------------- 
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'validate_password%';            		<---------------------- 
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 6     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 1     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)


不足

如果是一开始跳过了权限认证，也就是没有输入密码，那么这个操作毫无意义，因为一旦改了my.cnf文件就需要重启mysql服务，这个密码策略会重置！！

尽管在更新完密码策略之后可以flush privileges;。

解决办法2

前面所有的办法都试过了，然后我就想既然这个密码安全策略只要重启服务就更改不成功，那如果不需要重启服务呢再在低密码安全策略下改个简单的密码不就行了。

首先要更改密码，就必须在登录MySQL时是用密码登入的，而不是跳过权限认证登录的，否则报错，如下

1
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

所以我们可以使用update语句先将密码置为空（null），为啥置为空可以呢？我猜测是因为null值用MySQL的算法加密后和不加密结果是一样的

1
update user set authentication_string='' where  user = 'root';

接下来我们得知道密码安全策略的值分别代表什么意思？

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 6     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 1     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.01 sec)

  • validate_password_length 固定密码的总长度；

  • validate_password_dictionary_file 指定密码验证的文件路径；

  • validate_password_mixed_case_count 整个密码中至少要包含大/小写字母的总个数；

  • validate_password_number_count 整个密码中至少要包含阿拉伯数字的个数；

  • validate_password_policy 指定密码的强度验证等级，默认为 MEDIUM；

    关于 validate_password_policy 的取值：

    • 0/LOW：只验证长度；
    • 1/MEDIUM：验证长度、数字、大小写、特殊字符；
    • 2/STRONG：验证长度、数字、大小写、特殊字符、字典文件；

  • validate_password_special_char_count 整个密码中至少要包含特殊字符的个数；

接下来我们就设置一个符合MySQL安全策略的密码就可，然后设置成功后更改密码安全策略，再更改成简单的密码，然后大功告成！

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '@admin123A';   <------------设置符合安全策略的密码
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
[root@cc01 ~]# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@cc01 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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> set global validate_password.policy=LOW;      <----------------------    设置安全策略等级为low
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password.length=6;       <----------------------    设置长度至少为6位
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'validate_password%';    <----------------------   查看密码安全策略表
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 6     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 1     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.01 sec)

mysql> flush privileges;    					 <----------------------   刷新权限
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '12345678';    <-------------更改成简单密码成功
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
[root@cc01 ~]# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@cc01 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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服务后发现，密码安全策略又被重置了，所以下次改密码也要按照上面步骤来，好在可以正常登进mysql。

2003-Can’t connect to MySql server on ‘localhost’(10038)错误

一般三个原因：

  • MySQL服务没有启动
  • 用户没有权限使用远程连接
  • Linux中防火墙中没有配置MySQL端口

MySQL服务没有启动

1
2
3
4
service mysql status
service mysql restart
service mysql start
service mysql stop

用户没有权限使用远程连接

如果没有权限，使用如下sql语句修改

1
2
update user set host = '%' where user = 'root' and host = 'localhost';
flush privileges;

Linux中防火墙中没有配置MySQL端口

关闭Linux防火墙即可

1
2
firewall-cmd --state #查看防火墙状态
systemctl stop firewalld  #关闭防火墙