PHP& MySQL - SQLSTATE [HY000] [2003]无法连接到MySQL服务器 - 通过TCP的许多连接 [英] PHP & MySQL - SQLSTATE[HY000] [2003] Can't connect to MySQL server - Many Connections over TCP

问题描述

我有很多PHP实例（250到500或更多），它们打开一个DB-Connection，并执行SELECT，UPDATE和INSERT。几秒钟后，我收到以下错误：

  PHP致命错误：未捕获的异常'PDOException'与消息'SQLSTATE [HY000] [ 2003]无法连接到/var/www/xxx/vendor/propel/propel/src/Propel/Runtime/Connection/PdoConnection.php:49中的'xxx.xxx.xxx.xxx'（4）'上的MySQL服务器
  

我花了至少30个小时找到解决方案。在我看来，错误在于错误的MySQL或Debian配置。我发现了各种配置选项，但没有一个可以使用。

---

其他信息

• 错误只发生在远程服务器上 - 当我直接在DB-Server上启动脚本时，我没有错误。

---

当前配置

sysctl.conf / p>

  net.ipv4.conf.default.rp_filter = 1 
 net.ipv4.conf.default.accept_source_route = 0 
 kernel.sysrq = 0 
 kernel.core_uses_pid = 1 
＃禁用网桥上的netfilter。 
＃net.bridge.bridge-nf-call-ip6tables = 0 
＃net.bridge.bridge-nf-call-iptables = 0 
＃net.bridge.bridge-nf-call -arptables = 0 
 
 net.ipv4.ip_forward = 1 
 net.ipv4.neigh.default.gc_thresh1 = 4096 
 net.ipv4.neigh.default.gc_thresh2 = 8192 
 net.ipv4.neigh.default.gc_thresh3 = 16384 
 net.ipv4.neigh.default.gc_interval = 5 
 net.ipv4.neigh.default.gc_stale_time = 120 
 net。 core.netdev_max_backlog = 262144 
＃net.core.rmem_default = 16777216 
 net.core.rmem_max = 108544 
 net.core.somaxconn = 32768 
 net.core.wmem_max = 108544 
 
 net.netfilter.nf_conntrack_max = 10000000 
 net.netfilter.nf_conntrack_tcp_timeout_established = 40 
 net.netfilter.nf_conntrack_tcp_timeout_close = 10 
 net.netfilter.nf_conntrack_tcp_timeout_close_wait = 10 
 net.netfilter.nf_conntrack_tcp_timeout_fin_wait = 10 
 net.netfilter.nf_conntrack_tcp_timeout_last_ack = 10 
 net.netfilter.nf_conntrack_tcp_timeout_syn_recv = 10 
 net.netfilte r.nf_conntrack_tcp_timeout_syn_sent = 10 
 net.netfilter.nf_conntrack_tcp_timeout_time_wait = 10 
 
 net.ipv4.tcp_fin_timeout = 1 
 net.ipv4.tcp_max_orphans = 262144 
 net.ipv4。 tcp_max_syn_backlog = 16384 
 net.ipv4.tcp_max_syn_backlog = 262144 
 net.ipv4.tcp_rmem = 4096 87380 16777216 
 net.ipv4.tcp_sack = 0 
 net.ipv4.tcp_syn_retries = 2 
 net.ipv4.tcp_synack_retries = 2 
 net.ipv4.tcp_syncookies = 0 
 net.ipv4.tcp_timestamps = 0 
 net.ipv4.tcp_tw_recycle = 1 
 net.ipv4 .tcp_wmem = 4096 16384 16777216 
  

etc / mysql / my.cf / p>

  [client] 
 port = 3306 
 socket = /var/run/mysqld/mysqld.sock 
 
 [mysqld_safe] 
 socket = /var/run/mysqld/mysqld.sock 
 nice = 0 
 
 [mysqld] 
 user = mysql 
 pid-file = /var/run/mysqld/mysqld.pid 
 socket = /var/run/mysqld/mysqld.sock 
 port = 3306 
 basedir = / usr 
 datadir = / var / lib / mysql 
 tmpdir = / tmp 
 lc-messages-dir = / usr / share / mysql 
 skip-external-locking 
 
 bind-address = {local server ip} 
 
 key_buffer = 16M 
 max_allowed_pa​​cket = 16M 
 thread_stack = 192K 
 thread_cache_size = 1100 
 myisam -recover = BACKUP 
 max_connections = 1000 
 #table_cache = 64 
 #thread_concurrency = 10 
 
 query_cache_limit = 2G 
 query_cache_size = 32M 
 
 #general_log_file = /var/log/mysql/mysql.log 
 #general_log = 1 
 log_error = /var/log/mysql/error.log 
 slow_query_log_file = / var / log /mysql/mysql-slow.log 
 slow_query_log = 1 
 long_query_time = 2 
 log_queries_not_using_indexes 
＃server-id = 1 
 #log_bin = / var / log / mysql /mysql-bin.log 
 expire_logs_days = 10 
 max_binlog_size = 100M 
 #binlog_do_db = include_database_n ame 
 #binlog_ignore_db = include_database_name 
 
 innodb_buffer_pool_size = 16G 
 innodb_buffer_pool_instances = 16 
 
 [mysqldump] 
 quick 
 quote-names 
 max_allowed_pa​​cket = 16M 
 
 [mysql] 
＃no-auto-rehash＃更快启动mysql但没有标签完成
 
 [isamchk] 
 key_buffer = 16M 
 
！includedir /etc/mysql/conf.d/ 
  

加载模块 - modules.conf

  nf_conntrack 
 nf_conntrack_ipv4 
  

脚本启动时启动

  echo1> / proc / sys / net / ipv4 / tcp_tw_reuse 
 echo1> / proc / sys / net / ipv4 / tcp_tw_recycle 
 echo15> / proc / sys / net / ipv4 / tcp_fin_timeout 
 /etc/init.d/networking restart 
  

< hr>

我的系统

• Debian 8.2


• MySQL 5.5.44-0 + deb8u1


• php5-mysql 5.6.13 + dfsg-0 + deb8u1


• 虚拟机具有64GB内存，48个CPU （2.0 GHz以上的英特尔至强E7540）


• 在ESxi-Host v。6.0.0 2494585运行

load

开始x PHP-instances / loadTester.php

 <？php 
 require_once __DIR__。 /autoloader.php; 
 
 $ numberOfInstances = 100; 
 $ index = 0; 
 $ numberOfDataset = 500; 
 while（$ index< $ numberOfInstances）{
 @system（php。__DIR__。/loadTest.php $ index $ numberOfDataset> / dev / null&）; 
 
 $ index ++; 
} 
  

一个实例SELECT / loadTest.php p>

 <？php 
 require_once __DIR__。 /autoloader.php; 
 
 $ skip = $ argv [1]; 
 $ numberOfDataset = $ argv [2]; 
 
 $ loops = 100; 
 $ index = 0; 
 $ skip = $ skip * $ numberOfDataset; 
 while（$ index< $ loops）{
 $ adresses = AddressQuery :: create（） - > offset（$ skip） - > limit（$ numberOfDataset） - > find（）; 
} 
  

网络图

网络图

解决方案

我找到了一个可能的TCP / IP问题解决方案：

我创建了一个到MySQL的SSH隧道-Server - 远程服务器现在具有10倍的CPU负载，错误不再出现，并且连接现在被加密。

在远程服务器上设置

我创建了一个运行在每个启动时的bash脚本： p>

  ssh -fNg -L 3307：{local server ip}：3306 -p {redirect port} root @ {public ip} 
  

在我的路由器上配置了转发到虚拟数据库服务器 - > {public ip} {redirect port} / p>

I have many PHP-Instances (250 to 500 and more), which open a DB-Connection and do SELECT's, UPDATE's and INSERT's. After few seconds i get the following Error:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2003] Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (4)' in /var/www/xxx/vendor/propel/propel/src/Propel/Runtime/Connection/PdoConnection.php:49

I spend at least 30 hours to find a solution for that. In my opinion, the fault lies in a wrong configuration of MySQL or Debian. I found various configuration-options, but none of them work.

---

Additional informations

• The error only occurs on remote servers - when I start the scripts on the DB-Server directly, i get no errors.

---

Current configs

sysctl.conf

net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
# Disable netfilter on bridges.
#net.bridge.bridge-nf-call-ip6tables = 0
#net.bridge.bridge-nf-call-iptables = 0
#net.bridge.bridge-nf-call-arptables = 0

net.ipv4.ip_forward = 1
net.ipv4.neigh.default.gc_thresh1 = 4096
net.ipv4.neigh.default.gc_thresh2 = 8192
net.ipv4.neigh.default.gc_thresh3 = 16384
net.ipv4.neigh.default.gc_interval = 5
net.ipv4.neigh.default.gc_stale_time = 120
net.core.netdev_max_backlog = 262144
#net.core.rmem_default = 16777216
net.core.rmem_max = 108544
net.core.somaxconn = 32768
net.core.wmem_max = 108544

net.netfilter.nf_conntrack_max = 10000000
net.netfilter.nf_conntrack_tcp_timeout_established = 40
net.netfilter.nf_conntrack_tcp_timeout_close = 10
net.netfilter.nf_conntrack_tcp_timeout_close_wait = 10
net.netfilter.nf_conntrack_tcp_timeout_fin_wait = 10
net.netfilter.nf_conntrack_tcp_timeout_last_ack = 10
net.netfilter.nf_conntrack_tcp_timeout_syn_recv = 10
net.netfilter.nf_conntrack_tcp_timeout_syn_sent = 10
net.netfilter.nf_conntrack_tcp_timeout_time_wait = 10

net.ipv4.tcp_fin_timeout = 1
net.ipv4.tcp_max_orphans = 262144
net.ipv4.tcp_max_syn_backlog = 16384
net.ipv4.tcp_max_syn_backlog = 262144
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_sack = 0
net.ipv4.tcp_syn_retries = 2
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syncookies = 0
net.ipv4.tcp_timestamps = 0
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_wmem = 4096 16384 16777216

etc/mysql/my.cf

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address        = {local server ip}

key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 1100
myisam-recover         = BACKUP
max_connections        = 1000
#table_cache            = 64
#thread_concurrency     = 10

query_cache_limit   = 2G
query_cache_size        = 32M

#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
log_error = /var/log/mysql/error.log
slow_query_log_file = /var/log/mysql/mysql-slow.log
slow_query_log      = 1
long_query_time = 2
log_queries_not_using_indexes
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name

innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 16

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 16M

!includedir /etc/mysql/conf.d/

load modules - modules.conf

nf_conntrack
nf_conntrack_ipv4

script start on startup

echo "1" > /proc/sys/net/ipv4/tcp_tw_reuse
echo "1" > /proc/sys/net/ipv4/tcp_tw_recycle
echo "15" > /proc/sys/net/ipv4/tcp_fin_timeout
/etc/init.d/networking restart

---

My System

• Debian 8.2
• MySQL 5.5.44-0+deb8u1
• php5-mysql 5.6.13+dfsg-0+deb8u1
• Virtual Machine with 64GB RAM, 48 CPU's (Intel Xeon E7540 at 2.0 GHz)
• Run at an ESxi-Host v. 6.0.0 2494585

load Testing

start x PHP-instances / loadTester.php

<?php
    require_once __DIR__ . "/autoloader.php";

    $numberOfInstances = 100;
    $index = 0;
    $numberOfDataset = 500;
    while($index < $numberOfInstances) {
        @system( "php " . __DIR__ . "/loadTest.php $index $numberOfDataset > /dev/null &" );

        $index++;
    }

one instance SELECT / loadTest.php

<?php
    require_once __DIR__ . "/autoloader.php";

    $skip = $argv[ 1 ];
    $numberOfDataset = $argv[ 2 ];

    $loops = 100;
    $index = 0;
    $skip = $skip * $numberOfDataset;
    while ( $index < $loops ) {
        $adresses = AddressQuery::create()->offset( $skip )->limit( $numberOfDataset )->find();
    }

Network Diagram

Network Diagram

解决方案

I found a solution for the "probably TCP/IP-Problem":

I created a SSH-Tunnel to the MySQL-Server - the remote-Server now has a 10 times lower CPU load, the error no longer appears and the connection is now encrypted.

Setting on the remote server

I created a bash-script, that runs on every startup:

ssh -fNg -L 3307:{local server ip}:3306 -p {redirect port} root@{public ip}

On my router I configured a forwarding to the virtual database server -> {public ip} {redirect port}

这篇关于PHP&amp; MySQL - SQLSTATE [HY000] [2003]无法连接到MySQL服务器 - 通过TCP的许多连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！