November 30, 2019
By: Damon

MySQL错误问题小结

  1. MySQL错误问题小结(2019-12-9更新)
    1. 一、概述
    2. 二、分析
      1. 1. open file数量达到上限
      2. 2.数据库连接数达到上限
      3. 3.错误连接达到上限
    3. 三、分析
      1. 1.2019-12-05
      2. 2.2019-12-07
      3. 3.2019-12-08
      4. 4.2019-12-09

MySQL错误问题小结(2019-12-9更新)

一、概述

  • 最近开发过程中遇到的MySQL错误：
  • open file数量达到上限
  • 数据库连接数达到上限
  • 错误连接达到上限

二、分析

最近公司的244应用服务器，连不上13数据库了，在解决的过程中，遇到了概述中的几个错误，下面我们来逐一分析一下。

1. open file数量达到上限

MySQL数据库每创建一个连接，就会打开对应的socket文件，随着连接数的增多，打开的socket文件也会增多。 所以当MySQL打开的最大文件数达到上限了，会报too many open file的错误。 而在通过Druid连接的应用服务器，会报下图中的错误。

mysqlinfo

通过服务器Shell查询MySQL当前打开文件数量

pid=$(ps aux | grep mysqld | grep -v grep | awk '{ print $2 }')
lsof -p $pid | wc -l

通过SQL语句查询open_file_limit

show variables like "open_files_limit";

如果当前打开文件数量大于open_file_limit，则需要修改my.cnf中的open_file_limits为更大的数。 MySQL官方文档给出了open_file_limit的建议值。 有效的open_files_limit值是基于系统启动指定的open_files_limit，max_connections和table_open_cache 计算得到，服务器将会获取三个指标中最大的值，如果三者指标都没有指定，服务器将获得os允许的最大值。

1) 10 + max_connections + (table_open_cache * 2)
2) max_connections * 5
3) 启动时设定的open_files_limit，如果没有指定默认为5000

2.数据库连接数达到上限

查询数据库最大连接数

show variables like "max_connections";

我本地库的最大连接数为151。 查询数据库当前的连接数

show global status like "Threads_connected";

我本地库的当前连接数为1。

下面我们来用程序模拟一下，使MySQL连接数达到最大。

(ns custombackend.db.get-connections
  (:require [clojure.java.jdbc :as jdbc]))

(def mysql-db {
               :class-name "com.mysql.jdbc.Driver"
               :subprotocol "mysql"
               :subname "//localhost:3306/bihai?serverTimezone=Asia/Shanghai"
               :user "root"
               :password "root"
               })

(doseq [i (range 200)]
  (prn "connection:" i)
  (jdbc/get-connection mysql-db))

执行上面的代码，结果如下：

"connection:" 0
"connection:" 1
"connection:" 2
......
"connection:" 149
"connection:" 150
"connection:" 151
Syntax error (SQLNonTransientConnectionException) compiling at 
(C:\develop\workspaces\Clojure_Projects\customplatform\custombackend\src\clj\custombackend\db\get_connections.clj:12:1).
Data source rejected establishment of connection,  message from server: "Too many connections"

再次查询数据库当前的连接数

show global status like "Threads_connected";

我本地库的当前连接数为152。 MySQL官方文档给出了说明，MySQL最多会维持max_connections+1个连接，那个额外的连接是给拥有超级权限的帐号使用的。

经查阅资料Max_used_connections为服务器启动后，响应的最大连接数，Threads_connected才为当前打开的连接数。 对于mysql服务器最大连接数值的设置范围比较理想的是：服务器响应的最大连接数值占服务器上限连接数值的比例值在10%以上， 如果在10%以下，说明mysql服务器最大连接上限值设置过高。 这里我个人建议，可以把max_connections设置为Max_used_connections * 3 解决方法，修改max_connections的值

set global max_connections = 300;

3.错误连接达到上限

应用服务器报以下错误。

java.sql.SQLException: null,  message from server: 
"Host '47.105.130.244' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"

根据错误提示，执行了以下SQL语句

flush hosts;

可是没过多长时间，还是报相同错误。通过查阅资料，是max_connect_errors这个参数的默认值为100太小了。

解决方法：

  • a)将最大错误连接数设置一个极大值
set global max_connect_errors = 4294967295;
  • b)编写定时任务auto_flush.sh定期执行flush hosts
#!/bin/bash

mysql -u root -pHCZT_Data_999 -e="flush hosts"

将上述shell脚本加入到定时任务中

crontab -e

# 每天5点执行
0 5 * * * /root/auto_flush.sh
  • c)禁用host cache

执行下面SQL

set global host_cache_size =0

或者

修改my.cnf配置文件，添加 skip-name-resolve

禁用host cache的方法，会导致 performance_schema.host_cache 表的记录就为空。 这样MySQL就不会记录错误连接数了。

####4.数据包分析 使用tcpdump命令在13服务器上抓取来自244应用服务器访问13205端口的数据包写入到info.txt中

tcpdump src host 47.105.130.244 and port 13205 -w info.txt

然后在用Wireshark分析这些数据包，如下图所示：

mysqlinfo

我们发现 4、5、7 关于 TLSv1.1的三个数据包，出现了异常，然后8中244就申请关闭244到13(内网为125)的TCP连接， 当244再次和13通信时，紧接着9、10两个数据包就会报错了。 之前一直怀疑是https证书的问题，但是应用服务器连接服务器，走的是tcp协议，不是https，所以之前的假设错误。 我们的13数据库服务器打开了SSL。

show VARIABLES like '%SSL%';

mysqlinfo

于是我们怀疑是数据库服务器开启了SSL，与之通信的客户端并没有正确配置SSL证书。

我们来测试一下，我本地的数据库未开启SSL

mysqlinfo

Jdbc连接中不使用SSL useSSL=false 抓包结果没有错误，如下

mysqlinfo

Jdbc连接中使用SSL useSSL=true 抓包结果也没有错误，如下

mysqlinfo

如果MySQL没有开启SSL，那么jdbc中设置useSSL为true或者false都不会产生异常报文。

下面我们连接开启了SSL的13数据库。

Jdbc连接中不使用SSL useSSL=false 抓包结果也没有错误，如下，这点也我预想的结果有点偏差。

mysqlinfo

Jdbc连接中使用SSL useSSL=true 本地项目直接启动不起来，报错如下：Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target。 是因为我本地没有13服务器的mysql SSL客户端证书。

最终解决方案： 在13服务器的mysql配置文件中加入

[mysqld]
...

# disable ssl
skip_ssl

重启mysql数据库，执行

show global VARIABLES like '%ssl%';

执行结果如下，说明ssl已经关闭 mysqlinfo

执行下面sql，查看错误连接数

select *
from `performance_schema`.host_cache;

从下图中可以看出错误连接数为0了

mysqlinfo

再次抓取244发往13的数据包，发现没有错误了。

mysqlinfo

由此我们可以推断出，是因为13服务器开启了ssl，244服务器在连接13服务器的时候， 作为客户端，没有正确配置客户端的证书，或者244上众多服务没有正确配置useSSL参数导致的。

三、分析

1.2019-12-05

  • 之前研究的Max_used_connections的含义理解错误了，Threads_connected才为当前打开的连接数，再次更正。

2.2019-12-07

  • 修改定时flush hosts脚本，解决cpu占用率高的问题。
  • 本地使用clojure建立连接，模拟数据库too many connections出现场景。
  • open_files_limit、max_connections设置规则更新。
  • 数据包错误分析。

3.2019-12-08

  • 定时flush hosts脚本，使用更简单的shell脚本，并使用crontab做定时器。
  • 数据包错误深一层次分析。
  • 明天试试先把13服务器上的SSL关闭，然后再次分析数据包。

4.2019-12-09

  • 将13服务器的SSL关闭，重启MySQL，解决了错误连接数一直增长的问题
Tags: mysql