常见的登录失败问题

本文汇总了一些与mysql登录失败有关的case，仅供日后参考。

[TOC]

场景1：User 'xxx' has exceeded the 'max_user_connections' resource

故障上报

• 2021.11.16.开发同事反馈连不上mysql，报错如下：

ERROR 1226 (42000): User 'xxx' has exceeded the 'max_user_connections' resource (current value: 20)

确认过程

• 执行 cat /data/mysql_3306/my_3306.cnf | grep max_user_connections 确认 max_user_connections = 2000
• 由于本地也无法登录数据库，只能停掉所有应用才能进入，然后查询 show variables like 'max_user_connections'; 确认 max_user_connections=20

解决方案

• 方案1：执行 set global max_user_connections=2000; 不报错但也不生效 确认 max_user_connections仍为20
• 方案2：尝试重启 mysql 服务后，max_user_connections仍为20
• 方案3：执行 alter user 'xxx'@'%'with max_user_connections 2000; 再次确认 select user,host,max_user_connections from mysql.user where user='xxx'; 生效了

场景2：Host 'x.x.x.x' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

故障上报

• 2021.08.11.开发同事反馈连不上mysql，报错如下：

ERROR 1129 (HY000) - Host 'x.x.x.x' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

发生原因

• 同一个ip在短时间内产生太多（超过mysql数据库max_connection_errors的最大值）中断的数据库连接而导致的阻塞，若该ip成功登录一次则清零；其他ip只要正常连接和释放则不受影响。在host_cache中记录了的blocked错误, 如果这种错误的数量太多, 且在上一次成功连接之后, 这种错误的数量超过了max_connect_errors的值，就会抛出这个错误并提示需要flush hosts;

确认过程

• 执行 show global status like 'Aborted_connects'; 确实如描述一般，too many......
• 执行 show variables like 'max_connect_errors'; 默认100
• 执行 show variables like '%host_cache_size%';
• 执行 select user,host,account_locked from mysql.user; 确实有账号被block

解决方案

• 方案1：执行 alter user 'xxx'@'%' account unlock; 临时解除锁定（治标不治本）
• 方案2：执行 set global max_connect_errors = 200; 提高允许的max_connection_errors数量（治标不治本）
• 方案3：执行 flush hosts; 清除host缓存，重置最大错误次数（治标不治本）
• 方案4：执行 set global host_cache_size=0; 禁用host cache，设置host_cache_size=0使得mysql认为blocked的数量一直是0，则避免了这个问题
• 方案5：如果dns解析比较慢的话，通过skip-name-resolve禁用DNS，也可以解决。

  注：生产库应该尽量设置skip_name_resolve以避免对DNS的依赖，当DNS不能正常工作时，会导致客户端无法通过TCP正常连接数据库；此外开启该功能有可能招致DoS攻击；

• 方案6：本质原因是由于程序中创建了过多的mysql连接，程序关闭的时候应及时断开与数据库的连接（最佳方案）

场景3：常见的 ERROR 1045 (28000) 错误

通常，ERROR 1045 (28000) 常见的报错场景如下：

• 指定的数据库 IP 错误
• 数据地址采用域名，但 DNS 解析主机名异常，比如解析失败或解析到别的ip
• 用户名不正确
• 密码填写错误
• 当密码出现在 Shell 脚本中，并且包含特殊字符如 $，#，! 等
• 当密码出现在配置文件中，并且包含特殊字符 # 时，需要用双引号将密码括起来
• 数据库用户受到连接主机限制，当前主机不允许连接
• 该账号开启了 SSL 连接属性（MySQL 客户端在 5.7 以后默认就开启 SSL）
• 使用了外部的认证方式，（如 AD、PAM、LDAP 等），但配置不正确

场景4：Access denied for user 'xxx'@'localhost'

故障上报

• 2021.08.11.通过 grant all privileges on *.* to 'xxx'@'%' identified by '***'; 创建新用户后登录失败，报错如下：

[root@localhost]# mysql -uxxx -p
ERROR 1045 (28000): Access denied for user 'xxx'@'localhost' (using password: YES)

发生原因

• MySQL中默认存在一个用户名为空的账户，只要在本地，可以不用输入账号密码即可登录到MySQL中。而因为这个账户的存在，导致了使用密码登录无法正确登录。

MariaDB [(none)]> select user,host from mysql.user;
+--------------+-------------------------+
| user         | host                    |
+--------------+-------------------------+
| root         | 127.0.0.1               |
| mariadb      | 172.16.%                |
| root         | ::1                     |
|              | izbp1ioe8vrh4dfe15xjh0z |
| root         | izbp1ioe8vrh4dfe15xjh0z |
|              | localhost               |
+--------------+-------------------------+

解决方案

• 删除这个用户名为空的账户即可

delete from mysql.user where user='';
flush privileges;

场景5：Access denied for user 'xxx'@'localhost'

故障上报

• 2020.07.16.本地登录mysql一直报如下错误，但是在别的机器通过 -hx.x.x.x 登录却ok

[root@localhost]# mysql -udba_monitor -p -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'dba_monitor'@'localhost' (using password: YES)

确认过程

• 执行 select user,host from mysql.user; 确认该账号 host='%'，但本地登录报错

发生原因

• MySQL在5.6.36及以前版本，%不包含127.0.0.1和localhost，即本地登录失败/别的机器成功

场景6：Hive连接MySQL报错 Access denied for user 'xxx'@'a.b.c.d'

故障上报

• 2022.02.08.开发反馈在x.x.4.178上用hive连mysql报如下错误：

[root@test-4-178 ~]# tail -n100 /var/log/hive/hadoop-cmf-hive-HIVEMETASTORE-test-4-178.log.out

2022-02-08 15:30:02,576 INFO  org.apache.hadoop.hive.metastore.HiveMetaStore: [main]: 0: Opening raw store with implementation class:org.apache.hadoop.hive.metastore.ObjectStore
2022-02-08 15:30:02,897 ERROR org.apache.hadoop.hive.metastore.HiveMetaStore: [main]: javax.jdo.JDOFatalDataStoreException: Unable to open a test connection to the given database. JDBC url = jdbc:mysql://x.x.4.177:3306/cdh_metastore?createDatabaseIfNotExist=true, username = xxx. Terminating connection pool (set lazyInit to true if you expect to start your database after your app). Original Exception: ------^M
java.sql.SQLException: Access denied for user 'xxx'@'test-4-178' (using password: YES)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)

解决方案

• 步骤1：任意机器 mysql -h x.x.4.177 -uxxx -p 连mysql实例ok，排除了账号的密码问题；
• 步骤2：执行 GRANT ALL PRIVILEGES ON *.* TO 'xxx'@'%'; 添加 all privileges 权限后 hive 仍报错，排除了账号的权限问题；
• 步骤3：在CDH的 hive-site.xml 的 Hive Metastore Server 高级配置代码段（安全阀） 下增加下述配置，检查配置文件 hive-site.xml 发现其并未更新。

<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://x.x.4.177:3306/cdh_metastore?createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>xxx</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>***</value>
</property>

• 步骤4：在CDH的 hive-site.xml 的 Hive 客户端高级配置代码段（安全阀） 下增加上述配置，重启 Hive 后检查配置文件 hive-site.xml 发现已更新，且Hive成功连接mysql。也就是说，步骤3未生效也不需要。

场景7：Unknown MySQL error

故障上报

• 2020.12.22.开发同事反馈登录mysql偶尔正常，偶尔报如下错误：

[root@localhost]# mysql -uroot -p
Enter password: 
ERROR 2000 (HY000): Unknown MySQL error

解决方案

• 方案1：执行 show processlist; 看到有1999个连接，而执行 show variables like '%connections%'; 看到 max_connections=2000，明显是达到最大连接数，调大它就好了

set global max_connections=4000;
set global max_user_connections=3000;

场景8：Authentication plugin 'caching_sha2_password' reported error

故障上报

• 2021.12.23.登录 mysql 8.0.22 报如下错误：

ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

确认过程

• 出现这个原因是 mysql 8 将默认的加密规由 mysql_native_password 改为 caching_sha2_password
• 登入 mysql，确认登录异常的账号其 plugin 为 caching_sha2_password，而 plugin 为 mysql_native_password 的账号则登录正常

(root@localhost) [(none)]> select user,host,plugin from mysql.user;
+--------------------+-----------+-----------------------+
| user               | host      | plugin                |
+--------------------+-----------+-----------------------+
| dba_admin_readonly | %         | caching_sha2_password |
| root               | %         | mysql_native_password |
+--------------------+-----------+-----------------------+

解决方案

• 方案：参考如下sql 将登录异常账号的 plugin 改为 mysql_native_password

alter user 'dba_admin_readonly'@'%' identified with mysql_native_password by 'xxx';

场景9：SSL connection error

故障上报

• 2022.08.22.在 ubuntu 20.04 登录mysql报错 SSL_connect: error:1425F102 ... unsupported protocol

[root@localhost]# mysql -uroot -proot -P3306 -h192.168.100.11
ERROR 2026 (HY000): SSL connection error: error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol

解决方案

• 方案：mysql登录添加参数 --ssl-mode=DISABLED

mysql -uroot -proot -P3306 -h192.168.100.11 --ssl-mode=DISABLED